Professional Microsoft SQL Server 2008 Programming

Robert Vieira

• Publisher: Wrox
• Year: 2010
• Print ISBN: 9780470257029
• eBook ISBN: 9780470632093
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

This book is written for SQL Server 2008. However, it does maintain roots going back a few versions and looks out for backward compatibility issues with SQL Server 2005 and SQL Server 2000. These versions are old enough that there is little to no time spent on them except in passing.

The book is oriented around developing on SQL server. Most of the concepts are agnostic to what client language you use although the examples that leverage a client language general do so in C#. For those who are migrating from early versions of SQL Server, some "gotchas" that exist any time a product has versions are discussed to the extent that they seem to be a genuinely relevant issue.

This book assumes that you have some experience with SQL Server and are at an intermediate to advanced level. The orientation of the book is highly developer focused. While there is a quick reference-oriented appendix, there is very little coverage given to beginner level topics. It is assumed that you already have experience with data manipulation language (DML) statements and know the basics of the mainstream SQL Server objects (views, stored procedures, user defined functions, etc.). If you would like to brush up on your knowledge before diving into this book, the author recommends reading Beginning SQL Server 2008 Programming first. There is very little overlap between the Beginning and Professional books and they are designed to work as a pair.

1

Being Objective: Re-Examining Objects in SQL Server

If you're someone who's read my Professional level titles before, you'll find we're continuing the path we started in Professional SQL Server 2005 Programming and have the “Professional” become a little bit more “Pro” in level. That said, I still want to touch on all the basic objects and also address some things like new data types and additional objects that are new with SQL Server 2008.

So, What Exactly Do We Have Here?

