Viewing 15 posts - 5,956 through 5,970 (of 7,619 total)
And I realize that an absolute rule is not the intent of what you're doing, but that is what very often happens now. Identity keys have become so much...
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 16, 2014 at 11:12 am
But that's the problem that is over-looked in the all-out desire to achieve a "small clustering key". Often the "always a small clustering key" approach requires creating far more...
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 16, 2014 at 11:08 am
I think that Query1 data got scrambled or something.
Can you use a spreadsheet instead? After you run the queries, in the Results/Output area, left-click in the empty box...
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 16, 2014 at 10:17 am
The Dixie Flatline (7/15/2014)
Statement of fact: No single clustered index can ideally serve the needs of multiple queries searching against different columns of the table.
Another statement of fact:...
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 16, 2014 at 8:29 am
The Dixie Flatline (7/15/2014)
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 15, 2014 at 3:21 pm
You don't need to explicitly convert it. Just get the format to a string of YYYYMMDD, which is always a valid date/datetime format:
SELECT DATEADD(HOUR, @hour, CAST(@date AS varchar(8)))
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 15, 2014 at 1:48 pm
Here's a few thoughts:
If T270.C18133 might often be less than 19 bytes, add this to the WHERE clause:
WHERE
LEN(T270.C18133) >= 19
Since you don't have an ending %,...
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 15, 2014 at 12:57 pm
Interesting, but not definitive.
Would you please run these commands on that database and post the results? That will show what indexes SQL "thinks" are missing, and how existing 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".
July 15, 2014 at 10:42 am
It will cover that specific process as it exists now, yes, but you're doubling one table's size. And you'll have to periodically go back and re-do the "covering" index...
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 15, 2014 at 10:19 am
D'OH, sorry, I left off one all-important WHERE condition, an absolute NO-NO for a DBA :-):
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 1 UNION...
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 14, 2014 at 2:30 pm
I strongly agree that you should substitute numeric values in place of the (very) long varchar key columns: [SubscriberID],[UserID] and [DeviceID].
But, until then, based on what you've posted, you should...
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 14, 2014 at 1:54 pm
Not sure specifically what you're looking for, maybe this can help:
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL...
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 14, 2014 at 1:05 pm
Ed Wagner (7/14/2014)
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 14, 2014 at 12:31 pm
A computed age column could not be persisted in any event because it uses a non-deterministic value (todays_date).
Whether you use the MONTH+DAY version or the -YEAR version could be based...
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 14, 2014 at 11:53 am
For (very) large tables, typically you do any massive updates in batches if at all possible. Do them by clustered key range; that is, start at a given cluster...
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 14, 2014 at 11:37 am
Viewing 15 posts - 5,956 through 5,970 (of 7,619 total)