So You Wanna Be An SQL Server DBA...

[Hack]

If so, then here is an article worth taking a moment to read.

http://www.databasejournal.com/featu...61/article.htm

I'm not the least bit interested in becoming a DBA, but I did find one note worthy thing...I have been writing "correlated sub queries" for years and did not know that is what they are called?

[dilettante]

Yep, they're even called that in Jet databases. From the MSDN Library:

Intermediate Microsoft Jet SQL for Access 2000

The EXISTS subquery

The EXISTS predicate is used in subqueries to check for the existence of values in a result set. In other words, if the subquery does not return any rows, the comparison is False. If it does return one or more rows, the comparison is True.

SELECT *
   FROM tblCustomers AS A
   WHERE EXISTS
   (SELECT * FROM tblInvoices
   WHERE A.CustomerID = tblInvoices.CustomerID)

Note that in the previous SQL statement an alias is used on the tblCustomers table. This is so that we can later refer to it in the subquery. When a subquery is linked to the main query in this manner, it is called a correlated query.

[danasegarane]

I am interested, to start

[GaryMazzone]

But as a DBA the question should be what is there any thing wrong with using correlated sub queries? What if the result set is large?

[Max Peck]

If so, then here is an article worth taking a moment to read.

http://www.databasejournal.com/featu...61/article.htm

I'm not the least bit interested in becoming a DBA, but I did find one note worthy thing...I have been writing "correlated sub queries" for years and did not know that is what they are called?

Ahh, so that's what it's called. Yeah, I've been doing that for years too. Now I've lost my innocence!

No ... I wouldn't want to be a DBA either. Seems to me to be too much like herding cats.

http://www.youtube.com/watch?v=VrlV-cG35nI

-Max

[Merrion]

If you want to be a DBA then this song is worth remembering.

[techgnome]

in 20+ years, I had never encountered correlated queries until my current employment. Quite frankly, they confuse me and aren't intuitive.

-tg

[GaryMazzone]

I use them but they really need to be thought about. Would you want to run the correlated query (which runs against every row) if you were pulling a record set with millions of rows? I would say this would be a problem.

[szlamany]

I'm going to go out on a limb here and suggest that the correlated query - in an EXISTS() function - never returns any rows. It just makes it as far as the INDEX SEEK/SCAN or TABLE SCAN - finds that a row exists - and then it's job is done.