Viewing 15 posts - 1,486 through 1,500 (of 7,619 total)
I would say the modern approach to doing this is using ROW_NUMBER(), partly because of its inherent efficiency:
SELECT theDate, ID, val
FROM (
SELECT *,...
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 25, 2021 at 4:36 am
(inmate_nbr CHAR(18) NOT NULL PRIMARY KEY, -- required, not optional.
Man... talk about a worse practice ever. If you're going to do something this silly, use a GUID... at...
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 25, 2021 at 4:31 am
SELECT
'DataLake' AS DatabaseName, SchemaName, TableName,
p.Rows AS Row_Count, 'Row count same as yesterday''s row count' AS Message
FROM
DataLake.sys.tables...
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 24, 2021 at 3:23 pm
Doesn't need to be a PK, just the clustering index. The Quality_ID by itself would typically be the (nonclustered) PK.
Create the _test table from scratch. Add the UNIQUE CLUSTERED index...
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 23, 2021 at 10:20 pm
Proper clustering would make the archiving process very fast and easy, since all rows would be contiguous by Quality_Date.
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 22, 2021 at 8:22 pm
If you cluster the table properly -- Quality ( Quality_Date, Quality_ID ) -- you likely won't have to purge them now. That is one of the benefits of properly clustering...
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, 2021 at 4:54 pm
I can't see the point in the clustered index :
CREATE UNIQUE CLUSTERED INDEX dbo.PERSON_HISTORY__CL
ON dbo.PERSON_HISTORY ( OccurredDate, My )...
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, 2021 at 3:22 pm
We need to see the index definitions as well. It seems to me that the index definitions would need to be different to produce plans and stats that drastically different.
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, 2021 at 3:09 pm
I can't see the point in the clustered index :
CREATE UNIQUE CLUSTERED INDEX dbo.PERSON_HISTORY__CL
ON dbo.PERSON_HISTORY ( OccurredDate, My )
...
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, 2021 at 2:54 am
A better option overall is to just have the last history row id stored in the header table (yes, denormalize it).
A trigger on the PERSON_HISTORY table can do that very...
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, 2021 at 12:40 am
Hmm, I remember a WHERE condition on the OccurredDate; maybe I'm mixing this query up with another one.
At any rate, one last time, as I stated earlier:
the history table should...
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 20, 2021 at 11:58 pm
First, the history table should be clustered leading on OccurredDate. That's generally true for log tables. [Forget the stupid myth that "by default, every table should be clustered...
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 20, 2021 at 5:25 am
In addition - make sure you define the PK on the Quality_Test table using a unique constraint (non-clustered) on the Quality_ID column.
That's not technically required on the _Test version...
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, 2021 at 12:28 am
I think it's just this specific thing that bothers me: the first digit of a zip code is meaningless in a business context.
That's why I just can't see myself building...
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 18, 2021 at 9:16 pm
Yes, delay, which will hopefully prevent. For example, rebuild that index every 3 days, or however many days between when it gets too fragmented. If it still fragments too quickly,...
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 18, 2021 at 8:17 pm
Viewing 15 posts - 1,486 through 1,500 (of 7,619 total)