Viewing 15 posts - 2,686 through 2,700 (of 7,619 total)
Don't convert a column to a different data type unless you absolutely can't avoid it (sargability and all). For your situation, this should handle both dates and the -1 properly. ...
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 9, 2019 at 4:13 pm
Thanks for the follow up.
Now it's back to the old approach -- you have to go thru the query plan query by query and tune each separately (and perhaps consolidate...
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 9, 2019 at 4:07 pm
There's nothing per se wrong with CREATEing the table using INTO; indeed, it can help prevent future errors when data types changes, for example. But, you don't want to load...
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 8, 2019 at 10:11 pm
Hmm, interesting. With those symptoms, I'd probably first look for a physical reason. Bad RAM, bad NIC, bad local drive, some physical failing on one node vs the other.
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 8, 2019 at 7:24 pm
An inline TVF should perform better, although how much better it's very difficult to estimate. I've done the best I can converting this in a short time, you may 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".
October 8, 2019 at 6:35 pm
Another version, trying to do the least prep work possible (although for determining day or month first, the entire staging table may need to be scanned):
--Assumes that...
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 2, 2019 at 6:50 pm
ALTER TABLE dbo.table_name ADD MinuteToHour AS CONVERT(char(5), DATEADD(MINUTE, CONVERT(int,[sun_total]), '19000101'), 108);
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".
September 30, 2019 at 6:46 pm
We're seeing only a partial plan with none of the actual stats and no sql query text. I can't offer any real guidance with such limited info.
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".
September 27, 2019 at 8:46 pm
I don't think going back just 1 row will be accurate, at least as I understand the requirements (which, of course, could be incorrect). For example, assume times of:
12:24; 12:25;...
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".
September 27, 2019 at 7:11 pm
Something along these lines should let you generate the code:
DECLARE @sql nvarchar(max)
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 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".
September 27, 2019 at 6:49 pm
Based on my reading of Netezza syntax online:
IF DATE_PART('DOW',CURRENT_DATE) IN ('3','6') THEN
statement
[statement] ...
END 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".
September 27, 2019 at 6:25 pm
The two UPDATEs are already executed in separate transactions. Since you don't have an explicit BEGIN TRANSACTION, every statement will be treated as a separate transaction by default.
Since the subquery...
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".
September 27, 2019 at 3:42 pm
You can add SET ARITHABORT ON to the trigger, but it may not help, because the error may be raised when the trigger is compiled the first time.
I don't understand...
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".
September 25, 2019 at 8:42 pm
You can use SET ARITHABORT and other SET statements within the trigger itself. Those settings will be discarded when the trigger ends, so you can't do any harm to outside...
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".
September 25, 2019 at 6:06 pm
One should always try to avoid a function on a table column being used for lookup. Therefore, for the last JOIN, do this instead:
LEFT JOIN...
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".
September 24, 2019 at 4:55 pm
Viewing 15 posts - 2,686 through 2,700 (of 7,619 total)