Peter, I couldn't really follow the code you had. Was it supposed to be just separate batch files or was some/all of it supposed to be wrapped up in...

Which is why I was saying it should be decompiled down to it's logical equivalent, which will do the right thing:

create table #t (fname varchar(20))

insert #t (fname)

select top 100 'asdf'

from...

phystech (1/7/2009)

---

No, zero length string plays the role of NULL. That is if the...

I've been using the approach within rather complex query for some months. And my strong impression is that the short-circuiting works. Though I don't short-circuit it on NULL value, I...

phystech (1/6/2009)

---

Adam Haines (1/6/2009)

WHERE CASE

WHEN @FirstName IS NOT NULL THEN

CASE WHEN FirstName = @FirstName THEN 1

...

Here is an example I slapped together just yesterday on another thread that shows how bad table vars can be for even very FEW rows in them. Here is...

Tom.Thomson (1/6/2009)

---

This example may be rather misleading, depending...

Matt Whitfield (1/6/2009)

---

Yep - no worries.

Temp table: 1.218

Table variable: 0.921

I think probably what you wanted me to do instead was to change the 100 to 1000. Then it moves heavily...

Classic problem of using table valued functions: the optimizer can't get the estimations right. Note that nested loop plans are chosen because the estimation is very low (130...

2 points to add:

1) If you are going with dynamic sql, the go completely with it. Constructs such as this:

...

Matt, can you please run your last example without the PK clustered?

1) you cannot have a regular old non-unique/nonPK index on a table variable

2) you can do this however to create a multiple column PK:

declare @tab table (a tinyint, b tinyint,...

There is definitely one situation where table vars help - high-volumn sproc calls where recompiles kill performance. Those are pretty rare situations but they do exist.

Matt Whitfield (1/5/2009)

---

I thought that until Jeff Moden set me straight. You can declare a table variable like this

declare @myTable TABLE (ID INT...

I will go on record saying that in my book this is a situation where a cursor is perfectly acceptable if not preferred.