Before delving into the new features in SQL Server 2016 and 2017, let's have a quick recapitulation of the SQL Server features for developers that are already available in the previous versions of SQL Server. Please note that this chapter is not a comprehensive development guide; covering all features would be out of the scope of this book. Recapitulating the most important features will help you remember what you already have in your development toolbox, and also understand the need for and the benefits of the new or improved features in SQL Server 2016 and 2017.

The recapitulation starts with the mighty T-SQL SELECT statement. Besides the basic clauses, advanced techniques such as window functions, common table expressions, and the APPLY operator are explained. Then you will pass quickly through creating and altering database objects, including tables and programmable objects, such as triggers, views, user-defined functions, and stored procedures. You will also review the data modification language statements. Of course, errors might appear, so you have to know how to handle them. In addition, data integrity rules might require that two or more statements are executed as an atomic, indivisible block. You can achieve this with the help of transactions.

The last section of this chapter deals with parts of the SQL Server Database Engine that is marketed with the common name Beyond Relational. This is nothing beyond the relational model; beyond relational is really just a marketing term. Nevertheless, you will review how SQL Server supports spatial data, how you can enhance the T-SQL language with Common Language Runtime (CLR) elements written in some .NET language such as Visual C#, and how SQL Server supports XML data.

The code in this chapter uses the WideWorldImportersDW demo database. In order to test the code, this database must be present in your SQL Server instance you are using for testing, and you must also have SQL Server Management Studio (SSMS) as the client tool.

This chapter will cover the following points:

• Core Transact-SQL SELECT statement elements
• Advanced SELECT techniques
• Data definition language statements
• Data modification language statements
• Triggers
• Data abstraction—views, functions, and stored procedures
• Error handling
• Using transactions
• Spatial data
• CLR integration
• XML support in SQL Server