August 27, 2016

程序员容易忽略的SQL Server错误集锦

程序员容易忽略的SQL Server错误集锦

概述

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

步骤

大小写

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

使用“;”

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

（cte表表达式除外）

数据类型

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

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

查询条件不要使用计算列

例如year(createdate)=2014,使用createdate>=’ 20140101’ and createdate<=’ 20141231’来取代。
IF OBJECT_ID('News','U') IS NOT NULL DROPTABLE News GOCREATETABLE News (IDINTNOTNULL PRIMARY KEYIDENTITY(1,1), NAMENVARCHAR(100) NOTNULL, Createdate DATETIME NOTNULL ) 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] GOGOINSERTINTO 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')
—使用计算列查询（走的是索引扫描）
SELECTID,NAME,Createdate FROM News WHEREYEAR(Createdate)=2014
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305158.jpg "程序员容易忽略的SQL Server错误集锦")

—不使用计算列（走的是索引查找）

SELECTID,NAME,Createdate FROM News WHERE CreateDate>='2014-01-01 00:00:00'and CreateDate<'2015-01-01 00:00:00'
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305159.jpg "程序员容易忽略的SQL Server错误集锦")

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

![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305161.jpg "程序员容易忽略的SQL Server错误集锦")

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

程序员容易忽略的SQL Server错误集锦

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

建表时字段不允许为null

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

T-SQL是三值逻辑（true,flase,unknown） IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROPTABLE DBO.Customer GOCREATETABLE DBO.Customer (Customerid intnotnull ); GO IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROPTABLE DBO.OrderS GOCREATETABLE DBO.OrderS (Orderid intnotnull, custid int); GO INSERTINTO Customer VALUES(1),(2),(3); INSERTINTO OrderS VALUES(1,1),(2,2),(3,NULL); ----查询没有订单的顾客SELECT Customerid FROM DBO.Customer WHERE Customerid NOTIN(SELECT custid FROM OrderS); ---分析为什么查询结果没有数据/* 因为true,flase,unknown都是真值 因为not in 是需要结果中返回flase值,not true=flase,not flase=flase,not unknown=unknown 因为null值是unknown所以not unknownn无法判断结果是什么值所以不能返回数据 */--可以将查询语句修改为SELECT Customerid FROM DBO.Customer WHERE Customerid NOTIN(SELECT custid FROM OrderS WHERE custid isnotnull); --或者使用EXISTS，因为EXISTS是二值逻辑只有（true,flase）所以不存在未知。SELECT Customerid FROM DBO.Customer A WHERENOTEXISTS(SELECT custid FROM OrderS WHERE OrderS.custid=A.Customerid ); ---in查询可以返回值，因为in是true，子查询true,flase,unknown都是真值所以可以返回子查询的trueSELECT Customerid FROM DBO.Customer WHERE Customerid IN(SELECT custid FROM 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 DROPTABLE DBO.Customer GOCREATETABLE DBO.Customer (Customerid intnotnull ); GO IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROPTABLE DBO.OrderS GOCREATETABLE DBO.OrderS (Orderid intnotnull, custid int); GO INSERTINTO Customer VALUES(1),(2),(3); INSERTINTO OrderS VALUES(1,1),(2,2),(3,NULL); 例如：需要统计每一个顾客的订单数量 ---如果使用count(*)SELECT Customerid,COUNT(*) FROM Customer TA LEFTJOIN OrderS TB ON TA.Customerid=TB.custid GROUPBY Customerid ;

程序员容易忽略的SQL Server错误集锦

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

----正确的方法是使用count(custid)SELECT Customerid,COUNT(custid) FROM Customer TA LEFTJOIN OrderS TB ON TA.Customerid=TB.custid GROUPBY Customerid;
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305166.jpg "程序员容易忽略的SQL Server错误集锦")
### **子查询的表加上表别名**
IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROPTABLE DBO.Customer GOCREATETABLE DBO.Customer (Customerid intnotnull ); GO IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROPTABLE DBO.OrderS GOCREATETABLE DBO.OrderS (Orderid intnotnull, custid int); GO INSERTINTO Customer VALUES(1),(2),(3); INSERTINTO OrderS VALUES(1,1),(2,2),(3,NULL);
大家发现下面语句有没有什么问题，查询结果是怎样呢？
SELECT Customerid FROM Customer WHERE Customerid IN(SELECT Customerid FROM OrderS WHERE Orderid=2 );
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305167.jpg "程序员容易忽略的SQL Server错误集锦")

