--压缩备份

Declare @strDataBaseName varchar(50)

Declare @FullFileName varchar(50) 

Set @strDataBaseName=\’data\’

Set @FullFileName=\’d:\db_backup\data20170222.bak\’
BACKUP DATABASE @strDataBaseName TO DISK = @FullFileName WITH INIT, STATS = 20

--sql 事务
BEGIN TRAN 事物名

IF(@@ERROR<>0)BEGIN ROLLBACK TRAN  事物名;RETURN;END

COMMIT TRAN  事物名

--数据库清缓存
DBCC DROPCLEANBUFFERS

--忽略自增列 按照sql语句的id新增
SET IDENTITY_INSERT [dbo].[EduMenuFunction] ON
--插入语句
SET IDENTITY_INSERT [dbo].[EduMenuFunction] OFF

--修改语句联表修改
UPDATE    S_GB_StudentBasicInfo
SET BH =ci.BH
from S_GB_StudentBasicInfo sbi  WITH(NOLOCK),C_GB_ClassInfo ci WITH(NOLOCK)
where sbi.BJID=CONVERT(nvarchar(20),ci.ID)

UPDATE    A
SET A.c2 =B.c3
from 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\' )
 BEGIN
Alter 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 all
select a.*
from Base_Company a join cte b on a.ParentId = b.id
where a.id is not null
)
select * from cte


--创建数据库
USE master

create 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=0
WHERE (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=0
WHERE (D.name=\'@ViewName\' )
GROUP BY
A.COLORDER
,A.NAME
,B.NAME
,ISNULL(G.[VALUE], \' \') 
,A.ID
ORDER BY 
A.ID,A.COLORDER

--触发器
ALTER TRIGGER [TRG_QBItem] 
---------项触发器-------------------
ON [dbo].[QBItem] 
FOR delete
AS 
delete from QBOptions where ItemId in (select ItemId from deleted)

--计算执行sql语句的时间
--首先 在各个sql 执行语句前加：
declare @time datetime
set @time=getdate()
--然后在sql执行语句后加上：
select [语句执行花费时间(毫秒)]=datediff(ms,@time,getdate())