Long ago (in SQL Server years) many metadata changes were implemented with special one-off stored procedures. For example, we had sp_addindex and sp_dropindex, as well as sp_addtype, sp_droptype, sp_addlogin and sp_droplogin. For changing certain database properties, we had sp_changedbowner and sp_dbcmptlevel, to name a few.
Gradually, Microsoft has started replacing these procedures with the more generic DDL commands ADD, DROP and ALTER. This is both good news and bad news. It’s good because these commands are more efficient and more predictable. It’s bad because the stored procedures were made up of T-SQL code that we could read and actually learn things from! I learned a lot of what I know about SQL Server metadata in the early days by reading the definitions of the system stored procedures.
Some of the changes sort of snuck up on me. I knew for example that sp_dboption had morphed into ALTER DATABASE, but I just discovered recently that you don’t even need to specify the database name. You can use the word ‘current’ to indicate the current database. This change was introduced in SQL Server 2012, but no one told me. For example, we can run the following:
1 | ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON |
However, this doesn’t apply to all options. And it’s not clear which ones.
The general ALTER DATABASE documentation seems to imply ‘current’ applies to all options, except for ones that aren’t even listed on the page: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql.
Now, if you look in the docs for the page for the ALTER DATABASE file options, it doesn’t show using ‘current’ at all. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options.
However, the commands that I’ve tested, including the one to add a new filegroup, and then one to add two files to that filegroup, both seem to work using ‘current’.
And if you look at the page for the ALTER DATABASE SET options https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options, there is a note:
This message admits that using ‘current’ doesn’t work for every option, but it specifically doesn’t tell us which ones. It basically says “Try it and see!”.
So what about changing the database owner? That is a database property and is viewable in sys.databases. However, changing the owner uses a completely different command: ALTER AUTHORIZATION.
Even though you might think that ALTER DATABASE is all you need to change any database property, it just isn’t so.
And there’s more. Now in SQL Server 2016, you can change configuration options for a specific database. I’ll tell you about that feature next time.
