Introduction

In the mid-1990s, when Microsoft parted ways with Sybase in their conjoint development of SQL Server and started developing Windows NT versions, it was almost a whole different product. When version 6.5 was released in 1996, it was starting to gain credibility as an enterprise-class database server. It still had rough management tools and only core functionalities, and some limitations that are forgotten today, like fixed size devices and the inability to drop table columns. It was doing anyway what a database server is designed for: storing and retrieving data for client applications. There was already enough to learn for anyone new to the relational database world. A lot of concepts had to be understood, like foreign keys, stored procedures or triggers, and of course, the dedicated language, T-SQL, a baffling experience for every newcomer. Writing SELECT queries sometimes involves a lot of head-scratching. But when we—developers—eventually mastered all that, we still had to keep up with additions made by Microsoft to the database engine with each new version, and some of them were not for the faint of heart, like .NET database modules, support for XML and the XQuery language or even a full implementation of symmetric and asymmetric encryption. These additions are today core components of SQL Server. Because an RDBMS (Relational DataBase Management Server) like SQL Server is one of the most important elements of the IT environment, we need to make the best of it, which implies a good understanding of the more advanced features. We have designed this book with the goal of helping T-SQL developers get the absolute most out of the development features and functionality in SQL Server 2012. We will cover all of what’s needed to master T-SQL development, from the management and development tools to performance tuning. We hope you will enjoy it and it will help you to become a pro SQL Server 2012 developer.

Whom This Book Is For

This book is intended for SQL Server developers who need to port code from prior versions of SQL Server, and those who want to get the most out of database development on the 2012 release. You should have a working knowledge of SQL, preferably T-SQL on SQL Server 2008 or 2005, as most of the examples in this book are written in T-SQL. In this book, we will cover some of the basics of T-SQL, including some introductory concepts like data domain and three-valued logic—but this is not a beginner’s book. We will not be discussing database design, database architecture, normalization, and the most basic of SQL constructs in any kind of detail. Apress offers a beginner’s guide to T-SQL 2012 that does that. We will be focusing here on topics of advanced SQL Server 2012 functionalities, which assume a basic understanding of SQL statements like INSERT and SELECT. A working knowledge of C# and the .NET Framework is also useful (but not required), as two chapters are dedicated to .NET client programming and .NET database integration. Some examples in the book will be written in C#. When C# sample code is provided, it is explained in detail, so an in-depth knowledge of the .NET Framework class library is not required.

How This Book Is Structured

This book was written to address the needs of four types of readers:

  • SQL developers who are coming from other platforms to SQL Server 2012
  • SQL developers who are moving from prior versions of SQL Server to SQL Server 2012
  • SQL developers who have a working knowledge of basic T-SQL programming and want to learn about advanced features
  • Database Administrators and nondevelopers who need a working knowledge of T-SQL functionality to effectively support SQL Server 2012 instances

For all types of readers, this book is designed to act as a tutorial that describes and demonstrates T-SQL features with working examples, and as a reference for quickly locating details about specific features. The following sections provide a chapter-by-chapter overview.

Chapter 1

Chapter 1 starts this book off by putting SQL Server 2012’s implementation of T-SQL in context, including a short history of T-SQL, a discussion of T-SQL basics, and an overview of T-SQL coding best practices.

Chapter 2

Chapter 2 gives an overview of the tools that are packaged with SQL Server and available to SQL Server developers. Tools discussed include SQL Server Management Studio (SSMS), SQLCMD, SQL Server Data Tools (SSDT), and SQL Profiler, among others.

Chapter 3

Chapter 3 introduces T-SQL procedural code, including control-of-flow statements like IF...THEN and WHILE. In this chapter, we also discuss CASE expressions and CASE-derived functions, and provide an in-depth discussion of SQL three-valued logic.

Chapter 4

Chapter 4 discusses the various types of T-SQL user-defined functions available to encapsulate T-SQL logic on the server. We talk about all forms of T-SQL-based user-defined functions, including scalar user-defined functions, inline table-valued functions, and multistatement table-valued functions.

Chapter 5

Chapter 5 covers stored procedures, which allow you to create server-side T-SQL subroutines. In addition to describing how to create and execute stored procedures on SQL Server, we also address a thorny issue for some—the issue of why you might want to use stored procedures.

Chapter 6

Chapter 6 introduces all three types of SQL Server triggers: classic DML triggers, which fire in response to DML statements; DDL triggers, which fire in response to server and database DDL events; and logon triggers, which fire in response to server LOGON events.

Chapter 7

Chapter 7 discusses SQL Server encryption, including the column-level encryption functionality introduced in SQL Server 2005 and the newer transparent database encryption (TDE) and extensible key management (EKM) functionality, both introduced in SQL Server 2008.

Chapter 8

Chapter 8 dives into the details of common table expressions (CTEs) and windowing functions in SQL Server 2012, which feature some improvements to the OVER clause to achieve row-level running and sliding aggregations.

Chapter 9

Chapter 9 discusses T-SQL data-types, first with some important things to know about basic data-types, like how to handle date and time in your code, and then with advanced data types and features, like the hierarchyid complex type, and the FILESTREAM and filetable functionality.

Chapter 10

Chapter 10 covers the full-text search (FTS) feature and advancements made since SQL Server 2008, including greater integration with the SQL Server query engine and greater transparency by way of FTS-specific data management views and functions.

Chapter 11

Chapter 11 provides an in-depth discussion of SQL Server 2012 XML functionality, which carries forward the new features introduced in SQL Server 2005 and improves upon them. We cover several XML-related topics in this chapter, including the xml data type and its built-in methods, the FOR XML clause, and XML indexes.

Chapter 12

Chapter 12 discusses XQuery and XPath support in SQL Server 2012, including improvements on the XQuery support introduced in SQL Server 2005, like support for the xml data type in XML DML insert statements and the let clause in FLWOR expressions.

Chapter 13

Chapter 13 introduces SQL Server catalog views, which are the preferred tools for retrieving database and database object metadata. This chapter also discusses dynamic management views and functions, which provide access to server and database state information.

Chapter 14

Chapter 14 is a discussion of SQL CLR Integration functionality in SQL Server 2012. In this chapter, we discuss and provide examples of SQL CLR stored procedures, user-defined functions, user-defined types, and user-defined aggregates.

Chapter 15

Chapter 15 focuses on client-side support for SQL Server, including ADO.NET-based connectivity and the newest Microsoft ORM (Object-Relational Mapping) technology, Entity Framework 4.

Chapter 16

Chapter 16 discusses SQL Server connectivity using middle-tier technologies. Since native HTTP endpoints are deprecated since SQL Server 2008, we discuss them as items that may need to be supported in existing databases but should not be used for new development. We focus instead on possible replacement technologies, such as ADO.NET Data Services and IIS/.NET Web Services.

Chapter 17

Chapter 17 discusses improvements to server-side error handling made possible with the TRY...CATCH block. We also discuss various methods for debugging code, including using the Visual Studio T-SQL debugger. This chapter wraps up with a discussion of dynamic SQL and SQL injection, including the causes of SQL injection and methods you can use to protect your code against this type of attack.

Chapter 18

Chapter 18 provides an overview of performance-tuning SQL Server code. This chapter discusses SQL Server storage, indexing mechanisms, and query plans. We wrap up the chapter with a discussion of a proven methodology for troubleshooting T-SQL performance issues.

Appendix A

Appendix A provides the answers to the exercise questions that we’ve included at the end of each chapter.

Appendix B

Appendix B is designed as a quick reference to the XQuery Data Model (XDM) type system.

Appendix C

Appendix C provides a quick reference glossary to several terms, many of which may be new to those using SQL Server for the first time.

Appendix D

Appendix D is a quick reference to the SQLCMD command-line tool, which allows you to execute ad hoc T-SQL statements and batches interactively, or run script files.

Conventions

To help make reading this book a more enjoyable experience, and to help you get as much out of it as possible, we’ve used the following standardized formatting conventions throughout.

C# code is shown in code font. Note that C# code is case sensitive. Here’s an example:

while (i < 10)

T-SQL source code is also shown in code font, with keywords capitalized. Note that we’ve lowercased the data types in the T-SQL code to help improve readability. Here’s an example:

DECLARE @x xml;

XML code is shown in code font with attribute and element content in bold for readability.

Some code samples and results have been reformatted in the book for easier reading. XML ignores whitespace, so the significant content of the XML has not been altered. Here’s an example:

<book publisher = "Apress" > Pro SQL Server 2012 XML</book>:

image Note  Notes, tips, and warnings are displayed like this, in a special font with solid bars placed over and under the content.

SIDEBARS

Sidebars include additional information relevant to the current discussion and other interesting facts. Sidebars are shown on a gray background.

Prerequisites

This book requires an installation of SQL Server 2012 to run the T-SQL sample code provided. Note that the code in this book has been specifically designed to take advantage of SQL Server 2012 features, and some of the code samples will not run on prior versions of SQL Server. The code samples presented in the book are designed to be run against the AdventureWorks 2012 sample database, available from the CodePlex web site at http://www.codeplex.com/MSFTDBProdSamples. The database name used in the samples is not AdventureWorks2012, but AdventureWorks, for the sake of simplicity.

If you are interested in compiling and deploying the .NET code samples (the client code and SQL CLR examples) presented in the book, we highly recommend an installation of Visual Studio 2010. Although you can compile and deploy .NET code from the command line, we’ve provided instructions for doing so through the Visual Studio Integrated Development Environment (IDE). We find that the IDE provides a much more enjoyable experience.

Some examples, such as the ADO.NET Data Services examples in Chapter 16, require an installation of IIS (Internet Information Server) as well. Other code samples presented in the book may have specific requirements, such as the Entity Framework 4 samples, which require the .NET Framework 3.5. We’ve added notes to code samples that have additional requirements like these.

Apress Website

Visit this book’s apress.com webpage at http://www.apress.com/9781430245964 for the complete sample code download for this book. It is compressed in a zip file and structured so that each subdirectory contains all the sample code for its corresponding chapter.

We and the Apress team have made every effort to ensure that this book is free from errors and defects. Unfortunately, the occasional error may have slipped past us, despite our best efforts. In the event that you find an error in the book, please let us know! You can submit errors to Apress by visiting http://www.apress.com/9781430245964 and filling out the form under the “Errata” tab.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset