Transact-SQL Commands, Clauses, and Predicates

Following are the core components of the Transact-SQL language. New commands for SQL Server 2005 are explicitly called out in this section.

WITH

This is a new method in SQL Server 2005 for defining an alias for the result set returned by a SELECT expression.

 WITH MyCTE  AS ( SELECT * FROM Product WHERE ListPrice < 1000 ) 

Optionally, column aliases can be defined in parentheses following the Common Table Expression (CTE) name:

 WITH MyCTE ( ID, ProdNumber, ProdName, Price ) AS ( SELECT  ProductID , ProductNumber , Name , ListPrice  FROM Product WHERE ListPrice < 1000 ) 

SELECT

Return all columns from a table or view:

 SELECT * FROM table_name 

Return specific columns from a table or view:

 SELECT Column1, Column2, Column3 FROM table_name 

Column alias techniques:

 SELECT Column1 AS Col1, Column2 AS Col2 FROM table_name  SELECT Column1 Col1, Column2 Col2 FROM table_name  SELECT Col1 = Column1, Col2 = Column2 FROM table_name 

Literal values:

 SELECT 'Some literal value' SELECT 'Some value' AS Col1, 123 AS Col2 

Returning an expression value:

 SELECT (1 + 2) * 3 

Returning the result of a function call:

 SELECT CONVERT( VarChar(20), GETDATE(), 101 ) 

TOP

Return a fixed number of rows:

 SELECT TOP 10 * FROM table_name ORDER BY Column1  SELECT TOP 10 Column1, Column2 FROM table_name ORDER BY Column2 

Return a fixed number of rows with the ties for last position:

 SELECT TOP 10 WITH TIES Column1, Column2 FROM table_name ORDER BY Column2 

Return a percentage of all available rows:

 SELECT TOP 25 PERCENT * FROM table_name ORDER BY Column2  SELECT TOP 25 PERCENT Column1, Column2 FROM table_name ORDER BY Column2 

For SQL Server 2005 only, substitute a variable or expression for a top values number:

 DECLARE @TopNumber Int SET @TopNumber = 15 SELECT TOP @ TopNumber * FROM table_name ORDER BY Column2 

Top values based on an expression:

 SELECT TOP (SELECT a_column_value FROM some_table) * FROM another_table 

SELECT INTO

Create and populate a table from a result set:

 SELECT Column1, Column2 INTO new_table_name FROM existing_table_or_view_name 

FROM

Single table query:

 SELECT * FROM table_name 

Multi-table join query:

 SELECT *  FROM table1.key_column INNER JOIN table2 ON table1.key_column = table2.key_column 

Derived table:

 SELECT DerTbl.Column1, DerTbl.Column2  FROM  ( SELECT Column1, Column2 FROM some_table ... ) AS DerTbl 

WHERE

Exact match:

 SELECT ... FROM ... WHERE Column1 = 'A literal value' 

Not NULL:

 SELECT ... FROM ... WHERE Column1 IS NOT NULL 

Any trailing characters:

 SELECT ... FROM ... WHERE Column1 LIKE 'ABC%' 

Any leading characters:

 SELECT ... FROM ... WHERE Column1 LIKE '%XYZ' 

Any leading or trailing characters:

 SELECT ... FROM ... WHERE Column1 LIKE '%MNOP%' 

Placeholder wildcard:

 SELECT ... FROM ... WHERE Column1 LIKE '_BC_EF' 

Criteria using parentheses to designate order:

 SELECT ... FROM ... WHERE  (Column1 LIKE 'ABC%' AND Column2 LIKE '%XYZ')  OR Column3 = '123' 

GROUP BY

All non-aggregated columns in the SELECT list must be included in the GROUP BY list:

 SELECT COUNT(Column1), Column2, Column3  FROM ... WHERE ... GROUP BY Column2, Column3 

Designating order:

 SELECT COUNT(Column1), Column2, Column3  FROM ... WHERE ... GROUP BY Column2, Column3 ORDER BY Column2 DESC, Column3 ASC 

HAVING

Filter results based on values available after the aggregations and groupings are performed:

 SELECT COUNT(Column1), Column2, Column3  FROM ... WHERE ... GROUP BY Column2, Column3 HAVING COUNT(Column1) > 5 

UNION

Combine multiple results with the same column count:

 SELECT Column1, Column2 FROM table1_name  UNION SELECT Column1, Column2 FROM table2_name 

Combine literal values and query results:

 SELECT -1 AS Column1, 'A literal value' AS Column2  UNION SELECT Column1, Column2 FROM table1_name 

Include non-distinct selection (UNION performs SELECT DISTINCT by default):

 SELECT Column1, Column2 FROM table1_name  UNION ALL SELECT Column1, Column2 FROM table2_name 

EXCEPT and INTERSECT

Select the differences (EXCEPT) or common values (INTERSECT) between two queries:

 SELECT * FROM TableA EXCEPT SELECT * FROM TableB SELECT * FROM TableA INTERSECT SELECT * FROM TableB 

ORDER BY

Order a result set by one or more column values. The default order is ascending. If ordering by more than one column, each column can have a different order.

 SELECT * FROM table_name ORDER BY Column1 SELECT * FROM table_name ORDER BY Column1 DESC, Column2 ASC 

COMPUTE (BY)

The COMPUTE and COMPUTE BY clauses generate totals that are appended to the end of an aggregate query result set. These clauses are not very useful in applications because the aggregated results are not in relational form and cannot be utilized in a dataset.

 SELECT * FROM table_name ORDER BY Column1 SELECT * FROM table_name ORDER BY Column1 DESC, Column2 ASC 

FOR Clause

The FOR clause is used with either the XML or BROWSE option in a SELECT statement. However, the BROWSE and XML options are completely unrelated. FOR XML specifies that the result set is returned in XML format. FOR BROWSE is used when accessing data through the DB-Library so that rows can be browsed and updated one row at a time in an optimistic locking environment. There are several requirements when using the FOR BROWSE option. For more information consult the SQL Server Books Online under the topic "Browse Mode."

 SELECT * FROM table_name FOR XML {XML Option} SELECT * FROM table_name FOR BROWSE 

OPTION Clause

The OPTION clause is used in a SELECT statement to provide a query hint that will override the query optimizer and specify an index or specific join mechanism to be used along with other hint options.

CASE

CASE evaluates one or more expressions and returns one or more specified values based on the evaluated expression:

 SELECT expression = CASE Column WHEN value THEN resultant_value  WHEN value2 THEN resultant_value2 . . .  ELSE alternate_value END FROM table SELECT value =  CASE           WHEN column IS NULL THEN value WHEN column {expression true} THEN different_value  WHEN column {expression true} and price {expression true} THEN other_value  ELSE different_value END, column2 FROM table 

INSERT

Adds a new row to a table:

 INSERT table (column list) VALUES (column values) INSERT table  SELECT columns FROM source expression INSERT table EXEC stored_procedure 

UPDATE

Updates selected columns in a table:

 UPDATE table SET column1 = expression1, column2 = expression2  WHERE filter_expression 

Update a table based on the contents of another table:

 UPDATE table SET column1 = expression  FROM table INNER JOIN table2 ON table.column = table2.column  WHERE table.column = table2.column 

DELETE

Deletes selected rows from a table:

 DELETE table  WHERE filter_expression 

Deletes rows from a table based on the contents of a different table:

 DELETE table  FROM table INNER JOIN table2  ON table.column = table2.column  WHERE column = filter_expression 

DECLARE @local_variable

This creates a named object that temporarily holds a value with the data type defined in the declaration statement. Local variables have scope only within the calling batch or stored procedure. The value of a local variable can be set with either a SET or SELECT operation. SELECT is more efficient and has the advantage of populating multiple variables in a single operation, but the SELECT operation cannot be confined with any data retrieval operation.

 DECLARE @local_variable AS int SET @local_variable = integer_expression DECLARE @local_variable1 AS int, @local_variable2 AS varchar(55)  SELECT @local_variable1 = integer_column_expression, @local_variable2 =  character_column_expression FROM table 

