--
--4.4.1节示例
--
--使用新增T-SQL语法
CREATE TABLE #temp
(
c1 INT,
c2 VARCHAR(50)
)
DECLARE @i INT =2 --定义变量并赋值
INSERT INTO #temp VALUES (1,'a'),(@i,'b') --一次插入多个值
UPDATE #temp SET c1+=1 --累加运算
SELECT * FROM #temp
--使用GROUP BY和UNION ALL查询
SELECT customerType,Null as TerritoryID,MAX(ModifiedDate)
FROM Sales.Customer GROUP BY customerType
UNION ALL
SELECT Null as customerType,TerritoryID,MAX(ModifiedDate)
FROM Sales.Customer GROUP BY TerritoryID
ORDER BY TerritoryID
--使用GROUPING SETS实现同样功能
SELECT customerType,TerritoryID,MAX(ModifiedDate)
FROM Sales.Customer
GROUP BY GROUPING SETS ((customerType), (TerritoryID))
ORDER BY TerritoryID
--
--4.4.3节示例
--
--创建稀疏列
CREATE TABLE t1
(
c1 INT IDENTITY PRIMARY KEY,
c2 VARCHAR(20) SPARSE NULL , --定义为稀疏列
c3 NVARCHAR(10) SPARSE NULL--定义为稀疏列
)
--
--4.4.4节示例
--
--创建稀疏列和列集
CREATE TABLE t2
(
c1 INT IDENTITY PRIMARY KEY,
c2 VARCHAR(20) SPARSE NULL ,
c3 NVARCHAR(10) SPARSE NULL,
c4 xml column_set FOR ALL_SPARSE_COLUMNS --列集
)
--添加数据并查询列集
INSERT INTO t2(c2,c3)
VALUES('a','aa'),('b',null),(null,'cc')
GO
SELECT c1,c4 FROM t2
--通过列集插入和更新数据
INSERT INTO t2(c4)
VALUES('<c2>d</c2>')
UPDATE t2 SET c4='<c3>aa</c3>'
WHERE c1=1
GO
SELECT c1,c2,c3 FROM t2
--
--4.4.5节示例
--
--创建筛选索引
CREATE NONCLUSTERED INDEX IX_t2_c2
ON t2(c2)
WHERE c2 IS NOT NULL --筛选条件
