It's not a huge deal to do this, but it will result in recompiles on the procedure since the execution plan for an UPDATE and one for an INSERT are...

TRY/CATCH won't help performance, but it will make your code easier to write & maintain.

Can you post an execution plan? If possible, the actual execution plan not the estimated one.

Paul DB (9/10/2008)

---

I...

There is so much code missing from this query, it's hard to see where you're going wrong. First, using sp_executesql is for working with dynamic TSQL. Nothing in this query...

This is psuedo-code, but something along these lines:

CREATE TABLE #MyIDs

(ID int)

INSERT INTO TableA

(...)

OUTPUT INSERTED.ID

INTO #MyIDs

SELECT ...

FROM TableB

Then you can obviously do what you want with the temporary table. use...

I'm not a RAID expert, but having seperate file groups on different drives will be faster than all the filegroups on one drive, yes.

Or, rather than hard coding all those values in a query, create a table and store them there. It'll sure make maintenance easier.

Can you break it down and run a couple of steps at a time? I tried duplicating the script in miniature and it worked fine.

CREATE TABLE x ( id INT,...

Another vote for using the OUTPUT clause. That's incredibly useful for doing exactly what you're asking for.

Also, I wouldn't recommend using @@IDENTITY. @@IDENTITY is not limited by the scope of...

GilaMonster (9/10/2008)

---

Itzik Ben-Gan did a demo last year at PASS. It's got nothing to do with bad data. All it requires is that a page split occurs during a scan...

Ah, that's a shortcoming with the tool, not SQL Server. Ummm, you might try associating to the first column and then renaming the association (not sure how to do that)...

I'd start by attempting to kill the process. That should initiate a rollback, meaning whatever changes that process had been making will go away, but you shouldn't have problems with...

The best description of this I've seen is contained in the first two chapters of Itzik Ben-Gan's book, Inside SQL Server 2005: T-SQL Querying.

If I understand the question, you simply add the relationship three different times, relating the lookup value to the three different columns individually. It's a fine method.

Hooey.

If it's a reporting system, and only a reporting system, you should mark the database itself as read only.