:::: MENU ::::

Posts Categorized / T-SQL Enhancements

  • Oct 02 / 2017
  • 0
T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Exec vs Sp_ExecuteSQL for dynamic SQL execution

Exec and sp_executeSQL both are used for executing the dynamic sql statements. There are some differences between the both

EXEC

Exec is available since early versions before the sp_executesql. It has no direct support of using or storing the local variables. Also using Exec makes the setup vulnerable to sql injection attacks as parameters appear as part of code.

SP_ExecuteSQL

sp_executesql is improved way to execute dynamic sql statements. It provides mechanism to use and store values in local variables. This mechanism is not vulnerable to sql injection as variables do not appear as part of code but as an operand. Also it enables the sql engine to use the cached execution plan of the statement like a stored procedure.

Conclusion

sp_executesql has upper hand as compared to exec method of executing dynamic sql. Keeping in view the flexibility, efficiency and security of sp_executesql we should opt it for executing dynamic SQL.

  • 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.

Pages:123
Consult us to explore the Databases. Contact us