In this post we will discuss System functions, it returns information about various system parameters.
- @@ROWCOUNT function returns number of rows affected by the last executed statement.
- It is used to check if the previous statement affects any rows by checking the function’s result is zero or greater than zero.
- The @@ROWCOUNT function returns an INT type value.
Employee (emp_id is primary key)
Select * from Employee where emp_id IS NULL;
print ’employee id is not null’;
employee id is not null
- ROWCOUNT_BIG is same as @@ROWCOUNT function but it is used when number
Rows are more than 2 billion.
- The ROWCOUNT_BIG function returns a BIGINT type value.
- SQL support function called COMPRESS that allows you to compress an input character or binary string using the GZIP algorithm into a result binary string.
- It also supports function DECOMPRESS that decompress a previously compressed string.
Here expression can be nvarchar(n), nvarchar(max), varchar(n), varbinary(max) or binary(n).
select COMPRESS(‘Riya Patel’)as compress_value;
select DECOMPRESS(0x1F8B08000000000004000BCAAC4C5408482C49CD010090EC26830A000000) as decompress_value
select CAST(0x5269796120506174656C as varchar(max)) as original_value;
Context info and session context
- Context_info() function returns the context information value that was set for the current session or batch by using the SET CONTEXT_INFO statement.
Declare @mycontext as varbinary(128)=CAST(‘us-english’ as varbinary(128))
set CONTEXT_INFO @mycontext;
select CAST(CONTEXT_INFO() as varchar(128)) as myinfo;
- Session context is more convenient and alternative to context info.
- Using session context, we can store key-value pairs, where key is a name of a sysname type that we assign to our session’s variable the value is SQL_VARIANT typed value that is associated with the key.
- We create the key and set its associated value using the sp_set_session_context stored procedure and read it using the SESSION_CONTEXT function.