一些 SQL Server 的小技巧

记得备份！备份！备份！

以下我会记录一些我在实际工作中遇到的或者看到别人记录下的关于 SQL Server 的用法和小技巧。文章会不定期的更新。

临时表结构的创建

1
2


-- 可以用于构建临时表的表结构
select top 0 a, b, c into #d from e

临时表的一些优化方式

临时表可以添加索引或者直接增加主键或复合主键来提升性能。

判断表是否为空

1
2


-- 可以用来判断表里还有没有数据
select 1 from a

大批量数据的生产环境更新

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59


-- 建立表
select top(0) sod.SalesOrderID,
              sod.SalesOrderDetailID,
              sod.ProductID,
              sod.UnitPrice
Into #SalesOrderNeedUpdate
From Sales.SalesOrderDetail as sod;

alter table #SalesOrderNeedUpdate add constraint PK_#SalesOrderNeedUpdate PRIMARY KEY (SalesOrderDetailID, SalesOrderID)

set identity_insert #SalesOrderNeedUpdate on

-- 初始化表数据
insert into #SalesOrderNeedUpdate(SalesOrderID, SalesOrderDetailID, ProductID, UnitPrice)
select sod.SalesOrderID,
       sod.SalesOrderDetailID,
       sod.ProductID,
       0
from Sales.SalesOrderDetail as sod;

-- 更新数据
update #SalesOrderNeedUpdate
set UnitPrice = p.ListPrice
from #SalesOrderNeedUpdate as sonu
join Production.Product as p on p.ProductID = sonu.ProductID

-- 创建分页数据临时表
select top 0 sod.SalesOrderID,
             sod.SalesOrderDetailID,
             sod.UnitPrice
into #SalesOrderPagedData
from Sales.SalesOrderDetail as sod

-- set identity_insert #SalesOrderPagedData on

while exists(select 1 from #SalesOrderNeedUpdate)
begin
    -- 插入分页数据表
    insert into #SalesOrderPagedData(SalesOrderID, SalesOrderDetailID, UnitPrice)
    select top 3000 sonu.SalesOrderID,
                    sonu.SalesOrderDetailID,
                    sonu.UnitPrice
    from #SalesOrderNeedUpdate as sonu

    -- 更新实际表
    update sod
    set sod.UnitPrice = sopd.UnitPrice
    from Sales.SalesOrderDetail as sod
    join #SalesOrderPagedData as sopd
    on sod.SalesOrderDetailID = sopd.SalesOrderDetailID and
       sod.SalesOrderID = sopd.SalesOrderID

    -- 在总的更新数据表中删除已更新的数据
    delete sonu
    from #SalesOrderNeedUpdate as sonu
    join #SalesOrderPagedData as sopd
    on sopd.SalesOrderDetailID = sonu.SalesOrderDetailID and
       sopd.SalesOrderID = sonu.SalesOrderID
end

清空表数据

当需要清空整表数据时，推荐使用 TRUNCATE 语句，因为 DELETE 语句会为每条删除的语句建立 log，所以直接用 TRUNCATE 会更快。对于 I/O 有好处，对于磁盘空间也有好处。

合并大批量数据的字段

在一些业务中，会遇到需要合并字段的需求。当然，也可以考虑由程序端来进行。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


-- 传统做法
DECLARE @pOrderNum VARCHAR(MAX) = ''

select @pOrderNum += soh.PurchaseOrderNumber + ','
FROM Sales.SalesOrderHeader as soh
where soh.PurchaseOrderNumber is not null;

select @pOrderNum;

-- 改进做法
DECLARE @pOrderNum VARCHAR(MAX) = ''

SET @pOrderNum = (
    select soh.PurchaseOrderNumber + ','
    FROM Sales.SalesOrderHeader as soh
    where soh.PurchaseOrderNumber is not null
FOR xml path('')
)

select @pOrderNum

具体查看： FOR XML 子句的应用

通过打开 set statistics io on 来看内存相关的信息，同时 set statistics cpu on 来看 CPU 的执行时间。

旧方法

Old way to merge column

新方法

New way to merge column

SQLCMD

可以使用 SQLCMD 来快速的执行一些 SQL 的操作。

1

sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d AdventureWorks2019 -Q "select top 1 * from Person.Person"

批量更新数据

BULK INSERT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- Use BCP to export data
-- 1. Export format file
bcp AdventureWorks2019.Production.Product format nul -f 'Desktop\Product.fmt' -S localhost -U sa -P "yourStrong(!)Password" -c

-- 2. Export data
bcp "select top 10 * from Production.Product" queryout "Desktop\Product.dat" -S localhost -U sa -P "yourStrong(!)Password" -d AdventureWorks2019 -f "Desktop\Product.fmt"

-- BULK INSERT
BULK INSERT Production.Product
FROM 'C:\Users\Sharry.xu\Desktop\Product.dat'
WITH (formatfile = 'C:\Users\Sharry.xu\Desktop\Product.fmt')

INSERT SELECT

1
2
3
4
5
6
7
8
9


CREATE TABLE #Temp
(
    ProductID     int identity primary key,
    ProductNumber nvarchar(25) not null
)

INSERT INTO #Temp(ProductNumber)
SELECT TOP 10 ProductNumber
FROM Production.Product

SELECT INTO

1
2
3


SELECT TOP 10 ProductNumber
INTO #Temp
FROM Production.Product

必须导入一个并不存在的表中

OUTPUT

1
2
3
4


UPDATE Person.Address
SET AddressLine1 = '5725 Glaze Drive Road'
OUTPUT inserted.AddressLine1, deleted.AddressLine1
WHERE AddressID = 26

当被修改的目标表中有触发器时，不能使用。

MERGE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


SELECT *
INTO #SalesOrderHeaderSalesReason
FROM Sales.SalesOrderHeaderSalesReason    

MERGE Sales.SalesOrderHeaderSalesReason AS target
USING #SalesOrderHeaderSalesReason AS source
ON target.SalesOrderID = source.SalesOrderID AND target.SalesReasonID = source.SalesReasonID
    WHEN MATCHED THEN
    UPDATE SET ModifiedDate = getdate()
    WHEN NOT MATCHED THEN
    INSERT (salesorderid, salesreasonid, modifieddate) VALUES (source.SalesOrderID, source.SalesReasonID, getdate());

快速查询所有相关表

假设你希望一次性查询所有相关表的数据，可以使用以下的 SQL 语句来一次性生成查询需要使用的语句。

1
2
3


SELECT 'select top 100 ''' + name + ''', * from [' + name + ']'
FROM sys.tables
WHERE name LIKE '%ACH%'