Viewing 15 posts - 181 through 195 (of 7,613 total)
In case you want it, here's a function to prep the string as we discussed:
DECLARE @string nvarchar(max);
SET @string = '"A1","A2","A3","A4","A5"
"000066666XYZ",00002,"","","1,000,000"';
SET @string = REPLACE(@string, CHAR(13) + CHAR(10), ',');
SELECT @string, dbo.prepare_string_for_split(@string, DEFAULT,...
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".
June 7, 2024 at 7:49 pm
I look at missing index stats quite frequently. Very often I don't add or update index based on them, but I still review the stats just in case. Sometimes 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".
June 7, 2024 at 6:44 pm
Cluster STXL on ( MANDT, TDOBJECT, TDNAME ) instead of creating all those separate non-clus indexes. If those key columns are not inherently unique, and you can add a single...
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".
June 7, 2024 at 6:36 pm
When I had to do something like this, I created a custom function that would replace commas if, and only if, they were enclosed in double quotes. Using a function prior...
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".
June 6, 2024 at 5:49 pm
(dup post, removed)
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".
June 6, 2024 at 5:01 pm
You can't really accurately consider one index in isolation. You need to look at all indexes on the table AND at all uses of the table.
sys.indexes will give you 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".
June 6, 2024 at 5:00 pm
The clus index must be unique, and it is a lot of overhead for SQL to force uniqueness, which it must do if you don't. You then will often also...
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".
May 31, 2024 at 1:39 pm
No, if the combination of all three columns would always be unique if CHANGENR were added:
dbo.CDPOS ( OBJECTCLAS, OBJECTID, CHANGENR )
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".
May 30, 2024 at 1:49 pm
You've got the correct clustered index on CDPOS, except perhaps that it's not unique. If adding CHANGENR to the clus index will make it UNIQUE, you should do that.
Either way,...
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".
May 29, 2024 at 2:11 pm
If you are putting NOLOCK on an INSERT, UPDATE, or DELETE query, that's not going to do anything. SQL doesn't allow NOLOCK on data change operations as it doesn'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".
May 21, 2024 at 9:58 pm
... but is using surrogate keys really that bad? ...
Surrogate keys are not necessarily bad, if chosen after proper analysis, but just automatically defaulting to using a surrogate...
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".
May 21, 2024 at 1:41 pm
IF the most critical lookup on the table is the one you've posted, and since you're deleting rows frequently, I'd suggest you consider re-clustering the table to allow clus index...
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".
May 21, 2024 at 12:33 am
The ERD is nice, thanks for that.
But I think you need to step back and do the logical design first (designing using Entities and Attributes rather than tables and columns),...
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".
May 20, 2024 at 6:35 pm
SELECT SUBSTRING(ID, ID_start_byte_2, 50) AS ID_you_wanted, ID AS original_ID
FROM dbo.table_name tn
CROSS APPLY ( SELECT CASE WHEN LEFT(ID, 1) = 'D' THEN 2 ELSE 1 END AS ID_start_btye_1 ) AS ca1
CROSS...
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".
May 16, 2024 at 7:37 pm
n/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".
May 14, 2024 at 1:13 am
Viewing 15 posts - 181 through 195 (of 7,613 total)