Optimization Tip for SQL 6.5: Using UNION ALL Statement

CREATE TABLE TestTable (

  f1 int identity primary key,

  f2 char(50)

)

GO

DECLARE @i int

SELECT @i = 1

WHILE @i <= 1000
  BEGIN
    INSERT INTO TestTable VALUES (LTRIM(str(@i)))
    SELECT @i = @i + 1
  END
GO
CREATE INDEX ind_f2 ON TestTable (f2)
GO

SELECT * FROM TestTable WHERE f2 = ‘100’ OR f2 = ‘500’

SET NOCOUNT ON

GO

SET STATISTICS IO ON

GO

SELECT * FROM TestTable WHERE f2 = ‘100’ OR f2 = ‘500’

GO

SELECT * FROM TestTable WHERE f2 = ‘100’

UNION ALL

SELECT * FROM TestTable WHERE f2 = ‘500’

GO

SET STATISTICS IO OFF

GO

f1          f2

———– ————————————————–

500         500

100         100

Table: TestTable  scan count 2,  logical reads: 8,  physical reads: 0,  read

ahead reads: 0

Table: Worktable  scan count 3,  logical reads: 11,  physical reads: 3,  read ahead reads: 0

f1          f2

———– ————————————————–

100         100

Table: TestTable  scan count 1,  logical reads: 3,  physical reads: 0,  read

ahead reads: 0

500         500

Table: TestTable  scan count 1,  logical reads: 3,  physical reads: 0,  read

ahead reads: 0