SQL Server built-in functions

Source: Internet
Author: User
Tags configuration settings getdate mathematical functions rand

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.