Microsoft’s SQL Server is a powerful database server that integrates well with other Microsoft technologies like ASP and .NET, and includes some of the best database management tools available. The following reference lists the various functions available in SQL Server, and demonstrates the creation of stored procedures, triggers and functions.
Data Functions |
Dateparts |
DATEADD (datepart, number, date) DATEDIFF (datepart, start, end) DATENAME (datepart, date) DATEPART (datepart, date) DAY (date) GETDATE() GETUTCDATE() MONTH (date) YEAR (date) |
Year – yy, yyyy Quarter – qq, q Month – mm, m Day of Year – dy, y Day – dd, d Week – wk, ww Hour – hh Minute – mi, n Second – ss, s Millisecond – ms |
|
|
Type Conversion |
Ranking Functions |
CAST (expression AS datatype) CONVERT (datatype, expression) |
RANK – NTILE DENSE_RANK -ROW_NUMBER |
|
|
Mathematical Functions |
String Functions |
ABS – ACOS – ASIN ATAN – ATN2 – CEILING COS – COT – DEGREES EXP – FLOOR – LOG LOG10 – PI – POWER RADIANS – RAND – ROUND SIGN – SIN – SQUARE SQRT – TAN |
ASCII – CHAR -CHARINDEX DIFFERENCE – LEFT – LEN LOWER – LTRIM -NCHAR PATINDEX – REPLACE – QUOTENAME REPLICATE – REVERSE -RIGHT RTRIM – SOUNDEX – SPACE STR – STUFF – SUBSTRING UNICODE – UPPER |
|
|
Grouping (Aggregate) Functions |
Table Functions |
AVG – BINARY_CHECKSUM CHECKSUM – CHECKSUM_AVG COUNT – COUNT_BIG GROUPING – MAX MIN – SUM – STDEV STDEVP – VAR – VARP |
ALTER CREATE DROP TRUNCATE |
|
|
Datatypes |
Create a Function |
Exact Numerics: Bit – tinyint – smallint – bigint Decimal – money – numeric Approximate Numerics: Float – real Date and Time: Smalldatetime – timestamp Datetime Strings: Char – text – varchar Unicode Strings: Nchar – ntext – nvarchar Binary Strings: Binary – image – varbinary Miscellaneous: Cursor – table sql_variant – xml |
CREATE FUNCTION name (@variable datatype(length)) RETURNS datatype(length) AS BEGIN DECLARE @return datatype(length) SELECT @return = CASE @variable WHEN ‘a’ THEN ‘return a’ WHEN ‘b’ THEN ‘return b’ ELSE ‘return c’ RETURN @return END |
|
|
Create a Stored Procedure |
Create a Trigger |
CREATE PROCEDURE name @variable AS datatype = value AS — Comments SELECT * FROM table GO |
CREATE TRIGGER name ON table FOR DELETE, INSERT, UPDATE AS — Comments SELECT * FROM table GO |
|
|
Create a View |
Create an Index |
CREATE VIEW name AS — Comments SELECT * FROM table GO |
CREATE UNIQUE INDEX name ON table (columns) |
|
|
This is a quick reference function list … good job.