Viewing 15 posts - 2,176 through 2,190 (of 7,613 total)
@ScottPetcher
This is a READ COMMITTED transaction. The lock should only be held for the duration of the statement, not the entire transaction. So that doesn't seem to answer why...
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 6, 2020 at 5:30 pm
SQL probably takes the key lock before / as it's reading the row. SQL's not taking a lock on a "row that doesn't exist", it's taking a lock on 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".
November 6, 2020 at 4:21 pm
Hmm.
Presumably if SQL Server lets you alter the data type, then I would think the index should be good to go?!
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 5, 2020 at 6:11 pm
Every db on an instance is upgraded internally to the current SQL version. A backward compatibility level only affects certain things, not absolutely everything. Thus, yes, it's possible that a...
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 5, 2020 at 2:09 pm
I don't have time to attempt to consolidate the ExportSignal lookups right now.
SELECT
ISNULL(STA.STA_Manufacturer_Code, RDD.rdd_5) as 'Manufacturer Code',
(
SELECT TOP (1) ID2.Content FROM ItemMaster IM
join ItemMasterDetail ID1 on IM.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".
November 4, 2020 at 9:28 pm
CASE
WHEN Phone IS NOT NULL OR MobilPhone IS NOT NULL THEN
CONCAT(CONCAT(LEFT(COALESCE(Phone,MobilPhone),3),'-'),
CONCAT(CONCT(RIGHT(LEFT(COALESCE(Phone,MobilPhone),7),3),'-'),
RIGHT(LEFT(COALESCE(Phone,MobilPhone),12),4)))
ELSE ''
END As PhoneNumber
That's very odd code. It looks like whoever wrote it didn't know...
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 4, 2020 at 1:49 pm
Maybe as below? I've got a feeling CONCAT may be a bit slow, just like some of the other newer functions. FORMAT is notoriously slow.
CASE
WHEN Phone IS...
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 3, 2020 at 8:47 pm
The standard approach is to use ELSE to avoid unnecessary comparisons and show that only one path can be executed. Something like:
IF @param1 = 1 AND @param2...
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 3, 2020 at 4:57 pm
SELECT
FilePath,
LEFT(FilePath, LEN(FilePath) - CHARINDEX('\', REVERSE(FilePath)))
FROM #Path
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 3, 2020 at 4:51 pm
I see no need to use a 4 digit year here. Almost no businesses go back and analyze 100-year-old data, so there's no ambiguity. Even more so if 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".
November 3, 2020 at 3:56 pm
> how to optimize the query from the table design to create index and statisctis ? <<
As Jeff said, you can't, not with the data in its current form.
You should...
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 3, 2020 at 3:46 pm
Since PIVOT requires that you put specific values in the PIVOT, you'd at least have to use dynamic SQL to make a PIVOT work.
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 2, 2020 at 5:59 pm
I do the same thing with transaction log backups... if the "log_reuse_wait_desc" in the sys.databases view contains "NOTHING" for a database, then nothing in the database has...
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, 2020 at 5:01 pm
You could have told us "20 rows" instead of making us count them :-).
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, 2020 at 3:16 pm
Look in the SQL Server error log, there may be additional info there.
Just in case, add another file(s) to tempdb if you can, to give it additional space. You never...
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, 2020 at 12:10 pm
Viewing 15 posts - 2,176 through 2,190 (of 7,613 total)