Using T-SQL to Set Database Options

If you prefer to use T-SQL, or if the option you need to set doesn’t appear in the Database Properties dialog, you can use the ALTER DATABASE command to set options. For example, the following command sets AUTO_UPDATE STATISTICS to OFF in the AdventureWorks2012 database:

Click here to view code image

ALTER DATABASE [AdventureWorks2012] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT

Prior to SQL Server 2012, you could also change some of the options by using the system stored procedure sp_dboption. The sp_dboption was removed in SQL Server 2012. This system procedure was an essential tool for DBA’s in the past. Make sure to update any scripts or other places that you may have use this procedure so that they use ...

Get Microsoft SQL Server 2014 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial