Beginning Microsoft SQL Server 2012 Programming

by Paul Atkinson, Robert Vieira

Overview

Get up to speed on the extensive changes to the newest release of Microsoft SQL Server

The 2012 release of Microsoft SQL Server changes how you develop applications for SQL Server. With this comprehensive resource, SQL Server authority Robert Vieira presents the fundamentals of database design and SQL concepts, and then shows you how to apply these concepts using the updated SQL Server. Publishing time and date with the 2012 release, Beginning Microsoft SQL Server 2012 Programming begins with a quick overview of database design basics and the SQL query language and then quickly proceeds to show you how to implement the fundamental concepts of Microsoft SQL Server 2012.

You'll explore the key additions and changes to this newest version, including conditional action constructs, enhanced controls for results paging, application integration with SharePoint and Excel, and development of BI applications.

• Covers new features such as SQL Azure for cloud computing, client-connectivity enhancements, security and compliance, data replication, and data warehouse performance improvements
• Addresses essential topics including managing keys, writing scripts, and working with store procedures
• Shares helpful techniques for creating and changing tables, programming with XML, and using SQL Server Reporting and Integration Services

Beginning Microsoft SQL Server 2012 Programming demystifies even the most difficult challenges you may face with the new version of Microsoft SQL Server.

Product Details

ISBN-13:	9781118102282
Publisher:	Wiley
Publication date:	04/16/2012
Series:	Programmer to Programmer
Pages:	864
Product dimensions:	7.40(w) x 9.20(h) x 1.60(d)

About the Author

Paul Atkinson works for Huron Healthcare as a BI Architect and Team Lead developing both traditional and real-time BI solutions. His training classes in high-performance TSQL programming are among the most popular course offerings available at Huron.

Robert Vieira is a Software Architect with Huron Consulting Group and is considered one of the leading authorities on Microsoft SQL Server. He speaks at conferences nationally and is well known for his unique tutorial approach in his teaching and writing.

Wrox Beginning guides are crafted to make learning programming languages and technologies easier than you think, providing a structured, tutorial format that guides you through all the techniques involved.

Read an Excerpt

Beginning Microsoft SQL Server 2012 Programming

---

By Paul Atkinson Robert Vieira

John Wiley & Sons

Copyright © 2012 John Wiley & Sons, Ltd
All right reserved.
ISBN: 978-1-1181-0228-2

---

Chapter One

RDBMS Basics: What Makes Up a SQL Server Database?

WHAT YOU WILL LEARN IN THIS CHAPTER:

* Understand what the objects are that make up a SQL Server database

* Learn the data types available for use in SQL Server 2012

* Discover how to name objects

What makes up a database? Data for sure. (What use is a database that doesn't store anything?) But 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 the kind of data that can go into the system, and facilitating getting data out of the system. If all you want is to tuck the data away somewhere safe, you could 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, or the business rules of the data.

Don't confuse what I'm calling the "business rules of the data" with the more generalized business rules that drive your entire system (for example, preventing someone from seeing anything until they've logged in, or automatically adjusting the current period in an accounting system on the first of the month). Those types of rules can be enforced at virtually any level of the system (these days, it's usually in the middle or client tier of an n-tier system). Instead, what I'm talking about here are the business rules that specifically relate to the data. For example, you can't have a sales order with a negative amount. With an RDBMS, you can incorporate these rules right into the integrity of the database itself.

The notion of the database taking responsibility for the data within, as well as the best methods to input and extract data from that database, serves as the foundation for this book. This chapter provides an overview of the rest of the book. Most items discussed in this chapter are covered again in later chapters, but this chapter is intended to provide you with a road map or plan to bear in mind as you progress through the book. With this in mind, I'll give you a high-level look into:

* Database objects

* Data types

* Other database concepts that ensure data integrity

AN OVERVIEW OF DATABASE OBJECTS

An instance of an RDBMS such as SQL Server contains many objects. Object purists out there may quibble with whether Microsoft's choice of what to (and what not to) call an object 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

* The transaction log

* Tables

* Indexes

* Filegroups

* Diagrams

* Views

* Stored procedures

* User-defined functions

* Sequences

* Users

* Roles

* Assemblies

* Reports

* Full-text catalogs

* User-defined data types

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 I'm not going there.) Most, but not all, other objects in a SQL Server are children of the database object.

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

What types of tables do you store in just one database, and what goes in a separate database? I'll discuss that in some detail later in the book, but for now I'll take the simple approach of saying that any data that is generally thought of as belonging to just one system, or is significantly related, will be stored in a single database. An RDBMS, such as SQL Server, may have multiple databases on just one server, or it may have only one. The number of databases that reside on an individual SQL Server depends on such factors as capacity (CPU power, disk I/O limitations, memory, and so on), autonomy (you want one person to have management rights to the server this system is running on, and someone else to have admin rights to a different server), and just how many databases your company or client has. Some servers have only one production database; others have many. Also, any version of SQL Server that you're likely to find in production these days has multiple instances of SQL Server — complete with separate logins and management rights — all on the same physical server. (SQL Server 2000 was already five years old by the time it was replaced, so I'll assume most shops have that or higher.)

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

