Viewing 15 posts - 2,671 through 2,685 (of 7,613 total)
The problem with the all digits string is it can be mistaken for an integer.
100% false. Date literals are strings, or delimited in some other way to distinguish them 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 11, 2019 at 5:13 pm
This might give better performance, if the optimizer recognizes the chance:
WHERE AKey LIKE '[ABCDEGJ]%' AND (AKey LIKE 'AAA%' OR AKey LIKE 'BBB%' OR AKey LIKE 'CCC%' OR...
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 10, 2019 at 5:36 pm
And if my suggestions are broken, are so bad, why do the recent DATE and DATETIME2(n) data types default to it?
Default to what exactly? We know they're not stored in...
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 10, 2019 at 5:27 pm
I disagree; when at all possible, you want to make the clustered index unique yourself whenever possible. Yes, SQL will always force it to be unique anyway, but that often...
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 10, 2019 at 3:21 pm
I'd say just use > CHAR(0) if you're going to go that route. Btw, wouldn't you have to use >= CHAR(9), just in case only a tab char was there?
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 10, 2019 at 3:10 pm
And that's why I wrote the code the way I did. The other way is somewhat easier to code, but potentially far worse in performance.
The underlying rule is:
Never use 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, 2019 at 10:17 pm
2) these display strings sort correctly in temporal order. They are not ambiguous.
As I stated, In the real world dates are sometimes given as YYYY-DD-MM for certain regions of 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".
October 9, 2019 at 10:07 pm
Actually, the format YYYYMMDD is perfectly acceptable under ISO. I don't know why Celko insists on repeating his false claim over and over about YYYY-MM-DD despite being told that it's...
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, 2019 at 8:16 pm
You probably need something like this:
WHERE (AKey LIKE 'AAA%' OR AKey LIKE 'BBB%' OR AKey LIKE 'CCC%' OR AKey LIKE 'DDD%' OR
AKey LIKE...
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, 2019 at 8:10 pm
Don't convert a column to a different data type unless you absolutely can't avoid it (sargability and all). For your situation, this should handle both dates and the -1 properly. ...
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, 2019 at 4:13 pm
Thanks for the follow up.
Now it's back to the old approach -- you have to go thru the query plan query by query and tune each separately (and perhaps consolidate...
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, 2019 at 4:07 pm
There's nothing per se wrong with CREATEing the table using INTO; indeed, it can help prevent future errors when data types changes, for example. But, you don't want to load...
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, 2019 at 10:11 pm
Hmm, interesting. With those symptoms, I'd probably first look for a physical reason. Bad RAM, bad NIC, bad local drive, some physical failing on one node vs the other.
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, 2019 at 7:24 pm
An inline TVF should perform better, although how much better it's very difficult to estimate. I've done the best I can converting this in a short time, you may need...
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, 2019 at 6:35 pm
Another version, trying to do the least prep work possible (although for determining day or month first, the entire staging table may need to be scanned):
--Assumes that...
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 2, 2019 at 6:50 pm
Viewing 15 posts - 2,671 through 2,685 (of 7,613 total)