SET

The SET operator has many functions, from setting the value of a variable to setting a database or connection property. The SET operator is divided into the categories listed in the following table.

Category	Alters the Current Session Settings For
Date and time	Handling date and time data
Locking	Handling Microsoft(r) SQL Server locking
Miscellaneous	Miscellaneous SQL Server functionality
Query execution	Query execution and processing
SQL-92 settings	Using the SQL-92 default settings
Statistics	Displaying statistics information
Transactions	Handling SQL Server transactions

LIKE

LIKE is a pattern-matching operator for comparing strings or partial strings.

Compare a string value where the compared string is anywhere in the string:

 SELECT * FROM table WHERE column1 LIKE '%string%' 

Compare a string value where the compared string is at the beginning of the string:

 SELECT * FROM table WHERE column1 LIKE 'string%' 

Compare a string value where the compared string is at the end of the string:

 SELECT * FROM table WHERE column1 LIKE '%string' 

Compare a string value where a specific character or character range is in the string:

 SELECT * FROM table WHERE column1 LIKE '[a-c]'  SELECT * FROM table WHERE column1 LIKE '[B-H]olden' 

Compare a string value where a specific character or character range is not in the string:

 SELECT * FROM table WHERE column1 LIKE '[M^c]%' –Begins with M but not Mc 

ALTER TABLE

Alter the structure of a table by adding or removing table objects such as Constraints, Columns, and Partitions or enabling and disabling Triggers:

 ALTER TABLE table_name ADD new_column int NULL ALTER TABLE table_name ADD CONSTRAINT new_check CHECK (check expression) ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name 

CREATE DATABASE

Create a database and all associated files:

 CREATE DATABASE new_database ON (  NAME = ‘logical_name’, FILENAME = ‘physical_file_location’, SIZE = initial_size_in_MB, MAXSIZE = max_size_in_MB, --If no MAXSIZE specified unlimited growth is assumed FILEGROWTH = percentage_OR_space_in_MB) LOG ON ( NAME = ‘logical_log_name’, FILENAME = ‘physical_file_location’, SIZE = initial_size_in_MB, MAXSIZE = max_size_in_MB, --If no MAXSIZE specified unlimited growth is assumed FILEGROWTH = percentage_OR_space_in_MB) COLLATE database_collation 

CREATE DEFAULT

Create a database-wide default value that can then be bound to columns in any table to provide a default value:

 CREATE DEFAULT default_name AS default_value --bind the default to a table column sp_bindefault default_name, ‘table.column’ 

CREATE PROCEDURE

Create a new stored procedure:

 CREATE PROC proc_name @variable variable_data_type ...n AS ...procedure code 

CREATE RULE

Create a database-wide rule, much like a Check Constraint, that can then be bound to individual columns in tables throughout the database:

 CREATE RULE rule_name AS rule_expression --bind the Rule to a table column sp_bindrule rule_name, ‘table.column’ 

CREATE TABLE

Create a new table:

 CREATE TABLE table_name ( Column1 data_type nullability column_option, Column2 data_type nullability column_option, Column3 data_type nullability column_option, --Column_option = Collation, IDENTITY, KEY... 

Create a new partitioned table:

 CREATE TABLE partitioned_table_name (col1 int, col2 char(10)) Column1 data_type nullability column_option, Column2 data_type nullability column_option, Column3 data_type nullability column_option ON partition_scheme_name (column)  

CREATE TRIGGER

Create a new trigger on a table that fires AFTER a DML event or INSTEAD OF a DML event:

 CREATE TRIGGER trigger_name ON table_name FOR dml_action –INSERT, UPDATE or DELETE AS ...trigger_code CREATE TRIGGER trigger_name ON view_or_table_name INSTEAD OF dml_action –INSERT, UPDATE or DELETE AS ...trigger_code 

CREATE VIEW

Creates a new view:

 CREATE VIEW view_name AS  ...Select Statement 

CREATE SCHEMA

Creates a new schema in SQL Server 2005 with the option of specifying a non-dbo owner with the AUTHORIZATION clause:

 CREATE SCHEMA schema_name AUTHORIZATION user_name 

CREATE PARTITION FUNCTION

Creates a partition function in SQL Server 2005 to use in physically partitioning tables and indexes:

 CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) AS RANGE LEFT –-or RIGHT  FOR VALUES (value1, value2, value3, ...n) 

