European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2016 Hosting - HostForLIFE.eu :: Max Degree Of Parallelism Vs Cost Threshold For Parallelism

clock May 8, 2020 10:09 by author Peter

The title sounds like something that you would see for a boxing match. However, in reality it is a misleading title. The Max Degree of Parallelism and the Cost Threshold for Parallelism SQL Server settings actually work more together than they do against each other.

These two settings actually define the how many and the when in regards to parallel execution plans. The Max Degree of Parallelism (MDop) simply defines the number of processors/cores that SQL Server will use when the optimizer determines parallelism is needed. The Cost Threshold for Parallelism is cost threshold of when the SQL Server will use parallelism. The cost is the overall cost the optimizer determines for each query and SQL Server will use parallelism if the cost is above the threshold value.
 

The recommended settings for MDop is the number of cores not to exceed 8. However, when setting this, it is important to continue to monitor the system to see if the change has caused an improvement. The default value is 0.
 
The recommended setting for the Cost Threshold for Parallelism is 25 to 50 and has a default value of 5. Yes, the default setting is too low. By changing this, SQL Server will reduce the number of smaller queries that may use parallelism. Just like the MDop setting, it is important to monitor the server after making a change to see if an improvement is make. There have been many times where simply changing this setting from the default, I have seen the CPU utilization drop from close to 100% to less than 10%. This in no way means you will see the same improvement, just what I have seen.
 
Neither one of these settings require a reboot of the service when changing. It is also important to keep in mind that if the MDop is set to 1, SQL Server will ignore the Cost Threshold for Parallelism setting.
 
This snippet of code can be used to query server configuration settings
    SELECT CONVERT(VARCHAR(60), name) AS 'Name' 
    , value 
    , description 
    FROM sys.configurations



SQL Server 2016 Hosting - HostForLIFE.eu :: Could Not Find Server 'Server Name' In sys.servers In SQL Server

clock April 29, 2020 07:39 by author Peter

I was working with a client and they had set up one sql server for an ETL process. When we tried to get the data from  the database we got the error:
"Could not find server 'server name' in sys.servers in SQL Server"

How to resolve this
First you need to check if the server exists in sys servers,
select name from sys.servers 

You will get the servers list here, if the server does not exist in the list, then add it using the command,
EXEC sp_addlinkedserver @server = 'New_Server_Name' 

Once the server is added to the linked server, then you can log in like this,
EXEC sp_addlinkedsrvlogin 'New_Server_Name','false',NULL,'USERNAME','PASSWORD' 

Now you can do whatever you want to do, you can use your local server now,
exec [New_Server_Name].[Database_Name].dbo.Procedure_NAME

Finally, you can drop this server from the linked server list using this command,
sp_dropserver 'New_Server_Name', 'droplogins' 



SQL Server 2016 Hosting - HostForLIFE.eu :: Apply a Single Script for Multiple Databases Using Database Name

clock April 17, 2020 07:12 by author Peter

Hi all, In this blog, I will explain to you how to apply a script or a query to multiple databases in a single execution using a database name. I get all database names, create a loop, and execute a command that I want to do.

