sql server global data version

I wonder what is the best way to implement global data version for database. I want for any modification that is done to the database to incerease the ver开发者_开发百科sion in "global version table" by one. I need this so that when I talk to application users I know what version of data we are talking about.

1. Should I store this information in table?
2. Should I use triggers for this?

---

This version number can be stored in a configuration table or in a dedicated table (with one field).

This parameter should not be automatically updated because you are the owner of the schema and you are responsible for knowing when you need to update it. Basically, you need to update this number every time you deploy a new application package (regardless of the reason for the package: code or database change).

Each and every deployment package should take care of updating the schema version number and the database schema (if necessary)

---

I tend to have a globals or settings table with various pseudo-static values stored.
- Just one row
- Many fields

This can include version numbers.

In terms of maintaining the version number you refer to, would this change when the data content changes? If so, the a trigger would be useful. If you mean for the version number to relate to table structures, etc, I'd be more inclined to manage this by hand. (Some changes may be irrelevant as far as teh applications are concerned, or there maybe several changes wrapped up into a single version upgrade.)

---

The best way to implement a "global data version for database" is via your source control system and build process. When all the changes have been submitted and passed testing your build process will increment your versioning number schema.

The version number could be implemented in a stored procedure. The result of the call to the stored proc could be added to a screen in your app so you can avoid users directly accessing a table.

---

To complete the previous answers, I came across the concept of "Migrations" (from the Ruby on Rails world apparently) today, and there was already a question on SO that covered existing frameworks in .Net.

The concept is still to store DB versioning information as data in a table somewhere, but for that versioning information to be managed automatically by a framework, rather than manually by your custom deployment processes:

previous SO question with overview of options: https://stackoverflow.com/questions/313/net-migrations-engine