Performance of query processing on SQLServer
-
I have a general question on the performance of query proccessing on SQLServer - the difference between a full table scan and a scan untill a value is found.
I run a select on a large table with many records, my "where" clause includes the two primary keys of the table but there is no index defined on that table.
Now, if I use the select when the value does not exist I think this will initiate a full table scan, isn't it?
If a value exists - I don't remember the exact syntax but I can tell SQL Server to stop searching when it hits the first value that matches my select criteria, on this case will it be faster? assuming that SQL Server will scan only part of the table until it hits the first match.
Or am I completely wron here and when I use the P. Keys the processing time will be the same.
thanks, yoni
-
If I understand you correctly you have a primary key with two columns in it.
A search using the two columns within the primary key should use an index seek, or if your primary key is a clustered key it should be a clustered index seek.
As far a searching until it finds something, by definition a search on the primary key can only find one value.
If you are searching a field and you simply want to SQL to stop as soon as it finds a value then you can either use and EXISTS clause of TOP clause.
SELECT * FROM dbo.MyTable WHERE EXISTS(Select field from table where criteria)
-
Hi,
This link will help you..
http://support.microsoft.com/kb/243588/en-us
http://support.microsoft.com/kb/243589/en-us
Minaz
"More Green More Oxygen !! Plant a tree today"
-
So, if my query searches by P.Key (unique result) the server will optimize it to return the result as soon as it gets one and not continue searching, right?
In such case I don't need to define an index because the P.Keys are indexed automatically?
thanks, yoni
-
A primary key is a special sort of unique index.
The difference between an primary key and a unique index is
- A primary key cannot contain a NULL value
- A primary key is clustered unless you explicitly tell it otherwise
- A unique index is nonclustered unless you explicitly tell it otherwise
You cannot get faster than an exact match on a clustered primary key.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply