Important Commands and Functions in SQL Server 2005

ABS
Row-level function that returns an absolute value.

ALTER COLUMN
Command used to change a column's size or type in a table.

ALTER TABLE
Command used to modify a table's definition.

AND
Logical operator that, when used in a WHEREclause, means that both criteria have to be met for a row to be included in the result set.

ASC
Function used in ORDER BY to put a SQL result set in ascending order.

AVG
Aggregate function used to average a group of row values.

BETWEEN
An operator used to determine whether a value occurs within a given range of values (inclusive); used with a WHERE clause.

BIGINT
Integer data type that can store numbers from -263 to 263 - 1.

BINARY
Data type used to store strings of bits.

BIT
Data type that consumes only a single bit of storage.

CAST
Conversion function used to change a data type of a column within a query.

CEILING
Row-level function that returns the next larger integer.

CHAR(size)
Character data type used when the column length is known and unvarying.

CHARACTER
Data type used to store any combination of letters, numbers, and symbols.

CHARINDEX
String function that returns the starting position of a specified pattern.

CHECK
Integrity constraint used to create bounds for a column value.

CONSTRAINTS
Restrictions that can be placed on values when creating database objects such as tables and views.

CONVERT
Conversion function used to explicitly convert to a given data type within in a query.

COUNT(*)
Function used to count the total number of rows in a result set.

COUNT(attribute)
Group function that counts the number of rows where attribute is not NULL.

CREATE INDEX
Command used to create an index.

CREATE DATABASE
Command used to create a database.

CREATE SYNONYM
Command used to create a synonym.

CREATE TABLE
Command used to create a table.

CREATE VIEW
Command used to create a view.

CROSS JOIN
A query option used to generate a Cartesian product.

DATE
Oracle equivalent of DATETIME .

DATEADD
Date function that adds to a specified part of a date.

DATEDIFF
Date function that returns the difference between two dates.

DATEFORMAT
Date function that controls how SQL Server interprets date constants that are entered for dates.

DATEPART
Date function that returns the specified part of the date requested.

DATETIME
Data type that can be used for dates.

DAY
Date function that extracts a day from a date.

DEC
Data type; synonym for DECIMAL data type.

DECIMAL
Numeric data type whose storage type varies based on a specified precision.

DECLARE
Command used to create variables on the fly within a script.

DELETE FROM
Command that deletes rows in a table that may satisfy a given condition.

DESC
Function used in ORDER BY to put a SQL result set into descending order.

DISTINCT
Result set function that omits rows that contain duplicate data.

DROP COLUMN
Command used to delete a column in a table.

DROP CONSTRAINT
Command used to delete a named constraint.

DROP INDEX
Command used to delete an index.

DROP SYNONYM
Command used to delete a synonym.

DROP TABLE
Command used to delete a table.

DROP VIEW
Command used to delete a view.

EXISTS
A keyword in a SQL statement that returns true in a WHERE clause if the subquery following it returns at least one row.

FLOAT
Decimal data type that has a precision of 15 digits.

FLOOR
Row-level function which returns the next lower integer value when a number contains decimal places.

GEtdATE
Date function that returns the current system date and time.

GROUP BY
Produces one summary row for the aggregate value of all values for a given column.

GUID
Global unique identifier; UNIQUEIDENTIFIER data type guarantees worldwide uniqueness, even among unconnected computers.

HAVING
Part of a SQL statement that is used to determine which groups of a GROUP BY will be included in the result set.

IMAGE
Large object binary data type; used to store pictures.

IN
Logical operator for a WHERE clause that tests for inclusion in a named set.

INT
Integer data type that can store numbers from -231 to 231 - 1.

INDEX BY
Command used to create an index on a table by a certain column value.

INNER JOIN
Command used to combine two tables in an equi-join operation.

INSERT INTO.. SELECT
A way to insert many rows into a new table at one time.

INSERT INTO..VALUES
A way to insert values into a table one row at a time.

INSERT
Command that allows for the addition of new rows to a table.

INTEGER
Numeric data type that has no digits after the decimal point.

INTERSECT
Set operation that combines two queries such that it returns all rows that are the same in both result sets.

IS NOT NULL
Function that tests for the NOT NULL condition.

ISNULL
Function that returns a true value if a data item contains a null.

JOIN
Command used to join two tables; synonymous with INNER JOIN.

LEFT
String function that returns the left portion of a string up to a given number of characters.

LEFT JOIN
Same as LEFT OUTER JOIN .

LEFT OUTER JOIN
A join where all the rows from the first (left) table are kept in the result set, regardless of whether they have matching rows in the second (right) table.

