Link/Unkink Static Views?

JackAce

Is there a way to version control the content in views using SSC?

I found a way to create synchronization scripts between views using the SQL Data Compare GUI, but I am not sure whether it is possible using SQL Source Control.

aolcot

Unless I have misunderstood your question, but wouldn't you want to version control the data within the base tables instead seeing as a view is just something referencing those base tables.

James B

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?

JackAce

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

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.

James B

Thanks for the clarification.

I think the scenario you have here is one that we'd not usually expect - we'd normally assume that static data you want to source control lives in its own table, and isn't "mixed" in with other records- so the short answer is you'd need to split it out.

We can certainly look at implementing source-controlling views, but I'm not sure as to the complexity it would involve. I'd suggest you add it as a request over on our Uservoice so that we can gauge the interest from other users to see how in-demand this would be.

Thanks!

JackAce

james.billings wrote:

Thanks for the clarification.

I think the scenario you have here is one that we'd not usually expect - we'd normally assume that static data you want to source control lives in its own table, and isn't "mixed" in with other records- so the short answer is you'd need to split it out.

We can certainly look at implementing source-controlling views, but I'm not sure as to the complexity it would involve. I'd suggest you add it as a request over on our Uservoice so that we can gauge the interest from other users to see how in-demand this would be.

Thanks!

Yes, I understand that this is probably an unusual scenario that is spurned by a questionable design pattern, so I'll probably be a lone voice. In any case, I'll submit the request shortly.

Moving forward, we'll probably refactor the single table into two tables.