* master

* model

* msdb

* tempdb

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

* ReportServer: The database that serves Reporting Server configuration and model storage needs

* ReportServerTempDB: The working database for Reporting Server

* AdventureWorks: The sample database

* AdventureWorksDW: Sample for use with Analysis Services

In addition to the system-installed examples, you may, when searching the web or using other tutorials, find reference to a couple of older samples:

* pubs

* Northwind

Because these examples were no longer used in the prior edition of this book, I won't deal with them further here, but I still mention them mostly because they carry fond memories from simpler times, and partly because you might find them out there somewhere.

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. 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, were, in the past, occasionally used in a pinch to provide system configuration information, such as whether certain objects existed before you performed operations on them. Microsoft warned developers for years not to use the system tables directly, but, because there were few other options, most developers ignored that advice. Happily, Microsoft began providing other options in the form of system and information schema views; you can now utilize these views to get at the systems' metadata as necessary, with

Microsoft's full blessing. For example, if you try to create an object that already exists in any particular database, you get an error. If you want to force the issue, you could test to see whether the table already has an entry in the sys.objects table for that database. If it does, you would delete that object before re-creating it.

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 want, 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 because 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 points to keep in mind when altering the model database:

* 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.

* Similar pitfalls apply when adding objects or changing settings, which can lead to unintended consequences. 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. 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. (An alias is created in the local database for you to reference it by, but the physical table is created in tempdb.) 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. Not only are the objects within it temporary, the database itself is temporary. It has the distinction of being the only database in your system that is rebuilt from scratch every time you start your SQL Server.

ReportServer

This database will exist only if you installed ReportServer. (It does not necessarily have to be the same server as the database engine, but note that if it is a different server, it requires a separate license.) The ReportServer database stores any persistent metadata for your Reporting Server instance. Note that this is purely an operational database for a given Reporting Server instance, and should not be modified (and only rarely accessed) other than through the Reporting Server.

ReportServerTempDB

This serves the same basic function as the ReportServer database, except that it stores nonpersistent data (such as working data for a report that is running). Again, this is a purely operational database, and you should not access or alter it in any way except through the Reporting Server.

AdventureWorks

SQL Server included samples long before this one came along. The old samples had their shortcomings, though. For example, they contained a few poor design practices. In addition, they were simplistic and focused on demonstrating certain database concepts rather than on SQL Server as a product, or even databases as a whole. I'll hold off the argument of whether AdventureWorks has the same issues. Let's just say that AdventureWorks was, among other things, an attempt to address this problem.

From the earliest stages of development of SQL Server 2005, Microsoft knew it wanted a far more robust sample database that would act as a sample for as much of the product as possible. AdventureWorks is the outcome of that effort. As much as you will hear me complain about its overly complex nature for the beginning user, it is a masterpiece in that it shows it all off. Okay, so it's not really everything, but it is a fairly complete sample, with more realistic volumes of data, complex structures, and sections that show samples for the vast majority of product features. In this sense, it's truly terrific.

AdventureWorks will be something of your home database — you'll use it extensively as you work through the examples in this book.

AdventureWorksDW

This is the Analysis Services sample. The DW stands for Data Warehouse, which is the type of database over which most Analysis Services projects are built. Perhaps the greatest thing about this sample is that Microsoft had the foresight to tie the transaction database sample with the analysis sample, providing a whole set of samples that show the two of them working together.

Decision support databases are discussed in more detail in Chapters 17 and 18 of this book, and you will be using this database, so keep that in mind as you fire up Analysis Services and play around. Take a look at the differences between the two databases. They are meant to serve the same fictional company, but they have different purposes: learn from it.

The pubs Database

Ahhhh, pubs! It's almost like an old friend. pubs is one of the original example databases and was supplied with SQL Server as part of the install prior to SQL Server 2005. It is now available only as a separate download from the Microsoft website. You still find many training articles and books that refer to pubs, but Microsoft has made no promises regarding how long they will continue to make it available. pubs has absolutely nothing to do with the operation of SQL Server. It is merely there to provide a consistent place for your training and experimentation. You do not need pubs to work the examples in this book, but you may want to download and install it to work with other examples and tutorials you may find on the web.

The Northwind Database

If your past programming experience has involved Access or Visual Basic, you should already be somewhat familiar with the Northwind database. Northwind was added to SQL Server beginning in version 7.0, but was removed from the basic installation as of SQL Server 2005. Much like pubs, it can, for now, be downloaded separately from the base SQL Server install. (Fortunately, it is part of the same sample download and install as pubs is.) Like pubs, you do not need the Northwind database to work the examples in this book, but it is handy to have it available for work with various examples and tutorials you will find on the web.

The Transaction Log

Believe it or not, the database file itself isn't where most things happen. Although the data is certainly read in from there, any changes you make don't initially go to the database itself. Instead, they are written serially to the transaction log. At some later point in time, the database is issued a checkpoint; it is at that point in time that all the changes in the log are propagated to the actual database file.

The database is in a random access arrangement, but the log is serial in nature. While the random nature of the database file allows for speedy access, the serial nature of the log allows things to be tracked in the proper order. The log accumulates changes that are deemed as having been committed, and then writes several of them at a time to the physical database file(s).

You'll take a much closer look at how things are logged in Chapter 14, but for now, remember that the log is the first place on disk that the data goes, and it's propagated to the actual database at a later time. You need both the database file and the transaction log to have a functional database.

The Most Basic Database Object: Table

Databases are made up of many things, but none is more central to the make-up of a database than tables are. A table can be thought of as equating to an accountant's ledger or an Excel spreadsheet and consists of domain data (columns) and entity data (rows). The actual data for the database is stored in the tables.

Each table definition also contains the metadata (descriptive information about data) that describes the nature of the data it is to contain. Each column has its own set of rules about what can be stored in that column. A violation of the rules of any one column can cause the system to reject an inserted row, an update to an existing row, or the deletion of a row.

Take a look at the Production.Location table in the Adventure Works database. (The view presented in Figure 1-1 is from the SQL Server Management Studio. This is a fundamental tool and you will see how to make use of it in the next chapter.)

The table in Figure 1-1 is made up of five columns of data. The number of columns remains constant regardless of how much data (even zero) is in the table. Currently, the table has 14 records. The number of records will go up and down as you add or delete data, but the nature of the data in each record (or row) is described and restricted by the data type of the column.

Indexes

An index is an object that exists only within the framework of a particular table or view. An index works much like the index does in the back of an encyclopedia. There is some sort of lookup (or "key") value that is sorted in a particular way, and once you have that, you are provided another key with which you can look up the actual information you were after.

(Continues...)

---

Excerpted from Beginning Microsoft SQL Server 2012 Programming by Paul Atkinson Robert Vieira Copyright © 2012 by John Wiley & Sons, Ltd. Excerpted by permission of John Wiley & Sons. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---

Table of Contents

INTRODUCTION xxv

CHAPTER 1: RDBMS BASICS: WHAT MAKES UP A SQL SERVER DATABASE? 1

An Overview of Database Objects 2

SQL Server Data Types 13

SQL Server Identifiers for Objects 19

Summary 21

CHAPTER 2: LEARNING THE TOOLS OF THE TRADE 23

Getting Help with Books Online 24

SQL Server Configuration Manager 25

SQL Server Management Studio 31

SQL Server Data Tools (formerly BIDS) 43

SQL Server Integration Services (SSIS) 44

SQL Server Reporting Services (SSRS) 45

SQL Server Analysis Services (SSAS) 45

Bulk Copy Program (BCP) 46

SQL Server Profiler 46

sqlcmd 46

PowerShell 47

Summary 47

CHAPTER 3: THE FOUNDATION STATEMENTS OF T-SQL 49

Getting Started with a Basic SELECT Statement 50

Adding Data with the INSERT Statement 76

Changing What You’ve Got with the UPDATE Statement 84

The DELETE Statement 87

Summary 88

CHAPTER 4: JOINS 91

Combining Table Data with JOINs 92

Selecting Matching Rows with INNER JOIN 93

Retrieving More Data with OUTER JOIN 102

Seeing Both Sides with FULL JOINs 115

Understanding CROSS JOINs 117

Exploring Alternative Syntax for Joins 118

Stacking Results with UNION 121

Summary 125

CHAPTER 5: CREATING AND ALTERING TABLES 129

Object Names in SQL Server 130

The CREATE Statement 134

The ALTER Statement 155

The DROP Statement 164

Using the GUI Tool 165

Summary 171

CHAPTER 6: KEYS AND CONSTRAINTS 175

Types of Constraints 176

Constraint Naming 178

Key Constraints 179

CHECK Constraints 197

DEFAULT Constraints 199

Disabling Constraints 201

Rules and Defaults — Cousins of Constraints 206

Triggers for Data Integrity 210

Choosing What to Use 210

Summary 211

CHAPTER 7: ADDING MORE TO YOUR QUERIES 213

What Is a Subquery? 214

Building Correlated Subqueries 218

Derived Tables 225

Using Common Table Expressions (CTEs) 228

Using the EXISTS Operator 232

Mixing Data Types: CAST and CONVERT 236

Synchronizing Data with the MERGE Command 239

Gathering Affected Rows with the OUTPUT Clause 243

Through the Looking Glass: Windowing Functions 246

One Chunk at a Time: Ad Hoc Query Paging 251

Performance Considerations 252

CHAPTER 8: BEING NORMAL: NORMALIZATION AND OTHER BASIC DESIGN ISSUES 257

Understanding Tables 258

Keeping Your Data “Normal” 258

Understanding Relationships 270

Diagramming Databases 279

Denormalization 291

Beyond Normalization 291

Drawing Up a Quick Example 293

Summary 302

CHAPTER 9: SQL SERVER STORAGE AND INDEX STRUCTURES 305

SQL Server Storage 306

Understanding Indexes 308

Creating, Altering, and Dropping Indexes 323

Choosing Wisely: Deciding Which Index Goes Where and When 331

Maintaining Your Indexes 345

Summary 350

CHAPTER 10: VIEWS 353

Creating Simple Views 354

Editing Views with T-SQL 367

Dropping Views 367

Creating and Editing Views in the Management Studio 367

Auditing: Displaying Existing Code 371

Protecting Code: Encrypting Views 373

About Schema Binding 374

Making Your View Look like a Table with VIEW_METADATA 375

Indexed (Materialized) Views 375

Indexing an Aggregate View 378

Summary 381

CHAPTER 11: WRITING SCRIPTS AND BATCHES 383

Understanding Script Basics 384

Grouping Statements into Batches 399

Running from the Command Prompt: sqlcmd 405

Dynamic SQL: Using the EXEC Command 409

Using Control-of-Flow Statements 415

Summary 431

CHAPTER 12: STORED PROCEDURES 435

