What has changed in SQL Server 2012 in relation to T-SQL - In the view of Developers

A very common need in application development is the use of data paging in SQL Server, so that only a limited amount of records are returned by queries, causing less data to be processed on exit and queries to have a response time. smaller. This scenario, which is quite common in applications that often paginate data on screen, both to avoid information overload and to shorten the time required to load information.

With the advent of ROW_NUMBER () in SQL Server 2005, many people started using this function to create data pagination, working like this:

Result:

However, from SQL Server 2012 we have the native paging functionality in SQL Server itself which many people end up not using unknowingly. We're talking about OFFSET and FETCH, which work together to allow us to page our results before displaying and sending them to applications and customers.

See with its use is simple:

Result:

If you would like to know more about this feature, be sure to visit my article SQL Server - How to create data pagination in query results with OFFSET and FETCH

Old known to Oracle DBA's, the Sequence feature was introduced in SQL Server 2012 and consists of creating sequences (similar to IDENTITY) not associated with a specific object, that is, you can create a sequence for a specific table or you can use a sequence for more than one table so that ID numbers will not be repeated between the tables involved.

Classic example: Tables Physical_Person and Legal_Person. If you use IDENTITY to generate a sequence, the 2 tables will have a record with ID = 25, for example. If you use a single sequence to control the ID of these two tables, ID = 25 will only exist in one of two tables, working like this:

Test Source Code

Result

If you would like to know more about Sequences in SQL Server, read my article. SQL Server 2012 - Working with Sequences and IDENTITY Comparisons.

Every good developer of query and bank routines uses error and exception handling in their T-SQL codes. Starting with SQL Server 2005, Microsoft introduced the feature of TRY ... CATCH in T-SQL, where we could generate exceptions using the command RAISEERROR. Beginning with SQL Server 2012, the new DBMS method was incorporated into the DBMS. THROW, which makes it easier and simpler to generate exceptions in the database.

Zero division treatment using RAISEERROR

Result:

Zero division treatment using THROW

Result:

Much simpler right? And it doesn't stop there. To use custom messages, THROW is also much simpler because the error code of the message (error_number) does not have to be registered in sys.messages.

Another advantage of THROW is that you can output error messages returned by THROW to a history table (using RAISEERROR you cannot capture the command return, but you can also create this history by inserting records into the table and capturing messages using ERROR_% functions, such as ERROR_MESSAGE ())

To learn more about the benefits of THROW, be sure to check out the article Applying error handling on microsoft sql server 2012 and 2014 - using throw command.

Well known to programmers, the IIF function was introduced in SQL Server 2012 and its goal is to simplify simple logical operations, which have only possible 2 results.

Simple Example with CASE

Simple Example with IIF

Much simpler, right? However, if you need several results, I recommend continuing to use CASE, as the IIF ends up being very complex to maintain when it is nested with several other IIF's.

Another function available from SQL Server 2012 is the CHOOSE logic function. This function allows you to retrieve a value based on the numeric position (index) of a list of values. This function can be used to simplify some scenarios in which you would have to place multiple CASE conditions.

Example of use:

Result:

Another example, now retrieving the index from the result of a function:

Result:

Examples using off-list indexes and decimal indexes

Result:

As we can see above, when the index of the CHOOSE function is not in the list range, the function will return NULL. And when we use an index with decimal places, the index will be converted (truncated) to an integer.

Note: Internally, CHOOSE is a shortcut to CASE, so 2 has the same performance for performance.

Quite commonly used functions of Query developers, data type conversion functions have greatly evolved in SQL Server 2012 with the new PARSE, TRY_PARSE, TRY_CONVERT, and TRY_CAST functions.

The function PARSE (available for dates and numbers) is very useful for conversions of some non-standard formats that CAST and CONVERT can't convert, like the example below:

Result:

As evolution of the PARSE function, we have the function TRY_PARSE, which has basically the same behavior as the PARSE function, but with the differential that when conversion is not possible, instead of returning an exception during processing, it will only return NULL.

Example:

Same thing with functions TRY_CONVERT e TRY_CAST, which have the same behavior as the original functions, but do not generate exception when unable to perform a conversion.

Examples of use:

Result:

To learn more about data processing and conversions, see my article. SQL Server - How to identify data conversion errors using TRY_CAST, TRY_CONVERT, TRY_PARSE, ISNUMERIC, and ISDATE.

Very interesting function for those who work with BI and calculations involving dates and month end. Basically, this function returns the last day of the month from the entered date.

Examples of use

Result

Starting with SQL Server 2012, we have new 6 functions for generating integer dates, which was pretty annoying to do in previous versions of SQL Server:

• DATEFROMPARTS (year, month, day)
• DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
• DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
• DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
• SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
• TIMEFROMPARTS (hour, minute, seconds, fractions, precision)

Script for test data generation:

Example of the DATEFROMPARTS function and how we did it before SQL Server 2012:

