March 24, 2004 at 11:30 am
Does anyone have any insight into which one of these two queries is
more efficient?
CREATE TABLE dbo.authors
(
au_id [id] NOT NULL,
au_lname varchar(40) NOT NULL,
au_fname varchar(20) NOT NULL,
phone char(12) CONSTRAINT DF__authors__phone__78B3EFCA
DEFAULT 'UNKNOWN' NOT NULL,
address varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
zip char(5) NULL,
contract bit NOT NULL,
Category_Id int NOT NULL,
CONSTRAINT UPKCL_auidind
PRIMARY KEY CLUSTERED (au_id)
ON [PRIMARY],
CONSTRAINT CK__authors__au_id__77BFCB91
CHECK ([au_id] like
'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'),
CONSTRAINT CK__authors__zip__79A81403
CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
)
Query 1:
SELECT COUNT(*)
FROM authors
WHERE au_id='672-71-3249'
Query 2:
IF Exists (SELECT au_id FROM authors WHERE au_id='672-71-3249')
PRINT 1
ELSE
PRINT 0
The reason there is any question in my mind is because au_id is the
PK. Also, in the execution plan for Query 2 there is an extra step
called "Constant Scan" which has a cost of 0%.
Thanks in advance!
Noel
March 24, 2004 at 12:57 pm
If there is a difference it will be insignificant in many cases (especially on an unique indexed field) but large datasets can do the EXISTS situation faster simple because it will only read to the first occurrance unless not found.
March 24, 2004 at 1:22 pm
Right! I guess what my question really is, is this. Because the count(*) is on the primary key (i.e. it is unique) will SQL continue searching once it has found one occurrence of that value?
March 24, 2004 at 1:25 pm
If your index is set up as unique (which I am assuming it is the primary key and would be) then no it already knows 1 value to find no more to look for.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply