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

What is the default TCP/IP port that SQL Server runs on and how can you change it ?

The default port is 1433.  To change that we would use the SQL Server configuration manager as described by MSDN.

Enjoy!

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

What is a view (and is it a view to a kill)?

A view is a great way to combine various pieces of data from multiple tables.  It could also be that you want to do this for security reasons where a user does not have access to one of those tables because of sensitive fields but you give them access to the view because the fields contained in it are safe enough for them to view without exposing the rest of the table.  Views also are useful for a heavily referenced query.  Instead of reusing the query, you just reference the view.

Incidentally, it is only a view to a kill if Mr Bond is involved.  Enjoy!

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

What is instant file initialization?

Instant file initialization allows for a file to be created or also to grow instantly.  Therefore it does require the process to zero out the space inside of the file.   It is important to point out that this only applies to SQL Server data files.  Enjoy!

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!