背景
一个项目的开发，离不开数据库的相关操作，表/视图设计，存储过程，触发器等等数据库对象的操作是非常频繁的。有时候，我们会查找系统中类似的代码，然后复制/粘贴进行再进行相应的修改。本文的目的在于归纳、总结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
【sqlserver常用知识点备忘录】相关文章：
★ sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享
★ SqlServer 2005中使用row_number()在一个查询中删除重复记录