Transact-SQL 学习小结

1.把系统里所有用全局临时表的改成局部临时表，不然并发高时会引发对象已经存在的问题，不要用##要用#。

2.int 不能写成 id = '1'，比如Select * from A where ID='1'，因为本身的ID为int,那么你写的索引就没起到作用,而且SQL里面还需要转换类型，影响性能。

3.print error_message() 输出错误信息 可用于调试存储过程或其他批量SQL。也可以输入其他信息,print 'test1';这个可以结合try cath,具体代码如下：

BEGIN TRY                    
    BEGIN
    INSERT INTO A
        (ID,Name)VALUES (1,'test1')
    END
END TRY
BEGIN CATCH
    PRINT ('仓库库存时没有找到对应的记录，所以需要新增一条，新增时失败!' + ERROR_MESSAGE());
END CATCH

print '时间:'+CAST(GETDATE() as nvarchar)+ 
';error_procedure:'+error_procedure()+
';error_line:'+CAST(error_line() as nvarchar)+
';ERROR_MESSAGE:'+ERROR_MESSAGE()+
';ERROR_NUMBER:'+CAST(ERROR_NUMBER() as nvarchar)+
';ERROR_SEVERITY():'+CAST(ERROR_SEVERITY() as nvarchar)

 

4.返回当前会话生成的最后一个标识值，不用@@IDENTITY而用SCOPE_IDENTITY();就是新增表的时候，字段定义为字段1 int identity(1,1)。

 在与会话无关的情况下，如果想知道表中的当前标识列的值（即最后生成的值），应该使用IDENT_CURRENT函数，并为其提供一个表名作为输入，例如：

 select  SCOPE_IDENTITY() as [SCOPE_IDENTITY],@@IDENTITY  as [@@IDENTITY],IDENT_CURRENT('dbo.T1') as [IDENT_CURRENT];

5.一般的金额和数量字段，没设约束，默认为null,都要设isnull(字段,0),或者新建表的时候，设置字段不为NUll，默认值为0,如：[字段] int NOT NULL DEFAULT(0)。

6.查询数据很多的时候，建议用with(nolock)，select 字段1，字段2 from A with(nolock),用with(nolock) 造成脏读，也就是这个地方操作表A的时候，其他地方也可以操作表A，互相不影响 。

7.查数据额时候，尽量不要写*，比如select * from Person with(nolock)，这样写select 字段1，字段2 from Person with(nolock)。

8.SQL里面判断不能为空，应该这样写(@Effect IS NOT NULL AND @Effect <> ''),反之(@Effect IS NULL or @Effect = '')。

9.事务的使用：BEGIN TRAN ：开始事务；COMMIT TRAN：提交事务；ROLLBACK TRAN：事务回滚。具体如下：

BEGIN TRY    
    BEGIN TRAN;    --开始事务        
    BEGIN
    INSERT INTO A
        (ID,Name)VALUES (1,'Test1')
    END
    INSERT INTO B
        (ID,Name)
    VALUES (2,'Test2')
    END
    COMMIT TRAN;--提交事务
END TRY
BEGIN CATCH
    ROLLBACK TRAN;--事务回滚
END CATCH

注意：当在数据库里面批量修改数据的时候，也可以用事务，修改之后的数据验证之后 如果有错，就可以回滚(rollback)，没有错就commit, 代码如下：

begin tran
update TestA 
set Num = 1
where ID = '13'
rollback

commit

10.Indentity(1,1) 除了可以作为表的字段属性，还可以结合临时表来使用，具体如下：

select IDENTITY(INT, 1, 1) AS SeqNo
    into #temp 
    from  new_TestA se
    left join new_TestB sh on se.Id= sh.AId

然后查询临时表#temp，SeqNo会出现1,2,3。 

11.DATEADD(HOUR,10,CreatedOn) 这样写，还可以这样写DATEADD(hh,10,CreatedOn)

12.查询数量或金额字段的时候，默认值为null,或者插入的时候，直接赋值为null,那么查询条件为0的时候，需要加上isnull(字段A,0) = 0,或者 字段A = 0 or 字段A is null，另外

可以在建表的时候默认值设为0，[字段]   int DEFAULT(0)。

13.全文搜索 And Contains(A.LikeCode,'Test001'); 注意使用全文搜索需要配置，数据库—>存储->全文目录->新建，具体如下图:

   

