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

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

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

2024-07-26 03:31:11 233人阅读

标签：SQL SERVER/MSSQL/DBA/T-SQL好习惯/数据库/需要注意的地方/程序员/容易犯的错误/遇到的问题

概述

因为每天需要审核程序员发布的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);

**分组统计时避免使用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(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---性能不好，由于内部查询会返回查询结果，就是如果子查询有100条记录就返回100个1，性能消耗0.1秒，如果子查询的数据量大性能就会越差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

理解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---事务全部执行回滚操作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 的影响。

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

总结

后面收集到类似的容易忽略的问题会补充在文章的末尾。

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

备注：

作者：pursuer.chen

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

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

欢迎大家拍砖

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

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

联系
我们

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