TSQL not using indexes

This is a test senario, made with temporary tables to illustrate the problem. Pretend table @userdata has index on userid and table @users has index on id

Why is the first select unable to use index, I assumed it would perform better in 1 subselect than in 2 subselects?

Version - Microsoft SQL Server 2008 R2 (RTM) Compatibility level - SQL Server 2000.

-- test tables
DECLARE @userdata TABLE(info VARCHAR(50), userid INT)
DECLARE @users    TABLE(id INT, username VARCHAR(20), superuser BIT)

-- test data
INSERT @users    VALUES(1, 'superuser', 1)
INSERT @users    VALUES(2, 'testuser1', 0)
INSERT @users    VALUES(3, 'testuser2', 0)
INSERT @userdata VALUES('secret information', 1)
INSERT @userdata VALUES('testuser1''s data', 2)
INSERT @userdata VALUES('testuser2''s data', 3)
INSERT @userdata VALUES('testuser2''s data',3)

DECLARE @username VARCHAR(50)
SET @username = 'superuser'
--SET @username = 'testuser1'


--The superuser can read all data
--The开发者_C百科 testusers can only read own data

-- This sql can't use indexes and is very slow
SELECT *
FROM @userdata d
WHERE EXISTS 
(SELECT 1 FROM @users u
WHERE u.username = @username AND u.superuser = 1 OR 
u.id = d.userid AND u.username = @username)

-- This sql uses indexes and performs well
SELECT *
FROM @userdata d
WHERE EXISTS 
(SELECT 1 FROM @users u
WHERE u.username = @username AND u.superuser = 1)
OR EXISTS (SELECT 1 FROM @users u
WHERE u.ID = d.userid 
AND u.username = @username)

I think that or can cause some trouble for the query analyzer to come up with a good query plan. This is not really an answer to your question but an alternative way of doing this query. Apart from the index you already have I would suggest an index on @users.username.

if exists(select * from @users where username = @username and superuser = 1)
begin
  select *
  from @userdata
end
else
begin
  select d.*
  from @userdata as d
    inner join @users as u
      on d.userid = u.id
  where u.username = @username
end

SQL Server won't always produce the optimal plan when you use local variables (@username in your case).

See the following link for an example where SQL Server do not use an index because a local variable is used: http://www.sqlbadpractices.com/using-local-variables-in-t-sql-queries/ .

Its possible that the problem is SQL wont use the indexes, not that it can't. Their are a variety of reasons for this.

You can try to force it to use an index. You might find the query is slower that way.

You can try ALTER INDEX ixFoo REBUILD to rebuild the index. The index might not be used since it is excessively fragmented.

You can also try UPDATE STATISTICS.