February 21, 2010 at 1:28 am
RBarryYoung (2/20/2010)
Also there's no Primary Key on InventReorder.
Do we need one? If so, ([Location], [PartNumber]) seems like a candidate.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 21, 2010 at 10:02 am
Paul White (2/21/2010)
RBarryYoung (2/20/2010)
Also there's no Primary Key on InventReorder.Do we need one? If so, ([Location], [PartNumber]) seems like a candidate.
Some things are pretty hard without it. For instance, CTEs with complimentary ROW_NUMBER uses in them are not guaranteed to always produce a consistent ordering without a unique column-values set*. That's a serious problem for many of the "differential" ordering techniques, including ones that I would like to use for this problem.
Probably more importantly, there are a lot of internally implicit ordering assumptions in this Cursor code. That's unreliable and bad practice, as you know, (and possibly flat-out wrong, depending) so I would like to make things more explicit. In particular the ordering of this table is very important to the current procedures logic, assumptions and output sequence assignment.
*(something that Celko discovers belatedly in one of his posts at Simple-Talk)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 10:36 am
Barry,
I see. I hadn't looked closely enough at the nested cursors to see the need for a ranking function on that particular table yet.
But if the key I suggested is unique, that would do for a ranking function, no?
Or are we waiting for mbender to confirm a PK?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 21, 2010 at 12:56 pm
Yes, I am just waiting for confirmation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 4:14 pm
Yes we would need to assign a pkey, it would not be PartNumber or location because those are not always unique.
February 21, 2010 at 9:39 pm
mbender (2/21/2010)
Yes we would need to assign a pkey, it would not be PartNumber or location because those are not always unique.
Sigh. My suggestion was for a compound primary key. The combination of PartNumber and Location is UNIQUE, yes?
If not, please provide your suggestion.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 1:22 am
mbender (2/21/2010)
Yes we would need to assign a pkey, it would not be PartNumber or location because those are not always unique.
We can go with PartNumber + Location as Paul suggests, or add an Identity column to the Reorders table as the Primary Key. You just have to tell us which.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2010 at 1:55 am
I'm tempted to just add a column with the IDENTITY property as PK and be done with it 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 7:19 am
Here is the table with the PKey, a combination of PartNumber and Location would also work, but I like having int's for pkeys. Thanks for your help and patience.
CREATE TABLE [dbo].[InventReorder](
[Location] [nvarchar](50) NOT NULL,
[PartNumber] [nvarchar](50) NOT NULL,
[Quantity] [int] NOT NULL,
[DistribQoh] [int] NOT NULL,
[Pkey] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_InventReorder] PRIMARY KEY CLUSTERED
(
[Pkey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply