{"id":2194,"date":"2016-03-31T00:00:00","date_gmt":"2016-03-31T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-system-functions-the-basics\/"},"modified":"2021-09-29T16:21:21","modified_gmt":"2021-09-29T16:21:21","slug":"sql-server-system-functions-the-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-system-functions-the-basics\/","title":{"rendered":"SQL Server System Functions: The Basics"},"content":{"rendered":"
\n

SQL Server provides a set of system functions for working with database objects, settings, and values. You can use the functions to carry out a variety of tasks, such as generating sequential GUIDs, calculating checksums, or returning identity values. You can also perform such operations as viewing connection properties, retrieving information about your transactions, or returning error messages.<\/p>\n

In this article, I walk you through a number of examples that demonstrate how to use system functions. Some of the examples rely on the AdventureWorks2014<\/code><\/b> database, but you can use a different database to run most of the statements, modifying the T-SQL where necessary. I don’t cover every system function, but I touch upon the majority of them. You can find information about all system functions in the MSDN topic System Functions (Transact-SQL)<\/a>.<\/p>\n

Identifier functions<\/h1>\n

The system functions include several for identifying the connecting environment or the data itself. For example, SQL Server provides the NEWSEQUENTIALID<\/code><\/b> function for generating sequential GUIDs, the HOST_ID<\/code><\/b> function for returning the identifier of the connecting computer, and the HOST<\/code><\/b>_NAME<\/code><\/b> function for returning the computer’s name. The following CREATE<\/code><\/b> TABLE<\/code><\/b> statement uses all three functions to provide default values for columns in the dbo.Products<\/code><\/b> table:<\/p>\n

IF OBJECT_ID (N'dbo.Products', N'U') IS NOT NULL\r\nDROP TABLE dbo.Products;\r\nGO\r\n\u00a0\r\nCREATE TABLE dbo.Products\r\n(\r\n\u00a0 ProdID INT PRIMARY KEY IDENTITY(101, 1),\r\n\u00a0 ProdAltID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),\r\n\u00a0 ProdName NVARCHAR(50) NOT NULL,\r\n\u00a0 AppPID CHAR(10) NOT NULL DEFAULT HOST_ID(),\r\n\u00a0 Workstation NVARCHAR(128) NOT NULL DEFAULT HOST_NAME()\r\n);\r\n<\/pre>\n
Each time the  NEWSEQUENTIALID<\/code><\/b> function runs, it creates a GUID greater than any previously created since Windows first started. Sequential GUIDs can result in faster operations than those generated with the  NEWID<\/code><\/b> function. If you restart the server, the  NEWSEQUENTIALID<\/code><\/b> function will start with a lower value, but still create unique GUIDs in sequential order.<\/p>\n
The workstation identifier returned by the  HOST_ID<\/code><\/b> function is actually the process ID (PID) of the application on the client computer that is connecting to SQL Server. The  HOST_NAME<\/code><\/b> function returns the workstation name provided by the client application, which means the name is not necessarily reliable.<\/p>\n
In practice, you probably won’t want to include information such as the host ID and name in the same table as the product information, choosing instead to log this data elsewhere. For example, you could create an INSERT<\/code><\/b> trigger that sends the data to an audit table. For now, however, the example shown here provides a simply way to demonstrate how easy it is to capture this information.<\/b><\/p>\n
Another system function that falls into the identifier category is @@IDENTITY<\/code><\/b>, which returns the last identity value inserted into a table. For example, suppose we create a stored procedure for adding data to the  Products<\/code><\/b> table:<\/p>\n
IF OBJECT_ID (N'dbo.InsertProduct', N'P') IS NOT NULL\r\nDROP PROCEDURE dbo.InsertProduct;\r\nGO\r\n\u00a0\r\nCREATE PROCEDURE dbo.InsertProduct\r\n\u00a0 (@ProdName VARCHAR(50) = NULL)\r\nAS\r\nIF @ProdName IS NOT NULL\r\nBEGIN\r\n\u00a0 INSERT INTO dbo.Products(ProdName)\r\n\u00a0 VALUES(@ProdName);\r\n\u00a0 SELECT @@IDENTITY;\r\nEND\r\nGO\r\n<\/pre>\n
The last step within the procedure is to return the identity value of the inserted row. As a result, when we run the following statement for the first time, it returns the value  101<\/code><\/b>:<\/p>\n
EXEC InsertProduct 'widget1';\r\n<\/pre>\n
We can use the returned value to log our insert operation or perform other tasks that rely on the identity value. In the meantime, let’s retrieve the row of data we just inserted:<\/p>\n
SELECT * FROM dbo.Products;\r\n<\/pre>\n
As expected, we find that out  ProdID<\/code><\/b> value matches the value returned by the  EXEC<\/code><\/b> statement:<\/p>\n\n\n\n\n
\n
 ProdID<\/b><\/p>\n<\/td>\n
\n
 ProdAltID<\/b><\/p>\n<\/td>\n
\n
 ProdName<\/b><\/p>\n<\/td>\n
