Viewing 15 posts - 7,321 through 7,335 (of 7,613 total)
gerard-593414 (7/11/2012)
There are numerous users and want...
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".
July 11, 2012 at 12:12 pm
I think the query below is equivalent to your original one, w/o all the unnecessary join (unless the join on DAY restricts the rows selected from the "report" 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".
July 10, 2012 at 2:45 pm
cfradenburg (7/10/2012)
ScottPletcher (7/10/2012)
Jeff Moden (7/9/2012)
ScottPletcher (7/9/2012)
SELECT
(ident - 1) % 3,
MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0...
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".
July 10, 2012 at 11:11 am
Jeff Moden (7/9/2012)
ScottPletcher (7/9/2012)
SELECT
(ident - 1) % 3,
MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS...
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".
July 10, 2012 at 8:33 am
SELECT
(ident - 1) % 3,
MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS part,
...
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".
July 9, 2012 at 4:41 pm
In general, I too prefer NOT EXISTS, although SQL will often generate identical plans for either one.
It's definitely strongly preferred whenever possible to have an index to support the lookup,...
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".
July 9, 2012 at 4:37 pm
I think this gets you pretty close, w/o having to resort to CLR:
WHERE
--one +/- optional, but must be first if used
PATINDEX('%[+-]%', varcharValue)...
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".
July 9, 2012 at 4:21 pm
SQL Server explicitly discards SET values at the end of the stored proc and, AFAIK, there is absolutely no way to change that directly.
You could, of course, save the desired...
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".
July 9, 2012 at 3:36 pm
Please substitute your db name below,
and your table name in BOTH places below where <your_table_name> appears, then run the queries and post the results.
USE <your_db_name>
SELECT
GETDATE()...
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".
July 9, 2012 at 3:14 pm
You likely should have (at least) column A as the clustered index on the table.
Would need more details and info to determine if another column(s) should be in the clus...
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".
July 9, 2012 at 2:54 pm
SQLKnowItAll (7/9/2012)
ScottPletcher (7/9/2012)
SQLKnowItAll (7/9/2012)
ScottPletcher (7/9/2012)
I know why the date was left out of the Sales table -- it can be derived.
What am I missing... How can it...
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".
July 9, 2012 at 2:39 pm
SQLKnowItAll (7/9/2012)
ScottPletcher (7/9/2012)
I know why the date was left out of the Sales table -- it can be derived.
What am I missing... How can it be derived?
Join...
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".
July 9, 2012 at 2:17 pm
Gullimeel (7/9/2012)
Just a small thing.Why do you have a fill factor of 80 for increasing identity field?Shouldnt it be 100%?
I agree that the fillfactor should be carefully reviewed, but not...
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".
July 9, 2012 at 2:14 pm
OmegaZero (7/6/2012)
I'm not 100% sure of how to handle the AccountSales table though - since if I did it by id the dates would not match up correctly if joined...
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".
July 9, 2012 at 2:07 pm
Dev (7/9/2012)
ScottPletcher (7/9/2012)
Dev (7/9/2012)‘select only a very small % of the total rows’ is applicable to Clustered Index as well for seek operation else it will scan the cluster.
False, of...
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".
July 9, 2012 at 10:29 am
Viewing 15 posts - 7,321 through 7,335 (of 7,613 total)