程序員容易忽略的SQL Server錯(cuò)誤集錦 – 碼農(nóng)網(wǎng)

codingparty 2015-01-31

展開全文

概述

因?yàn)槊刻煨枰獙徍顺绦騿T發(fā)布的SQL語(yǔ)句，所以收集了一些程序員的一些常見問題，還有一些平時(shí)收集的其它一些問題，這也是很多人容易忽視的問題，在以后收集到的問題會(huì)補(bǔ)充在文章末尾，歡迎關(guān)注，由于收集的問題很多是針對(duì)于生產(chǎn)數(shù)據(jù)，測(cè)試且數(shù)據(jù)量比較大，這里就不把數(shù)據(jù)共享出來(lái)了，大家理解意思就行。

步驟

大小寫

大寫T-SQL 語(yǔ)言的所有關(guān)鍵字都使用大寫，規(guī)范要求。

使用“;”

使用“;”作為 Transact-SQL 語(yǔ)句終止符。雖然分號(hào)不是必需的，但使用它是一種好的習(xí)慣,對(duì)于合并操作MERGE語(yǔ)句的末尾就必須要加上“;”

（cte表表達(dá)式除外）

數(shù)據(jù)類型

避免使用ntext、text 和 image 數(shù)據(jù)類型，用 nvarchar(max)、varchar(max) 和 varbinary(max)替代

后續(xù)版本會(huì)取消ntext、text 和 image 該三種類型

查詢條件不要使用計(jì)算列

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

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')

—使用計(jì)算列查詢（走的是索引掃描）

SELECT ID,NAME,Createdate FROM News
WHERE YEAR(Createdate)=2014

—不使用計(jì)算列（走的是索引查找）

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

對(duì)比兩個(gè)查詢顯然絕大部分情況下走索引查找的查詢性能要高于走索引掃描，特別是查詢的數(shù)據(jù)庫(kù)不是非常大的情況下，索引查找的消耗時(shí)間要遠(yuǎn)遠(yuǎn)少于索引掃描的時(shí)間，如果想詳細(xì)了解索引的體系結(jié)構(gòu)可以查看了我前面寫的幾篇關(guān)于聚集、非聚集、堆的索引體系機(jī)構(gòu)的文章。

請(qǐng)參看：http://www.cnblogs.com/chenmh/p/3780221.html

請(qǐng)參看：http://www.cnblogs.com/chenmh/p/3782397.html

建表時(shí)字段不允許為null

發(fā)現(xiàn)很多人在建表的時(shí)候不會(huì)注意這一點(diǎn)，在接下來(lái)的工作中當(dāng)你需要查詢數(shù)據(jù)的時(shí)候你往往需要在WHERE條件中多加一個(gè)判斷條件IS NOT NULL,這樣的一個(gè)條件不僅僅增加了額外的開銷，而且對(duì)查詢的性能產(chǎn)生很大的影響，有可能就因?yàn)槎嗔诉@個(gè)查詢條件導(dǎo)致你的查詢變的非常的慢；還有一個(gè)比較重要的問題就是允許為空的數(shù)據(jù)可能會(huì)導(dǎo)致你的查詢結(jié)果出現(xiàn)不準(zhǔn)確的問題，接下來(lái)我們就舉個(gè)例子討論一下。

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);

---分析為什么查詢結(jié)果沒有數(shù)據(jù)
/*
因?yàn)閠rue,flase,unknown都是真值
因?yàn)閚ot in 是需要結(jié)果中返回flase值,not true=flase,not flase=flase,not unknown=unknown
因?yàn)閚ull值是unknown所以not unknownn無(wú)法判斷結(jié)果是什么值所以不能返回?cái)?shù)據(jù)
*/

--可以將查詢語(yǔ)句修改為
SELECT Customerid FROM DBO.Customer WHERE Customerid NOT IN(SELECT custid FROM OrderS WHERE custid is not null);
--或者使用EXISTS，因?yàn)镋XISTS是二值邏輯只有（true,flase）所以不存在未知。
SELECT Customerid FROM DBO.Customer A WHERE  NOT EXISTS(SELECT custid FROM OrderS WHERE OrderS.custid=A.Customerid );

---in查詢可以返回值，因?yàn)閕n是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(”)

**分組統(tǒng)計(jì)時(shí)避免使用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);
例如：需要統(tǒng)計(jì)每一個(gè)顧客的訂單數(shù)量
---如果使用count(*)
SELECT Customerid,COUNT(*) FROM Customer TA LEFT JOIN OrderS TB ON TA.Customerid=TB.custid 
GROUP BY Customerid ;

實(shí)際情況customerid=3是沒有訂單的，數(shù)量應(yīng)該是0，但是結(jié)果是1,count()里面的字段是左連接右邊的表字段，如果你用的是主表字段結(jié)果頁(yè)是錯(cuò)誤的。

----正確的方法是使用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.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);

大家發(fā)現(xiàn)下面語(yǔ)句有沒有什么問題，查詢結(jié)果是怎樣呢？

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

正確查詢結(jié)果下查詢出的結(jié)果是沒有customerid為3的值

為什么結(jié)果會(huì)這樣呢？

大家仔細(xì)看應(yīng)該會(huì)發(fā)現(xiàn)子查詢的orders表中沒有Customerid字段，所以SQL取的是Customer表的Customerid值作為相關(guān)子查詢的匹配字段。

所以我們應(yīng)該給子查詢加上表別名，如果加上表別名，如果字段錯(cuò)誤的話會(huì)有錯(cuò)誤標(biāo)示

正確的寫法：

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

建立自增列時(shí)單獨(dú)再給自增列添加唯一約束

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.插入報(bào)錯(cuò)后，自增值依舊增加
INSERT INTO tempdb.DBO.TEST(NAME)
VALUES('中');
GO
SELECT IDENT_CURRENT('tempdb.DBO.TEST');
SELECT * FROM tempdb.DBO.TEST;

---插入正常的數(shù)據(jù)
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

----會(huì)發(fā)現(xiàn)ID并不是根據(jù)自增值排列的，而且根據(jù)插入的順序排列的
SELECT IDENT_CURRENT('tempdb.DBO.TEST');
SELECT * FROM tempdb.DBO.TEST;

----6.插入重復(fù)的自增值
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是唯一的，單單只設(shè)置自增值不行，需要給字段設(shè)置主鍵或者唯一約束
DROP TABLE tempdb.DBO.TEST;

查詢時(shí)一定要制定字段查詢

l 查詢時(shí)一定不能使用”*”來(lái)代替字段來(lái)進(jìn)行查詢，無(wú)論你查詢的字段有多少個(gè)，就算字段太多無(wú)法走索引也避免了解析”*”帶來(lái)的額外消耗。

l 查詢字段值列出想要的字段，避免出現(xiàn)多余的字段，字段越多查詢開銷越大而且可能會(huì)因?yàn)槎嗔谐隽四硞€(gè)字段而引起查詢不走索引。

創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)

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]

創(chuàng)建索引

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

查詢測(cè)試

---使用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字段，所以需要通過鍵查找去聚集索引中獲取該字段的值

 ---列出需要的字段查詢，因?yàn)樽侄尾话恍枰牧校宰咚饕?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

由于查詢語(yǔ)句中沒有對(duì)ModifiedDate字段進(jìn)行查詢，所以只走索引查找就可以查詢到需要的數(shù)據(jù)，所以建議在查詢語(yǔ)句中列出你需要的字段而不是為了方便用*來(lái)查詢所有的字段，如果真的

需要查詢所有的字段也同樣建議把所有的字段列出來(lái)取代‘*’。

使用存儲(chǔ)過程的好處

減少網(wǎng)絡(luò)通信量。調(diào)用一個(gè)行數(shù)不多的存儲(chǔ)過程與直接調(diào)用SQL語(yǔ)句的網(wǎng)絡(luò)通信量可能不會(huì)有很大的差別，可是如果存儲(chǔ)過程包含上百行SQL語(yǔ)句，那么其性能絕對(duì)比一條一條的調(diào)用SQL語(yǔ)句要高得多。
執(zhí)行速度更快。有兩個(gè)原因：首先，在存儲(chǔ)過程創(chuàng)建的時(shí)候，數(shù)據(jù)庫(kù)已經(jīng)對(duì)其進(jìn)行了一次解析和優(yōu)化。其次，存儲(chǔ)過程一旦執(zhí)行，在內(nèi)存中就會(huì)保留一份這個(gè)存儲(chǔ)過程緩存計(jì)劃，這樣下次再執(zhí)行同樣的存儲(chǔ)過程時(shí)，可以從內(nèi)存中直接調(diào)用。
更強(qiáng)的適應(yīng)性：由于存儲(chǔ)過程對(duì)數(shù)據(jù)庫(kù)的訪問是通過存儲(chǔ)過程來(lái)進(jìn)行的，因此數(shù)據(jù)庫(kù)開發(fā)人員可以在不改動(dòng)存儲(chǔ)過程接口的情況下對(duì)數(shù)據(jù)庫(kù)進(jìn)行任何改動(dòng)，而這些改動(dòng)不會(huì)對(duì)應(yīng)用程序造成影響。
布式工作：應(yīng)用程序和數(shù)據(jù)庫(kù)的編碼工作可以分別獨(dú)立進(jìn)行，而不會(huì)相互壓制。
更好的封裝移植性。
安全性，它們可以防止某些類型的 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----清空緩存

---測(cè)試兩個(gè)查詢是否都走了緩存計(jì)劃
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;

--執(zhí)行計(jì)劃在第一次執(zhí)行SQL語(yǔ)句時(shí)產(chǎn)生，緩存在內(nèi)存中，這個(gè)緩存的計(jì)劃一直可用，直到 SQL Server 重新啟動(dòng)，或直到它由于使用率較低而溢出內(nèi)存。

默認(rèn)情況下，存儲(chǔ)過程將返回過程中每個(gè)語(yǔ)句影響的行數(shù)。如果不需要在應(yīng)用程序中使用該信息（大多數(shù)應(yīng)用程序并不需要），請(qǐng)?jiān)诖鎯?chǔ)過程中使用 SET NOCOUNT ON 語(yǔ)句以終止該行為。根據(jù)存儲(chǔ)過程中包含的影響行的語(yǔ)句的數(shù)量，這將刪除客戶端和服務(wù)器之間的一個(gè)或多個(gè)往返過程。盡管這不是大問題，但它可以為高流量應(yīng)用程序的性能產(chǎn)生負(fù)面影響。

判斷一條查詢是否有值

--以下四個(gè)查詢都是判斷連接查詢無(wú)記錄時(shí)所做的操作
---性能最差消耗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,之前因?yàn)橛谐绦騿T誤認(rèn)為查詢SELECT 1無(wú)論查詢的數(shù)據(jù)有多少只返回一個(gè)1，其實(shí)不是這樣的，和查詢字段是一樣的意思只是有多少記錄就返回多少個(gè)1,1也不是查詢的第一個(gè)字段。

理解TRUNCATE和DELETE的區(qū)別

---創(chuàng)建表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

--插入測(cè)試數(shù)據(jù)
INSERT INTO Table1
VALUES(1,101),(2,102),(3,103),(4,104)
GO

---創(chuàng)建表Table2
IF OBJECT_ID('Table2','U') IS NOT NULL
DROP TABLE Table2
GO
CREATE TABLE Table2
(
FOID INT NOT NULL)
GO
--插入測(cè)試數(shù)據(jù)
INSERT INTO Table2 VALUES(101),(102),(103),(104)
GO 
SELECT * FROM Table1
GO 
SELECT * FROM Table2
GO

在Table1表中創(chuàng)建觸發(fā)器,當(dāng)表中的數(shù)據(jù)被刪除時(shí)同時(shí)刪除Table2表中對(duì)應(yīng)的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

---測(cè)試DELETE刪除操作
DELETE FROM Table1 WHERE ID=1

GO
---執(zhí)行觸發(fā)器成功,Table2表中的FOID=101的數(shù)據(jù)也被刪除
SELECT * FROM Table1
GO
SELECT * FROM Table2

---測(cè)試TRUNCATE刪除操作
TRUNCATE TABLE Table1

GO
---Table2中的數(shù)據(jù)沒有被刪除
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)

---測(cè)試TRUNCATE刪除操作
TRUNCATE TABLE Table1

GO
---Table2中的數(shù)據(jù)沒有被刪除
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)

在第四行記錄有一個(gè)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操作沒有記錄刪除日志操作

主要的原因是因?yàn)門RUNCATE操作不會(huì)激活觸發(fā)器,因?yàn)門RUNCATE操作不會(huì)記錄各行的日志刪除操作,所以當(dāng)你需要?jiǎng)h除一張表的數(shù)據(jù)時(shí)你需要考慮是否應(yīng)該如有記錄日志刪除操作，而不是根據(jù)個(gè)人的習(xí)慣來(lái)操作。

事務(wù)的理解

---創(chuàng)建表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

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

1.簡(jiǎn)單的事務(wù)提交

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
---第二條記錄沒有執(zhí)行成功,其他的都執(zhí)行成功
SELECT * FROM Table1
所以并不是事務(wù)中的任意一條語(yǔ)句報(bào)錯(cuò)整個(gè)事務(wù)都會(huì)回滾，其它的可執(zhí)行成功的語(yǔ)句依然會(huì)執(zhí)行成功并提交。

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

----重新打開一個(gè)回話執(zhí)行查詢，發(fā)現(xiàn)由于存在對(duì)象出錯(cuò)BEGIN CATCH并沒有收到執(zhí)行報(bào)錯(cuò)，且事務(wù)一直處于打開狀態(tài)，沒有被提交，也沒有執(zhí)行回滾。
SELECT * FROM Table1

---如果事務(wù)已經(jīng)提交查詢XACT_STATE()的狀態(tài)值是0,或者執(zhí)行DBCC OPENTRAN
SELECT XACT_STATE()

DBCC OPENTRAN

---手動(dòng)執(zhí)行提交或者回滾操作
ROLLBACK TRANSACTION

TRY…CATCH不會(huì)返回對(duì)象錯(cuò)誤或者字段錯(cuò)誤等類型的錯(cuò)誤

想詳細(xì)了解TRY…CATCH請(qǐng)參考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

---事務(wù)全部執(zhí)行回滾操作(對(duì)象table3是不存在報(bào)錯(cuò)，但是也回滾所有的提交，跟上面的TRY...CATCH的區(qū)別)
SELECT * FROM Table1

---查詢是否有打開事務(wù)
SELECT XACT_STATE()

DBCC OPENTRAN
未查詢到有打開事務(wù)
當(dāng) SET XACT_ABORT 為 ON 時(shí)，如果執(zhí)行 Transact-SQL 語(yǔ)句產(chǎn)生運(yùn)行時(shí)錯(cuò)誤，則整個(gè)事務(wù)將終止并回滾。
當(dāng) SET XACT_ABORT 為 OFF 時(shí)，有時(shí)只回滾產(chǎn)生錯(cuò)誤的 Transact-SQL 語(yǔ)句，而事務(wù)將繼續(xù)進(jìn)行處理。如果錯(cuò)誤很嚴(yán)重，那么即使 SET XACT_ABORT 為 OFF，也可能回滾整個(gè)事務(wù)。OFF 是默認(rèn)設(shè)置。
編譯錯(cuò)誤（如語(yǔ)法錯(cuò)誤）不受 SET XACT_ABORT 的影響。

所以我們應(yīng)該根據(jù)自己的需求選擇正確的事務(wù)。

修改字段NOT NULL的過程

在Address表中的有一個(gè)Address字段，該字段允許為NULL，現(xiàn)在需要將其修改為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

---從上面就是一個(gè)重置字段為非空的過程，從上面的語(yǔ)句我們可以看到首先要?jiǎng)?chuàng)建一張臨時(shí)表在臨時(shí)表中Address字段建成了NOT NULL，然后將原表中的數(shù)據(jù)插入到臨時(shí)表當(dāng)中，最后修改表名，大家可以想一下如果我要修改的表有幾千萬(wàn)數(shù)據(jù)，那這個(gè)過程該多么長(zhǎng)而且內(nèi)存一下子就會(huì)增加很多，所以大家建表的時(shí)候就要養(yǎng)成設(shè)字段為NOT NULL

--當(dāng)你要向現(xiàn)有的表中增加一個(gè)字段的時(shí)候你也要不允許為NULL，可以用默認(rèn)值替代空
Alter Table Address Add Type smallint Not Null Default (1)

條件字段的先后順序

你平時(shí)在寫T_SQL語(yǔ)句的時(shí)候WHERE條件后面的字段的先后順序你有注意嗎？

---創(chuàng)建測(cè)試表 
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
---插入測(cè)試數(shù)據(jù)
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

如果這是你的寫的查詢語(yǔ)句

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

我現(xiàn)在根據(jù)你的查詢語(yǔ)句創(chuàng)建一條索引

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

分別執(zhí)行三條查詢語(yǔ)句

 ---1.WHERE條件是索引字段且查詢字段也是索引字段
 SELECT ID,OID,Stats MAC FROM TAINFO WHERE MAC='46B550F9-6E24-436D-9BC7-F0650F562E54' AND STATS=1  AND OID=102  
 --2.WHERE 條件是索引的部分字段(這條語(yǔ)句或許是平時(shí)查詢?cè)摫碛玫降淖疃嗟囊粭l語(yǔ)句)
 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

執(zhí)行計(jì)劃分別為

