Viewing 15 posts - 1,486 through 1,500 (of 7,613 total)
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
Who t.h. is "Scoot", Jeft?
Hmm, so you allow ad-hoc tables with NO design process or thought, i.e. no data modeling, to be created and deployed? Interesting.
At any rate, how do...
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:15 pm
Actually they got the structure right for this table: the clustered index is what's critical, and it's not fragmented (presumably).
To help correct the PK but NONCLUSTERED index -- which again...
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 7:29 pm
And I still say you need business input before deciding on what to put in any table you create. I don't think you can properly model the data until then,...
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 7:23 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 on IDENTITY"....
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 4:22 pm
Again, I'm still sticking with code, such as a function, for this rather than a physical table until I see an actual need for a table. The first digit alone...
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 3:59 pm
But any lookup table does NOT need to contain a list of all zip codes as originally stated. If the "slab" is what is significant, the lookup only needs to...
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 2:58 pm
Viewing 15 posts - 1,486 through 1,500 (of 7,613 total)