Does SQL uses index in the following select statement

16 Dec 2005 5:13 AM

Alex via SQLMonster.com

I have the following table structure:

PK_Column1
PK_Column2
IndexedColumn
Column_ABC
Column_XYZ

Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
values in the following select statement:

SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
PK_Column1=@MyParam

When I run this statement it works too slow and I see alot of reads in SQL
Server Profiler.

Is there any way to improve the performance in this case?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200512/1

16 Dec 2005 5:26 AM

Mike Epprecht (SQL MVP)

Hi

If Indexed column is clustered, it probably would as it can do a clustered
index range scan.

Else, it may not. It all depends on how up to date the statistics are, the
data types of the columns, how selective the indexes are and the number of
rows.

Show the query plan and we can tell.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Alex via SQLMonster.com" <no@spam.pls> wrote in message

news:58e799d08a0ce@uwe...
Show quote

>I have the following table structure:
>
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
>
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
>
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@MyParam
>
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
>
> Is there any way to improve the performance in this case?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200512/1

16 Dec 2005 6:15 AM

Alex via SQLMonster.com

Thank you for your answer.

Here are more details:
PK_Column1            smallint
PK_Column2            int
IndexedColumn        DateTime         (NON-CLUSTERED and not unique)
Column_ABC            varchar
Column_XYZ             varbinary(BLOB)

The table has about 4M rows.

>Show the query plan and we can tell.

How can I get it? I am using the Standard edition of SQL Server 2005.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200512/1

17 Dec 2005 12:17 AM

Gert-Jan Strik

If there is a clustered index on PK_Column1, then it might do an index
scan on the index of IndexedColumn. However, the query would benefit
more from an index on (PK_Column1, IndexedColumn).

I am not running SQL2K5, but I guess that SET SHOWPLAN_TEXT ON will
probably still work...

HTH,
Gert-Jaqn

Show quote

"Alex via SQLMonster.com" wrote:
>
> I have the following table structure:
>
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
>
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
>
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@MyParam
>
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
>
> Is there any way to improve the performance in this case?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200512/1