Beginning Microsoft SQL Server 2012 Programming

Paul Atkinson, Robert Vieira

• Publisher: Wrox
• Year: 2012
• ISBN:
  9781118236215
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

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.

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

---

NOTE If you are already familiar with SQL Server you may now be saying, “What? What happened to logins or 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 won’t give them significant thought during the design and programming processes. (They are programmable via something called the SQL Management Objects [SMO], which is beyond the scope of this book.) Although there are some exceptions to this rule, I generally consider them to be advanced in nature, and thus they are not covered in the beginner version of this book.

---

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

---

NOTE I’m sure many of you are now asking, “Can I have different versions of S...

Table of contents

• Cover
• Contents
• Title page
• Copyright
• Dedication
• Credits
• About the Authors
• About the Technical Editor
• Acknowledgments
• Introduction
• 1 RDBMS Basics: What Makes Up a SQL Server Database?
• 2 Learning the Tools of the Trade
• 3 The Foundation Statements of T-SQL
• 4 Joins
• 5 Creating and Altering Tables
• 6 Keys and Constraints
• 7 Adding More to Your Queries
• 8 Being Normal: Normalization and Other Basic Design Issues
• 9 SQL Server Storage and Index Structures
• 10 Views
• 11 Writing Scripts and Batches
• 12 Stored Procedures
• 13 User-Defined Functions
• 14 Transactions and Locks
• 15 Triggers
• 16 A Brief XML Primer
• 17 Business Intelligence Fundamentals
• 18 BI Storage and Reporting Resources
• 19 Reporting for Duty, Sir! A Look at Reporting Services
• 20 Getting Integrated with Integration Services
• 21 Playing Administrator
• Appendix: Answers to Exercises
• Bonus Appendices
• Advertisement
• End User License Agreement