Professional Microsoft SQL Server 2008 Programming E-Book

Robert Vieira

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Veröffentlichungsjahr: 2010

Beschreibung

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.

Leseprobe

Table of Contents

Title Page

Copyright

Dedication

About the Authors

Credits

Acknowledgments

Introduction

Version Issues

Who This Book Is For

What This Book Covers

How This Book Is Structured

Conventions

Source Code

Errata

p2p.wrox.com

www.professionalsql.com

Chapter 1: Being Objective: Re-Examining Objects in SQL Server

So, What Exactly Do We Have Here?

An Overview of Database Objects

SQL Server Data Types

SQL Server Identifiers for Objects

Summary

Chapter 2: Tool Time

Books Online

The SQL Server Configuration Manager

The SQL Server Management Studio

SQL Server Business Intelligence Development Studio

SQL Server Integration Services (SSIS)

Reporting Services

Bulk Copy Program (bcp)

SQL Server Profiler

sqlcmd

Summary

Chapter 3: Asking a Better Question: Advanced Queries

A Quick Review of Subqueries

Building a Nested Subquery

Correlated Subqueries

Derived Tables

The EXISTS Operator

The INTERSECT and EXCEPT Operators

Common Table Expressions (CTEs)

Recursive Queries

MERGE

Using External Calls to Perform Complex Actions

Performance Considerations

Summary

Chapter 4: XML Integration

The XML Data Type

Retrieving Relational Data in XML Format

A Quick Heads Up Regarding XML Indexes

A Brief Word on Hierarchical Data

Summary

Chapter 5: Daring to Design

Normalization 201

Relationships

Diagramming

Logical versus Physical Design

Dealing with File-Based Information Via Classic BLOBs

Subcategories

Database Reuse

De-Normalization

Partitioning for Scalability

The SQL Server Diagramming Tools

Regarding Date Columns

Summary

Chapter 6: Core Storage and Index Structure

SQL Server Storage

Understanding Indexes

Creating, Altering, and Dropping Indexes

Choosing Wisely: Deciding What Index Goes Where and When

Maintaining Your Indexes

Summary

Chapter 7: More Advanced Index Structures

XML Indexes

User-Defined Data Types

Hierarchical Data

Spatial Data

Filestreams

Enabling Filestreaming

Table Compression

Summary

Chapter 8: Views

Reviewing View Syntax

More Complex Views

Editing Views with T-SQL

Dropping Views

Auditing: Displaying Existing Code

Protecting Code: Encrypting Views

About Schema Binding

Making Your View Look Like a Table with VIEW_METADATA

Indexed (Materialized) Views

Partitioned Views

Summary

Chapter 9: Scripts and Batches

Script Basics

Batches

SQLCMD

Dynamic SQL: Generating Your Code on the Fly with the EXEC Command

Control-of-Flow Statements

Summary

Chapter 10: Advanced Programmability

A More Advanced Look At Stored Procedures

Table-Valued Parameters (TVPs)

Debugging

Understanding the SQLCLR and .NET Programming in SQL Server

Creating Aggregate Functions

Custom Data Types

Summary

Chapter 11: Transactions and Locks

Transactions

How the SQL Server Log Works

Locks and Concurrency

Setting the Isolation Level

Dealing with Deadlocks (a.k.a. “A 1205”)

Summary

Chapter 12: Triggers

What Is a Trigger?

Using Triggers for Data Integrity Rules

Other Common Uses for Triggers

Other Trigger Issues

INSTEAD OF Triggers

IF UPDATE() and COLUMNS_UPDATED()

Performance Considerations

Dropping Triggers

Summary

Chapter 13: SQL Cursors

What Is a Cursor?

The Life Span of a Cursor

Types of Cursors and Extended Declaration Syntax

Navigating the Cursor: The FETCH Statement

Altering Data within Your Cursor

Summary

Chapter 14: Reporting Services

A Quick Look at Reports as a Concept

Reporting Services 101

Report Server Projects

A Brief Note on RDL

Summary

