Jeff Moden (1/31/2008)

---

Sure it does, Frank... it's a clustered index and scanning the index inherently means it's also scanning the table (believe someone else also said that on this thread)....

I think Andy has a point there. On multiprocessor machine SQL Server might use more threads to scan the index and then merge the result sets in one data stream...

Adam,

I see your point. Maybe those 'gurus' should elevate their minds a little more. Temp tables are not proprietary per se, only the way you declare them is, and all...

I'm the one whom Gary first expressed his feelings about temp tables to, but since Gary and I had already come to an agreement (that is, I like them, he...

you're right, still it's conceptually different, e.g. table variables don't get involved in transactions.

 

It's not a temp table, it's a table variable

Since the whole thing seems to be revamped, here's the solution I proposed one year ago.

It's pure set-based T-SQL, no cursor or structured instructions (the If's are there only for...

Try for instance to connect to a stopped SQL Server: you'll get an ODBC driver error (which is really reporting an error from the underlying network library DBNETLIB):

And you're right too

Under some circustances, setting ansi_nulls on or off can change the execution plan (By the way, in my examples it...

Although the WHERE part of the two execution plans is similar, if we look at them in their entirety they are very different, since the originating queries are different, the...

QA uses ODBC to connect. Besides, the option 'Set ansi_nulls' should be on by installation default in its Tools/Options menu

I think that, if not otherwise specified, ansi_nulls should be considered to be ON. Although at database level it's usually OFF by default, it's set automatically ON by the connection...

It's not weird, it's... ANSI!

The ANSI standard requires that every comparison against NULL always evaluates to UNKNOWN (even when you're comparing two NULLs). This means that we have a three-valued...

Just to correct myself. In my previous post I said that none of the proposed answers seemed right, and also posted the execution plans as proof.

Now, after a good night's...

But the statements didn't specify the alias in the order by. They explicity indicated myIT.Unique_ID