ms sql语句模板

create table

USE dbname
GO
/*================================================================================  
Server:    ?  
DataBase:  ?  
Author:    ?
Object:    ? 
Version:   1.0  
Date:      ??/??/????
Content:   ?
----------------------------------------------------------------------------------  
Modified history:      
      
Date        Modified by    VER    Description      
------------------------------------------------------------  
??/??/????  ??			   1.0    Create.  
================================================================================*/  

/* Policies by DBA Team 
	--BYDBA 1 不建议使用联合主键，而是用自增列配合"创建唯一约束"或"创建唯一索引"
	--BYDBA 1.主键规范命名为：PK_表名或PK_表名_主键字段名
	--BYDBA 1 xml/varhcar(max)/nvarchar(max)这三种类型的列，DBA 建议存储在独立的表中，否则会产生很大的性能问题
	--BYDBA 1 char类型字段，需要预估是否包含多国字符，如果是，请使用nchar
	--BYDBA 1 Money类型是不允许使用的，请用Decimal(12,2)代替
*/

CREATE Table [dbo].[Table]
(
	TransactionNumber		INT IDENTITY(1,1)	NOT NULL,
	Field1					NCHAR(10)			NOT NULL,
	Field2  				NCHAR(3)			NOT NULL CONSTRAINT DF_Table_Field2 DEFAULT ('USA'),--no forget to define the constraint name of default
	Field3					INT					NOT NULL CONSTRAINT DF_Table_Field3 DEFAULT (1),
	Field4					INT					NOT NULL,
	CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED --Also can be 'PK_Table_TransactionNumber'
	(
		TransactionNumber ASC
	)
) ON [PRIMARY]
GO

--How to create standard nonclustered index 
--创建普通索引
CREATE NONCLUSTERED INDEX IX_Table_Field1 ON dbo.Table 
(
	[Field1]
)WITH (FILLFACTOR = 90)
Go

--How to create unique index
--创建唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [IXU_Table_Code1_Code2] ON dbo.[Table]
(
	[Code1],[Code2]
)WITH (FILLFACTOR=80) ON [PRIMARY]

游标

/*  
    定义游标时，如果不是特别需要，使用LOCAL关键显式的将游标定义为局部游标，  
    尽量避免使用全局（GLOBAL，这是数据库的默认行为）游标；没有特殊需要的话，  
    尽量使用FORWARD_ONLY READ_ONLY STATIC游标,   
    --For example:*/  
    DECLARE your_cursor CURSOR   
        LOCAL   
        STATIC   
        FORWARD_ONLY   
        READ_ONLY   
    FOR    
        SELECT …  
  
    OPEN your_cursor        
    FETCH NEXT FROM cur INTO @your_variable   
    WHILE @@fetch_status = 0        
      BEGIN        
    --Put your code here to use @your_variable  
  
        FETCH NEXT FROM cur INTO @your_variable   
      END    
    CLOSE your_cursor        
    DEALLOCATE your_cursor 

事务：

CREATE PROC procedure_name
AS
SET NOCOUNT ON;
-- ========================================
-- TRY...CATCH 中的标准事务处理模块 - 1
-- 当前的事务信息
DECLARE
	@__$tran_count int,
	@__$tran_name_save varchar(32),
	@__$tran_count_save int
;
SELECT
	@__$tran_count = @@TRANCOUNT,
	@__$tran_name_save = '__$save_'
						+ CONVERT(varchar(11), ISNULL(@@PROCID, -1))
						+ '.'
						+ CONVERT(varchar(11), ISNULL(@@NESTLEVEL, -1)),
	@__$tran_count_save = 0
;

-- TRY...CATCH 处理
BEGIN TRY;
	-- ========================================
	-- 不需要事务处理的 T-SQL 批处理

	-- ========================================
	-- TRY...CATCH 中的标准事务处理模块 - 2
	-- 需要事务处理的 T-SQL 批处理
	-- ----------------------------------------
	-- 2.1 开启事务, 或者设置事务保存点
	IF @__$tran_count = 0
		BEGIN TRAN;
	ELSE
	BEGIN;
		SAVE TRAN @__$tran_name_save;
		SET @__$tran_count_save = @__$tran_count_save + 1;
	END;

	-- ----------------------------------------
	-- 这里放置处于事务中的各种处理语句

	-- ----------------------------------------
	-- 2.2 提交 / 回滚事务
	-- 2.2.1 提交事务
	--       有可提交的事务, 并且事务是在当前模块中开启的情况下, 才提交事务
	IF XACT_STATE() = 1 AND @__$tran_count = 0
		COMMIT;

	/* -- 2.2.2 回滚事务
	IF XACT_STATE() <> 0
	BEGIN;
		IF @__$tran_count = 0
			ROLLBACK TRAN;
		-- XACT_STATE 为 -1 时, 不能回滚到事务保存点, 这种情况留给外层调用者做统一的事务回滚
		ELSE IF XACT_STATE() = 1
		BEGIN;
			IF @__$tran_count_save > 0
			BEGIN;
				ROLLBACK TRAN @__$tran_name_save;
				SET @__$tran_count_save = @__$tran_count_save - 1;
			END;
		END;
	END;
	-- -------------------------------------- */
	-- ========================================

