CODEWITHSUNDEEP
sql-serverindexingprimary-key
Snowy
Snowy

Reputation: 6122

SQL Server Query Between PK Values Optimal?

Using SQL Server 2012 Express. I have a table which represents key/value pairs. keycol is the PK of the table. I am trying to find the optimal way to query the table to find valcol, when the query param is between two keycol values in the table. So I want to find 'a' if I am looking for keycol=15, and find 'b' if keycol=20, and 'c' if keycol=36.

;
with tblextr ( keycol , valcol )
As
(
   Select 10 , 'a'
   Union All
   Select 19 , 'a'
   Union All
   Select 20 , 'b'
   Union All
   Select 29 , 'b'
   Union All
   Select 30 , 'c'
   Union All
   Select 39 , 'c'
)
select valcol from tblextr 
where keycol = (
   select max(keycol)
   from tblextr
   where keycol <= 36
)

Since keycol is the PK already, is there a way to write the SQL better than I have it? Is there another index I should put on the table? I don't mind using more space if the speed goes up. This is a lookup table, so rows are only added monthly as part of a refresh with a full table recreate.

Thanks.

Upvotes: 0

Views: 70

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

I think it should be:

...
SELECT a.valcol 
FROM 
    ( SELECT TOP(1) valcol  
      FROM tblextr
      WHERE keycol <= 36
      ORDER BY keycol DESC
    ) AS a
  JOIN
    ( SELECT TOP(1) valcol  
      FROM tblextr
      WHERE keycol >= 36
      ORDER BY keycol ASC
    ) AS b
    ON b.valcol = a.valcol

If you already have a clustered index on the Primary Key (keycol), I don't think you need any otehr index for this query.

Upvotes: 1

Related Questions