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
 

Tara said:

July 17, 2018 3:50 AM
 

obat diabetes said:

August 1, 2018 11:14 PM
 

obat bronkitis anak secara alami said:

August 7, 2018 7:47 PM
 

ketty said:

Allassignmenthelp.co.uk Reviews has helped many students to choose the right website. It has also helped the students to score good grades in their academics. Our writing services review will help you to find the best writing websites for your success.

http://assignmentshelpreview.com/all-assignment-help-uk-review/

August 9, 2018 2:09 AM
 

obat sinusitis said:

August 10, 2018 7:20 PM
 

masker wajah alami untuk menghilangkan jerawat said:

https://goo.gl/HQh67p Masker wajah alami untuk menghilangkan jerawat

https://goo.gl/4UJxUL Obat liver alami aman dan terbaik

https://goo.gl/j3PFHJ Obat herbal sinusitis kronis terbaik

https://goo.gl/W5W8F9 Obat bronkitis anak secara alami

https://goo.gl/2AsGLu Obat Kanker Payudara terbaru 2018

https://goo.gl/ssFQGs Cara meningkatkan nafsu makan secara alami

https://goo.gl/KSnnFb Obat herbal diabetes menurunkan gula darah

https://goo.gl/ngiymL Obat herbal kusta terbaik

https://goo.gl/ZsjaLf Obat benjolan di leher tanpa operasi

https://goo.gl/arrkfe Obat Herbal Kanker Otak tuntas hingga akar

https://goo.gl/VBFyi2 Obat TBC alami aman tanpa efek samping

https://goo.gl/ooXyhS Obat radang amandel sembuh tanpa operasi

https://goo.gl/94gZFX Obat asam urat alami tanpa efek sampinfg

https://goo.gl/Ld4VKF Obat tetes Sariawan secara alami

https://goo.gl/8v2oKL Cara mengobati mata bengkak dengan cepat

https://goo.gl/pnP3nA Obat tradisional kanker lambung terbaik

https://goo.gl/96UaW3 Obat kanker paru paru

https://goo.gl/bjzQzS Obat usus buntu alami tanpa operasi

https://goo.gl/PUDELN Obat asam lambung naik cara alami

August 20, 2018 8:56 PM
 

obat herbal koresterol terbaik said:

August 23, 2018 10:55 PM
 

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:19 AM
 

obat benjolan di tubuh said:

August 30, 2018 7:39 PM
 

obat wasir said:

September 4, 2018 8:25 PM
 

Finance-Assignments.com said:

Well thanks for posting such an outstanding idea. I like this blog & I like the topic and thinking of making it right.

https://www.finance-assignments.com

September 5, 2018 3:29 AM
 

famitofu said:

Remember to play it everyday to laugh more http://basketballgames.io

September 14, 2018 2:30 AM
 

obat katarak said:

September 16, 2018 5:44 PM
 

obat maag said:

September 17, 2018 7:09 PM
 

obat limfoma said:

Bismillaahirrohmaanirrohiim

https://goo.gl/a79hGd

September 23, 2018 6:49 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Privacy Statement