Important Commands and Functions(Sql Server 2005)

Important Commands and Functions

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.