Viewing 15 posts - 2,311 through 2,325 (of 7,619 total)
ALTER SCHEMA [dbo] TRANSFER HWData.DesktopSoft_Master;
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".
September 8, 2020 at 3:50 pm
Then your original JOIN is fine and likely the cleanest way to do the join.
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".
September 5, 2020 at 5:34 am
So you want a NULL in the column to match *every* column on the other table that isn't NULL? I.e., NULL is like a wildcard match?
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".
September 4, 2020 at 10:31 pm
I would think you'd want this:
T1 join T2
on ((T1.C1 = T2.C1) OR (T1.C1 IS NULL AND T2.C1 IS NULL))
and ((T1.C2 = T2.C2) OR (T1.C2 IS NULL AND T2.C2 IS NULL))
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".
September 4, 2020 at 8:51 pm
How critical is your application?
You will have an outage between steps 2 and 3 and if the table has foreign key constraints you cannot use truncate table
Don't forget 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".
September 3, 2020 at 9:14 pm
As Jeff noted, include TRUNCATE in here.
I did include TRUNCATE in the trans, from the start. Not exactly sure how else you want the transaction structured.
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".
September 3, 2020 at 8:46 pm
I can understand SQL having to scan the table / index, but I don't see why SQL would need to do a sort. Btw, an asc index will do, 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".
September 2, 2020 at 7:07 pm
Yes, technically TABLOCKX is only for that statement. I guess in theory someone could INSERT a row between the SELECT and the TRUNCATE.
I should have added HOLDLOCK to the first...
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".
September 2, 2020 at 6:57 pm
I have to admit, in this situation I'd likely just use a cursor and a loop, something like below, just because of the complexity, and likely overhead, of recursion 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".
September 2, 2020 at 6:48 pm
Something along these lines:
SELECT TOP (0) *
INTO dbo.maintable_backup
FROM dbo.maintable
--Edit: Added UNION ALL to cancel IDENTITY property, if present
UNION ALL
SELECT TOP (0) *
FROM dbo.maintable
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO dbo.maintable_backup
SELECT...
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".
September 2, 2020 at 3:38 pm
The max list is easy enough to produce. Sorry, I don't fully understand the 'OK' part well enough yet to add that to the query. 'OK' just meaning that 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".
September 1, 2020 at 3:38 pm
Would you please provide directly usable data, i.e. CREATE TABLE and INSERT statement(s) for the sample data. [A splat of data on the screen does us no good to 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".
September 1, 2020 at 10:21 am
Congrats, very nice article. Informative and to the point. And it's so great to see an intersection ("bridge") table without an identity column, instead keyed by the parent's keys, 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".
August 31, 2020 at 7:20 pm
There was very likely different activity on the two servers. That could cause SQL's missing index recommendations to change, which would in turn likely cause BlitzIndex's recommendations to change. (Note:...
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".
August 31, 2020 at 5:59 pm
I think there was a bit of a typo there, CR + LF is (13)+(10), not (10)+(13). You probably also want to replace single CHAR(10)/CHAR(13) chars, to be safe, like...
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".
August 28, 2020 at 3:29 pm
Viewing 15 posts - 2,311 through 2,325 (of 7,619 total)