This post comes as a result of a discussion related to this post in the Database Design forum:
http://social.technet.microsoft.com/Forums/en-US/databasedesign/thread/2c991301-5b80-454a-80d8-090a5a475643
I used this table for test purposes:
create table dbo.someTable
( record_id integer not null
constraint pk_SomeTable primary key
constraint ck_someTable_Record_Id
check (record_Id > 0),
filler char(50)
)
go
;WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
insert into dbo.someTable
select
n,
'This is a sample record; record # ' + cast(n as varchar(7))
from numbers
where n <= 999999
go
update statistics someTable
I tested this code:
declare @theId integer
declare @sql nvarchar(800)
set @theId = 14
--select top 5000 x.*
--from dbo.someTable x
--where @theId is null
-- or x.record_Id = @theId
select top 5000 x.*
from ( select coalesce(@theId, 0) as theId,
case when @theId is null then 0 else 1 end as joinType,
convert(int, 0x7fffffff) as maxId
) jt
join dbo.someTable x
on x.record_Id >= theId * joinType
and x.record_Id <= theId * joinType + ((joinType+1)%2) * maxId
/* -------- Output when parameter is specified: --------
record_id filler
----------- --------------------------------------------------
14 This is a sample record; record # 14
(1 row(s) affected)
Table 'someTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Top(TOP EXPRESSION:((5000)))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[someTable].[pk_SomeTable] AS [x]), SEEK:([x].[record_id] >= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END AND [x].[record_id] <= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+((CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+(1))%(2))*(2147483647)) ORDERED FORWARD)
*/
/* -------- Output when parameter is NULL: --------
record_id filler
----------- --------------------------------------------------
1 This is a sample record; record # 1
2 This is a sample record; record # 2
...
5000 This is a sample record; record # 5000
(5000 row(s) affected)
Table 'someTable'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Top(TOP EXPRESSION:((5000)))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[someTable].[pk_SomeTable] AS [x]), SEEK:([x].[record_id] >= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END AND [x].[record_id] <= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+((CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+(1))%(2))*(2147483647)) ORDERED FORWARD)
(2 row(s) affected)
*/
Is this mumbo jumbo worth the effort?
Mud slinging comments? Insults?
