Back to the original post, that code looks far better than most of what I see as a DBA!

Just one serious change, to get rid of the CAST of/function against...

Keep in mind, though, that you could use non-temp table names in tempdb. Tables will have slightly less overhead in tempdb.

It sounds like you have typical "createdate", "createuser", 'updatedate", "updateuser" that you want to exclude.

But do it by column name rather than position. Those won't necessarily be the last...

No, it should not! That's far, far too much overhead.

The trigger should not...

tshad (9/12/2016)

---

True.

That could be an issue if you have rows that large. I have a customer where some of out tables column definitions exceed the row size by hundreds...

tshad (9/10/2016)

---

Which is why, as I mentioned, I use both. I use audit tables on the tables I need to audit but I also have 5 columns on all...

Also maybe check:

SQL Jobs (who knows, maybe a yearly job)

SSIS data sources / SSIS packages

The other really huge performance factor to consider is how the tables are joined. Often natural keys allow for merge joins, which are extremely efficient even on very large...

You can add all 5 columns to a given table in one statement, but you need separate statements for each table.

I reject entirely the notion that there is such a thing as a "default" clustering key. That is the issue to me. While certainly there are times when...

If the overall number of pages to be read is small either way, it's just not worth the optimizer's time to keep going.

SQL naturally favors the clustered index. Overall...

Sean Lange (9/2/2016)

---

ScottPletcher (9/2/2016)

---

A clustering index is enough to avoid having a heap, you don't have to have a primary key. In particular, don't slap an identity column on...

Don't have time right now to fully test/refine this, but it should be close at least:

SELECT DISTINCT

SERVERPROPERTY('servername') [instance]

,DB_NAME() [database]

,QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) +'.'+QUOTENAME(Object_name(p.object_id))

,p.data_compression

,p.data_compression_desc

FROM sys.partitions p

INNER JOIN (

SELECT container_id, SUM(total_pages)...

A clustering index is enough to avoid having a heap, you don't have to have a primary key. In particular, don't slap an identity column on the table just...

Luis Cazares (9/1/2016)

---

Second, cursors can be local or global and the default is...