Result:

Example Using 6 Functions Together

Result:

One of the most useful functions in everyday life for creating BI queries and routines, the FORMAT lets you apply custom masks to dates and numbers, much like the C # string.ToString () method.

Some examples of the use of FORMAT - Numbers

Result

Other examples with numbers:

Result:

Filling number with leading zeros:

Some examples of using FORMAT - Dates

Result:

Custom Date Formatting:

Result:

If you want to know more about the FORMAT function, read my article. SQL Server - Using the FORMAT Function to Apply Masks and Formatting to Numbers and Dates.

Available from SQL Server 2012, the function CONCAT allows you to concatenate variables, strings and / or columns in a more practical way, which I will demonstrate its advantages below:

Data generation for examples

Examples

Result:

As you can see, the CONCAT function automatically converts data types to varchar and still handles NULL values ​​and converts them to empty strings. That is, simpler, CONCAT is, but what about performance? What is it like?

At least in the tests I did, CONCAT proved to be faster than the traditional concatenation (using “+”). Simpler and faster.

Very interesting analytical functions, the function FIRST_VALUE Returns the first value of a resultset, where you can define an optional break (PARTITION BY) and a rule to order the results (ORDER BY). Already the function LAST_VALUE, does the opposite, returning the last value from this data set.

T-SQL script for data creation for example:

Example 1 - Identifying the older and younger age and the name of these people

Result:

Well, the code just got a lot simpler and cleaner. Speaking of performance, there are several ways to achieve this, with more performative queries than the one I used in the first example, but I wanted to put the code as simple as possible. If you really use this type of programming in your code, it's good to review your queries, as they perform very poorly.

Here's what the first query and query execution plan looks like using the FIRST_VALUE function:

And now let's use the LAST_VALUE function to return the largest dataset records:

Result:

Ué .. The function LAST_VALUE did not bring the last value, but the value of the current line .. This happens due to the concept of frame. The frame allows you to specify a set of lines for the “window”, which is even smaller than the partition. The standard frame contains lines that start with the first line and up to the current line. For line 1, the “window” is just line 1. For line 3, the window contains lines 1 through 3. When using FIRST_VALUE, the first line is included by default, so you don't have to worry about that for get the expected results.

In order for the LAST_VALUE function to actually return the last value of the entire dataset, we will use the ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING parameters together with the LAST_VALUE function, which causes the window to start at the current line to the last line of the data. partition.

New script using the LAST_VALUE function:

Result:

Starting with SQL Server 2012, we have two more analytic functions available to access the record prior to a record (LAG) and subsequent registration (LEAD).

T-SQL script to generate test data:

Imagine that I want to create a pointer and access who is the next ID and the ID prior to the current record. Prior to SQL Server 2008, we would need to create self-joins to complete this task. From SQL Server 2012, we can use the LAG and LEAD functions for this need:

Result:

Much simpler and cleaner code. Let's look at the performance of both queries now:

Yeah, although LAG and LEAD are a lot simpler and more readable, their performance turns out to be a little under Self-Joins, probably due to the ordering that is done to apply the functions. I'm going to insert a larger volume of records (3 million records) to see if performance is still worse than Self-Joins, which I don't think makes sense, since Self-Join does a lot of table reading and functions, In theory, they should only do 1.

Test results with 3 million records (surprised me):

Implementation plan with SELF-JOIN:

LED and LEAD execution plan:

I figured that because of a simpler plan and lower table readings, the functions would perform much better than self-joins, but because of a very heavy SORT operator, the performance using the functions turned out to be worse as we can note in the execution plan and warning of the sort operator:

In short, if you are working with small data sets, you can use the LAG and LEAD functions quietly, as the code is more readable. If you need to use this on large volumes of data, test self-join to gauge how your performance will look in your scenario.

A scenario that is somewhat common in data professionals' day to day, capturing data returned by Stored Procedures can end up being a little work when data type conversions are required. In this case, you must use an intermediate table to perform the data conversion and then use the required data.

Starting from SQL Server 2012, we can now use the WITH RESULT SETS clause to execute Stored Procedures, so that we can change the name and type of fields returned by Stored Procedures, very simply and practically.

Example 1:

Result:

If the Stored Procedure returns more than one dataset, you can use the WITH RESULT SETS clause like this:

Result:

A new feature that has been added to SQL Server 2012 is the ability to return x% of data from a table using the TOP command. This is very useful when you want to return a measured sample of data as a percentage.

Its use is practically the same as the traditional and already known TOP:

Result:

Until the 2008 version, SQL Server had 2 functions for calculations with logarithms: LOG (default base) and LOG10. If you needed to use a custom basis in your mathematical calculations, you would have to implement the calculation manually.

Starting with SQL Server 2012, the LOG function now has an optional second parameter, where you can enter which base you want to use in your logarithm calculation:
Example: