For SQL Server, you should ignore the INFORMATION_SCHEMA views and use system views instead, such as sys.tables, sys.columns, etc..

You should use "total_pages" rather than "used_pages" because the table may currently take up more space, sometimes a lot more space, than it is actually using.

For example, a...

I suggest reviewing the overall index definitions and stats on the table.  Perhaps even the clustering index needs changed to get the best overall performance.  If the clus index does...

SQL 2005 doesn't have a time data type.  It looks like one of your columns in SQL 2012 is time.  That may cause a conversion problem unless you explicitly adjust...

There are times when multiple, narrower indexes would overall perform better.  But that's more difficult to accurately determine and code for.  You must also continue to monitor it, because adding...

chuck.hamilton (1/10/2017)

---

That's simply not true. An instance will not hang because you filled the tempdb database. Queries will fail and the instance will throw errors like 1105, but it most...

In old-style SQL, outside of HAG, you could use OPENROWSET to reach a remote server without having to create a linked server. That may or may not work in...

I specialize in tuning, particularly index tuning, and I tend strongly toward the larger, shared index rather than separate indexes. Be aware that if the index needs scanned, multiple...

Never disable autogrowth on tempdb -- it could bring the entire instance to a waiting state. Instead, put alert(s) into place to make sure you are made aware if/when...

I'd suggest an inline-table-valued-function. That will give you performance, reusability and flexibility.

Although I can't see that CASE statement ever affecting the query plan, unless the result of the CASE...

Is there already a clustered index on the table?

If not, and ID is an identity value, are you sure it's best to cluster the table on ID?? What do...

I agree that the join hints should only be used if you truly understand them.

The critical thing would seem to be that "T3" should almost certainly be clustered on (...

Also, look at the size of the log file in the original db. When you restore, the entire log file must be pre-formatted (even if IFI is on --...

I think the batch number would be:

1 + ((row_number() over (order by custnum) - 1) / 500)