SQL Server 容易忽略的錯誤

一、概述

因為每天需要稽核程式設計師釋出的SQL語句，所以收集了一些程式設計師的一些常見問題，還有一些平時收集的其它一些問題，這也是很多人容易忽視的問題，在以後收集到的問題會補充在文章末尾，歡迎關注，由於收集的問題很多是針對於生產資料，測試且資料量比較大，這裡就不把資料共享出來了，大家理解意思就行。

 

二、概念

1.大小寫

大寫T-SQL 語言的所有關鍵字都使用大寫，規範要求。

2.使用“;”

使用“;”作為 Transact-SQL 語句終止符。雖然分號不是必需的，但使用它是一種好的習慣,對於合併操作MERGE語句的末尾就必須要加上“;”

（cte表表示式除外）

3.資料型別

避免使用ntext、text 和 image 資料型別，用 nvarchar(max)、varchar(max) 和 varbinary(max)替代

後續版本會取消ntext、text 和 image 該三種型別

4.查詢條件不要使用計算列

例如year(createdate)=2014,使用createdate>=’ 20140101’ and createdate<=’ 20141231’來取代。

IF OBJECT_ID('News','U') IS NOT NULL DROP TABLE News
GO
CREATE TABLE News
(ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
NAME NVARCHAR(100) NOT NULL,
Createdate DATETIME NOT NULL
)
GO
CREATE 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]
GO

GO
INSERT 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 News
WHERE YEAR(Createdate)=2014

---不使用計算列（走的是索引查詢）

SELECT ID,NAME,Createdate FROM News
WHERE 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

5.建表時欄位不允許為null

 發現很多人在建表的時候不會注意這一點，在接下來的工作中當你需要查詢資料的時候你往往需要在WHERE條件中多加一個判斷條件IS NOT NULL,這樣的一個條件不僅僅增加了額外的開銷，而且對查詢的效能產生很大的影響，有可能就因為多了這個查詢條件導致你的查詢變的非常的慢；還有一個比較重要的問題就是允許為空的資料可能會導致你的查詢結果出現不準確的問題，接下來我們就舉個例子討論一下。

T-SQL是三值邏輯（true,flase,unknown）
IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROP TABLE DBO.Customer
GO
CREATE TABLE DBO.Customer
(Customerid int not null );
GO
IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROP TABLE DBO.OrderS
GO
CREATE TABLE DBO.OrderS
(Orderid int not null,
custid int);
GO
INSERT 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都是真值所以可以返回子查詢的true
SELECT 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('')

 

6.分組統計時避免使用count(*)

IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROP TABLE DBO.Customer
GO
CREATE TABLE DBO.Customer
(Customerid int not null );
GO
IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROP TABLE DBO.OrderS
GO
CREATE TABLE DBO.OrderS
(Orderid int not null,
custid int);
GO
INSERT 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;

 

7.子查詢的表加上表別名

IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROP TABLE DBO.Customer
GO
CREATE TABLE DBO.Customer
(Customerid int not null );
GO
IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROP TABLE DBO.OrderS
GO
CREATE TABLE DBO.OrderS
(Orderid int not null,
custid int);
GO
INSERT 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 );

8.建立自增列時單獨再給自增列新增唯一約束

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]%' ) 
);

GO
INSERT INTO tempdb.DBO.TEST(NAME)
VALUES('A中'),('a名'),('Aa'),('ab'),('AA'),('az');

----4.插入報錯後，自增值依舊增加
INSERT INTO tempdb.DBO.TEST(NAME)
VALUES('中');
GO
SELECT 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 ON

INSERT 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 ON

INSERT INTO tempdb.DBO.TEST(ID,NAME)
VALUES(8,'A中');

SET IDENTITY_INSERT tempdb.DBO.TEST OFF

SELECT IDENT_CURRENT('tempdb.DBO.TEST')
SELECT * FROM tempdb.DBO.TEST;
---所以如果要保證ID是唯一的，單單隻設定自增值不行，需要給欄位設定主鍵或者唯一約束
DROP TABLE tempdb.DBO.TEST;

 

9.查詢時一定要制定欄位查詢

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 ON
SET STATISTICS TIME ON
SELECT  *  FROM [Sales].[Customer]
WHERE PersonID=1;
 SET STATISTICS TIME OFF
 SET STATISTICS IO OFF

由於建的索引‘IX1_Customer’沒有包含ModifiedDate欄位，所以需要通過鍵查詢去聚集索引中獲取該欄位的值

 ---列出需要的欄位查詢，因為欄位不包含不需要的列，所以走索引
SET STATISTICS IO ON
SET 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欄位進行查詢，所以只走索引查詢就可以查詢到需要的資料，所以建議在查詢語句中列出你需要的欄位而不是為了方便用*來查詢所有的欄位，如果真的

需要查詢所有的欄位也同樣建議把所有的欄位列出來取代‘*’。

10.使用儲存過程的好處

減少網路通訊量。呼叫一個行數不多的儲存過程與直接呼叫SQL語句的網路通訊量可能不會有很大的差別，可是如果儲存過程包含上百行SQL語句，那麼其效能絕對比一條一條的呼叫SQL語句要高得多。

