70-432: Database Options

Have you ever cracked open the properties for a database, and looked at all the options available on that page?

If you’re preparing for the 70-432, you might want to spend an hour looking over the options, and what they mean to how the database will run.

This is a quick overview of those options, along with some links to useful information.  This is your chance to look inside my reading list.  These are the reading materials I used to prepare for the exam.  You can start with the BOL Page for Database Properties, or you can use my quick guide and commentary.



This setting controls how character data will be sorted and compared.  I’m basically reading through Working with Collations in the BOL  I have a basic understanding of how collations work.  I’m not a master, but I do know that the CI in collation names means case insensitive.  CS would be case sensitive.  AI is accent insensitive, meaning are you going to treat ‘a’ different than ‘ä’?

Follow the links from the original page, absorb what you can.  I also used the page Windows Collation Sorting Styles in my preparation.



I’ve already covered this topic in an earlier post.  For the 70-432, you’ll need a good instinct for when to use Simple versus Full, versus Bulk Logged.  I feel pretty comfortable about when to choose each, so I’m not going to dig into any BOL articles.


Compatibility Level

We all know there are changes in how SQL Server behaves between versions.  If you upgrade SQL Server and simply attach older database files to the new instance, they’ll continue to use the previous database level until you set the compatibility level to current (As long as the database level is at least SQL Server 2000).

You might want to take a look at this article to see what the different modes can mean for your queries.  There are some pretty big gotchas.  WITH can become required for locking hints in your FROM clause.  You may or may not be able to INSERT records into your views.  At the minimum, you need to realize there are compatibility issues that can come from taking an SQL 2000 database and attaching it to a SQL 2008 Server.



Auto Close, Create Statistics, Shrink, Update Statistics, Update Statistics Asynchronously… Basically do you want the server to do these things automatically.  While I definitely like the idea of automatically creating and updating my statistics, I believe there was less of an uproar when Alderaan was destroyed than there would be if you set your database to Auto Shrink.

Ask #sqlhelp if you’d like confirmation on that one.

I’ve never had a need to change these options, though I do have a database that’s 99% reads for web users.  I am still considering auto close on that database to free the hundreds of outstanding connections.  It’s more likely I can do that, than change how the web clients connect to the database server.



Close Cursor on Commit Enabled, or “do you want to close the cursor when the transaction commits?

Default Cursor, is it Locally scoped or Globally scoped.  If you want more information, check out Scope of Transact-SQL Cursor Names.

My solution to these two option is simply ask why are you using a cursor.  Why don’t we re-write the logic and avoid the cursor?



The next section has a ton of options for setting how NULL values are treated, how padding is treated (trailing spaces anyone?)  This list is worth reading through at least once.  I don’t see the examiners asking you exactly how a comparison will evaluate under a certain condition, but you should be aware you can change how certain operators behave on a database level, rather than having to set behavior options at the beginning of each query.



This option could be important not only during the exam, but in your day-to-day operations as a DBA.  Check out Understanding and Managing the suspect_pages Table.  This option can help you recover from problems on your disks more easily.

This is an area I’ve got to do a bit more reading.  I’m looking at several Google results for “TORN_PAGE_DETECTION versus CHECKSUM”


Service Broker

If you need to use Service Broker with this database, you have to turn it on first.  You can also choose whether or not you use priorities with your service brokers.  This is another area I’ll have to study more.  I know what the Service Broker is, and an idea of how it would be useful, but I’ve yet to implement a solution with it.  I’m hoping to take in a few discussions on it at an upcoming SQL Saturday or at the PASS Summit!

But for now, I’m only scratching the surface.



When you start using these options, you’re applying a good deal of control over the database.  You can set the database READ ONLY.  Useful if you want to make sure users are only reading the data.

You can see the current state of your database.  If you see something other than NORMAL listed here, you really need to know what it’s telling you.  Otherwise you’re in some real trouble!  Check out the BOL article on Database States.

Encryption Enabled, basically are you going to encrypt any data?  If so, you’ll want to begin reading Understanding Transparent Data Encryption (TDE).

Finally Restrict Access, you can limit how the database is used.  This one becomes useful when you want to do some really intense work with your database, and you need to lock down access to just your connection, or to just special users.



This is just a starting point to prepare for the 70-432.  There’s more information about these options than I could ever fit into a single article.  Start here, and if you have any questions, let me know.  I’ll try to direct you to more information!  Good luck with your preparation!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *