临时表和表变量、内联UDF 派生表 CTE

1. tsql需要把临时数据具体化。如临时表、表变量、表表达式（视图、内联UDF、派生表、CTE）

2. ----------------------------------------------------------------------- Chapter 02 - Temporary Tables and Table Variables-----------------------------------------------------------------------tsql需要把临时数据具体化。如临时表、表变量、表表达式（视图、内联UDF、派生表、CTE）CREATE TABLE #T1(col1 INT);SELECT name FROM tempdb.sys.objects WHERE name LIKE '#T1%';DROP TABLE #T1;GO----------------------------------------------------------------------- Temporary Table Name Resolution----------------------------------------------------------------------- Creation Script for proc1 and proc2; T1 and T2 with Same SchemaSET NOCOUNT ON;USE tempdb;GOIF OBJECT_ID('dbo.proc1') IS NOT NULL  DROP PROC dbo.proc1;GOIF OBJECT_ID('dbo.proc2') IS NOT NULL  DROP PROC dbo.proc2;GOCREATE PROC dbo.proc1ASCREATE TABLE #T1(col1 INT NOT NULL);INSERT INTO #T1 VALUES(1);SELECT * FROM #T1;EXEC dbo.proc2;GOCREATE PROC dbo.proc2ASCREATE TABLE #T1(col1 INT NULL);INSERT INTO #T1 VALUES(2);SELECT * FROM #T1;GO-- Execute proc1EXEC dbo.proc1;GO-- Alter proc2; T1 and T2 with Different SchemasALTER PROC dbo.proc2ASCREATE TABLE #T1(col1 INT NULL, col2 INT NOT NULL);INSERT INTO #T1 VALUES(2, 2);SELECT * FROM #T1;GO-- Execute proc1, failsEXEC dbo.proc1;GO-- Execute proc2, succeedsEXEC dbo.proc2;GO-- Execute proc1, succeedsEXEC dbo.proc1;GO----------------------------------------------------------------------- Schema Changes of Temporary Tables in Dynamic Batches----------------------------------------------------------------------- Sample Code -- Assume @schema and @insert were constructed dynamicallyDECLARE @schema AS VARCHAR(1000), @insert AS VARCHAR(1000);SET @schema = 'col1 INT, col2 DECIMAL(10, 2)';SET @insert = 'INSERT INTO #T42 VALUES(10, 20.30)';--In the outer level, create temp table #T with a single dummy columnCREATE TABLE #T42(dummycol INT);--Within a dynamic batch:--    Alter #T adding the columns you need--    Alter #T dropping the dummy column--    Open another level of dynamic execution--      Populate #TEXEC('ALTER TABLE #T42 ADD ' + @schema + ';ALTER TABLE #T42 DROP COLUMN dummycol;EXEC(''' + @insert + ''')');GO--Back in the outer level, access #T in a new batchSELECT * FROM #T42;-- CleanupDROP TABLE #T42;GO----------------------------------------------------------------------- Global Temporary Tables----------------------------------------------------------------------- Termination-- Connection 1CREATE TABLE ##T1(col1 INT);INSERT INTO ##T1 VALUES(1);-- Connection 2BEGIN TRAN  UPDATE ##T1 SET col1 = col1 + 1;-- Close connection 1-- Connection 2  -- Succeeds  SELECT * FROM ##T1;COMMIT-- FailsSELECT * FROM ##T1;GO-- Created in startup procedure-- Creation Script for sp_Globals ProcedureUSE master;GOIF OBJECT_ID('dbo.sp_Globals') IS NOT NULL  DROP PROC dbo.sp_GlobalsGOCREATE PROC dbo.sp_GlobalsASCREATE TABLE ##Globals(  varname sysname NOT NULL PRIMARY KEY,  val     SQL_VARIANT NULL);GOEXEC dbo.sp_procoption 'sp_Globals', 'startup', 'true';GO-- Restart SQL Server-- Add a global variableSET NOCOUNT ON;INSERT INTO ##Globals VALUES('var1', CAST('abc' AS VARCHAR(10)));SELECT * FROM ##Globals;-- CleanupUSE master;GODROP PROC dbo.sp_Globals;DROP TABLE ##Globals;GO----------------------------------------------------------------------- Table Variables----------------------------------------------------------------------- Creating a table variableDECLARE @T1 TABLE(col1 INT);INSERT @T1 VALUES(1);SELECT * FROM @T1;GO----------------------------------------------------------------------- tempdb---------------------------------------------------------------------SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIKE '%#%';GODECLARE @T TABLE(col1 INT);INSERT INTO @T VALUES(1);SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIKE '%#%';GO----------------------------------------------------------------------- Statistics----------------------------------------------------------------------- Table Variable ExampleDECLARE @T TABLE(  col1 INT NOT NULL PRIMARY KEY,  col2 INT NOT NULL,  filler CHAR(200) NOT NULL DEFAULT('a'),  UNIQUE(col2, col1));INSERT INTO @T(col1, col2)  SELECT n, (n - 1) % 100000 + 1 FROM dbo.Nums  WHERE n <= 100000;SELECT * FROM @T WHERE col1 = 1;SELECT * FROM @T WHERE col1 <= 50000;SELECT * FROM @T WHERE col2 = 1;SELECT * FROM @T WHERE col2 <= 2;SELECT * FROM @T WHERE col2 <= 5000;GO-- Temp Table ExampleSELECT n AS col1, (n - 1) % 100000 + 1 AS col2,  CAST('a' AS CHAR(200)) AS fillerINTO #TFROM dbo.NumsWHERE n <= 100000;ALTER TABLE #T ADD PRIMARY KEY(col1);CREATE UNIQUE INDEX idx_col2_col1 ON #T(col2, col1);GOSELECT * FROM #T WHERE col1 = 1;SELECT * FROM #T WHERE col1 <= 50000;SELECT * FROM #T WHERE col2 = 1;SELECT * FROM #T WHERE col2 <= 2;SELECT * FROM #T WHERE col2 <= 5000;GO-- CleanupDROP TABLE #T;GO----------------------------------------------------------------------- Table Expressions----------------------------------------------------------------------- Return for each Employee, the Row with the Highest OrderIDUSE Northwind;SELECT O.OrderID, O.EmployeeID, O.CustomerID, O.OrderDateFROM dbo.Orders AS O  JOIN (SELECT EmployeeID, MAX(OrderID) AS MaxOid        FROM dbo.Orders        GROUP BY EmployeeID) AS D    ON O.OrderID = D.MaxOid;GO----------------------------------------------------------------------- Summary Exercise - Relational Division----------------------------------------------------------------------- Which customers were handled by the same set of employees?-- Listing 2-1: Code that Creates Realistic Table Sizes for Summary Exercise BenchmarkSET NOCOUNT ON;USE tempdb;GOIF OBJECT_ID('dbo.Orders') IS NOT NULL  DROP TABLE dbo.Orders;GOIF OBJECT_ID('dbo.Customers') IS NOT NULL  DROP TABLE dbo.Customers;GOSELECT n AS CustomerIDINTO dbo.CustomersFROM dbo.NumsWHERE n <= 10000;ALTER TABLE dbo.Customers ADD PRIMARY KEY(CustomerID);SELECT n AS OrderID,  1 + ABS(CHECKSUM(NEWID())) % 10000 AS CustomerID,  1 + ABS(CHECKSUM(NEWID())) % 40    AS EmployeeIDINTO dbo.OrdersFROM dbo.NumsWHERE n <= 1000000;ALTER TABLE dbo.Orders ADD PRIMARY KEY(OrderID);CREATE INDEX idx_cid_eid ON dbo.Orders(CustomerID, EmployeeID);GO-- Solution Based on Subqueries; no Temp TableSELECT CustomerID,    CASE WHEN EXISTS(SELECT * FROM dbo.Orders AS O                     WHERE O.CustomerID = C1.CustomerID)      THEN COALESCE(        (SELECT MIN(C2.CustomerID)           FROM dbo.Customers AS C2           WHERE C2.CustomerID < C1.CustomerID             AND NOT EXISTS               (SELECT * FROM dbo.Orders AS O1                WHERE O1.CustomerID = C1.CustomerID                  AND NOT EXISTS                    (SELECT * FROM dbo.Orders AS O2                     WHERE O2.CustomerID = C2.CustomerID                       AND O2.EmployeeID = O1.EmployeeID))             AND NOT EXISTS               (SELECT * FROM dbo.Orders AS O2                WHERE O2.CustomerID = C2.CustomerID                  AND NOT EXISTS                    (SELECT * FROM dbo.Orders AS O1                     WHERE O1.CustomerID = C1.CustomerID                       AND O1.EmployeeID = O2.EmployeeID))),        CustomerID) END AS GrpFROM dbo.Customers AS C1ORDER BY Grp, CustomerID;GO-- Listing 2-2: Solution Based on Temp TablesSELECT DISTINCT CustomerID, EmployeeIDINTO #CustsEmpsFROM dbo.Orders;CREATE UNIQUE CLUSTERED INDEX idx_cid_eid  ON #CustsEmps(CustomerID, EmployeeID);GOWITH Agg AS(  SELECT CustomerID,    MIN(EmployeeID) AS MN,    MAX(EmployeeID) AS MX,    COUNT(*)        AS CN,    SUM(EmployeeID) AS SM,    CHECKSUM_AGG(EmployeeID) AS CS  FROM #CustsEmps  GROUP BY CustomerID),AggJoin AS(  SELECT A1.CustomerID AS Cust1, A2.CustomerID AS Cust2, A1.CN  FROM Agg AS A1    JOIN Agg AS A2      ON  A2.CustomerID <= A1.CustomerID      AND A2.MN = A1.MN      AND A2.MX = A1.MX      AND A2.CN = A1.CN      AND A2.SM = A1.SM      AND A2.CS = A1.CS),CustGrp AS(  SELECT Cust1, MIN(Cust2) AS Grp  FROM AggJoin AS AJ  WHERE CN = (SELECT COUNT(*)              FROM #CustsEmps AS C1                JOIN #CustsEmps AS C2                  ON C1.CustomerID = AJ.Cust1                  AND C2.CustomerID = AJ.Cust2                  AND C2.EmployeeID = C1.EmployeeID)  GROUP BY Cust1)SELECT CustomerID, GrpFROM dbo.Customers AS C  LEFT OUTER JOIN CustGrp AS G    ON C.CustomerID = G.Cust1ORDER BY Grp, CustomerID;GODROP TABLE #CustsEmps;GO-- Solution Based on Concatenation with XML PATH; no Temp TableWITH CustGroups AS(  SELECT CustomerID,    (SELECT CAST(EmployeeID AS VARCHAR(10)) + ';' AS [text()]     FROM (SELECT DISTINCT EmployeeID           FROM dbo.Orders AS O           WHERE O.CustomerID = C.CustomerID) AS D     ORDER BY EmployeeID     FOR XML PATH('')) AS CustEmps  FROM dbo.Customers AS C)SELECT CustomerID,  CASE WHEN CustEmps IS NULL THEN NULL    ELSE MIN(CustomerID) OVER(PARTITION BY CustEmps) END AS GrpFROM CustGroupsORDER BY Grp, CustomerID;GO

   
   
3. 回滚事务对表变量无效，回滚事务对临时表有效

4. SET NOCOUNT ON;DECLARE @TestTable TABLE (  RowID   INT IDENTITY PRIMARY KEY CLUSTERED,  Name     VARCHAR(9) NOT NULL UNIQUE,  Age     TINYINT NULL,  rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid()));begin tranINSERT INTO @TestTable (Name, Age) VALUES ('Roy', 25),('中国风', 21);rollback tranSELECT 'Insert rows' as Action, * FROM @TestTable;/*ActionRowIDNameAgerowguidInsert rows1Roy2584C4F6CC-3AB3-4D7D-8779-0DCF3414AB61Insert rows2中国风211DB02A77-F20E-4326-A4E4-023CE9F5DEA7*/goif OBJECT_ID('Tempdb..#TestTable') is not nulldrop table #TestTableCREATE TABLE #TestTable (  RowID   INT IDENTITY PRIMARY KEY CLUSTERED,  Name     VARCHAR(9) NOT NULL UNIQUE,  Age     TINYINT NULL,  rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT(newid()));  begin tran INSERT INTO #TestTable (Name,Age) VALUES ('Roy', 25),('中国风', 21);rollback tranSELECT 'Insert rows' as Action,* FROM #TestTable/*无记录*/