從上面三天查詢語(yǔ)句可以看出，只有第一條語(yǔ)句走的是索引查找，另外兩條語(yǔ)句走的是索引掃描，而我們從字段的名稱應(yīng)該可以看的出OID字段應(yīng)該是該表的一個(gè)外鍵字段也是經(jīng)常會(huì)被用作查詢的字段。

接下來(lái)我們重新?lián)Q一下索引順序

 --創(chuàng)建索引
DROP INDEX IX2_TAINFO ON TAINFO
GO
CREATE INDEX IX1_TAINFO ON TAINFO(OID)
INCLUDE(STATS,MAC)
GO

依然執(zhí)行前面的三條查詢語(yǔ)句分析執(zhí)行計(jì)劃

分析執(zhí)行計(jì)劃前面兩條查詢語(yǔ)句都走的是索引查找，第三條查詢的是索引掃描，而根據(jù)一般單獨(dú)用第三條查詢的業(yè)務(wù)應(yīng)該不會(huì)常見，所以現(xiàn)在一條索引解決了兩個(gè)常用查詢的索引需求，避免了建兩條索引的必要（所以當(dāng)你建索引的時(shí)候索引的順序很重要，一般把查詢最頻繁的字段設(shè)第一個(gè)字段，可以避免建多余的索引）。

為什么要把這個(gè)問題提出來(lái)呢，因?yàn)槠綍r(shí)有遇到程序員在寫查詢語(yǔ)句的時(shí)候?qū)τ谕粋€(gè)查詢條件每次的寫法都不一樣，往往是根據(jù)自己想到哪個(gè)字段就寫哪個(gè)字段先，這樣的習(xí)慣往往是不好的，就好比上面的例子如果別人看到你的查詢條件建一個(gè)索引也是這樣寫的話往往一個(gè)表會(huì)出現(xiàn)很多多余的索引（或許有人會(huì)說DBA建好索引的順序就好了，這里把這個(gè)因素排除吧），像后面的那個(gè)索引就解決了兩個(gè)查詢的需求。

所以這里我一般是這樣規(guī)定where條件的，對(duì)于經(jīng)常用作查詢的字段放在第一個(gè)位置（比如上面例子的OID）,其它的字段根據(jù)表的實(shí)際字段順序排列，這樣往往你的查詢語(yǔ)句走索引的概率會(huì)更大。

理解外連接

---創(chuàng)建測(cè)試表
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

---插入測(cè)試數(shù)據(jù)
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 表中的數(shù)據(jù)
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

看到這結(jié)果是不是有點(diǎn)疑惑，我在連接條件里面寫了TA.stats<>’1′，為什么結(jié)果還會(huì)查詢出。

接下來(lái)我們換一種寫法吧！

----查詢OrderS 表中stats不等于1且不在Customer 表中的數(shù)據(jù)
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

接下來(lái)我就解釋一下原因：對(duì)于外連接，連接條件不會(huì)改變主表的數(shù)據(jù)，即不會(huì)刪減主表的數(shù)據(jù)

對(duì)于上面的查詢主表是orders，所以無(wú)論你在連接條件on里面怎樣設(shè)置主表的條件都不影響主表數(shù)據(jù)的輸出，影響主表數(shù)據(jù)的輸出只在where條件里，where條件影響最后數(shù)據(jù)的輸出。而對(duì)于附表Customer 的條件就應(yīng)該寫在連接條件（on）里而不是where條件里，這里說的是外連接（包括左連接和右連接）。

對(duì)于inner join就不存在這種情況，無(wú)論你的條件是寫在where后面還是on后面都是一樣的，但是還是建議寫在where后面。

謂詞類型要與字段類型對(duì)齊

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
)
---插入測(cè)試數(shù)據(jù)
INSERT INTO Person(Phone,CreateDate)
VALUES('13700000000',GETDATE()),('13700000000',GETDATE()),('13800000000',GETDATE())

---創(chuàng)建索引
CREATE INDEX IX_Person ON Person(Phone,CreateDate)

1.謂詞類型與字段類型不一致

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

由于定義表的phone字段類型是字符型，而上面的查詢條件phone寫成了整形，導(dǎo)致執(zhí)行計(jì)劃走了索引掃描，且執(zhí)行計(jì)劃select也有提示。

2.謂詞類型與字段類型一致

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

第二種查詢phone謂詞類型與字段類型一致，所以查詢走了索引查找

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

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自： codingparty > 《java》

舉報(bào)/認(rèn)領(lǐng)