LEN
String function that returns the length of a string.

LIKE
A WHERE clause option that matches a particular pattern.

LONG
Oracle equivalent of TEXT data type.

LOWER
String function used to convert a string to lowercase.

LTRIM
String function that removes blanks or other named character from the beginning of a string.

MAX
Aggregate function that returns the highest of all values from a column in a set of rows.

MIN
Aggregate function that returns the lowest of all values from a column in a set of rows.

MINUS
Set operation that returns only those rows from the result of the first query that are not in the result of the second query; not available in SQL Server.

MONEY
Data type used with currency data.

MONTH
Date function that extracts the month from a date.

NATIONAL CHARACTER
A data type; synonym for NCHAR data type.

NCHAR
Fixed-length Unicode character data type.

NOT
Operator that reverses the effect of any logical operator such as IN, LIKE, and EXISTS.

NOT BETWEEN
Operator that allows you to determine whether a value does not occur within a given range of values.

NOT EXISTS
Operator that returns true in a WHERE clause if the subquery following it returns no rows.

NOT NULL
Operator that returns true if an attribute has a non-null value.

NOT NULL Constraint
Integrity constraint that denies the creation of a row when an attribute has a null value.

NULL
Value that is unknown.

NULLIF
Function that returns a NULL if a certain condition is met in an expression.

NUMERIC
Synonym for DECIMAL data type.

NVARCHAR
Variable-length Unicode character data type.

OR
Binary logical operator that returns a true value if either one of the expressions is true.

ORDER BY
Clause that sorts the results of a query before they are displayed.

OUTER JOIN
Join where rows from a table are kept in the result set although there is no matching row in the other table used in the join.

PERCENT
Function that is used to return a certain percentage of records that fall at the top of a range specified.

PRIMARY KEY
Constraint used to create a primary key in a table; used in CREATE TABLE and ALTER TABLE commands.

REAL
Decimal data type that has a precision of seven digits.

REFERENCES
Constraint part that defines the table name and key used to reference another table.

RIGHT
String function that returns the right portion of a string.

RIGHT JOIN
Same as RIGHT OUTER JOIN .

RIGHT OUTER JOIN
Join where all the rows from the second (right) relation are kept whether matched or not in a join operation.

ROUND
Function used to round numbers to a specified number of decimal places.

ROWCOUNT(n)
Function that returns the first n rows.

RTRIM
String function that removes blanks from the right end of a string.

SELECT
Command that allows you to retrieve rows from tables (or views) in a database.

SET
Command used to assign values to variables.

SET DATEFORMAT
Date function used to change the format in which SQL Server reads in dates.

SMALLDATETIME
Data type used to store dates.

SMALLINT
Integer data type that can store numbers between -215 to 215 - 1.

SMALLMONEY
Data type that can be used with currency data.

SQUARE
Row-level function that returns the square of a number.

SQL_VARIANT
Data type used to store values of any data type except TEXT or IMAGE.

SQRT
Row-level function that returns the square root of positive numeric values.

STR
Conversion function that always converts from a number to a character data type.

SUBSTRING
String function that returns part of a string.

SUM
Group function that adds up all the values for a column value in a set of rows.

TABLE
A two-dimensional (row by column) arrangement of data.

TEXT
Character large object data type.

TINYINT
Integer data type that can store numbers between 0 and 255.

TOP
Function that returns a specified number of records from the top of a result set.

UNION
Set operation that combines two queries such that it returns all distinct rows for the result sets of both queries. The two queries must have union-compatible result sets.

UNION ALL
Set operation that combines two queries and returns all rows from both the SELECT statements (queries). A UNION ALL also includes duplicate rows. The two queries must have union-compatible result sets.

UNIQUE
Integrity constraint that disallows duplicate entries for an attribute even though the column is not a primary key.

UNIQUEIDENTIFIER
Data type that guarantees uniqueness of the identifier, even among unconnected computers.

UPDATE
Command that changes values in specified columns in specified tables .

UPPER
String function used to display all output in uppercase.

USE
Command used to open a database.

UUID
Universal unique identifier; the UNIQUEIDENTIFIER data type that guarantees uniqueness, even among unconnected computers.


VARBINARY
Data type used to store variable-length binary data.

VARCHAR
Character data type used when the field length is varying.

VARCHAR2
Oracle equivalent of VARCHAR.

WHERE
Row filter part of a SQL statement that allows you to specify criteria on column values for rows that are being selected from a table.

WITH TIES
Clause used with the TOP function to retrieve rows that are ties.

XML
A new SQL Server data type used to model complex data.


YEAR
Date function that extracts the year from a date.

No comments: