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
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.
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.
T-SQL Related Interview Questions
|PL/SQL Interview Questions||MSBI Interview Questions|
|SQL Database Interview Questions||MySQL Interview Questions|
|DB2 Using SQL Interview Questions||SQL DBA Interview Questions|
|SQL Interview Questions||PL/SQL and Informatica Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||MYSQL DBA Interview Questions|
|PL/I Interview Questions||DB2 SQL Programming Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||NoSQL Interview Questions|
|SQL Server Architect Interview Questions|
T-SQL Related Practice Tests
|PL/SQL Practice Tests||MSBI Practice Tests|
|SQL Database Practice Tests||MySQL Practice Tests|
|DB2 Using SQL Practice Tests||SQL DBA Practice Tests|
|SQL Practice Tests||SSIS(SQL Server Integration Services) Practice Tests|
|MYSQL DBA Practice Tests||PL/I Practice Tests|
|DB2 SQL Programming Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.