MSDN Standard Document: https://msdn.microsoft.com/zh-cn/library/ff848784 (v=sql.120). aspx
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 (97)
Converts an int ASCII code to a character.
tab = CHAR (9)
Line break = char (10)
return character = char (13)
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 ', 10)
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, and the 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