Introduction
In the mid-1990s, when Microsoft parted ways with Sybase in their conjoint development of SQL Server, it was an entirely different product. When SQL Server 6.5 was released in 1996, it was starting to gain credibility as an enterprise-class database server. It still had rough management tools, only core functionalities, and some limitations that are forgotten today, like fixed-size devices and the inability to drop table columns. It functioned as a rudimentary database server: storing and retrieving data for client applications. There was already plenty for anyone new to the relational database world to learn. Newcomers had to understand many concepts, such as foreign keys, stored procedures, triggers, and the dedicated language, T-SQL (which could be a baffling experience—writing SELECT queries sometimes involves a lot of head-scratching). Even when developers mastered all that, they still had to keep up with the additions Microsoft made to the database engine with each new version. Some of the changes were not for the faint of heart, like .NET database modules, support for XML and the XQuery language, and a full implementation of symmetric and asymmetric encryption. These additions are today core components of SQL Server.
Because a relational database management server (RDBMS) like SQL Server is one of the most important elements of the IT environment, you need to make the best of it, which implies a good understanding of its 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 2014. We cover all of what’s needed to master T-SQL development, from using management and development tools to performance tuning. We hope you enjoy the book and that it helps you to become a pro SQL Server 2014 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 2014 release. You should have a working knowledge of SQL, preferably T-SQL on SQL Server 2005 or later, because most of the examples in this book are written in T-SQL. The book covers some of the basics of T-SQL, including introductory concepts like data domain and three-valued logic, but this isn’t a beginner’s book. We don’t discuss database design, database architecture, normalization, and the most basic SQL constructs in any detail. Apress offers a beginner’s guide to T-SQL 2012 that covers more basic SQL constructs.
We focus here on advanced SQL Server 2014 functionalities, and so we assume you have 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), because two chapters are dedicated to .NET client programming and .NET database integration.
Some examples in the book are written in C#. When C# sample code is provided, it’s explained in detail, so an in-depth knowledge of the .NET Framework class library isn’t 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 2014
- SQL developers who are moving from prior versions of SQL Server to SQL Server 2014
- SQL developers who have a working knowledge of basic T-SQL programming and want to learn about advanced features
- Database administrators and non-developers who need a working knowledge of T-SQL functionality to effectively support SQL Server 2014 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 by putting SQL Server 2014’s implementation of T-SQL in context, including a short history, a discussion of the 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. This chapter also discusses CASE expressions and CASE-derived functions, and provides 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 an issue that is thorny for some: why you might want to use stored procedures.
Chapter 6
Chapter 6 covers the latest features available in SQL Server 2014: In-Memory OLTP tables. The In-Memory features provide the capability to dramatically increase the database performance of an OLTP or data-warehouse instance. With the new features also come some limitations.
Chapter 7
Chapter 7 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 8
Chapter 8 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 9
Chapter 9 dives into the details of common table expressions (CTEs) and windowing functions in SQL Server 2014, which feature some improvements to the OVER clause to achieve row-level running and sliding aggregations.
Chapter 10
Chapter 10 discusses T-SQL data types: first some important things to know about basic data types, such as how to handle date and time in your code, and then advanced data types and features, such as the hierarchyid complex type and FILESTREAM and filetable functionality.
Chapter 11
Chapter 11 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 12
Chapter 12 provides an in-depth discussion of SQL Server 2014 XML functionality, which carries forward and improve on the new features introduced in SQL Server 2005. 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 13
Chapter 13 discusses XQuery and XPath support in SQL Server 2014, including improvements on the XQuery support introduced in SQL Server 2005, such as support for the xml data type in XML DML insert statements and the let clause in FLWOR expressions.
Chapter 14
Chapter 14 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 15
Chapter 15 covers SQL CLR Integration functionality in SQL Server 2014. In this chapter, we discuss and provide examples of SQL CLR stored procedures, user-defined functions, user-defined types, and user-defined aggregates.
Chapter 16
Chapter 16 focuses on client-side support for SQL Server, including ADO.NET-based connectivity and the newest Microsoft object-relational mapping (ORM) technology, Entity Framework 4.
Chapter 17
Chapter 17 discusses SQL Server connectivity using middle-tier technologies. Because native HTTP endpoints have been deprecated since SQL Server 2008, we discuss them as items that may need to be supported in existing databases but shouldn’t be used for new development. We focus instead on possible replacement technologies, such as ADO.NET data services and IIS/.NET web services.
Chapter 18
Chapter 18 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 19
Chapter 19 provides an overview of performance-tuning SQL Server code. This chapter discusses SQL Server storage, indexing mechanisms, and query plans. We end 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 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 2014 XML</book>:
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 2014 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 2014 features, and some of the code samples won’t run on prior versions of SQL Server. The code samples presented in the book are designed to be run against the AdventureWorks 2014 and SQL Server 2014 In-Memory OLTP sample databases, available from the CodePlex web site at www.codeplex.com/MSFTDBProdSamples. The database name used in the samples is not AdventureWorks2014, but AdventureWorks or 2014 In-Memory, for the sake of simplicity.
If you’re 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 or a later version. 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 Internet Information Server(IIS) 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 Web Site
Visit this book’s apress.com web page at www.apress.com/9781484201466 for the complete sample code download for this book. It’s 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 does slip 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 www.apress.com/9781484201466 and filling out the form on the Errata tab.