I don't know that I'd recommend recompiling out of hand. The execution plan using a parameter is going to sample the appropriate indexes and arrive at a generic "best" plan....

Yeah, that's basically it, but it's hard to tell someone exactly why one function turns out like...

I would strongly recommend you use the OUTPUT clause instead of triggers. Not least because, i think, you won't have access to the identity values inside the trigger.

OUTPUT is pretty...

Most of the time, when I'm observing client statements in trace events, I see the parameter values on stored procedure calls. And if I run a batch that looks like...

I'd add a step to design the clustered indexes. They should be part of the fundamental decisions around the database design.

They're not visible in the textdata column?

I'd treat it like any other system. Put an index maintenance routine in place that reindexes or reorgs the indexes as needed based on their size and fragmentation levels. Because...

Exactly. That's why I was trying to clarify the question asked. If I saw more than 4 or 5 indexes I think I'd be quite concerned. 249 would positively freak...

Checking out the plans, the first thing I'm noticing is that the estimated rows are pretty different. 1 for the slow plan and 584 for the fast plan. That tells...

Jeff Moden (7/16/2009)

Grant Fritchey (7/16/2009)

Jeff Moden (7/15/2009)

Grant Fritchey (7/15/2009)

I'm sorry, but can you zip those up & reattach them. I'm having a hard time getting them...

Yeah, you can just leave that as is. You don't need to add another transaction to it.

It's an update statement and it's within a distributed transaction, so yeah, you're going...

As far as I know, it's just a limit. In SQL Server 2008, the limit is 999.

Was he asking why you would not want that many indexes? Or was he...

Inserts will absolutely be slower. There's no way to get around the fact that you'll have to udpate the index as part of the insert. So, as previously stated, you...

The variable can only be loaded once. So it's going to be set a single time and then when you reference it in the second select statement it will only...

Take a look at the OUTPUT clause in the Books Online. That will allow you to capture the ID's as you create them and then use those ID's in the...