June 27, 2018 at 9:40 am
Dear All,
I have a query which has an Index scan operator and I wanted to know how to change it to an Index seek. My query contains subqueries with an 'EXISTS' and 'NOT EXITSTS'; the OUTPUT just has 1 column, I wanted to know if creating a NON-Clustered Index would get rid on the Index scan or would I have to look at other factors please?
Thank you in advance!
June 27, 2018 at 9:46 am
We would have to see the tables definition and the query itself to give you a reliable answer here.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 28, 2018 at 4:33 am
Thank you for your reply! the script and table definitions are as follows:
SELECT b.time*1.0 credit, 0.0 credits,b.Id, b.date
FROMstaff b
INNER JOIN emp e on e.code = b.code
WHERE b.date >= isnull(dbo.ConvDate2(e.Startdate),'2011/11/01')
AND b.id >= COALESCE(@Id,0) AND b.staffId <= COALESCE(@Id,56433) AND EXISTS (SELECT TOP 1 t.Id FROM payment t where p.code = b.Id)
AND NOT EXISTS (select id from invoice w where w.id = b.id)
ORDER BY DATE
ORDINAL_POSITION COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE
1 Time smalldatetime NULL YES
2 Comments varchar -1 YES
3 ID bigint NULL NO
4 StaffId int NULL YES
5 DeptId int NULL YES
6 Timestamp timestamp NULL NO
7 DateCreated datetime NULL NO
ORDINAL_POSITION COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE
1 Code varchar 30 YES
2 LastName varchar 50 YES
3 FirstName varchar 50 YES
4 Startdate varchar 20 YES
5 DeptNo varchar 30 YES
6 ID bigint NULL NO
7 StaffId int NULL YES
June 28, 2018 at 6:59 am
The ISNULL command is absolutely going to lead to table scans with no possibility of seeks. You need to get rid of it. If you have to use an OR command. It still might not use the index, but the chances are better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2018 at 9:18 am
SELECT b.time * 1.0 credit, 0.0 credits, b.Id, b.date
FROM staff b
INNER JOIN (
SELECT *, isnull(dbo.ConvDate2(e.Startdate),'20111101') AS Startdate_converted
FROM emp
) AS e on e.code = b.code
WHERE b.date >= e.Startdate_converted
AND (@Id IS NULL OR (b.id >= @Id AND b.staffId <= @Id))
AND EXISTS (SELECT TOP 1 t.Id FROM payment t where p.code = b.Id)
AND NOT EXISTS (select id from invoice w where w.id = b.id)
ORDER BY DATE
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply