避免在 SQL Server 中盲目地追求一句处理

业务需求如下：

有表 A 和表 B ，这两个表结构一致，为不同的业务服务，现在要写一个存储过程，存储过程接受一个参数，当参数为 0 时，查询表 A ，参数为 1 时，查询表 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 具有技巧性，它们两者之间，究竟那一个更好呢？你可能会从性能上来评估，以决定到底用那一种。单纯从语句上来看，似乎两者的效率差不多，下面通过数据测试来反映结果似乎和想像的一样

USE tempdb

GO

SET NOCOUNT ON

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

-- 创建测试环境

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

RAISERROR ( ' 创建测试环境 ' , 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

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

-- 生成测试数据

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

RAISERROR ( ' 生成测试数据 ' , 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

从结果看，两者的性能差异很小，所以两者从性能上比较，可以视为没有差异

虽然在性能上，两者没有什么差异，但另一个问题也许你从来没有考虑过，那就是对表的访问的问题，在方法 A 中，肯定只会访问到一个表；而在方法 B 中，情况还是如此吗？答案是否定的，方法 B 始终会扫描两个表。而这样的潜台词是， 即使在我的查询中，只会用到 A 表，但如果 B 表被下了锁的话，整个查询就会被阻塞，而方法 A 不会 。

为了证明这个问题，我们再做下面的测试

BEGIN TRAN

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

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

--ROLLBACK TRAN -- 不回滚事务，让锁一直保持

-- run query windows 2

DECLARE @a int

SET @a = 1

IF @a = 0

SELECT [TranNumber] FROM A

WHERE [