Viewing 15 posts - 166 through 180 (of 7,619 total)
For one thing, don't force only LOOP joins, YIKES!
To tell you anything else, would need much more details on the tables and parameters.
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".
July 8, 2024 at 8:20 pm
The data seemed inconsistent / "impossible" to me. How can the left-most 50 bytes be '3006' when that doesn't appear in the original value at all??
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".
July 3, 2024 at 9:46 pm
Don't do online, do offline. That should be ok if you have scheduled down time anyway.
Otherwise, hard to say without more details. As stated above, you may not really need...
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".
July 2, 2024 at 1:45 pm
SELECT DISTINCT UPC, STARTDATE, [MASTERCHAINNAME], REPLENTYPE,
CASE WHEN [REPLENTYPE] IS NULL THEN 'Not in CKB'
WHEN [REPLENTYPE] = 'NONE' THEN 'Non Replenishable'
ELSE 'Replenishable' END AS 'Replentype'
FROM dbo.tablename t1
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".
July 1, 2024 at 9:58 pm
Assuming that AVALUE is based on the ID value (that is, the same ID always has the same AVALUE), then you can leave AVALUE out of the GROUP BY. If...
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 28, 2024 at 5:50 pm
Or take advantage of SQL's inherent capabilities by using the sql_variant data type (yes, technically you don't need the second CAST):
SELECT DISTINCT
...
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 27, 2024 at 1:56 pm
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
Viewing 15 posts - 166 through 180 (of 7,619 total)