業(yè)務(wù)需求如下：

有表 A 和表 B ，這兩個(gè)表結(jié)構(gòu)一致，為不同的業(yè)務(wù)服務(wù)，現(xiàn)在要寫一個(gè)存儲(chǔ)過程，存儲(chǔ)過程接受一個(gè)參數(shù)，當(dāng)參數(shù)為 0 時(shí)，查詢表 A ，參數(shù)為 1 時(shí)，查詢表 B 。

IF @Flag = 0

SELECT * FROM dbo . A

ELSE IF @Flag = 1

SELECT * FROM dbo . B

SELECT * FROM dbo . A

WHERE @Flag = 0

UNION ALL

SELECT * FROM dbo . B

WHERE @Flag = 1

從語句的簡捷性來看，方法 B 具有技巧性，它們兩者之間，究竟那一個(gè)更好呢？你可能會(huì)從性能上來評估，以決定到底用那一種。單純從語句上來看，似乎兩者的效率差不多，下面通過數(shù)據(jù)測試來反映結(jié)果似乎和想像的一樣

USE tempdb

GO

SET NOCOUNT ON

--======================================

-- 創(chuàng)建測試環(huán)境

--======================================

RAISERROR ( ' 創(chuàng)建測試環(huán)境 ' , 10 , 1 ) WITH NOWAIT

-- Table A

CREATE TABLE [dbo] . A (

[TranNumber] [int] IDENTITY ( 1 , 1 ) NOT NULL,

[INVNO] [char] ( 8 ) NOT NULL,

[ITEM] [char] ( 15 ) NULL DEFAULT ( '' ),

PRIMARY KEY ( [TranNumber] )

)

CREATE INDEX [indexONinvno] ON [dbo] . A ( [INVNO] )

CREATE INDEX [indexOnitem] ON [dbo] . A ( [ITEM] )

CREATE INDEX [indexONiteminnvo] ON [dbo] . A ( [INVNO] , [ITEM] )

GO

-- Table B

CREATE TABLE [dbo] . B (

[ItemNumber] [char] ( 15 ) NOT NULL DEFAULT ( '' ),

[CompanyCode] [char] ( 4 ) NOT NULL,

[OwnerCompanyCode] [char] ( 4 ) NULL,

PRIMARY KEY ( [ItemNumber] , [CompanyCode] )

)

CREATE INDEX [ItemNumber] ON [dbo] . B ( [ItemNumber] )

CREATE INDEX [CompanyCode] ON [dbo] . B ( [CompanyCode] )

CREATE INDEX [OwnerCompanyCode] ON [dbo] . B ( [OwnerCompanyCode] )

GO

--======================================

-- 生成測試數(shù)據(jù)

--======================================

RAISERROR ( ' 生成測試數(shù)據(jù) ' , 10 , 1 ) WITH NOWAIT

INSERT [dbo] . A ( [INVNO] , [ITEM] )

SELECT LEFT( NEWID (), 8 ), RIGHT( NEWID (), 15 )

FROM syscolumns A , syscolumns B

INSERT [dbo] . B ( [ItemNumber] , [CompanyCode] , [OwnerCompanyCode] )

SELECT RIGHT( NEWID (), 15 ), LEFT( NEWID (), 4 ), LEFT( NEWID (), 4 )

FROM syscolumns A , syscolumns B

GO

DECLARE @a int

SET @a = 1

DECLARE @t TABLE (

id int IDENTITY ,

a int , b int )

DECLARE @dt datetime , @loop int , @id int

SET @loop = 0

WHILE @loop 5

BEGIN

SET @loop = @loop + 1

RAISERROR ( 'test %d' , 10 , 1 , @loop ) WITH NOWAIT

SET @dt = GETDATE ()

SELECT [ITEM] FROM A

WHERE @a = 0

AND [ITEM] 'A'

UNION ALL

SELECT [ItemNumber] FROM B

WHERE @a = 1

AND [ItemNumber] 'A'

INSERT @t ( a ) VALUES ( DATEDIFF ( ms , @dt , GETDATE ()))

SELECT @id = SCOPE_IDENTITY (), @dt = GETDATE ()

IF @a = 0

SELECT [ITEM] FROM A

WHERE [ITEM] 'A'

ELSE IF @a = 1

SELECT [ItemNumber] FROM B

WHERE [ItemNumber] 'A'

UPDATE @t SET b = DATEDIFF ( ms , @dt , GETDATE ())

WHERE id = @id

END

SELECT * FROM @t

UNION ALL

SELECT NULL, SUM ( a ), SUM ( b ) FROM @t

id a b

--- ------- -------

1 3410 2063

2 1703 1656

3 1763 1656

4 1800 1793

5 1643 1856

NULL 10319 9024

從結(jié)果看，兩者的性能差異很小，所以兩者從性能上比較，可以視為沒有差異

雖然在性能上，兩者沒有什么差異，但另一個(gè)問題也許你從來沒有考慮過，那就是對表的訪問的問題，在方法 A 中，肯定只會(huì)訪問到一個(gè)表；而在方法 B 中，情況還是如此嗎？答案是否定的，方法 B 始終會(huì)掃描兩個(gè)表。而這樣的潛臺(tái)詞是， 即使在我的查詢中，只會(huì)用到 A 表，但如果 B 表被下了鎖的話，整個(gè)查詢就會(huì)被阻塞，而方法 A 不會(huì) 。

為了證明這個(gè)問題，我們再做下面的測試

BEGIN TRAN

UPDATE A SET [ITEM] = RIGHT( NEWID (), 4 )

WHERE [ITEM] BETWEEN '9' AND 'A'

--ROLLBACK TRAN -- 不回滾事務(wù)，讓鎖一直保持

-- run query windows 2

DECLARE @a int

SET @a = 1

IF @a = 0

SELECT [TranNumber] FROM A

WHERE [