I think I understand. You can get all the collectors from a single query, but you have to include the collector in a GROUP BY:

SELECT Collector, COUNT(DISTINCT LoanID) AS...

Script out that index first so you can easily re-create it later if you need to. The index definition itself could still be OK.

That's actually a "good error", in...

Absolutely. If a NULL means 0, then actually put 0, not NULL. NULL is ambiguous, zero is not. If you compress the row, either value won't take...

I would allow NULLs if it's possible a value won't be known. Sometimes unknown is not the same as zero. For example, I might not know the sales...

You don't need MERGE, use UPDATE instead:

UPDATE tt1

SET DealershipName = TT2.[DealershipName],

DealershipAddress = TT2.[DealershipAddress]

FROM TEST_TABLE1 tt1

INNER JOIN TEST_TABLE2 tt2 ON TT2.DealershipCode = TT1.DealershipCode;

Rather than a dynamic trigger, you need dynamic code that generates a static trigger.

That is, after the table is created, run code that dynamically analyzes that table definition and generates...

For one thing, "save to table" is a lot of overhead. Save it to file(s) instead, then load the file(s) into tables later to analyze.

For absolute max speed, you should also cluster the temp table on ID:

SELECT TOP (0) IDENTITY(int, 1, 1) AS ID, ClustKey1, ClustKey2 --, ...

INTO #TEMP

FROM F_POLICY_TRANSACTION

CREATE CLUSTERED INDEX TEMP__CL ON...

When you create the temp table, i.e before it's loaded, cluster it on ( COUNT, MESS ). A nonclustered index will not be efficient. That will speed up...

So is the clustered index on the table permanent, and you just add nonclustered indexes? The clustered index is the most critical index. The best clustered index keys...

I don't have time to line up all the columns, but here's the general format of what you need. I don't see where the computed @FT* values are being...

Eric M Russell (10/12/2015)

---

Kristen-173977 (10/9/2015)

---

Eric M Russell (10/9/2015)

---

Stuff like aliasing column names and stripping quotes from column values can be easily handled within the view or stored procedure, from which...

Even more important would be properly (uniquely) clustering the tblRecipeAllergen table on ( Recipe_number, Allergen ) (and removing AllergenId from the table, as it's not needed).

You're welcome!

Ooh, one last interesting thing. If both the "local" table and the "outer" table have a column with that name, SQL will use the "local" ("closer") table. ...

A subquery can refer to a column in the outer/main query. Thus, SQL is comparing the column to itself. You need to prefix the column with the local...