CREATE PARTITION SCHEME

Creates a partition scheme in SQL Server 2005 to use in physically partitioning tables and indexes:

 CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name TO (filegroup1, filefroup2, filefroup3, ...n) 

Script Comment Conventions

In-line comment:

 SELECT ProductID, Name AS ProductName -- Comment text 

Single-line comment:

 /* Comment text */ -- Comment text 

Comment block:

 /*************************************************** spProductUpdateByCategory Created by Paul Turley, 5-21-06 [e-mail] Updates product price info for a category Revisions: 5-22-06 - Fixed bug that formatted C:  drive if wrong type was passed in. ****************************************************/ 

Reserved Words

Chapter 12 gave some recommendations and guidance around the naming of objects in SQL Server. One of the recommendations was that reserved words should not be used as names of objects. Reserved words are typically easy to see in both Query Analyzer and SQL Server Management Studio. Both these tools change the color of reserved words to blue, but for whatever reason, not all reserved words are recognized by Query Analyzer and Management Studio and color-coded. To make matters worse, some words are color-coded blue even when they are not really reserved words. Also, if the object names are delimited with double-quotes or square brackets, which they often are if using a graphical tool to create queries, then they won't show up color-coded at all. However, use of a non-delimited reserved word, whether or not it is blue, will always cause a syntax error to be raised. You will know when you have placed a non-delimited reserved word in your script when you receive the error "Incorrect syntax near the keyword 'keyword'." Keep in mind that if the decision is made to use a keyword in an object name, you will be forced to delimit that keyword every time it is used in the future.

The following keywords have significant meaning within Transact-SQL and should be avoided in object names and expressions. If any of these words must be used in a SQL expression, they must be contained within square brackets [ ].

ADD	EXCEPT	PERCENT
ALL	EXEC	PLAN
ALTER	EXECUTE	PRECISION
AND	EXISTS	PRIMARY
ANY	EXIT	PRINT
AS	FETCH	PROC
ASC	FILE	PROCEDURE
AUTHORIZATION	FILLFACTOR	PUBLIC
BACKUP	FOR	RAISERROR
BEGIN	FOREIGN	READ
BETWEEN	FREETEXT	READTEXT
BREAK	FREETEXTTABLE	RECONFIGURE
BROWSE	FROM	REFERENCES
BULK	FULL	REPLICATION
BY	FUNCTION	RESTORE
CASCADE	GOTO	RESTRICT
CASE	GRANT	RETURN
CHECK	GROUP	REVOKE
CHECKPOINT	HAVING	RIGHT
CLOSE	HOLDLOCK	ROLLBACK
CLUSTERED	IDENTITY	ROWCOUNT
COALESCE	IDENTITY_INSERT	ROWGUIDCOL
COLLATE	IDENTITYCOL	RULE
COLUMN	IF	SAVE
COMMIT	IN	SCHEMA
COMPUTE	INDEX	SELECT
CONSTRAINT	INNER	SESSION_USER
CONTAINS	INSERT	SET
CONTAINSTABLE	INTERSECT	SETUSER
CONTINUE	INTO	SHUTDOWN
CONVERT	IS	SOME
CREATE	JOIN	STATISTICS
CROSS	KEY	SYSTEM_USER
CURRENT	KILL	TABLE
CURRENT_DATE	LEFT	TEXTSIZE
CURRENT_TIME	LIKE	THEN
CURRENT_TIMESTAMP	LINENO	TO
CURRENT_USER	LOAD	TOP
CURSOR	NATIONAL	TRAN
DATABASE	NOCHECK	TRANSACTION
DBCC	NONCLUSTERED	TRIGGER
DEALLOCATE	NOT	TRUNCATE
DECLARE	NULL	TSEQUAL
DEFAULT	NULLIF	UNION
DELETE	OF	UNIQUE
DENY	OFF	UPDATE
DESC	OFFSETS	UPDATETEXT
DISK	ON	USE
DISTINCT	OPEN	USER
DISTRIBUTED	OPENDATASOURCE	VALUES
DOUBLE	OPENQUERY	VARYING
DROP	OPENROWSET	VIEW
DUMMY	OPENXML	WAITFOR
DUMP	OPTION	WHEN
ELSE	OR	WHERE
END	ORDER	WHILE
ERRLVL	OUTER	WITH
ESCAPE	OVER	WRITETEXT

