:::: MENU ::::

Posts Categorized / T-SQL Enhancements

  • Apr 15 / 2009
  • 0
dbDigger, T-SQL Enhancements, T-SQL Interview Questions, T-SQL Tips and Tricks

Use EXCEPT to select values that does not exist in other table

EXCEPT operation is introduced in SQL Server 2005 and onwards. It is used to rows from left side query that are not present in right side query. So, It may be used to get rows that are present in one table but not in other.
Consider a case that we want to select rows containing name, designation, department from table name staff which are not present in table name Officers.

 
SELECT nameofStaff, designationofStaff, departmentofStaff  
 FROM staff  
 EXCEPT  
 SELECT nameofOfficer, designationofOfficer, departmentofOfficer  
 FROM Officers  

Above script will provide us distinct records that are there in Staff table but are not present in Officers table. Number of columns should be same on both sides of EXCEPT. And data types of columns should also match.
For more details please visit BOL

  • Apr 15 / 2009
  • 1
dbDigger, T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Use INSTERSECT to select common values between two tables

INTERSECT operation is introduced in SQL Server 2005 and onwards. It is used to get common rows between both sides in distinct form. So, It may be used to get common rows between two tables.
Consider a case that we want to select common rows containing name, designation, department from two tables named staff, officers’
In this scenario we can use the INTERSECT operands in following way

SELECT nameofStaff, designationofStaff, departmentofStaff  
 FROM staff  
 INTERSECT  
 SELECT nameofOfficer, designationofOfficer, departmentofOfficer  
 FROM Officers  

Above script will provide us distinct rows that are common on given columns. Number of columns should be same on both sides of INTERSECT. And data types of columns should also match. For more details please visit BOL

  • Jan 29 / 2009
  • 0
dbDigger, Derived Tables, T-SQL Enhancements, T-SQL Tips and Tricks

Consider use of derived tables instead of temporary tables

Derived tables are SELECT statements that act as tables in the FROM clause. In various scenarios these can provide better performance than temporary tables. Consider following piece of script where i have used derived table

 
USE AdventureWorks  
 GO  
 SELECT DISTINCT s.PurchaseOrderNumber  
 FROM Sales.SalesOrderHeader s INNER JOIN  
 ( SELECT SalesOrderID  
 FROM Sales.SalesOrderDetail  
 ) d  
 ON s.SalesOrderID = d.SalesOrderID  

commands following the JOIN statement are working as derived table.

  • Dec 04 / 2008
  • 0
dbDigger, Monitoring and Analysis, T-SQL Enhancements

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views

As we know that system tables of SQL Server 2000 have been evolved to system views in SQL Server 2005. Although many system tables tables have been migrated to SQL Server 2005, but using the system view is more recommended because these will be preserved in coming versions of SQL Server. And also system views provided in SQL Server 2005 provide much more information about internals of SQL Server and meta data.
Visit following link to get mapping of SQL Server 2000 tables to SQL Server 2005 system views.

http://msdn.microsoft.com/en-us/library/ms187997(SQL.90).aspx

  • 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  
Pages:123
Consult us to explore the Databases. Contact us