Chapter 15: Buying in Bulk: The Bulk Copy Program (BCP) and Other Basic Bulk Operations

bcp Utility

Format Files

BULK INSERT

OPENROWSET (BULK)

Summary

Chapter 16: Getting Integrated

Understanding the Problem

An Overview of Packages

Building a Simple Package

Executing Packages

Summary

Chapter 17: Replication

Replication Basics

Replication Models

Replication Topology

Planning for Replication

Setting Up Replication in Management Studio

Replication Management Objects (RMO)

Summary

Chapter 18: Looking at Things in Full: Full-Text Search

Full-Text Search Architecture

Setting Up Full-Text Indexes and Catalogs

More on Index Population

Full-Text Query Syntax

Stop Words

Summary

Chapter 19: Feeling Secure

Security Basics

Security Options

User Permissions

Server and Database Roles

Application Roles

More Advanced Security

Certificates and Asymmetric Keys

Summary

Chapter 20: A Grand Performance: Designing a Database That Performs Well

When to Tune

Index Choices

Client vs. Server-Side Processing

Strategic De-Normalization

Organizing Your Sprocs Well

Uses for Temporary Tables

Update Your Code In a Timely Fashion

Sometimes, It's the Little Things

Hardware Considerations

Summary

Chapter 21: What Comes After: Forensic Performance Tuning

When to Tune (Mark Two)

Routine Maintenance

Troubleshooting

Summary

Chapter 22: Administration

Scheduling Jobs

Backup and Recovery

Index Maintenance

Archiving of Data

PowerShell

Policy-Based Management

Summary

Chapter 23: SMO: SQL Management Objects

The History of SQL Server Management Object Models

The SMO Object Model

Walking through Some Examples

Dropping a Database

Backing Up a Database

Scripting

Pulling It All Together

Summary

Chapter 24: Data Warehousing

Considering Differing Requirements

Dimensional Databases

Data Warehouse Concepts

SQL Server Integration Services

Creating an Analysis Services Solution

Accessing a Cube

Summary

Chapter 25: Being Well Connected

Appendix A: System Functions

Legacy System Functions (a.k.a. Global Variables)

Aggregate Functions

Configuration Functions

Cryptographic Functions

Cursor Functions

Date and Time Functions

Mathematical Functions

Basic Metadata Functions

Rowset Functions

Security Functions

String Functions

System Functions

Text and Image Functions

Appendix B: Going Meta: Getting Data About Your Data

System Views

Dynamic Management Views

Appendix C: The Basis

Everything to Do with Queries

The INSERT Statement

The UPDATE Statement

The DELETE Statement

Exploring Alternative Syntax for Joins

UNION

Summary

Index

Advertisement

Professional Microsoft® SQL Server® 2008 Programming

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-0-470-25702-9

Vieira, Robert.

Professional Microsoft SQL server 2008 programming / Rob Vieira.

p. cm.

Includes index.

ISBN 978-0-470-25702-9 (paper/website)

1. SQL server. 2. Client/server computing. 3. Database management. I. Title.

QA76.9.C55V535 2009

005.75′85–dc22

2009010578

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

This book is dedicated with all my heart to Ashley, Addy, and Anna. It's hard to believe that Ashley was still in elementary school when I wrote my first book (she'll graduate from college later this year) and didn't have to share this dedication with other kids (heck, she isn't much of a kid anymore—eeek!). Then there's Addy, who literally dreads me writing, yet continues to put up with me “disappearing” into my home office during the lengthy period of time I work on a book. Finally, there's Anna, who came in the middle of this round of writing, and didn't have any real history on what to expect from my writing disappearances.

A dedication of this book would most certainly not be complete without a particularly special thank you to Deb, who has leveraged my efforts in this and my recent Beginning title to redefine for me what the terms “love” and “support” mean.

As I've said for many releases now, I wish Wrox would let me print a picture of the women in my life on the cover of this book rather than my ugly mug—I'm certain that you, the reader, would appreciate it too!

About the Authors

