:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Apr 09 / 2009
  • 0
Case Statement, dbDigger, Null Values, T-SQL Interview Questions

None of the result expressions in a CASE specification can be NULL.

The error message
Msg 8133, Level 16, State 1, Line 2
None of the result expressions in a CASE specification can be NULL.
is a run time error message and may be generated at all versions of SQL Srever. The said error message is generated when one result expression in a CASE expression is NULL. To remove the error message, analyze your case statement and modify it to make sure that no expression is NULL.

  • Mar 19 / 2009
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Using integers in order by clause of T-SQL

Column names are commonly used in order by clause of any select statement in T-SQL. For example a simple select statement in AdventureWorks database using order by clause is as follows.

 
USE AdventureWorks  
 GO  
 SELECT name, groupName, modifieddate  
 FROM humanresources.department  
 ORDER BY Groupname  
 GO  

Result is obviously three columns ordered by [GroupName]. Let us use another more simple approach while using order by clause. Instead of using column name, we may bring order to our results by using integer in order by clause. For example ordering our result set through integer, we have following way.

 
USE AdventureWorks  
 GO  
 SELECT name, groupName, modifieddate  
 FROM humanresources.department  
 ORDER BY 2  
 GO  

Result of both of our scripts is in same order. SQL Server starts count of columns used in select list by 1 and increments by 1. In case of above scripts used, as we have three columns in select list, so we may use integer value from 1 to 3 for first, second and third column respectively. Value provided may not be less than 1. And also it may not be greater than number of columns used in select list. As in our case maximum value is 3.
This syntax of order by using integers may be used in all versions of SQL Server. It is simple than using full column names. Changing the order of columns in select list will also change the criteria being used in order by clause.

Short Comings:

A short coming is there in this approach. By using integer in order by clause you can not order by any column that is not in select list. While by using column names in order list you can order even by the column that is not in select list.

  • Nov 29 / 2008
  • 0
dbDigger, Null Values, T-SQL Interview Questions

Comparing nulls

Question:
What is the Output of the following

IF(NULL IN (34, 35, NULL))
PRINT 'TRUE'
IF(34 NOT IN (35, 36, NULL))
PRINT 'FALSE'

Answer:
Command(s) completed successfully.

Explanation:
Since NULL is an unknown entity, the result of comparing two unknowns is always false. This means that the PRINT statements are never executed as the result of the IF statement is always false.
Ref: IF..ELSE – http://msdn.microsoft.com/en-us/library/ms182717.aspx

Note: The Question is taken from SQLServercentral.com.

  • Nov 27 / 2008
  • 0
dbDigger, Merge Statement, T-SQL Enhancements, T-SQL Interview Questions

operations performed by MERGE statement in SQL Server 2008

Question:
Which of the following operations can be performed by MERGE statement in SQL Server 2008?

Answer:

  • WHEN MATCHED
  • WHEN NOT MATCHED BY TARGET
  • WHEN NOT MATCHED BY SOURCE

Explanation:
Merge is used to check if values match in both Source and Target, which is equal to WHEN MATCHED and an UPDATE is fired. When value exist in SOURCE table but not in TARGET table then WHEN NOT MATCHED [ BY TARGET ] then INSERT the new value and if the value exist in TARGET and not in SOURCE then WHEN NOT MATCHED [BY SOURCE] and DELETE is fired.

Ref: Merge – http://msdn.microsoft.com/en-us/library/bb510625.aspx

Note: The Question is taken from SQLServercentral.com.

  • Nov 15 / 2008
  • 0
dbDigger, T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Insert multiple rows by using a single T-SQL insert statement

Until SQL Server 2000 T-SQL, we have to use multiple insert statements for inserting multiple rows of data in a table. For SQL Server 2005 T-SQL we can insert multiple rows by using a single insert statement. And for SQL Server 2008 T-SQL we have two methods to insert multiple rows by using a single insert statement.
In following example we will go through the statements used in SQL Server 200 T-SQL, SQL Server 2005 T-SQL, SQL Server 2008 T-SQL to insert multiple rows by using single insert statement.

 --For SQL server 2000 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FirstValue',1)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('SecondValue',2)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('ThirdValue',3)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FourthValue',4)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FifthValue',5)  
 GO  


 -- For SQL Server 2005 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 SELECT 'FirstValue' ,1  
 UNION ALL  
 SELECT 'SecondValue' ,2  
 UNION ALL  
 SELECT 'ThirdValue' ,3  
 UNION ALL  
 SELECT 'FourthValue' ,4  
 UNION ALL  
 SELECT 'FifthValue' ,5  
 GO  


 -- For SQL Server 2008 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FirstValue',1),  
 ('SecondValue',2),  
 ('ThirdValue',3),  
 ('FourthValue',4),  
 ('FifthValue',5)  
 GO  
Consult us to explore the Databases. Contact us