Viewing 15 posts - 2,641 through 2,655 (of 7,613 total)
Don't, don't, don't dismiss development and say "NO!" to developers.
I learnt this from a wise old network manager who told me that you catch more flies with honey...
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".
November 1, 2019 at 5:15 pm
Along with the excellent suggestions above, also look very carefully at the query plans to see if you're hitting a "tipping point" query. That is, one query that returns, for...
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".
November 1, 2019 at 4:52 pm
There's at least one other thread besides this one where someone used it and got the wrong date, because they added a month after the calc with -1 in it. For...
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".
November 1, 2019 at 4:46 pm
People often learn via patterns.
If I see a pattern like "DATEADD(<time_period>, DATEDIFF(..." in consistent code, I know already what the result of the main computation will be. 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".
November 1, 2019 at 2:25 pm
The standard pattern for getting the first day of the month is:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
Stick to that pattern, don't corrupt it with -1 tricks! That just makes it 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".
October 31, 2019 at 3:45 pm
Or re-create the index so that it allows multiple NULLs but no other dups.
Apparently, the morning coffee hasn't kicked in for me yet today... what would the CREATE...
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 30, 2019 at 6:21 pm
Or re-create the index so that it allows multiple NULLs but no other dups.
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 30, 2019 at 3:38 pm
select s.c1,s.c2,s.c3, COUNT(*)*IIF(m.c1 IS NULL,0,1) cnt
from stg_tbl s
left join main_tbl m
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".
October 29, 2019 at 7:54 pm
But my god MSDTC can play havoc with you when you do remote updates.
Yes, quite true. Thus, when you can, with relatively low amounts of data, don't do remote updates. ...
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 29, 2019 at 4:39 pm
Replication has far more headaches than a simple linked server. I'm not against replication when it's really needed, of course, but I don't see that need here. I, too, would...
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 29, 2019 at 3:07 pm
Ok, for a fellow DBA. I actually "stole" this from a Paul White article. To be honest, I wasn't aware that the cursor setting prevented allocation scan reads until I...
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 28, 2019 at 6:22 pm
You can get phantom reads and repeat reads using the default iso level of READ COMMITTED. The only thing NOLOCK adds is dirty reads. And you can greatly reduce 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 28, 2019 at 5:58 pm
I do use SET DATEFORMAT as needed. I agree, it's much easier to do that than to try to rewrite a script. Then reset it asap to its original value...
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 28, 2019 at 5:44 pm
I'd strongly advise against messing with DATEFIRST setting. The code below works under any/all DATEFIRST settings.
declare @date_to_calc_week_of date
set @date_to_calc_week_of = '20191028'
;with cte_date_calcs 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".
October 25, 2019 at 3:01 pm
You don't need multiple counts.
DROP TABLE IF EXISTS #actions;
CREATE TABLE #actions ( action nvarchar(10) NULL )
MERGE
...
OUTPUT $ACTION into #actions
...
DECLARE @insert_count int
DECLARE @update_count int
SELECT @insert_count = SUM(CASE WHEN...
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 24, 2019 at 10:05 pm
Viewing 15 posts - 2,641 through 2,655 (of 7,613 total)