What has changed in SQL Server 2008 compared to T-SQL - In Developers' view

Views: 396
This post is the 1 part of 2 in the series. What has changed in T-SQL
Reading Time: 6 minutes

Hey guys!
All right ?

In today's post, I will share with you some research I have been doing for some time about the new features of SQL Server with each release, focusing on query developers and database routines. In the environments I work with, I find that many end up “reinventing the wheel” or creating UDF functions to perform certain tasks (which we know are bad for performance) when SQL Server itself already provides native solutions to it.

My goal in this post is to help you, who are using older versions of SQL Server, assess what advantages and new features (developer view only) you will have access to when upgrading your SQL Server.

My initial idea was to create a post with just about everything that changed from 2008 to 2019, but after chatting and getting some feedback from great professionals that I consider, like Edvaldo Castro, Nilton Pinheiro, Caio Lover and Ariel fernandez, I will, for the first time in the blog, start a series of posts, so that the article does not get too big and tiring to read ..

Therefore, I start the series "What has changed in SQL Server X compared to T-SQL - In the Developers' view", starting with SQL Server 2008 and going to post a post for each version until 2019. After all the articles published, I will create an article to centralize this whole series.

SQL Server - What has changed in T-SQL in the 2008 version?

New data types: date, time, datetime2, datetimeoffset

View content
In the 2008 release, SQL Server introduces us with new data types to handle with date and time. Prior to SQL Server 2008, there was the DATETIME field, but for working with hours there was no specific data type for it.

To take a DATETIME field and return only the date part, without the time, one of the solutions (and quite common) was using the CFC (Cast, Floor, Cast) technique:

This is much simpler today using the DATE type:

And I will demonstrate a little about the other data types:

New data types: data, time, datetime2, datetimeoffset.

Data types in table format

View content
Resources available since SQL Server 2008, we can now declare types with table structures and use as parameters in SP's and functions, as example below:

Result:

To learn more about this, visit the post. SQL Server - How to Pass a Table as a Parameter for Stored Procedures and Functions.

Assign values ​​during variable declaration

View content
Starting with SQL Server 2008, you can assign values ​​already in the variable declaration, making it easier and reducing the number of lines of code you need.

Examples of use:

Note: This functionality is not applied to TEXT, NTEXT and IMAGE variables.

Grouping data with GROUPING SETs

View content
A very well-received feature from SQL Server 2008 is the use of GROUPING SETS, CUBE, and ROLLUP to simply create data groupings, totals, and subtotals with few changes to your original query.

Usage example - ROLLUP

Usage example - CUBE

Usage example - GROUPING SETS

Want to know more about this feature? Be sure to access the post SQL Server - Grouping Data Using ROLLUP, CUBE, and GROUPING SETS.

Using MERGE for INSERT, DELETE, and UPDATE with just 1 Command

View content
From SQL Server 2008, you can now use the MERGE command for INSERT, DELETE, and UPDATE with only 1 SQL statement. Its operation is simple: One or more columns of the tables involved are considered keys (identifiers) so if the key value exists in the target table, the values ​​will be updated according to the source table. If this identifier does not exist, this record will be inserted into the target table.

Example of use:

Want to learn more about the MERGE command and see several other examples, including stpETL_Upsert, which automatically creates MERGE commands for you? Be sure to visit my post SQL Server - How to use MERGE command to enter, update and delete data with 1 command only.

Multiple INSERT's with 1 Single Command

View content
From SQL Server 2008, you can now enter multiple values ​​in 1 single INSERT command with VALUES. Although it was already possible to do this using INSERT… SELECT, this feature helps a lot in everyday life.

Example of use:

Remember the maximum amount of values ​​that can be entered in a single INSERT is 1.000 records.

Using compound value assignment operators

View content
From SQL Server 2008, we can use compound value assignment operators, which are these:

  • + = Add and assign value
  • - = Substrair and assign value
  • * = Multiply and assign value
  • / = Split and assign value
  • % = Module and assign value
  • & = Bitwise AND and assign value
  • ^ = Bitwise XOR and assign value
  • | = Bitwise OR and assign value

Examples of use:

Spatial data types

View content
SQL Server 2008 introduced spatial datatypes into the DBMS, which allows us to represent the physical location or shape of any geometry using only T-SQL. We can use this kind of data to represent countries, streets, cities, etc. These data types are implemented using the .NET Common Language Runtime (CLR).

Example 1:

Result

2 Example

Result

Available methods:

  • STLength
  • STStartPoint
  • STEndPoint
  • STPointN
  • STNumPoints
  • STIsSimple
  • STIsClosed
  • STIsRing

I hope you enjoy this theme, the series I am starting and leave your doubts in the comments.
Hug!