Viewing 15 posts - 1 through 15 (of 7,612 total)
A leading datetime clustering key would not be a problem at all, i.e., there's no reason to exclude a datetime from a clustering key. As you, and others, have noted,...
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".
May 21, 2026 at 4:21 pm
Hmm, it would have been best to put a data change capture in place before the mods occurred. If the mods aren't too long and you can go back to...
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".
April 22, 2026 at 8:52 pm
Something like this should do it:
DECLARE @date date;
SET @date = GETDATE();
SELECT CONVERT(varchar(8), @date, 112) AS DateKey, StorageTier,
COUNT(DISTINCT FileId) AS FileCount, SUM(SizeBytes) 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".
April 8, 2026 at 6:06 pm
Performance wise, you don't want the db to contain more than 5,000 to 6,000 tables when using that script.
To limit the tables to process, specify a different table name pattern:
...
SET...
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".
March 5, 2026 at 11:24 pm
There is no automatic formula that can do this analysis for you, it has to be done by a knowledgeable person.
If you have a critical table(s) you want reviewed, 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".
March 5, 2026 at 11:22 pm
For clarity, and potentially accuracy for more complex "OR" conditions, I suggesting another set of parentheses:
WHERE
((@Type = 'AB' AND PlanDescription IN ('DEF','GHI')) OR
(@Type = 'CD' AND PlanDescription IN ('UVW','XYZ'))
)
Yes, the original...
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 16, 2026 at 10:01 pm
What table is vision_flag in? Helps for others to follow your code if you use aliases for table and then ALWAYS add an alias for every column name used 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".
February 6, 2026 at 10:06 pm
> 3 columns (2 int and 1 bigint) <
This could be a (rare) case where partitioning is a good idea; partitioning being based on one/both of the 2 int leading...
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".
January 28, 2026 at 12:19 am
Actually, none of the suggested code, since you should always specify NULL or NOT NULL when ADDing columns to be sure you get the specific setting you 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".
January 21, 2026 at 7:22 pm
You can also do it with a single result set. Which performs better would depend, so you'd have to test that out.
This method would also be much easier if 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".
December 10, 2025 at 9:10 pm
Great, glad it helped. Many thanks for the feedback.
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".
December 10, 2025 at 9:04 pm
I'm not sure PIVOT can give you the results you want.
Maybe try this instead?:
SELECT
COALESCE(BBBB.STOCK_CODE, CCCC.STOCK_CODE) AS STOCK_CODE,
COALESCE(BBBB.MNEMONIC, CCCC.MNEMONIC)...
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".
December 10, 2025 at 7:58 pm
D'OH, obviously there is an mbi column, since it's directly referenced in the query.
The point here is that, in a subquery, if you reference a column that exists in 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".
December 9, 2025 at 4:15 pm
Is there an MBI column in table "edi.dbo.PEC_RGT_EDI_834_Inbound_Exception"?
If so, the second query works because it is comparing xx.MBI to itself, which will always be equal (unless MBI is NULL, oc).
x.MBI...
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".
December 9, 2025 at 2:44 pm
My thoughts on this:
(1) Decide whether you want to include nvarchar or not.
(2) Gen a list of the tables to be changed: then, if you need to, you can custom...
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".
December 1, 2025 at 8:57 pm
Viewing 15 posts - 1 through 15 (of 7,612 total)