SSMS操作資料表欄位效能問題

使用Microsoft SQL Server Management Studio(本文之後都簡稱為SSMS)圖形介面操作帶給我們許多方便，但有些時候應該直接以T-SQL語法下達，以下是以資料表新增欄位來說明效能問題

原始資料表schema

CREATE TABLE [dbo].[tbl]
(
    [col1] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
    [col2] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
) ON [PRIMARY]

我們針對上述的資料表tbl新增欄位col3，分別以SSMS和T-SQL操作並以SQL Server Profiler錄製以了解背後運作過程

SSMS操作畫面


SSMS錄製結果

CREATE TABLE dbo.Tmp_tbl
(
    col1 nvarchar(50) NOT NULL,
    col2 nvarchar(50) NOT NULL,
    col3 nvarchar(50) NOT NULL
)  ON [PRIMARY]

IF EXISTS(SELECT * FROM dbo.tbl)
    EXEC('INSERT INTO dbo.Tmp_tbl (col1, col2)
        SELECT col1, col2 FROM dbo.tbl WITH (HOLDLOCK TABLOCKX)')

DROP TABLE dbo.tbl

EXECUTE sp_rename N'dbo.Tmp_tbl', N'tbl', 'OBJECT'

T-SQL操作畫面


T-SQL錄製結果

alter table tbl
    add col3 nvarchar(50) not null

由上面的結果我們可以發現利用SSMS新增欄位時會執行四個步驟
1.建立以Tmp_開頭的資料表
2.將原本的資料表資料寫入Tmp_資料表
3.drop原本資料表
4.將Tmp_資料表改名

如果要新增欄位的資料表本身含有大量資料，此時就不適合利用SSMS應該改以直接下達T-SQL以減輕系統負載，另外變更資料表欄位類型也跟新增欄位執行相同的步驟。

SQL 2005 PIVOT多欄彙總

微軟的SQL Server產品在SQL 2005開始就支援PIVOT，但從線上叢書通常看到PIVOT範例只有一個彙總欄位，以下的語法是利用SQL 2005的範例資料庫AdventureWorks，呈現2001-07-01到2001-07-07銷售明細產品每日的訂購數加總及產品每日的銷售加總

SELECT

ProductName,

SUM(ISNULL([1_Q], 0)) [1_Q], SUM(ISNULL([2_Q], 0)) [2_Q], SUM(ISNULL([3_Q], 0)) [3_Q], SUM(ISNULL([4_Q], 0)) [4_Q], SUM(ISNULL([5_Q], 0)) [5_Q], SUM(ISNULL([6_Q], 0)) [6_Q], SUM(ISNULL([7_Q], 0)) [7_Q],

SUM(ISNULL([1_A], 0)) [1_A], SUM(ISNULL([2_A], 0)) [2_A], SUM(ISNULL([3_A], 0)) [3_A], SUM(ISNULL([4_A], 0)) [4_A], SUM(ISNULL([5_A], 0)) [5_A], SUM(ISNULL([6_A], 0)) [6_A], SUM(ISNULL([7_A], 0)) [7_A]

FROM

(

SELECT

p.Name AS ProductName,

SUM(d.OrderQty) AS OrderQty,

SUM(d.LineTotal) AS LineTotal,

CAST(DAY(d.ModifiedDate) AS VARCHAR) + '_Q' AS D1,

CAST(DAY(d.ModifiedDate) AS VARCHAR) + '_A'AS D2

FROM Sales.SalesOrderDetail AS d

INNER JOIN Production.Product AS p ON d.ProductID = p.ProductID

WHERE d.ModifiedDate BETWEEN '2001-07-01' AND '2001-07-07'

GROUP BY p.Name, d.ModifiedDate

) p

PIVOT

(

SUM (OrderQty)

FOR D1 IN

( [1_Q], [2_Q], [3_Q], [4_Q], [5_Q], [6_Q], [7_Q])

) AS pvt1

PIVOT

(

SUM(LineTotal)

FOR D2 IN

( [1_A], [2_A], [3_A], [4_A], [5_A], [6_A], [7_A])

) AS pvt2

GROUP BY ProductName

上面的語法有用到一些小技巧，因為要求出每日的訂購數及銷售加總，所以日期欄位需要二個，一個給訂購數一個給銷售加總，這裡我們用D1代表訂購數的日期D2代表銷售加總的日期，另外SELECT清單需要14個欄位，其中7個欄位代表1~7日的訂購數，其餘7個欄位代表1~7日的銷售加總數，為了有所區別在訂購數的日期後加上_Q如1_Q，銷售加總日期後加上_A如1_A，最後注意在SUM函數裡要用ISNULL避免加總時因為有值為NULL造成結果為NULL。

Tech.Days 2010 SQL Server 2008 R2 T-SQL心得

今年Tech.Days有參加楊志強老師SQL 2008 R2 T-SQL技術與建議的課程，覺得獲益良多，因此想寫入以加深印象，以下是 Where Clause使用 Like 子句與 Left的差別

測試環境
Cpu:T4200
Ram:2G
OS:winxp sp3
DB:Microsoft SQL Server 2005 Developer Edition

測試資料表為一訂單主檔資料表，筆數為122798

以下語法為找出2006年9月的訂單

SELECT IssNum
FROM IssMaster
WHERE IssNum LIKE '200609%'

SELECT IssNum
FROM IssMaster
WHERE LEFT(IssNum, 6) = '200609'

可以看出採用 Like子句的成本花費較Left函數來得小，原因在於Like是採用clustered index seek而Left函數會採用index scan，這裡要注意的是如果Like由 '200609%'改為'%200609%'則會變成index scan，相關資料可由google鍵入index seek index scan得到

*延伸參考

利用T-SQL去除字串最後一個逗號

DECLARE @str varchar(20)
SET @str = 'A,B,C,D,E,'

SELECT @str

--將字串反轉
SET @str = REVERSE(@str)
SELECT @str

--去除逗號
SET @str = CASE WHEN CHARINDEX(',', @str) = 1 THEN STUFF(@str, 1, 1, '') ELSE @str END
SELECT @str

--將字串反轉
SET @str = REVERSE(@str)
SELECT @str

那上面的語法與一般我們利用SUBSTRING(@str, 1, LEN(@str) - 1)有什麼不同?我們利用上面的語法時我們可以不用考慮字串變數的值(如NULL)及長度，改用SUBSTRING(@str, 1, LEN(@str) - 1)時要注意字串變數的長度另外還是要判斷最後一碼是否為逗號。如果讓筆者選擇利用T-SQL或程式來去除最後一個逗號，筆者會傾向程式。

T-SQL切割字串另類方法

在SQL Server中並沒有提供類似Split的函數，所以在做字串切割時得自己撰寫自定函數或在Stored Procedure裡撰寫相關T-SQL語法，在這裡介紹一個SQL內建的函數PARSENAME，這個函數原本是提供傳回物件名稱的指定部份。物件的可擷取部份有物件名稱、擁有者名稱、資料庫名稱和伺服器名稱。底下是擷取SQL 2005的線上叢書的程式片段
USE AdventureWorks;
SELECT PARSENAME('AdventureWorks..Contact', 1) AS 'Object Name';
SELECT PARSENAME('AdventureWorks..Contact', 2) AS 'Schema Name';
SELECT PARSENAME('AdventureWorks..Contact', 3) AS 'Database Name;'
SELECT PARSENAME('AdventureWorks..Contact', 4) AS 'Server Name';
GO

結果集
Object Name
------------------------------
Contact

(1 row(s) affected)

Schema Name
------------------------------
(null)

(1 row(s) affected)

Database Name
------------------------------
AdventureWorks

(1 row(s) affected)

Server Name
------------------------------
(null)

(1 row(s) affected)

這個函數可接受的參數有二個，第一個參數是由四個piece所組成，piece與piece之間以.為連結符號，第二個參數是指要取得第幾個piece。現在我們將這個函數引用到其它的地方，下面的T-SQL片段，主要是將IP位地做切割
DECLARE @IP_Address VARCHAR(15)
SET @IP_Address = '192.168.0.1'
SELECT PARSENAME(@IP_Address, 4) AS piece4
SELECT PARSENAME(@IP_Address, 3) AS piece3
SELECT PARSENAME(@IP_Address, 2) AS piece2
SELECT PARSENAME(@IP_Address, 1) AS piece1

結果集
piece4
------------------------------
192

(1 row(s) affected)

結果集
piece3
------------------------------
168

(1 row(s) affected)

結果集
piece2
------------------------------
0

(1 row(s) affected)

結果集
piece1
------------------------------
1

(1 row(s) affected)

使用PARSENAME有幾個限制，第一個是參數1的組成必需小於等於四個piece，也就是說一個piece也行，第二個是參數2的值必需介於1~4的整數，所以當你的字串是由非常多的piece所組成可能就得自己寫自定函數，更詳細的資料請參考SQL2005線上叢書或sqlteam。

Stored Procedure中為流水編號補0

在系統開發中有時需要產生一些流水編號，例如格式為yyyymmnnnn(2009060001)，通常在產生新的流水編號前，我們會利用SELECT語法得出某個期間內的最後一個流水編號，以上面的格式來說，如果沒有200906開頭的流水編號，表示流水編號由1開始，相反地如果有則把流水編號加1，在這裡我只針對流水號補0來說明，底下有二段程式片段，第一段是一般寫法，第二段則是比較精簡的寫法
一般寫法
IF (LEN(@num) = 1)
BEGIN
　　　SET @num = '000' + @num
END
ELSE IF (LEN(@num) = 2)
BEGIN
　　　SET @num = '00' + @num
END
ELSE IF (LEN(@num) = 3)
BEGIN
　　　SET @num = '0' + @num
END

精簡寫法
SET @num = RIGHT(('0000' + @num), 4)

上面的寫法只是提供另一個思維，真正在撰寫時可能要考慮更多東西(例如資料型態的轉型)