Viewing 15 posts - 421 through 435 (of 7,619 total)
You very likely already have a clustered index on the table, on ( ClaimHeaderID, ClaimLineID ). (That's a vastly better clus key than an identity column, btw.)
You can only have one...
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 13, 2023 at 9:52 pm
But you're going to have to include the date in the clustering key to do partitioning anyway, you might as well change the clustering first. And that will be less...
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 13, 2023 at 9:47 pm
By far the best performance payoff for this is creating the best clustered index on the table, as coded below. You would likely not even need to partition then.
Based on...
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 13, 2023 at 7:34 pm
Clustering by the date will solve the performance issues. Partitioning will aid performance only if you need to rebuild the table, in which case you would only need to rebuild...
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 13, 2023 at 3:06 pm
That should be fairly easy using tally tables.
The trickier part would be excluding certain letter combinations, meaning of course you have to determine which ones to omit. For example: 'ASS',...
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 12, 2023 at 7:40 pm
This should give you some idea. With that partitioning, you can no longer have ClaimLineID as a pk (or even a stand-alone unique index/constraint).
If you can use data compression, 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".
July 12, 2023 at 7:37 pm
Not at that level.
I believe ONLINE create/rebuild of indexes is not available in standard.
If you're on SQL 2016 on above, data compression is still available.
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 12, 2023 at 1:14 pm
I would definitely use a conversion table.
Personally I prefer the "A[0-9][0-9]" format.
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, 2023 at 8:32 pm
Here's an alternative, an all-in-one function that directly produces the last_business_days from start_date and end_date params.
If for some reason you needed more than 100 months of results, naturally you'd have...
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, 2023 at 2:08 pm
If you don't already have an index on the dbo.OptionsEOD table to directly support the "LAG(T1.CLOSE_PRICE) OVER PARTITION BY T1.UNDERLYING_SYMBOL, T1.ROOT_SYMBOL, T1.EXPIRATION, T1.STRIKE, T1.OPTION_TYPE ORDER BY T1.QUOTE_DATE)" you should try...
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 3, 2023 at 1:37 pm
as an example - assume a sequence increment of 500 - and you need 5000. ...
you do a "SELECT NEXT VALUE FOR sequencename; " 10 times and you immediately...
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".
June 30, 2023 at 9:44 pm
The most important thing (assuming basics such as enough RAM and CPU, etc. are OK) is likely to analyze missing index data (do not blindly build all indexes SQL suggests). ...
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".
June 29, 2023 at 3:08 pm
SQL Server does not allow duplicate column names in a table.
You could put brackets in the actual column name if you really wanted to, although of course that's a terrible...
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".
June 22, 2023 at 3:03 pm
I agree. Yes, MS might have good reasons for dropping the cache in those cases, but it should document that it will do 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".
June 12, 2023 at 5:31 pm
That seems to be normal SQL Server activity, to clear the plan cache when certain ALTER DATABASE statements are executed. Ref:
https://www.sqlskills.com/blogs/erin/query-store-and-the-plan-cache-flushing/
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".
June 12, 2023 at 2:55 pm
Viewing 15 posts - 421 through 435 (of 7,619 total)