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