SQL Server 技巧随手记

目录 | 索引

SSMS查询结果带表头
VALUES的妙用
行转列与列转行(PIVOT与UNPIVOT)
查找某字段的所有引用(表、存储过程、试图等)
顺序GUID

一、SSMS查询结果带表头

拷贝到Excel中

二、VALUES的妙用

1. INSERT INTO VALUES

--Create table Employee
CREATE TABLE [dbo].[Employee] (
    [EmployeeNo] INT PRIMARY KEY,
    [EmployeeName] [nvarchar](50) NULL,
    [CreateUser] [nvarchar](50) NULL,
    [CreateDatetime] [datetime] NULL
);

--Insert
INSERT INTO Employee(EmployeeNo, EmployeeName, CreateUser, CreateDatetime)
VALUES 
('1', 'Employee 1', 'System', GETDATE()),
('2', 'Employee 2', 'System', GETDATE()),
('3', 'Employee 3', 'System', GETDATE())

--Select
SELECT * FROM Employee

2. SELECT FROM VALUES

SELECT * FROM 
(VALUES 
('1', 'Employee 1', 'System', GETDATE()),
('2', 'Employee 2', 'System', GETDATE()),
('3', 'Employee 3', 'System', GETDATE())
) Employee(EmployeeNo, EmployeeName, CreateUser, CreateDatetime)

3. INERT INTO DEFAULT VALUES GO

新建测试表

CREATE TABLE Employee (
    EmployeeNo INT IDENTITY,
    EmployeeName NVARCHAR(100),
    CreateUser NVARCHAR(100) DEFAULT 'System',
    CreateDatetime DATETIME DEFAULT GETDATE()
)
GO

用Default Values插入测试数据(8笔)

INSERT INTO Employee DEFAULT VALUES;
GO 8

查询结果

SELECT * FROM Employee

三、行转列与列转行(PIVOT与UNPIVOT)

1. 行转列

新建测试表和测试数据

CREATE TABLE StudentScore (
    UserName NVARCHAR(50),
    Course NVARCHAR (50),
    Score FLOAT
)

INSERT INTO StudentScore(UserName, Course, Score)
VALUES
('Helen', '语文', 100),
('Helen', '数学', 90),
('Helen', '英语', 85),
('Helen', '物理', 80),
('Jack', '语文', 75),
('Jack', '数学', 90),
('Jack', '英语', 85),
('Jack', '物理', 100)

SELECT * FROM StudentScore

传统行转列的实现方法如下

SELECT UserName, 
      MAX(CASE Course WHEN '语文' THEN Score ELSE 0 END) AS '语文',
      MAX(CASE Course WHEN '数学' THEN Score ELSE 0 END) AS '数学',
      MAX(CASE Course WHEN '英语' THEN Score ELSE 0 END) AS '英语',
      MAX(CASE Course WHEN '物理' THEN Score ELSE 0 END) AS '物理'
FROM StudentScore
GROUP BY UserName

使用PIVOT数据透视的方法实现行转列

SELECT UserName, 语文, 数学, 英语, 物理
FROM StudentScore
PIVOT
(
    SUM(Score) FOR Course IN (语文, 数学, 英语, 物理)
) T

--使用 * 也可以
SELECT *
FROM StudentScore
PIVOT
(
    SUM(Score) FOR Course IN (语文, 数学, 英语, 物理)
) T

注意：在PIVOT运算符的圆括号内要指定：聚合函数（本例为SUM）、聚合元素（Score）、扩展元素（Course）以及目标列名称的列表（语文, 数学, 英语, 物理）。

对于PIVOT运算符有个重要的地方需要注意：不需为它显式地指定分组元素，也就不许要在查询中使用GROUP BY字句。PIVOT运算符隐式地把源表（或表表达式）中既没有指定为扩展元素，也没有指定为聚合元素的那些元素作为分组元素。所以在使用PIVOT运算符时，须要保证PIVOT运算符的源表除了分组、扩展和聚合元素以外，不能再包含其它属性（列）。以便在指定了扩展元素和聚合元素以后，剩下的属性全部都是欲指定为分组元素的属性。为此，一般不直接把PIVOT运算符应用到源表（本例为StudentScore表），而是将其应用到一个表表达式（改表表达式只包含透视转换需要的3中元素，不包含其他属性）。