LIKE 与全文搜索的比较
与全文搜索不同，LIKE Transact-SQL 谓词仅对字符模式有效。 另外，不能使用 LIKE 谓词来查询格式化的二进制数据。 此外，对大量非结构化的文本数据执行 LIKE 查询要比对相同数据执行同样的全文查询慢得多。 对数百万行文本数据进行的 LIKE 查询可能需要几分钟的时间才能返回结果；而对于同样的数据，全文查询只需要几秒甚至更少的时间，具体取决于返回的行数。

MSDN：http://msdn.microsoft.com/zh-cn/library/ms189822.aspx 

14.把字段拼接成1,2, SELECT orderId+',' FROM [Order]

WHERE orderId=A.orderId
FOR XML PATH('')) AS OrderCodeStr  效果如下图:

15.查询关键字所在的批SQL（存储过程，视图等）select OBJECT_NAME(object_id) from sys.all_sql_modules  where definition like '%关键字%'。

16.定义表变量，

DECLARE @Tb_PlatformSku table
    (
        SKUCode nvarchar(50) null,
        SKUName nvarchar(200) null,
        OnLineProductCode varchar(50) null,
        OnLineProductName nvarchar(200) null,
        OnLineSKUCode varchar(50) null,
        OnLineProductId varchar(50) null,
        CreateDate datetime null,
        Creator nvarchar(50) null,
        UpdateDate datetime null,
        OnLineSkuId varchar(50) null,
        UpdatePerson nvarchar(50) null,
        SaleStatus nvarchar(100) null,
        ShopId int null,
        Id int null
  );

更新表变量的字段值： 推荐这种写法

        update T1
        set T1.Id = T.Id
        from [PlatformSku] T 
            JOIN @Tb_PlatformSku T1 
                ON T.[ShopId] = T1.ShopId 
                    and T.onlineProductId = T1.OnLineProductId 
                    and T.onlineSkuId=T1.onLineSKUId

 17 (1).创建索引， 最好先创建聚集索引，再创建非聚集所。一般主键在创建表的时候已经创建了，为唯一聚集索引，一般为了优化SQL查询语句，需要创建非聚集索引，

比如：

select OnLineProductCode,OnLineProductName  from PlatformTest
where OnLineProductId = '13174426193'

 查询创建索引如下：

CREATE NONCLUSTERED INDEX [IX_PlatformTest_ShopId_OnlineProductId_OnlineSkuId] ON [dbo].[PlatformTest]([OnLineProductId]) --标记为A处，这里是where 后面的查询条件或者left join，inner join后面on 的条件
INCLUDE ([OnLineProductCode],[OnLineProductName]) --这里为查询的字段，标记为B处，这里不能和A处的字段一样。

CREATE NONCLUSTERED INDEX INDEX_TEST_ID
ON TEST (ID ASC);

(2).删除索引：a.drop index 表名.索引名    b.drop index 索引名 on 表名  

DROP INDEX INDEX_TEST_ID ON TEST

 注意区别索引查找和索引扫描区别？ 

18.获取表类型关键字的脚本：

select * from sys.types 

如下图：

  19.SQL SERVER 游标的使用

--基于查询声明游标
declare cur CURSOR FOR
select id from OrderProducts where OrderCode =@OrderCode
    --select @OrderProductsId = id from OrderProducts where OrderCode =@OrderCode
    
    --打开游标
    OPEN cur
    --读取下一个游标
    FETCH NEXT FROM cur INTO @OrderProductsId
    
    --遍历游标记录，直至到达游标的末尾
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
IF @OrderProductsId <> '' or @OrderProductsId is not null
begin
   IF NOT EXISTS(SELECT 1 FROM OrderProducts WHERE IsInvented=0 AND id=@OrderProductsId) /*判断订单号是否是虚拟商品*/
BEGIN
SET @error=-1080;
GOTO FailedLabel;
END

end   

FETCH NEXT FROM cur INTO @OrderProductsId
END

--关闭游标
CLOSE cur
--释放游标
DEALLOCATE cur

 

--基于查询声明游标
declare @MedicalOrgId nvarchar(64)
declare cur CURSOR FOR select MedicalOrgId from Test where IsValid = 1
-- select @TestId = TestId from Test where IsValid = 1
    --打开游标
    OPEN cur
    --读取下一个游标
    FETCH NEXT FROM cur INTO @TestId
    
    --遍历游标记录，直至到达游标的末尾
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    IF @TestId <> '' or @TestId is not null
    BEGIN
       IF NOT EXISTS(SELECT 1 FROM TestConfig WHERE IsValid=1 AND  [Key]='testPDF'and TestId=@TestId) /*是否有记录，没有就新增*/
       BEGIN
         INSERT [dbo].[TestConfig] ([Id], [TestId], [Key], [Value], [Creator], [CreateTime],[IsValid]) 
         select lower(newid()),@TestId,'testPDF',1,'',CONVERT(varchar(100), GETDATE(), 20),1
       END
    END   

    FETCH NEXT FROM cur INTO @TestId
    END

--关闭游标
CLOSE cur
--释放游标
DEALLOCATE cur

 

 

 

20.(1).SET STATISTICS IO 使 SQL Server 显示有关由 Transact-SQL 语句生成的磁盘活动量的信息。

语法：SET STATISTICS IO { ON | OFF } MSDN:http://technet.microsoft.com/zh-cn/library/ms184361(SQL.90).aspx

注：如果 STATISTICS IO 为 ON，则显示统计信息，直到遇到OFF时不显示统计信息。

set statistics IO on
select * from t1
set statistics IO off

表 't1'。扫描计数 1，逻辑读取 12 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SET STATISTICS IO 也可以直接开启，

 方法：在Management Studio 选择Query(查询)—> Query Options(查询选项)—> Advanced(高级)—>勾选 Set Statistics IO 确定就可以了。

(2). SET STATISTICS TIME on  可以查看CPU的时间

(3).set showplan_xml on  go  .. /go set showplan_xml off 开销最大的查询的估算XML执行计划 

set showplan_xml on 
go
select soh.AccountNumber,sod.LineTotal,sod.OrderQty,sod.UnitPrice,p.Name
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod
on soh.SalesOrderID = sod.SalesOrderID
join Production.Product p
on sod.ProductID = p.ProductID
where sod.LineTotal > 1000
go 
set showplan_xml off

 21.如何通过SQL SERVER（我这里是SQL SERVER 2008 R2） 同时生成脚本和数据？ 还可以生成索引等，具体如下图： 

      

 

    

 

   

 

   

   关键的一步：要编写脚本的数据的类型：架构和数据

  

  打开脚本如下：

