MS SQL Functions You Need to Know
Microsoft SQL Sever has a lot of functions and commands. Like a lot. Way more than one person can remember.
One of the problems I often face working in SQL Server is I tend to stick to the same, small, set of functions and use them over and over. By doing this, I limit myself from learning new and more efficient commands and ultimately hinder my ability to grow as an analyst in SQL.
This past weekend I attended the PassMN SQL Saturday gathering and had the privilege of hearing some very influential members of the data community speak on different topics. One such member was Thomas Grosher, a SQL Server Infrastructure Architect and Engineer who has over 26years of experience inside of SQL Server. The name of his session, titled “42 T-SQL Functions”, provided a look into some lesser known but high yield Functions. To share all 42 would be quite a long article, so for the sake of efficiency I’ll share the Functions I found to be the most helpful and that I plan to implement into my SQL usage.
Some of these you may already know, some you may not. The important thing in any profession that deals with data (or any profession in general really), is that there is always something new to learn and always ways to improve. It therefore becomes our duty to be stewards of professional growth.
With that said, lets get into it!
DB_NAME()
Have you ever found your self working in SQL Server and wondered if there’s an efficient way to print the name of the database you are working in inside a table? If so, DB_NAME has you covered. The syntax is simply DB_NAME()
Select
DB_NAME() AS [current Database]This function does exactly what it looks like it does. It returns the name of the database. However, the uses for this become quite powerful, especially when used within a stored procedure and can allow you to execute some dynamic SQL queries.
In the following query, I create a stored procedure titled “[mytestdb]” inside of the specified [AdventureWorks] database. We add input parameter ‘@db sysname’ which is set to NULL if no value is given. Since we do not need row numbers for our result set, we set NOCOUNT to ON. Next we declare a variable and set it equal to our select statement returning the name of the database. Now we declare one more variable to deal with potential nulls. For this we use COALESCE to check if the ‘db’ parameter is not null and uses that ‘db’ value. We then set the second value of our COALESCE function to return the DB_NAME in the event the ‘db’ parameter is NULL. Finally, we can execute both our variables to pass ‘@sql’ as a parameter to the dynamic statement.
--Using DB_NAME in a stored procedure
USE [AdventureWorks2022];
GO
CREATE PROCEDURE dbo.[mytestdb]
@db sysname = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(); /* other stuff */'
DECLARE @exec nvarchar(770) = COALESCE(@db, DB_NAME())
+ N'.sys.sp_executesql';
EXEC @exec @sql;
END
GOIn summation, this simple function not only has the power to execute dynamic queries, it has the power to specify a target database where that dynamic query should be executed.
DATEFROMPARTS
Have you ever been working with dates in SQL Server and needed to format them differently for a client in a different country ? Or perhaps you simply don’t like the format of your dates column is currently in? There are a few out there that can change this, another one to add to your tool belt is DATEFROMPARTS. This function returns a date in a specified format. The syntax is simply DateFromParts(). Simply add the date in whatever format you like and you are off. You are also able to add the hours, minutes and seconds if you so choose.
Select
DateFromParts(1992,09,06)The above query returns the date in the exact order it was entered with dash delimiters. So I know what you’re probably thinking, “isn’t this exactly what FORMAT is for?” and it’s exactly what I was thinking as well. But, almost as if he was able to read my mind he stated that from a speed and efficiency standpoint, DateFromParts is superior when compared to FORMAT.
DATETIME2
DATETIME2 is a rival of GETDATE but does things a little differently. A data type which specifies a date and time with fractional seconds, DATETIME2 is far more precise than GETDATE is when it comes to returning date and more specifically, time.
DECLARE @t as DATETIME2(7) = GETDATE()
PRINT @t
GO 100
Compare this to GETDATE…
Select GETDATE() as TimeDate
As we can see, there is a much higher degree of precision using DATETIME2 compared to GETDATE. So now lets say you want to use this function to create a table with a date column using DATETIME2. What would that look like?
CREATE TABLE TestTable
(
Date DATETIME2(7)
)
INSERT INTO TestTable (Date)
VALUES
(GETDATE())
SELECT
DATE
FROM TestTable
As you can see, it’s a standard process for creating a table and columns. We simply just add the DATETIME2 as our data type and set our value as todays date (GETDATE) and it returns it in the desired format. Now the next time you need an insanely specific date and time column, you’re all set!
STATS_DATE
Having the most accurate and up-to-date data is one of the most crucial aspects of working with databases and data in general. It therefore becomes paramount to know when the database you’re working with has last been updated. But how does one do this ? With a little function called STATS_DATE.
The syntax for this function is simply STATS_DATE (Object_id, Stats_id), where object_id is the table you wish to retrieve information on and Stats_id is the ID of the statistics object. In the following code we Select the name and stats_date from System Stats and provide a WHERE clause to specify the table ‘Person.Address’.
Additionally, this function not only returns the date of the most recent update for tables, it can also provide refresh times for things like indexed views as well.
USE AdventureWorks2022
GO
SELECT
Name as Stats_name,
STATS_DATE(Object_ID, Stats_ID) as stats_update_date
FROM sys.stats
WHERE object_id = OBJECT_ID('Person.Address');
This function provides an exceptionally quick and easy way to verify that you are working with the latest data for a specified table. Doing so ensures we arrive at the most accurate insights and help our stakeholders make the most informed decisions possible.
TYPE_NAME
When working with data it’s important to ensure that we use the correct data type for the correct type of data. But how do we know what the data type is? Sure we could go into Programmability → Types → System Data Types and get the data types, but that’s a lot of clicking and you can’t display it in a result set table. Enter TYPE_NAME.
The syntax for this function is TYPE_NAME(type_id) where type_id is the ID of the type that will be used. The following query returns the data types from a joined View where the object name is Vendor. To begin, we select the columns and use TYPE_NAME specifying User_Type_ID as our type_id and alias it as TYPE_NAME. We then join System Objects View to System Columns View on the similar column Object_id and add a WHERE clause to filter names equal to Vendor. Finally, we order the result set by the column name.
SELECT
O.NAME AS OBJ_NAME,
C.NAME AS COL_NAME,
TYPE_NAME(C.USER_TYPE_ID) AS TYPE_NAME
FROM SYS.OBJECTS AS O
JOIN SYS.COLUMNS AS C ON
O.OBJECT_ID = C.OBJECT_ID
WHERE O.NAME = 'Vendor'
ORDER BY COL_NAME
CONCAT_WS()
In the introduction to this article I talked about how I tend to use some functions over and over. Well CONCAT is one of those functions and on its own its very useful but too often I find myself typing on and on adding in separators like ‘ ‘, or ‘,’ to incredibly long lists of string. CONCAT_WS() is the answer to this problem I didn’t know I needed.
This function acts identical to CONCAT but simply adds the separator you wish to use and applies it to all items in the list, instead of you physically typing each separator in. This is has been a huge time saver for me and I can’t tell you how often I’ve been using this function since learning it.
SELECT
CONCAT_WS(',',1,2,3,4,5)
--comma is identified as seperator
--returns the list separated by commas 
STRING_AGG
But what if instead of connecting string together you want to separate it? Here we can use a function which called STRING_SPLIT which does exactly as its name suggests. The syntax is incredibly simple, being STRING_SPLIT(‘String’, separator, [optional ordinal number]). So the function accepts 3 arguments, the last is optional. The first argument is the string you wish to split, the second is what you want to separate them with (ie. space, comma, period etc.). Here’s an example,
SELECT
*
FROM STRING_SPLIT('WRITING CODE IN SQL IS REALLY FUN', ' ', 1)
Bearing somewhat of a resemblance to the process of unpivoting a table, by separating with a space we have taken the string and turned them into rows. This becomes an incredibly powerful function when used with the CROSS APPLY function.
CROSS APPLY
Lets say you have a table called TestTable with 2 columns, one is ID and the second is Product_Lists which contains strings of Product_ID’s. You need to split this string and place them into their own rows as Unique identifiers. This is a perfect time to use Cross Apply with String split.
Select
t.ID,
s.value as Product_ID
FROM TestTable t
CROSS APPLY STRING_SPLIT(Product_lists, ',') as sCross Apply STRING_SPLIT(o.ProductLists, ',') applies the STRING_SPLIT function to each row in the TestTable, splitting the Product Lists by commas.
- The result is a table with two columns: ID(from the Orders table) and Value (from the split result), where Value contains the individual Product IDs.
IIF
Have you ever found yourself needing to perform a conditional check for true or false but didn’t want to type out a whole CASE statement? Well then IIF is going to make your life easier. This function accepts 3 arguments, it evaluates the first, returns the second if it is true, otherwise it returns the third if it is false. This simple and concise function is especially useful when you need to perform a simple conditional check and return one of two values based on that condition. It is important to note that while IIF is quicker than typing out a CASE statement, you are limited to only 2 conditional checks.
Lets say I have a table with information about individual customers and one of the columns is Title which provides either ‘Mr.’ or ‘Ms.’. I am tasked with finding a way to create another column which records the gender o each customer. This is a perfect time to use IIF.
In the IIF statement, any column who’s Title is ‘Ms.’ should return ‘Female’ if this is true, and ‘Male’ if this is not true. Additionally, the level of complexity can increase exponentially for this function with the added ability to use nested IIF statements which involve setting another IIF statement as a condition for return.
SELECT
IIF([Title] = 'Ms.', 'Female', 'Male') as Gender
FROM
[Person].[Person]
CONCLUSION
In conclusion, there are many SQL functions out there and more get added every year. So knowing that, don’t continue to use the same ‘go to’ functions from your toolbelt. It’s worth taking the time to learn new functions and deviate from the standard practice. Doing so helps broaden our SQL skills, evoke change, and can even help us improve query efficiency. I hope this article helped you find at least one new function which you can implement into your SQL practice. Finally, I’d like to thank everyone that made PASSMN — Minnesota SQL Server, SQL Saturday happen! I learned an incredible amount and it was great to listen to some cornerstones of the data community speak.