2. 列转行

新建测试表与测试数据

CREATE TABLE StudentScore (
    [UserName] NVARCHAR(50),
    [语文] FLOAT,
    [数学] FLOAT,
    [英语] FLOAT,
    [物理] FLOAT
)

INSERT INTO StudentScore(UserName, [语文], [数学], [英语], [物理])
VALUES
('Helen', 100, 90, 85, 80),
('Jack', 75, 90, 85, 100)

SELECT * FROM StudentScore

传统列转行方法 (UNION ALL与GROUP BY)

SELECT UserName, '语文' AS Course, 
        MAX([语文]) AS Score 
FROM StudentScore 
GROUP BY UserName 
UNION ALL
SELECT UserName, '数学' AS Course, 
        MAX([数学]) AS Score 
FROM StudentScore 
GROUP BY UserName 
UNION ALL
SELECT UserName, '英语' AS Course, 
        MAX([英语]) AS Score 
FROM StudentScore 
GROUP BY UserName 
UNION ALL
SELECT UserName, '物理' AS Course, 
        MAX([物理]) AS Score 
FROM StudentScore 
GROUP BY UserName

使用UNPIVOT实现列转行

SELECT UserName, Score, Course
FROM StudentScore
UNPIVOT
(
    Score FOR Course IN (语文, 数学, 英语, 物理)
) T

--也可以使用 *
SELECT *
FROM StudentScore
UNPIVOT
(
    Score FOR Course IN (语文, 数学, 英语, 物理)
) T

四、查找某字段的所有引用(表、存储过程、试图等)

　　下面的SQL语句摘抄自网上 (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75273)，感觉有瑕疵，至少对索引和索引的包含列不起作用。不过貌似Red Gate的SQL Search工具好像也无法找到包含字段的索引，看样子要找出所有的引用还是比较困难的，如果有哪位童鞋有更好的办法请不吝赐教。

DECLARE 
  @string varchar(1000),
  @ShowReferences char(1)

SET @string = 'searchstring' --> searchstring

SET @ShowReferences = 'N'
/****************************************************************************/
/*                                                                          */
/* TITLE:   sp_FindReferences                                               */
/*                                                                          */
/* DATE:    18 February, 2004                                               */
/*                                                                          */
/* AUTHOR:  WILLIAM MCEVOY                                                  */
/*                                                                          */
/****************************************************************************/
/*                                                                          */
/* DESCRIPTION:  SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */
/*                                                                          */
/****************************************************************************/
set nocount on

declare @errnum         int         ,
        @errors         char(1)     ,
        @rowcnt         int         ,
        @output         varchar(255)

select  @errnum         = 0         ,
        @errors         = 'N'       ,
        @rowcnt         = 0         ,
        @output         = ''        

/****************************************************************************/
/* INPUT DATA VALIDATION                                                    */
/****************************************************************************/


/****************************************************************************/
/* M A I N   P R O C E S S I N G                                            */
/****************************************************************************/

-- Create temp table to hold results
DECLARE @Results table
(
  Name        varchar(55),
  Type        varchar(12),
  DateCreated datetime,
  ProcLine    varchar(4000)
)


IF (@ShowReferences = 'N')
BEGIN
  insert into @Results
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
    join syscomments SC on SC.id = SO.id
   where SC.text like '%' + @string + '%'
  union
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
   where SO.name like '%' + @string + '%'
  union
  select distinct
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         ''
    from sysobjects  SO
    join syscolumns SC on SC.id = SO.ID
   where SC.name like '%' + @string + '%'
   order by 2,1
END
ELSE
BEGIN
  insert into @Results
  select 
         'Name'      = convert(varchar(55),SO.name),
         'Type'      = SO.type,
         crdate,
         'Proc Line' = text
    from sysobjects  SO
    join syscomments SC on SC.id = SO.id
   where SC.text like '%' + @string + '%'
  union
  select 
         'Name'      = convert(varchar(55),SO.name),
         'Type'      = SO.type,
         crdate,
         'Proc Line' = ''
    from sysobjects  SO
   where SO.name like '%' + @string + '%'
  union
  select 
         'Name' = convert(varchar(55),SO.name),
         'Type' = SO.type,
         crdate,
         'Proc Line' = ''
    from sysobjects  SO
    join syscolumns SC on SC.id = SO.ID
   where SC.name like '%' + @string + '%'
   order by 2,1