Seems like sort of a silly question doesn't it? If you're here reading this title, you obviously know we have a database, but what makes up a database? It is my hope that, by now (meaning by the time you're ready for a professional level title), you've come to realize that a Relational Database Management System (RDBMS) is actually much more than data. Today's advanced RDBMSs not only store your data, they also manage that data for you, restricting what kind of data can go into the system, and also facilitating getting data out of the system. If all you want is to tuck the data away somewhere safe, you can use just about any data storage system. RDBMSs allow you to go beyond the storage of the data into the realm of defining what that data should look like—this has never been more true than with SQL Server 2008. Improved support for hierarchies means that you can store hierarchical data in a far more native way, and still access it very efficiently. The new Policy Based Management feature allows you to control many elements of how your data is administrated using a rules-driven approach. SQL Server also provides services that help automate how your data interacts with data from other systems through such powerful features as the SQL Server Agent, Integration Services, Notification Services, the increasingly popular Reporting Services, and more.

This chapter provides an overview to the core objects used in SQL Server. Much of what is discussed in this chapter may be old news for you at this stage of your database learning, so this is the only point in the book where we will call them out in broad terms of how they relate to each other. I will assume that you are already somewhat familiar with most of the objects discussed here, but the goal is to fill in any holes and fully prepare you for the more advanced discussions to come.

In this chapter, we will take a high-level look into:

• Database objects
• Data types (including some that are new with SQL Server 2008!)
• Other database concepts that ensure data integrity

An Overview of Database Objects

An RDBMS such as SQL Server contains many objects. Object purists out there may quibble with whether Microsoft's choice of what to call an object (and what not to) actually meets the normal definition of an object, but, for SQL Server's purposes, the list of some of the more important database objects can be said to contain such things as:

The database itself	Indexes
The transaction log	CLR assemblies
Tables	Reports
Filegroups	Full-text catalogs
Diagrams	User-defined data types
Views	Roles
Stored procedures	Users
User-defined functions	Encryption Keys

This is far from being a comprehensive list, and is in no particular order, but it does give you some of a feel for the breadth of objects that your SQL Server can manage.

The Database Object

The database is effectively the highest-level object that you can refer to within a given SQL Server. (Technically speaking, the server itself can be considered to be an object, but not from any real “programming” perspective, so we're not going there.) Most, but not all, other objects in a SQL Server are children of the database object.

If you are familiar with old versions of SQL Server you may now be saying, “What? What happened to logins? What happened to Remote Servers and SQL Agent tasks?” SQL Server has several other objects (as listed previously) that exist in support of the database. With the exception of linked servers, and perhaps Integration Services packages, these are primarily the domain of the database administrator and as such, you generally don't give them significant thought during the design and programming processes. (They are programmable via something called the SQL Management Objects [SMO], but that is far too special a case to concern you with here. We will look at SMO more fully in Chapter 26.)

A database is typically a group that includes at least a set of table objects and, more often than not, other objects, such as stored procedures and views that pertain to the data stored in the database's tables.

When you first load SQL Server, you will start with four system databases:

• master
• model
• msdb
• tempdb

All of these need to be installed for your server to run properly. (Indeed, for some of them, it won't run at all without them.) From there, things vary depending on which installation choices you made. Examples of some of the databases you may also see include the following:

• AdventureWorks or AdventureWorks2008 (the sample databases downloadable from codeplex.com)
• AdventureWorksLT or AdventureWorksLT2008 (a “lite” version of the main sample database)
• AdventureWorksDW or AdventureWorksDW2008 (sample for use with Analysis Services)

In addition to the primary examples supported by Microsoft, you may, when searching the Web or using other tutorials, find reference to a couple of older samples:

• pubs
• Northwind

The master Database

Every SQL Server, regardless of version or custom modifications, has the master database. This database holds a special set of tables (system tables) that keeps track of the system as a whole. For example, when you create a new database on the server, an entry is placed in the sysdatabases table in the master database (though, if you're interested in data from sysdatabases, you should only access it via the sys.databases metadata view). All extended and system stored procedures, regardless of which database they are intended for use with, are stored in this database. Obviously, since almost everything that describes your server is stored in here, this database is critical to your system and cannot be deleted.

The system tables, including those found in the master database, can, in a pinch, be extremely useful. That said, their direct use is diminishing in importance as Microsoft continues to give more and more other options for getting at system level information.

I used to be a significant user of system tables; that is no longer the case.

Microsoft has recommended against using the system tables since prior to version 7.0 (1998 or so?). They make absolutely no guarantees about compatibility in the master database between versions—indeed, they virtually guarantee that they will change. The worst offense comes when performing updates on objects in the master database. Trust me when I tell you that altering these tables in any way is asking for a SQL Server that no longer functions. (I've saved a system doing this, and I've killed a system doing this; I don't like 50/50 odds with the life of my server….).

Microsoft has created several alternatives (for example, system functions, system stored procedures, information_schema views, and a wide array of system metadata functions) for retrieving much of the information that is stored in the system tables. These alternatives are what you should be using.

The model Database

The model database is aptly named, in the sense that it's the model on which a copy can be based. The model database forms a template for any new database that you create. This means that you can, if you wish, alter the model database if you want to change what standard, newly created databases look like. For example, you could add a set of audit tables that you include in every database you build. You could also include a few user groups that would be cloned into every new database that was created on the system. Note that since this database serves as the template for any other database, it's a required database and must be left on the system; you cannot delete it.

There are several things to keep in mind when altering the model database. First, any database you create has to be at least as large as the model database. That means that if you alter the model database to be 100MB in size, you can't create a database smaller than 100MB. There are several other similar pitfalls. As such, for 90 percent of installations, I strongly recommend leaving this one alone.

The msdb Database

msdb is where the SQL Agent process stores any system tasks. If you schedule backups to run on a database nightly, there is an entry in msdb. Schedule a stored procedure for one-time execution, and yes, it has an entry in msdb. Other major subsystems in SQL Server make similar use of msdb. SQL Server Integration Services (SSIS) packages and Policy Based Management definitions are examples of other processes that make use of msdb.

The tempdb Database

tempdb is one of the key working areas for your server. Whenever you issue a complex or large query that SQL Server needs to build interim tables to solve, it does so in tempdb. Whenever you create a temporary table of your own, it is created in tempdb, even though you think you're creating it in the current database. Whenever there is a need for data to be stored temporarily, it's probably stored in tempdb.

tempdb is very different from any other database in that not only are the objects within it temporary, but the database itself is temporary. It has the distinction of being the only database in your system that is completely rebuilt from scratch every time you start your SQL Server.

Technically speaking, you can actually create objects yourself in tempdb—I strongly recommend against this practice. You can create temporary objects from within any database you have access to in your system—it will be stored in tempdb. Creating objects directly in tempdb gains you nothing but adds the confusion of refe...

Table of contents

• Cover
• Title Page
• Copyright
• Dedication
• About the Authors
• Credits
• Acknowledgments
• Introduction
• Chapter 1: Being Objective: Re-Examining Objects in SQL Server
• Chapter 2: Tool Time
• Chapter 3: Asking a Better Question: Advanced Queries
• Chapter 4: XML Integration
• Chapter 5: Daring to Design
• Chapter 6: Core Storage and Index Structure
• Chapter 7: More Advanced Index Structures
• Chapter 8: Views
• Chapter 9: Scripts and Batches
• Chapter 10: Advanced Programmability
• Chapter 11: Transactions and Locks
• Chapter 12: Triggers
• Chapter 13: SQL Cursors
• Chapter 14: Reporting Services
• Chapter 15: Buying in Bulk: The Bulk Copy Program (BCP) and Other Basic Bulk Operations
• Chapter 16: Getting Integrated
• Chapter 17: Replication
• Chapter 18: Looking at Things in Full: Full-Text Search
• Chapter 19: Feeling Secure
• Chapter 20: A Grand Performance: Designing a Database That Performs Well
• Chapter 21: What Comes After: Forensic Performance Tuning
• Chapter 22: Administration
• Chapter 23: SMO: SQL Management Objects
• Chapter 24: Data Warehousing
• Chapter 25: Being Well Connected
• Appendix A: System Functions
• Appendix B: Going Meta: Getting Data About Your Data
• Appendix C: The Basis
• Index
• Advertisement