SQL Server built-in functions

SQL Server categories of built-in functions

function columns	Description
Aggregation functions	The action performed is to combine multiple values into a single value. For example , COUNT,SUM,MIN, and MAX.
Configuration functions	is a scalar function that returns information about the configuration settings.
Cryptographic functions	Supports encryption, decryption, digital signature, and digital signature verification.
Cursor functions	Returns information about the state of the cursor.
Date and Time functions	You can change the value of a date and time.
Mathematical functions	Perform trigonometric, geometric, and other numeric operations.
Meta-data functions	Returns property information for database and database objects.
Ranking function	is a non-deterministic function that returns the rank value of each row in a partition.
Rowset functions	Returns a rowset that can be used in the location of a table reference in a Transact-SQL statement.
Security functions	Returns information about users and roles.
String functions	You can change the values of char, varchar,nchar,nvarchar,binary, and varbinary.
System functions	Manipulate or report on various options and objects at the system level.
System statistics functions	Returns information about the performance of SQL Server.
Text and image functions	You can change the value of text and image.

Configuration functions

Select @ @servername

Returns the name of the local server running SQL Server. When the local server name changes, the@ @servername cannot report this change unless you use sp_addserver or sp_dropserver to change it.

It is recommended to use the ServerName property of the system function Serverproperty to automatically report this change.

Select serverproperty (' servername ')

Date and Time functions

Select getdate ()

returns the current system date and time as a datetime value in the SQL Server 2005 standard internal format.

Select datepart (dy,getdate ())

Returns an integer that represents the specified date portion of the specified date.

Select dateadd (hh,1,getdate ())

Returns a new datetime value that is added to the specified date after a time interval .

Select DateDiff (d, ' 2008-07-01 ', ' 2008-07-14 ')

Returns the number of date boundaries and time boundaries across two specified dates.

Select getdate ()

Select Convert (Varchar,getdate (), 101)

Select Convert (Varchar,getdate (), 111)

Converts date data to character data in a specific date format.

Mathematical functions

Select Ceiling (12.34), ceiling ( -12.34)

Returns the smallest integer greater than or equal to the specified numeric expression.

Select rand ()

Returns a random float value from 0 to 1 .

Select Convert (Int,rand () *3+1)

Returns a random integer value from 1 to 3.

Select Round (748.53,0), round (748.53,1)

Select Round (748.53,-2), round (748.53,-1)

Returns a numeric expression that is rounded to the specified length or precision.

Meta-data functions

Select object_id (' master.sys.databases ')

Returns the database object identification number of the schema-scoped object.

Select object_name (ID) from master.sys.sysobjects where id=-213

Returns the name of the database object for the schema-scoped object.

Select object_definition (object_id (' Master.sys.sp_who2 '))

Returns the Transact-SQL source text for the definition of the specified object.

Security functions

Select is_member (' dbo ')

Select is_srvrolemember (' sysadmin ')

Indicates whether the current user is a member of the specified Microsoft Windows group or SQL Server database role.

Indicates whether the SQL Server 2005 logon name is a member of the specified fixed server role.

Select has_perms_by_name (null,null, ' View server State ')

Do I have server-level VIEW server state permissions?

Select has_perms_by_name (' Tom ', ' login ', ' impersonate ')

Can I impersonate server principal Tom?

If you can, you can use EXECUTE as to switch to Tom's context;

exec as user= ' Tom '

Use revert to switch back to the original context.

revert

Select has_perms_by_name (' master.sys.databases ', ' object ', ' select ')

Do I have select permission for master.sys.databases?

String functions

Select char (+), char ($)

converts an int ASCII code to a character.

tab = char(9)

Line break = Char(ten)

Carriage return = char(in)

Select charindex (' abc ', ' Abcdefgabc ', 5)

Returns the starting position of the specified expression in the string.

Select len (' abc ')

Returns the number of characters (not bytes) of the specified string expression, with no trailing spaces.

Select replicate (' XY ', ten)

Repeats the character expression at the specified number of times.

Select datalength (RTrim (' abc '))

Returns a string after truncating all trailing spaces.

where datalength() returns the number of bytes used to represent any expression.

Select substring (' abcdeft ', 3,2)

Returns a character expression, binary expression, text expression, or part of an image expression.

System functions

Select specialofferid,minqty,maxqty,isnull (maxqty,9999) as MaxQty2

From AdventureWorks.Sales.SpecialOffer

Replaces NULL with the specified replacement value .

Select @ @rowcount, Rowcount_big ()

Returns the number of rows affected by the previous statement, andthe return type of Rowcount_big is bigint.

Select * from Sys.dm_db_index_physical_stats (null,null,null,null,null)

Returns the size and fragmentation information for the data and indexes of the specified table or view.

System statistics functions

Select @ @total_errors, @ @total_read, @ @total_write

Returns the number of disk write errors that SQL Server encountered since the last startup, the number of times the disk was read (not read cache), and the number of disk writes performed.

SQL Server built-in functions