THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Changing How To Change Your Database Properties — ALTER DATABASE

(This article was originally published at https://www.dbbest.com/blog/ )

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:


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.

ALTER Database

 

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:

alter CURRENT database

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.

 

Have fun!

~Kalen

Published Wednesday, September 20, 2017 9:50 AM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

drsql said:

Excellent!  I learned something the easy way today, by reading your blog!  I find there are so many features I don't see because I am too busy with other ones. I just blogged about AT TIME ZONE, and I only recently learned of the READCOMMITTEDLOCK hint.

Keeps life exciting, I reckon.

September 23, 2017 8:38 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Privacy Statement