Summary and review of new features of SQL Server 2008 (1)

Time:2019-9-5

1. Differences between TVP, Table Variables, Temporary Tables and CTE
Both TVP and temporary tables can be indexed. There is always tempdb, which increases the overhead of the system database. Table variables and CTE are written to tempdb only when memory overflows. For those with large amount of data, repeated use and repeated query association, it is recommended to use temporary tables or TVP with small amount of data. It is more appropriate to use table variables or CTE.

2. sql_variant Universal Type
All data types can be stored, equivalent to the object data type in C #.

3. datetime, datetime2, datetimeoffset
Datetime time has a small validity period, which can not be used before 1753-1-1. Its accuracy is in milliseconds, while the datetime 2 data range corresponds to the datetime in C #, and its accuracy reaches 7 decimal places after seconds. Datetime offset is considered as the date type of time zone.

4. Use of MERGE
The grammar is very simple. It is mainly to deal with the operation after comparing some fields of two tables. It is necessary to pay attention to the difference between when not matched (by target) and when not matched by source. The former is for records that do not exist in the target table after comparing, insert operation can be selected, while the latter is for more target tables after comparing. To record, you can select Delete or update operation
5. rowversion type
Instead of the previous timestamp, timestamp, 8-byte binary value, it is often used to solve the problem of concurrent operation.

6. Sysdatetime()
Returns the datetime 2 type with higher accuracy than datetime

7. With cube, with rollup, grouping sets operators
Both can be used after group by. With cube means a combination of all levels. With rollup is a combination of levels. The difference can be seen in detail from the following code. Note that the summary line, null, can be seen as all values
The grouping sets operator, on the other hand, returns only the top-level summary rows of each grouping, which can be judged by grouping (field name) = 1 in the query summary rows. The operator can be used with rollup and cube to represent the union of all result sets processed according to grouping by sets and rollup/cube.
The sample code is as follows:

Copy codeThe code is 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’Fanke TX’,’Red’,’S’, 1
Insert @t select’Fanke TX’,’Black’,’S’, 2
Insert @t select’Fanke TX’,’White’,’L’, 3
Insert @t select’Jingdong Village Mountain’,’White’,’L’, 4
Insert @t select’Jingdong Village Mountain’,’Red’,’S’, 5
Insert @t select’Jingdong Village Mountain’,’Black’,’L’, 6
[email protected] select’Amazon slippers’,’white’,’L’, 7
[email protected] 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’Fanke TX’,’Red’,’S’, 1
Insert @t select’Fanke TX’,’Black’,’S’, 2
Insert @t select’Fanke TX’,’White’,’L’, 3
Insert @t select’Jingdong Village Mountain’,’White’,’L’, 4
Insert @t select’Jingdong Village Mountain’,’Red’,’S’, 5
Insert @t select’Jingdong Village Mountain’,’Black’,’L’, 6
[email protected]t select’Amazon slippers’,’white’,’L’, 7
[email protected] 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 fast grammars such as [email protected] int = 0

Although sometimes quick, DBA does not recommend this, [email protected] = select top 1 ID from the table name, recommending that declarations and lookup table assignments be separated

9. Common expression CTE

Features: It can be nested to replace sub-queries in join tables. The structure level is clearer, and it can also be used for recursive queries. In addition, the recursive level is controlled by ingenious constant columns.

The sample code is as follows:

Copy codeThe code is as follows:
Using CTE

Common expression CTE Common table expression

Realization of Recursive Algorithms with CTE

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)

———————-

Determine which employees report recursive queries 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)

Setting Cascade Association Recursion without Error Reporting
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)

10. pivot and unpivot

The former is used in row and column transitions. Note that aggregation functions must be used with PIVOT to calculate the party without considering any null values in the column of values. Generally, pivot statements can be replaced by sub-queries on the column, but this is inefficient.

The latter is used for column wrapping. Note that if null values are present in some columns, they will be filtered out and no new rows will be generated. The new columns specified before for in the grammar specify the values in the column names for the original tables, and the new columns specified after for the values of the titles in the column names specified by the original tables.

Both have common features: grammatically, aliases must end up; column types specified in IN must be identical.

The sample code is as follows:

Copy codeThe code is as follows:
Pivot and unpivot

About the operation of PIVOT

CREATE TABLE #test
(
NAME VARCHAR(max),
SCORE INT
)

INSERT INTO # test VALUES (‘Zhang San’,’97’)
INSERT INTO # test VALUES (‘Li Si’,’28’)
INSERT INTO # test VALUES (‘Wang Wu’,’33’)
INSERT INTO # test VALUES (‘Man of God’,’78’)

–NAME SCORE
–Zhang San97
Li Si 28
Wang Wu33
– Man of God 78

–row and column
SELECT -‘Report Card’AS SCORENAME,
[Zhang San], [Li Si], [Wang Wu]
FROM #test
PIVOT (AVG (SCORE) FOR NAME IN ([Zhang San], [Li Si], [Wang Wu]) 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 to row

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 Vendor Employee UNPIVOT (ORDERS FOR [operator name] IN ([Emp1Order], [Emp2Order], [Emp3Order], [Emp4Order], [Emp5Order])

Recommended Today

Wondertrader releases version v0.5.0

Today (July 28, 2020), wondertrader released the latest version of v0.5.0, along with the latest version of wtpy, v0.5.0 Wondertrader has made the following changes in v0.5.0: High frequency strategy engine officially released。 High frequency strategy engine beforeWonderTraderHowever, due to insufficient testing, it has not been officially released for the time being. It only focuses […]