Experiencing his first infection with computing fever in 1978, Rob Vieira knew right away that this was something “really cool.” In 1980 he began immersing himself into the computing world more fully—splitting time between building and repairing computer kits, and programming in BASIC as well as Z80 and 6502 assembly. In 1983, he began studies for a degree in Computer Information Systems, but found the professional mainframe environment too rigid for his tastes and dropped out in 1985 to pursue other interests. Later that year, he caught the “PC bug” and began the long road of programming in database languages from dBase to SQL Server. Rob completed a degree in business administration in 1990 and since has typically worked in roles that allow him to combine his unique knowledge of business and computing. Beyond his bachelor's degree, he has been certified as a Certified Management Accountant (CMA); Microsoft Certified as a Solutions Developer (MCSD), Trainer (MCT), and Database Administrator (MCDBA); and even had a brief stint certified as an Emergency Medical Technician (EMT).

Rob is currently the Database Team Lead for Stockamp, a Huron Consulting Group practice. He has published six books on SQL Server development

He resides in Vancouver, Washington, where he does his best to take care of “his girls,” and even takes his best shot at letting Deb take care of him some too.

Credits

Executive Editor

Bob Elliott

Development Editor

Sydney Jones

Adaobi Obi Tulton

Technical Editor

John Mueller

Production Editor

Daniel Scribner

Copy Editor

Kim Cofer

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Barry Pruett

Associate Publisher

Jim Minatel

Project Coordinator, Cover

Lynsey Stanford

Proofreader

Publication Services, Inc.

Indexer

Robert Swanson

Acknowledgments

Nearly a decade has gone by since I finished my first book on SQL Server, and the list of those deserving some credit continues to grow.

As always, I'll start with my kids. They are the ones who most sacrifice time when a new release comes along and I spend so much time writing.

Deb—This was the first time she had to deal with one of my writing cycles, and she couldn't have done anything more to make me feel more supported. Deb has helped me redefine the meaning of the word support. She is my rock.

You—the readers. You continue to write me mail and tell me how I helped you out in some way. That continues to be the number one reason I find the strength to write another book. The continued support of my Professional series titles has been amazing. We struck a chord—I'm glad. Here's to hoping we help make your SQL Server experience a little less frustrating and a lot more successful.

As always, I want to pay special thanks to several people past and present. Some of these are at the old Wrox Press, but they remain so much of who I am as a writer that I need to continue to remember them. Others are new players for me, but have added their own stamp to the mix—sometimes just by showing a little patience:

Kate Hall—Who, although she was probably ready to kill me by the end of each of my first two books, somehow guided me through the edit process to build a better book each time. I have long since fallen out of touch with Kate, but she will always be the most special to me as someone who really helped shape my writing career. I will likely always hold this first “professional” dedication spot for her. Wherever you are Kate, I hope you are doing splendidly.

Sydney Jones—Normally, when you add someone new to a list like this, they seem to wind up at the bottom—that didn't seem quite right for Syd. Syd first appeared to me as one of a cast of seemingly thousands (it was less than that, but it seemed like a constantly changing set of people), but has evolved into one of the constants in my last few titles. Syd has done a great job of finding some balance between upholding the notion of “Wiley standards” versus allowing my books to be my books. It's beyond time that she got her due—here is at least a little bit of it.

Adaobi Obi Tulton—Four books now—who'd a thunk it? Adaobi is something of the “figure out where everything is” person of the book. She has a way of displaying a kind of “peace” in most things I've seen her do—I need to learn that.

