I am working on figuring out how to consolidate an old system into a new system. The old system was hundreds of independant RDBMS at customer sites, the new system is a centrally hosted Sql Server. In my proof of concept, I have created a customer
table in the 'old system' which contains: customer_id (int), customer_name (varchar), sales_person (varchar), and customer_type(varchar).
The old system currently uses 32-bit int's as PKID's through out the system. It is guaranteed that each client of ours will have a customer_id of 1, so... the 'new system' will have a place keeper for the customer_id, but it's REAL primary
key will be an uniqueidentifier, so it looks like this:
CREATE TABLE [dbo].[CUSTOMER](
[UUID] [uniqueidentifier] NOT NULL,
[CUSTOMER_ID] [int] NULL,
[CUSTOMER_NAME] [nvarchar](100) NOT NULL,
[SALES_PERSON] [nvarchar](100) NOT NULL,
[CUSTOMER_TYPE] [nvarchar](100) NOT NULL,
PRIMARY KEY CLUSTERED
(
[UUID] 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
ALTER TABLE [dbo].[CUSTOMER] ADD DEFAULT (newid()) FOR [UUID]
GO
Then I ran a little program to have SqlSyncScopeProvisioning.Apply() create the tracking table for this CUSTOMER table. Since I need to tweak all the stored proc's to handle my unique situation, I when in and looked at them... They all are using
CUSTOMER_ID as the primary key, not uuid.
Why? How do I fix this without changing the name of CUSTOMER_ID? the name UUID can change, just not the other columns, or at least I would prefer for them not to change.
Sam
P.S. I do know a column is needed to differenciate between our different clients, I am trying to take this one baby step at a time.