SQL Server Performance Forum – Threads Archive

Index Seek x Clustered Index Scan

I would like that you solved the following problem.
In the Siafisico database I have a called table "mestre". I executed the following command: select *
from master
where cnpj like ‘ 000203% ‘
In the execution plan the SQL uses the index "ix_mestre_cnpj". This is correct according to my opinion. After that I executed the following command: Declare @cnpj varchar(14)
Set @cnpj = ‘ 000203% ‘
select * from mestre
where cnpj like @cnpj
In essence, the command is accurately equal to the previous one, however, in this case , SQL adopted the PK. In resume, this is equivalent to a "full scan". This quandary affects a large table (in the siafisico database it only has 200,000 rows – a sample) and the problem has the same dimension. I did not obtain how to decide the problem. Please can you help me?
Thanks in advance and
regards.
Cristina. M Cristina
DBA PRODESP
[email protected]
Tel.: 3243-3871

---

http://www.sql-server-performance.com/transact_sql.asp for your information.
Is cnpj column is the primary key on this table? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I guess you’ve already tried to update the statistics and also tried to clean the buffers?
By any chance, are you on SP4?
Just asking, because I think I remember some thread some the MS newsgroups claiming almost the same. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Problem with the code:
is that query optimizer doesn’t even try ro resolve local variable value and use it later to calculate costs based on statistics. However if you package that code in stored procedure and make @cnpj procedure parameter instead of local variable, good execution plan will be selected again.

---

]]>