Here I am using 2 databases, EnterpriseGL and GR8. I get all database names using a loop, and after that, I get database names one by one, put a script with a name, and execute the script.
  BEGIN TRAN 
    
  CREATE TABLE #TempGETDBLIST 
     ( 
        [NAME] NVARCHAR(255) NULL, 
        [ROWID] INT IDENTITY NOT NULL 
     ) 
           INSERT INTO #TempGETDBLIST (NAME) 
    
        --unomment which database use want to apply 
       --SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%EnterpriseGL%'  -- get all GL databases 
        --SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%GR8%' --get all GR8 databases 
    
          SELECT '#TempGETDBLIST',* FROM #TempGETDBLIST 
           DECLARE @Flag INT = 1 
    
  WHILE (@Flag <= (SELECT COUNT(1) FROM #TempGETDBLIST)) 
  BEGIN 
        DECLARE @ABC NVARCHAR(50), @query NVARCHAR(max) 
        SET @ABC =(SELECT Name FROM #TempGETDBLIST WHERE ROWID = @Flag) 
        SET @query = 'USE '+ @ABC +' select top 1 * from SystemConfiguration' 
    
     EXECUTE( @query ) 
     SET @Flag = @Flag + 1 
    
  END 
    
  DROP TABLE #TempGETDBLIST 
  ROLLBACK TRAN

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: SQL 'Is Null' Statement Explained

clock April 8, 2020 07:41 by author Peter

The SQL 'Is Null' statement returns the specified value if the expression is NULL, otherwise, it returns the expression. If the expression is NOT NULL, then this function returns the expression.


Syntax
    SELECT ISNULL(NULL, 'Csharpcorner');   

This replaces Null with the specified replacement value. 
 
A null statement doesn't specify that the Boolean result is negated. The predicate reverses its return values, returning True if the value is not Null, and False if the value is a Null statement.
 
Syntax

    ISNULL ( check_expression , replacement_value ) 

This is the expression returned if the check_expression is NULL. The replacement_value must be of a type that is implicitly convertible to the type of check_expression.
 
Using IsNull with Avg in SQL statement
 
The following example finds the average of the OrderDetails.  It substitutes the value 8 for all NULL entries in the orderId column of theOrderDetails table.
 
Syntax
    USE sample ;     
    GO     
    SELECT AVG(ISNULL(OrderId, 8))     
    FROM OrderDetails ;     
    GO     


Example

Using ISNULL in SQL
The following example selects the description, discount percentage, minimum quantity, and maximum quantity for all special offers in the sample. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00. 
 
Syntax
    USE sample ;     
    GO     
    SELECT OrderId , OrderName, Orderaddress , ISNULL(OrderId, 0.00) AS 'Max Quantity'     
    FROM OrderDetails;     
    GO   

Testing for NULL in a Where clause
Do not use ISNULL to find NULL values; use IS NULL instead. The following example finds all OrderDetails that have NULL in the ordername, orderAddress, OrderDate  column. Note the space between IS and NULL.
 
Syntax
    -- Uses sample     
    SELECT OrderName, orderAddress, OrderDate     
    FROM  OrderDetails     
    WHERE OrderId  IS NULL;  



SQL Server 2016 Hosting - HostForLIFE.eu :: Cursors In SQL Server

clock April 1, 2020 10:28 by author Peter

A SQL cursor is a database object that is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row. This article explains everything about SQL cursors. In this article, we will learn the following:

  •     Introduction to SQL cursor
  •     Cursor life cycle
  •     Why and when use a cursor
  •     How to implement cursors
  •     What are the limitation of SQL cursor
  •     How can we replace a SQL Cursor

SQL Cursor Life Cycle
The following steps are involced in a SQL cursor life cycle.

    Declaring Cursor
    A cursor is declared by defining the SQL statement.

    Opening Cursor
    A cursor is opened for storing data retrieved from the result set.

    Fetching Cursor
    When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

    Closing Cursor
    The cursor should be closed explicitly after data manipulation.

    Deallocating Cursor
    Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.

Why use a SQL Cursor?
In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors.

In programming, we use a loop like FOR or WHILE to iterate through one item at a time, the cursor follows the same approach and might be preferred because it follows the same logic.
SQL Cursor Syntax
    DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]  
    [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]  
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]  
    [ TYPE_WARNING ] FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;] 
 

Cursor Example

The following cursor is defined for retrieving employee_id and  employee_name from Employee table.The FETCH_STATUS value is 0 until there are rows.when all rows are fetched then  FETCH_STATUS becomes 1.
    use Product_Database 
    SET NOCOUNT ON;   
     
    DECLARE @emp_id int ,@emp_name varchar(20),   
        @message varchar(max);   
     
    PRINT '-------- EMPLOYEE DETAILS --------';   
     
    DECLARE emp_cursor CURSOR FOR    
    SELECT emp_id,emp_name   
    FROM Employee 
    order by emp_id;   
     
    OPEN emp_cursor   
     
    FETCH NEXT FROM emp_cursor    
    INTO @emp_id,@emp_name   
     
    print 'Employee_ID  Employee_Name'      
     
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        print '   ' + CAST(@emp_id as varchar(10)) +'           '+ 
            cast(@emp_name as varchar(20)) 
     
         
        FETCH NEXT FROM emp_cursor    
    INTO @emp_id,@emp_name   
      
    END    
    CLOSE emp_cursor;   
    DEALLOCATE emp_cursor;   


The Output of the above program will be as follows

SQL Server

What are the limitations of a SQL Cursor

A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.

Cursors can be faster than a while loop but they do have more overhead.

Another factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements.

Too many columns being dragged around in memory, which are never referenced in the subsequent cursor operations, can slow things down.

The cursors are slower because they update tables row by row.
How can we replace SQL Cursors
There's one replacement for cursors in SQL server joins.

Suppose we have to retrieve data from two tables simultaneously by comparing primary keys and foreign keys. In these types of problems, the cursor gives very poor performance as it processes through each and every column. On the other hand using joins in those conditions is feasible because it processes only those columns which meet the condition. So here joins are faster than cursors.

The following example explains the replacement of cursors through joins.

Suppose, we have two tables, ProductTable and Brand Table. The primary key of BrandTable is brand_id which is stored in ProductTable as foreign key brand_id. Now suppose, I have to retrieve brand_name from BrandTable using foreign key brand_id from ProductTable. In these situations cursor programs will be as follows,
    use Product_Database 
    SET NOCOUNT ON;   
     
    DECLARE @brand_id int    
    DECLARE @brand_name varchar(20)  
     
     
    PRINT '--------Brand Details --------';   
     
    DECLARE brand_cursor CURSOR FOR    
    SELECT distinct(brand_id) 
    FROM ProductTable;  
     
    OPEN brand_cursor   
     
    FETCH NEXT FROM brand_cursor    
    INTO @brand_id   
     
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        select brand_id,brand_name from BrandTable where brand_id=@brand_id 
    --(@brand_id is of ProductTable) 
         
        FETCH NEXT FROM brand_cursor    
    INTO @brand_id  
      
    END    
    CLOSE brand_cursor;   
    DEALLOCATE brand_cursor;   


The Output of the above program will be as follows

SQL Server

The same program can be done using joins as follows,
Select distinct b.brand_id,b.brand_name from BrandTable b inner join
ProductTable p on b.brand_id=p.brand_id

The Output of the above program will be as follows

SQL Server

As we can see from the above example, using joins reduces the lines of code and gives faster performance in case huge records need to be processed.



SQL Server 2016 Hosting - HostForLIFE.eu :: SQL UNIQUE Constraint

clock March 18, 2020 12:55 by author Peter

The Unique constraint statement ensures that all values in a column are different. Both the Unique and Primary Key constraints provide a guarantee for uniqueness for a column or set of columns. A Primary key constraint automatically has a unique constraint in SQL. However, you can have many unique constraints per table, but only one primary key constraint per table.

 
We can create a unique constraint in SQL Server 2019 (15.x) by using SQL server management studio or SQL to ensure no duplicate values are entered in specific columns that do not participate in a primary key. Creating a unique constraint automatically creates a corresponding unique index in SQL.  
 
SQL Server Unique constraints allow you to ensure that the data stored in a column, or a group of columns, is unique among the rows in a table. 
 
Syntax
    CREATE TABLE EmployeeName  (       
     EmpID int NOT NULL UNIQUE,       
      EmpName varchar(255) NOT NULL,        
    );    


The above query creates a table with the name "EmployeeName"  and column name EmpID which is both Not Null and Unique(i.e we cannot have empty or duplicate data) and EmpName
 
Using Unique constraint on create table statement
 
Syntax
    CREATE TABLE Employee  (     
     EmpID int NOT NULL UNIQUE,     
      EmpName varchar(255) NOT NULL,     
      EmpFirstName varchar(255),     
      EmpLastname varchar(255),     
      EmpAge int     
    );    


The above query created a table with the name "Employee" and the first column name EmpId is Not NULL and UNIQUE, other column name EmpName,EmpFirstName,EmpLastname, EmpAge
 
Using Unique constraint on alter table
 
Syntax
    ALTER TABLE Employee      
    ADD UNIQUE (EmpID);    


 The above query with add a column EmpID and make it UNIQUE.
 
"Unique" is used to signify a Unique constraint, and also to define a unique name a Unique constraint,on multiple columns.
 
Syntax
    ALTER TABLE Employee      
    ADD CONSTRAINT UC_Employee UNIQUE (EmpID,EmpLastName);    

The above query will add EmpID and EmpLastName columns into the UC_Employee table, with the Unique Constraint
 
Using Drop a Unique constraint statement
 
Use the following example to drop a Unique constraint:
 
Syntax 
    ALTER TABLE Employee      
    DROP CONSTRAINT UC_Employee;    

The above query will remove the "Unique" Constrain from the Employee table.
 
Using SQL Server Management Studio in Unique Constraint
 
To create a unique constraint statement:
    In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.
    On the Table Designer menu, click Indexes/Keys.
    In the Indexes/Keys dialog box, click Add.
    In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.
    On the File menu, click Save table name.

Using unique constraint in SQL
 
To create a unique constraint,

    In Object Explorer, connect to an instance of Database Engine.
    On the Standard bar, click New Query.
    Copy and paste the following example into the query window and click Execute. This example creates the table SampleDetails and creates a unique constraint on the column TransactionID.

Syntax
    USE sample ;       
    GO       
    CREATE TABLE SampleDetails       
     (       
       TransactionID int NOT NULL,        
       CONSTRAINT AK_TransactionID UNIQUE(TransactionID)        
    );        
    GO      

The above query will create a table SampleDetails in the sample database, with TransactionID and the AK_TransactionID constraint which makes TransactionID unique
 
To create a unique constraint on an existing table
    In Object Explorer, connect to an instance of Database Engine.
    On the Standard bar, click New Query.
    Copy and paste the following example into the query window and click Execute. The example creates a unique constraint on the columns PasswordHash and PasswordSalt in the table Person.Password. 

Syntax
    USE sample         
    GO       
    ALTER TABLE Person.Password        
    ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);        
    GO  
     

The above query appends the table Person.Password in the sample database , with the AK_Password CONSTRAINT which makes PasswordHash amd PasswordSalt unique.
 
To create a unique constraint in a new table
    In Object Explorer, connect to an instance of Database Engine.
    On the Standard bar, click New Query.
    Copy and paste the following example into the query window and click Execute. The example creates a table and defines a unique constraint on the column TransactionID.

Syntax 
    USE sample;       
    GO       
    CREATE TABLE Production.TransactionHistoryArchive2       
    (       
       TransactionID int NOT NULL,       
       CONSTRAINT AK_TransactionID UNIQUE(TransactionID)       
    );       
    GO   
   

The above query uses the sample database and creates a table with the name "Production.TransactionHistoryArchive2" and column name TransactionID and CONSTRAINT AK_TransactionID UNIQUE column name is TransactionID.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: Collation Error In SQL Sever

clock February 12, 2020 11:46 by author Peter

ERROR - "Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS within the up to operation."

Don’t panic if you get this error while joining your tables. there's an easy way to solve this. It happens because of the different collation settings on 2 columns we are joining.

The first step is to figure out what are the two collations that have caused the conflicts.
Let us assume that collation has not been set at the column level and instead at the db level. Then, we've to execute two straightforward statements as below.
Statements

  • Select DATABASEPROPERTYYEX('DB1',N'Collation')
  • Select DATABASEPROPERTYYEX('DB2',N'Collation') 

One more thing to make a note of here is that if you are on SharePoint, you will get an error as following.

Latin_General_CI_AS_KS_WS.
 
If you are on any other database and use the default settings, you may get this SQL_Latin_General_CP1_CI_AS.

Now, we have to do something similar to CAST, called Collate (FOR Collation).

Refer to the example below.
      select * from Demo1.dbo.Employee emp 
    join Demo2.dbo.Details dt 
    on (emp.email =dt.email COLLATE SQL_Latin_General_CP1_CI_AS) 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: SQL Server Full Text Search with rank values

clock August 28, 2019 12:51 by author Peter

OnceSQL Server Full-Text Search with rank values  I wrote a post titled enabling Fulltext search in Azure SQL database discussing Full-Text search in Azure. while using it with one of my databases, needed to show the result of the search ordered by however well they match to the search criteria. in order to sort the result as i need, the best is, get a rank generated for every row and use it for ordering the result. I had used Freetext operate for obtaining the result but if i realized that this can not be achieved using the Freetext function.

The CONTAINSTABLE and FREETEXTTABLE functions return a column named Rank for showing the rank related to the record based on matching. this can be used get the result sorted based on it, showing most relevant records at the top. Remember, the higher value of the Rank generated indicates the best matching.

Now, write the following code:
view plainprint?

    -- Creating a table  
    CREATE TABLE dbo.EmployeeDetails  
    (  
     EmployeeDetailsId int identity(1,1) not null  
     , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)  
     , WorkingExperience nvarchar(4000) not null  
     , ProjectsWorked nvarchar(4000) not null  
     , Resume nvarchar(max)   
    )  
    GO  
      
    CREATE FULLTEXT CATALOG EmployeeCatelog;  
    GO  
      
    CREATE FULLTEXT INDEX ON dbo.EmployeeDetails   
     (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails  
     ON EmployeeCatelog;  
     -- By default CHANGE_TRACKING = AUTO  
      
      
    -- Once enabled, search can be performed;  
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');  
      
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((Resume), 'SQL');  
      
    -- Get the rank and sort the result using it  
    SELECT t.Rank, e.*  
    FROM dbo.EmployeeDetails e  
     INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t  
      ON e.EmployeeDetailsId = t.[Key]  
    ORDER BY t.Rank DESC  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: Sp_MSforeachtable Procedure in SQL Server

clock June 20, 2019 11:29 by author Peter
All versions of SQL Server have undocumented Stored Procedures or functions. This may be because those Stored Procedures or functions are used by Microsoft internally. This type of Stored Procedure or function (undocumented) can be any without any notification. The "sp_MSforeachtable" Stored Procedure comes with SQL Server, but it is not documented in MSDN. This Stored Procedure could be found in the Master database. The Stored Procedure "sp_MSforeachtable" allows us to easily process some code against each and every table in a single database. It means that it is used to process a single T-SQL command or number of various T-SQL commands against every table in the database.

sp_MSforeachtable Syntax

sp_MSforeachtable [ @command1 = ] 'command1' [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @whereand = ] where_and_Condition ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]

Parameter
Parameter Description
@command1 It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000).
@replacechar It is a character in the command string that needs to be replaced with the table name being processed. The default value of this parameter is a "?".
@command2 @command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute.
@command3
@whereand This parameter could be used to provide additional constraints to the command for helping to identify the rows in the sysobjects table that will be selected. Its data type is nvarchar(2000).
@precommand This command is to be run before processing any table. Its data type is nvarchar(2000).
@postcommand This command is to be run after the processing of all the tables. Its data type is nvarchar(2000).

Definition of sp_MSforeachtable procedure in SQL Server

    CREATE PROCEDURE sys.sp_MSforeachtable   
     @command1 NVARCHAR(2000),   
     @replacechar NCHAR(1) = N'?',   
     @command2 NVARCHAR(2000) = null,   
     @command3 NVARCHAR(2000) = null,   
     @whereand NVARCHAR(2000) = null,   
     @precommand NVARCHAR(2000) = null,   
     @postcommand NVARCHAR(2000) = null   
    AS   
    -- This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set   
     -- @precommand and @postcommand may be used to force a single result set via a temp table.  
     -- Preprocessor won't replace within quotes so have to use STR().  
     DECLARE @mscat NVARCHAR(12)   
     SELECT @mscat = LTRIM(STR(CONVERT(INT, 0x0002)))   
     IF (@precommand is not null)   
      EXEC(@precommand)   
     -- Create the SELECT  
       EXEC(N'DECLARE hCForEachTable cursor global for SELECT ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =   
     syso.object_id '   
             + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '   
             + @whereand)   
     DECLARE @retval INT   
     SELECT @retval = @@error   
     IF (@retval = 0)   
      EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0   
     IF (@retval = 0 and @postcommand is not null)   
      EXEC(@postcommand)   
     RETURN @retval 


The following script helps us to list all the tables of the "TestDb" database.

Example script

Use Testdb  
exec sp_MSforeachtable 'print "?"' 
Another example. The following script helps us to determine the space used and allocated for every table in the database.

Example script
 Use Testdb 
    exec sp_MSforeachtable 'EXECUTE sp_spaceused [?];' 
