Not Exists double check? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Not Exists double check?

Author

Topic

ipisors
Starting Member

39 Posts

Posted - 2015-02-17 : 11:57:58

Hello, my goal is simple:

Find out all records in tbl_members_phpcaid (alias BackportedMembers), displaying only PROV_KEYID_EZ column, where the following is true: that PROV_KEYID_EZ value is found in dwpresentation_prod.dbo.members (alias DWMembers) in the field currassignedProvider1.

We are doing some data migration, and the current question is, are there records where tbl_members_phpcaid.prov_keyid_ez is the same as dwpresentation_prod.dbo.members.currassignedprovider1 ?

I figure I can use either EXISTS with correlated subquery, or IN(). At the moment for the sake of practice, I was trying to use EXISTS.

PS, I tried it without Cast, but I got the error:
Conversion failed when converting from a character string to uniqueidentifier.

I'm using this code. Does it seem correct? I'm getting zero records, which is KIND OF what I expected but was unsure - just hoping for 2nd set of eyes to confirm no glaring problems:


select
	 BackportedMembers.PROV_KEYID_EZ
from
	tbl_members_phpcaid BackportedMembers
where
	exists (select * from dwpresentation_prod.dbo.members DWMembers where DWMembers.currassignedProvider1 = cast(BackportedMembers.PROV_KEYID_EZ as varchar(255)))

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 12:18:52

it's the right idea. Note that you can just say 'SELECT 1' in your sub query instead of 'SELECT *' with the same results.

ipisors
Starting Member

39 Posts

Posted - 2015-02-18 : 15:07:44

Thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 15:12:27

quote:
Originally posted by gbritton

it's the right idea. Note that you can just say 'SELECT 1' in your sub query instead of 'SELECT *' with the same results.

Actually SELECT * should be used for EXISTS/NOT EXISTS for performance reasons.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 15:31:21

@tara -- I've done some testing on this. It actually makes no difference to performance. In fact, the compiler recognizes the constant and changes the expression to count(*) anyway, which you can see in the execution plan.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 15:42:11

That might be the case for newer versions these days, but * is the best practice for EXISTS/NOT EXISTS. Extensive testing was done in the past which showed * was the better performer, though minimally better. Perhaps the best practice should be changed if the compiler now executes them the same way. I'll leave that up to the experts, but I haven't come across an article yet that showed it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 15:50:05

I just checked it against SQL Server 2005 (the oldest version I have access to). Exactly the same behavior. You'll see this in the EP:

Scalar Operator(Count(*))

even though you write SELECT COUNT('Rows I am interested in')

I could write up an article on it I suppose, but it's really so simple there seems little point.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 16:06:04

I am pretty sure when I came across it that it was for SQL Server 2000 since I remember which job I was at. I've been using SQL Server since version 6.5, actually 4.2.1 but it was so minimal that I don't count it. My memory just doesn't go back that far!

But looking at various articles on the subject now, it looks like it's a compile time difference and not an execution time difference. The compile time difference would be seen if you show STATISTICS TIME, so it can't be seen by looking at the execution plan. And according to Microsoft due to the compile time difference, SELECT 1 wins out, which I'm surprised considering the best practice from many years ago. But duly noted since I haven't had to support 2000 in at least 7 years. Learn something new everyday.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources