ADVANDCED TSQL Server Temporary Objects DENIZ TASKESEN ITC

ADVANDCED T-SQL Server Temporary Objects DENIZ TASKESEN ITC 226 07/16/2013

SQL Server Temporary Objects Ø The main purpose of using temporary objects is to temporarily store a data set for later use in order to increase the efficiency. Ø Using temporary objects offers the advantages of improved readability and ease in maintenance of complex queries. o Common Table Expressions (CTE) o Table Variables o Temporary Tables

Common Table Expressions (CTE) A common table expression (CTE) is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. Ø CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views. Ø CTEs are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends. Ø

Syntax structure WITH expression_name [ ( column_name [, . . . n] ) ] AS ( CTE_query_definition ) The statement to run the CTE is: SELECT <column_list> FROM expression_name;

CTE Use Community. Assist Go With Service. Grant_CTE (Person. Key, Grant. Date, Grant. Amount) As (Select Person. Key, YEAR(Grant. Date) As [YEAR], Grant. Amount AS [Grant Amount] From Dbo. Service. Grant Where Grant. Date. Approved is Not Null) Select Person. Key , Grant. Date, SUM(Grant. Amount) As [Total Grant] From Service. Grant_CTE Group by Person. Key, Grant. Date Order By Person. Key, Grant. Date Desc;

Table Variables Ø Ø Table variables are used within the scope of the routine or batch within which they are defined, and were originally created to make table-valued functions possible. They behave like other variables in their scoping rules. Once out of scope, they are disposed of. Table variables require less locking resources as they are 'private' to the process that created them. Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.

Syntax structure DECLARE @Local_Variable [As] Table ( [(Column_Definition) [, …n]) ) * It has to be prefixed with an at (@) symbol.

Table Variables DECLARE @Temp. Grant As Table ( Person. Key Int, Total. Grant Money ) INSERT INTO @Temp. Grant SELECT SG. Person. Key, CONVERT(Varchar(20), Sum(Grant. Amount), 1) FROM Service. Grant SG GROUP BY SG. Person. Key Select * From @Temp. Grant;

Temporary Tables Local Temporary Tables Ø Global Temporary Tables Ø

Local Temporary Tables Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed. Ø With Local temporary table is similar to table variables. As with Table Variables, Local Temporary tables are private to the process that created it. Ø They cannot therefore be used in views and you cannot associate triggers with them. Ø

Local Temporary Tables Create Table #Temp. Grant ( Person. Key Int, Total. Grant Money ) INSERT INTO #Temp. Grant SELECT SG. Person. Key, CONVERT(Varchar(10), Sum(Grant. Amount), 1) FROM Service. Grant SG Where Grant. Date. Approved is Not NULL GROUP BY SG. Person. Key Select * From #Temp. Grant;

Global Temporary Tables Ø Ø Ø Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed. Anyone who has access to Temp. DB at the time these Global Temporary tables exist can directly query, modify or drop these temporary objects. You can associate rules, defaults, and indexes with temporary tables, but you cannot create views on temporary tables or associate triggers with them.

Global Temporary Tables Create Table ##Temp. Grant ( Person. Key Int, Total. Grant Money ) INSERT INTO ##Temp. Grant SELECT SG. Person. Key, CONVERT(Varchar(10), Sum(Grant. Amount), 1) FROM Service. Grant SG Where Grant. Date. Approved is Not NULL GROUP BY SG. Person. Key Select * From ##Temp. Grant;