System Functions and Variables - T-SQL

This section discusses utility functions used to perform a variety of tasks. These include value comparisons and value type testing. This category is also a catch - all for other functionality

System Functions and Variables

System Functions and Variables

Some examples related to a few of the functions listed in the preceding table follow.

The COALESCE() Function

The COALESCE() function can be very useful in returning the first non-null value from a list of arguments, saving quite a lot of IF or CASE decision logic. The following example populates a table of products, showing up to three prices each:

All products have a list price, some have a sale price, and others may have a super sale price. The current price of a product is going to be the lowest existing price, or the first non-null value when reading each of the price columns as they are listed:

This method is far more elegant than using multiple lines of branching and decision logic, and the result is equally simple, as illustrated in Figure Show In Below.

System Functions and Variables

The DATALENGTH() Function

The DATALENGTH() function returns the number of bytes used to manage a value. This can be used to reveal some interesting differences between data types. It's probably no surprise that when a varchar type is passed to both the DATALENGTH() and LEN() functions, they return the same value:

These statements both return 3 because the varchar type uses three single-byte characters to store the three-character value. However, if an nvarchar type is used, it takes twice as many bytes to manage a value of the same length:

The DATALENGTH() function returns 6 because 2 bytes are used to store each character using a Unicode character set. The LEN() function returns 3 because this function returns the number of characters, not the number of bytes. Here's an interesting test. How many bytes does it take to store an integer variable set to the value 2? How about an integer variable set to 2 billion? Let's find out:

The DATALENGTH() function returns 4 in both cases because the int type always uses 4 bytes, regardless of the value. The LEN() function essentially treats the integer value as if it were converted to a character type, returning the number of digits, in this case, 1 and 10, respectively.

The following global system variables all return an int type. These may be useful in stored procedures and other programming objects to implement custom business logic.


All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

T-SQL Topics