Viewing 15 posts - 2,236 through 2,250 (of 7,613 total)
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
The good-ole standard tally table should handle this nicely. I default to end of calendar year for end date; naturally change that if/as you need to.
DECLARE @end_date...
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 3:09 pm
Assuming you consider only whole months, truncating any remaining days, then:
declare @age varchar(6)
declare @dob datetime
set @dob = '2015-07-31 00:00:00.000'
select @age = cast(months_old / 12 as varchar(3)) +...
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:02 am
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 110-300 baud...
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 8:46 am
GUIDs are horrendous for clustered indexes. They fragment like crazy. And a guid is always a single key lookup, not a range (at least in my experience), thus a non-clus...
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, 2020 at 10:07 pm
CREATE TABLE Employee_Leave
also state that your CHECK for the start date being less than or equal to the end data will fail if the end date is not...
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, 2020 at 10:00 pm
I believe all you need to do is specify the proper PARTITION BY clause in the code (new code is below). The code originally comes from Itzik Ben-Gan (I believe),...
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, 2020 at 7:13 am
Viewing 15 posts - 2,236 through 2,250 (of 7,613 total)