Lesson 1

Thus far we have used Transact-SQL to write queries that capture information in your database. This module covers ways in which you can use Transact-SQL to interact with your server in other ways.
I call it the programming module because it covers ways that you can integrate programming concepts into your Transact-SQL statements to give you greater control over your database.

SQL Server 2019 provides a range of built-in functions that you can utilize in order to extract, analyze and manipulate your database information. These functions can be grouped into several categories, including:

1. Scalar functions: These return a single value based on the input value. For example, mathematical functions (ABS, CEILING, FLOOR), string functions (LEFT, RIGHT, SUBSTRING), date and time functions (GETDATE, DATEADD, DATEDIFF), etc.
2. Aggregate functions: These return a single value, calculated from multiple rows of your table. Examples include AVG (for average), COUNT, SUM, MIN, MAX, etc.
3. Window functions: These provide computations across a set of table rows that are related to the current row. They include RANK, ROW_NUMBER, LEAD, LAG, etc.
4. System functions: These provide access to SQL Server operations, resources, and metadata. For instance, @@VERSION returns the current SQL Server version, and GETDATE() returns the current system date and time.

Let's see how you can use some of these functions in SQL Server 2019 operations:

Scalar functions can be used in SQL queries to transform or calculate data. Here's an example:

SELECT FirstName, LEFT(LastName, 1) as Initial
FROM Employees;

This query would return a list of all employees, showing their first name and the initial of their last name.

Aggregate functions can help summarize data. Here's an example:

SELECT COUNT(*) as TotalEmployees
FROM Employees;

This query would return the total number of employees in the Employees table.

Window functions are used to solve problems that need to compute something over a group of rows, potentially partitioned by some other values. Here's an example:

SELECT FirstName, LastName, 
       RANK() OVER(ORDER BY Salary DESC) as RankBySalary
FROM Employees;

This query would return a list of all employees, along with a rank based on their salary.

System functions can be used to return information about the current system status or user session. Here's an example:

SELECT @@VERSION as SQLServerVersion;

This query would return the current version of the SQL Server.
Remember to review the official SQL Server documentation for an extensive list of available functions and their usage. SQL Server 2019 has robust support for various functions that can simplify your data manipulation and analysis tasks, and understanding them well is an important aspect of effective SQL Server operations.

After completing this module, you will be able to:

1. Declare and use variables
2. Use functions to obtain information and perform SQL Server 2012 operations
3. Handle errors generated by SQL Server 2012
4. Program your code so that errors are raised to notify you of events

In the next lesson, declaration and use of variables in your Transact-SQL code will be discussed.

Well, you now know how to declare variables, but the question that follows is, How do you change their values?
There are three ways to set the value in a variable. You can initialize it in the DECLARE statement, use a SELECT statement, or use a SET statement. Functionally, SET and SELECT work almost the same, except that a SELECT statement can do a couple more things:

1. SELECT can assign a value from a column in the SELECT statement
2. SELECT can assign values to many variables in the same statement

So why have two ways of doing this? SELECT predates SET back in SQL Server history, so why go to the trouble to implement SET at all?
Suffice to say that SET is now part of the ANSI/ISO standard, and that is why it has been put in there. However, I cannot find anything wrong with the same functionality in SELECT, even ANSI/ISO seems to think that it is okay.