lb_Return:
	-- ========================================
	-- TRY...CATCH 中的标准事务处理模块 - 3
	-- 如果需要防止 TRY 中有遗漏的事务处理, 则可在 TRY 模块的结束部分做最终的事务处理
	IF @__$tran_count = 0
	BEGIN;
		IF XACT_STATE() = -1
			ROLLBACK TRAN;
		ELSE
		BEGIN;
			WHILE @@TRANCOUNT > 0
				COMMIT TRAN;
		END;
	END;
END TRY
BEGIN CATCH
	-- ========================================
	-- TRY...CATCH 中的标准事务处理模块 - 4
	-- 在 CATCH 模块中的事务回滚处理
	IF XACT_STATE() <> 0
	BEGIN;
		IF @__$tran_count = 0
			ROLLBACK TRAN;
		-- XACT_STATE 为 -1 时, 不能回滚到事务保存点, 这种情况留给外层调用者做统一的事务回滚
		ELSE IF XACT_STATE() = 1
		BEGIN;
			WHILE @__$tran_count_save > 0
			BEGIN;
				ROLLBACK TRAN @__$tran_name_save;
				SET @__$tran_count_save = @__$tran_count_save - 1;
			END;
		END;
	END;

	-- ========================================
	-- TRY...CATCH 中的标准事务处理模块 - 5
	-- 错误消息处理
	-- ----------------------------------------
	-- 5.1 获取错误信息
	--     这提提取了错误相关的全部信息, 可以根据实际需要调整
	DECLARE
		@__$error_number int,
		@__$error_message nvarchar(2048),
		@__$error_severity int,
		@__$error_state int,
		@__$error_line int,
		@__$error_procedure nvarchar(126),
		@__$user_name nvarchar(128),
		@__$host_name nvarchar(128)
	;
	SELECT
		@__$error_number = ERROR_NUMBER(),
		@__$error_message = ERROR_MESSAGE(),
		@__$error_severity = ERROR_SEVERITY(),
		@__$error_state = ERROR_STATE(),
		@__$error_line = ERROR_LINE(),
		@__$error_procedure = ERROR_PROCEDURE(),
		@__$user_name = SUSER_SNAME(),
		@__$host_name = HOST_NAME()
	;

	-- ----------------------------------------
	-- 5.2 对于重要的业务处理存储过程, 应该考虑把错误记录到表中备查(这个表需要先建立)
	--     记录错误应该在没有事务的情况下进行了, 否则可能因为外层事务的影响导致保存失败
	IF XACT_STATE() = 0   
		INSERT dbo.tb_ErrorLog(
			error_number,
			error_message,
			error_severity,
			error_state,
			error_line,
			error_procedure,
			user_name,
			host_name,
			indate
		)
		VALUES(
			@__$error_number,
			@__$error_message,
			@__$error_severity,
			@__$error_state,
			@__$error_line,
			@__$error_procedure,
			@__$user_name,
			@__$host_name,
			GETDATE()
		);

	-- ----------------------------------------
	-- 5.3 如果没有打算在 CATCH 模块中对错误进行处理, 则应该抛出错误给调用者
	/*-- 注:
			不允许在被 SSB 调用的存储过程中, 将错误或者其他信息抛出
			因为 SSB 是自动工作的, 如果它调用的存储过程有抛出信息, 则这个信息会被直接记录到 SQL Server 系统日志
			而目前 SSB 的消息数量是很多的, 这会导致 SQL Server 日志爆涨掉
			对于被 SSB 调用的存储过程, 应该在 CATCH 模块中加入自己的错误处理(最简单的就是将错误记录到表中)
	-- */
	RAISERROR(
		N'User: %s, Host: %s, Procedure: %s, Error %d, Level %d, State %d, Line %d, Message: %s ',
		@__$error_severity, 
		1,
		@__$user_name,
		@__$host_name,
		@__$error_procedure,
		@__$error_number,
		@__$error_severity,
		@__$error_state,
		@__$error_line,
		@__$error_message
	);
END CATCH;
GO