Viewing 15 posts - 181 through 195 (of 7,619 total)
Typically that's done with a cte that uses ROW_NUMBER(). Easy for first/specific row num, but for last to be easy, you'd have to do a DESC ORDER BY in 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 14, 2024 at 5:56 pm
I prefer a cross-tab approach for these types of queries:
;WITH cte_data AS (
SELECT MyId, PayeeId, ROW_NUMBER() OVER(PARTITION BY MyId ORDER BY PayeeId) AS...
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 14, 2024 at 1:55 pm
Because SQL, deliberately, has delayed verification. This allows you to create a proc before other objects exist. For example, this should run fine:
USE tempdb;
GO
CREATE PROC proc1 AS SELECT * FROM...
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 11, 2024 at 5:18 pm
If you need to update PoolId based on another table, you should do the lookup in the same query as the UPDATE. And there's no reason to update PoolId twice...
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 10, 2024 at 5:57 pm
DECLARE @intID BIGINT
,@bridgeid BIGINT = 1
;
UPDATE dbo.bridge
SET @intID...
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 10, 2024 at 5:50 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...
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:58 pm
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
Viewing 15 posts - 181 through 195 (of 7,619 total)