Is there a scalar database variable in SQL Server?

Question

We are creating a schema version for our database. Right now we have a database table with a column version and a single row with the version.

Is it possible to make a scalar variable on the database, rather than a table?

Something like:

SET DBNAME.dbo.DBVersion = 1;

Answer 1

You may consider to store a scalar value in an Extended Property attached to your database.

To add an extended property use sp_addextendedproperty

EXEC sp_addextendedproperty @name = N'DBVersion', @value = '1';

To get a value of your property fn_listextendedproperty

SELECT value FROM fn_listextendedproperty(N'DBVersion', default, default, default, default, default, default);

Output:

Value
-----
    1

Further reading:

• Using Extended Properties on Database Objects