Viewing 15 posts - 4,726 through 4,740 (of 7,614 total)
Eric M Russell (10/12/2015)
Kristen-173977 (10/9/2015)
Eric M Russell (10/9/2015)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 12, 2015 at 11:54 am
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).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2015 at 1:24 pm
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. ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2015 at 12:01 pm
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2015 at 10:42 am
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2015 at 10:40 am
ChrisM@Work (10/8/2015)
Greg Edwards-268690 (10/8/2015)
Aggregates must first gather all results.
I am not at all surprised it is generally faster.
Many times I...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 8, 2015 at 11:21 am
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 8, 2015 at 9:41 am
drew.allen (10/7/2015)
ScottPletcher (10/7/2015)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2015 at 2:49 pm
Michael L John (10/7/2015)
Nope, I actually double checked
Interesting. That's just really not logical to me. The NOT EXISTS() will have to do a full scan of the table...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2015 at 2:09 pm
Michael L John (10/7/2015)
ScottPletcher (10/7/2015)
Michael L John (10/7/2015)
ScottPletcher (10/7/2015)
SELECT ra.Recipe_number, r.Recipe_name
FROM (
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2015 at 1:55 pm
Michael L John (10/7/2015)
ScottPletcher (10/7/2015)
SELECT ra.Recipe_number, r.Recipe_name
FROM (
SELECT Recipe_number
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2015 at 1:02 pm
Kim Crosser (10/7/2015)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2015 at 12:59 pm
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
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2015 at 9:35 am
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2015 at 9:24 am
Ed Wagner (10/6/2015)
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...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2015 at 1:04 pm
Viewing 15 posts - 4,726 through 4,740 (of 7,614 total)