ODBC Reserved Words

Although ODBC keywords are not strictly prohibited, as a best practice to prevent driver inconsistencies, they should be avoided. These are listed in the following table.

ABSOLUTE	EXEC	OVERLAPS
ACTION	EXECUTE	PAD
ADA	EXISTS	PARTIAL
ADD	EXTERNAL	PASCAL
ALL	EXTRACT	POSITION
ALLOCATE	FALSE	PRECISION
ALTER	FETCH	PREPARE
AND	FIRST	PRESERVE
ANY	FLOAT	PRIMARY
ARE	FOR	PRIOR
AS	FOREIGN	PRIVILEGES
ASC	FORTRAN	PROCEDURE
ASSERTION	FOUND	PUBLIC
AT	FROM	READ
AUTHORIZATION	FULL	REAL
AVG	GET	REFERENCES
BEGIN	GLOBAL	RELATIVE
BETWEEN	GO	RESTRICT
BIT	GOTO	REVOKE
BIT_LENGTH	GRANT	RIGHT
BOTH	GROUP	ROLLBACK
BY	HAVING	ROWS
CASCADE	HOUR	SCHEMA
CASCADED	IDENTITY	SCROLL
CASE	IMMEDIATE	SECOND
CAST	IN	SECTION
CATALOG	INCLUDE	SELECT
CHAR	INDEX	SESSION
CHAR_LENGTH	INDICATOR	SESSION_USER
CHARACTER	INITIALLY	SET
CHARACTER_LENGTH	INNER	SIZE
CHECK	INPUT	SMALLINT
CLOSE	INSENSITIVE	SOME
COALESCE	INSERT	SPACE
COLLATE	INT	SQL
COLLATION	INTEGER	SQLCA
COLUMN	INTERSECT	SQLCODE
COMMIT	INTERVAL	SQLERROR
CONNECT	INTO	SQLSTATE
CONNECTION	IS	SQLWARNING
CONSTRAINT	ISOLATION	SUBSTRING
CONSTRAINTS	JOIN	SUM
CONTINUE	KEY	SYSTEM_USER
CONVERT	LANGUAGE	TABLE
CORRESPONDING	LAST	TEMPORARY
COUNT	LEADING	THEN
CREATE	LEFT	TIME
CROSS	LEVEL	TIMESTAMP
CURRENT	LIKE	TIMEZONE_HOUR
CURRENT_DATE	LOCAL	TIMEZONE_MINUTE
CURRENT_TIME	LOWER	TO
CURRENT_TIMESTAMP	MATCH	TRAILING
CURRENT_USER	MAX	TRANSACTION
CURSOR	MIN	TRANSLATE
DATE	MINUTE	TRANSLATION
DAY	MODULE	TRIM
DEALLOCATE	MONTH	TRUE
DEC	NAMES	UNION
DECIMAL	NATIONAL	UNIQUE
DECLARE	NATURAL	UNKNOWN
DEFAULT	NCHAR	UPDATE
DEFERRABLE	NEXT	UPPER
DEFERRED	NO	USAGE
DELETE	NONE	USER
DESC	NOT	USING
DESCRIBE	NULL	VALUE
DESCRIPTOR	NULLIF	VALUES
DIAGNOSTICS	NUMERIC	VARCHAR
DISCONNECT	OCTET_LENGTH	VARYING
DISTINCT	OF	VIEW
DOMAIN	ON	WHEN
DOUBLE	ONLY	WHENEVER
DROP	OPEN	WHERE
ELSE	OPTION	WITH
END	OR	WORK
END-EXEC	ORDER	WRITE
ESCAPE	OUTER	YEAR
EXCEPT	OUTPUT	ZONE
EXCEPTION

Future Reserved Words

The following table contains keywords that may be reserved in future editions of SQL Server.

ABSOLUTE	FOUND	PRESERVE
ACTION	FREE	PRIOR
ADMIN	GENERAL	PRIVILEGES
AFTER	GET	READS
AGGREGATE	GLOBAL	REAL
ALIAS	GO	RECURSIVE
ALLOCATE	GROUPING	REF
ARE	HOST	REFERENCING
ARRAY	HOUR	RELATIVE
ASSERTION	IGNORE	RESULT
AT	IMMEDIATE	RETURNS
BEFORE	INDICATOR	ROLE
BINARY	INITIALIZE	ROLLUP
BIT	INITIALLY	ROUTINE
BLOB	INOUT	ROW
BOOLEAN	INPUT	ROWS
BOTH	INT	SAVEPOINT
BREADTH	INTEGER	SCROLL
CALL	INTERVAL	SCOPE
CASCADED	ISOLATION	SEARCH
CAST	ITERATE	SECOND
CATALOG	LANGUAGE	SECTION
CHAR	LARGE	SEQUENCE
CHARACTER	LAST	SESSION
CLASS	LATERAL	SETS
CLOB	LEADING	SIZE
COLLATION	LESS	SMALLINT
COMPLETION	LEVEL	SPACE
CONNECT	LIMIT	SPECIFIC
CONNECTION	LOCAL	SPECIFICTYPE
CONSTRAINTS	LOCALTIME	SQL
CONSTRUCTOR	LOCALTIMESTAMP	SQLEXCEPTION
CORRESPONDING	LOCATOR	SQLSTATE
CUBE	MAP	SQLWARNING
CURRENT_PATH	MATCH	START
CURRENT_ROLE	MINUTE	STATE
CYCLE	MODIFIES	STATEMENT
DATA	MODIFY	STATIC
DATE	MODULE	STRUCTURE
DAY	MONTH	TEMPORARY
DEC	NAMES	TERMINATE
DECIMAL	NATURAL	THAN
DEFERRABLE	NCHAR	TIME
DEFERRED	NCLOB	TIMESTAMP
DEPTH	NEW	TIMEZONE_HOUR
DEREF	NEXT	TIMEZONE_MINUTE
DESCRIBE	NO	TRAILING
DESCRIPTOR	NONE	TRANSLATION
DESTROY	NUMERIC	TREAT
DESTRUCTOR	OBJECT	TRUE
DETERMINISTIC	OLD	UNDER
DICTIONARY	ONLY	UNKNOWN
DIAGNOSTICS	OPERATION	UNNEST
DISCONNECT	ORDINALITY	USAGE
DOMAIN	OUT	USING
DYNAMIC	OUTPUT	VALUE
EACH	PAD	VARCHAR
END-EXEC	PARAMETER	VARIABLE
EQUALS	PARAMETERS	WHENEVER
EVERY	PARTIAL	WITHOUT
EXCEPTION	PATH	WORK
EXTERNAL	POSTFIX	WRITE
FALSE	PREFIX	YEAR
FIRST	PREORDER	ZONE
FLOAT	PREPARE