Create an index::

keyed on : id

INCLUDE : serialnumber

Use quotes in the WHERE:

WHERE id IN ('1', '2', '3')

You can really get around that error directly, as SQL has control of master.

Back up the master db and restore it to a different name.

RESTORE DATABASE master_dbcc_checkdb

FROM DISK = 'x:\full\path\to\master.BAK'

Then...

You can have odd errors occur in that situation.

You're better off having a fixed, permanent owner for dbs and granting permissions as needed to other users, up to and including...

The second style would at least have a chance of being fully optimized, particularly if you force a recompile of the query. Stick with it.

I'd avoid the first style...

The problem with a table is that one option must apply to everyone. Parameter or other methods allow customized runs, so that your own special cases can still run...

Eirikur Eiriksson (9/15/2014)

---

ScottPletcher (9/15/2014)

---

All of those seem far too disruptive to me.

Two quick possibilities:

1) Add an optional parameter to the proc which requires a certain value or the proc just...

That GROUP BY is gonna kill your performance. See if the code below is close to what you need.

I've added clustered indexes on the temp tables prior to loading...

You can't consider missing index info in isolation. At the very least, you also need to look at index usage stats. Both of those are the minimum needed...

The big delay is likely in allocating and formatting log space, not in the identity number assignment, which is trivial.

Thus, before you run the big INSERT, pre-allocate sufficient log space...

All of those seem far too disruptive to me.

Two quick possibilities:

1) Add an optional parameter to the proc which requires a certain value or the proc just exits (or does...

Luis Cazares (9/11/2014)

---

Your little example is really nice, real life isn't like that.

Please find the errors in the following code:

INSERT INTO dbo.FactInternetSalesCopy

(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey,...

thomashohner (9/9/2014)

---

I'm a noob but Predicates only evaluate to TRUE. Also NULL is not a value. Query 3 is correct as far as my very limited knowledge goes.

So it skips...

Most likely is that a database backup is in progress, and you can't alter the database files during a backup. Check running processes for a backup, dbcc check, or...

EXEC ('DELETE FROM onedb.stg.tab1') AT [linkserv]

You'll also need to decide if you're willing to allow the rounding to change the day/date or not. That is, if the time is, say, "20140908 23:59:59.670", do you...