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...

Since all of table "a" would seem to fit in a single page, flip the table order in the CROSS JOIN, i.e. FROM b CJ a rather than FROM a...

ChrisM@Work (10/8/2015)

---

Greg Edwards-268690 (10/8/2015)

---

NOT EXISTS, from what I understand, is a simple Boolean check.

Aggregates must first gather all results.

I am not at all surprised it is generally faster.

Many times I...

Another possibility to consider:

SELECT CustomerNumber,

CAST(CAST(DATEDIFF(DAY, MIN(SalesDate), MAX(SalesDate)) AS decimal(9, 2)) / (COUNT(*) - 1) / 30.0 AS decimal(9, 2)) AS Avg_Months_Btwn_Sales

FROM dbo.your_tablename

GROUP BY CustomerNumber

--ORDER BY...

drew.allen (10/7/2015)

---

ScottPletcher (10/7/2015)

---

Interesting. Are you sure you don't have those stats backwards? The NOT EXISTS must check the entire table every time to verify that a given row...

Interesting. That's just really not logical to me. The NOT EXISTS() will have to do a full scan of the table...

Michael L John (10/7/2015)

---

ScottPletcher (10/7/2015)

---

Michael L John (10/7/2015)

---

ScottPletcher (10/7/2015)

---

For efficiency, try this. It is also easy to extend this to include/exclude multiple ingredients.

SELECT ra.Recipe_number, r.Recipe_name

FROM (

...

Michael L John (10/7/2015)

---

ScottPletcher (10/7/2015)

---

For efficiency, try this. It is also easy to extend this to include/exclude multiple ingredients.

SELECT ra.Recipe_number, r.Recipe_name

FROM (

SELECT Recipe_number

...

Kim Crosser (10/7/2015)

---

I always find it scary that anyone (including myself :-)) is doing direct SQL to "fix" individual records. There are too many ways that a malformed statement...

For efficiency, try this. It is also easy to extend this to include/exclude multiple ingredients.

SELECT ra.Recipe_number, r.Recipe_name

FROM (

SELECT Recipe_number

FROM tblRecipeAllergen

...

You don't really need a subquery here.

SELECT Student,

CAST(DATEDIFF(MINUTE, MIN([Date]), MAX([Date])) / NULLIF((COUNT(*) - 1), 0) / 60.0 AS decimal(9, 2)) AS Avg_Hours_Btwn_Tests

FROM #Sample

GROUP BY Student

ORDER...

Ed Wagner (10/6/2015)

---

I like the convention child_parent_PK

Where:

child = Name of the table with the foreign key

parent = Name of the table with the primary key being referred to

_PK = constant

For...

I prefer:

tablename__xx_name[&name]|description

where xx is:

PK | CL | IX | CK (check) | TR (trigger) | etc.

Sometimes a column name(s) are not really right and a description is better, such as...

Yes, this is a good situation for shrinking a file.

You might first want to rebuild large table(s), esp. if you can rebuild ONLINE.

I've had this dramatically speed up the shrink...