END


IF (@ShowReferences = 'N')
BEGIN
  select Name,
         'Type' = Case (Type)
                    when 'P'  then 'Procedure'
                    when 'TR' then 'Trigger'
                    when 'X'  then 'Xtended Proc'
                    when 'U'  then 'Table'
                    when 'C'  then 'Check Constraint'
                    when 'D'  then 'Default'
                    when 'F'  then 'Foreign Key'
                    when 'K'  then 'Primary Key'
                    when 'V'  then 'View'
                    else Type
                  end,
         DateCreated
    from @Results
    order by 2,1
END
ELSE
BEGIN
  select Name,
         'Type' = Case (Type)
                    when 'P'  then 'Procedure'
                    when 'TR' then 'Trigger'
                    when 'X'  then 'Xtended Proc'
                    when 'U'  then 'Table'
                    when 'C'  then 'Check Constraint'
                    when 'D'  then 'Default'
                    when 'F'  then 'Foreign Key'
                    when 'K'  then 'Primary Key'
                    when 'V'  then 'View'
                    else Type
                  end,
         DateCreated,
         ProcLine
    from @Results
    order by 2,1
END

五、顺序GUID

　　在设计表主键时，我们一般有两种选择：

自增键 INT/BIGINT

　　优势

占用空间小（4/8字节）　
顺序性：减少叶级别索引的页争用
性能高：前两点的优势自然性能比较高
可读性
返回生成的主键值

- 　　劣势
GUID

　　优势
- 唯一性：不论是合并表的时候，还是高并发，都能保证唯一性
- 不可猜测性：因为是随机生成的，根本不可能猜测到
　　劣势

占用空间大：16字节
可读性差
性能低
随机性：增加叶级别索引的页争用

　　上面提到当用GUID作为主键时，由于其随机性，导致增加页级别索引的页争用。其实我们也可以创建顺序性的GUID - NEWSEQUENTIALID()

CREATE TABLE GUID_NEWSEQUENTIALID
(
    ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL DEFAULT NEWSEQUENTIALID(),
    NUMBER INT,
    CREATE_USER NVARCHAR(50),
    CREATE_DATE_TIME DATETIME DEFAULT GETDATE()
)

　　注意：NEWSEQUENTIALID() 只能与 uniqueidentifier 类型表列上的 DEFAULT 约束一起使用，不能在查询中使用

SELECT NEWSEQUENTIALID()

　　报错信息如下

　　下面我们来检验一下看看插入的是不是具有顺序性（循环插入10000条测试记录，注意ID使用Default值）

DECLARE @NUM INT
SET @NUM = 0
WHILE(@NUM < 100000)
BEGIN
    INSERT INTO GUID_NEWSEQUENTIALID(NUMBER,CREATE_USER)
    VALUES(@NUM,'SHG.CPAN')
    SET @NUM = @NUM + 1
END

　　检索一下看看（确实按顺序）

SELECT * FROM GUID_NEWSEQUENTIALID ORDER BY ID

注意：使用 NEWSEQUENTIALID() 生成的每个 GUID 在该计算机上都是唯一的。仅当源计算机具有网卡时，使用 NEWSEQUENTIALID() 生成的 GUID 在多台计算机上才是唯一的。

在启动 Windows 后在指定计算机上创建大于先前通过该函数生成的任何 GUID 的 GUID。在重新启动 Windows 后，GUID 可以再次从一个较低的范围开始，但仍是全局唯一的。在 GUID 列用作行标识符时，使用 NEWSEQUENTIALID 可能比使用 NEWID 函数的速度更快。其原因在于，NEWID 函数导致随机行为并且使用更少的缓存数据页。使用 NEWSEQUENTIALID 还有助于完全填充数据和索引页。（https://msdn.microsoft.com/zh-cn/library/ms189786(v=sql.120).aspx）

NEWSEQUENTIALID 是对 Windows UuidCreateSequential 函数的包装

下面的代码展示如何在C#中生成顺序性的GUID（参考链接：http://stackoverflow.com/questions/211498/is-there-a-net-equalent-to-sql-servers-newsequentialid）

[DllImport("rpcrt4.dll", SetLastError=true)]
static extern int UuidCreateSequential(out Guid guid);