Sql Server Developers Factsheet
ADVERTISEMENT
1
2SQL Server developers factsheet
by Xander Zelders,
Data types
String Functions (T-SQL)
Date and Time functions (T-SQL)
DATEADD (datepart , number, date): Returns a new datetime value based on adding an
ASCII (character) : Returns the ASCII code value of the leftmost character of character
type
Size
Range (from/to)
interval to the specified date.
CHAR (int) : Converts the integer ASCII code int to a character
DATEDIFF (datepart , number, date): Returns the number of date and time boundaries
Exact numerics
CHARINDEX (search, expression, [start]) : Returns starting position (int) of first occurrence of the string search
crossed between two specified dates.
within table or string expression starting from position start
bigint
8 bytes -9,223,372,036,854,775,808
DATENAME (datepart , date): Returns a character string representing the specified datepart
DIFFERENCE (expression1, expression2) : Returns the difference between the SOUNDEX values of the two
9,223,372,036,854,775,807
of the specified date.
character expressions as an integer
bit
1 bit
0 to 1
DATEPART (datepart , date): Returns an integer that represents the specified datepart of the
LEFT (expression, int): Returns part of character string expression starting at int characters from the left.
specified date.
decimal
-10^38 +1 to 10^38 –1
LEN (expression) : Returns the number of characters of the string expression, excluding trailing blanks.
DAY (date): Returns an integer representing the day datepart of the specified date.
LOWER (expression) : Returns character expression after converting uppercase string to lowercase
GETDATE : Returns the current system date and time.
int
4 bytes -2,147,483,648 to
LTRIM (expression) : Returns a character string after removing all leading blanks.
2,147,483,647
MONTH (date): Returns an integer that represents the month part of a specified date.
NCHAR (int) : Returns the Unicode character with the given integer code.
money
8 bytes -922,337,203,685,477.5808
YEAR (date): Returns an integer that represents the year part of a specified date.
PATINDEX ('%pattern%', expression) : Returns starting position of the first occurrence of a pattern in a specified
+922,337,203,685,477.5807
expression, or zeros if the pattern is not found, on all valid text and character data types.
numeric
19 bytes -10^38 +1 to 10^38 –1
REPLACE (expression1,expression2,expression3) : Replaces all occurrences of the second given string
Dateparts
Cursor Functions (T-SQL)
smallint
2 bytes -32,768 to 32,767
expression in the first string expression with a third expression.
QUOTENAME (character_string[, quote_character]) : Returns a Unicode string with the delimiters added to make
smallmoney
4 bytes -214,748.3648 to
the input string a valid Microsoft® SQL Server™ delimited identifier.
@@CURSOR_ROWS
Datepart
Abbreviations
+214,748.3647
REPLICATE (expression, int) : Repeats a character expression a specified number of times
Returns the number of qualifying rows currently
tinyint
1 byte
0 to 255
REVERSE (expression) : Returns the reverse of a character expression.
year
yy, yyyy
in the last cursor opened on the connection.
quarter
qq, q
RIGHT (expression, int): Returns part of character string expression starting at int characters from the right.
Approximate numerics
@@FETCH_STATUS
month
mm, m
RTRIM (expression) : Returns a character string after removing all trailing blanks.
Returns the status of the last cursor FETCH
dayofyear
dy, y
float
8 bytes -1.79E + 308 to
SOUNDEX (expression) : Returns a four-character (SOUNDEX) code.
statement issued against any cursor currently
day
dd, d
1.79E + 308
SPACE (int) : Returns a string of int spaces.
week
wk, ww
opened by the connection.
real
4 bytes -3.40E + 38 to
STR (float_expression[, length[, decimal]]) : Returns character data converted from numeric data.
weekday
dd
CURSOR_STATUS
3.40E + 38
STUFF (expression1, start, length, expression2 ) : Deletes a specified length (length) of characters from
hour
hh
A scalar function that allows the caller of a
Dates
minute
mi, n
expression1 and inserts another set (expression2) at a specified starting point (start) of expression1.
stored procedure to determine whether or not
second
ss, s
SUBSTRING (expression, start, length) : Returns part of character, binary, text expression or image expression
datetime
8 bytes Jan 1, 1753 to Dec 31, 9999
the procedure has returned a cursor and result
millisecond
ms
starting from position start with length length
set for a given parameter.
smalldatetime
4 bytes Jan 1, 1900 to Jun 6, 2079
UNICODE (char) : Returns the Unicode int value for the first character of char.
UPPER (expression) : Returns a character expression after converting lowercase string to uppercase.
Type / performance Characteristics
Mathematical Functions (T-SQL)
System Functions (T-SQL)
Character Strings
ABS (Expression): Returns the absolute (positive) value of a numeric expression.
char
Fixed-length non-Unicode character.
ACOS (Expression): Returns the angle, in radians, whose cosine is the specified float
@@ERROR : Returns the error number for the last Transact-SQL statement executed.
Max 8000 characters
expression; also called arccosine.
varchar
Variable-length non-Unicode data.
@@IDENTITY : returns the last-inserted identity value.
ASIN (Expression): Returns the angle, in radians, whose sine is the specified float
Max 8000 characters
@@ROWCOUNT : Returns the number of rows affected by the last statement.
expression. This is also called arcsine.
varchar(max)
Variable-length non-Unicode data
@@TRANCOUNT : Returns the number of active transactions for the current connection.
ATAN (Expression): Returns the angle in radians whose tangent is a specified float
Max 2^31 characters (SQL 2005)
APP_NAME : Returns the application name for the current session if set by the application.
expression. This is also called arctangent.
text
Variable-length non-Unicode data.
CASE : Evaluates a list of conditions and returns one of multiple possible result expressions.
Max 2,147,483,647 characters
ATN2 (Expression): Returns the angle, in radians, between the positive x-axis and the ray
CAST (expression AS data_type) / CONVERT : Converts an expression of one data type to another.
Unicode Character Strings
from the origin to the point (y, x), where x and y are the values of the specified float
COALESCE (expression [ ,...n ]) : Returns the first nonnull expression among its arguments.
expressions.
nchar
Fixed-length Unicode data.
CURRENT_TIMESTAMP : Returns the current date and time. ANSI SQL equivalent to GETDATE.
CEILING(Expression): Returns the smallest integer greater than, or equal to, the specified
Max 4000 characters
CURRENT_USER : Returns the name of the current user. Equivalent to USER_NAME().
numeric expression.
nvarchar
Variable-length Unicode data.
DATALENGTH (Expression) : Returns the number of bytes used to represent any expression.
COS (Expression): Returns the trigonometric cosine of the specified angle, in radians.
Max 4000 characters
FORMATMESSAGE (msg_number , [param_value [,...n]]) : Constructs a message from an existing message in
COT (Expression): Returns the trigonometric cotangent of the specified angle, in radians.
nvarchar(max)
Variable-length Unicode data
sys.messages and returns the formatted message for further processing.
DEGREES (Expression): Returns the corresponding angle in degrees for an angle specified
Max 2^30 characters (SQL 2005)
GETANSINULL : Returns the default nullability for the database for this session.
ntext
Variable-length Unicode data.
in radians.
HOST_ID : Returns the workstation identification number.
Max 1,073,741,823 characters
EXP (Expression): Returns the exponential value of the specified float expression.
HOST_NAME : Returns the workstation name.
Binary Strings
FLOOR (Expression): Returns the largest integer less than or equal to the specified numeric
IDENT_INCR : Returns the increment value (returned as numeric (@@MAXPRECISION,0)) specified during the
expression.
binary
Fixed-length binary data.
creation of an identity column in a table or view that has an identity column.
LOG (Expression): Returns the natural logarithm of the specified float expression.
Max 8000 bytes
IDENT_SEED : Returns the seed value (returned as numeric(@@MAXPRECISION,0)) that was specified when an
LOG10 (Expression): Returns the base-10 logarithm of the specified float expression.
varbinary
Variable-length binary data.
identity column in a table or a view that has an identity column was created.
PI : Returns the constant value of PI.
Max 8000 bytes
IDENTITY : to insert an identity column into a new table
POWER (Expression, y): Returns the value of the specified expression to the specified
varbinary(max)
Variable-length binary data.
ISDATE (expression): Determines whether an input expression is a valid date.
Max 2^31 bytes (SQL 2005)
power.
ISNULL (expression , replacement_value) : Replaces NULL with the specified value.
image
Variable-length binary data.
RADIANS (Expression): Returns radians of the numeric expression, in degrees.
ISNUMERIC (expression): Determines whether an expression is a valid numeric type.
Max 2,147,483,647 bytes.
RAND : Returns a random float value from 0 through 1.
Other types
NEWID : Creates a unique value of type uniqueidentifier.
ROUND (numeric_expression ,length [,function ]): Returns a numeric value, rounded to the
NULLIF (expression , expression) : Returns a null value if the two specified expressions are equal.
specified length or precision.
cursor
A data type for variables or stored
PARSENAME ('object_name',object_piece) : Returns the specified part of an object name. Parts of an object that
SIGN (Expression): Returns the positive (+1), zero (0), or negative (-1) sign of the specified
procedure OUTPUT parameters that
can be retrieved are the object name, owner name, database name, and server name.
contain a reference to a cursor.
expression.
PERMISSIONS ([objectid [,'column']]): Returns a value containing a bitmap that indicates the statement, object, or
sql_variant
A data type that stores values of
SIN (Expression): Returns the trigonometric sine of the specified angle, in radians, and in an
column permissions of the current user.
various SQL Server 2005-supported
approximate numeric, float, expression.
SESSION_USER : returns the user name of the current context in the current database.
data types, except text, ntext, image,
SQRT (Expression): Returns the square root of the specified float value.
timestamp, and sql_variant.
STATS_DATE : Returns the date that the statistics for the specified index were last updated.
SQUARE (Expression): Returns the square of the specified float value.
table
Is a special data type that can be
SYSTEM_USER : Allows a system-supplied value for the current login to be inserted into a table when no default
TAN (Expression): Returns the tangent of the input expression.
used to store a result set for
value is specified.
processing at a later time.
USER_NAME ([ID]): Returns a database user name from a specified identification number.
timestamp
Is a data type that exposes
automatically generated, unique
binary numbers within a database.
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education