Viewing 15 posts - 4,366 through 4,380 (of 7,613 total)
Are you certain that query is the one that is deadlocking? So that query wasn't really commented out in the proc?!
Do you have on trace flags, at least 1222,...
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".
March 24, 2016 at 9:32 am
And prefix every column in the query with a table alias. Remember, we have no idea what columns are in what tables.
Which table is "ID" in?
Which table is "NO_OF_PIN"...
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".
March 24, 2016 at 9:19 am
mceventphoto (3/23/2016)
Where b.ITEMCODE = @itemCode
with
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".
March 23, 2016 at 3:00 pm
Sure, you can do it, but if you post the code it's extremely likely we here can re-work the entire method being used and make it much better.
create table #values...
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".
March 23, 2016 at 10:05 am
The year, the 3-digit number and the incrementing number should all be stored as separate columns. You then use a computed column to construct the rptnum column. That...
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".
March 22, 2016 at 11:12 am
Excellent article! My only additional suggestion would be to put your code in the master db, and start the proc name with sp_, so that the code can easily...
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".
March 22, 2016 at 10:35 am
edward_hall76 (3/21/2016)
DBA or developer.
Hmm, overall vastly different qs, but if they are supposed to have experience, here's one q I love to ask ... but give them some time 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".
March 22, 2016 at 9:37 am
Don't see anything better at a relatively quick look, although I would get rid of the double negative logic:
SELECT *
FROM #OrderDetails OD
WHERE OD.ActionCode = 'V' OR...
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".
March 18, 2016 at 4:06 pm
Sergiy (3/17/2016)
TheSQLGuru (3/17/2016)
1) using a temp table (NOT table variable!!)Not sure if there is much of a difference here.
Scoping - yes, quite different.
Can you name anything else?
You can...
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".
March 18, 2016 at 9:35 am
Phil Parkin (3/17/2016)
ScottPletcher (3/17/2016)
Phil Parkin (3/16/2016)
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".
March 17, 2016 at 2:24 pm
Phil Parkin (3/16/2016)
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".
March 17, 2016 at 2:00 pm
Jeff Moden (3/16/2016)
Sergiy (3/16/2016)
Jeff Moden (3/16/2016)
Lynn Pettis (8/6/2012)
An empty string is not null, it is a known value (empty).
I strongly agree. NULL is not NOTHING. NOTHING...
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".
March 17, 2016 at 1:52 pm
You need to review the indexes from scratch. That is, analyze SQL's index usage stats, missing index stats and index operational stats. The longer time period you 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".
March 17, 2016 at 11:56 am
You can reduce the effect on production by using "WITH (NOLOCK)" on the reads, unless you need an absolutely accurate copy of the data in test. If so, you'd...
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".
March 15, 2016 at 1:28 pm
I suggest creating the proc in the master db and marking it as a "system" proc. It will then work within the context of the current db, whatever that...
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".
March 15, 2016 at 10:21 am
Viewing 15 posts - 4,366 through 4,380 (of 7,613 total)