Viewing 15 posts - 166 through 180 (of 7,613 total)
The above general approach should work, there just may be a more efficient way that's not popping into my head right now.
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 21, 2024 at 8:44 pm
We could brute force it; I'll try to think of a better way when I get a chance:
;WITH cte_person AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY...
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 21, 2024 at 7:05 pm
Combining them wouldn't optimize it. For three different tables, SQL must still scan each table/covering index to provide the results.
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 19, 2024 at 7:34 pm
Realistically at this point, I'm just trying to gather a better understanding of what is going on here for my own sanity. If the Execution plan shows SQL expects...
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 19, 2024 at 5:32 pm
Personally, I'd try using a temp table and see if it works well before I spent lots of time trying other work-arounds:
declare @p3 dbo.IdList
select * into #p3 from @p3
create unique...
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 19, 2024 at 3:17 pm
I don't know Snowflake SQL, but based on Google for their syntax, derived tables are allowed in an UPDATE:
UPDATE target SET v = b.v /* actual UPDATE example from Snowflake...
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 17, 2024 at 5:37 pm
I think it's easier to use ROW_NUMBER(), like this:
;WITH cte_STUDENTIDPOOL AS (
SELECT STATESTUDENTID, ROW_NUMBER() OVER (ORDER BY STATESTUDENTID) AS row_num
...
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 17, 2024 at 1:50 pm
Yes, that is correct.
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 15, 2024 at 5:50 am
Roughly:
;WITH cte_person AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY hPerson ORDER BY hID) AS row_num,
ROW_NUMBER() OVER(PARTITION...
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 6:00 pm
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
Viewing 15 posts - 166 through 180 (of 7,613 total)