Analytic SQL in SQL Server 2014/2016

4Functions

4.1. Predefined functions

4.1.1. Introduction

The SQL language is a stable language that has not had any major changes in its kernel (its commands) since its invention. This does not mean that SQL is a stagnant language, since it has had multiple enhancements through the integration of new functions capable of being used with SQL commands, mainly the SELECT command.

The dynamism of SQL is also due to the possibility of creating user functions using Transact SQL (TSQL). This procedural add-on to almost all DBMSs is a procedural programming environment designed primarily for server-side development to ensure greater integrity, optimization and security when accessing the database.

Functions with their two variants (predefined and user) play an important role in data analysis.

4.1.2. Mind map of the first section

This first section will be devoted to presenting and classifying the predefined functions of SQL Server, as well as introducing the user-defined functions.

Figure 4.1 presents the mind map of the different concepts that will be covered in this section.

Figure 4.1. Mind map of the first section

4.1.3. Scalar built-in functions

SQL Server has a very rich set of predefined functions referred to as built-in functions. These functions can be classified into different categories, mainly scalar functions, aggregation functions and windowing functions.

This ...