SQL Server知识点

一、索引

介绍

　　索引分为聚集索引和非聚集索引，数据库中的索引类似于一本书的目录，在一本书中通过目录可以快速找到你想要的信息，而不需要读完全书。

索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间。但是索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。

索引建少了，用 WHERE 子句找数据效率低，不利于查找数据。

索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL SERVER 除了要更新数据表本身，还要连带立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间。

索引的分类

索引就类似于中文字典前面的目录，按照拼音或部首都可以很快的定位到所要查找的字。

1、唯一索引（UNIQUE）：每一行的索引值都是唯一的（创建了唯一约束，系统将自动创建唯一索引）

2、主键索引：当创建表时指定的主键列，会自动创建主键索引，并且拥有唯一的特性。

3、聚集索引（CLUSTERED）：聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音 a 过了后面肯定是 b 一样。

4、非聚集索引（NONCLUSTERED）：非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续，物理存储并不连续。

PS：聚集索引一个表只能有一个（因为数据真实的物理存储顺序就是按照聚集索引存储的。），而非聚集索引一个表可以存在多个。

　　查看表中的索引： select * from sys.indexes where object_id = OBJECT_ID('表名');

问题：主键就是加了唯一性约束的聚集索引？

不对，因为一般创建表时，主键的索引默认为聚集索引，但是我们可以手动改为非聚集索引【NONCLUSTERED 】的。

CREATE TABLE [dbo].[OrderExpertAmount](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[Remark] [nvarchar](max) NULL, 

CONSTRAINT [PK_OrderExpertAmount] 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]

我们可以对表执行以下语句，看报不报错。

　　创建聚集索引：CREATE CLUSTERED INDEX index_orderid ON OrderExpertAmount(OrderID);

参考：主键就是聚集索引吗？

实际上，让主键ID作为聚集索引是一种资源浪费，因为：ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。

更多参考：聚集索引和非聚集索引的区别有哪些

实例

-- 创建唯一聚集索引

create unique clustered        --表示创建唯一聚集索引
index UQ_Clu_StuNo        --索引名称
on Student(S_StuNo)        --数据表名称（建立索引的列名）

PS：当 create index 时，如果未指定 clustered 和 nonclustered，那么默认为 nonclustered。

索引定义原则：

避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。

在频繁进行排序或分组（即进行 GROUP BY 或 ORDER BY 操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必需遍历的索引页面降低到最小，此外，尽可能的使用整数做为键值，因为整数的访问速度最快。

索引可以优化SQL查询性能，在sql管理器中

1、查看耗时：工具里面第一个工具（sql server profiler），连接上远程的服务器，看每个查询耗费的时间（监测SQL语句执行的性能参数）。

2、在查询分析器中，运行一个查询，执行

3、切换到sql server profiler中，可以看到第2步sql语句具体的用时。

4、索引自建：切换到查询分析器，选中耗时的sql语句右键-》数据库引擎优化顾问中的分析查询-》登录-》开始分析-》

操作-》应用建议

索引导出：数据库右键-》任务-》生成脚本-》

扩展：SQL Server Profiler工具使用说明

　　 SQL Server Profiler的简单使用

二、视图

　　视图是一种虚拟表，来自一个或者多个表的行或者列，视图并不是数据库中存储的数据值，可以简单的理解视图就是封装了一段查询语句，调用该视图就得到查询语句查询出来的临时表。

创建视图：View_UWBandVIDEO

Create view View_UWBandVIDEO
 as
SELECT     dbo.INFO_PARKING_LOT.ID, dbo.VIDEO_HISTORY_CO.TIME, 
dbo.VIDEO_HISTORY_CO.FLAG,
 dbo.VIDEO_HISTORY_CO.PATH, dbo.VIDEO_HISTORY_CO.DEVICEID,
dbo.INFO_PARKING_LOT.PARKINGNAME, dbo.INFO_PARKING_LOT.PARKINGID, dbo.INFO_PARKING_LOT.STATUS, 
dbo.INFO_PARKING_LOT.TIMEIN,
dbo.INFO_PARKING_LOT.TIMEOUT, 
dbo.INFO_PARKING_LOT.PLATE, 
dbo.INFO_PARKING_LOT.OBUID

FROM  dbo.INFO_PARKING_LOT INNER JOIN   dbo.VIDEO_HISTORY_CO

ON dbo.INFO_PARKING_LOT.ID = dbo.VIDEO_HISTORY_CO.LOTID

View Code

程序中执行视图查询，就像查询数据表一样操作：

select * FROM View_UWBandVIDEO where lotid=’211001’

但是视图没法传参数。

三、储存过程

　　是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程的好处：

1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。
3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

但是存储过程：移植性差、代码可复用差。

用法：

-------------创建名为GetUserAccount的存储过程----------------
create Procedure GetUserAccount
as
select * from UserAccount
go

-------------执行上面的存储过程----------------
exec GetUserAccount

结果：相当于运行 select * from UserAccount 这行代码，结果为整个表的数据。

eg 之前做项目过程中用到的部分存储过程：

视频历史表中查实时数据，三次查询, [sp_berthCheckResult]
第一次查询需要查出一些列，将他们设置为参数，，最后将这些参数insert到一个临时表（#tmp 需加#号），这个临时表需要创建，字段的长度需要定义好，
①    拼接语句中，单引号需要转义：’’’
②    执行语句中注意转化，EXEC sp_executesql
Int类型的flagco    CAST( @flagco  as nvarchar(50)) 
Datetime类型的timeTemp2，，04 27 2017  1:20PM需要转为'2017-04-27 13:20:22'。CAST((convert(varchar(100),@timeTemp2,20)) as varchar)
sql日期时间格式转换： convert(varchar(100),@timeTemp2,20
存储过程的修改，  编写存储过程脚本为->创建到，修改。。然后删除，在执行这个创建sql

USE [CIP-MIDDLEWARE]
GO
/****** Object:  StoredProcedure [dbo].[sp_berthCheckResult]    Script Date: 05/12/2017 10:57:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <hy>
-- Create date: <2017-5-12>
-- Description:    <Description,视频历史表中查实时数据，三次查询,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_berthCheckResult]
    -- Add the parameters for the stored procedure here
    @LOTID varchar(50)=''
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    create table #tmp(
     AUTOINDEX bigint null,
     LOTID varchar(10),
     TIME datetime null,
     FLAG int null,
     PATH varchar(1024) null,
     DEVICEID varchar(50) null,
     RELIABILITY varchar(50) null,
     FLAGREMARK nvarchar(1024) null,
     ISILLEGPARKING int null,
     ILLEGREMARK nvarchar(1024) null
    )
    declare @strwhere nvarchar(500);
    declare @sql1 nvarchar(2000);
    declare @sql2 nvarchar(2000);
    declare @sql3 nvarchar(2000);
    --set @sql1=' LOTID='''+@LOTID+'''';
    if(@LOTID<>'')
    begin
      set @strwhere=' LOTID='''+@LOTID+''' ';
    end
    
    ---第一次查询，需要flagco，PATH，DEVICEID，RELIABLITY，FLAGCOREMARK，ISILLEGPARKING，ILLEGREMARK，
    declare @flagco int;
    declare @timeTemp1 datetime ;  --可以不要
    
    declare @autoindex bigint;
    declare @PATH varchar(1024);
    declare @DEVICEID varchar(50);
    declare @RELIABLITY varchar(50);
    declare @FLAGCOREMARK nvarchar(1024);
    declare @ISILLEGPARKING int;
    declare @ILLEGREMARK nvarchar(1024);
    
    set @sql1='select top 1 @flagco=FLAGCO,@timeTemp1=TIME,@autoindex=AUTOINDEX,@PATH=PATH,@DEVICEID=DEVICEID,
    @RELIABLITY=RELIABLITY,@FLAGCOREMARK=FLAGCOREMARK,@ISILLEGPARKING=ISILLEGPARKING,@ILLEGREMARK=ILLEGREMARK 
    from VIDEO_HISTORY_CO where '+@strwhere+' order by TIME desc';
    EXEC sp_executesql @sql1,N'@flagco int OUTPUT,@timeTemp1 datetime OUTPUT,@autoindex bigint OUTPUT,@PATH varchar(1024) OUTPUT,@DEVICEID varchar(50) OUTPUT,
    @RELIABLITY varchar(50) OUTPUT,@FLAGCOREMARK nvarchar(1024) OUTPUT,@ISILLEGPARKING int OUTPUT,@ILLEGREMARK nvarchar(1024) OUTPUT', 
     @flagco OUTPUT,@timeTemp1 OUTPUT ,@autoindex OUTPUT,@PATH OUTPUT,@DEVICEID OUTPUT,@RELIABLITY OUTPUT,@FLAGCOREMARK OUTPUT,@ISILLEGPARKING OUTPUT,@ILLEGREMARK OUTPUT;
    print @sql1;
    Print @timeTemp1;    
    
    
  --第二次查询，需要flagco，TIME
    declare @flagcoTemp int;
    declare @timeTemp2 datetime;
    set @sql2='select top 1 @flagcoTemp=FLAGCO,@timeTemp=TIME from VIDEO_HISTORY_CO where '+@strwhere+' AND FLAGCO!=99 AND FLAGCO!='+ CAST(@flagco as nvarchar(50)) +' order by TIME desc';
    EXEC sp_executesql @sql2,N'@flagcoTemp int OUTPUT,@timeTemp datetime OUTPUT',@flagcoTemp OUTPUT,@timeTemp2 OUTPUT;
    print @sql2;
    print  @flagcoTemp;
    print @timeTemp2;

  --第三次查询，需要flagco，TIME
    declare @time datetime;
    set @sql3='select top 1 @time=TIME from VIDEO_HISTORY_CO where '+@strwhere+' AND  FLAGCO=' + CAST( @flagco  as nvarchar(50)) + ' AND TIME>'''+ CAST((convert(varchar(100),@timeTemp2,20)) as varchar) +''' order by TIME asc';
    EXEC sp_executesql @sql3,N'@time datetime OUTPUT',@time OUTPUT;
    print @sql3;
    print @time;
    
    insert into #tmp (AUTOINDEX, LOTID, FLAG,TIME,PATH,DEVICEID,RELIABILITY,FLAGREMARK,ISILLEGPARKING,ILLEGREMARK)values(@autoindex, @LOTID, @flagco,@time,@PATH,@DEVICEID,@RELIABLITY,@FLAGCOREMARK,@ISILLEGPARKING,@ILLEGREMARK);
    select * from #tmp;
    drop table #tmp;
END

为了查询数据量少，限定个时间。第一次查询时，只查TIME>(当前时间-N天)的。
' AND TIME>'''+ CAST((convert(varchar(100),DATEADD(day,-30,GETDATE()),20)) as varchar) +''' order by TIME desc';

View Code

问题一、sql 在将 nvarchar 值转换成数据类型 int 时失败

　　存储过程需要的参数是int类型，按道理直接传值（+@Id）是没有问题的，　　

　　if @Id <> 0

　　set @sql += ' and Id = ' +@Id

但是在执行存储过程的时候，却弹出：“sql 在将 nvarchar 值转换成数据类型 int 时失败。”

这时候需要转换一下：

set @sql += ' and Id = ' +Cast(@Id as nvarchar(50))

四、Declare的使用

　　声明变量的意思，eg 1：

IF 1=1
BEGIN
    DECLARE @test VARCHAR
    SET @test='1'   
    PRINT 'in if:'+@test
END

运行看结果，输出 in if:1 这是可以预想的结果。那我们在if外面使用变量@test试试。

eg 2：

IF 1=1
BEGIN
   DECLARE @test VARCHAR
   SET @test='1'   
   PRINT 'in if:'+@test
END
PRINT 'out if:'+@test

这样会是什么结果呢，不知道大家怎么想的，以我的大脑顺势就想到这应该报错啊，出了变量的作用域了。

实际结果不仅没报错而且@test的值还在。

in if:1

out if:1

eg 3：

IF 1=1
BEGIN
   DECLARE @test VARCHAR
   SET @test='1'   
   PRINT 'in if:'+@test
END
GO
PRINT 'out if:'+@test

这下对了，检查语法后SQL报错 “必须声明标量变量"@test"”

注：GO就是用于一个sql语句的结束

比如说一个批处理语句是这样的 select *from ,b select *from a 在后一个select后面加上一个GO，这样可以一次执行两条sql 语句

同时，分号(；) 也是语句的结尾，用了分号也不行。

五、exist与not exist

exists （sql 返回结果集，为真）
not exists (sql不返回结果集，为真）

如下：
表A
ID NAME
1 A1
2 A2
3 A3

表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3

SELECT   ID,NAME  
FROM   A   WHERE   EXIST   (SELECT   *   FROM   B   WHERE   A.ID=B.AID)

执行结果为
1 A1
2 A2
原因可以按照如下分析

SELECT   ID,NAME  

FROM   A   WHERE   EXISTS   (SELECT   *   FROM   B   WHERE   B.AID=１) 
---> SELECT   *   FROM   B   WHERE   B.AID=１有值，返回真，所以有数据

SELECT   ID,NAME  

FROM   A   WHERE   EXISTS   (SELECT   *   FROM   B   WHERE   B.AID=2) 
---> SELECT   *   FROM   B   WHERE   B.AID=２有值，返回真，所以有数据

SELECT   ID,NAME  

FROM   A   WHERE   EXISTS   (SELECT   *   FROM   B   WHERE   B.AID=3) 
---> SELECT   *   FROM   B   WHERE   B.AID=３无值，返回假，所以没有数据

View Code

NOT EXISTS 就是反过来

SELECT   ID,NAME  
FROM   A   WHERE　NOT   EXIST   (SELECT   *   FROM   B   WHERE   A.ID=B.AID)

执行结果为
3 A3
===========================================================EXISTS = IN,意思相同

不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因

SELECT   ID,NAME  
FROM   A　   WHERE　ID   IN   (SELECT   AID   FROM   B)

六、查询优化小技巧

技巧1 比较运算符能用 “=”就不用“<>”。“=”增加了索引的使用几率。

技巧2 明知只有一条查询结果，那请使用 “LIMIT 1” 。“LIMIT 1”可以避免全表扫描，找到对应结果就不会再继续扫描了。

技巧3 为列选择合适的数据类型。能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT，道理你懂的，磁盘和内存消耗越小越好嘛。

技巧4 将大的DELETE，UPDATE or INSERT 查询变成多个小查询

技巧5 使用UNION ALL 代替 UNION，如果结果集允许重复的话。因为 UNION ALL 不去重，效率高于 UNION。

技巧6 尽量避免使用 “SELECT *”。如果不查询表中所有的列，尽量避免使用 SELECT *，因为它会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。

技巧7 ORDER BY 的列尽量被索引。ORDER BY的列如果被索引，性能也会更好。

技巧8 使用 LIMIT 实现分页逻辑。不仅提高了性能，同时减少了不必要的数据库和应用间的网络传输。

技巧9 使用 EXPLAIN 关键字去查看执行计划。EXPLAIN 可以检查索引使用情况以及扫描的行。