You probably already know that the most important SQL statement is the mighty SELECT statement you use to retrieve data from your databases. Every database developer knows the basic clauses and their usage:

• SELECT to define the columns returned, or a projection of all table columns
• FROM to list the tables used in the query and how they are associated, or joined
• WHERE to filter the data to return only the rows that satisfy the condition in the predicate
• GROUP BY to define the groups over which the data is aggregated
• HAVING to filter the data after the grouping with conditions that refer to aggregations
• ORDER BY to sort the rows returned to the client application

Besides these basic clauses, SELECT offers a variety of advanced possibilities as well. These advanced techniques are unfortunately less exploited by developers, although they are really...

As a developer, you are often also responsible for creating the database objects. Of course, in an application, you need also to insert, update, and delete the data. In order to maintain data integrity, meaning enforcing data to comply with business rules, you need to implement constraints. In our quick review of the data definition language (DDL) and data modification language (DML) elements, we will look at the following statements:

• CREATE for creating tables and programmatic objects
• ALTER to add constraints to a table
• DROP to drop an object
• INSERT to insert new data
• UPDATE to change existing data
• DELETE to delete the data

In a SQL Server database, you can also use programmatic objects. You can use triggers for advanced constraints or to maintain some redundant data such as aggregated data. You can use other programmatic objects for data...

In a real-world application, errors always appear. Syntax or even logical errors can be in the code, the database design might be incorrect, there might even be a bug in the database management system you are using. Even if everything works correctly, you might get an error because the users insert wrong data. With Transact-SQL error handling you can catch such user errors and decide what to do upon them. Typically, you want to log the errors, inform the users about the errors, and sometimes even correct them in the error handling code.

Error handling for user errors works on the statement level. If you send SQL Server a batch of two or more statements and the error is in the last statement, the previous statements execute successfully. This might not be what you desire. Often, you need to execute a batch of statements as a unit, and fail all of...

Beyond relational is actually only a marketing term. The relational model, used in the relational database management system, is nowhere limited to specific data types, or specific languages only. However, with the term beyond relational, we typically mean using specialized and complex data types that might include spatial and temporal data, and XML or JSON data, and extending the capabilities of the Transact-SQL language with CLR languages like Visual C#, or statistical languages like R. SQL Server, in versions before 2016, already supports some of the features mentioned. Here is a quick review of this support, which includes:

• Spatial data
• CLR support
• XML data

In modern applications, often...

In this chapter, we reviewed the SQL Server features for developers that already exist in the previous versions. You can see that this support goes well beyond basic SQL statements, and also beyond pure Transact-SQL.

The features recapitulation in this chapter is also useful as an introduction to new features in SQL Server 2016 and 2017, introduced in the following chapters, especially in Chapter 4, Transact-SQL and Database Engine Enhancement; Chapter 5, JSON Support in SQL Server; Chapter 7, Temporal Tables; and Chapter 13, Supporting R in SQL Server.