The most important thing is to:

Cluster the dbo.FreightRate table on ( SourceKey, CompanyKey, PackingType ).

But you could adjust the query slightly also:

SELECT COALESCE(fr1.FreightRate, fr2.FreightRate, fr3.FreightRate, 0)

FROM dbo.Sales s

LEFT JOIN dbo.FreightRate...

To be fair, the number of databases would have an impact here. I've got servers with hundreds (and hundreds) of dbs. Even small files add up to a...

drew.allen (10/4/2016)

---

ScottPletcher (10/4/2016)

---

Create a proc in the master db, beginning the name with "sp_", mark that proc as a system object, then use sp_MSforeachdb to run the proc in the...

Create a proc in the master db, beginning the name with "sp_", mark that proc as a system object, then use sp_MSforeachdb to run the proc in the db(s) you...

DECLARE @start_date datetime;

DECLARE @end_date datetime;

SET @start_date = '20010101';

SET @end_date = '20010101 23:00';

;WITH tally10 AS (

SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) tally(number)

),

tally1000 AS (

SELECT ROW_NUMBER()...

Sorry, just now have time to follow up on this.

CROSS APPLY (CA) really is a type of join, I'm just co-opting it here to assign names to values. You...

I think messing with DATEFIRST when you don't absolutely have to is just asking for issues later. The CROSS APPLYs are used just to simplify the main SELECT list...

thomashohner (9/30/2016)

---

The clustered index issue is hard as these are staging tables and the method we use from change tracking is prone to duplicate inserts sometimes. Its far from ideal....

Great, glad it...

Try this:

...

Ethnicity,

MaritalStatus,

...

In this case, I think it's ok to add the current status to the Orders table, using an efficient, well-written trigger to maintain the status.

However, the lookup of the current...

I can't see any of the indexes, but perhaps you can combine the Lists lookups as below.

Also, you'll probably get best performance by clustering the Lists table by ( Databasekey,...

Probably not, if the space was never used.

The way to try to shrink with no fragmentation is to use...

Several things to consider here. I'll number them so that everyone can comment on them more easily:

1) Make sure IFI is on (just in case).

2) Pre-allocate additional space in...

Sample dynamic SQL:

DECLARE @MySprocParam VARCHAR(50) = 'TestAlias'

EXEC('SELECT ''ASDF'' AS [' + @MySprocParam + ']')