sql server 2008 新特性

1.同时插入多行，

insert into a values(2,'C'),(3,'D'),(4,'E')；

or 可套查询，如：

insert into a values((select top 1 id from b),(select top 1 name from c))

2.

定义表变量，定义完以后在整个DB中有效，且是持久的，如此可以在存储过程，函数中作为参数传递。

IF (SELECT COUNT(*) FROM sys.table_types
WHERE name = 'TVPParam' AND schema_id = 1) = 0
  CREATE TYPE dbo.TVPParam AS TABLE(ProdCode integer, Qty integer)
GO

3.

以后可以不再使 用union all 来组合查询了。

SELECT * FROM (VALUES('John Smith', 25, 5),('Jane Dow', 36, 6))
 mytable(name, age, score)
GO

4.合并两表结构相同的表，不同也可以，但是意义不大。

MERGE Customers C
  USING QuarterlyUpdate Q
  ON C.customer_id =Q.customer_id
  WHEN MATCHED THEN 
   UPDATE SET 
    C.customer_name = Q.customer_name,
    C.policy_type = Q.policy_type,
    C.deductable = Q.deductable,
    C.number_of_claims += new_claims
  WHEN NOT MATCHED BY SOURCE THEN
    DELETE
  WHEN NOT MATCHED BY TARGET THEN
    INSERT VALUES(customer_name, policy_type, deductable, new_claims)
    OUTPUT $action,
      INSERTED.customer_id,
      INSERTED.customer_name AS [New Name],
      INSERTED.policy_type AS [New Policy],
      INSERTED.deductable AS [New Deductable],
      INSERTED.number_of_claims AS [New Number Of Claims],
      DELETED.customer_id AS [Original Customer Id],
      DELETED.customer_name AS [Original Name],
      DELETED.policy_type AS [Original Policy],
      DELETED.deductable AS [Original Deductable],
      DELETED.number_of_claims AS [Original Number Of Claims];
GO

按多组一次执行分组。

CREATE TABLE T ( a int, b int, c int, x int ) GO

INSERT T VALUES(1,2,3,8)
INSERT T VALUES(1,2,4,1)
INSERT T VALUES(1,2,5,5)
INSERT T VALUES(1,3,4,9)
GO

SELECT a, b, c, SUM(x) AS sumx FROM T GROUP BY GROUPING SETS (a, (b), (a, b, c)) GO

a    b    c    sumx
1    2    3    8
1    2    4    1
1    2    5    5
N    2    N    14
1    3    4    9
N    3    N    9
1    N    N    23

其效果与下面传统的写法相同，但是效率会提高。

SELECT a, NULL AS b, NULL AS c, SUM(x) AS sumx FROM T
GROUP BY a
UNION ALL
SELECT NULL AS a, b, NULL AS c, SUM(x) AS sumx FROM T
GROUP BY b
UNION ALL
SELECT a, b, c, SUM(x) AS sumx FROM T
GROUP BY a, b, c
GO

下面查询列出所有的组合，cube 解释为立方体。

SELECT a, b, c, SUM(x)
FROM T
GROUP BY a, b, c
WITH CUBE