james.billings wrote:As aolcot said, I'd expect you to simply source control the table data that the view references. Is there a specific reason you'd like to source control the view interpretation of this?
Well, perhaps I should have explained a little more.
We have a table that has system-defined lookup data as well as user-defined data. Not the greatest database design, I agree, but it is a design that I have inherited.
So the underlying table looks like this
- Code: Select all
MyID BIGINT IDENTITY(1, 1), -- Primary Key
MyCode VARCHAR(50), -- Unique values when UserID is NULL
UserID BIGINT NULL,
MyDescription VARCHAR(200) -- Data that we are concerned with
-- other columns go here
The view does not contain the Primary Key and is schema bound. It also filters out records where UserID is not NULL. In the view, there is a unique constraint/index defined for the MyCode field.
We rely on the MyCode field to identify records that we need. The underlying Primary Key values may be different in Development, QA, Staging and Production.
So if we want to version control the content that we care about (values in the table where the UserID is NULL), then I figured that we could do it by versioning the content in the views.
If I pull up the SQL Data Compare GUI and allow views to be shown, it gives me the option of synchronizing the views. This works fine. If I create a record in the table that has MyID of 10 and MyCode of 'happy birthday', then a record will be created in the other table with MyID of 101 and MyCode 'happy birthday', assuming that 101 is the next IDENTITY record in the table. If you update MyDescription in the first table to 'You live in a zoo' for the 'happy birthday' record, then the corresponding record in the second table will also update, even if the underlying MyID value is different.
The problem is that there is no way to detect/commit changes to source control when changes are made to the data.
I wouldn't be surprised to hear that I am going about this in a very backwards way. If there is a more elegant solution (besides putting the data in a separate table), I'd love to hear it.