1. TVP, table variable, temporary table, CTE difference
Both TVP and temporary tables are indexed, always in tempdb and increase system database overhead, while table variables and CTE are written to tempdb only when memory overflows. For a large amount of data, and repeated use, repeated query association, the proposed use of temporary tables or TVP, small amount of data, using table variables or CTE more appropriate
2. Sql_variant Universal type
Can hold all data types, equivalent to the object data type in C #
3. DateTime, DateTime2, DateTimeOffset
DateTime time is small, not available before 1753-1-1, the precision is millisecond, and datetime2 data range equivalent to the DateTime in C #, precision reached 7 digits after the second decimal point, DateTimeOffset is to consider the date type of the time zone
4. Use of the merge
The grammar is very simple does not say, mainly handles two tables some fields contrast the operation, should notice when does not matched (by target) and when does matched by source difference, the former is for compares after the target table does not exist the record, may choose Inse RT operations, while the latter is for records that are more than the target table after the comparison, you can select the Delete or update operation
5. RowVersion type
Replace the previous timestamp, timestamp, 8 byte binary value, commonly used to solve the problem of concurrent operations
6. Sysdatetime ()
Returns the DateTime2 type with a higher precision than datetime
7. With cube, with rollup, grouping sets operator
Can be used with the group by, the with cube represents a combination of all levels, with rollup is summarized by level, and the difference can be seen in detail from the following code. Note that the total row, NULL can be considered as all values
Instead of the grouping sets operator, only the top-level summary row for each group is returned, and the Grouping (field name) = one can be used in the query rollup row to determine that the operator is available with the rollup, the cube, in accordance with grouping by sets and according to rollup/ Cube-processed result set UNION ALL
The sample code is as follows:
Copy Code code as follows:
With Cube, with rollup
--Sample code
Declare @t table (Goodsname VARCHAR (max), Sku1name VARCHAR (max), Sku2name VARCHAR (max), qty INT)
Insert @t Select ' van tx ', ' Red ', ' S ', 1
Insert @t Select ' van tx ', ' Black ', ' S ', 2
Insert @t Select ' van tx ', ' White ', ' L ', 3
Insert @t select ' Jing Dong cun shan ', ' white ', ' L ', 4
Insert @t select ' Jing Dong cun shan ', ' Red ', ' S ', 5
Insert @t select ' Jing Dong cun shan ', ' black ', ' L ', 6
Insert @t Select ' Amazon slippers ', ' white ', ' L ', 7
Insert @t Select ' Amazon slippers ', ' red ', ' S ', 8
SELECT * from @t
Select Goodsname,sku1name,sku2name,sum (qty) sumqty
From @t
GROUP BY Goodsname,sku1name,sku2name with rollup
ORDER BY Goodsname,sku1name,sku2name
Select Goodsname,sku1name,sku2name,sum (qty) sumqty
From @t
Group by Goodsname,sku1name,sku2name with Cube
ORDER BY Goodsname,sku1name,sku2name
-----------------------
Declare @t table (Goodsname VARCHAR (max), Sku1name VARCHAR (max), Sku2name VARCHAR (max), qty INT)
Insert @t Select ' van tx ', ' Red ', ' S ', 1
Insert @t Select ' van tx ', ' Black ', ' S ', 2
Insert @t Select ' van tx ', ' White ', ' L ', 3
Insert @t select ' Jing Dong cun shan ', ' white ', ' L ', 4
Insert @t select ' Jing Dong cun shan ', ' Red ', ' S ', 5
Insert @t select ' Jing Dong cun shan ', ' black ', ' L ', 6
Insert @t Select ' Amazon slippers ', ' white ', ' L ', 7
Insert @t Select ' Amazon slippers ', ' red ', ' S ', 8
--grouping SETS operator
SELECT goodsname,sku1name,sku2name, SUM (qty) from @t GROUP by GROUPING SETS (goodsname,sku1name,sku2name)
SELECT Goodsname, Sku1name, Sku2name, SUM (qty) from @t
GROUP by GROUPING SETS (Goodsname), ROLLUP (Sku1name,sku2name)
ORDER BY Goodsname,sku1name,sku2name
SELECT Goodsname, Sku1name, Sku2name, SUM (qty) from @t
GROUP by ROLLUP (Goodsname,sku1name,sku2name)
ORDER BY Goodsname,sku1name,sku2name
SELECT case when GROUPING (goodsname) = 1 THEN ' [all] ' ELSE goodsname end Goodsname,
Case when GROUPING (sku1name) = 1 THEN ' [all] ' ELSE sku1name end Sku1name,
Case when GROUPING (sku2name) = 1 THEN ' [all] ' ELSE sku2name end Sku2name, SUM (qty) from @t
GROUP by GROUPING SETS (Goodsname), ROLLUP (Sku1name,sku2name)
ORDER BY Goodsname,sku1name,sku2name
8. Some quick syntax such as Declare @id int = 0
Although it is sometimes quick, the DBA does not recommend this, Declare @id = SELECT top 1 id from table name, recommendation Declaration and table look-up Assignment
9. Common Expression CTE
Features: can be nested use, instead of the join table in the subquery, the structure is more clear, but also can be used to recursive query, in addition, through the clever constant column control recursive hierarchy
The sample code is as follows:
Copy Code code as follows:
Using a CTE
--Common expression CTE Common table expression
--using CTE to realize recursive algorithm
CREATE TABLE Employeetree (
EMPLOYEE INT PRIMARY KEY,
EmployeeName nvarchar (50),
ReportsTo int
)
INSERT into Employeetree values (1, ' Richard ', null)
INSERT into Employeetree values (2, ' Stephen ', 1)
INSERT into Employeetree values (3, ' Clemens ', 2)
INSERT into Employeetree values (4, ' Malek ', 2)
INSERT into Employeetree values (5, ' Goksin ', 4)
INSERT into Employeetree values (6, ' Kimberly ', 1)
INSERT into Employeetree values (7, ' Ramesh ', 5)
----------------------
--a recursive query that determines which employees report to Stephen
With Employeetemp as
(
Select Employee, EmployeeName, ReportsTo from employeetree where EMPLOYEE = 2
UNION ALL
Select A.employee, A.employeename, A.reportsto from Employeetree as a
INNER JOIN employeetemp as B on a.reportsto = B.employee
)
SELECT * from Employeetemp where EMPLOYEE <> 2--option (maxrecursion 2)
--No Error setting Cascade Association recursion
With Employeetemp as
(
Select Employee, EmployeeName, reportsto,0 as Sublevel from employeetree where EMPLOYEE = 2
UNION ALL
Select A.employee, A.employeename, a.reportsto,sublevel+1 from Employeetree as a
INNER JOIN employeetemp as B on a.reportsto = B.employee
)
SELECT * from Employeetemp where EMPLOYEE <> 2 and Sublevel <=2--option (maxrecursion 2)
Pivot and Unpivot
The former is done in rows, note: You must use aggregate functions with pivot, calculate the party without considering any null values that appear in the Value column; In general, you can replace the pivot statement with a subquery on the column, but this is inefficient
The latter is used in column careers, note: If there is a null value in some columns, it will be filtered out, no new rows are generated, the new column specified before syntax for, corresponding to the value in the column name specified in the original table, and the new column specified for the value of the header in the column name specified in the original table.
There is a common denominator: there must be an alias in the grammar, and the column type specified in inside must be consistent.
The sample code is as follows:
Copy Code code as follows:
Pivot and Unpivot
--about the operation of pivot
CREATE TABLE #test
(
NAME VARCHAR (max),
SCORE INT
)
INSERT into #test VALUES (' John ', ' 97 ')
INSERT into #test VALUES (' Dick ', ' 28 ')
INSERT into #test VALUES (' Harry ', ' 33 ')
INSERT into #test VALUES (' Man of God ', ' 78 ')
--name SCORE
--Zhang 397
--Lee 428
--Wang 533
--Man of God 78
--row column
SELECT--' transcript ' as Scorename,
[John], [Dick], [Harry]
From #test
PIVOT (AVG (SCORE) for NAME in ([John], [Dick], [Harry])) b
-----------------------------------------
CREATE TABLE Vendoremployee (
VendorID INT,
Emp1order INT,
Emp2order INT,
Emp3order INT,
Emp4order INT,
Emp5order INT,
)
Go
INSERT into Vendoremployee VALUES (1,4,3,5,4,4)
INSERT into Vendoremployee VALUES (2,4,1,5,5,5)
INSERT into Vendoremployee VALUES (3,4,3,5,4,4)
INSERT into Vendoremployee VALUES (4,4,2,5,4,4)
INSERT into Vendoremployee VALUES (5,5,1,5,5,5)
SELECT * from Vendoremployee
----------------
--Column Careers
SELECT * FROM (
SELECT Vendorid,[emp1order],[emp2order],[emp3order],[emp4order],[emp5order] from Vendoremployee) as Unpiv
Unpivot (Orders for Elyid in ([Emp1order],[emp2order],[emp3order],[emp4order],[emp5order])) as child
ORDER BY Elyid
SELECT * from Vendoremployee
Unpivot (Orders for Elyid in ([Emp1order],[emp2order],[emp3order],[emp4order],[emp5order])) as child
ORDER BY Elyid
SELECT * from Vendoremployee Unpivot (ORDERS for [operator name] in ([Emp1order],[emp2order],[emp3order],[emp4order],[emp5order ]))
