:::: MENU ::::

Posts Categorized / Derived Tables

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

  • Nov 29 / 2008
  • 0
dbDigger, Derived Tables

The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

The error message
Msg 1011, Level 16, State 1, Line 1
The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

is a run time error message of severity level 16. The said error message can appear on all versions of SQL Server. This error message appears when you try to use the same name for more than one derived table within the same scope in a query. As errors of Severity Level 16 are generated by the and can be corrected by the user. To remove the error each derived table in the same scope must have a unique name.

Consult us to explore the Databases. Contact us