SqlServer 创建存储过程（Create Procedure）注意事项

临时表

- 使用临时表方法

1. 创建后使用

CREATE TABLE #TEMPDealUIDsData(
    　　　　ID   int IDENTITY (1,1)     not null, 　　--创建列ID,并且每次新增一条记录就会加1
    　　　　DealUID int,   
    　　　　primary key (ID)      　　　　　　　　　--定义ID为临时表#Tmp的主键      
　　　　);

2. 直接使用

SELECT *
INTO #TEM
FROM Deals D

- 删除临时表

DROP TABLE #TEM 

和表值函数对比

比表值函数多了可以动态执行语句的功能，也就是说可以使用insert、update语句。

数据插入和删除效率问题

1. 数据插入PK
1.1. 循环插入，执行时间为38026毫秒
--循环插入
SET STATISTICS TIME ON;
DECLARE @Index INT = 1;
DECLARE @Timer DATETIME = GETDATE();

WHILE @Index <= 100000
BEGIN
    INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_' + CAST(@Index AS CHAR(6)), 'system', GETDATE());
    SET @Index = @Index + 1;
END

SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;

1.2.   事务循环插入，执行时间为6640毫秒
--事务循环
BEGIN TRAN;
SET STATISTICS TIME ON;
DECLARE @Index INT = 1;
DECLARE @Timer DATETIME = GETDATE();

WHILE @Index <= 100000
BEGIN
    INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime) VALUES(@Index, 'Employee_' + CAST(@Index AS CHAR(6)), 'system', GETDATE());
    SET @Index = @Index + 1;
END

SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;

COMMIT;

1.3.   批量插入，执行时间为220毫秒
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();

INSERT [dbo].[Employee](EmployeeNo, EmployeeName, CreateUser, CreateDatetime)
SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJECT_ID]), 'Employee_', 'system', GETDATE()
FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2
ORDER BY C1.[OBJECT_ID]

SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;

1.4.   CTE插入，执行时间也为220毫秒
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();

;WITH CTE(EmployeeNo, EmployeeName, CreateUser, CreateDatetime) AS(
    SELECT TOP(100000) EmployeeNo = ROW_NUMBER() OVER (ORDER BY C1.[OBJECT_ID]), 'Employee_', 'system', GETDATE()
    FROM SYS.COLUMNS AS C1 CROSS JOIN SYS.COLUMNS AS C2
    ORDER BY C1.[OBJECT_ID]
)
INSERT [dbo].[Employee] SELECT EmployeeNo, EmployeeName, CreateUser, CreateDatetime FROM CTE;

SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;

• 插入:
  CTE=批量插入>事务插入>直接全部插入速度；

• 单循环插入速度最慢是由于INSERT每次都有日志，事务插入大大减少了写入日志次数，批量插入只有一次日志，CTE的基础是CLR，善用速度是最快的。

• 删除：

2.  数据删除PK
2.1.   循环删除，执行时间为1240毫秒
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();

DELETE FROM [dbo].[Employee];

SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;
2.2.  批量删除，执行时间为106毫秒
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();

SET ROWCOUNT 100000;

WHILE 1 = 1
BEGIN
    BEGIN TRAN
    DELETE FROM [dbo].[Employee];
    COMMIT
    IF @@ROWCOUNT = 0
        BREAK;
END

SET ROWCOUNT 0;

SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;
2.3.  TRUNCATE删除，执行时间为0毫秒
SET STATISTICS TIME ON;
DECLARE @Timer DATETIME = GETDATE();

TRUNCATE TABLE [dbo].[Employee];

SELECT DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)];
SET STATISTICS TIME OFF;

TRUNCATE>批量删除(delete)>直接全部删除（delete）

事务

示例:

if 1!=1
begin --类似于{
	BEGIN TRAN  --事务开始
	DECLARE @SQLD NVARCHAR(500)
	SET @SQLD='DELETE FROM enstep.'+@TableName+' WHERE ModelScenarioUID='+@CurrentScenarioUIDD
	EXEC(@SQLD)
	COMMIT--事务结束
end--类似于}