Setting version on SQL Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Guys,

Iam trying set a version number to a database table. The main reason is if i
add a new column later on i will be able to make sure that table schema need
to be updated.

I have tried updating the version column in the sysobjects for that table.
but couldn't do that. Is it a good way to versioning the table or some other
better mechanism for this. Please help me ..

Hari
 
Hi Hari,

Regarding this issue of versioning the table, I would like to tell you that
we should try to never update the system tables in any way unless instructed
to do so by Microsoft.

In oder to accomplish this, we can store the database or table versions in
another table. A simple example of this woild be as follows :



Create Tabledbo.TableVersion (
TableOwner NVARCHAR(128) NOT NULL,
TableName NVARCHAR(128) NOT NULL,
TableVersion DECIMAL(4, 2) NOT NULL
Primary Key (TableOwner, TableName) )



Hope this helps.



Regards,

Mona [Grapecity]
 
Thanks for your suggetion Mona. The problem iam finding with this approach
is, if someone delete the table and recreate it with out knowing the version
table, the new table will be out of sync with version. I mean the new table
will be become a brand new table but the version info will be independant. So
thats the reason i like to connect the table maintenance with systables. Any
suggetions?

Thanks
Hari

Mona said:
Hi Hari,

Regarding this issue of versioning the table, I would like to tell you that
we should try to never update the system tables in any way unless instructed
to do so by Microsoft.

In oder to accomplish this, we can store the database or table versions in
another table. A simple example of this woild be as follows :



Create Tabledbo.TableVersion (
TableOwner NVARCHAR(128) NOT NULL,
TableName NVARCHAR(128) NOT NULL,
TableVersion DECIMAL(4, 2) NOT NULL
Primary Key (TableOwner, TableName) )



Hope this helps.



Regards,

Mona [Grapecity]



Hari said:
Hi Guys,

Iam trying set a version number to a database table. The main reason is if i
add a new column later on i will be able to make sure that table schema need
to be updated.

I have tried updating the version column in the sysobjects for that table.
but couldn't do that. Is it a good way to versioning the table or some other
better mechanism for this. Please help me ..

Hari
Click to expand...
Click to expand...
 
Hi,

I believe best way for this is to have a good policy and permissions on who
could do specific staff with the database. If any person could delete any
table it is not good. It should be dedicated person available who will
maintain database, make backup copy of it and issue permissions. Otherwise
versioning will not help much.

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Hari said:
Thanks for your suggetion Mona. The problem iam finding with this approach
is, if someone delete the table and recreate it with out knowing the
version
table, the new table will be out of sync with version. I mean the new
table
will be become a brand new table but the version info will be independant.
So
thats the reason i like to connect the table maintenance with systables.
Any
suggetions?

Thanks
Hari

Mona said:
Hi Hari,

Regarding this issue of versioning the table, I would like to tell you
that
we should try to never update the system tables in any way unless
instructed
to do so by Microsoft.

In oder to accomplish this, we can store the database or table versions
in
another table. A simple example of this woild be as follows :



Create Tabledbo.TableVersion (
TableOwner NVARCHAR(128) NOT NULL,
TableName NVARCHAR(128) NOT NULL,
TableVersion DECIMAL(4, 2) NOT NULL
Primary Key (TableOwner, TableName) )



Hope this helps.



Regards,

Mona [Grapecity]



Hari said:
Hi Guys,

Iam trying set a version number to a database table. The main reason is
if i
add a new column later on i will be able to make sure that table schema need
to be updated.

I have tried updating the version column in the sysobjects for that
table.
but couldn't do that. Is it a good way to versioning the table or some other
better mechanism for this. Please help me ..

Hari
Click to expand...
Click to expand...
Click to expand...
 
Back
Top