You are here

SQL: System functions

system function

System functions

In this post we will discuss System functions, it returns information about various system parameters.

@@ROWCOUNT

  • @@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.

Example:

Employee (emp_id is primary key)

emp_id

fname man_id
1 Riya

NULL

2

Diya 1
3 Jiya

2

4

Priya

3

Select * from Employee where emp_id IS NULL;

IF @@ROWCOUNT=0

print ’employee id is not null’;

output:

employee id is not null

ROWCOUNT_BIG

  • 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.

Compression Functions

  • 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.

Syntax:

COMPRESS (expression)

DECOMPRESS (expression)

Here expression can be nvarchar(n), nvarchar(max), varchar(n), varbinary(max) or binary(n).

Example:

select COMPRESS(‘Riya Patel’)as compress_value;

output:

compress_value

0x1F8B08000000000004000BCAAC4C5408482C49CD010090EC26830A000000

select DECOMPRESS(0x1F8B08000000000004000BCAAC4C5408482C49CD010090EC26830A000000) as decompress_value

output:

decompress_value

0x5269796120506174656C

select CAST(0x5269796120506174656C as varchar(max)) as original_value;

output:

original_value

Riya Patel

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.

Example:

Declare @mycontext as varbinary(128)=CAST(‘us-english’ as varbinary(128))

set CONTEXT_INFO @mycontext;

select CAST(CONTEXT_INFO() as varchar(128)) as myinfo;

output:

myinfo

us-english

  • 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.

Leave a Reply

Top