摘要：use master go --检索死锁进程 select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name from sysprocesses where spid in ( select blocked from sysprocesses where blocked ... 阅读全文

摘要：create database ServiceDB on primary ( name='ServiceDB_data', -- 主数据文件的逻辑名称 filename='D:\WebRoot\DB\ServiceDB_data.mdf', -- 主数据文件的物理名称 siz... 阅读全文

摘要：declare @tbl table(order_id varchar(20),dates datetime)insert @tbl(order_id,dates)select 'A','2014-1-1' unionselect 'A','2014-2-1' unionselect 'A','20... 阅读全文

摘要：@echo onsetlocalrem 设置数据库和备份文件参数...set sid=testorclset username=testnameset password=testpwdset bakdir=E:\BackUp\HOMAOAset bakdate=%date:~11%set conne... 阅读全文

摘要：--创建聚集索引create clustered index ix_tbl_test_DocDateon tbl_test(DocDate)with fillfactor=30GO--创建非聚集索引create nonclustered index ix_tbl_test_DocNoon tbl_t... 阅读全文

摘要：declare @yr int,@mth int,@logistics_code varchar(30),@fee_type varchar(50)select @yr=2014,@mth=1,@logistics_code='All',@fee_type='All'declare @index int,@from_date datetime,@to_date datetime,@strDateList varchar(180),@strSql varchar(1000)select @index=1,@strDateList='',@from_ 阅读全文

摘要：--创建登录账户use masterGOEXEC sp_addlogin 'jacky', 'pwd'--EXEC sp_droplogin 'jacky' --删除登陆账户 use TestDBGO--创建数据库用户EXEC sp_grantdbaccess 'jacky'GO--drop sc... 阅读全文

摘要：declare @date datetime;set @date = getdate()--思路，指定日期是当年的第几周-给定日期所在月第一天是当年的第几周select datepart(week,@date)-datepart(week,dateadd(month,datediff(month,0,@date),0))+1 [week_month]select datepart(week,@date)-datepart(week,dateadd(day,1-datepart(day,@date),@date))+1 [week_month] 阅读全文

摘要：--创建链接服务器/*sp_addlinkedserver @参数 @server='DB_sql', --被访问的服务器别名 @srvproduct='',@provider='SQLOLEDB',@datasrc='Server2' --要访问的服务器sp_addlinkedsrvlogin@参... 阅读全文

摘要：CREATE PROC sp_Select_Table @TableName NVARCHAR(200) AS if charindex(' ', @TableName)<=0 begin DECLARE @sql NVARCHAR(1000) SET @sql='SELECT * FROM ' + @TableName EXEC (@sql) endGO 阅读全文

摘要：declare @tbl table(name varchar(20),age datetime)insert into @tbl select 'jacky','1985-12-23' union select 'myjacky','1986-12-10' unionselect 'myjacky','1986-12-12' unionselect 'jacky','1988-09-23'--方法一select a.* from @tbl a inner join 阅读全文