sqlserver常用知識點備忘錄

 背景

　　一個項目的開發，離不開數據庫的相關操作，表/視圖設計，存儲過程，觸發器等等數據庫對象的操作是非常頻繁的。有時候，我們會查找系統中類似的代碼，然後復制/粘貼進行再進行相應的修改。本文的目的在於歸納、總結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]  &nbs