Viewing 15 posts - 2,311 through 2,325 (of 7,614 total)
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
No, on a non-partitioned table, there is no way to build chunks / parts of indexes (frankly I think there should be, but there isn't).
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 4:37 am
Typically people use a NULLIF like below, to avoid a CASE statement -- whether you prefer that or not is, of course, up to you:
ISNULL(((JobInfo.EstHrs / NULLIF(JobHrs.ActualHrs, 0)) * 100),...
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 25, 2020 at 5:50 pm
If the NULL determination was not a 1-time thing, but needed to be periodically available, I'd use triggers to maintain a counter per column in another table that "told" me...
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 25, 2020 at 3:11 pm
That's what I assumed it meant. Other interpretations didn't make sense. I suppose writing the table name and column name to at least a temporary table and then...
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 25, 2020 at 3:08 pm
I'm not sure specifically what the OP meant by "records [presumably meaning rows] for only the columns that have data in them".
Does that mean rows with NO null columns at...
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 24, 2020 at 6:34 pm
Viewing 15 posts - 2,311 through 2,325 (of 7,614 total)