正确查询结果下查询出的结果是没有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 CREATETABLETEST (IDINTNOTNULLIDENTITY(1,1), orderdate dateNOTNULLDEFAULT(CURRENT_TIMESTAMP), NAMENVARCHAR(30) NOTNULL, CONSTRAINT CK_TEST_NAME CHECK(NAMELIKE'[A-Za-z]%' ) ); GO INSERTINTO tempdb.DBO.TEST(NAME) VALUES('A中'),('a名'),('Aa'),('ab'),('AA'),('az'); ----4.插入报错后，自增值依旧增加INSERTINTO tempdb.DBO.TEST(NAME) VALUES('中'); GO SELECTIDENT_CURRENT('tempdb.DBO.TEST'); SELECT * FROM tempdb.DBO.TEST; ---插入正常的数据INSERTINTO tempdb.DBO.TEST(NAME) VALUES('cc'); SELECTIDENT_CURRENT('tempdb.DBO.TEST') SELECT * FROM tempdb.DBO.TEST; ----5.显示插入自增值SET IDENTITY_INSERT tempdb.DBO.TEST ONINSERTINTO tempdb.DBO.TEST(ID,NAME) VALUES(8,'A中'); SET IDENTITY_INSERT tempdb.DBO.TEST OFF----会发现ID并不是根据自增值排列的，而且根据插入的顺序排列的SELECTIDENT_CURRENT('tempdb.DBO.TEST'); SELECT * FROM tempdb.DBO.TEST; ----6.插入重复的自增值SET IDENTITY_INSERT tempdb.DBO.TEST ONINSERTINTO tempdb.DBO.TEST(ID,NAME) VALUES(8,'A中'); SET IDENTITY_INSERT tempdb.DBO.TEST OFFSELECTIDENT_CURRENT('tempdb.DBO.TEST') SELECT * FROM tempdb.DBO.TEST; ---所以如果要保证ID是唯一的，单单只设置自增值不行，需要给字段设置主键或者唯一约束DROPTABLE tempdb.DBO.TEST;
### **查询时一定要制定字段查询**

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

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

创建测试数据库

CREATETABLE [Sales].[Customer]( [CustomerID] [int] IDENTITY(1,1) NOTFORREPLICATIONNOTNULL, [PersonID] [int] NULL, [StoreID] [int] NULL, [TerritoryID] [int] NULL, [AccountNumber] AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')), [rowguid] [uniqueidentifier] ROWGUIDCOL NOTNULL, [ModifiedDate] [datetime] NOTNULL, 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 * 查询SETSTATISTICS IO ONSETSTATISTICSTIMEONSELECT * FROM [Sales].[Customer] WHERE PersonID=1; SETSTATISTICSTIMEOFFSETSTATISTICS IO OFF
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305169.jpg "程序员容易忽略的SQL Server错误集锦")

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

---列出需要的字段查询，因为字段不包含不需要的列，所以走索引SETSTATISTICS IO ONSETSTATISTICSTIMEONSELECT CustomerID, [PersonID] ,[StoreID] ,[TerritoryID] ,[AccountNumber] ,[rowguid] FROM [Sales].[Customer] WHERE PersonID=1; SETSTATISTICSTIMEOFFSETSTATISTICS IO OFF
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305170.jpg "程序员容易忽略的SQL Server错误集锦")

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

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

使用存储过程的好处

  1. 减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。
  2. 执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程缓存计划，这样下次再执行同样的存储过程时，可以从内存中直接调用。
  3. 更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。
  4. 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。
  5. 更好的封装移植性。
  6. 安全性，它们可以防止某些类型的 SQL 插入攻击。
