Microsoft SQL Server 2005 Programming For Dummies (For Dummies (Computer/Tech))

create And Modify Databases And Keep Them Secure Get Up To Speed On Using T-sql To Store And Manipulate Data Sql Server 2005 Improves An Already Great Database Management System. This Book Shows You How To Put It To Work In A Hurry. You'll Find Out How To Use The Sql Server Management Studio And The Sqlcmd Utility To Write T-sql Code, Retrieve Data From Single Or Multiple Sql Server Tables, Add Data Using The Insert Statement, And Much More. * Create Queries To Retrieve Data * Ensure Sql Server Security * Use Visual Studio(r) 2005 With Sql Server * Create Tables, Views, And Indexes * Work With Common Language Runtime * Query Xml Data Microsoft SQL Server 2005 Programming For Dummies 1 About the Author 6 Dedication 8 Author’s Acknowledgments 8 Contents at a Glance 10 Table of Contents 12 Introduction 20 About SQL Server 2005 Programming For Dummies 20 Foolish Assumptions 21 Conventions Used in This Book 21 2 21 What You Don’t Have to Read 22 How This Book Is Organized 22 3 22 4 23 Icons Used in This Book 24 Where to Go from Here 24 5 24 6 25 Part I: Get Started Using the SQL Server 2005 Development Environment 26 Chapter 1: The Joy of SQL Server 2005 Programming 28 Deciding Which Version of SQL Server 2005 to Use 29 Gathering and Manipulating Data 30 Enforcing Business Rules for Your Database 31 Ensuring SQL Server Security 31 When Transact-SQL Isn’t Enough 32 Chapter 2: Understanding Database Fundamentals 34 Getting to Know the Database Engine 34 Discovering Database Objects 36 Introducing SQL Server Data Types 39 Getting Familiar with SQL Server Naming Rules 40 Talking Transact-SQL 43 Chapter 3: Getting to Know the SQL Server Toolset 44 Exploring SQL Server Management Studio 45 Using the SQLCMD Utility 55 Getting to Know the SQL Server Configuration Manager 56 Using Other SQL Server Programming Tools 57 Accessing SQL Server Books Online (BOL) 58 Installing the SQL Server Sample Databases 61 Part II: Retrieving Data Using Transact-SQL 64 Chapter 4: Retrieving Data Using the SELECT Statement 66 Exploring Your Database’s Objects 67 Introducing the SELECT Statement 70 Using the FROM Clause 72 The WHERE Clause 74 The ORDER BY Clause 83 The GROUP BY Clause 84 Chapter 5: Creating Joins 88 Understanding the Need for Joins 89 Creating an Inner Join 92 Creating an Outer Join 102 Creating a Cross Join 107 Chapter 6: Creating Advanced Queries 110 Using Subqueries 110 Examining the EXISTS Keyword 113 Using the CAST and CONVERT Functions 115 Working with Common Table Expressions 119 Chapter 7: Manipulating Data 124 Copying a Database 124 Adding Data Using INSERT 130 Removing Data Using DELETE 135 Changing Data Using UPDATE 139 Transactional Control 141 Part III: Creating Databases and Database Objects with Transact-SQL 142 Chapter 8: Designing, Creating, and Altering Database Objects 144 Examining the Key Phases to Designing a Database 144 Normalizing Data 150 Designing Relationships 155 Creating Databases 155 Altering Databases 158 Dropping Databases 159 Chapter 9: Designing, Creating, and Changing Tables 160 Choosing a Naming Scheme for Tables and Columns 160 Choosing Data Types for Columns 162 The CREATE TABLE statement 168 Creating Relationships Using Transact-SQL 170 Creating Tables Using SQL Server Management Studio 173 The ALTER TABLE Statement 180 Chapter 10: Applying Constraints 182 Understanding the Types of Constraints 182 Creating NOT NULL Constraints 183 Creating DEFAULT Constraints 187 Creating UNIQUE Constraints 189 Creating CHECK Constraints 192 Removing Constraints 197 Using Advanced Constraints 198 Chapter 11: Creating Views 200 What Is a View? 200 Understanding the Need for Views 201 Creating a View 202 Using Views for Security 209 Updating through Views 210 Indexing a View 211 Chapter 12: Using Stored Procedures 212 Getting to Know Stored Procedures 212 Why Use Stored Procedures? 218 Creating a Stored Procedure 219 Using ALTER to Change a Stored Procedure 223 Using Parameters with Stored Procedures 225 Chapter 13: Using Triggers 228 Using Events That Execute Triggers 229 Understanding Where and When to Use Triggers 230 Using AFTER Triggers 232 Using INSTEAD OF Triggers 239 Using DDL Triggers 242 Debugging Triggers 243 Chapter 14: Creating Functions 246 What Is a Transact-SQL Function? 246 Creating User-Defined Functions 247 Altering and Dropping Functions 252 Using System Functions 253 Chapter 15: Creating Indexes 274 Getting a Handle on Why You Might Need Indexes 275 Considering the Types of Indexes 277 Creating an Index 279 Altering an Index 283 Dropping an Index 285 Chapter 16: Handling Errors Using TRY...CATCH 286 Error Handling in Transact-SQL 287 Using the @@ERROR Function 288 Using RAISERROR 290 Using TRY...CATCH 293 Nesting TRY...CATCH Statements 298 Part IV: Programming SQL Server Security 300 Chapter 17: Adding Logins and Users 302 Introducing SQL Server 2005 Security 303 Understanding Logins and Users 306 Adding Logins 307 Understanding Schemas and Users 309 Using Schemas 310 Adding Users 312 Chapter 18: Creating Database-Level Security 316 Assigning Permissions on Database Objects 316 Using Roles 321 Using Application Roles 322 Using Security Functions 323 Chapter 19: Securing Data Using Encryption 326 Introducing Encryption 326 Working with Asymmetric and Symmetric Encryption 333 Using Certificates 335 Encrypting Data 336 Part V: Beyond Transact-SQL Programming 338 Chapter 20: Working with XML 340 XML in SQL Server 2005 341 Using Typed and Untyped XML 342 Querying XML Data 349 DML on XML data 351 Indexing XML 354 Exposing Relational Data as XML 355 Chapter 21: Working with the Common Language Runtime 360 Introducing the CLR 360 Understanding CLR Permissions 363 Configuring SQL Server for the CLR 364 Creating an Assembly 367 Chapter 22: Using Visual Studio 2005 370 Using Server Explorer 371 Using the Visual Designers 374 Using Solution Explorer 381 Creating a Project 382 Chapter 23: Working with SQL Server Management Objects 392 Getting Started with SQL-SMO 392 Discovering the SQL-SMO Object Model 393 Creating a SQL-SMO Program 400 Part VI: The Part of Tens 408 Chapter 24: Ten Tips to Program Like a Pro 410 Listen to Your Clients’ Needs 410 Document the Project 410 Budget Enough Time for the Project 411 Think about Long-Term Needs 411 Think Carefully about Relations in the Database 411 Handle Many-to-Many Relationships Appropriately 412 Think about Performance 412 Design and Test on Development Hardware 412 Test Your Application Carefully 413 Think about Which Edition of SQL Server to Use 413 Think about the Hardware You Need 413 Chapter 25: Ten Sources for More Information on SQL Server 2005 414 Books Online 414 The Public Newsgroups 415 The Public Fora 415 The SQL Server 2005 Web Site 416 The SQL Server Developer Center 416 The SQL Server 2005 TechCenter 416 The Business Intelligence Site 416 The Integration Services Developer Center 416 The Reporting Services Web Site 417 Channel 9 417 Other Web Sites 417 Index 418 Get up to speed on using T-SQL to store and manipulate data. Discover how to create queries to retrieve data; ensure SQL server security; use Visual Studio 2005 with SQL server; create tables, views, and indexes; work with common language runtime; query XML data