執行速度更快。有兩個原因：首先，在儲存過程建立的時候，資料庫已經對其進行了一次解析和優化。其次，儲存過程一旦執行，在記憶體中就會保留一份這個儲存過程快取計劃，這樣下次再執行同樣的儲存過程時，可以從記憶體中直接呼叫。

更強的適應性：由於儲存過程對資料庫的訪問是通過儲存過程來進行的，因此資料庫開發人員可以在不改動儲存過程介面的情況下對資料庫進行任何改動，而這些改動不會對應用程式造成影響。

布式工作：應用程式和資料庫的編碼工作可以分別獨立進行，而不會相互壓制。

更好的封裝移植性。

安全性，它們可以防止某些型別的 SQL 插入攻擊。

PROCEDURE [dbo].[SPSalesPerson]
(@option varchar(50))
AS
BEGIN
SET NOCOUNT ON
IF @option='select'
    BEGIN
    SELECT [DatabaseLogID]
          ,[PostTime]
          ,[DatabaseUser]
          ,[Event]
          ,[Schema]
          ,[Object]
          ,[TSQL]
          ,[XmlEvent]
      FROM [dbo].[DatabaseLog]
      END
IF @option='SalesPerson'
   BEGIN
   SELECT [BusinessEntityID]
      ,[TerritoryID]
      ,[SalesQuota]
      ,[Bonus]
      ,[CommissionPct]
      ,[SalesYTD]
      ,[SalesLastYear]
      ,[rowguid]
      ,[ModifiedDate]
   FROM [Sales].[SalesPerson]
   WHERE BusinessEntityID<300
   END
SET 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 語句以終止該行為。根據儲存過程中包含的影響行的語句的數量，這將刪除客戶端和伺服器之間的一個或多個往返過程。儘管這不是大問題，但它可以為高流量應用程式的效能產生負面影響。

11.判斷一條查詢是否有值

--以下四個查詢都是判斷連線查詢無記錄時所做的操作
---效能最差消耗0.8秒
SET STATISTICS IO ON 
SET STATISTICS TIME ON
DECLARE @UserType INT ,@Status INT
SELECT  @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
            END
SET 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也不是查詢的第一個欄位。

 12.理解TRUNCATE和DELETE的區別

---建立表Table1
IF OBJECT_ID('Table1','U') IS NOT NULL
DROP TABLE Table1
GO
CREATE TABLE Table1
(ID INT NOT NULL,
FOID INT NOT NULL)
GO

--插入測試資料
INSERT INTO Table1
VALUES(1,101),(2,102),(3,103),(4,104)
GO

---建立表Table2
IF OBJECT_ID('Table2','U') IS NOT NULL
DROP TABLE Table2
GO
CREATE 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 Table1
AFTER DELETE
AS
BEGIN
  DELETE FROM TA FROM Table2 TA INNER JOIN deleted TB ON TA.FOID=TB.FOID 
END
GO

---測試DELETE刪除操作
DELETE FROM Table1 WHERE ID=1

GO
---執行觸發器成功,Table2表中的FOID=101的資料也被刪除
SELECT * FROM Table1
GO
SELECT * FROM Table2

---測試TRUNCATE刪除操作
TRUNCATE TABLE Table1

GO
---Table2中的資料沒有被刪除
SELECT * FROM Table1
GO
SELECT * FROM Table2

---檢視TRUNCATE和DELETE的日誌記錄情況
CHECKPOINT
GO
SELECT * FROM fn_dblog(NULL,NULL)
GO
DELETE FROM Table2
WHERE FOID=102
GO
SELECT * FROM fn_dblog(NULL,NULL)

  在第四行記錄有一個lop_delete_rows,lcx_heap的刪除操作日誌記錄

----TRUNCATE日誌記錄
CHECKPOINT
GO
SELECT * FROM fn_dblog(NULL,NULL)
GO
TRUNCATE TABLE Table2
GO
SELECT * FROM fn_dblog(NULL,NULL)
GO

 TRUNCATE操作沒有記錄刪除日誌操作

主要的原因是因為TRUNCATE操作不會啟用觸發器,因為TRUNCATE操作不會記錄各行的日誌刪除操作,所以當你需要刪除一張表的資料時你需要考慮是否應該如有記錄日誌刪除操作，而不是根據個人的習慣來操作。 

13.事務的理解

---建立表Table1
IF OBJECT_ID('Table1','U') IS NOT NULL
DROP TABLE Table1
GO
CREATE TABLE Table1
(ID INT NOT NULL PRIMARY KEY,
Age INT NOT NULL CHECK(Age>10 AND Age<50));
GO

---建立表Table2
IF OBJECT_ID('Table2','U') IS NOT NULL
DROP TABLE Table2
GO
CREATE TABLE Table2
(
ID INT NOT NULL)
GO

1.簡單的事務提交