PROCEDURE [dbo].[SPSalesPerson] (@option varchar(50)) AS BEGINSET NOCOUNT ONIF @option='select'BEGINSELECT [DatabaseLogID] ,[PostTime] ,[DatabaseUser] ,[Event] ,[Schema] ,[Object] ,[TSQL] ,[XmlEvent] FROM [dbo].[DatabaseLog] ENDIF @option='SalesPerson'BEGINSELECT [BusinessEntityID] ,[TerritoryID] ,[SalesQuota] ,[Bonus] ,[CommissionPct] ,[SalesYTD] ,[SalesLastYear] ,[rowguid] ,[ModifiedDate] FROM [Sales].[SalesPerson] WHERE BusinessEntityID<300ENDSET NOCOUNT OFFEND
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 语句以终止该行为。根据存储过程中包含的影响行的语句的数量，这将删除客户端和服务器之间的一个或多个往返过程。尽管这不是大问题，但它可以为高流量应用程序的性能产生负面影响。
### 判断一条查询是否有值
--以下四个查询都是判断连接查询无记录时所做的操作---性能最差消耗0.8秒SETSTATISTICS IO ONSETSTATISTICSTIMEONDECLARE @UserType INT ,@StatusINTSELECT @UserType=COUNT(c.Id) FROM Customerfo t INNERJOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel='13400000000'IF(@UserType=0) BEGINSET @Status = 2 PRINT @StatusENDSETSTATISTICSTIMEOFFSETSTATISTICS IO OFFgo----性能较好消耗0.08秒 SETSTATISTICS IO ONSETSTATISTICSTIMEONIFNOTEXISTS(SELECT c.Id FROM Customerfo t INNERJOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel='13400000000') BEGINDECLARE @StatusintSET @Status = 2 PRINT @StatusENDSETSTATISTICSTIMEOFFSETSTATISTICS IO OFFgo----性能较好消耗0.08秒 SETSTATISTICS IO ONSETSTATISTICSTIMEONIFNOTEXISTS(SELECT top 1 c.id FROM Customerfo t INNERJOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel='13400000000'ORDERBY NEWID() ) BEGINDECLARE @StatusintSET @Status = 2 PRINT @StatusENDSETSTATISTICSTIMEOFFSETSTATISTICS IO OFFGO---性能和上面的一样0.08秒SETSTATISTICS IO ONSETSTATISTICSTIMEONIFNOTEXISTS(SELECT1FROM Customerfo t INNERJOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel='13410700660' ) BEGINDECLARE @StatusintSET @Status = 2 PRINT @StatusENDSETSTATISTICSTIMEOFFSETSTATISTICS IO OFF 这里说一下SELECT1,之前因为有程序员误认为查询SELECT1无论查询的数据有多少只返回一个1，其实不是这样的，和查询字段是一样的意思只是有多少记录就返回多少个1,1也不是查询的第一个字段。
**理解TRUNCATE和DELETE的区别**
---创建表Table1 IF OBJECT_ID('Table1','U') IS NOT NULL DROPTABLE Table1 GOCREATETABLE Table1 (IDINTNOTNULL, FOID INTNOTNULL) GO--插入测试数据INSERTINTO Table1 VALUES(1,101),(2,102),(3,103),(4,104) GO---创建表Table2IF OBJECT_ID('Table2','U') ISNOTNULLDROPTABLE Table2 GOCREATETABLE Table2 ( FOID INTNOTNULL) GO--插入测试数据INSERTINTO Table2 VALUES(101),(102),(103),(104) GOSELECT * FROM Table1 GOSELECT * FROM Table2 GO 在Table1表中创建触发器,当表中的数据被删除时同时删除Table2表中对应的FOID CREATETRIGGER TG_Table1 ON Table1 AFTERDELETEASBEGINDELETEFROM TA FROM Table2 TA INNERJOIN deleted TB ON TA.FOID=TB.FOID ENDGO---测试DELETE删除操作DELETEFROM Table1 WHEREID=1GO---执行触发器成功,Table2表中的FOID=101的数据也被删除SELECT * FROM Table1 GOSELECT * FROM Table2 ---测试TRUNCATE删除操作TRUNCATETABLE Table1 GO---Table2中的数据没有被删除SELECT * FROM Table1 GOSELECT * FROM Table2
---查看TRUNCATE和DELETE的日志记录情况 CHECKPOINT GO SELECT * FROM fn_dblog(NULL,NULL) GODELETEFROM Table2 WHERE FOID=102GOSELECT * FROM fn_dblog(NULL,NULL)
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305172.jpg "程序员容易忽略的SQL Server错误集锦")
---测试TRUNCATE删除操作TRUNCATETABLE Table1 GO---Table2中的数据没有被删除SELECT * FROM Table1 GOSELECT * FROM Table2
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305173.jpg "程序员容易忽略的SQL Server错误集锦")
---查看TRUNCATE和DELETE的日志记录情况 CHECKPOINT GO SELECT * FROM fn_dblog(NULL,NULL) GODELETEFROM Table2 WHERE FOID=102GOSELECT * FROM fn_dblog(NULL,NULL)
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305174.jpg "程序员容易忽略的SQL Server错误集锦")

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

