Can this be done in one pass? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Can this be done in one pass?

Author

Topic

Qest
Starting Member

6 Posts

Posted - 2012-05-17 : 18:36:45

Consider this table:

DECLARE @ExampleTable TABLE (
	ExampleColumn INTEGER PRIMARY KEY
);

INSERT INTO @ExampleTable VALUES (1)
INSERT INTO @ExampleTable VALUES (3)
INSERT INTO @ExampleTable VALUES (7)
INSERT INTO @ExampleTable VALUES (14)
INSERT INTO @ExampleTable VALUES (19)
INSERT INTO @ExampleTable VALUES (26)
INSERT INTO @ExampleTable VALUES (34)
INSERT INTO @ExampleTable VALUES (40)
INSERT INTO @ExampleTable VALUES (46)
INSERT INTO @ExampleTable VALUES (50)

I want to select all the values from the table between 17 and 33 as well as the first value less than 17 and the first value greater than 33.

Consider this query:

SELECT * FROM @ExampleTable WHERE ExampleColumn BETWEEN (
	SELECT MAX(ExampleColumn) FROM @ExampleTable WHERE ExampleColumn < 17
) AND (
	SELECT MIN(ExampleColumn) FROM @ExampleTable WHERE ExampleColumn > 33
)

This query returns the results I am looking for, but unfortunately requires three clustered seeks.

It occurs to be that there's really no reason that the database engine couldn't do this in one pass of the table.

Can this query be rewritten to only need one clustered seek (for SQL Server 2012)?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-17 : 20:22:47

Not sure why you think three clustered seeks is so bad. This might be a little more efficient:

WITH CTE(low,high) AS (SELECT MAX(CASE WHEN ExampleColumn<17 THEN ExampleColumn END),
MIN(CASE WHEN ExampleColumn>33 THEN ExampleColumn END) FROM @ExampleTable)
SELECT * FROM @ExampleTable A
INNER JOIN CTE B ON A.ExampleColumn BETWEEN B.low AND B.high

Qest
Starting Member

6 Posts

Posted - 2012-05-17 : 20:58:17

Your query replaces two seeks with a scan... It seems to perform about the same for my purposes.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-17 : 23:37:38

Depending on the size of the table and the number of rows in the window you're selecting, a scan could very well be more efficient.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources