View on Index used in Query Plan? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

View on Index used in Query Plan?

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-10-25 : 13:06:52

I have a VIEW on a table which just selects the IS NOT NULL rows for a given column. I have a UNIQUE INDEX on that view's column.

basically we have loads of rows which are NULL, but all the rest I want to be unique.

That index does not seem to be being used in my Query Plans. Is this the scenario where the index is only use in Enterprise Edition?

Any harm also putting an index on the column in the table itself? The fact that more than 50% of the rows are NULL won't mean that it is not selective, will it?

Kristen

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-25 : 15:14:58

The index on the table won't work with the index being unique and there being more than one null. Also, if values arent at least 80-85% unique the index probably won't be used. 50% is very much non selective. Look at it as a column being male or female.

-Jon
Should still be a "Starting Member"

.

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 01:08:10

Sorry, wasn't clear in my post. I'll leave the UNIQUE index on the view, and put another 9non unique) index on the table itself. Seems overkill ...

Perhaps I should put a trigger to prevent duplicate values ... the VIEW with the INDEX is a PITA anyway ...

Are you sure that 50% nulls and then all the rest unique is treated by SQL Optimised as having the same selectivity as an index on a table with only "Male" and "Female" as its values?

Kristen

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-26 : 08:31:48

Null is still a value, it's just unknown.

-Jon
Should still be a "Starting Member"

.

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 08:47:28

So ... if I have an indexed column with 10,000 rows all set to "AAAA" and another 10,000 rows set to different unique values will SQL never use that index? or just never use it for queries involving "AAAA"?

Kristen

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-26 : 08:56:50

The index will not be selective enough; so more often then not, SQL Server won't use that index.

-Jon
Should still be a "Starting Member"

.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-26 : 09:08:33

It also depends when the plan is created.


DROP TABLE T

CREATE TABLE T (
  pk int PRIMARY KEY,
  s varchar(20) NOT NULL,
  n int NOT NULL
)

INSERT INTO T
SELECT n*2, c+c+c+c+c+c+c+c, FLOOR(RAND(CAST(NEWID() AS binary(4)))* 1000)
FROM (
    SELECT n, CHAR(FLOOR(RAND(CAST(NEWID() AS binary(4)))*26)+97 + n*0) AS c
    FROM Numbers
    WHERE n BETWEEN 1 AND 10000
  ) AS A
UNION ALL
SELECT n*2+1, 'AAAA', FLOOR(RAND(CAST(NEWID() AS binary(4)))* 1000)
FROM (
    SELECT n
    FROM Numbers
    WHERE n BETWEEN 1 AND 10000
  ) AS A

CREATE INDEX Ts ON T(s)

-- Scan clustered index
SELECT *
FROM T
WHERE s = 'AAAA'

-- Seek Ts, bookmark lookup
-- (well, sort of: SQL Server sneakily turns it into a TOP WITH TIES)
SELECT *
FROM T
WHERE s = (SELECT MAX(s) FROM T)

-- Seek Ts, bookmark lookup
DECLARE @s varchar(20)
SET @s = (SELECT MAX(s) FROM T)
SELECT *
FROM T
WHERE s = @s

-- Seek Ts, bookmark lookup (eek!)
SET @s = 'AAAA'
SELECT *
FROM T
WHERE s = @s

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-26 : 09:15:54

True, but how long will SQL server use that plan before it updates it?

-Jon
Should still be a "Starting Member"

.

Kristen
Test

22859 Posts

Posted - 2004-10-26 : 09:54:12

Thanks Arnold - so basically SQL is smart enough to use an index provided I don't search for NULL [my single value that accounts for 50% of the rows]. I think I'm smart enough not to do that!

surefooted: should be less than an hour - any SProc where the average run time shoots up will get recompiled at our next hourly check

Kristen

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-26 : 10:09:34

Check out http://www.sql-server-performance.com/indexes_not_equal.asp

-Jon
Should still be a "Starting Member"

.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources