Viewing 15 posts - 241 through 255 (of 7,619 total)
You've already got restrictions based on @LOOK_BACK_PERIOD in the WHEREs, it would just be more efficient to pre-filter rather than waiting until some processing has been done on all 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".
March 7, 2024 at 8:41 pm
Did you add the WHERE condition for the TXN_DATE? If you have a long history in the table, that would help the most.
Then try an index on (txn_date, company) include...
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".
March 7, 2024 at 6:17 pm
Need the DDL for the table to sure of more specific recommendations for best speed, but, assuming you will keep a long history in this table:
(1) Either: (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".
March 7, 2024 at 4:51 pm
Need the DDL for the table to sure of more specific recommendations for best speed, but, assuming you will keep a long history in this table:
(1) Either:
(A) cluster the table...
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".
March 7, 2024 at 3:52 pm
I've done similar set ups. Dynamic SQL is definitely a good choice here. To keep the FilterAttribute column consistent, I suggest you use a different column to control include or...
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".
March 6, 2024 at 3:55 pm
The *data* file drives should be 64K. Log file drives should still be 4K. If you mix data and log files on the same drive, you'll want to use 64K.
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".
March 6, 2024 at 3:52 pm
SELECT T3.SYMBOL,
T3.TRADE_DATE,
(AB30_C + AB30_P + AB30_G + AB30_R + AB30_Y) / NULLIF(SUM(CASE WHEN AB30_C = 0 THEN 0 ELSE 1 END + CASE WHEN AB30_P = 0...
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".
March 5, 2024 at 9:48 pm
I would think you would want four ids:
drive full path (other than drive, of course) filename extension
When new data comes in, you'd pre-add any new ids before loading 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".
March 4, 2024 at 8:12 pm
I would think you would want four ids:
drive
full path (other than drive, of course)
filename
extension
When new data comes in, you'd pre-add any new ids before loading the main table. That 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".
March 4, 2024 at 2:24 pm
Somewhat confusing, the right join looks odd, but, anyway, you might try this and see if it produces the result you need:
;WITH DUniques 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".
February 29, 2024 at 4:40 pm
You've got some choices, some more annoying than others:
(1) Use an INSERT trigger to check for dups and cancel if a dup would be created.
(2) Create additional columns that are...
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".
February 27, 2024 at 7:21 pm
That's somewhat confusing. Do you want to not allow mixed case at all? Or do you just want to prevent a duplicate from being inserted, basically ignoring case in determining...
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".
February 27, 2024 at 6:53 pm
;WITH Cte_Persons AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Id) AS Row_Num
FROM @TBLPERSON
)
SELECT
Name,
...
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".
February 26, 2024 at 7:27 pm
You can't use declared RI, but you could write your own.
As to the design, I assume the EntityIDs of the various tables -- Person, Service, Organisation, etc. -- overlap. That's...
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".
February 20, 2024 at 2:56 pm
I think my code would adjust as follows to check program_date for inclusion:
SELECT ca1.*
FROM dbo.cust_sales_temp cst
CROSS APPLY ...
FROM (
SELECT month#, sales, ROW_NUMBER() OVER(ORDER...
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".
February 16, 2024 at 8:12 pm
Viewing 15 posts - 241 through 255 (of 7,619 total)