Viewing 15 posts - 2,236 through 2,250 (of 7,619 total)
Here's my version, also no recursion, with added alias names for more clarity:
SELECT
start_date, end_date,
full_weeks * 2 +
...
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 16, 2020 at 1:49 pm
Sorry to the OP for this bit of a side issue.
As noted by others, there's no easy, sure way to know which tables are / aren't being used. Periodically capturing...
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 16, 2020 at 1:16 pm
Since it's based on name, I just exclude tables that start with 'tmp_', 'temp_', 'work_' or 'wk_'. Seems just as easy as excluding schemas to me. And I don't 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".
October 16, 2020 at 1:15 pm
I don't use separate schemas (nor would I personally recommend it, given the general hassle of it -- different approaches for different folks). How does one resolve it when 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 15, 2020 at 9:37 pm
Again, as I stated, it took only 1 more byte to store YYYY vs YY. Dates were stored as numeric and not char, also to save space. YYMMDD...
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 15, 2020 at 3:33 pm
OK, if you set the fillfactor to 51 and rebuild you will have far fewer splits, perhaps none, because you've already at least doubled the size of the table. 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".
October 15, 2020 at 2:57 am
You should be able to in this case because whether the called proc inserts into a table or returns the result set directly will be controlled by a new parameter...
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 14, 2020 at 9:41 pm
You're very welcome.
It would help everyone if you marked it as the "Answer". That way future readers know it's been resolved and they don't need to spend time on it...
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 14, 2020 at 7:52 pm
It is very possible you need both indexes. However, developers typically don't really know. You need to check the index usage stats, as Anthony stated, to know for sure.
While you're...
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 14, 2020 at 6:22 pm
1. If the varchar columns repeat values frequently, encode the varchar(50) columns as ints, using a separate encoding/lookup table. This would be, by far, the biggest performance improvement.
2. Page compress...
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 14, 2020 at 6:18 pm
There's almost always an alternative in coding. If you don't want the CROSS APPLY, you can replace "dash_2" everywhere it appears in the SELECT with "CHARINDEX('-', string, PATINDEX('%-----%', string) +...
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 14, 2020 at 6:05 pm
Again, as I stated, it took only 1 more byte to store YYYY vs YY. Dates were stored as numeric and not char, also to save space. YYMMDD took 4...
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 14, 2020 at 5:50 pm
Regarding the fact_OHADiagnosis table:
1) should likely be clustered / partitioned by ( OHADiag_id, OHADiagnosisOrder ). You can have a table that can quickly translate FileYear to beginning and ending OHADiag_id....
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 14, 2020 at 5:38 pm
If you want better performance, I think we do need actual table definitions, esp. including the indexes. It appears the diagnosis id is what's critical. You should likely be partitioning...
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 14, 2020 at 5:26 pm
It also increases the number of characters to be transmitted by a whopping 25% compared with "YYYYMMDD" and it was specified and designed in an age where...
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 14, 2020 at 4:38 pm
Viewing 15 posts - 2,236 through 2,250 (of 7,619 total)