\n
 AppPID<\/b><\/p>\n<\/td>\n
\n
 Workstation<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
101<\/p>\n<\/td>\n
\n
E0AAAB89-79E8-E511-B4C5-001C429524EA<\/p>\n<\/td>\n
\n
widget1<\/p>\n<\/td>\n
\n
5764\u00a0\u00a0\u00a0\u00a0\u00a0<\/p>\n<\/td>\n
\n
WKSTN9263VM<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Notice that the row also includes the data generated by the NEWSEQUENTIALID<\/code><\/b>,  HOST_ID<\/code><\/b>, and HOST<\/code><\/b>_NAME<\/code><\/b> functions, as well as the product name itself.<\/p>\n
As well as @@identity<\/code> that provides the last identity generated in the current session, we should just mention that there are two other functions; SCOPE_IDENTITY()<\/code> that provides the last identity value generated in the current session for just the current scope and IDENT_CURRENT(Tablename)<\/code> that returns the last identity value generated for a particular table .<\/p>\n
Data-specific functions<\/h1>\n
SQL Server’s system functions also include functions for working with specific types of data. For example, you can use the  ISNUMERIC<\/code><\/b> function to determine whether an expression is a valid numeric type, and you can use the  ISNULL<\/code><\/b> function to replace NULL<\/code><\/b> with a specified value.<\/p>\n
The following example creates the  Orders<\/code><\/b> table and the  InsertOrder<\/code><\/b> stored procedure, which incorporates both functions into its definition:<\/p>\n
IF OBJECT_ID (N'dbo.Orders', N'U') IS NOT NULL\r\nDROP TABLE dbo.Orders;\r\nGO\r\n\u00a0\r\nCREATE TABLE dbo.Orders\r\n(\r\n\u00a0 OrderID INT PRIMARY KEY IDENTITY(101, 1),\r\n\u00a0 OrderDate DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),\r\n\u00a0 ShipToZip INT NOT NULL,\r\n\u00a0 SalesPerson NVARCHAR(25) \r\n);\r\n\u00a0\r\n\u00a0\r\nIF OBJECT_ID (N'dbo.InsertOrder', N'P') IS NOT NULL\r\nDROP PROCEDURE dbo.InsertOrder;\r\nGO\r\n\u00a0\r\nCREATE PROCEDURE dbo.InsertOrder\r\n\u00a0 (@ShipToZip VARCHAR(15) = NULL, @SalesPerson VARCHAR(25) = NULL)\r\nAS\r\nIF @ShipToZip IS NOT NULL\r\nBEGIN\r\n\u00a0 IF ISNUMERIC(@ShipToZip) = 1\r\n\u00a0\u00a0\u00a0 INSERT INTO dbo.Orders(ShipToZip, SalesPerson)\r\n\u00a0\u00a0\u00a0 VALUES(@ShipToZip, ISNULL(@SalesPerson, 'online'));\r\n\u00a0 ELSE\r\n\u00a0\u00a0\u00a0 PRINT N'You must provide a valid five-digit US zip code.';\r\nEND\r\nGO\r\n<\/pre>\n
The  ISNUMERIC<\/code><\/b> function tests whether the @ShipToZip<\/code><\/b> input parameter is a valid numeric value. The function returns  1<\/code><\/b> if the value is valid, otherwise returns  0<\/code><\/b>. Chances are, you’ll want to use a regular expression or some other device to verify the input parameter, in addition to the application verification, but this example is enough to demonstrate the  ISNUMERIC<\/code><\/b> function. If the input value is numeric, the  INSERT<\/code><\/b> statement will run.<\/p>\n
If the statement does run, it also uses the  ISNULL<\/code><\/b> function to replace the value of the  @SalesPerson<\/code><\/b> input parameter if that value is  NULL<\/code><\/b>. For example, suppose we run the following  EXEC<\/code><\/b> and  SELECT<\/code><\/b> statements:<\/p>\n
EXEC InsertOrder '90210';\r\nSELECT OrderID, ShipToZip, SalesPerson FROM dbo.Orders;\r\n<\/pre>\n
Because we do not pass in a value for the  @SalesPerson<\/code><\/b> parameter, the  SELECT<\/code><\/b> statement returns the following results:<\/p>\n\n\n\n\n
\n
 OrderID<\/b><\/p>\n<\/td>\n
\n
 ShipToZip<\/b><\/p>\n<\/td>\n
\n
 SalesPerson<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
101<\/p>\n<\/td>\n
\n
90210<\/p>\n<\/td>\n
\n
online<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
If we do provide a value for the  @SalesPerson<\/code><\/b> parameter:<\/p>\n
EXEC InsertOrder '90210', 'Jeff';\r\nSELECT OrderID, ShipToZip, SalesPerson FROM dbo.Orders;\r\n<\/pre>\n
We get the results shown in the second row of the following table:<\/p>\n\n\n\n\n\n
\n
 OrderID<\/b><\/p>\n<\/td>\n
