Product version in SQL Server.

Question

Simple situation. I've created an application which uses SQL Server as database. I could add a table to this database which contains the version number of my application, so my application can check if it's talking to the correct version of the database. But since there are no other settings that I store inside a database, this would mean that I would add a single table with a single field, which contains only one record.

What a waste of a good resource...

Is there another wat that I can tell the SQL Server database about the product version that it's linked to?

I'm not interested in the version of SQL Server itself but of the database that it's using.

(Btw, this applies to both SQL Server 2000 and 2005.)

Answer 1

I'd go with the massive overhead of a varchar(5) field with a tinyint PK. It makes the most sense if you're talking about a product that already uses the SQL Server database.

You're worried about overhead on such a small part of the system, that it becomes negligible.

Answer 2

You're worried about wasting a table? I'd be more worried about putting the data in an unrelated table and confusing all of the developers who come after you.

Answer 3

I would put the connection settings in the application or a config file that the application reads. Have the app check the version number in the connection settings.

Answer 4

Even if there was such a feature in SQL Server, I wouldn't use it. Why?

• Adding a new table to store the information is negligible to both the size and speed of the application and database
• A new table could store other configuration data related to the application, and you've already got a mechanism in place for it (and if your application is that large, you will have other configuration data)
• Coupling the application to a specific database engine (especially this way) is very rarely a good thing
• Not standard practice, and not obvious to someone new looking at the system for the first time

Answer 5

I highly recommend writing the data base version into the database. In an application we maintained over a decade or so we had updates of the database schema every release. When the user started the application after an update installation it could detect if the database was to old and convert it to the newer schema. We actually did an incremental update: In order to get from 7 to 10 we did 7 -> 8, 8->9, 9->10. Also imagine the scenario when somebody restores the database to an older state from a backup.

Don't even think about adding a single table, just do it (and think about the use cases).

Answer 6

If you're using SQL 2005 and up, you can store version info as an Extended Property of the database itself and query the sys.extended_properties view to get the info, eg :

sys.sp_addextendedproperty @name=N'CurrentDBVersion', @value=N'1.4.2'

SELECT Value FROM sys.extended_properties WHERE name = 'CurrentDBVersion' AND class_desc = 'DATABASE'

If SQL 2000, I think your only option is your own table with one row. The overhead is almost non-existent.