背景
一個項目的開發，離不開數據庫的相關操作，表/視圖設計，存儲過程，觸發器等等數據庫對象的操作是非常頻繁的。有時候，我們會查找系統中類似的代碼，然後復制/粘貼進行再進行相應的修改。本文的目的在於歸納、總結sqlserver數據庫的常用操作，並不斷更新。期以備忘！
P1 sql的執行順序
sql語句是操作數據庫的工具，了解sql的執行順序會極大地幫助我們提高我們編寫的sql的執行效率。見以下代碼：
（8）SELECT （9）DISTINCT （11）<Top Num> <select list>
（1）FROM [left_table]
（3）<join_type> JOIN <right_table>
（2）ON <join_condition>
（4）WHERE <where_condition>
（5）GROUP BY <group_by_list>
（6）WITH <CUBE | RollUP>
（7）HAVING <having_condition>
（10）ORDER BY <order_by_list>
FROM:對FROM子句中的前兩個表執行笛卡爾積（Cartesian product）（交叉聯接），生成虛擬表VT1
ON:對VT1應用ON篩選器。只有那些使<join_condition>為真的行才被插入VT2.
OUTER（JOIN）：如 果指定了OUTER JOIN（相對於CROSS JOIN 或（INNER JOIN），保留表（preserved table:左外部聯接把左表標記為保留表，右外部聯接把右表標記為保留表，完全外部聯接把兩個表都標記為保留表）中未找到匹配的行將作為外部行添加到 VT2,生成VT3.如果FROM子句包含兩個以上的表，則對上一個聯接生成的結果表和下一個表重復執行步驟1到步驟3,直到處理完所有的表為止。
WHERE:對VT3應用WHERE篩選器。只有使<where_condition>為true的行才被插入VT4.
GROUP BY:按GROUP BY子句中的列列表對VT4中的行分組，生成VT5.
CUBE|ROLLUP:把超組（Suppergroups）插入VT5,生成VT6.
HAVING:對VT6應用HAVING篩選器。只有使<having_condition>為true的組才會被插入VT7.
SELECT:處理SELECT列表，產生VT8.
DISTINCT:將重復的行從VT8中移除，產生VT9.
ORDER BY:將VT9中的行按ORDER BY 子句中的列列表排序，生成游標（VC10）。
TOP:從VC10的開始處選擇指定數量或比例的行，生成表VT11,並返回調用者。
總的來說，select的列是最後一步被執行的，而From的Table是首先被執行的。
P2 創建帶Try…Catch的存儲過程模板
Copy下面的代碼，然後新建查詢，就可以寫sql語句，執行完後，一個你自己的存儲過程就建立好了！
USE [DB]--設定對應的數據庫
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:
-- DESCRIBE:
-- =============================================
CREATE PROCEDURE [dbo].[UP_InsertJHBData] --存儲過程名
（
@CustomerName VARCHAR（50） --參數
）
AS
BEGIN
SET NOCOUNT ON --提高性能的，必須要有
DECLARE @Now DATETIME
SET @Now = GETDATE（） --所有操作保證統一時間
BEGIN TRY
--在這裡寫SQL
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR（4000） ;
DECLARE @ErrorSeverity INT ;
DECLARE @ErrorState INT ;
SELECT @ErrorMessage = ERROR_MESSAGE（） ,
@ErrorSeverity = ERROR_SEVERITY（） ,
@ErrorState = ERROR_STATE（） ;
PRINT @ErrorMessage
RAISERROR（@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
） ;
RETURN -1 ;
END CATCH
END
P3 創建帶事務的存儲過程模板
只是將帶Try…Catch的存儲過程的模板中加入了事務的控制，使用類似
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- AUTHOR:
-- DESCRIBE:
-- =============================================
CREATE PROCEDURE [dbo].[UP_InsertJHBData]--存儲過程名
--參數
（
@CustomerName VARCHAR（50）
）
--參數
AS
BEGIN
SET NOCOUNT ON ;--提高性能的，必須要有
DECLARE @Now DATETIME ;
SET @Now = GETDATE（） ;--所有操作保證統一時間
BEGIN TRY
BEGIN TRANSACTION myTrans ;--開始事務
--在這裡寫SQL
COMMIT TRANSACTION myTrans ;--事務提交語句
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION myTrans-- 始終回滾事務
--拋出異常
DECLARE @ErrorMessage NVARCHAR（4000） ;
DECLARE @ErrorSeverity INT ;
DECLARE @ErrorState INT ;
SELECT @ErrorMessage = ERROR_MESSAGE（） ,
@ErrorSeverity = ERROR_SEVERITY（） ,
@ErrorState = ERROR_STATE（） ;
RAISERROR（@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
） ;
END CATCH
END
P8 分組數據集並返回每個組的前n條記錄
Row_NUMBER（）函數用於生成行號；利用PARTITION BY可以將結果集按照指定需求進行分組；最終使用一個簡單的子查詢就能夠獲取每組的前3條數據
SELECT *
FROM （ SELECT ROW_NUMBER（） OVER （ PARTITION BY ProductNO ORDER BY ProductNO ） AS RowNum ,
*
FROM IM.dbo.ItemInfo
） t
WHERE t.RowNum IN （ 1, 2, 3 ）
P9 【用戶自定義表類型】的使用
您是否碰到過這樣的需求：調用存儲過程的時候傳一張表進去？？？
在sqlserver數據庫中有一種稱為【用戶自定義表類型】的數據結構，類似表，存儲過程的參數可以定義為【用戶自定義表類型】，代碼調用時可以直接傳入一個List<T>,而存儲過程調用時可以直接傳入一個表變量。
以下代碼實現了在IM數據庫中新建一個名稱為GCRP_PendingGiftCard_TYPE的用戶自定義表結構：
USE [IM]
GO
/****** Object: UserDefinedTableType [dbo].[GCRP_PendingGiftCard_TYPE] Script Date: 04/08/2014 14:56:16 ******/
CREATE TYPE [dbo].[GCRP_PendingGiftCard_TYPE] AS TABLE（
[RowNum] [int] NULL,
[GiftCardNO] [varchar]（500） NULL,
[UsedDate] [datetime] NULL,
[CustomerName] [varchar]（50） NULL,
[ReduceAmount] [decimal]（18, 2） NULL,
[Amount] [decimal]（18, 2） NULL
）
GO
下面的代碼演示了【用戶自定義表類型】的使用方式和場景（使用起來和臨時表、表變量類似）
--1 聲明一個自定義表類型@T_PendingGiftCard
DECLARE @T_PendingGiftCard GCRP_PendingGiftCard_TYPE
--2 執行一個存儲過程，並把返回的結果集插入到上面聲明的自定義表類型@T_PendingGiftCard中
INSERT INTO @T_PendingGiftCard
EXEC IM.dbo.UP_GCRP_GetEntireGfitCardWithReduceAmount
--3 聲明一個自定義表類型@T_PendingSO
DECLARE @T_PendingSO GCRP_PendingSO_TYPE
--4 執行一個存儲過程，傳入表類型@T_PendingGiftCard,並把返回的結果集插入自定義表類型@T_PendingSO中
INSERT INTO @T_PendingSO
EXEC IM.dbo.UP_GCRP_GetEntireSOWithGiftCardUsed
@T_PendingGiftCard
下面貼出這兩個存儲過程的源碼，供大家參考
View Code
USE [IM]
GO
/****** Object: StoredProcedure [dbo].[UP_GCRP_GetEntireSOWithGiftCardUsed] Script Date: 04/08/2014 15:02:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: DeanZhou
-- Create date: 2014-04-04
-- Description: 獲取禮品卡報表數據-獲取使用禮品卡的訂單信息
-- =============================================
ALTER PROCEDURE [dbo].[UP_GCRP_GetEntireSOWithGiftCardUsed]
（
@T_PendingGiftCard GCRP_PendingGiftCard_TYPE READONLY
）
AS
BEGIN
SET NOCOUNT ON ;--提高性能的，必須要有
--訂單狀態：1 待審核 2 待支付 3 已支付，待確認 4 已支付 5 出庫中 6 已發貨 7 已收貨 8 換貨中 9 退貨中
-- 10 已換貨 11 已退貨 12 退款成功 13 換貨被拒絕 14 退貨被拒絕 15 交易成功 16 訂單已關閉 17 客戶作廢
-- 18 商城作廢 19 系統自動作廢 20 退款中 21 等待團購成功 22 組團失敗
SELECT S.SONO , -- 訂單編號 VARCHAR（30）
S.EwalletDiscountAmount , -- 電子錢包支付金額 DECIMAL（18,2）
S.RefundAmount , -- 發生退款的總金額 DECIMAL（18,2）
（ S.EwalletDiscountAmount + S.RefundAmount ） AS RealUsedAmount , -- 實際使用電子錢包的金額 DECIMAL（18,2）
0 AS RelatedRefundID ,
S.CustomerName , -- 客戶名稱 VARCHAR（50）
S.SaleOrderStatus , -- 訂單狀態 INT
S.CreateDate -- 下單日期 DATETIME
INTO #T_PendingSO
FROM （ SELECT A.SONO ,
A.EwalletDiscountAmount ,
（ SELECT -ISNULL（SUM（B.Amount）， 0）
FROM RMA.dbo.RefundRecord B
WHERE B.SONO = A.SONO
AND B.RefundType = 3
AND B.Status IN （ 2, 3, 4 ）
） AS RefundAmount ,
CONVERT（DECIMAL（18, 2）， 0） AS RealUsedAmount ,
A.CustomerName ,
A.SaleOrderStatus ,
A.CreateDate
FROM SO.dbo.SOMaster A
WHERE EwalletDiscountAmount > 0
AND SaleOrderStatus NOT IN （ 1, 2, 17, 19 ）
AND EXISTS （ SELECT 1
FROM @T_PendingGiftCard C
WHERE A.CustomerName = C.CustomerName
AND A.CreateDate > C.UsedDate ）
AND NOT EXISTS （ SELECT 1
FROM IM.dbo.Temp_UsedGiftCardReportDetail B
WHERE A.SONO = B.SONO
AND B.UsedAmount > 0 ）
） S
DELETE IM.dbo.Temp_UsedGiftCardReportDetail WHERE UsedAmount <= 0 AND EXISTS （SELECT 1 FROM #T_PendingSO B WHERE Temp_UsedGiftCardReportDetail.SONO = B.SONO ）
SELECT ROW_NUMBER（） OVER （ ORDER BY S.CustomerName, S.SONO, S.CreateDate ASC ） AS RowNum ,
S.*
FROM （ SELECT *
FROM #T_PendingSO
UNION ALL
SELECT A.SONO ,
B.EwalletDiscountAmount ,
-A.Amount AS RefundAmount ,
B.RealUsedAmount ,
A.RelatedRefundID ,
B.CustomerName ,
A.Status ,
A.CreateDate
FROM RMA.dbo.RefundRecord A
INNER JOIN #T_PendingSO B ON A.SONO = B.SONO
WHERE A.RefundType = 3
AND A.Status IN （ 2, 3, 4 ）
UNION ALL
SELECT A.SONO ,
0 AS EwalletDiscountAmount ,
-A.Amount AS RefundAmount ,
B.UsedAmount ,
A.RelatedRefundID ,
B.CustomerName ,
A.Status ,
A.CreateDate
FROM RMA.dbo.RefundRecord A
INNER JOIN IM.dbo.Temp_UsedGiftCardReportDetail B ON A.SONO = B.SONO AND B.UsedAmount > 0
WHERE A.RefundType = 3
AND A.Status IN （ 2, 3, 4 ）
） S
END
