Viewing 15 posts - 6,811 through 6,825 (of 7,619 total)
akshay.pawar123 (2/11/2013)
GilaMonster (1/9/2013)
ScottPletcher (1/9/2013)
If SQL needs only key column(s), why does it read all the leaf pages of the table as well?? That's an incredible waste of I/O.
The...
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".
February 12, 2013 at 8:07 am
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
ScottPletcher (2/11/2013)
...
I haven't been in any business that worked with dates before 1900, but if yours did, then that would be 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".
February 12, 2013 at 8:01 am
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
ScottPletcher (2/11/2013)
...
I haven't been in any business that worked with dates before 1900, but if yours did, then that would be a relevant concern for you....
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".
February 11, 2013 at 4:09 pm
Sean Lange (2/11/2013)
DON'T use things you don't need, like the BEGIN and END for the proc itself - completely unneeded and doing nothing but cluttering up the screen.
Actually begin...
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".
February 11, 2013 at 4:05 pm
For best performance,
Cluster the table by
LAST_DAY_OF_MONTH
and not the dopey identity column. If you still need the identity (??), you can leave it as the nonclustered pk.
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".
February 11, 2013 at 1:12 pm
You would need to code the time constraints in your WHERE clause. That is, bypass those rows yourself rather than trying to have SQL do it itself.
You might 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".
February 11, 2013 at 1:08 pm
am wondering if the CASE requires all arguments to run against the exact same column?
No. CASE is very flexible: you can compare any columns you need to, in any...
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".
February 11, 2013 at 10:03 am
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
DECLARE @startDate datetime
DECLARE @number_of_months int
SET @startDate = GETDATE()
SET @number_of_months = 7
SELECT
DATEADD(DAY,...
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".
February 11, 2013 at 9:49 am
[superceded by next post]
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".
February 11, 2013 at 9:48 am
Marius.D (2/11/2013)
or maybe you specified DESC on one of the existing index keys?!
I did not, but would that make a difference? I didn't think the missing indexes...
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".
February 11, 2013 at 9:44 am
Method below works for any and all date and language settings:
DECLARE @startDate datetime
DECLARE @number_of_months int
SET @startDate = GETDATE()
SET @number_of_months = 7
SELECT
DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7...
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".
February 11, 2013 at 8:50 am
Hmm, that still doesn't make sense.
Compare them very closely: there must be some difference somewhere ... or maybe you specified DESC on one of the existing index keys?!
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".
February 8, 2013 at 4:13 pm
In SQL Server, there is no difference.
I guess the backward compatability would be that you can still use "numeric" rather than the offical "decimal".
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".
February 8, 2013 at 1:49 pm
Yeah, reasonably close.
Probably the major difference is that the OP and myself don't want to "flatten" the Chain and Store into a single "location".
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".
February 7, 2013 at 5:31 pm
nick.mcdermaid (2/7/2013)
I did change the PK to
RowNum_Start,SRC_System, SRC_Application
and also
SRC_System, SRC_Application,RowNum_Start
(Yes I know you can create a clustered index that isn't based on the primary key)
In both...
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".
February 7, 2013 at 4:06 pm
Viewing 15 posts - 6,811 through 6,825 (of 7,619 total)