Pro T-SQL Programmer's Guide

Frontmatter

Chapter 1. Foundations of T-SQL

Abstract

SQL Server 2014 is the latest release of Microsoft’s enterprise-class database management system (DBMS). As the name implies, a DBMS is a tool designed to manage, secure, and provide access to data stored in structured collections in databases. Transact-SQL (T-SQL) is the language that SQL Server speaks. T-SQL provides query and data-manipulation functionality, data definition and management capabilities, and security administration tools to SQL Server developers and administrators. To communicate effectively with SQL Server, you must have a solid understanding of the language. In this chapter, you begin exploring T-SQL on SQL Server 2014.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 2. Tools of the Trade

Abstract

SQL Server 2014 comes with a wide selection of tools and utilities to make development easier and more productive for developers. This chapter introduces some of the most important tools for SQL Server developers, including SQL Server Management Studio (SSMS) and the SQLCMD utility, SQL Server Data Tool add-ins to Microsoft Visual Studio, SQL Profiler, Database Tuning Advisor, Extended Events, and SQL Server 2014 Books Online (BOL). You’re also introduced to supporting tools like SQL Server Integration Services (SSIS), the Bulk Copy Program (BCP), and the AdventureWorks 2014 sample database, which you use in examples throughout the book.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 3. Procedural Code

Abstract

T-SQL has always included support for procedural programming in the form of control-of-flow statements and cursors. One thing that throws developers from other languages off their guard when migrating to SQL is the peculiar three-valued logic (3VL) we enjoy. Chapter 1 introduced you to SQL 3VL, and this chapter expands further on this topic. SQL 3VL is different from most other programming languages’ simple two-valued Boolean logic. This chapter also discusses T-SQL control-of-flow constructs, which allow you to change the normally sequential order of statement execution. Control-of-flow statements let you branch your code logic with statements like IF…ELSE…, perform loops with statements like WHILE, and perform unconditional jumps with the GOTO statement. You’re also introduced to CASE expressions and CASE-derived functions that return values based on given comparison criteria in an expression. Finally, we finish the chapter by explaining a topic closely tied to procedural code: SQL cursors.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 4. User-Defined Functions

Abstract

Each new version of SQL Server features improvements to T-SQL that make development easier. SQL Server 2000 introduced (among other things) the concept of user-defined functions (UDFs). Like functions in other programming languages, T-SQL UDFs provide a convenient way for developers to define routines that accept parameters, perform actions based on those parameters, and return data to the caller. T-SQL functions come in three flavors: inline table-valued functions (TVFs), multistatement TVFs, and scalar functions. SQL Server 2014 also supports the ability to create CLR integration UDFs, which are discussed in Chapter 15.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 5. Stored Procedures

Abstract

Stored procedures (SPs) have been a part of T-SQL from the beginning. SPs provide a means for creating server-side subroutines written in T-SQL. SQL Server 2014 introduces the ability to natively compile an SP that accesses memory-optimized tables. The efficiencies gained with natively compiled SPs are an absolute game-changer in how you consider architecting an OLTP database solution.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 6. In-Memory Programming

Abstract

SQL Server 2014 introduces new In-Memory features that are a game-changer in how you consider the data and physical architecture of database solutions. The manner in which data is accessed, the indexes used for in-memory tables, and the methods used for concurrency make this a significant new feature of the database software in SQL Server 2014. In-Memory OLTP is a performance enhancement that allows you to store data in memory using a completely new architecture. In addition to storing data in memory, database objects are compiled into a native DLL in the database.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 7. Triggers

Abstract

SQL Server provides triggers as a means of executing T-SQL code in response to database object, database, and server events. SQL Server 2014 implements three types of triggers: classic T-SQL Data Manipulation Language (DML) triggers, which fire in response to INSERT, UPDATE, and DELETE events against tables; Data Definition Language (DDL) triggers, which fire in response to CREATE, ALTER, and DROP statements; and logon triggers, which fire in response to LOGON events. DDL triggers can also fire in response to some system SPs that perform DDL-like operations.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 8. Encryption

Abstract

SQL Server 2014 supports built-in column- and database-level encryption functionality directly through T-SQL. Column-level encryption allows you to encrypt the data in your database at the column level. Back in the days of SQL Server 2000 (and before), you had to turn to third-party tools or write your own extended stored procedures (XPs) to encrypt sensitive data. Even with these tools in place, subpar implementation of various aspects of the system, such as encryption key management, could leave many systems in a vulnerable state.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 9. Common Table Expressions and Windowing Functions

Abstract

SQL Server 2014 continues support for the extremely useful common table expression (CTE), first introduced in SQL Server 2005. CTEs can simplify your queries to make them more readable and maintainable. SQL Server also supports self-referential CTEs, which make for very powerful recursive queries.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 10. Data Types and Advanced Data Types

Abstract

Transact-SQL is a strongly-typed language. Columns and variables must have a valid data type, and the type is a constraint of the column. In this chapter, we will not cover all data types comprehensively. We will skip the obvious part and concentrate on specific information and on more complex and sophisticated data types that were introduced in SQL Server over time.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 11. Full-Text Search

Abstract

Full-text search (FTS) is a powerful SQL Server feature allowing for advanced searches using multiple languages to find information in documents as well as document properties. FTS is tightly integrated with SQL Server 2014 and can be easily managed with SQL Server Management Studio (SSMS) and monitored with standard dynamic management views. FTS broadens the scope of what is thought of as a T-SQL search by providing meaningful results from sometimes seemingly unstructured textual data. SQL Server 2012 introduced statistical semantics which allow for searching on document meaning as opposed to simply searching content. Based on word distributions and other factors, statistical semantics allows you to find documents with similar contents.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 12. XML

Abstract

SQL Server 2014 continues the standard for XML integration included with the SQL Server 2008 release. SQL Server 2014 XML still offers tight integration with T-SQL through the xml data type, support for the World Wide Web Consortium (W3C) XQuery and XML Schema recommendations.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 13. XQuery and XPath

Abstract

As we described in Chapter 12, SQL Server 2014 continues the high level of XML integration begun in SQL Server 2005. As part of that integration, SQL Server’s xml data type provides built-in functionality for shredding XML data into relational format, querying XML nodes and singleton atomic values via XQuery, and modifying XML data via XML Data Modification Language (XML DML). This chapter focuses on how to get the most out of SQL Server’s implementation of the powerful and flexible XPath and XQuery standards.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 14. Catalog Views and Dynamic aent Views

Abstract

SQL Server has always offered access to metadata describing databases, tables, views, and other database objects. Prior to the introduction of catalog views in SQL Server 2005, the primary methods of accessing this metadata included system tables, system SPs, INFORMATION_SCHEMA views, and SQL Distributed Management Objects (SQL-DMO). Catalog views provide access to a richer set of detailed information than any of these options provided in previous SQL Server releases. SQL Server even includes catalog views that allow you to access server-wide configuration metadata.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 15. .NET Client Programming

Abstract

Which is more important: an efficient database or a well-designed client application that connects to the database? In our estimation, they’re both equally important. After all, your database can be very well designed and extremely efficient, but that won’t matter to the end user if the client application they use to connect to your database is slow and unresponsive. This book focuses on SQL Server server-side development functionality, but we’ve decided to take a moment to introduce some of the tools available to create efficient SQL Server client applications. The .NET Framework, in particular, offers several options to make SQL Server 2014 client connectivity simple and efficient. This chapter discusses using ADO.​NET and the .NET SqlClient as a basis for building your own easy-to-use, cutting-edge SQL Server client applications, and you venture into modern O/RM trends with LINQ to SQL and Entity Framework.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 16. CLR Integration Programming

Abstract

