57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page v Beginning Microsoft SQL Ser ver® 2008 Programming Robert Vieira 57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page vi Beginning Microsoft SQL Ser ver® 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-13: 978-0-470-25701-2 Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 Library of Congress Cataloging-in-Publication Data: Vieira, Robert. Beginning SQL server 2008 programming / Robert Vieira. p. cm. Includes index. ISBN 978-0-470-25701-2 (paper/website) 1. SQL server. 2. Database management. 3. Relational databases. I. Title. QA76.9.D3V5254 2008 005.75'85--dc22 2008033212 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 Cen- ter, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indi- anapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions. Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or war- ranties 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 Website 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 Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites 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 (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. 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. SQL Server is a registered trademark 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. 57012ftoc.qxd:WroxBeg 11/22/08 10:18 AM Page xv Contents Introduction xxxi Chapter 1: RDBMS Basics: What Mak es Up a SQL Ser ver Database? 1 An Overview of Database Objects 1 The Database Object 2 The master Database 3 The model Database 4 The msdb Database 4 The tempdb Database 5 ReportServer 5 ReportServerTempDB 5 AdventureWorks2008 5 AdventureWorksLT2008 6 AdventureWorksDW2008 6 The pubs Database 6 The Northwind Database 6 The Transaction Log 6 The Most Basic Database Object: Table 7 Indexes 7 Triggers 8 Constraints 8 Filegroups 8 Diagrams 8 Views 9 Stored Procedures 10 User-Defined Functions 10 Users and Roles 11 Rules 11 Defaults 11 User-Defined Data Types 11 Full-Text Catalogs 12 SQL Server Data Types 12 NULL Data 18 SQL Server Identifiers for Objects 18 What Gets Named? 18 Rules for Naming 19 Summary 19 57012ftoc.qxd:WroxBeg 11/22/08 10:18 AM Page xvi Contents Chapter 2:Tools of the Trade 21 Books Online 21 SQL Server Configuration Manager 23 Service Management 23 Network Configuration 24 The Protocols 25 Named Pipes 26 TCP/IP 26 Shared Memory 27 On to the Client 27 SQL Server Management Studio 28 Getting Started with the Management Studio 29 Server Type 29 Server Name 29 Authentication 31 Query Window 33 Getting Started 33 Results to Text 35 Results to Grid 36 Results to File 36 sqlcmd Mode 36 Show Execution Plan 37 The Available Databases Combo Box 38 The Object Explorer 38 SQL Server Integration Services (SSIS) 39 Bulk Copy Program (bcp) 39 SQL Server Profiler 40 sqlcmd 40 PowerShell 41 Summary 41 Chapter 3: The F oundation Statements of T -SQL 43 Getting Started with a Basic SELECT Statement 44 The SELECT Statement and FROM Clause 44 The WHERE Clause 48 ORDER BY 51 Aggregating Data Using the GROUP BY Clause 54 Aggregates 56 Placing Conditions on Groups with the HAVING Clause 62 Outputting XML Using the FOR XML Clause 64 xvi 57012ftoc.qxd:WroxBeg 11/22/08 10:18 AM Page xvii Contents Making Use of Hints Using the OPTION Clause 64 The DISTINCT and ALL Predicates 64 Adding Data with the INSERT Statement 67 Multirow Inserts 72 The INSERT INTO . . . SELECT Statement 73 Changing What You’ve Got with the UPD ATE Statement 75 The DELETE Statement 77 Summary 78 Exercises 79JOINs 81 JOINs 81 INNER JOINs 83 How It Works 88 How an INNER JOIN Is Like a WHERE Clause 88 How It Works 90 OUTER JOINs 91 The Simple OUTER JOIN 91 How It Works 94 How It Works 94 Finding Orphan or Non-Matching Records 94 Dealing with More Complex OUTER JOINs 97 Seeing Both Sides with FULL JOINs 101 How It Works 103 CROSS JOINs 103 Exploring Alternative Syntax for Joins 104 An Alternative INNER JOIN 105 An Alternative OUTER JOIN 105 An Alternative CROSS JOIN 106 The UNION 107 How It Works 109 Summary 111 Exercises 112 Chapter 5: Creating and Altering T ables 113 Object Names in SQL Ser ver 113 Schema Name (aka Ownership) 114 A Little More About Schemas 114 The Default Schema: dbo 115 The Database Name 116 Naming by Server 116 Reviewing the Defaults 117 xvii 57012ftoc.qxd:WroxBeg 11/22/08 10:18 AM Page xviii Contents The CREATE Statement 117 CREATE DATABASE 117 ON 118 NAME 119 FILENAME 119 SIZE 119 MAXSIZE 119 FILEGROWTH 120 LOG ON 120 COLLATE 120 FOR ATTACH 120 WITH DB CHAINING ON|OFF 121 TRUSTWORTHY 121 Building a Database 121 CREATE TABLE 123 Table and Column Names 124 Data Types 125 DEFAULT 126 IDENTITY 126 NOT FOR REPLICATION 126 ROWGUIDCOL 127 COLLATE 128 NULL/NOT NULL 128 Column Constraints 128 Computed Columns 128 Table Constraints 130 ON 130 TEXTIMAGE_ON 130 Creating a Table 130 The ALTER Statement 136 ALTER DATABASE 136 Option and Termination Specs 139 ALTER TABLE 140 The DROP Statement 143 Using the GUI Tool 144 Creating a Database Using the Management Studio 145 Backing into the Code: The Basics of Creating Scripts with the Management Studio 150 Summary 151 Exercises 152 xviii 57012ftoc.qxd:WroxBeg 11/22/08 10:18 AM Page xix Contents Chapter 6: Constraints 153 Types of Constraints 154 Domain Constraints 154 Entity Constraints 154 Referential Integrity Constraints 155 Constraint Naming 156 Key Constraints 157 PRIMARY KEY Constraints 157 Creating the Primary Key at Table Creation 158 Creating a Primary Key on an Existing Table 159 FOREIGN KEY Constraints 160 Adding a Foreign Key to an Existing Table 161 Making a Table Self-Referencing 162 Cascading Actions 164 Other Things to Think About with Foreign Keys 169 UNIQUE Constraints 170 Creating UNIQUE Constraints on Existing Tables 171 CHECK Constraints 172 DEFAULT Constraints 173 Defining a DEFAULT Constraint in Your CREATE TABLE Statement 174 Adding a DEFAULT Constraint to an Existing Table 175 Disabling Constraints 175 Ignoring Bad Data When You Create the Constraint 176 Temporarily Disabling an Existing Constraint 178 Rules and Defaults — Cousins of Constraints 180 Rules 180 Dropping Rules 182 Defaults 182 Dropping Defaults 182 Determining Which Tables and Data Types Use a Given Rule or Default 183 Triggers for Data Integrity 183 Choosing What to Use 183 Summary 185 Chapter 7: Adding More to Our Queries 187 What Is a Subquer y? 188 Building a Nested Subquery 189 Nested Queries Using Single-Value SELECT Statements 189 Nested Queries Using Subqueries That Return Multiple Values 190 Using a Nested SELECT to Find Orphaned Records 191 xix 57012ftoc.qxd:WroxBeg 11/22/08 10:18 AM Page xx Contents Correlated Subqueries 192 How Correlated Subqueries Work 192 Correlated Subqueries in the WHERE Clause 192 Correlated Subqueries in the SELECT List 195 Dealing with NULL Data — the ISNULL Function 196 Derived Tables 197 The EXISTS Operator 200 Using EXISTS in Other Ways 202 Mixing Data Types: CAST and CONVERT 203 The MERGE Command 206 A Brief Look at BY TARGET versus BY SOURCE 209 The Output Clause 210 Performance Considerations 212 JOINs versus Subqueries versus ? 212 Summary 214 Exercises 214 Chapter 8: Being Nor mal: Normalization and Other Basic Design Issues 215 Tables 216 Keeping Your Data “Normal” 216 Before the Beginning 217 The First Normal Form 260 The Second Normal Form 260 The Third Normal Form 260 Other Normal Forms 260 Relationships 260 One-to-One 260 Zero or One-to-One 260 One-to-One or Many 260 One-to-Zero, One, or Many 260 Many-to-Many 260 Diagramming 260 Tables 260 Adding and Deleting Tables 260 Editing Table Properties and Objects That Belong to the Table 260 Properties Window 260 Relationships 260 Indexes/Keys 260 Check Constraints 260 Relationships 260 Adding Relationships in the Diagramming Tool 260 xx 57012ftoc.qxd:WroxBeg 11/22/08 10:18 AM Page xxi Contents De-Normalization 260 Beyond Normalization 260 Keep It Simple 260 Choosing Data Types 260 Err on the Side of Storing Things 260 Drawing Up a Quick Example 260 Creating the Database 260 Adding the Diagram and Our Initial Tables 260 Adding the Relationships 260 Adding Some Constraints 260 Summary 260 Exercises 260 Chapter 9: SQL Ser ver Storage and Index Str uctures 261 SQL Server Storage 261 The Database 261 The Extent 262 The Page 262 Page Splits 263 Rows 263 Sparse Columns 263 Understanding Indexes 264 B-Trees 265 Page Splits — A First Look 267 How Data Is Accessed in SQL Server 268 Use of Table Scans 268 Use of Indexes 269 Index Types and Index Navigation 269 Clustered Indexes 270 Non-Clustered Indexes on a Heap 272 Non-Clustered Indexes on a Clustered Table 273 Creating, Altering, and Dropping Indexes 276 The CREATE INDEX Statement 277 ASC/DESC 277 INCLUDE 278 WITH 278 PAD_INDEX 278 FILLFACTOR 279 IGNORE_DUP_KEY 279 DROP_EXISTING 279 STATISTICS_NORECOMPUTE 280 xxi 57012ftoc.qxd:WroxBeg 11/22/08 10:18 AM Page xxii Contents SORT_IN_TEMPDB 280 ONLINE 281 ALLOW ROW/PAGE LOCKS 281 MAXDOP 281 ON 282 Creating XML Indexes 282 The Primary XML Index 282 Secondary XML Indexes 283 Implied Indexes Created with Constraints 283 Creating Indexes on Sparse and Geospatial Columns 283 Choosing Wisely: Deciding What Index Goes Where and When 283 Selectivity 284 Watching Costs: When Less Is More 284 Choosing That Clustered Index 285 The Pros 286 The Cons 286 Column Order Matters 287 ALTER INDEX 288 Index Name 289 Table or View Name 289 REBUILD 289 DISABLE 289 REORGANIZE 290 Dropping Indexes 290 Take a Hint from the Query Plan 290 Use the Database Engine Tuning Advisor 290 Maintaining Your Indexes 291 Fragmentation 291 Identifying Fragmentation vs. Likelihood of Page Splits 292 ALTER INDEX and FILLFACTOR 294 Summary 296 Exercises 297 Chapter 10: V iews 299 Simple Views 299 Views as Filters 324 How It Works 324 More Complex V iews 324 The DATEADD and CAST Functions 324 Using a View to Change Data — Before INSTEAD OF Triggers 324 Dealing with Changes in Views with Joined Data 324 xxii