SQL Server 致程序员(容易忽略的错误)

概述

因为每天需要审核程序员发布的SQL语句,所以收集了一些程序员的一些常见问题,还有一些平时收集的其它一些问题,这也是很多人容易忽视的问题,在以后收集到的问题会补充在文章末尾,欢迎关注,由于收集的问题很多是针对于生产数据,测试且数据量比较大,这里就不把数据共享出来了,大家理解意思就行。

 

步骤

大小写

大写T-SQL 语言的所有关键字都使用大写,规范要求。

使用“;”

使用“;”作为 Transact-SQL 语句终止符。虽然分号不是必需的,但使用它是一种好的习惯,对于合并操作MERGE语句的末尾就必须要加上“;”

(cte表表达式除外)

数据类型

避免使用ntext、text 和 image 数据类型,用 nvarchar(max)、varchar(max) 和 varbinary(max)替代

后续版本会取消ntext、text 和 image 该三种类型

 

查询条件不要使用计算列

复制代码

例如year(createdate),使用createdate’ ’  createdate’

复制代码

IF OBJECT_ID('News','U') IS NOT NULL DROP TABLE NewsGOCREATE TABLE News
(ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
NAME NVARCHAR(100) NOT NULL,
Createdate DATETIME NOT NULL)GOCREATE NONCLUSTERED INDEX [IX1_News] ON [dbo].[News] (    [Createdate] ASC)
INCLUDE ( [NAME]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]GOGOINSERT INTO News(NAME,Createdate) 
VALUES( '新闻','2014-08-20 00:00:00'),( '新闻','2014-08-20 00:00:00'),( '新闻','2014-08-20 00:00:00'),( '新闻','2014-08-20 00:00:00')

复制代码


—使用计算列查询(走的是索引扫描)

SELECT ID,NAME,Createdate FROM NewsWHERE YEAR(Createdate)=2014

—不使用计算列(走的是索引查找)

SELECT ID,NAME,Createdate FROM NewsWHERE CreateDate>='2014-01-01 00:00:00' and CreateDate<'2015-01-01 00:00:00'

对比两个查询显然绝大部分情况下走索引查找的查询性能要高于走索引扫描,特别是查询的数据库不是非常大的情况下,索引查找的消耗时间要远远少于索引扫描的时间,如果想详细了解索引的体系结构可以查看了我前面写的几篇关于聚集、非聚集、堆的索引体系机构的文章。

复制代码

 

请参看:http://www.cnblogs.com/chenmh/p/3780221.html

 请参看:http://www.cnblogs.com/chenmh/p/3782397.html

 

建表时字段不允许为null


      发现很多人在建表的时候不会注意这一点,在接下来的工作中当你需要查询数据的时候你往往需要在WHERE条件中多加一个判断条件IS NOT NULL,这样的一个条件不仅仅增加了额外的开销,而且对查询的性能产生很大的影响,有可能就因为多了这个查询条件导致你的查询变的非常的慢;还有一个比较重要的问题就是允许为空的数据可能会导致你的查询结果出现不准确的问题,接下来我们就举个例子讨论一下。

复制代码

T (,)         (,)          Customer (),(),(  OrderS (,),(,),(, Customerid  DBO.Customer  Customerid  ( custid  Customerid  DBO.Customer  Customerid  ( custid  OrderS  custid    Customerid  DBO.Customer A    ( custid  OrderS  OrderS.custid Customerid  DBO.Customer  Customerid  ( custid  OrderS);


----如果整形字段可以赋0,字符型可以赋值空(这里只是给建议)这里的空和NULL是不一样的意思
--增加整形字段可以这样写ALTER TABLE TABLE_NAME ADD  COLUMN_NAME  INT NOT NULL DEFAULT(0)--增加字符型字段可以这样写ALTER TABLE TABLE_NAME ADD  COLUMN_NAME  NVARCHAR(50) NOT NULL DEFAULT('')

 

复制代码

 

分组统计时避免使用count(*)

复制代码

IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROP TABLE DBO.CustomerGOCREATE TABLE DBO.Customer
(Customerid int not null );GOIF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROP TABLE DBO.OrderSGOCREATE TABLE DBO.OrderS
(Orderid int not null,
custid int);GOINSERT INTO Customer VALUES(1),(2),(3);INSERT INTO OrderS VALUES(1,1),(2,2),(3,NULL);
例如:需要统计每一个顾客的订单数量---如果使用count(*)SELECT Customerid,COUNT(*) FROM Customer TA LEFT JOIN OrderS TB ON TA.Customerid=TB.custid 
GROUP BY Customerid ;

实际情况customerid=3是没有订单的,数量应该是0,但是结果是1,count()里面的字段是左连接右边的表字段,如果你用的是主表字段结果页是错误的。

----正确的方法是使用count(custid)SELECT Customerid,COUNT(custid) FROM Customer TA LEFT JOIN OrderS TB ON TA.Customerid=TB.custid 
GROUP BY Customerid;

 

复制代码

 

子查询的表加上表别名

复制代码

IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROP TABLE DBO.CustomerGOCREATE TABLE DBO.Customer
(Customerid int not null );GOIF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROP TABLE DBO.OrderSGOCREATE TABLE DBO.OrderS
(Orderid int not null,
custid int);GOINSERT INTO Customer VALUES(1),(2),(3);INSERT INTO OrderS VALUES(1,1),(2,2),(3,NULL);

复制代码

大家发现下面语句有没有什么问题,查询结果是怎样呢?

SELECT Customerid FROM Customer WHERE Customerid IN(SELECT Customerid FROM OrderS WHERE Orderid=2 );


正确查询结果下查询出的结果是没有customerid为3的值

为什么结果会这样呢?

大家仔细看应该会发现子查询的orders表中没有Customerid字段,所以SQL取的是Customer表的Customerid值作为相关子查询的匹配字段。

所以我们应该给子查询加上表别名,如果加上表别名,如果字段错误的话会有错误标示

 正确的写法:

SELECT Customerid  FROM Customer WHERE Customerid IN(SELECT tb.custid   FROM OrderS tb WHERE Orderid=2 );

建立自增列时单独再给自增列添加唯一约束

复制代码

复制代码

USE tempdb 
CREATE TABLE TEST
(ID INT NOT NULL IDENTITY(1,1),
orderdate date NOT NULL DEFAULT(CURRENT_TIMESTAMP),
NAME NVARCHAR(30) NOT NULL,CONSTRAINT CK_TEST_NAME CHECK(NAME LIKE '[A-Za-z]%' ) 
);GOINSERT INTO tempdb.DBO.TEST(NAME)VALUES('A中'),('a名'),('Aa'),('ab'),('AA'),('az');----4.插入报错后,自增值依旧增加INSERT INTO tempdb.DBO.TEST(NAME)VALUES('中');GOSELECT IDENT_CURRENT('tempdb.DBO.TEST');SELECT * FROM tempdb.DBO.TEST;---插入正常的数据INSERT INTO tempdb.DBO.TEST(NAME)VALUES('cc');SELECT IDENT_CURRENT('tempdb.DBO.TEST')SELECT * FROM tempdb.DBO.TEST;----5.显示插入自增值SET IDENTITY_INSERT tempdb.DBO.TEST ONINSERT INTO tempdb.DBO.TEST(ID,NAME)VALUES(8,'A中');SET IDENTITY_INSERT tempdb.DBO.TEST OFF----会发现ID并不是根据自增值排列的,而且根据插入的顺序排列的SELECT IDENT_CURRENT('tempdb.DBO.TEST');SELECT * FROM tempdb.DBO.TEST;----6.插入重复的自增值SET IDENTITY_INSERT tempdb.DBO.TEST ONINSERT INTO tempdb.DBO.TEST(ID,NAME)VALUES(8,'A中');SET IDENTITY_INSERT tempdb.DBO.TEST OFFSELECT IDENT_CURRENT('tempdb.DBO.TEST')SELECT * FROM tempdb.DBO.TEST;---所以如果要保证ID是唯一的,单单只设置自增值不行,需要给字段设置主键或者唯一约束DROP TABLE tempdb.DBO.TEST;

复制代码

 

复制代码

 

查询时一定要制定字段查询

l  查询时一定不能使用”*”来代替字段来进行查询,无论你查询的字段有多少个,就算字段太多无法走索引也避免了解析”*”带来的额外消耗。

l  查询字段值列出想要的字段,避免出现多余的字段,字段越多查询开销越大而且可能会因为多列出了某个字段而引起查询不走索引。

创建测试数据库

复制代码

CREATE TABLE [Sales].[Customer](    [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,    [PersonID] [int] NULL,    [StoreID] [int] NULL,    [TerritoryID] [int] NULL,    [AccountNumber]  AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')),    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,    [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED (    [CustomerID] 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 NONCLUSTERED INDEX [IX1_Customer] ON [Sales].[Customer] (    [PersonID] ASC)
INCLUDE ( [StoreID],[TerritoryID],[AccountNumber],[rowguid]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]GO

复制代码

查询测试

复制代码

---使用SELECT  * 查询SET STATISTICS IO ONSET STATISTICS TIME ONSELECT  *  FROM [Sales].[Customer]WHERE PersonID=1; SET STATISTICS TIME OFF
 SET STATISTICS IO OFF

复制代码

由于建的索引‘IX1_Customer’没有包含ModifiedDate字段,所以需要通过键查找去聚集索引中获取该字段的值

复制代码

 ---列出需要的字段查询,因为字段不包含不需要的列,所以走索引SET STATISTICS IO ONSET STATISTICS TIME ON  
  SELECT CustomerID, 
       [PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
  FROM [Sales].[Customer]
  WHERE PersonID=1; SET STATISTICS TIME OFF
 SET STATISTICS IO OFF

复制代码

由于查询语句中没有对ModifiedDate字段进行查询,所以只走索引查找就可以查询到需要的数据,所以建议在查询语句中列出你需要的字段而不是为了方便用*来查询所有的字段,如果真的

需要查询所有的字段也同样建议把所有的字段列出来取代‘*’。

 

使用存储过程的好处

  1. 减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL语句,那么其性能绝对比一条一条的调用SQL语句要高得多。

  2. 执行速度更快。有两个原因:首先,在存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程缓存计划,这样下次再执行同样的存储过程时,可以从内存中直接调用。

  3. 更强的适应性:由于存储过程对数据库的访问是通过存储过程来进行的,因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动,而这些改动不会对应用程序造成影响。

  4. 布式工作:应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。

  5. 更好的封装移植性。

  6. 安全性,它们可以防止某些类型的 SQL 插入攻击。

复制代码

PROCEDURE [dbo].[SPSalesPerson](@option varchar(50))ASBEGINSET NOCOUNT ONIF @option='select'
    BEGIN
    SELECT [DatabaseLogID]
          ,[PostTime]
          ,[DatabaseUser]
          ,[Event]
          ,[Schema]
          ,[Object]
          ,[TSQL]
          ,[XmlEvent]
      FROM [dbo].[DatabaseLog]
      ENDIF @option='SalesPerson'
   BEGIN
   SELECT [BusinessEntityID]
      ,[TerritoryID]
      ,[SalesQuota]
      ,[Bonus]
      ,[CommissionPct]
      ,[SalesYTD]
      ,[SalesLastYear]
      ,[rowguid]
      ,[ModifiedDate]
   FROM [Sales].[SalesPerson]
   WHERE BusinessEntityID<300
   ENDSET NOCOUNT OFF  END

复制代码

复制代码

EXEC SPSalesPerson @option='select'EXEC SPSalesPerson @option='SalesPerson'DBCC FREEPROCCACHE----清空缓存---测试两个查询是否都走了缓存计划SELECT usecounts,size_in_bytes,cacheobjtype,objtype,TEXT FROM  sys.dm_exec_cached_plans  cp  
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st;--执行计划在第一次执行SQL语句时产生,缓存在内存中,这个缓存的计划一直可用,直到 SQL Server 重新启动,或直到它由于使用率较低而溢出内存。默认情况下,存储过程将返回过程中每个语句影响的行数。如果不需要在应用程序中使用该信息(大多数应用程序并不需要),请在存储过程中使用 SET NOCOUNT ON 语句以终止该行为。根据存储过程中包含的影响行的语句的数量,这将删除客户端和服务器之间的一个或多个往返过程。尽管这不是大问题,但它可以为高流量应用程序的性能产生负面影响。

复制代码

判断一条查询是否有值

复制代码

  IO    TIME    ,   (c.Id)  Customerfo t   Customer c  c.Id c.customerTel
            (
                         
                      
              TIME       IO             
          IO    TIME 
          ( c.Id  Customerfo t   Customer c  c.Idt.CustomerId  c.customerTel
      
         
     
    
          TIME       IO 
          IO    TIME 
          (   c.id  Customerfo t   Customer c  c.Idt.CustomerId  c.customerTel
          
      
         
     
    
          TIME       IO     
              IO    TIME 
        
      (    Customerfo t   Customer c  c.Idt.CustomerId  c.customerTel
      
         
     
    
          TIME       IO     

这里说一下SELECT 1,之前因为有程序员误认为查询SELECT 1无论查询的数据有多少只返回一个1,其实不是这样的,和查询字段是一样的意思只是有多少记录就返回多少个1,1也不是查询的第一个字段。

复制代码

 理解TRUNCATE和DELETE的区别

复制代码

 (,)         --插入测试数据 (,),(,),(,),(, (,)
--插入测试数据
 (),(),(),(
在Table1表中创建触发器,当表中的数据被删除时同时删除Table2表中对应的FOID

复制代码

CREATE TRIGGER TG_Table1 ON Table1
AFTER DELETEASBEGIN
  DELETE FROM TA FROM Table2 TA INNER JOIN deleted TB ON TA.FOID=TB.FOID 
ENDGO

复制代码



复制代码

---测试DELETE删除操作DELETE FROM Table1 WHERE ID=1GO---执行触发器成功,Table2表中的FOID=101的数据也被删除SELECT * FROM Table1GOSELECT * FROM Table2

复制代码

复制代码

---测试TRUNCATE删除操作TRUNCATE TABLE Table1GO---Table2中的数据没有被删除SELECT * FROM Table1GOSELECT * FROM Table2

复制代码

复制代码

复制代码

---查看TRUNCATE和DELETE的日志记录情况CHECKPOINTGOSELECT * FROM fn_dblog(NULL,NULL)GODELETE FROM Table2WHERE FOID=102GOSELECT * FROM fn_dblog(NULL,NULL)

复制代码

  在第四行记录有一个lop_delete_rows,lcx_heap的删除操作日志记录

复制代码

----TRUNCATE日志记录CHECKPOINTGOSELECT * FROM fn_dblog(NULL,NULL)GOTRUNCATE TABLE Table2GOSELECT * FROM fn_dblog(NULL,NULL)GO

复制代码

 TRUNCATE操作没有记录删除日志操作

主要的原因是因为TRUNCATE操作不会激活触发器,因为TRUNCATE操作不会记录各行的日志删除操作,所以当你需要删除一张表的数据时你需要考虑是否应该如有记录日志删除操作,而不是根据个人的习惯来操作。

 

事务的理解

复制代码

---创建表Table1IF OBJECT_ID('Table1','U') IS NOT NULLDROP TABLE Table1GOCREATE TABLE Table1
(ID INT NOT NULL PRIMARY KEY,
Age INT NOT NULL CHECK(Age>10 AND Age<50));GO---创建表Table2IF OBJECT_ID('Table2','U') IS NOT NULLDROP TABLE Table2GOCREATE TABLE Table2
(
ID INT NOT NULL)GO

复制代码

1.简单的事务提交

复制代码

  (, (, (, (,    Table1
所以并不是事务中的任意一条语句报错整个事务都会回滚,其它的可执行成功的语句依然会执行成功并提交。

复制代码

2.TRY…CATCH

复制代码

   (, (, (, (

复制代码

TRY...CATCH不会返回对象错误或者字段错误等类型的错误

想详细了解TRY…CATCH请参考http://www.cnblogs.com/chenmh/articles/4012506.html

 

3.打开XACT_ABORT

复制代码

SET XACT_ABORT ONBEGIN TRANSACTIONINSERT INTO Table1(ID,Age)VALUES(1,20)INSERT INTO Table1(ID,Age)VALUES(2,20)INSERT INTO Table1(ID,Age)VALUES(3,20)INSERT INTO Table3VALUES(1) 
COMMIT TRANSACTIONSET XACT_ABORT OFF---事务全部执行回滚操作(对象table3是不存在报错,但是也回滚所有的提交,跟上面的TRY...CATCH的区别)SELECT * FROM Table1

复制代码

复制代码

---查询是否有打开事务SELECT XACT_STATE()DBCC OPENTRAN
未查询到有打开事务

当 SET XACT_ABORT 为 ON 时,如果执行 Transact-SQL 语句产生运行时错误,则整个事务将终止并回滚。

当 SET XACT_ABORT 为 OFF 时,有时只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。如果错误很严重,那么即使 SET XACT_ABORT 为 OFF,也可能回滚整个事务。OFF 是默认设置。

编译错误(如语法错误)不受 SET XACT_ABORT 的影响。

复制代码

      所以我们应该根据自己的需求选择正确的事务。

    

修改字段NOT NULL的过程

复制代码

在Address表中的有一个Address字段,该字段允许为NULL,现在需要将其修改为NOT NULL.BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGIN TRANSACTIONGOCREATE TABLE dbo.Tmp_Address
    (
    ID int NOT NULL,
    Address nvarchar(MAX) NOT NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]GOALTER TABLE dbo.Tmp_Address SET (LOCK_ESCALATION = TABLE)GOIF EXISTS(SELECT * FROM dbo.Address)     EXEC('INSERT INTO dbo.Tmp_Address (ID, Address)
        SELECT ID, Address FROM dbo.Address WITH (HOLDLOCK TABLOCKX)')GODROP TABLE dbo.AddressGOEXECUTE sp_rename N'dbo.Tmp_Address', N'Address', 'OBJECT' GOCOMMIT---从上面就是一个重置字段为非空的过程,从上面的语句我们可以看到首先要创建一张临时表在临时表中Address字段建成了NOT NULL,然后将原表中的数据插入到临时表当中,最后修改表名,大家可以想一下如果我要修改的表有几千万数据,那这个过程该多么长而且内存一下子就会增加很多,所以大家建表的时候就要养成设字段为NOT NULL--当你要向现有的表中增加一个字段的时候你也要不允许为NULL,可以用默认值替代空Alter Table Address Add Type smallint Not Null Default (1)

复制代码

 条件字段的先后顺序

你平时在写T_SQL语句的时候WHERE条件后面的字段的先后顺序你有注意吗?

复制代码

---创建测试表 IF OBJECT_ID('TAINFO','U')IS NOT NULL DROP TABLE TAINFOGOCREATE TABLE [dbo].[TAINFO](
ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
OID INT NOT NULL,
Stats SMALLINT CHECK (Stats IN(1,2)),
MAC uniqueidentifier NOT NULL) ON [PRIMARY]GO---插入测试数据INSERT INTO TAINFO(OID,Stats,MAC) VALUES(101,1,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(101,2,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(102,1,'46B550F9-6E24-436D-9BC7-F0650F562E54'),
 (102,2,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(103,2,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(103,2,'46B550F9-6E24-436D-9BC7-F0650F562E54'),
 (103,1,'46B550F9-6E24-436D-9BC7-F0650F562E54'),(103,1,'46B550F9-6E24-436D-9BC7-F0650F562E54') GO

复制代码

 

如果这是你的写的查询语句

 SELECT ID,OID,Stats MAC FROM TAINFO WHERE MAC='46B550F9-6E24-436D-9BC7-F0650F562E54' AND STATS=1  AND OID=102

我现在根据你的查询语句创建一条索引

CREATE INDEX IX2_TAINFO ON TAINFO(MAC,STATS,OID)

分别执行三条查询语句

 ---1.WHERE条件是索引字段且查询字段也是索引字段
 SELECT ID,OID,Stats MAC FROM TAINFO WHERE MAC='46B550F9-6E24-436D-9BC7-F0650F562E54' AND STATS=1  AND OID=102  
 --2.WHERE 条件是索引的部分字段(这条语句或许是平时查询该表用到的最多的一条语句)
 SELECT ID,OID,Stats MAC FROM TAINFO WHERE OID=102  AND STATS=1
 --3.WHERE 条件是索引的部分字段
  SELECT ID,OID,Stats MAC FROM TAINFO WHERE STATS=1

执行计划分别为

 从上面三天查询语句可以看出,只有第一条语句走的是索引查找,另外两条语句走的是索引扫描,而我们从字段的名称应该可以看的出OID字段应该是该表的一个外键字段也是经常会被用作查询的字段。

接下来我们重新换一下索引顺序

 --创建索引DROP INDEX IX2_TAINFO ON TAINFOGOCREATE INDEX IX1_TAINFO ON TAINFO(OID)
INCLUDE(STATS,MAC)GO

依然执行前面的三条查询语句分析执行计划

 分析执行计划前面两条查询语句都走的是索引查找,第三条查询的是索引扫描,而根据一般单独用第三条查询的业务应该不会常见,所以现在一条索引解决了两个常用查询的索引需求,避免了建两条索引的必要(所以当你建索引的时候索引的顺序很重要,一般把查询最频繁的字段设第一个字段,可以避免建多余的索引)。

为什么要把这个问题提出来呢,因为平时有遇到程序员在写查询语句的时候对于同一个查询条件每次的写法都不一样,往往是根据自己想到哪个字段就写哪个字段先,这样的习惯往往是不好的,就好比上面的例子如果别人看到你的查询条件建一个索引也是这样写的话往往一个表会出现很多多余的索引(或许有人会说DBA建好索引的顺序就好了,这里把这个因素排除吧),像后面的那个索引就解决了两个查询的需求。

所以这里我一般是这样规定where条件的,对于经常用作查询的字段放在第一个位置(比如上面例子的OID),其它的字段根据表的实际字段顺序排列,这样往往你的查询语句走索引的概率会更大。

 

 理解外连接

复制代码

---创建测试表IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROP TABLE DBO.OrderSGOCREATE TABLE DBO.OrderS
(Orderid INT NOT NULL,
custid INT NOT NULL,
stats INT NOT NULL);GOIF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROP TABLE DBO.CustomerGOCREATE TABLE DBO.Customer
(Customerid INT NOT NULL );GO---插入测试数据INSERT INTO OrderS VALUES(1,101,0),(2,102,0),(3,103,1),(4,104,0);GOINSERT INTO Customer VALUES(101),(102),(103);----查询OrderS 表中stats不等于1且不在Customer 表中的数据SELECT TA.Orderid,TA.custid,TA.stats,TB.Customerid  FROM OrderS TA LEFT JOIN Customer TB ON TA.stats<>'1' AND TA.custid=TB.Customerid 
WHERE TB.Customerid IS NULL

复制代码

看到这结果是不是有点疑惑,我在连接条件里面写了TA.stats<>'1',为什么结果还会查询出。

接下来我们换一种写法吧!

----查询OrderS 表中stats不等于1且不在Customer 表中的数据SELECT TA.Orderid,TA.custid,TA.stats,TB.Customerid  FROM OrderS TA LEFT JOIN Customer TB ON  TA.custid=TB.Customerid 
WHERE TA.stats<>'1' AND TB.Customerid IS NULL

 接下来我就解释一下原因:对于外连接,连接条件不会改变主表的数据,即不会删减主表的数据

对于上面的查询主表是orders,所以无论你在连接条件on里面怎样设置主表的条件都不影响主表数据的输出,影响主表数据的输出只在where条件里,where条件影响最后数据的输出。而对于附表Customer 的条件就应该写在连接条件(on)里而不是where条件里,这里说的是外连接(包括左连接和右连接)。

对于inner join就不存在这种情况,无论你的条件是写在where后面还是on后面都是一样的,但是还是建议写在where后面。

谓词类型要与字段类型对齐

复制代码

IF OBJECT_ID('Person','u')IS NOT NULL DROP TABLE PersonGOCREATE TABLE Person
(ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
Phone NVARCHAR(20) NOT NULL,
CreateDate DATETIME NOT NULL)---插入测试数据INSERT INTO Person(Phone,CreateDate)VALUES('13700000000',GETDATE()),('13700000000',GETDATE()),('13800000000',GETDATE())---创建索引CREATE INDEX IX_Person ON Person(Phone,CreateDate)

复制代码

1.谓词类型与字段类型不一致

SELECT ID FROM Person WHERE Phone=13700000000 AND DATEDIFF(DAY,CreateDate,GETDATE())=0

由于定义表的phone字段类型是字符型,而上面的查询条件phone写成了整形,导致执行计划走了索引扫描,且执行计划select也有提示。

 2.谓词类型与字段类型一致

SELECT ID FROM Person WHERE Phone='13700000000' AND DATEDIFF(DAY,CreateDate,GETDATE())=0

      第二种查询phone谓词类型与字段类型一致,所以查询走了索引查找

在日常的语句编写过程中需要注意这类问题,这将直接影响性能。

避免使用长字节字段排序

 SELECT O.name,O.create_date,C.name  FROM SYS.columns C INNER JOIN SYS.objects  O ON C.object_id=O.object_id  ORDER BY O.create_date DESC GO SELECT O.name,O.create_date,C.name  FROM SYS.columns C INNER JOIN SYS.objects  O ON C.object_id=O.object_id  ORDER BY O.object_id  DESC

 

上面的语句查询结果是一样的,只是写法不一样,O.create_date是表的创建时间而object_id 是一个自增值根据两者的倒序排序得到的结果是一样的,但是二者的执行效率却不一样。无论是从执行时间还是执行计划明显是后者的效率要好,从执行计划可以看出后者的不需要进行排序操作因为object_id 本身就是排序好的,而且object_id 是整形而create_date是时间类型,如果是两个大表进行连接操作再进行排序效率更明显甚至前面用时间排序还可能查询很久不出来。

总结

后面收集到类似的问题会补充在文章的末尾,文章持续更新中….,欢迎关注讨论。

  

如果觉得文章对大家有所帮助,麻烦给个推荐,谢谢!!

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

《欢迎交流讨论》

分类: SQL Server 触发器

Leave a Reply