One of the most prominent enhancements to SQL Server 2005 was the introduction of the integrated SQL Common Language Runtime, named SQL CLR at that time. What is now called CLR integration is an SQL Server–specific version of the .NET Common Language Runtime, which allows you to run .NET-managed code in the database. CLR integration programming is a broad subject that could easily fill an entire book, and in fact it does—Pro SQL Server 2005 Assemblies, by Robin Dewson and Julian Skinner (Apress, 2005), is an excellent resource for in-depth coverage of CLR integration programming. This chapter discusses the methods used to extend SQL Server functionality in the past and explains the basics of the CLR integration programming model in SQL Server 2014.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 17. Data Services

Abstract

Today’s systems are disparate, and large enterprises have widely heterogeneous environments, with Windows and non-Windows platforms for application development. Developers, whether they’re enterprise developers, web developers, independent software vendor (ISV)) developers, or DBAs, have different needs and different ways of accessing the data that resides in SQL Server. For example, ISV developers look for stability in the platform, enterprise developers look for rich development tooling experience and interoperability, and web developers want the latest rich development experience. Similarly, what a PHP developer needs is very different from what a .NET developer needs. To achieve the rich development experience, developers can choose from various data access libraries such as ADO.NET, SQL Server 2014 Native Client (SNAC), JDBC, ODBC, and PHP, based on the application’s requirements. Since SQL Server 2000, the platform has supported interoperability with Windows and non-Windows environments. SQL Server 2000 started supporting Java development using JDBC drivers. PHP application development support was added to SQL Server with SQL Server 2005. With SQL Server 2014, support for ODBC driver for Linux has been added. This simplifies PHP or other application development on Linux to a greater extent.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 18. Error Handling and Dynamic SQL

Abstract

Prior to SQL Server 2005, error handling was limited almost exclusively to the @@error system function and the RAISERROR statement, or it was performed through client-side exception handling. T-SQL in SQL Server 2014 still provides access to these tools, but it also supports modern structured error handling similar to that offered by other high-level languages such as C++, C#, and Visual Basic. This chapter discusses legacy T-SQL error-handling functionality and the newer structured error-handling model in T-SQL. The chapter introduces tools useful for debugging server-side code, including T-SQL statements and the Visual Studio IDE.

Miguel Cebollero, Jay Natarajan, Michael Coles

Chapter 19. Performance Tuning

Abstract

In most production environments, database and server optimization have long been the domain of DBAs. This includes server settings, hardware optimizations, index creation and maintenance, and many other responsibilities. SQL developers, however, are responsible for ensuring that their queries perform optimally. SQL Server is truly a developer’s DBMS, and as a result the developer responsibilities can overlap with those of the DBA. This overlap includes recommending database design and indexing strategies, troubleshooting poorly performing queries, and making other performance-enhancement recommendations. This chapter discusses various tools and strategies for query optimization and performance enhancement and tuning queries.

Miguel Cebollero, Jay Natarajan, Michael Coles

Appendix A. Exercise Answers

Abstract

This appendix contains the answers to the exercises at the end of each chapter. The answers are grouped by chapter and numbered to match the associated exercises in the corresponding chapter.

Miguel Cebollero, Jay Natarajan, Michael Coles

Appendix B. XQuery Data Types

Abstract

SQL Server 2014 supports the data types defined in the XQuery Data Model (XDM). The supported data types are listed with their definitions in Table B-1. The diagram in Figure B-1 is a quick reference showing the relationships between the XDM data types.

Miguel Cebollero, Jay Natarajan, Michael Coles

Appendix C. Glossary

Abstract

An acronym for atomicity, consistency, isolation, durability. These four concepts of transactional data stores, including SQL databases, ensure data integrity.

Miguel Cebollero, Jay Natarajan, Michael Coles

Appendix D. SQLCMD Quick Reference

Abstract

SQLCMD is the standard text-based tool for executing batches of T-SQL on SQL Server. As a text-based tool, SQLCMD provides a lightweight but powerful tool for automating T-SQL batches. This appendix is designed as a quick reference to SQLCMD. The descriptions of many of the features and the functionality given here differ from BOL in some instances; the descriptions provided in this appendix are based on extensive testing of SQLCMD.

Miguel Cebollero, Jay Natarajan, Michael Coles

Backmatter