[转] SQL Server 致程序员（容易忽略的错误）

首页 > 代码库 > [转] SQL Server 致程序员（容易忽略的错误）

[转] SQL Server 致程序员（容易忽略的错误）

2024-08-04 06:09:46 234人阅读

转自：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 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-SQL是三值逻辑（true,flase,unknown）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 DBO.Customer WHERE Customerid NOT IN(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 NOT IN(SELECT custid FROM OrderS WHERE custid is not null);--或者使用EXISTS，因为EXISTS是二值逻辑只有（true,flase）所以不存在未知。SELECT Customerid FROM DBO.Customer A WHERE  NOT EXISTS(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 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字段进行查询，所以只走索引查找就可以查询到需要的数据，所以建议在查询语句中列出你需要的字段而不是为了方便用*来查询所有的字段，如果真的

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

使用存储过程的好处

减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。
执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程缓存计划，这样下次再执行同样的存储过程时，可以从内存中直接调用。
更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。
布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。
更好的封装移植性。
安全性，它们可以防止某些类型的 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 语句以终止该行为。根据存储过程中包含的影响行的语句的数量，这将删除客户端和服务器之间的一个或多个往返过程。尽管这不是大问题，但它可以为高流量应用程序的性能产生负面影响。

判断一条查询是否有值

--以下四个查询都是判断连接查询无记录时所做的操作---性能最差消耗0.8秒SET STATISTICS IO ON SET STATISTICS TIME ONDECLARE @UserType INT ,@Status INTSELECT  @UserType=COUNT(c.Id) FROM Customerfo t INNER JOIN Customer c ON c.Id=t.CustomerId            WHERE c.customerTel=‘13400000000‘            IF(@UserType=0)            BEGIN                SET @Status = 2                          PRINT  @Status            ENDSET STATISTICS TIME OFF    SET STATISTICS IO OFF                go    ----性能较好消耗0.08秒    SET STATISTICS IO ON SET STATISTICS TIME ON        IF NOT EXISTS(SELECT c.Id FROM Customerfo t INNER JOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel=‘13400000000‘)    BEGIN    DECLARE @Status int     SET @Status = 2     PRINT @Status    END        SET STATISTICS TIME OFF    SET STATISTICS IO OFF    go    ----性能较好消耗0.08秒            SET STATISTICS IO ON SET STATISTICS TIME ON        IF NOT EXISTS(SELECT top 1 c.id FROM Customerfo t INNER JOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel=‘13400000000‘        ORDER BY NEWID() )    BEGIN    DECLARE @Status int     SET @Status = 2     PRINT @Status    END        SET STATISTICS TIME OFF    SET STATISTICS IO OFF                GO---性能和上面的一样0.08秒SET STATISTICS IO ON SET STATISTICS TIME ON            IF NOT EXISTS(SELECT 1  FROM Customerfo t INNER JOIN Customer c ON c.Id=t.CustomerId WHERE c.customerTel=‘13410700660‘ )    BEGIN    DECLARE @Status int     SET @Status = 2     PRINT @Status    END        SET STATISTICS TIME OFF    SET STATISTICS IO OFF    

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

理解TRUNCATE和DELETE的区别

---创建表Table1IF OBJECT_ID(‘Table1‘,‘U‘) IS NOT NULLDROP TABLE Table1GOCREATE TABLE Table1(ID INT NOT NULL,FOID INT NOT NULL)GO

--插入测试数据INSERT INTO Table1VALUES(1,101),(2,102),(3,103),(4,104)GO---创建表Table2IF OBJECT_ID(‘Table2‘,‘U‘) IS NOT NULLDROP TABLE Table2GOCREATE TABLE Table2(FOID INT NOT NULL)GO

--插入测试数据

INSERT INTO Table2 VALUES(101),(102),(103),(104)
GO 
SELECT * FROM Table1
GO 
SELECT * FROM Table2
GO

在Table1表中创建触发器,当表中的数据被删除时同时删除Table2表中对应的FOID

CREATE TRIGGER TG_Table1 ON Table1AFTER 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.简单的事务提交

BEGIN TRANSACTIONINSERT INTO Table1(ID,Age)VALUES(1,20)INSERT INTO Table1(ID,Age)VALUES(2,5)INSERT INTO Table1(ID,Age)VALUES(2,20)INSERT INTO Table1(ID,Age)VALUES(3,20)COMMIT TRANSACTIONGO---第二条记录没有执行成功,其他的都执行成功SELECT * FROM Table1
所以并不是事务中的任意一条语句报错整个事务都会回滚，其它的可执行成功的语句依然会执行成功并提交。

2.TRY...CATCH

DELETE FROM Table1BEGIN TRYBEGIN 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 TRANSACTIONEND TRYBEGIN CATCHROLLBACK TRANSACTIONEND CATCH----重新打开一个回话执行查询，发现由于存在对象出错BEGIN CATCH并没有收到执行报错，且事务一直处于打开状态，没有被提交，也没有执行回滚。SELECT * FROM Table1---如果事务已经提交查询XACT_STATE()的状态值是0,或者执行DBCC OPENTRANSELECT XACT_STATE()DBCC OPENTRAN---手动执行提交或者回滚操作ROLLBACK TRANSACTION

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谓词类型与字段类型一致，所以查询走了索引查找

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

[转] SQL Server 致程序员（容易忽略的错误）

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > [转] SQL Server 致程序员（容易忽略的错误）