----TRUNCATE日志记录 CHECKPOINT GO SELECT * FROM fn_dblog(NULL,NULL) GOTRUNCATETABLE Table2 GOSELECT * FROM fn_dblog(NULL,NULL) GO
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305176.jpg "程序员容易忽略的SQL Server错误集锦")

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

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

事务的理解

---创建表Table1 IF OBJECT_ID('Table1','U') IS NOT NULL DROPTABLE Table1 GOCREATETABLE Table1 (IDINTNOTNULL PRIMARY KEY, Age INTNOTNULLCHECK(Age>10AND Age<50)); GO ---创建表Table2 IF OBJECT_ID('Table2','U') IS NOT NULL DROPTABLE Table2 GOCREATETABLE Table2 ( IDINTNOTNULL) GO
1.简单的事务提交
BEGINTRANSACTIONINSERTINTO Table1(ID,Age) VALUES(1,20) INSERTINTO Table1(ID,Age) VALUES(2,5) INSERTINTO Table1(ID,Age) VALUES(2,20) INSERTINTO Table1(ID,Age) VALUES(3,20) COMMITTRANSACTIONGO---第二条记录没有执行成功,其他的都执行成功SELECT * FROM Table1 所以并不是事务中的任意一条语句报错整个事务都会回滚，其它的可执行成功的语句依然会执行成功并提交。
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305177.jpg "程序员容易忽略的SQL Server错误集锦")

2.TRY…CATCH

DELETEFROM Table1 BEGIN TRY BEGINTRANSACTIONINSERTINTO Table1(ID,Age) VALUES(1,20) INSERTINTO Table1(ID,Age) VALUES(2,20) INSERTINTO Table1(ID,Age) VALUES(3,20) INSERTINTO Table3 VALUES(1) COMMITTRANSACTIONEND TRY BEGIN CATCH ROLLBACKTRANSACTIONEND CATCH ----重新打开一个回话执行查询，发现由于存在对象出错BEGIN CATCH并没有收到执行报错，且事务一直处于打开状态，没有被提交，也没有执行回滚。SELECT * FROM Table1 ---如果事务已经提交查询XACT_STATE()的状态值是0,或者执行DBCC OPENTRANSELECT XACT_STATE() DBCC OPENTRAN ---手动执行提交或者回滚操作ROLLBACKTRANSACTION
TRY…CATCH不会返回对象错误或者字段错误等类型的错误

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

3.打开XACT_ABORT

SET XACT_ABORT ONBEGINTRANSACTIONINSERTINTO Table1(ID,Age) VALUES(1,20) INSERTINTO Table1(ID,Age) VALUES(2,20) INSERTINTO Table1(ID,Age) VALUES(3,20) INSERTINTO Table3 VALUES(1) COMMITTRANSACTIONSET XACT_ABORT OFF---事务全部执行回滚操作(对象table3是不存在报错，但是也回滚所有的提交，跟上面的TRY...CATCH的区别)SELECT * FROM Table1
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305179.jpg "程序员容易忽略的SQL Server错误集锦")
---查询是否有打开事务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. BEGINTRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITBEGINTRANSACTIONGOCREATETABLE dbo.Tmp_Address ( IDintNOTNULL, Address nvarchar(MAX) NOTNULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GOALTERTABLE dbo.Tmp_Address SET (LOCK_ESCALATION = TABLE) GOIFEXISTS(SELECT * FROM dbo.Address) EXEC('INSERT INTO dbo.Tmp_Address (ID, Address) SELECT ID, Address FROM dbo.Address WITH (HOLDLOCK TABLOCKX)') GODROPTABLE dbo.Address GOEXECUTE sp_rename N'dbo.Tmp_Address', N'Address', 'OBJECT'GOCOMMIT---从上面就是一个重置字段为非空的过程，从上面的语句我们可以看到首先要创建一张临时表在临时表中Address字段建成了NOT NULL，然后将原表中的数据插入到临时表当中，最后修改表名，大家可以想一下如果我要修改的表有几千万数据，那这个过程该多么长而且内存一下子就会增加很多，所以大家建表的时候就要养成设字段为NOT NULL--当你要向现有的表中增加一个字段的时候你也要不允许为NULL，可以用默认值替代空AlterTable Address AddTypesmallintNotNullDefault (1)
### 条件字段的先后顺序

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

---创建测试表 IF OBJECT_ID('TAINFO','U')IS NOT NULL DROPTABLE TAINFO GOCREATETABLE [dbo].[TAINFO]( IDINTNOTNULL PRIMARY KEYIDENTITY(1,1), OIDINTNOTNULL, Stats SMALLINTCHECK (Stats IN(1,2)), MAC uniqueidentifier NOTNULL ) ON [PRIMARY] GO---插入测试数据INSERTINTO 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
如果这是你的写的查询语句
SELECTID,OID,Stats MAC FROM TAINFO WHERE MAC='46B550F9-6E24-436D-9BC7-F0650F562E54'AND STATS=1ANDOID=102
我现在根据你的查询语句创建一条索引
CREATEINDEX IX2_TAINFO ON TAINFO(MAC,STATS,OID)
分别执行三条查询语句
---1.WHERE条件是索引字段且查询字段也是索引字段SELECTID,OID,Stats MAC FROM TAINFO WHERE MAC='46B550F9-6E24-436D-9BC7-F0650F562E54'AND STATS=1ANDOID=102--2.WHERE 条件是索引的部分字段(这条语句或许是平时查询该表用到的最多的一条语句)SELECTID,OID,Stats MAC FROM TAINFO WHEREOID=102AND STATS=1--3.WHERE 条件是索引的部分字段SELECTID,OID,Stats MAC FROM TAINFO WHERE STATS=1
执行计划分别为

程序员容易忽略的SQL Server错误集锦

程序员容易忽略的SQL Server错误集锦

程序员容易忽略的SQL Server错误集锦

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

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

--创建索引DROPINDEX IX2_TAINFO ON TAINFO GOCREATEINDEX IX1_TAINFO ON TAINFO(OID) INCLUDE(STATS,MAC) GO
依然执行前面的三条查询语句分析执行计划

程序员容易忽略的SQL Server错误集锦

程序员容易忽略的SQL Server错误集锦

程序员容易忽略的SQL Server错误集锦

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

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

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

理解外连接

---创建测试表 IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROPTABLE DBO.OrderS GOCREATETABLE DBO.OrderS (Orderid INTNOTNULL, custid INTNOTNULL, stats INTNOTNULL); GO IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROPTABLE DBO.Customer GOCREATETABLE DBO.Customer (Customerid INTNOTNULL ); GO ---插入测试数据INSERTINTO OrderS VALUES(1,101,0),(2,102,0),(3,103,1),(4,104,0); GO INSERTINTO Customer VALUES(101),(102),(103); ----查询OrderS 表中stats不等于1且不在Customer 表中的数据SELECT TA.Orderid,TA.custid,TA.stats,TB.Customerid FROM OrderS TA LEFTJOIN Customer TB ON TA.stats<>'1'AND TA.custid=TB.Customerid WHERE TB.Customerid ISNULL
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305190.jpg "程序员容易忽略的SQL Server错误集锦")

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

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

----查询OrderS 表中stats不等于1且不在Customer 表中的数据SELECT TA.Orderid,TA.custid,TA.stats,TB.Customerid FROM OrderS TA LEFTJOIN Customer TB ON TA.custid=TB.Customerid WHERE TA.stats<>'1'AND TB.Customerid ISNULL
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305191.jpg "程序员容易忽略的SQL Server错误集锦")

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

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

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

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

IF OBJECT_ID('Person','u')IS NOT NULL DROPTABLE Person GOCREATETABLE Person (IDINTNOTNULL PRIMARY KEYIDENTITY(1,1), Phone NVARCHAR(20) NOTNULL, CreateDate DATETIME NOTNULL ) ---插入测试数据INSERTINTO Person(Phone,CreateDate) VALUES('13700000000',GETDATE()),('13700000000',GETDATE()),('13800000000',GETDATE()) ---创建索引CREATEINDEX IX_Person ON Person(Phone,CreateDate)
1.谓词类型与字段类型不一致
SELECTIDFROM Person WHERE Phone=13700000000ANDDATEDIFF(DAY,CreateDate,GETDATE())=0
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/201608271472305193.jpg "程序员容易忽略的SQL Server错误集锦")

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

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

SELECTIDFROM Person WHERE Phone='13700000000'ANDDATEDIFF(DAY,CreateDate,GETDATE())=0
![程序员容易忽略的SQL Server错误集锦](https://blog.lcrun.com/files/2016/08/-e7-a8-8b-e5-ba-8f-e5-91-98-e5-ae-b9-e6-98-93-e5-bf-bd-e7-95-a5-e7-9a-84sql-server-e9-94-99-e8-af-af-e9-9b-86-e9-94-a6.jpg "程序员容易忽略的SQL Server错误集锦")

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

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