T-SQL Tutorial

Functions - T-SQL Tutorial


TSQL Aggregate Functions

  • AVG - returns the average value.
  • COUNT - returns the number of rows.
  • MAX - returns the maximum value.
  • MIN - returns the minimum value.
  • SUM - returns the sum of all values.

TSQL String Functions

  • Charindex - returns the start position.
  • Concat - returns a string as a result of a concatenation.
  • Concat_ws - concatenate two or more strings into a single string.
  • Left - returns the left part of a character string.
  • Len - returns the number of characters from a string expression, excluding trailing spaces.
  • Lower - returns a lowercase character expression.
  • Ltrim - returns a character expression after it removes leading blanks.
  • Substring - returns part of a character.
  • Patindex - returns the starting position of the first occurrence of a pattern in a specified expression.
  • Replace - replace values of a specified string with another string values.
  • Right - returns the right part of a character string.
  • Rtrim - returns a character string after truncating all trailing spaces.
  • STRING_SPLIT - is a table-valued function to split a string into rows of substrings.
  • STRING_ESCAPE - is used to escape special characters within a string.
  • Upper - returns a uppercase character expression.

TSQL Date and Time Data Types and Functions

  • @@DATEFIRST - returns the first day of each week.
  • CURRENT_TIMESTAMP - returns the current database system timestamp.
  • DATEADD - returns a date with the specified number interval added.
  • DATEDIFF - returns the difference between the specified startdate and enddate.
  • DATEFROMPARTS - returns a date value for the specified year, month or day.
  • DATENAME - returns a string that represents the specified datepart of the specified date.
  • DATEPART - returns an integer that represents the specified datepart of the specified date.
  • DATETIMEFROMPARTS - returns a datetime value for the specified date and time arguments.
  • DATETIME2FROMPARTS - returns a datetime2 value for the specified date and time arguments.
  • DAY - returns from a date an integer that represents the day of the month.
  • EOMONTH - returns the last day of the month.
  • GETDATE() - returns the current database system timestamp.
  • GETUTCDATE() - returns the current database system timestamp.
  • ISDATE - returns 1 for valid date, time, or datetime value. otherwise, 0.
  • MONTH - returns an int value that represents the month of the specified date.
  • SMALLDATETIMEFROMPARTS - returns a smalldatetime value for the specified date and time.
  • SWITCHOFFSET - returns a datetimeoffset value that is changed from the stored time zone offset.
  • SYSDATETIME - returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
  • SYSDATETIMEOFFSET - Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
  • SYSUTCDATETIME - the date and time is returned as UTC time (Coordinated Universal Time).
  • TIMEFROMPARTS - returns a time value for the specified time and with the specified precision.
  • TODATETIMEOFFSET - returns a datetimeoffset value that is translated from a datetime2 expression.
  • YEAR - returns an int value that represents the year of the specified date.

TSQL System Functions

  • @@CONNECTIONS - returns the number of attempted connections.
  • @@ERROR - returns the error number for the last Transact-SQL statement executed.
  • @@IDENTITY - returns the last inserted identity value.
  • @@ROWCOUNT - returns the number of rows affected by the last statement.
  • ERROR_LINE - returns the line number of occurrence of an error.
  • ERROR_MESSAGE - returns the message text of the error.
  • ERROR_NUMBER - returns the error number of the error.
  • ERROR_PROCEDURE - returns the name of the stored procedure or trigger where an error occurs.
  • ERROR_SEVERITY - returns the severity value of the error where an error occurs.
  • ERROR_STATE - returns the state number of the error that caused.
  • HOST_ID - returns the workstation identification number.
  • HOST_NAME - returns the workstation name.
  • ISNULL - replaces NULL with the specified replacement value.
  • ISNUMERIC - determines whether an expression is a valid numeric type.

TSQL Security Functions

  • CURRENT_USER - returns the name of the current user.
  • ORIGINAL_LOGIN - returns the name of the login that connected to the instance of SQL Server.
  • SESSION_USER - returns the user name of the current session in the current database.
  • SUSER_SID - returns the security identification number (SID).
  • SYSTEM_USER - returns the system login user name.
  • USER_NAME - returns a database user name from a specified identification number.

TSQL Metadata Functions

  • APP_NAME - returns the application name for the current session.
  • DB_ID - returns the database id number.
  • DB_NAME - returns the database name.
  • OBJECT_DEFINITION - returns the definition of a specified object.
  • OBJECT_ID - returns the database object id number.
  • OBJECT_NAME - returns the database object name.
  • OBJECT_SCHEMA_NAME - returns the database schema name.
  • SCHEMA_ID - returns the schema ID associated with a schema name.
  • SCHEMA_NAME - returns the schema name associated with a schema ID.

TSQL Configuration Functions

  • @@LOCK_TIMEOUT - returns the current lock time-out setting in milliseconds for the current session.
  • @@MAX_CONNECTIONS - returns the maximum number of simultaneous user connections allowed on an instance of SQL Server.
  • @@SERVERNAME - returns the name of the local server.
  • @@SERVICENAME - returns the name of the registry key under which SQL Server is running.
  • @@SPID - returns the session ID of the current user process.

TSQL Conversion Functions

  • CAST - convert an expression of one data type to another.
  • CONVERT - convert an expression of one data type to another.
  • PARSE - returns the result of an expression, translated to the requested data type in SQL Server.
  • TRY_CAST - returns a value cast to the specified data type if the cast succeeds.
  • TRY_CONVERT - returns a value cast to the specified data type if the cast succeeds.
  • TRY_PARSE - is used for converting expressions from string to date/time and number types.

TSQL Analytic functions

  • CUME_DIST - calculates the cumulative distribution of a value within a group of values.
  • FIRST_VALUE - returns the first value from a specified column.
  • LAST_VALUE - returns the last value from a specified column.
  • LAG - returns values from a previous row.
  • LEAD - returns values from a next row.
  • PERCENT_RANK - is similar to the CUME_DIST function.
  • PERCENTILE_CONT - calculates a percentile based on a continuous distribution of the column value.
  • PERCENTILE_DISC - calculates a specific percentile for sorted values in an entire rowset.

TSQL Ranking functions

  • ROW_NUMBER - its primary purpose is to serialize the rows of the result set in the partitioned order provided by the OVER clause.
  • RANK - returns the rank of a value in a given list. The rank of a value is its position in the list, with the first value having a rank of 1.
  • DENSE_RANK - returns relative position of rows within a result set.
  • NTILE - divide the rows in groups.