ADVANDCED T-SQL SQL Server Temporary Objects

1. ADVANDCED T-SQLSQL Server Temporary Objects DENIZ TASKESEN ITC 226 07/16/2013

2. 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. • Common Table Expressions (CTE) • Table Variables • Temporary Tables

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

4. 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;

5. CTE Use CommunityAssist Go With ServiceGrant_CTE (PersonKey, GrantDate, GrantAmount) As (Select PersonKey, YEAR(GrantDate) As [YEAR], GrantAmount AS [Grant Amount] From Dbo.ServiceGrant Where GrantDateApproved is Not Null) Select PersonKey , GrantDate, SUM(GrantAmount) As [Total Grant] From ServiceGrant_CTE Group by PersonKey, GrantDate Order By PersonKey, GrantDateDesc;

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

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

8. Table Variables DECLARE @TempGrant As Table ( PersonKeyInt, TotalGrant Money ) INSERT INTO @TempGrant SELECT SG.PersonKey, CONVERT(Varchar(20), Sum(GrantAmount),1) FROM ServiceGrant SG GROUP BY SG.PersonKey Select * From @TempGrant;

9. Temporary Tables • Local Temporary Tables • Global Temporary Tables

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

11. Local Temporary Tables Create Table #TempGrant ( PersonKeyInt, TotalGrant Money ) INSERT INTO #TempGrant SELECT SG.PersonKey, CONVERT(Varchar(10), Sum(GrantAmount), 1) FROM ServiceGrant SG Where GrantDateApproved is Not NULL GROUP BY SG.PersonKey Select * From #TempGrant;

12. 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 TempDB 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.

13. Global Temporary Tables Create Table ##TempGrant ( PersonKeyInt, TotalGrant Money ) INSERT INTO ##TempGrant SELECT SG.PersonKey, CONVERT(Varchar(10), Sum(GrantAmount), 1) FROM ServiceGrant SG Where GrantDateApproved is Not NULL GROUP BY SG.PersonKey Select * From ##TempGrant;