Interesting, but not definitive.

Would you please run these commands on that database and post the results? That will show what indexes SQL "thinks" are missing, and how existing indexes...

It will cover that specific process as it exists now, yes, but you're doubling one table's size. And you'll have to periodically go back and re-do the "covering" index...

D'OH, sorry, I left off one all-important WHERE condition, an absolute NO-NO for a DBA :-):

;WITH

cteTally10 AS (

SELECT 0 AS tally UNION ALL SELECT 1 UNION...

I strongly agree that you should substitute numeric values in place of the (very) long varchar key columns: [SubscriberID],[UserID] and [DeviceID].

But, until then, based on what you've posted, you should...

Not sure specifically what you're looking for, maybe this can help:

;WITH

cteTally10 AS (

SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL...

Ed Wagner (7/14/2014)

---

I know the name has no functional impact on anything, but I prefer the naming convention that includes the referencing table and the referenced table. Example: Employees_Departments_FK....

A computed age column could not be persisted in any event because it uses a non-deterministic value (todays_date).

Whether you use the MONTH+DAY version or the -YEAR version could be based...

For (very) large tables, typically you do any massive updates in batches if at all possible. Do them by clustered key range; that is, start at a given cluster...

philosophaie (7/14/2014)

---

I am trying to get my head around this Foreign Key stuff.

I have a table: Employees with a column: DepartmentID.

I have another table: Departments with a column: ID that...

Not directly, but you could easily generate that code using sys.columns. I strongly suggest avoiding the INFORMATION_SCHEMA views, as they are less complete as well extremely slow and tending...

If possible, please attach the actual query plan xml as an xml file, rather than just a picture of the plan. There are row counts and other stats available...

fillfactor = 80 is rather low. What did you base that on?

sgmunson (7/10/2014)

---

That has some nasty potential performance with the OR in the JOIN. As we don't know much about the actual desired result, I decided making a guess...

By far the biggest tuning pay back is to get the best clustering index on all tables, esp. large ones. Until you do that, the rest is honestly pretty...

I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id or...