Creating the Sproc: Basic Syntax 436

Changing Stored Procedures with ALTER 438

Dropping Sprocs 438

Parameterizing Sprocs 438

More on Dealing with Errors 448

What a Sproc Offers 468

Extended Stored Procedures (XPs) 472

A Brief Look at Recursion 472

Debugging 475

Understanding .NET Assemblies 484

When to Use Stored Procedures 485

Summary 486

CHAPTER 13: USER-DEFINED FUNCTIONS 489

What a UDF Is 489

UDFs Returning a Scalar Value 491

UDFs That Return a Table 496

Debugging User-Defined Functions 506

Using .NET in a Database World 507

Summary 507

CHAPTER 14: TRANSACTIONS AND LOCKS 509

Understanding Transactions 509

How the SQL Server Log Works 514

Understanding Locks and Concurrency 518

Setting the Isolation Level 527

Dealing with Deadlocks (aka “a 1205”) 531

Summary 534

CHAPTER 15: TRIGGERS 537

What Is a Trigger? 538

ON 540

WITH ENCRYPTION 540

FOR|AFTER 540

The FOR|AFTER versus the INSTEAD OF Clause 541

NOT FOR REPLICATION 543

AS 543

Using Triggers for Data Integrity Rules 543

Dealing with Requirements Sourced from Other Tables 544

Using Triggers to Check the Delta of an Update 545

Using Triggers for Custom Error Messages 547

Other Common Uses for Triggers 548

Other Trigger Issues 548

Triggers Can Be Nested 548

Triggers Can Be Recursive 549

Triggers Don’t Prevent Architectural Changes 549

Triggers Can Be Turned Off without Being Removed 550

Trigger Firing Order 550

INSTEAD OF Triggers 552

Performance Considerations 552

Triggers Are Reactive Rather Than Proactive 552

Triggers Don’t Have Concurrency Issues with the Process That Fires Them 553

Using IF UPDATE() and COLUMNS_UPDATED 553

Keep It Short and Sweet 556

Don’t Forget Triggers When Choosing Indexes 556

Try Not to Roll Back within Triggers 556

Dropping Triggers 556

Debugging Triggers 557

Summary 558

CHAPTER 16: A BRIEF XML PRIMER 561

XML Basics 562

What SQL Server Brings to the Party 577

A Brief Word on XSLT 610

Summary 613

CHAPTER 17: BUSINESS INTELLIGENCE FUNDAMENTALS 615

What Is Business Intelligence? 616

Those Who Forget History: The Data Warehouse 619

Dimensional Modeling: Why Be Normal? 624

ETLs 637

Making Your Data Actionable: BI Reporting Techniques 642

Summary 647

CHAPTER 18: BI STORAGE AND REPORTING RESOURCES 649

SQL Server Analysis Services, or How I Learned to Stop Worrying and Love the Cube 661

Building Your First Cube 663

Self-Service BI: User Tools 675

Summary 678

CHAPTER 19: REPORTING FOR DUTY, SIR! A LOOK AT REPORTING SERVICES 681

Reporting Services 101 682

Understanding the SSRS Report Lifecycle 683

Understanding the Reporting Services Architecture 684

Building Simple Report Models 686

Report Server Projects 711

Summary 717

CHAPTER 20: GETTING INTEGRATED WITH INTEGRATION SERVICES 719

Understanding the Problem 720

Using the Import/Export Wizard to Generate Basic Packages 720

Examining Package Basics 727

Executing Packages 738

A Final Word on Packages 745

Summary 745

CHAPTER 21: PLAYING ADMINISTRATOR 747

Scheduling Jobs 748

Logins and Users 760

Backup and Recovery 764

Index Maintenance 770

Policy Based Management 774

Automating Administration Tasks with PowerShell 775

Summary 779

APPENDIX: ANSWERS TO EXERCISES 783

INDEX 807

ONLINE APPENDICES

BONUS APPENDIX 1: SYSTEM FUNCTIONS 1

BONUS APPENDIX 2: VERY SIMPLE CONNECTIVITY EXAMPLES 69