BEGIN TRANSACTION
INSERT 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 TRANSACTION
GO
---第二條記錄沒有執行成功,其他的都執行成功
SELECT * FROM Table1
所以並不是事務中的任意一條語句報錯整個事務都會回滾，其它的可執行成功的語句依然會執行成功並提交。

2.TRY...CATCH

DELETE FROM Table1

BEGIN TRY
BEGIN TRANSACTION
INSERT 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 Table3
VALUES(1) 
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

----重新開啟一個回話執行查詢，發現由於存在物件出錯BEGIN CATCH並沒有收到執行報錯，且事務一直處於開啟狀態，沒有被提交，也沒有執行回滾。
SELECT * FROM Table1

---如果事務已經提交查詢XACT_STATE()的狀態值是0,或者執行DBCC OPENTRAN
SELECT XACT_STATE()

DBCC OPENTRAN

---手動執行提交或者回滾操作
ROLLBACK TRANSACTION

TRY...CATCH不會返回物件錯誤或者欄位錯誤等型別的錯誤

想詳細瞭解TRY...CATCH請參考http://www.cnblogs.com/chenmh/articles/4012506.html

 

3.開啟XACT_ABORT

SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT 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 Table3
VALUES(1) 
COMMIT TRANSACTION
SET 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 的影響。

      所以我們應該根據自己的需求選擇正確的事務。

14.修改欄位NOT NULL的過程

在Address表中的有一個Address欄位，該欄位允許為NULL，現在需要將其修改為NOT NULL.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Address
    (
    ID int NOT NULL,
    Address nvarchar(MAX) NOT NULL
    )  ON [PRIMARY]
     TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Address SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Address)
     EXEC('INSERT INTO dbo.Tmp_Address (ID, Address)
        SELECT ID, Address FROM dbo.Address WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Address
GO
EXECUTE sp_rename N'dbo.Tmp_Address', N'Address', 'OBJECT' 
GO
COMMIT

---從上面就是一個重置欄位為非空的過程，從上面的語句我們可以看到首先要建立一張臨時表在臨時表中Address欄位建成了NOT NULL，然後將原表中的資料插入到臨時表當中，最後修改表名，大家可以想一下如果我要修改的表有幾千萬資料，那這個過程該多麼長而且記憶體一下子就會增加很多，所以大家建表的時候就要養成設欄位為NOT NULL

--當你要向現有的表中增加一個欄位的時候你也要不允許為NULL，可以用預設值替代空
Alter Table Address Add Type smallint Not Null Default (1)

 15.條件欄位的先後順序

你平時在寫T_SQL語句的時候WHERE條件後面的欄位的先後順序你有注意嗎？

---建立測試表 
IF OBJECT_ID('TAINFO','U')IS NOT NULL DROP TABLE TAINFO
GO

CREATE 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 TAINFO
GO
CREATE INDEX IX1_TAINFO ON TAINFO(OID)
INCLUDE(STATS,MAC)
GO

依然執行前面的三條查詢語句分析執行計劃

 分析執行計劃前面兩條查詢語句都走的是索引查詢，第三條查詢的是索引掃描，而根據一般單獨用第三條查詢的業務應該不會常見，所以現在一條索引解決了兩個常用查詢的索引需求，避免了建兩條索引的必要（所以當你建索引的時候索引的順序很重要，一般把查詢最頻繁的欄位設第一個欄位，可以避免建多餘的索引）。

為什麼要把這個問題提出來呢，因為平時有遇到程式設計師在寫查詢語句的時候對於同一個查詢條件每次的寫法都不一樣，往往是根據自己想到哪個欄位就寫哪個欄位先，這樣的習慣往往是不好的，就好比上面的例子如果別人看到你的查詢條件建一個索引也是這樣寫的話往往一個表會出現很多多餘的索引（或許有人會說DBA建好索引的順序就好了，這裡把這個因素排除吧），像後面的那個索引就解決了兩個查詢的需求。

所以這裡我一般是這樣規定where條件的，對於經常用作查詢的欄位放在第一個位置（比如上面例子的OID）,其它的欄位根據表的實際欄位順序排列，這樣往往你的查詢語句走索引的概率會更大。

 16.理解外連線

---建立測試表
IF OBJECT_ID('DBO.OrderS','U') IS NOT NULL DROP TABLE DBO.OrderS
GO
CREATE TABLE DBO.OrderS
(Orderid INT NOT NULL,
custid INT NOT NULL,
stats INT NOT NULL);
GO
IF OBJECT_ID('DBO.Customer','U') IS NOT NULL DROP TABLE DBO.Customer
GO
CREATE TABLE DBO.Customer
(Customerid INT NOT NULL );
GO

---插入測試資料
INSERT INTO OrderS VALUES(1,101,0),(2,102,0),(3,103,1),(4,104,0);
GO
INSERT 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後面。

17.謂詞型別要與欄位型別對齊

IF OBJECT_ID('Person','u')IS NOT NULL DROP TABLE Person
GO
CREATE 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謂詞型別與欄位型別一致，所以查詢走了索引查詢

在日常的語句編寫過程中需要注意這類問題，這將直接影響效能。

18.避免使用長位元組欄位排序

 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

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流討論》