:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • 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  
  • Nov 05 / 2008
  • 0
dbDigger, T-SQL Interview Questions

Quoted Identifier Enabled (QUOTED_IDENTIFIER) in SQL Server T-SQL

Quoted Identifier option controls the interpretation of double quotation marks by the parser. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, double quotation marks can be used to enclose delimited identifiers, for example, FirstName = “Mike”.
When set to FALSE, identifiers cannot be in quotation marks and must follow all T-SQL rules for identifiers.

Consult us to explore the Databases. Contact us