\n
 ShipToZip<\/b><\/p>\n<\/td>\n
\n
 SalesPerson<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
101<\/p>\n<\/td>\n
\n
90210<\/p>\n<\/td>\n
\n
online<\/p>\n<\/td>\n<\/tr>\n
\n
102<\/p>\n<\/td>\n
\n
90210<\/p>\n<\/td>\n
\n
Jeff<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Now suppose we pass in a postal code from a district in London, England:<\/p>\n
EXEC InsertOrder 'SW6 SBY';\r\nSELECT OrderID, ShipToZip, SalesPerson FROM dbo.Orders;\r\n<\/pre>\n
This time the  ISNUMERIC<\/code><\/b> function returns a  0<\/code><\/b>, which results in the procedure’s ELSE<\/code><\/b> statement being executed and returning the following results:<\/p>\n
You must provide a valid five-digit US zip code.\r\n<\/pre>\n
As the example demonstrates, the  ISNUMERIC<\/code><\/b> function is very simple to use, but know that the function is anything but simple and can return unexpected results. For a discussion of the ISNUMERIC<\/code><\/b> pitfalls, see the Simple-Talk article  How to Get SQL Server Data-Conversion Horribly Wrong<\/a>. The gist of this discussion is that you’re usually better off using the TRY_CONVERT<\/code><\/b> function if you’re running SQL Server 2012 or later:<\/p>\n
ALTER PROCEDURE dbo.InsertOrder\r\n\u00a0 (@ShipToZip VARCHAR(15) = NULL, @SalesPerson VARCHAR(25) = NULL)\r\nAS\r\nIF @ShipToZip IS NOT NULL\r\nBEGIN\r\n\u00a0 IF TRY_CONVERT(INT, @ShipToZip) IS NOT NULL\r\n\u00a0\u00a0\u00a0 INSERT INTO dbo.Orders(ShipToZip, SalesPerson)\r\n\u00a0\u00a0\u00a0 VALUES(@ShipToZip, ISNULL(@SalesPerson, 'online'));\r\n\u00a0 ELSE\r\n\u00a0\u00a0\u00a0 PRINT N'You must provide a valid five-digit US zip code.';\r\nEND\r\nGO\r\n<\/pre>\n
Another system function you might find useful (or at least entertaining) is  GETANSINULL<\/code><\/b>, which returns the database’s default nullability for the current session. For example, the following  SELECT<\/code><\/b> statement returns the default nullability for the  AdventureWorks2014<\/code><\/b> database:<\/p>\n
SELECT GETANSINULL('AdventureWorks2014');\r\n<\/pre>\n
In this case, the statement returns  1<\/code><\/b>, which means the database allows  NULL<\/code><\/b> values in the current session, unless a column is specifically defined as  NOT<\/code><\/b>  NULL<\/code><\/b>. As interesting as this might appear, the  GETANSINULL<\/code><\/b> function does not seem to be one that is commonly implemented, but I suppose you can use it to check a database’s nullability before carrying out an operation.<\/p>\n
SQL Server also provides the  CHECKSUM<\/code><\/b> and  BINARY_CHECKSUM<\/code><\/b> system functions for returning the checksum value over a row or list of expressions. This can be handy when you want to create a hash index based on one or more columns in a table. Some developers also use the functions to compare tables to determine whether values have changed, but the results are unreliable enough that even the SQL Server documentation warns against using  CHECKSUM<\/code><\/b> in this way:<\/p>\n
\n
‘However, there is a small chance that the checksum will not change. For this reason, we do not recommend using  CHECKSUM<\/code><\/b> to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using  HashBytes<\/code><\/b> instead. When an MD5 hash algorithm is specified, the probability of  HashBytes<\/code><\/b> returning the same result for two different inputs is much lower than that of  CHECKSUM<\/code><\/b>.’<\/p>\n<\/blockquote>\n
Microsoft is not as clear about the  BINARY_CHECKSUM<\/code><\/b> function in this regard, so let’s dig into both of the functions a bit more.<\/p>\n
To begin with, the order of expressions can affect the CHECKSUM<\/code><\/b> outcome, when comparing data values, as can different collations. There is also the issue of the same checksum being returned for different values, as the Microsoft documentation alludes to. Phil Factor provides a great overview of the  CHECKSUM<\/code><\/b> dilemma in his article  On Comparing Tables in SQL Server<\/a>. Borrowing from his examples, I created the following T-SQL code to sum up his points:<\/p>\n
DECLARE @str TABLE(StrValue VARCHAR(25));\r\n\u00a0\r\nINSERT INTO @str VALUES\r\n\u00a0 ('accosted guards'),\r\n\u00a0 ('accorded feasts'),\r\n\u00a0 ('Accorded Feasts');\r\n\u00a0\r\nSELECT StrValue, CHECKSUM(StrValue) Chksum,\r\n\u00a0 BINARY_CHECKSUM(StrValue) BiChksum\r\nFROM @str;\r\nGO\r\n<\/pre>\n
The example creates a table variable to hold three values and then runs the  CHECKSUM<\/code><\/b> and  BINARY_CHECKSUM<\/code><\/b> functions against each one, giving us the following results:<\/p>\n\n\n\n\n\n\n
\n
 StrValue<\/b><\/p>\n<\/td>\n
\n
 Chksum<\/b><\/p>\n<\/td>\n
\n
 BiChksum<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
accosted guards<\/p>\n<\/td>\n
\n
-1099752389<\/p>\n<\/td>\n
\n
22470672<\/p>\n<\/td>\n<\/tr>\n
\n
accorded feasts<\/p>\n<\/td>\n
\n
-1103946693<\/p>\n<\/td>\n
\n
22470672<\/p>\n<\/td>\n<\/tr>\n
\n
Accorded Feasts<\/p>\n<\/td>\n
\n
-1103946693<\/p>\n<\/td>\n
\n
592896016<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Notice that  CHECKSUM<\/code><\/b> is case insensitive so treats the second and third values the same. On the other hand,  BINARY_CHECKSUM<\/code><\/b> is case sensitive, but treats the first two values the same, even though they are clearly different. This appears to have something to do with values that share the same type and byte representation. Unfortunately, SQL Server documentation provides few specifics about the distinction between the two functions, made worse by the fact that their results are difficult to predict.<\/p>\n
To further complicate matters, the SQL Server documentation states that  CHECKSUM<\/code><\/b> returns “the checksum value” and “is intended for use in building hash indexes,” whereas  BINARY_CHECKSUM<\/code><\/b> returns “the binary checksum value” and “can be used to detect changes to a row of a table.” The latter assertion is particularly surprising given that BINARY_CHECKSUM<\/code><\/b> appears less reliable than  CHECKSUM<\/code><\/b>, unless case sensitivity is an overriding concern.<\/p>\n
Another interesting fact about the SQL Server documentation is that it states that  CHECKSUM<\/code><\/b> returns an  INT<\/code><\/b> value but fails to state the return type for  BINARY_CHECKSUM<\/code><\/b>. Luckily, we can find that out for ourselves:<\/p>\n
DECLARE @str VARCHAR(25) = 'Accorded Feasts';\r\n\u00a0\r\nSELECT @str StrValue,\r\n\u00a0 SQL_VARIANT_PROPERTY(CHECKSUM(@str), 'basetype') ChkType,\r\n\u00a0 SQL_VARIANT_PROPERTY(BINARY_CHECKSUM(@str), 'basetype') BiChkType;\r\nGO\r\n<\/pre>\n
As the following results show, both functions return an INT<\/code><\/b> value, although we might have expected a more binary-like result for  BINARY_CHECKSUM<\/code><\/b>:<\/p>\n\n\n\n\n
\n
 StrValue<\/b><\/p>\n<\/td>\n
\n
 ChkType<\/b><\/p>\n<\/td>\n
\n
 BiChkType<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Accorded Feasts<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
If you plan to use either of the checksum functions to compare rows, then do so with caution and be sure your application can tolerate a few anomalies; otherwise, go with another solution.<\/p>\n
In the meantime, let’s look at how we can use  CHECKSUM<\/code><\/b> to create a hash index. In the following T-SQL code, I use the function in the table definition to create a hash on the  ProdName<\/code><\/b> column and then create an index on the  ProdHash<\/code><\/b> column:<\/p>\n
IF OBJECT_ID (N'dbo.Products', N'U') IS NOT NULL\r\nDROP TABLE dbo.Products;\r\nGO\r\n\u00a0\r\nCREATE TABLE dbo.Products\r\n(\r\n\u00a0 ProdID INT PRIMARY KEY IDENTITY(101, 1),\r\n\u00a0 ProdName NVARCHAR(50) NOT NULL,\r\n\u00a0 ProdHash AS CHECKSUM(ProdName)\r\n);\r\nGO\r\n\u00a0\r\nCREATE INDEX chksum_products ON dbo.Products(ProdHash);\r\nGO\r\n\u00a0\r\nINSERT INTO dbo.Products(ProdName)\r\nVALUES('Widget Enterprise 1');\r\n<\/pre>\n
After I create the index, I insert a row into the  Product<\/code><\/b> table. I can then use the  CHECKSUM<\/code><\/b> function in my  WHERE<\/code><\/b> clause to compare a value to the ProdHash<\/code><\/b> column:<\/p>\n
SELECT * FROM dbo.Products\r\nWHERE CHECKSUM(N'Widget Enterprise 1') = ProdHash;\r\n<\/pre>\n
As expected, the  SELECT<\/code><\/b> statement returns the row I just created, as shown in the following results:<\/p>\n\n\n\n\n
\n
 ProdID<\/b><\/p>\n<\/td>\n
\n
 ProdName<\/b><\/p>\n<\/td>\n
\n
 ProdHash<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
101<\/p>\n<\/td>\n
\n
Widget Enterprise 1<\/p>\n<\/td>\n
\n
422011215<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
You probably noticed that I preceded my string with  N'<\/code><\/b> to pass it in as Unicode. Had I instead run the following  SELECT<\/code><\/b> statement, without the  N'<\/code><\/b> prefix, the query would have returned no results:<\/p>\n
SELECT * FROM dbo.Products\r\nWHERE CHECKSUM('Widget Enterprise 1') = ProdHash;\r\n<\/pre>\n
When working with the checksum functions, you want to be sure you take into account all the possible differences and peculiarities. And be sure to check out the  HashBytes<\/code><\/b> function. It gives you more control over the algorithm used to hash the values and provides more reliable results, although it is not as fast as the checksum alternatives.<\/p>\n
Session-related functions<\/h1>\n
Several of the system functions available in SQL Server provide session-related information. One of these,  CONNECTIONPROPERTY<\/code><\/b>, returns information about the properties associated with the current connection. When you call the function, you must specify one of several acceptable string values. For example, the following  SELECT<\/code><\/b> statement uses the function to call the  net_transport<\/code><\/b> property:<\/p>\n
SELECT CONNECTIONPROPERTY('net_transport');\r\n<\/pre>\n
The  net_transport<\/code><\/b> property refers to the physical transport protocol used by the connection. The value returned will be one of the following types:  HTTP<\/code><\/b>, Named<\/code><\/b>  pipe<\/code><\/b>,  Session<\/code><\/b>,  Shared<\/code><\/b>  memory<\/code><\/b>,  SSL<\/code><\/b>,  TCP<\/code><\/b>, or  VIA<\/code><\/b>.<\/p>\n
You can instead call the  protocol_type<\/code><\/b> property, which returns either  TSQL<\/code><\/b> or  SOAP<\/code><\/b>:<\/p>\n
SELECT CONNECTIONPROPERTY('protocol_type');\r\n<\/pre>\n
The function supports other properties, but you get to the point. Refer to SQL Server online help if you want to see the full list. Note, however, you can retrieve the same information through the  sys.dm_exec_connections<\/code><\/b> system view, along with other properties not available through the  CONNECTIONPROPERTY<\/code><\/b> function:<\/p>\n
SELECT session_id, net_transport, protocol_type, num_reads\r\nFROM sys.dm_exec_connections\r\nWHERE session_id = @@SPID;\r\n<\/pre>\n
In addition to the  net_transport<\/code><\/b> and protocol_type<\/code><\/b> values, the  SELECT<\/code><\/b> clause includes the session_id<\/code><\/b> and  num_reads<\/code><\/b> values, neither of which are available to the  CONNECTIONPROPERTY<\/code><\/b> function. The  WHERE<\/code><\/b> clause uses the  @@SPID<\/code><\/b> built-in function to return the session ID of the current user process, giving us the following results:<\/p>\n\n\n\n\n
\n
 session_id<\/b><\/p>\n<\/td>\n
\n
 net_transport<\/b><\/p>\n<\/td>\n
\n
 protocol_type<\/b><\/p>\n<\/td>\n
\n
 num_reads<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
52<\/p>\n<\/td>\n
\n
Shared memory<\/p>\n<\/td>\n
\n
TSQL<\/p>\n<\/td>\n
\n
17<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Another session-related system function is  CURRENT_REQUEST_ID<\/code><\/b>, which returns the ID of the current request in the current session. On the surface, there might seem to be few uses for this function, and SQL Server documentation offers even fewer clues, other than to suggest using the function in conjunction with  @@SPID<\/code><\/b>.<\/p>\n
One possible use of the  CURRENT_REQUEST_ID<\/code><\/b> function is for checking your code’s performance from within the code. For example, you can use the function along with  @@SPID<\/code><\/b> to query the  sys.dm_exec_requests<\/code><\/b> system view multiple times in a single batch:<\/p>\n
DECLARE @start DATETIME, @end DATETIME, @duration INT;\r\n\u00a0\r\nSELECT @start = start_time\r\nFROM sys.dm_exec_requests\r\nWHERE session_id = @@SPID\r\n\u00a0 AND request_id = CURRENT_REQUEST_ID(); \r\n\u00a0\r\nWAITFOR DELAY '00:00:02';\r\n\u00a0\r\nSELECT @end = DATEADD(ms, total_elapsed_time, start_time),\r\n\u00a0 @duration = DATEDIFF(ms, @start, @end)\r\nFROM sys.dm_exec_requests\r\nWHERE session_id = @@SPID\r\n\u00a0 AND request_id = CURRENT_REQUEST_ID();\r\n\u00a0\r\nSELECT CAST(@duration AS VARCHAR(10)) + ' milliseconds';\r\n<\/pre>\n
In this case, we’re using a  WAITFOR<\/code><\/b> statement to simulate our primary operation. By querying the  sys.dm_exec_requests<\/code><\/b> view multiple times, we can measure the operation’s performance.<\/p>\n
SQL Server also supports the  CONTEXT_INFO<\/code><\/b> function to return the  context_info<\/code><\/b> value that was explicitly set for the current session or batch through the use of a SET CONTEXT_INFO<\/code><\/b> statement, as shown in the following example:<\/p>\n
SET CONTEXT_INFO 0x1234567890\r\nGO\r\nSELECT CONTEXT_INFO()\r\nGO\r\n<\/pre>\n
The  SET CONTEXT_INFO<\/code><\/b> statement associates a binary data value (up to 128 bytes) with the current session or connection. In SQL Server Management Studio, the  SELECT<\/code><\/b> statement returns the following results:<\/p>\n
0x1234567890000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\r\n000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\r\n000000000000000000000000000000000000000000000000000000\r\n<\/pre>\n
However, you can also retrieve the  context_info<\/code><\/b> value by querying the  sys.dm_exec_session<\/code><\/b>s system view:<\/p>\n
SELECT session_id, context_info \r\nFROM sys.dm_exec_sessions\r\nWHERE session_id = @@SPID;\r\n<\/pre>\n
Not surprisingly, the view will also provide us with an assortment of other session-related information, such as the login time, host name, CPU time, memory usage, and much more. But if you need only the  context_info<\/code><\/b> value, the  CONTEXT_INFO<\/code><\/b> function is the way to go.<\/p>\n
Transactions and statement execution<\/h1>\n
SQL Server also provides a number of system functions related to transactions and statement execution. For example, you can use the  @@ROWCOUNT<\/code><\/b> function to return the number of rows affected by the last statement, whether the statement modified data or simply read that data. The following example shows how you might use the function in a batch to return a message based on the row count:<\/p>\n
DECLARE @prod TABLE(ProdID INT, ProdName VARCHAR(25));\r\n\u00a0\r\nINSERT INTO @prod VALUES\r\n\u00a0 (101, 'widget1'),\r\n\u00a0 (102, 'widget2'),\r\n\u00a0 (103, 'widgetX');\r\n\u00a0\r\nUPDATE @prod\r\nSET ProdName = 'widget3'\r\nWHERE ProdID = 103;\r\nGO\r\n\u00a0\r\nDECLARE @rowcount INT = @@ROWCOUNT;\r\n\u00a0\r\nIF @rowcount > 1\r\n\u00a0 PRINT 'More than one row has been updated.';\r\nELSE IF @rowcount = 0\r\n\u00a0 PRINT 'No rows have been updated.';\r\nELSE\r\n\u00a0 PRINT 'One row has been updated.';\r\n<\/pre>\n
The T-SQL code updates a single row and then captures the row count. In this case, the batch returns the following message:<\/p>\n
One row has been updated.\r\n<\/pre>\n
Given the we’ve defined the table without a primary key or unique index, any number of rows could have potentially been updated, but this was meant only as a simple example to demonstrate how the  @@ROWCOUNT<\/code><\/b> function can be used, not only to return a message, but also for logging and auditing purposes.<\/p>\n
Note that, if you plan to carry out operations that exceed 2 billion rows, you should instead use the  ROWCOUNT_BIG<\/code><\/b> function.<\/p>\n
Another count-like function is  @@TRANCOUNT<\/code><\/b>, which returns the number of  BEGIN<\/code><\/b>  TRANSACTION<\/code><\/b> statements that have run in the current sessions. Let’s take a look at how this works by embedding one transaction in another and returning counts along the way:<\/p>\n
DECLARE @trancounts VARCHAR(20) = NULL;\r\nSET @trancounts = CAST(@@TRANCOUNT AS VARCHAR(10));\r\n\u00a0\r\nBEGIN TRAN\r\n\u00a0 SET @trancounts += '; ' + CAST(@@TRANCOUNT AS VARCHAR(10));\r\n\u00a0 BEGIN TRAN\r\n\u00a0\u00a0\u00a0 SET @trancounts += '; ' + CAST(@@TRANCOUNT AS VARCHAR(10));\r\n\u00a0 COMMIT\r\n\u00a0 SET @trancounts += '; ' + CAST(@@TRANCOUNT AS VARCHAR(10)); \r\nCOMMIT\r\n\u00a0\r\nSET @trancounts += '; ' + CAST(@@TRANCOUNT AS VARCHAR(10));\r\nSELECT @trancounts;\r\n<\/pre>\n
We start by retrieving the first transaction count. Then we begin the first transaction and get another count. Next we begin a second transaction and get that count. We commit the second transaction and then the first, getting counts every step of the way. The final  SELECT<\/code><\/b> statement returns the following results:<\/p>\n
0; 1; 2; 1; 0\r\n<\/pre>\n
As you can see, the first and last calls to  @@TRANCOUNT<\/code><\/b> result in a  0<\/code><\/b> value, the second and fourth calls result in  1<\/code><\/b>, and the third call results in 2<\/code><\/b>, which is the call embedded in both active transactions. It turns out that the  COMMIT<\/code><\/b> statement decrements the count by one, as would the  ROLLBACK<\/code><\/b> statement, had we included one.<\/p>\n
Now let’s look at the  XACT_STATE<\/code><\/b> function, which returns the transaction state of a current request. We’ll start by creating the  Products<\/code><\/b> table:<\/p>\n
IF OBJECT_ID (N'dbo.Products', N'U') IS NOT NULL\r\nDROP TABLE dbo.Products;\r\nGO\r\n\u00a0\r\nCREATE TABLE dbo.Products\r\n(\r\n\u00a0 ProdID INT PRIMARY KEY IDENTITY(101, 1),\r\n\u00a0 ProdAltID INT NULL REFERENCES Production.Product(ProductID)\r\n);\r\n<\/pre>\n
Notice that the table includes a foreign key that references the Production.Product<\/code><\/b> table in the  AdventureWorks2014<\/code><\/b> database.<\/p>\n
If we were to now call the  XACT_STATE<\/code><\/b> function:<\/p>\n
SELECT XACT_STATE();\r\n<\/pre>\n
We would receive a value of  0<\/code><\/b>, indicating that there are no active user transactions for the current request.<\/p>\n
However, let’s now use the function within a transaction. But first, we’ll run a  SET<\/code><\/b>  XACT_ABORT<\/code><\/b> statement that sets the  XACT_ABORT<\/code><\/b> property to  ON<\/code><\/b>. Because we set the property to ON<\/code><\/b>, if our statement raises an error, the entire transaction is terminated and rolled back. This also lets us use the  XACT_STATE<\/code><\/b> function to capture the transaction state, as shown in the following example:<\/p>\n
SET XACT_ABORT ON;\r\nBEGIN TRY\r\n\u00a0 BEGIN TRANSACTION;\r\n\u00a0\u00a0\u00a0 INSERT INTO dbo.Products(ProdAltID) VALUES(1);\r\n\u00a0\u00a0\u00a0 SELECT XACT_STATE();\r\n\u00a0 COMMIT TRANSACTION;\r\nEND TRY\r\nBEGIN CATCH\r\n\u00a0 IF XACT_STATE() = -1\r\n\u00a0\u00a0\u00a0 SELECT XACT_STATE();\r\n\u00a0\u00a0\u00a0 ROLLBACK TRANSACTION;\r\nEND CATCH\r\n<\/pre>\n
When we run the batch, the  INSERT<\/code><\/b> statement adds the row of data, and the  XACT_STATE<\/code><\/b> function returns a value of  1<\/code><\/b>, indicating that the current request has an active transaction. The transaction will then commit.<\/p>\n
If we instead pass the value  5<\/code><\/b> into our INSERT<\/code><\/b> statement, the statement will result in a foreign key constraint violation and the  CATCH<\/code><\/b> block will run. This time, the  XACT_STATE<\/code><\/b> function will return a value of  -1<\/code><\/b>, indicating that the current request has an active transaction but that an error has occurred.<\/p>\n
Now let’s look at a much different system function:  @@PACK_RECEIVED<\/code><\/b>. The function returns the number of input packets that SQL Server has read from the network since it was last started. For example, when I ran the following  SELECT<\/code><\/b> statement, the function returned a value of  2638<\/code><\/b>:<\/p>\n
SELECT @@PACK_RECEIVED;\r\n<\/pre>\n
Note that you can also retrieve this same information, and much more, by using the  sp_monitor<\/code><\/b> system stored procedure.<\/p>\n
There’s one other interesting function I wanted to point out in this category,  MIN_ACTIVE_ROWVERSION<\/code><\/b>, which returns the lowest active  rowversion<\/code><\/b> value in the current database. The  rowversion<\/code><\/b> data type is often used for version-stamping rows in a table, as shown in the following example:<\/p>\n
IF OBJECT_ID (N'dbo.Products', N'U') IS NOT NULL\r\nDROP TABLE dbo.Products;\r\nGO\r\n\u00a0\r\nCREATE TABLE dbo.Products\r\n(\r\n\u00a0 ProdID INT PRIMARY KEY IDENTITY(101, 1),\r\n\u00a0 ProdName NVARCHAR(25) NOT NULL,\r\n\u00a0 ProdRowVersion rowversion\r\n);\r\nGO\r\n<\/pre>\n
When the  rowversion<\/code><\/b> data type is used, SQL Server automatically assigns a binary number to that row that is unique across the entire database. In conjunction with this, we can use the  MIN_ACTIVE_ROWVERSION<\/code><\/b> function to retrieve the next available (active) rowversion<\/code><\/b> value:<\/p>\n
SELECT MIN_ACTIVE_ROWVERSION();\r\n<\/pre>\n
When I tried this on my system, I received the following value:<\/p>\n
0x00000000000B6220\r\n<\/pre>\n
I then verified that this was the next available value by running the following  INSERT<\/code><\/b> and  SELECT<\/code><\/b> statements:<\/p>\n
INSERT INTO dbo.Products(ProdName) VALUES('widget1');\r\nSELECT ProdRowVersion FROM dbo.Products;\r\n<\/pre>\n
As expected, this  SELECT<\/code><\/b> statement returned the same  rowversion<\/code><\/b> value that was returned by the preceding  SELECT<\/code><\/b> statement. Because that was the lowest active value, that was the value inserted into my table.<\/p>\n
Error-related functions<\/h1>\n
The final category of system functions that we’ll look at are related to capturing error information. To demonstrate how they work, we can start by once again creating a version of our  Products<\/code><\/b> table:<\/p>\n
IF OBJECT_ID (N'dbo.Products', N'U') IS NOT NULL\r\nDROP TABLE dbo.Products;\r\nGO\r\n\u00a0\r\nCREATE TABLE dbo.Products\r\n(\r\n\u00a0 ProdID INT PRIMARY KEY IDENTITY(101, 1),\r\n\u00a0 ProdName NVARCHAR(25) NOT NULL,\r\n\u00a0 ProdAltID INT NULL REFERENCES Production.Product(ProductID)\r\n);\r\n<\/pre>\n
As in an earlier example, we are including a foreign key constraint that references the  Production.Product<\/code><\/b> table in the AdventureWorks2014<\/code><\/b> database. Now let’s re-create our  InsertProduct<\/code><\/b> stored procedure, using several error-related system functions:<\/p>\n
IF OBJECT_ID (N'dbo.InsertProduct', N'P') IS NOT NULL\r\nDROP PROCEDURE dbo.InsertProduct;\r\nGO\r\n\u00a0\r\nCREATE PROCEDURE dbo.InsertProduct\r\n\u00a0 (@ProdName VARCHAR(50) = NULL, @AltID INT = NULL)\r\nAS\r\nIF @ProdName IS NOT NULL AND @AltID IS NOT NULL\r\nBEGIN\r\n\u00a0 BEGIN TRY\r\n\u00a0\u00a0\u00a0 INSERT INTO dbo.Products(ProdName, ProdAltID)\r\n\u00a0\u00a0\u00a0 VALUES(@ProdName, @AltID);\r\n\u00a0 END TRY\r\n\u00a0 BEGIN CATCH\r\n\u00a0\u00a0\u00a0 PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));\r\n\u00a0\u00a0\u00a0 PRINT 'Error severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));\r\n\u00a0\u00a0\u00a0 PRINT 'Error state: ' + CAST(ERROR_STATE() AS VARCHAR(10));\r\n\u00a0\u00a0\u00a0 PRINT 'Error procedure: ' + ERROR_PROCEDURE();\r\n\u00a0\u00a0\u00a0 PRINT 'Error line: ' + CAST(ERROR_LINE() AS VARCHAR(10));\r\n\u00a0\u00a0\u00a0 PRINT 'Error message: ' + ERROR_MESSAGE();\r\n\u00a0 END CATCH\r\nEND\r\nGO\r\n<\/pre>\n
In this case, we’ve included six error-related functions, with names that should make their purpose self-evident. For example, the  ERROR_NUMBER<\/code><\/b> function returns the SQL Server error number, and the ERROR_MESSAGE<\/code><\/b> function returns the error text. Now let’s call the stored procedure, passing in an ID value that we know will raise a foreign key violation error:<\/p>\n
EXEC InsertProduct 'widget1', 5;\r\n<\/pre>\n
Because of the foreign key violation, the  CATCH<\/code><\/b> block in our stored procedure runs and returns the following error-related information:<\/p>\n
Error number: 547\r\nError severity: 16\r\nError state: 0\r\nError procedure: InsertProduct\r\nError line: 7\r\nError message: The INSERT statement conflicted with the FOREIGN KEY constraint \"FK__Products__ProdAl__13A7DD28\". The conflict occurred in database \"AdventureWorks2014\", table \"Production.Product\", column 'ProductID'.\r\n<\/pre>\n
As you can see, the functions make it relatively simple to return the details we want. One important point to note, however, is that each function returns a  NULL<\/code><\/b> value if called outside the scope of the  CATCH<\/code><\/b> block. Clearly, these functions serve a very specific purpose. For more details about them and other error-related functions, refer to the SQL Server documentation.<\/p>\n
SQL Server system functions<\/h1>\n
Although we have not covered all the system functions here, we’ve looked at most of them, so you should have a good sense of their scope and the types of tasks you can accomplish by using them. Because we covered so many of the functions, we could not go into each one in the detail that might have been warranted, so I again refer you to the SQL Server documentation to help you fill in the blanks.<\/p>\n
Systems functions represent only a subset of the built-in functions available in SQL Server. In addition to a wide range of scalar functions, SQL Server also provides rowset, aggregate, and ranking function. System functions represent only the tip of the iceberg, but they can serve as a good starting point for familiarizing yourself with the many types of built-in functions that Microsoft offers.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric())  Robert Sheldon provides an overview of the most commonly used of them.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4242,4168,4150,4151,4252],"coauthors":[6779],"class_list":["post-2194","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-basics","tag-database","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2194","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2194"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2194\/revisions"}],"predecessor-version":[{"id":92509,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2194\/revisions\/92509"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2194"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2194"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}