USE [BooksDB]
GO
/****** Object:  Table [dbo].[Books]    Script Date: 09/17/2014 22:53:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Books](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
    [Isbn] [varchar](20) NOT NULL,
    [Summary] [nvarchar](1000) NOT NULL,
    [Author] [nvarchar](50) NOT NULL,
    [Thumbnail] [varbinary](max) NULL,
    [Price] [decimal](16, 2) NOT NULL,
    [Published] [date] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Books] ON
INSERT [dbo].[Books] ([ID], [Title], [Isbn], [Summary], [Author], [Thumbnail], [Price], [Published]) VALUES (1, N'ASP.NET MVC 4 Web变成', N'9787560991', N'本书解释了微软最新的。。。', N'徐累，徐杨', NULL, CAST(52.70 AS Decimal(16, 2)), CAST(0x2B370B00 AS Date))
INSERT [dbo].[Books] ([ID], [Title], [Isbn], [Summary], [Author], [Thumbnail], [Price], [Published]) VALUES (2, N'ASP.NET MVC 4 框架解密', N'9787560991', N'本书解释了微软最新的1。。。', N'张三', NULL, CAST(67.70 AS Decimal(16, 2)), CAST(0x98380B00 AS Date))
SET IDENTITY_INSERT [dbo].[Books] OFF

不过有个缺点就是数据多的时候，运行时间要很长。

22.数据库中如果要执行SQL：DECLARE @sql nvarchar(max) ,EXEC sp_executesql  @sql 注意对 @sql 里面的参数一定要加两个双引号('' ''') ,sql varchar转为int :cast(varchar(50) as int)。

23.SQLSERVER 中常见的函数：

1.  CHARINDEX：在一个表达式中搜索另一个表达式并返回其起始位置，比如：

IF CHARINDEX('PayTime', @OrderBy) = 1
  BEGIN
   SET @orderByStr = REPLACE(@OrderBy, 'PayTime', 'A.PayTime')
  END
ELSE IF CHARINDEX('SellerMemo', @OrderBy) = 1
  BEGIN
    SET @orderByStr = REPLACE(@OrderBy, 'SellerMemo', 'A.SellerMemo')
  END

2 .@@ROWCOUNT  受影响的行数，可以用来统计总记录数。

24.存储过程解析XML信息，参考 C# 如何通过拼接XML调用存储过程来查询数据。如下:

DECLARE @Code nvarchar(50) 
        ,@OpStatus varchar(10)
        ,@PaymentOpType varchar(10)      
        ,@DiscountFee varchar(10)
        ,@DiscountFeeOpType varchar(10)      
        ,@PayTimeFrom varchar(10)
        ,@PayTimeTo varchar(10)
        ,@AvailableShippers nvarchar(max)
                
<Query>
    <Code></Code>
    <OpStatus></OpStatus>
    <DiscountFee OpType="=&gt;"></DiscountFee>
    <PayTime From="2014-05-01" To="2014-05-31" />
    <AvailableShippers>1,2,3</AvailableShippers>
</Query>

SELECT         
     @Code = R.c.value('(Code)[1]', 'nvarchar(50)')                           -- 定单编号
    ,@OpStatus = R.c.value('(OpStatus)[1]', 'varchar(10)')                    -- 锁定状态
    ,@DiscountFeeOpType = R.c.value('(./DiscountFee/@OpType)[1]', 'varchar(20)')  -- 优惠金额(查询操作符)
    ,@DiscountFee = R.c.value('(DiscountFee)[1]', 'varchar(10)')                  -- 优惠金额
    ,@PayTimeFrom = R.c.value('(./PayTime/@From)[1]', 'varchar(20)')          -- 支付时间(开始)
    ,@PayTimeTo = R.c.value('(./PayTime/@To)[1]', 'varchar(20)')              -- 支付时间(结束)
    ,@AvailableShippers = R.c.value('(AvailableShippers)[1]', 'nvarchar(max)')          -- 有权限访问的仓库
FROM @Where.nodes('/Query') R(c)

 25.创建数据库（这脚本可以直接在数据库里面生成）：

USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')
DROP DATABASE [TestDB]
GO
USE [master]
GO
CREATE database TestDB
ON 
( NAME = N'TestDB',
    FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.mdf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDB_log',
    FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 10GB,
    FILEGROWTH = 10% );
GO

26.给数据库授权（这脚本可以直接在数据库里面生成）：

USE TestDB;
CREATE USER test FOR LOGIN test;
EXEC sp_addrolemember N'db_owner', N'test';

 27.新建数据库登陆名和密码（这脚本可以直接在数据库里面生成）：其中test为uid,12345为密码

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'test')
CREATE LOGIN test WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[简体中文], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

 28.给用户还原数据库的权限（这脚本可以直接在数据库里面生成）

EXEC master..sp_addsrvrolemember @loginame = N'test1', @rolename = N'dbcreator';

29.还原数据库（这脚本可以直接在数据库里面生成）

USE master;
RESTORE DATABASE [xx] 
FROM  DISK = N'D:\backup\TestDB.bak' WITH  FILE = 1,  
MOVE N'TestDB' TO N'D:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xx.mdf',  
MOVE N'TestDB_log' TO N'D:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\xx_1og.ldf',  
NOUNLOAD, 
REPLACE, 
RECOVERY

30.查看创建存储过程脚本的方法： sp_helptext '存储过程名'; 

31.可以把创建数据库的表，放在存储过程里面，执行存储过程就可以了。

create database test2016;

use test2016;

--创建存储过程
create proc testproc
as 
begin 
   CREATE TABLE [dbo].[ItemcatsTest](
    [Cid] [int] NOT NULL,
    [parent_cid] [int] NULL,
    [name] [nvarchar](50) NULL,
    [is_parent] [bit] NULL,
    [status] [varchar](50) NULL,
    [sort_order] [int] NULL,
    [features] [nvarchar](100) NULL,
 CONSTRAINT [PK_Itemcats] PRIMARY KEY CLUSTERED 
(
    [Cid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[LabelTest](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LabelName] [nvarchar](100) NULL,
    [Status] [bit] NULL,
    [LabelType] [int] NULL,
    [ShipperId] [int] NULL,
    [BrandId] [int] NULL,
 CONSTRAINT [PK_PRODUCTBRAND] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

end

--执行存储过程之后，test2016库里面就会新增两张表 ItemcastTest、LabelTest
exec testproc;  

32.sqlserver 2008 以及以上版本，支持一次性插入多条记录。

create table Student  --学生成绩表
(
 id int PRIMARY KEY IDENTITY(1,1),  --主键
 Grade int, --班级
 Score int --分数
)

INSERT INTO Student 
VALUES(1,2),(2,3),(3,4);

SELECT * FROM Student;

 33.存储过程中，如果条件不满足，直接return.

SELECT @UserId = UserId FROM Test  WHERE OpenId=@OpenId and UserCurrent = 1
   IF @UserId is not null
   BEGIN
        SELECT Top 1 * FROM Test2 WHERE UserId=@UserId;
        RETURN
END

34.数据库中对字符串进行SHA1加密：

sys.fn_sqlvarbasetostr(HASHBYTES('SHA1', CONVERT(varchar(4000), LEFT(NEWID(), 10) + @PasswordSalt)))

其中@PasswordSalt为：

public static string CreateSalt()
{
    byte[] array = new byte[16];
    new RNGCryptoServiceProvider().GetBytes(array);
    return Convert.ToBase64String(array);
}

35. 传1,2,3类似的参数到存储过程中，作为条件select * from A where id in(1,2,3)，会'1,2,3'转为Int错误？

      解决方法：可以用临时表或表变量存起来，然后直接查询

    

CREATE PROC [dbo].[GetTestInfo]
  @UserId int,
  @OrderAmount decimal(10,2),
  @BindId nvarchar(500),
  @CategoryId nvarchar(500)
as 
begin
    DECLARE @ParentCategoryIdstr nvarchar(1000);    
    DECLARE @SqlStr nvarchar(1000);

    BEGIN TRY 
    IF @CategoryId IS NOT NULL
    BEGIN
        set @SqlStr = ''
        select @SqlStr = 'select ' + REPLACE(@CategoryId, ',', ' as CategoryId union all select ')
        
        -- 声明表变量
        declare @t table(CategoryId int)
    
        insert into @t(CategoryId)
        exec (@SqlStr)

        select @ParentCategoryIdstr = (select convert(varchar(10),ParentCategoryId)+','
        from Ecshop_Categories a 
        where CategoryId in(select CategoryId from @t) 
        FOR XML PATH(''));  --- 拼接字符串
    END
 END 

36.备份表

select * into  A_20150826_bak from  A

新增表字段：

IF NOT EXISTS (SELECT 1 FROM syscolumns WHERE id=OBJECT_ID('[test_student]') and name='IsRead')
BEGIN
    ALTER TABLE test_student ADD IsRead TINYINT DEFAULT(0) NOT NULL;
END

37.分区，相同名称，获取最新的一条数据 ,参考sqlserver中分区函数 partition by的用法

select * 
from (
    SELECT *,ROW_NUMBER() over(partition by name order by addtime desc) as PartNumber 
    FROM Test_Materials
) t where PartNumber = 1

38. 利用Row_Number() 分页

declare @PageSize int;
declare @StartIndex int;

set @PageSize = 10
set @StartIndex = 1;

with TableA_Paged as(
    select 
        Row_Number() over(order by 【排序字段】) as RowNumber,
        *
    from
        TableA
    where 条件
)
select * from MyTable_Paged
where RowNumber between @StartIndex and @StartIndex+@PageSize-1

 39.如何进行跨服务器的数据库查询

SELECT * FROM
OPENDATASOURCE('SQLOLEDB','Data Source=192.68.1;User ID=sa;Password=12356').[DB_TEST].[dbo].users AS A

40.

SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句', 
DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='testdb'  
ORDER BY [cpu_time] DESC

41.

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_013001_8992338.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_023001_5899369.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_033001_3128487.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_043001_0741320.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_053001_7422609.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_063001_1069637.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_073001_8956850.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_083001_7750602.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_093001_7876174.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_103001_3584290.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_113001_3949350.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_123001_9467479.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_133001_7003804.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_143006_1701343.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_153001_2204785.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

RESTORE LOG [tempxxxxxx] FROM  DISK = N'D:\db\backup\testDb\testDb_backup_2016_02_29_163001_6513601.trn' WITH  FILE = 1,  RECOVERY,  NOUNLOAD,  STATS = 10
GO

 42.查询性能,是否需要新建索引

SELECT TOP 10 
dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50  
ORDER BY [cpu_time] DESC

--查看CPU数和user scheduler数目
SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info
--查看最大工作线程数
SELECT max_workers_count FROM sys.dm_os_sys_info


SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;   --

 EqualityUsage 下新建

 

create index ix_Test_UserShippingAddresses_Userid on [dbo].[Ecshop_UserShippingAddresses]([UserId])

43.SQL中获取小数点后两位，但是不四舍五入的方法:round(@t,2,1);

44.创建数据库设置默认数据库字符集

USE master;
IF EXISTS (SELECT NAME FROM master.dbo.sysdatabases WHERE NAME = 'test001')
DROP DATABASE test001
GO
CREATE DATABASE test001
COLLATE Chinese_PRC_CI_AS -- 设置默认的数据字符集
GO

45.在数据库中查看影响SQL性能的方法：

45.存储过程格式良好的例子和执行的方法

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cp_Membership_Mobile_Create]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cp_Membership_Mobile_Create]

GO

/*新增用户信息

执行：
    DECLARE @UserName  NVARCHAR(256);
    DECLARE @Email        NVARCHAR(256) = '';
    DECLARE @MobilePIN    NVARCHAR(32);
    DECLARE @PasswordQuestion  NVARCHAR(256);
    DECLARE @PasswordAnswer   NVARCHAR(128);
    DECLARE @IsApproved       BIT = 1;
    DECLARE @CurrentTime      DATETIME = GETDATE();
    DECLARE @CreateDate       DATETIME = GETDATE();
    DECLARE @UniqueEmail      INT = 1;
    DECLARE @ProvinceId       INT = 0;
    DECLARE @SessionId        UNIQUEIDENTIFIER = NEWID();
    DECLARE @RegisterType     INT = 2;--手机
    DECLARE @Password  NVARCHAR(128);
    DECLARE @PasswordSalt NVARCHAR(128);
    DECLARE @PasswordFormat   INT = 1;
    DECLARE @NewUserId        INT;
    DECLARE @Status           INT = 0;
    DECLARE @Result           INT = 0;
    
    EXEC @Status = [cp_Membership_Mobile_Create] @UserName,@Password,@PasswordSalt,@Email,@MobilePIN,@PasswordQuestion,@PasswordAnswer,@IsApproved,@CurrentTime,@CreateDate,@UniqueEmail
        ,@PasswordFormat,@ProvinceId,@SessionId,@RegisterType,@NewUserId OUTPUT;
    --IF @Status = 1 -- 1 代表成功
*/
CREATE PROCEDURE [dbo].[cp_Membership_Mobile_Create]
(
    @UserName                               nvarchar(256),
    @Password                               nvarchar(128),
    @PasswordSalt                           nvarchar(128),
    @Email                                  nvarchar(256),
    @MobilePIN                              nvarchar(32),
    @PasswordQuestion                       nvarchar(256),
    @PasswordAnswer                         nvarchar(128),
    @IsApproved                             bit,
    @CurrentTime                            datetime,
    @CreateDate                             datetime = NULL,
    @UniqueEmail                            int      = 0,
    @PasswordFormat                         int      = 0,
    @ProvinceId                             int      = 0,
    @SessionId                              uniqueidentifier,
    @RegisterType                           int      = 1,
    @UserId                                 int OUTPUT
)  
AS 
BEGIN
    DECLARE @GradeId INT;
    DECLARE @RoleId uniqueidentifier;
    DECLARE @NewUserId int
    SELECT @NewUserId = NULL

    DECLARE @IsLockedOut bit
    SET @IsLockedOut = 0

    DECLARE @LastLockoutDate  datetime
    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAttemptCount int
    SET @FailedPasswordAttemptCount = 0

    DECLARE @FailedPasswordAttemptWindowStart  datetime
    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAnswerAttemptCount int
    SET @FailedPasswordAnswerAttemptCount = 0

    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @NewUserCreated bit
    DECLARE @ReturnValue   int
    SET @ReturnValue = 0

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

        SELECT top 1 @GradeId = GradeId FROM MemberGrades WHERE IsDefault = 1
        SELECT top 1 @RoleId = RoleId FROM Roles WHERE LoweredRoleName='member'

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END
    
    IF (@UniqueEmail = 1 AND @RegisterType = 3)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.Users WITH ( UPDLOCK, HOLDLOCK )
                    WHERE LoweredEmail = LOWER(@Email)))
        BEGIN
            SET @ErrorCode = 7
            GOTO Cleanup
        END
    END

    SET @CreateDate = @CurrentTime

    IF (@RegisterType = 1)
    BEGIN
        SELECT  @NewUserId = UserId FROM dbo.Users WHERE LOWER(@UserName) = LoweredUserName
    END

    IF (@RegisterType = 2)
    BEGIN
        SELECT  @NewUserId = UserId FROM dbo.Users WHERE MobilePIN = @MobilePIN
    END
    
    IF (@RegisterType = 3)
    BEGIN
        SELECT  @NewUserId = UserId FROM dbo.Users WHERE LoweredEmail = LOWER(@Email)
    END
        
    IF ( @NewUserId IS NULL )
    BEGIN
        SET @NewUserId = @UserId

        INSERT INTO dbo.Users
            (
            LoweredUserName,
            UserName,
            IsAnonymous,
            LastActivityDate,
            Password,
            PasswordSalt,
            Email,
            MobilePIN,
            LoweredEmail,
            PasswordQuestion,
            PasswordAnswer,
            PasswordFormat,
            IsApproved,
            IsLockedOut,
            CreateDate,
            LastLoginDate,
            LastPasswordChangedDate,
            LastLockoutDate,
            FailedPasswordAttemptCount,
            FailedPasswordAttemptWindowStart,
            FailedPasswordAnswerAttemptCount,
            FailedPasswordAnswerAttemptWindowStart,
            SessionId,
            UserRole)
         VALUES (
            LOWER(@UserName),
            @UserName,
            0,
            @CreateDate,
            @Password,
            @PasswordSalt,
            @Email,
            @MobilePIN,
            LOWER(@Email),
            @PasswordQuestion,
            @PasswordAnswer,
            @PasswordFormat,
            @IsApproved,
            @IsLockedOut,
            @CreateDate,
            @CreateDate,
            @CreateDate,
            @LastLockoutDate,
            @FailedPasswordAttemptCount,
            @FailedPasswordAttemptWindowStart,
            @FailedPasswordAnswerAttemptCount,
            @FailedPasswordAnswerAttemptWindowStart,
            @SessionId,
            3)
                  
        SELECT @NewUserId = SCOPE_IDENTITY(), @NewUserCreated = 1, @ReturnValue = 1

        INSERT INTO [dbo].[UsersInRoles] ([UserId], [RoleId]) VALUES (@NewUserId, @RoleId);

        INSERT INTO [dbo].[Members] (
             [UserId]
            ,[GradeId]
            ,[ReferralStatus]
            ,[IsOpenBalance]
            ,[TradePassword]
            ,[TradePasswordSalt]
            ,[TradePasswordFormat]
            ,[CellPhoneVerification]
            ,[EmailVerification]
            ,[RealName]
            ,[TopRegionId]
            ,[SessionId]) 
        VALUES (
             @NewUserId
            ,@GradeId
            ,0
            ,0
            ,@Password
            ,@PasswordSalt
            ,1
            ,0
            ,0
            ,''
            ,@ProvinceId
            ,@SessionId);
    END
    ELSE
    BEGIN
        SET @NewUserCreated = 0

        SET @ErrorCode = 6
        GOTO Cleanup
    END

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @ReturnValue = -1 )
    BEGIN
        SET @ErrorCode = 10
        GOTO Cleanup
    END

    SET @UserId = @NewUserId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN 1

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END