Common uses of sp_MSforeachtable Stored
Procedure
This stored produce may be used for the following purposes.
  • To get the size of all the tables in the database
  • To rebuild all indexes of all the tables in the database
  • Disable all constraints and triggers of all the tables in the database
  • Delete all the data from all the tables in the database
  • To RESEED all tables to 0
  • To get the Number of Rows in all tables in a database
  • Update the statistics of all the tables in a database
  • Reclaim space from dropped variable-length columns in tables or indexed views of the database
These undocumented Stored Procedures can be used if we want to do the same operation on each table of any database. Please note that Microsoft may change the functionality and definition of this Stored Procedure at any time.

HostForLIFE.eu SQL Server 2016 Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: SQL Interview Questions And Answers

clock May 22, 2019 06:40 by author Peter

In this post we are going to share the interview questions or the information which you must know as a programmer or a developer, especially if you are a Dot Net developer. I hope you will like this article.

Background

I am a dot net developer. As a dot net developer, there are so many things that I must be aware of. I am sharing those in the form of articles, you can always read my other interview questions here in the following links.

So shall we now discuss about SQL interview questions.

SQL Interview Questions
Question: What are the types of Joins in SQL. Explain?

INNER JOIN: Returns all rows when there is at least one match in BOTH the tables.
LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
FULL JOIN: Returns all rows when there is a match in ONE of the table.

Question: What is the default join in SQL? Give an example query?
The default join is INNER JOIN.

Example

SELECT column_name(s)  
FROM table1  
INNER JOIN table2  
ON table1.column_name=table2.column_name;  

Question: Describe all the joins with examples in SQL?
SQL LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
    SQL LEFT JOIN Syntax  
    SELECT column_name(s)  
    FROM table1  
    LEFT JOIN table2  
    ON table1.column_name=table2.column_name;  


SQL RIGHT JOIN

The right join returns all the rows in the right table i.e. table 2 with the matching ones on the left table (table1).
    SELECT column_name(s)  
    FROM table1  
    RIGHT JOIN table2  
    ON table1.column_name=table2.column_name;  


SQL FULL OUTER
The full join returns all rows from the left table (table1) and from the right table (table2).
    SELECT column_name(s)  
    FROM table1  
    FULL OUTER JOIN table2  
    ON table1.column_name=table2.column_name;  


Question: What is Union and Union All ? Explain the differences?
SQL UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Note: The UNION operator selects only distinct values by default.
    SELECT column_name(s) FROM table1  
    UNION  
    SELECT column_name(s) FROM table2;  
SQL UNION ALL
    SQL UNION ALL Syntax  
    SELECT column_name(s) FROM table1  
    UNION ALL  
    SELECT column_name(s) FROM table2;  

Allows duplicate values.

Question: Differentiate Clustered and Non clustered Index in SQL?
A clustered index is one in which the index’s order is arranged according to the physical order of rows in the table. Due to this reason there can only be one clustered index per table, usually this is the primary key.

A non clustered index is one in which the order of index is not in accordance with the physical order of rows in the table.

Create Index Syntax
CREATE INDEX [ CLUSTERED | NONCLUSTERED ] PIndex ON Persons (LastName,FirstName)

Question: Explain the difference between Stored Procedure and User Defined Function?

Stored Procedure

Stored procedures are reusable code in database which is compiled for first time and its execution plan saved. The compiled code is executed when every time it is called.

Function

Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only a single value or a table. We can’t use functions  to Insert, Update, Delete records in the database table(s). It is compiled every time it is invoked.

Basic Difference
Function must return a value but in Stored Procedure it is optional (Procedure can return zero or n values).
Functions can have only input parameters for it whereas procedures can have input/output parameters.

Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advanced Differences

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it,  whereas Function allows only SELECT statement in it. Procedures cannot be utilized in a SELECT statement, whereas function can be embedded in a SELECT statement. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas function can be.

The most important feature of stored procedures over function is retention and reusing the execution plan while in case of function it will be compiled every time.
Functions that return tables can be treated as another rowset. This can be used in JOINS with other tables.
Inline Function can be though of as views that take parameters and can be used in JOINS and other Rowset operations.
Exception can be handled by try-catch block in a procedure, whereas try-catch block cannot be used in a Function.
We can use transactions in stored procedure but not in functions.

Conclusion
Did I miss anything that you may think which is needed? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.

 



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in