SqlServer数据库日常操作手册

添加字段 alter table PM_ChangeColor add Status nvarchar(50) default '申请中'; 修改字段类型 Alter table PM_AfterDyePlan Alter column BlowingRate nvarchar(50) 删除字段 alter table PM_DyePlan drop column SolidWashingMethod 添加索引 CREATE NONCLUSTERED INDEX [index_PM_ChangeColor_Status] ON PM_ChangeColor ( [Status] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 唯一索引 CREATE UNIQUE NONCLUSTERED INDEX [UQ_PM_ChangeColor_Status] ON PM_ChangeColor ( [Status] ) ON [PRIMARY] 联合主键 --先设置非空约束 Alter table PM_BatchNoColorNoRemark alter column sMaterialLot nvarchar(50) not null Alter table PM_BatchNoColorNoRemark alter column utmColorGUID uniqueidentifier not null --增加主键 Alter table PM_BatchNoColorNoRemark add constraint pk_PM_BatchNoColorNoRemark primary key(sMaterialLot, utmColorGUID) 分组排名 select A.upbWorkingProcedureGUID,A.CreateDate,RANK() over(partition by A.upbWorkingProcedureGUID order by A.CreateDate) SortCode from PM_WorkPlanLog A 分组排序 select A.upbWorkingProcedureGUID,A.CreateDate,row_number() over(partition by A.upbWorkingProcedureGUID order by A.CreateDate) SortCode from PM_WorkPlanLog A 存储过程事务代码块 BEGIN TRY---------------------开始捕捉异常 BEGIN TRAN------------------开始事务 --程序逻辑 COMMIT TRAN -------提交事务 END TRY-----------结束捕捉异常 BEGIN CATCH------------有异常被捕获 IF @@TRANCOUNT > 0---------------判断有没有事务 BEGIN ROLLBACK TRAN----------回滚事务 END -----------执行存储过程将错误信息记录在表当中 END CATCH--------结束异常处理 跨库查询（优化版） 添加字段 alter table PM_ChangeColor add Status nvarch