--sql 事务BEGIN TRAN 事物名IF(@@ERROR<>0)BEGIN ROLLBACK TRAN  事物名;RETURN;ENDCOMMIT TRAN  事物名--数据库清缓存DBCC DROPCLEANBUFFERS--忽略自增列 按照sql语句的id新增SET IDENTITY_INSERT [dbo].[EduMenuFunction] ON--插入语句SET IDENTITY_INSERT [dbo].[EduMenuFunction] OFF--修改语句联表修改UPDATE    S_GB_StudentBasicInfoSET BH =ci.BHfrom S_GB_StudentBasicInfo sbi  WITH(NOLOCK),C_GB_ClassInfo ci WITH(NOLOCK)where sbi.BJID=CONVERT(nvarchar(20),ci.ID)UPDATE    ASET A.c2 =B.c3from A inner join B on A.c1=B.c1--创建索引 CREATE INDEX DataReportIndex  ON S_GB_StudentBasicInfo_DataReport(SFZJH (ASC), XB (ASC), CSRQ (ASC), DataYear (ASC), STATUS (ASC))--修改字段默认值alter table 表名 drop constraint 约束名字   ------说明：删除表的字段的原有约束alter table 表名 add constraint 约束名字 DEFAULT 默认值 for 字段名称 -------说明：添加一个表的字段的约束并指定默认值--修改字段名：alter table 表名 rename column A to B--修改字段类型：alter table 表名 alter column UnitPrice decimal(18, 4) not null --增加字段：alter table 表名 ADD 字段 类型 NOT NULL Default 0--添加字段 [CardRecord][Birthday]IF NOT EXISTS( SELECT * from syscolumns where [id]=object_id(‘CardRecord‘) AND [name]=‘Birthday‘ ) BEGINAlter Table CardRecord Add Birthday DATETIME NOT NULL DEFAULT(‘1900-01-01 00:00:00‘) ;EXEC sp_addextendedproperty ‘MS_Description‘, ‘出生日期‘, ‘user‘, dbo, ‘table‘,CardRecord, ‘column‘, Birthday; END  --清除表数据，自增列从1开始 truncate table 表名;--查询所有的子节点with cte as(select * from Base_Company where Id = ‘E7F5395E-6D11-4490-B3AD-513574268CF6‘ --查询节点union allselect a.*from Base_Company a join cte b on a.ParentId = b.idwhere a.id is not null)select * from cte--创建数据库USE mastercreate database AdayMS  on  primary  -- 默认就属于primary文件组,可省略 ( /*--数据文件的具体描述--*/     name=‘AdayMS_data‘,  -- 主数据文件的逻辑名称     filename=‘E:\数据库\jaday\AdayMS\AdayMS_data.mdf‘, -- 主数据文件的物理名称     size=5mb, --主数据文件的初始大小     maxsize=100mb, -- 主数据文件增长的最大值     filegrowth=15%--主数据文件的增长率 ) log on ( /*--日志文件的具体描述,各参数含义同上--*/     name=‘AdayMS_log‘,     filename=‘E:\数据库\jaday\AdayMS\AdayMS_log.ldf‘,     size=2mb,     filegrowth=1mb )--根据时间筛选datediff(dd,时间字段,当前时间)>=0--获取新增数据的标识列insert语句;select @@IDENTITY--获取数据库表和视图中的所有字段SELECT 列序号 = A.COLORDER, 列名 = A.NAME, --主键 = CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE= ‘PK ‘ AND PARENT_OBJ=A.ID AND NAME IN ( --SELECT NAME FROM SYSINDEXES WHERE INDID IN( --SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID))) THEN ‘√ ‘ ELSE ‘ ‘ END, 类型 = B.NAME, --允许空 = CASE WHEN A.ISNULLABLE=1 THEN ‘√ ‘ELSE ‘ ‘ END, 列说明 = ISNULL(G.[VALUE], ‘ ‘) FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE= ‘U ‘ AND D.NAME <> ‘DTPROPERTIES ‘ LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id LEFT JOIN sys.extended_properties F ON D.ID=F.major_id AND F.minor_id=0WHERE (D.name=‘@TableName‘ )ORDER BY A.ID,A.COLORDER--视图SELECT 列序号 = A.COLORDER, 列名 = A.NAME, --主键 = CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE= ‘PK ‘ AND PARENT_OBJ=A.ID AND NAME IN ( --SELECT NAME FROM SYSINDEXES WHERE INDID IN( --SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID))) THEN ‘√ ‘ ELSE ‘ ‘ END, 类型 = B.NAME, --允许空 = CASE WHEN A.ISNULLABLE=1 THEN ‘√ ‘ELSE ‘ ‘ END, 列说明 = ISNULL(G.[VALUE], ‘ ‘) FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND d.xtype=‘V‘ AND D.NAME <> ‘dtproperties ‘ LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id LEFT JOIN sys.extended_properties F ON D.ID=F.major_id AND F.minor_id=0WHERE (D.name=‘@ViewName‘ )GROUP BYA.COLORDER,A.NAME,B.NAME,ISNULL(G.[VALUE], ‘ ‘) ,A.IDORDER BY A.ID,A.COLORDER--触发器ALTER TRIGGER [TRG_QBItem] ---------项触发器-------------------ON [dbo].[QBItem] FOR deleteAS delete from QBOptions where ItemId in (select ItemId from deleted)--计算执行sql语句的时间--首先 在各个sql 执行语句前加：declare @time datetimeset @time=getdate()--然后在sql执行语句后加上：select [语句执行花费时间(毫秒)]=datediff(ms,@time,getdate())