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

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