GO

 46.给SQLserver表和字段添加注释

EXEC SYS.SP_ADDEXTENDEDPROPERTY @NAME=N'MS_DESCRIPTION', @VALUE=N'年收入' , @LEVEL0TYPE=N'SCHEMA',@LEVEL0NAME=N'DBO', @LEVEL1TYPE=N'TABLE',@LEVEL1NAME=N'TEST'
GO

EXEC SYS.SP_ADDEXTENDEDPROPERTY @NAME=N'MS_DESCRIPTION', @VALUE=N'用户账号' , @LEVEL0TYPE=N'SCHEMA',@LEVEL0NAME=N'DBO', @LEVEL1TYPE=N'TABLE',@LEVEL1NAME=N'TEST', @LEVEL2TYPE=N'COLUMN',@LEVEL2NAME=N'TESTID'
GO

 47.SqlServer 递归查询  http://www.imooc.com/article/27689

/*
Books – Philosophy – Metaphysics
Books – Philosophy – Confucianism - Mencius
Books – Literature – Lin Yutang
Software – Utilities – File Management
*/
WITH TempDeptment (CatalogueId,ParentCatalogueId,Name,[Conjunction]) AS
(
SELECT CatalogueId,ParentCatalogueId,Name, CAST(Name AS Nvarchar(1000)) as [Conjunction] FROM Catalogue WHERE ParentCatalogueId = 0
UNION ALL
SELECT d.CatalogueId, d.ParentCatalogueId, d.Name, CAST(([Conjunction] + N' - ' + d.Name) AS Nvarchar(1000)) FROM TempDeptment AS td,Catalogue AS d WHERE td.CatalogueId = d.ParentCatalogueId
)
SELECT CatalogueId, [Conjunction] FROM TempDeptment

 

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Catalogue](
    [CatalogueId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](126) NOT NULL,
    [Description] [varchar](256) NULL,
    [ParentCatalogueId] [int] NOT NULL,
    [CreatedTime] [datetime] NOT NULL,
    [ModifiedTime] [datetime] NOT NULL,
    [IsDeleted] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [CatalogueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[Catalogue] ON 

INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (1, N'Books', NULL, 0, CAST(N'2019-08-22T17:12:19.000' AS DateTime), CAST(N'2019-08-22T17:12:22.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (2, N'Philosophy', NULL, 1, CAST(N'2019-08-22T17:12:52.000' AS DateTime), CAST(N'2019-08-22T17:12:54.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (3, N'Metaphysics', NULL, 2, CAST(N'2019-08-22T17:13:19.000' AS DateTime), CAST(N'2019-08-22T17:13:21.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (4, N'Confucianism', NULL, 2, CAST(N'2019-08-22T17:13:36.000' AS DateTime), CAST(N'2019-08-22T17:13:38.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (5, N'Mencius', NULL, 4, CAST(N'2019-08-22T17:13:54.000' AS DateTime), CAST(N'2019-08-22T17:13:58.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (6, N'Literature', NULL, 1, CAST(N'2019-08-22T17:14:17.000' AS DateTime), CAST(N'2019-08-22T17:14:19.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (7, N'Lin Yutang', NULL, 6, CAST(N'2019-08-22T17:14:42.000' AS DateTime), CAST(N'2019-08-22T17:14:45.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (8, N'Software', NULL, 0, CAST(N'2019-08-22T17:15:02.000' AS DateTime), CAST(N'2019-08-22T17:15:04.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (9, N'Utilities', NULL, 8, CAST(N'2019-08-22T17:15:19.000' AS DateTime), CAST(N'2019-08-22T17:15:22.000' AS DateTime), 0)
INSERT [dbo].[Catalogue] ([CatalogueId], [Name], [Description], [ParentCatalogueId], [CreatedTime], [ModifiedTime], [IsDeleted]) VALUES (10, N'File Management', NULL, 9, CAST(N'2019-08-22T17:15:44.000' AS DateTime), CAST(N'2019-08-22T17:15:48.000' AS DateTime), 0)
SET IDENTITY_INSERT [dbo].[Catalogue] OFF
ALTER TABLE [dbo].[Catalogue] ADD  DEFAULT (NULL) FOR [Description]
GO
ALTER TABLE [dbo].[Catalogue] ADD  DEFAULT ('0') FOR [ParentCatalogueId]
GO
ALTER TABLE [dbo].[Catalogue] ADD  DEFAULT ('0') FOR [IsDeleted]
GO

48. sql server 客户端，选中某个表，按alt+f1 可以查看表结构，如下图