Dominic Shakeshaft—Who got me writing in the first place (then again, given some nights filled with writing instead of sleep lately, maybe it's not thanks I owe him). I've had the pleasure of re-establishing contact with Dominic recently, and it's great to see him doing well.

Catherine Alexander—Who played Kate's more than able-bodied sidekick for my first title and was central to round two. Catherine was much like Kate in the sense she had a significant influence on the shape and success of my first two titles. I've also recently managed to be in touch with “Cath” again, and am glad she also seems to be doing well.

John Mueller—Who continues to have the dubious job of finding my mistakes. I've done tech editing myself, and it's not the easiest job to notice the little details that were missed or are, in some fashion, wrong. It's even harder to read someone else's writing style and pick the right times to say, “You might want to approach this differently” and the right times to let it be. John did a terrific job on both counts.

John faced an especially trying time during the latter stages of this book's development, and did an amazing job of holding it all together. I wish he and his wife Rebecca great fortunes and good health in the year ahead.

Richard Waymire—Who has been such a pillar of the SQL Server community for oh so long. Richard is one of the true “nice guys” who isn't just nice—he just plain “gets it” when it comes to SQL Server. Richard has provided the answer to many a question over the years.

This book has been in development for so long and touched enough people that I'm sure I've missed one or two—if you're among those missed, please accept my humblest apologies and my assurance that your help was appreciated.

Introduction

And so we begin anew. While the wait for SQL Server 2005 took a ghastly five years, Microsoft has blessed us (and, no, I'm not being sarcastic!) with SQL Server 2008 in just three short years.

It probably shouldn't be surprising to me that SQL Server 2008 comes up short in terms of marquee features—the flash and glitz stuff. Yet, what I am surprised by is just how many “little things” have made it into this release. While there are some key feature areas (Policy Based Management, for example) that Microsoft can hang their marketing hat on, the most striking new features in SQL Server 2008 are far more subtle in nature.

SQL Server 2008 is jam-packed with a host of seemingly small, but extremely useful smaller additions such as:

New, very useful data types (discrete date and time data types as well as support for geospatial data and hierarchical data representation)The new MERGE command (combining the options of INSERT, UPDATE, and DELETE all into one statement, this is sometimes referred to as an UPSERT in other products)A revamping of Reporting Services to allow for far more elegant reportsTracking and availability of “missing” index information (noticing at time of optimization that a non-existent index would have been useful had it been there)

And these are just for starters.

For those of you that have read the 2005 versions of my books, the change toward the separation of beginning and professional level content continues. The real “beginning” level discussion is now almost completely moved into its own book (Beginning SQL Server 2008 Programming). There are some topics that I consider borderline in terms of whether they are a beginner, intermediate, or professional level topic that I have continued to provide some degree of coverage on, but, with this edition, most all genuinely beginner content exists solely in the Beginning title (a new appendix has been added at the end of the book to give extremely short syntax listings and a few examples, but what was hundreds of pages is now in the tens of pages).

There is, however, good news that has continued to free up even more space, and this has allowed the Professional title to return to more broad coverage of a wider range of topics. This allows me to get closer to the original goal I had for the Professional book: to give you enough grounding in most of the product that, even if you couldn't write at an expert level in each area, you would understand SQL Server as a whole and build a better system, knowing what's involved in the many areas where SQL Server offers functionality, and being prepared to go get even more information if need be.

Other than that, this book maintains most of the style it has always had. We cover most of the add-on services, as well as advanced programming constructs (such as .NET assemblies) and some of the supporting object models (which allow for management of your SQL Server and its various engines).

Version Issues

This book is written for SQL Server 2008. It does, however, maintain roots going back a few versions and keeps a sharp eye out for backward compatibility issues with SQL Server 2005 and even SQL Server 2000. Prior versions are old enough now where little to no time is spent on them except in passing (in short, few remain that have even seen SQL Server 6.5 and SQL Server 7.0 was just shy of a decade old at release of SQL Server 2008).

Who This Book Is For

This book assumes that you have some existing experience with SQL Server and are at an intermediate to advanced level. Furthermore, the orientation of the book is highly developer focused.

Aside from a quick reference-oriented appendix, very little coverage is given to beginner level topics. It is assumed you already have experience with data manipulation language (DML) statements, and know the basics of all the mainstream SQL Server objects (views, stored procedures, user defined functions and, to a much lesser extent, triggers). If you feel you are ready for more advanced topics, but also feel that brushing up on the basics can't hurt, I highly encourage you to check out Beginning SQL Server 2008 Programming, as the two books are now much more designed to work as a pair with some, but relatively limited, overlap.

What This Book Covers

This book is about SQL Server. More specifically, it is oriented around developing on SQL Server. Most of the concepts are agnostic to what client language you use, though the examples that leverage a client language generally do so in C#. (A few are shown in more than one language.)

For those of you migrating from earlier versions of SQL Server, some of the “gotchas” that inevitably exist any time a product has versions are discussed to the extent that they seem to be a genuinely relevant issue.

How This Book Is Structured

As is the case for all my books, this book takes something of a lassez faire writing style. We roam around a bit within a relatively loose structure. Each chapter begins with an explanation of the things to be covered in that chapter, and then we go through those items. Within each topic covered, some background is provided, and then we work through an example if appropriate. Examples are generally created to be short, and yet still quickly get at several of the concepts you need for whatever topic is being covered at the time.

In terms of “what to cover next,” there is a logical progression. We review tools and data types early on (since there are changes there), but move on very fast to topics that assume you are already comfortable with the product.

To make reasonable use of this book, you will need administrative access to a computer that is capable of running SQL Server 2008. While I highly recommend using the Developer Edition, the vast majority of samples and advice applies to virtually all editions of SQL Server. I do, however, recommend staying with a full version of SQL Server rather than using the Express Edition.

Conventions

To help you get the most from the text and keep track of what's happening, we've used a number of conventions throughout the book.

Boxes like this one hold important, not-to-be forgotten information that is directly relevant to the surrounding text.

Notes, tips, hints, tricks, and asides to the current discussion are offset and placed in italics like this.

As for styles in the text:

We highlight new terms and important words when we introduce them.We show keyboard strokes like this: Ctrl+A.We show file names, URLs, and code within the text like so: persistence.properties.We present code in two different ways:

We use a monofont type with no highlighting for most code examples.

We use gray highlighting to emphasize code that's particularly important in the present context.

Source Code

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All of the source code used in this book is available for download at http://www.wrox.com. Once at the site, simply locate the book's title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book's detail page to obtain all the source code for the book.

Because many books have similar titles, you may find it easiest to search by ISBN; this book's ISBN is 978-0-470-25702-9.

Once you download the code, just decompress it with your favorite compression tool. Alternatively, you can go to the main Wrox code download page at http://www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.

Errata

We make every effort to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, like a spelling mistake or faulty piece of code, we would be very grateful for your feedback. By sending in errata you may save another reader hours of frustration and at the same time you will be helping us provide even higher quality information.

To find the errata page for this book, go to http://www.wrox.com and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list including links to each book's errata is also available at www.wrox.com/misc-pages/booklist.shtml.

If you don't spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We'll check the information and, if appropriate, post a message to the book's errata page and fix the problem in subsequent editions of the book.

p2p.wrox.com

For author and peer discussion, join the P2P forums at p2p.wrox.com. The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At http://p2p.wrox.com you will find a number of different forums that will help you not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:

1. Go to p2p.wrox.com and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join as well as any optional information you wish to provide and click Submit.

4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

You can read messages in the forums without joining P2P but in order to post your own messages, you must join.

Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.

www.professionalsql.com

Limited support for the book and occasional blog entries can also be found at http://www.professionalsql.com. While formal support requests should be sent through the p2p.wrox.com website, professionalsql.com provides a mirror of the key downloads as well as occasional commentary from the author on the general state of the development world. You can contact me at [email protected]; my sole requests for questions or contacts are:

Please don't send me the questions from your take home mid-term or other school quizzes/tests (and yes, people really have done that).Focus questions to those not readily answered from general sources (Google, the p2p.wrox.com website, the many popular SQL Server websites, or a simple Books Online query).Understand that, while I try, I cannot always respond to every request for help, advice, or other questions.Recognize that the exposure of my e-mail address in this book represents a certain degree of trust in you, the reader, that you will not abuse that openness.

I am always happy to hear about people's SQL experiences, so please feel free to drop me a line and brag about the wonderful things you've managed to do with SQL Server.

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 objectsData 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: