Let us continue our question of the day series with day sixteen.

What is index fragmentation ?

Index fragmentation occurs as records are added, deleted or updated over time.  As data on a page fills the page, a page split occurs moving half of the data to a new page created after all of the other pages in the table.  As you can see, when the engine is searching for data it must bounce around the pages instead of moving along contiguous pages.  As the level of fragmentation rises, query performance will degrade considerably so regular maintenance is a good idea.

Enjoy!

Let us continue our question of the day series with day fifteen.

What is the function of the tempdb database (given the last three posts, did you really think we would leave out the last system database)?

The tempdb database stores global and local temporary tables as well as temporary stored procedures, table variables, and cursors (unfortunately).  It also contains internal objects used for spools and sorting.  Tempdb is recreated every single time that the instance is restarted making it unique among the system databases.  Nothing is ever saved from one session to another hence the moniker, temporary database.

Enjoy!

Let us continue our question of the day series with day fourteen.

What is the msdb database (did it really start as the home address directory for Bill Gates) ?

The msdb database stores scheduling jobs and alerts for the SQL Server Agent as well as database mail and service broker.  However, there was no word on Bill Gates personal address.

Enjoy!

Let us continue our question of the day series with day thirteen.

What is the purpose of the master database (besides handling the puppets)?

The master database is the heart and soul of your SQL Server.  It stores all of the system-level information for your system such as system configuration and linked servers.   In previous versions SQL Server used to store system objects in the master database, but they are now stored in the resource database.

Check out my interview with the mysterious resource database.  Enjoy!

Let us continue our question of the day series with day twelve.

What is the purpose of the model database (and does it look great in a swimsuit)?

The model database is an aptly named template for all of the newly created databases on your instance.  If you wish for every database to be configured with a certain option, such as autogrowth of 64 MB, then you would set the option on the model database.  At that point, every new database would have the same autogrowth setting.  Keep in mind, if you change the model database at a later date, the previously created databases will not inherit the changes only databases created from that point forward.

Also, keep in mind that there are a few options that cannot be set on the model database such as enabling change data capture.  Enjoy!

Let us continue our question of the day series with day eleven.

What is a DMV (and will there be a long line there)?

DMVs are Dynamic Management Views and you will hear them tossed around alot.  DMVs are an amazing tool introduced in SQL 2005 to replace querying system tables in SQL Server 2000.  However, they give you a plethora of internal information for troubleshooting purposes.  Want more information?  Check out the MSDN reference page for a full listing and categorization of DMVs.

Enjoy!

Let us continue our question of the day series with day ten.

Where are the recovery models for SQL Server?

There are three different recovery models for SQL Server:  simple, full, and bulk-logged.  The simple recovery model allows you to recover back to your last database or differential backup.  If that is nightly, then all of the changes since then will be lost.  The full recovery model allows for no work to be lost unless there is damage to the log file.  Finally, bulk-logged recovery model allows for the most high performance and smaller log file storage.

Enjoy!

Let us continue our question of the day series with day nine.

Where does referential integrity mean?

Referential integrity means that when a table references another table the foreign key must always point to a real and valid row in the corresponding table.  In other words, if you have an order table and an order line item table and you delete the order table, there should not be an order line item records for that order.  Referential integrity ensures that those related records are update or deleted accordingly.  The tables are in essence synchronized.

Enjoy!

Let us continue our question of the day series with day eight.

Where are different types of triggers?

There are two types of triggers, Data Manipulation Language triggers (DML) and Data Definition Language triggers (DDL).  DDL triggers are fired whenever Drop Table, Alter Table, Create Table or Login events.  DML triggers are fired instead of or after the triggering event.

Enjoy!

Let us continue our question of the day series with day seven.

Where are SQL Server usernames and passwords stored?

SQL Server usernames and passwords are stored in the System Catalog Views: sys,server_principals and sys.sql_logins.  Keep in mind that the passwords are encrypted.

Enjoy!