临时表和表变量、内联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 Schema
   SET NOCOUNT ON;
   USE tempdb;
   GO
   IF OBJECT_ID('dbo.proc1') IS NOT NULL
     DROP PROC dbo.proc1;
   GO
   IF OBJECT_ID('dbo.proc2') IS NOT NULL
     DROP PROC dbo.proc2;
   GO
   
   CREATE PROC dbo.proc1
   AS
   
   CREATE TABLE #T1(col1 INT NOT NULL);
   INSERT INTO #T1 VALUES(1);
   SELECT * FROM #T1;
   
   EXEC dbo.proc2;
   GO
   
   CREATE PROC dbo.proc2
   AS
   
   CREATE TABLE #T1(col1 INT NULL);
   INSERT INTO #T1 VALUES(2);
   SELECT * FROM #T1;
   GO
   
   -- Execute proc1
   EXEC dbo.proc1;
   GO
   
   -- Alter proc2; T1 and T2 with Different Schemas
   ALTER PROC dbo.proc2
   AS
   
   CREATE TABLE #T1(col1 INT NULL, col2 INT NOT NULL);
   INSERT INTO #T1 VALUES(2, 2);
   SELECT * FROM #T1;
   GO
   
   -- Execute proc1, fails
   EXEC dbo.proc1;
   GO
   
   -- Execute proc2, succeeds
   EXEC dbo.proc2;
   GO
   
   -- Execute proc1, succeeds
   EXEC dbo.proc1;
   GO
   
   ---------------------------------------------------------------------
   -- Schema Changes of Temporary Tables in Dynamic Batches
   ---------------------------------------------------------------------
   
   -- Sample Code
    
   -- Assume @schema and @insert were constructed dynamically
   DECLARE @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 column
   CREATE 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 #T
   
   EXEC('
   ALTER TABLE #T42 ADD ' + @schema + ';
   ALTER TABLE #T42 DROP COLUMN dummycol;
   EXEC(''' + @insert + ''')');
   GO
   
   --	Back in the outer level, access #T in a new batch
   SELECT * FROM #T42;
   
   -- Cleanup
   DROP TABLE #T42;
   GO
   
   ---------------------------------------------------------------------
   -- Global Temporary Tables
   ---------------------------------------------------------------------
   
   -- Termination
   
   -- Connection 1
   CREATE TABLE ##T1(col1 INT);
   INSERT INTO ##T1 VALUES(1);
   
   -- Connection 2
   BEGIN TRAN
     UPDATE ##T1 SET col1 = col1 + 1;
   
   -- Close connection 1
   
   -- Connection 2
     -- Succeeds
     SELECT * FROM ##T1;
   COMMIT
   
   -- Fails
   SELECT * FROM ##T1;
   GO
   
   -- Created in startup procedure
   
   -- Creation Script for sp_Globals Procedure
   USE master;
   GO
   IF OBJECT_ID('dbo.sp_Globals') IS NOT NULL
     DROP PROC dbo.sp_Globals
   GO
   CREATE PROC dbo.sp_Globals
   AS
   
   CREATE TABLE ##Globals
   (
     varname sysname NOT NULL PRIMARY KEY,
     val     SQL_VARIANT NULL
   );
   GO
   
   EXEC dbo.sp_procoption 'sp_Globals', 'startup', 'true';
   GO
   
   -- Restart SQL Server
   
   -- Add a global variable
   SET NOCOUNT ON;
   INSERT INTO ##Globals VALUES('var1', CAST('abc' AS VARCHAR(10)));
   SELECT * FROM ##Globals;
   
   -- Cleanup
   USE master;
   GO
   DROP PROC dbo.sp_Globals;
   DROP TABLE ##Globals;
   GO
   
   ---------------------------------------------------------------------
   -- Table Variables
   ---------------------------------------------------------------------
   
   -- Creating a table variable
   DECLARE @T1 TABLE(col1 INT);
   INSERT @T1 VALUES(1);
   SELECT * FROM @T1;
   GO
   
   ---------------------------------------------------------------------
   -- tempdb
   ---------------------------------------------------------------------
   SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME LIKE '%#%';
   GO
   DECLARE @T TABLE(col1 INT);
   INSERT INTO @T VALUES(1);
   SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME LIKE '%#%';
   GO
   
   ---------------------------------------------------------------------
   -- Statistics
   ---------------------------------------------------------------------
   
   -- Table Variable Example
   DECLARE @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 Example
   SELECT n AS col1, (n - 1) % 100000 + 1 AS col2,
     CAST('a' AS CHAR(200)) AS filler
   INTO #T
   FROM dbo.Nums
   WHERE n <= 100000;
   
   ALTER TABLE #T ADD PRIMARY KEY(col1);
   CREATE UNIQUE INDEX idx_col2_col1 ON #T(col2, col1);
   GO
   
   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
   
   -- Cleanup
   DROP TABLE #T;
   GO
   ---------------------------------------------------------------------
   -- Table Expressions
   ---------------------------------------------------------------------
   
   -- Return for each Employee, the Row with the Highest OrderID
   USE Northwind;
   
   SELECT O.OrderID, O.EmployeeID, O.CustomerID, O.OrderDate
   FROM 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 Benchmark
   SET NOCOUNT ON;
   USE tempdb;
   GO
   
   IF OBJECT_ID('dbo.Orders') IS NOT NULL
     DROP TABLE dbo.Orders;
   GO
   IF OBJECT_ID('dbo.Customers') IS NOT NULL
     DROP TABLE dbo.Customers;
   GO
   
   SELECT n AS CustomerID
   INTO dbo.Customers
   FROM dbo.Nums
   WHERE 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 EmployeeID
   INTO dbo.Orders
   FROM dbo.Nums
   WHERE 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 Table
   SELECT 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 Grp
   FROM dbo.Customers AS C1
   ORDER BY Grp, CustomerID;
   GO
   
   -- Listing 2-2: Solution Based on Temp Tables
   SELECT DISTINCT CustomerID, EmployeeID
   INTO #CustsEmps
   FROM dbo.Orders;
   
   CREATE UNIQUE CLUSTERED INDEX idx_cid_eid
     ON #CustsEmps(CustomerID, EmployeeID);
   GO
   
   WITH 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, Grp
   FROM dbo.Customers AS C
     LEFT OUTER JOIN CustGrp AS G
       ON C.CustomerID = G.Cust1
   ORDER BY Grp, CustomerID;
   GO
   
   DROP TABLE #CustsEmps;
   GO
   
   -- Solution Based on Concatenation with XML PATH; no Temp Table
   WITH 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 Grp
   FROM CustGroups
   ORDER 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 tran
   
   INSERT INTO @TestTable (Name, Age) VALUES ('Roy', 25),('中国风', 21);
   rollback tran
   
   SELECT 'Insert rows' as Action, * FROM @TestTable;
   /*
   Action	RowID	Name	Age	rowguid
   Insert rows	1	Roy	25	84C4F6CC-3AB3-4D7D-8779-0DCF3414AB61
   Insert rows	2	中国风	21	1DB02A77-F20E-4326-A4E4-023CE9F5DEA7
   */
   go
   if OBJECT_ID('Tempdb..#TestTable') is not null
   	drop table #TestTable
   CREATE 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 tran
   SELECT 'Insert rows' as Action,* FROM #TestTable
   /*
   无记录
   */