APPENDIX-T-SQL Reference


This appendix provides brief explanations of the T-SQL constructions such as functions, general commands, and system stored procedures. It essentially summarizes T-SQL reference information, providing a quick look-up facility. The full text and usage code are available in Books Online. These commands can be used in your application, in Query Analyzer, Enterprise Manager, and from the command-line utilities such as ISQL and OSQL. They represent the bulk of the most frequently used facilities provided by T-SQL. Many more procedures are available, such as the extended stored procedures, but they are less frequently used and can be referenced in SQL Server Books Online. The full explanation and usage of arguments and parameters is accessible from SQL Server Books Online.

T-SQL Construct

Usage, Usage Tips, Suggestions, and References

+ (Add)

Adds two numbers. This addition arithmetic operator can also add a number, in days, to a date.

+ (Positive)

A unary operator that returns the positive value of a numeric expression.

+ (String Concatenation)

An operator in a string expression that concatenates two or more character or binary strings or columns, or else a combination of strings and column names into one expression.

(Negative)

A unary operator that returns the negative value of a numeric expression.

(Subtract)

The arithmetic operator subtracts two numbers and can also subtract a number of days from a date.

* (Multiply)

The arithmetic multiplication operator multiplies two expressions.

/ (Divide)

The arithmetic division operator divides one number by another.

% (Modulo)

Provides the remainder of one number divided by another.

% (Wildcard- Character(s) to Match)

Matches any string of zero or more characters and can be used as either a prefix or a suffix.

& (Bitwise AND)

Performs the bitwise logical AND operation between two integer values.

| (Bitwise OR)

Performs the bitwise logical OR operation between two given integer values as translated to binary expressions within Transact-SQL statements.

^ (Bitwise Exclusive OR)

Performs the bitwise exclusive OR operation between two given integer values as translated to binary expressions within Transact-SQL statements.

~ (Bitwise NOT)

Performs a bitwise logical NOT operation for one given integer value as translated to binary expressions within Transact-SQL statements.

= (Equal)

A comparison operator that compares two expressions for equality. When you compare nonnull expressions, the result is TRUE if both operands are equal; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_ NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

> (Greater Than)

A companson operator that compares two expressions to determine if the left value is the greater value. When you compare nonnull expressions, the result is TRUE if the left operand has a higher value than the right operand; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

< (Less Than)

A comparison operator that compares two expressions to determine if the left value is the lesser value. When you compare nonnull expressions, the result is TRUE if the left operand has a lower value than the right operand; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

>= (Greater Than or Equal To)

A comparison operator that compares two expressions to determine if the left value is greater than or equal to the right value. When you compare nonnull expressions, the result is TRUE if the left operand has a higher or equal value than the right operand; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

<= (Less Than or Equal To)

A comparison operator that compares two expressions to determine if the left value is less than or equal to the right value. When you compare nonnull expressions, the result is TRUE if the left operand has a lower or equal value than the right operand; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

<> (Not Equal To)

A comparison operator that compares two expressions to determine if the left value is not equal to the right value. When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

!< (Not Less Than)

A comparison operator that compares two expressions to determine if the left value is not less than greater value. When you compare nonnull expressions, the result is TRUE if the left operand does not have a lower value than the right operand; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

!= (Not Equal To)

A comparison operator that compares two expressions to determine if the left value is not equal to the right value. Functions the same as the Not Equal To (<>) comparison operator.

!> (Not Greater Than)

A comparison operator that compares two expressions to determine if the left value is not greater than the right value. When you compare nonnull expressions, the result is TRUE if the left operand does not have a higher value than the right operand; otherwise, the result is FALSE. If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

- - (Comment)

Indicates user-provided text that is not evaluated by SQL Server. Comments can be inserted on a separate line, nested (- - only) at the end of a Transact-SQL command line, or within a Transact-SQL statement. Two hyphens (- -) is the SQL-92 ((or later)) standard indicator for comments.

/**/ (Comment)

Indicates user-provided text that is not evaluated by SQL Server. The text between the /* and */ commenting characters is ignored by SQL Server.

[ ] (Wildcard-Character(s) to Match)

The wildcard characters are used to match any single character within the specified range or set that is specified inside the square brackets.

[^] (Wildcard-Character(s) Not to Match)

The wildcard characters are used not to match any single character within the specified range or set that is specified inside the square brackets.

_ (Wildcard-Match One Character)

The underscore can be used to match any single character, and can be used as either a prefix or a suffix. The underscore can also be replaced by another wildcard if it exists in the values you are scanning, for example jeff_ shapiro@sql2ktcr.book.

$PARTITION

This construct returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.

@@CONNECTIONS

This function returns the number of connections, or attempted connections, that have occurred since SQL Server was last started.

@@CPU_BUSY

This function returns the time in milliseconds-based on the resolution of the system timer-that the CPU has spent working since SQL Server was last started.

@@CURSOR_ROWS

This function returns the number of qualifying rows currently in the last cursor opened on your connection. To improve performance, SQL Server can populate large keysets and static cursors asynchronously. @@CURSOR_ROWS can be called to determine that the number of the rows that quality for a cursor are retrieved at the time @@CURSOR_ROWS is called.

@@DATEFIRST

This function returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.

@@DBTS

This function returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database.

@@ERROR

This function returns the error number for the last Transact-SQL statement you executed.

@@FETCH_STATUS

This function returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

@@IDENTITY

This function returns the last inserted identity value.

@@IDLE

This function returns the time in milliseconds (based on the resolution of the system timer) that SQL Server has been idle since it was last started.

@@IO_BUSY

This function returns the time in milliseconds (based on the resolution of the system timer) that SQL Server has spent performing input and output operations since it was last started.

@@LANGID

This function returns the local language identifier (ID) of the language currently in use.

@@LANGUAGE

This function returns the name of the language currently in use.

@@LOCK_TIMEOUT

This function returns the current lock time-out setting, in milliseconds, for the current session.

@@MAX_CONNECTIONS

This function returns the maximum number of simultaneous user connections allowed on a SQL Server. The number returned is not necessarily the number currently configured.

@@MAX_PRECISION

This function returns the precision level used by decimal and numeric data types as currently set in the server.

@@NESTLEVEL

This function returns the nesting level of the current stored procedure execution (initially 0).

@@OPTIONS

This function returns information about current SET options.

@@PACK_RECEIVED

This function returns the number of input packets read from the network by SQL Server since last started.

@@PACK_SENT

This function returns the number of output packets written to the network by SQL Server since last started.

@@PACKET_ERRORS

This function returns the number of network packet errors that have occurred on SQL Server connections since SQL Server was last started.

@@PROCID

This function returns the stored procedure identifier (ID) of the current procedure.

@@REMSERVER

This function returns the name of the remote SQL Server database server as it appears in the login record.

@@ROWCOUNT

This function returns the number of rows affected by the last statement.

@@SERVERNAME

This function returns the name of the local server running SQL Server.

@@SERVICENAME

This function returns the name of the Registry key under which SQL Server is running. @@SERVICENAME returns MSSQLServer if the current instance is the default instance. The function also returns the instance name if the current instance is a named instance.

@@SPID

This function returns the server process identifier (ID) of the current user process.

@@TEXTSIZE

This function returns the current value of the TEXTSIZE option of the SET statement, which specifies the maximum length, in bytes, of text or image data that a SELECT statement returns.

@@TIMETICKS

This function returns the number of microseconds per tick.

@@TOTAL_ERRORS

This function returns the number of disk read/write errors encountered by SQL Server since it was last started.

@@TOTAL_READ

This function returns the number of disk reads (not cache reads) by SQL Server since it was last started.

@@TOTAL_WRITE

This function returns the number of disk writes by SQL Server since it was last started.

@@TRANCOUNT

This function returns the number of active transactions for the current connection.

@@VERSION

This function returns the date, version, and processor type for the current installation of SQL Server 2005.

ABS

This function returns the absolute, positive value of the given numeric expression.

ACOS

This function returns the angle, in radians, whose cosine is the given float expression; also called arccosine.

ALL

This compares a scalar value with a single-column set of values.

ALTER APPLICATION ROLE

This changes the name, password, or default schema of an application role.

ALTER ASSEMBLY

This changes a .NET assembly by modifying the SQL Server catalog properties of an assembly.

ALTER ASYMMETRIC KEY

This changes the properties of an asymmetric key.

ALTER AUTHORIZATION

Changes ownership.

ALTER CERTIFICATE

Changes the properties of a certificate.

ALTER CREDENTIAL

Changes the properties of a credential.

ALTER DATABASE

Adds or removes files and filegroups from a database. Can also be used to modify the attributes of files and filegroups, such as changing the name or size of a file. ALTER DATABASE provides the capability to change the database name, the filegroup names, and the logical names of data files and log files.

ALTER ENDPOINT

Enables modifying an existing endpoint by adding a new method to an existing endpoint; or by modifying or dropping an existing method from the endpoint; or by changing the properties of an endpoint.

ALTER FULLTEXT CATALOG

Changes the properties of a fulltext catalog.

ALTER FULLTEXT INDEX

Changes properties of a fulltext index.

ALTER FUNCTION

Alters an existing user-defined function, which was first created by executing the CREATE FUNCTION statement. It does not change permissions or any dependent functions, stored procedures, or triggers. For more information about the parameters used in the ALTER FUNCTION statement, also see the CREATE FUNCTION statement.

ALTER INDEX

Used for modifying an index in a view or table, either XML or relational.

ALTER LOGIN

Lets you changes the properties of a login.

ALTER MASTER KEY

Changes the properties of a master key.

ALTER MESSAGE TYPE

Changes the properties of message type.

ALTER PARTITION FUNCTION

Alters a partition function by splitting or merging its boundary values.

ALTER PARTITION SCHEME

Alters the properties of a partition scheme.

ALTER PROCEDURE

Alters a previously created procedure, which is created by executing the CREATE PROCEDURE statement. It does not change permissions or affect any dependent stored procedures or triggers. For more information about the parameters used in the ALTER PROCEDURE statement, see the CREATE PROCEDURE statement.

ALTER QUEUE

Alters the properties of a queue.

ALTER REMOTE SERVICE BINDING

This changes the user associated with a remote service binding. You can also change the anonymous authentication setting for the binding.

ALTER ROLE

This lets you change the name of the role.

ALTER ROUTE

Alters route information.

ALTER SCHEMA

Transfers the schema information between objects.

ALTER SERVICE

Lets you change the service.

ALTER SERVICE MASTER KEY

Changes the service master key for SQL Server.

ALTER SYMMETRIC KEY

Lets you change the properties of a symmetric key.

ALTER TABLE

This statement modifies a table definition by altering, adding, or dropping columns and constraints. It is also used to disable or enable constraints and triggers.

ALTER TRIGGER

This statement alters the definition of a trigger created previously by the CREATE TRIGGER statement. For more information about the parameters used in the ALTER TRIGGER statement, see also CREATE TRIGGER.

ALTER USER

Renames the user or changes its default schema.

ALTER VIEW

Allows you to alter a previously created view (created by executing CREATE VIEW), including indexed views, without affecting dependent stored procedures or triggers and without changing permissions. For more information about the parameters used in the ALTER VIEW statement, see also CREATE VIEW.

ALTER XML SCHEMA COLLECTION

Adds new schema components to an existing XML schema collection.

AND

Used to combine two Boolean expressions and returns TRUE when both expressions are TRUE. When more than one logical operator is used in a statement, AND operators are evaluated first. You can change the order of evaluation by using parentheses.

ANY

Used to compare a scalar value with a single-column set of values. For more information, see also SOME|ANY.

APPLOCK_MODE

This returns the lock mode owned by the lock owner on a particular application resource. It is an application lock function, which operates on the current database to which it is scoped.

APPLOCK_TEST

Returns information for the application of the APPLOCK-MODE.

APP_NAME

Used to return the application name for the current session, if set by the application.

ASCII

This command will return the ASCII code value of the leftmost character of a character expression.

ASIN

Will return the angle, in radians, whose sine is the given float expression. It is also called arcsine.

ASSEMBLY PROPERTY

Returns assembly information.

ASYMKEY_ID

Returns the key information.

ATAN

Will return the angle in radians whose tangent is the given float expression. It is also called arctangent.

ATN2

Will returns the angle, in radians, whose tangent is between the two given float expressions. It is also called arctangent.

AVG

Used to return the average of the values in a group. The Null values are ignored.

BACKUP

This statement backs up an entire database or transaction log, or one or more files or filegroups. For more information about database backup and restore operations see Chapter 7.

BACKUP KEYS

Used to export keys.

BEGINEND

A block that encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed. BEGIN and END are control-of-flow language keywords, similar to standard procedures in languages like Pascal.

BEGIN COVERSATION TIMER

Starts a timer for the service broker.

BEGIN DIALOG CONVERSATION

Starts the interservice dialog.

BEGIN DISTRIBUTED TRANSACTION

This clause specifies the start of the T-SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC).

BEGIN TRANSACTION

This clause marks the starting point of an explicit, local transaction. BEGIN TRANSACTION increments @@TRANCOUNT by 1.

BETWEEN

This clause specifies a range to test.

binary and varbinary

Binary data types of either fixed-length (binary) or variable-length (varbinary).

BINARY CHECKSUM

This command returns the binary checksum value computed over a row of a table or over a list of expressions. BINARY_ CHECKSUM can be used to detect changes to a row of a table.

bit

Integer data type 1, 0, or NULL.

BREAK

Use this command to exit the innermost WHILE loop. Any statements following the END keyword are ignored. BREAK is often, but not always, activated by an IF test.

BULK INSERT

Used to copy data into a database table or view in a user-specified format. It is frequently used for the initial load of data warehouse tables.

CASE

Used in flow-control segments, this evaluates a list of conditions and returns one of multiple possible result expressions.

CAST and CONVERT

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

CATCH (TRYCATCH)

The CATCH block of a T-SQL exception handler (see also TRY).

CEILING

This returns the smallest integer greater than, or equal to, the given numeric expression.

CertProperty

Gets you the value of a certificate property.

char and varchar

Fixed-length (char) or variable-length (varchar) character data types.

CHAR

This function converts an int ASCII code to a character.

CHARINDEX

This function returns the starting position of the specified expression in a character string.

CHECKPOINT

Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages modified after being entered into the buffer cache, but for which the modifications have not yet been written to disk.

CHECKSUM

This function returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indices.

CHECKSUM AGG

This function returns the checksum of the values in a group. Null values are ignored.

CLOSE

Closes an open cursor by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned. The CLOSE leaves the data structures accessible for reopening, but fetches and positioned updates are not allowed until the cursor is reopened. CLOSE must be issued on an open cursor; it is not allowed on cursors that have only been declared or are already closed.

CLOSE KEYS

Used to close keys in open sessions.

COALESCE

This function returns the first nonnull expression among its arguments.

COLLATE

A clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast. Windows Collation Name specifies the Windows collation name in the COLLATE clause. The Windows collation name is composed of the collation designator and the comparison styles. SQL Collation Name is a single string that specifies the collation name for a SQL collation.

COLLATIONPROPERTY

This function returns the property of a given collation.

COL_LENGTH

This function returns the defined length of a column.

COL_NAME

This function returns the name of a database column given the corresponding table identification number and column identification number.

COLUMNPROPERTY

This function returns information about a column or procedure parameter.

COMMIT TRANSACTION

Marks the end of a successful implicit or user-defined transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the connection, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1.

COMMIT WORK

Marks the end of a transaction.

COMPUTE

Use this call to generate a total as an additional summary column at the end of the result set. You can use this with BY, and the COMPUTE clause will generate control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.

Constants

A constant, also known as a literal or a scalar value, is a symbol that represents a specific data value. The format of a constant depends on the data type of the value it represents.

CONTAINS

Is a predicate used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINS can search for words and phrases, prefixes, words near other words, and so on.

CONTAINSTABLE

This function returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, matches according to the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

CONTINUE

Restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored. CONTINUE is often, but not always, activated by an IF test. For more information, see also WHILE.

CONTEXT_INFO

Use this to returns the context_info value that was set for the current session or batch.

CONVERT

See CAST and CONVERT earlier.

COS

A mathematical function that returns the trigonometric cosine of the given angle (in radians) in the given expression.

COT

A mathematical function that returns the trigonometric cotangent of the specified angle (in radians) in the given float expression.

COUNT

This function returns the number of items in a group.

COUNT_BIG

This function returns the number of items in a group. COUNT_BIG works like the COUNT function. The only difference between them is their return values: COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

CREATE AGGREGATE

Creates a user-defined (.NET Framework) aggregate function whose implementation is defined in a class. See also CREATE ASSEMBLY later.

CREATE APPLICATION ROLE

Adds and application role.

CREATE ASSEMBLY

Used to bind an assembly to the SQL Server .NET Framework Common Language Runtime (CLR) engine.

CREATE

Creates an asymmetric key.

CREATE CERTIFICATE

Creates a certificate.

CREATE CONTRACT

Used for service broker message definitions.

CREATE CREDENTIAL

Creates a credential.

CREATE DATABASE

Creates a new database and the files used to store the database, or attaches a database from the files of a previously created database.

CREATE DEFAULT

Creates an object called a default. When bound to a column or a user-defined data type, a default specifies a value to be inserted into the column to which the object is bound (or into all columns, in the case of a user-defined data type) when no value is explicitly supplied during an insert. Defaults, a backward compatibility feature, perform some of the same functions as default definitions created using the DEFAULT keyword of ALTER or CREATE TABLE statements. Default definitions are the preferred, standard way to restrict column data because the definition is stored with the table and automatically dropped when the table is dropped. A default is beneficial, however, when the default is used multiple times for multiple columns.

CREATE ENDPOINT

Creates an ENDPOINT and defines its properties for Web service clients.

CREATE EVENT NOTIFICATION

Creates an object that sends information pertaining to a database or server to service broker service.

CREATE FULLTEXT CATALOG

Creates a FULLTEXT service and all its properties.

CREATE FULLTEXT INDEX

Creates the FULLTEXT INDEX for the catalog.

CREATE FUNCTION

Creates a user-defined function, which is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.

CREATE INDEX

Creates an index on a given table or view.

CREATE LOGIN

Creates a SQL Sever login and its associated components, such as password and password policy.

CREATE MASTER KEY

Creates the database master key.

CREATE MESSAGE TYPE

Creates a new message type that defines the name of a message and the validation that the Service Broker performs on messages with that name. Both sides of a conversation define the same message types.

CREATE PARTITION FUNCTION

Creates a function in the current database that maps the rows of a table or index into partitions based on the values of a specified column.

CREATE PARTITION SCHEME

Creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups.

CREATE PROCEDURE

Creates a stored procedure, which is a saved collection of Transact-SQL statements that can take and return usersupplied parameters.

CREATE QUEUE

Creates a new queue in a database for the messages that get sent to a service. The Service Broker places the message on the queue associated with the service.

CREATE REMOTE SERVICE BINDING

Creates a binding that defines the security credentials used to initiate a conversation with a remote service.

CREATE ROLE

Creates a new database role in the current database.

CREATE ROUTE

Adds a new route to the routing table for the current database.

CREATE RULE

Creates an object called a rule. When bound to a column or a user-defined data type, a rule specifies the acceptable values that can be inserted into that column. Rules, a backward compatibility feature, perform some of the same functions as check constraints. CHECK constraints, created using the CHECK keyword of ALTER or CREATE TABLE, are the preferred, standard way to restrict the values in a column (multiple constraints can be defined on one or multiple columns). A column or user-defined data type can have only one rule bound to it. However, a column can have both a rule and one or more check constraints associated with it. When this is true, all restrictions are evaluated.

CREATE SCHEMA

Creates a schema that can be thought of as a conceptual object containing definitions of tables, views, and permissions.

CREATE SERVICE

Creates a new service in the Service Broker.

CREATE STATISTICS

Creates a histogram and associated density groups (collections) over the supplied column or set of columns.

CREATE SYMMETRIC KEY

Creates a symmetric key.

CREATE SYNONYM

Create a synonym.

CREATE TABLE

Creates a new table.

CREATE TRIGGER

Creates a trigger, which is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. SQL Server allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement.

CREATE TYPE

Creates a user-defined (.NET Framework) data type whose implementation is defined in a class. See also CREATE ASSEMBLY earlier.

CREATE USER

Creates a user and its associated properties such as password and account policy.

CREATE VIEW

Creates a virtual table that represents the data in one or more tables in an alternative way. Views are used as security mechanisms by granting permissions for a view but not on the underlying (base) tables.

CREATE XML SCHEMA COLLECTION

Imports an XML SCHEMA collection into a database.

CURRENT_REQUEST_ID

Returns the request ID of the current session.

CURRENT_TIMESTAMP

This function returns the current date and time. This function is equivalent to GETDATE().

CURRENT_USER

This function returns the current user. This function is equivalent to USER_NAME().

cursor

A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable.

CURSOR_STATUS

A scalar function that allows the caller of a stored procedure to determine whether or not the procedure has returned a cursor and result set for a given parameter.

Cursors

SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. SQL Server version 7.0 also introduces assigning a cursor to a variable or parameter with a cursor data type.

DATABASEPROPERTY

This function returns the named database property value for the given database and property name.

DATABASEPROPERTYEX

This function returns the current setting of the specified database option or property for the specified database.

DATALENGTH

This function returns the number of bytes used to represent any expression.

DATEADD

This function returns a new datetime value based on adding an interval to the specified date.

DATEDIFF

This function returns the number of date and time boundaries crossed between two specified dates.

DATENAME

This function returns a character string representing the specified date part of the specified date.

DATEPART

This function returns an integer representing the specified date part of the specified date.

datetime and smalldatetime

Date and time data types for representing date and time of day.

DAY

This function returns an integer representing the day date part of the specified date.

DB_ID

This function returns the database identification (ID) number.

DB_NAME

This function returns the database name.

DBCC

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server 2005. These statements check the physical and logical consistency of a database. Many DBCC statements can fix detected problems.

DBCC CHECKALLOC

Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKCATALOG

Checks for consistency in and between system tables in the specified database.

DBCC CHECKCONSTRAINTS

Checks the integrity of a specified constraint or all constraints on a specified table.

DBCC CHECKDB

Checks the allocation and structural integrity of all the objects in the specified database.

DBCC CHECKFILEGROUP

Checks the allocation and structural integrity of all tables (in the current database) in the specified filegroup.

DBCC CHECKIDENT

Checks the current identity value for the specified table and, if needed, corrects the identity value.

DBCC CHECKTABLE

Checks the integrity of the data, index, text, ntext, and image pages for the specified table or indexed view.

DBCC CLEANTABLE

Reclaims space for dropped variable-length columns and text columns.

DBCC CONCURRENCYVIOLATION

Displays statistics on how many times more than five batches were executed concurrently on SQL Server 2005 Express Engine. Also controls whether these statistics are also recorded in the SQL Server error log.

DBCC DBREINDEX

Rebuilds one or more indexes for a table in the specified database.

DBCC DBREPAIR (switch around)

Drops a damaged database.

DBCC dllname (FREE)

Unloads the specified extended stored procedure dynamiclink library (DLL) from memory.

DBCC DROPCLEANBUFFERS

Removes all clean buffers from the buffer pool.

DBCC FREEPROCCACHE

Removes all elements from the procedure cache.

DBCC FREEE SESSIONCACHE

Flushes the distributed query connection cache used by distributed queries against an instance of SQL Server.

DBCC FREESYSTEMCACHE

Releases all unused cache entries from all caches.

DBCC HELP

This function returns syntax information for the specified DBCC statement.

DBCC INDEXDEFRAG

Defragments clustered and secondary indexes of the specified table or view.

DBCC INPUTBUFFER

Displays the last statement sent from a client to SQL Server.

DBCC OPENTRAN

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

DBCC OUTPUTBUFFER

This function returns the current output buffer in hexadecimal and ASCII format for the specified system process ID (SPID).

DBCC PINTABLE (DEPRACTED)

Marks a table to be pinned, which means SQL Server does not flush the pages for the table from memory.

DBCC PROCCACHE

Displays information in a table format about the procedure cache.

DBCC SHOWCONTIG (SWITCH)

Displays fragmentation information for the data and indexes of the specified table.

DBCC SHOW_STATISTICS

Displays the current distribution statistics for the specified target on the specified table.

DBCC SHRINKDATABASE

Shrinks the size of the data files in the specified database.

DBCC SHRINKFILE

Shrinks the size of the specified data file or log file for the related database.

DBCC SQLPERF

Provides statistics about the use of transaction-log space in all databases.

DBCC TRACEOFF

Disables the specified trace flag(s).

DBCC TRACEON

Turns on (enables) the specified trace flag.

DBCC TRACESTATUS

Displays the status of trace flags.

DBCC UNPINTABLE

Marks a table as unpinned. After a table is marked as unpinned, the table pages in the buffer cache can be flushed.

DBCC UPDATEUSAGE

Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

DBCC USEROPTIONS

This function returns the SET options active (set) for the current connection.

DEALLOCATE

Removes a cursor reference. When the last cursor reference is deallocated, the data structures making up the cursor are released by SQL Server.

decimal and numeric

Numeric data types with fixed precision and scale.

DECLARE @ local_variable

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values with either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursorrelated statements. After declaration, all variables are initialized as NULL.

DECLARE CURSOR

Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the SQL-92 ((or later)) standard and a syntax using a set of Transact-SQL extensions.

DecryptBy

Commands used to decrypt using keys, certificates and passwords.

DEGREES

Given an angle in radians, returns the corresponding angle in degrees.

DELETE

Removes rows from a table.

DENSE_RANK

Returns the rank of rows within the partition of a result set.

DENY

Creates an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships.

DIFFERENCE

This function returns the difference between the SOUNDEX values of two character expressions as an integer.

DISABLE TRIGGER

Disables a trigger such that it will not fire.

DROP AGGREGATE

Drops the aggregate from a database.

DROP APPLICATION

Drops the application.

DROP ASSEMBLY

Drops an assembly.

DROP ASYMMETRIC KEY

Drops an asymmetric key.

DROP CERTIFICATE

Drops a certificate.

DROP CONTRACT

Drops a contract.

DROP CREDENTIAL

Drops a credential.

DROP DATABASE

Removes one or more databases from SQL Server. Removing a database deletes the database and the disk files used by the database.

DROP DEFAULT

Removes one or more user-defined defaults from the current database.

DROP ENDPOINT

Removes the endpoint from the system.

DROP EVENT NOTIFICATION

Removes an even notification.

DROP FULLTEXT CATALOG

Removes a fulltext catalog.

DROP FULLTEXT INDEX

Removes a fulltext index associated with a catalog.

DROP FUNCTION

Removes one or more user-defined functions from the current database. User-defined functions are created using CREATE FUNCTION and modified using ALTER FUNCTION.

DROP INDEX

Removes one or more indexes from the current database.

DROP LOGIN

Removes the login.

DROP MASTER KEY

Removes the master key.

DROP MESSAGE TYPE

Removes the message type.

DROP PARTITION FUNCTION

Removes the partition function from the database.

DROP PARTITIION SCHEME

Removes the partition scheme from the database.

DROP PROCEDURE

Removes one or more stored procedures or procedure groups from the current database.

DROP QUEUE

Removes a queue.

DROP REMOTE SERVICE BINDING

Removes the remote service binding.

DROP ROLE

Removes a role from the database.

DROP ROUTE

Removes a route from the service broker.

DROP RULE

Removes one or more user-defined rules from the current database.

DROP SCHEMA

Removes the schemas associated with a database.

DROP SERVICE

Removes a service on the server.

DROP SIGNATURE

Removes a signature.

DROP STATISTICS

Drops statistics for multiple collections within the specified tables (in the current database).

DROP SYMMETRIC KEY

Removes the symmetric key.

DROPY SYNONYM

Removes a synonym.

DROP TABLE

Removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. Any view or stored procedure that references the dropped table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE statement.

DROP TRIGGER

Removes one or more triggers from the current database.

DROP TYPE

Removes a user defined data type.

DROP USER

Removes a user from a database.

DROP VIEW

Removes one or more views from the current database. DROP VIEW can be executed against indexed views.

DROP XML SCHEMA

Removes a XML schema from the database.

DUMP

Makes a backup copy of a database (DUMP DATABASE) or makes a copy of the transaction log (DUMP TRANSACTION) in a form that can be read into SQL Server using the BACKUP or LOAD statements.

ELSE(IFELSE)

Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement (sql_statement) followingthe Boolean_expression is executed if the Boolean_ expression evaluates to TRUE. The optional ELSE keyword is an alternate Transact-SQL statement that is executed when Boolean_expression evaluates to FALSE or NULL.

ENABLE TRIGGER

Makes a trigger active so that it will fire when the table or database is accessed.

ENCRYPTBY

Used to encrypt using keys, certificates and passwords.

END (BEGINEND)

Encloses a series of Transact-SQL statements that will execute as a group. BEGINEND blocks can be nested.

END CONVERSATION

End the current conversation on the database.

ERROR

Several ERROR commands let you work with SQL Server error messages.

EVENT DATA

The message data in the error.

EXCEPT and INTERSECT

Returns distinct values by comparing the results of two queries.

EXECUTE, EXECUTE AS

Executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch.

EXISTS

Specifies a subquery to test for the existence of rows.

EXP

This function returns the exponential value of the given float expression.

Expressions

A combination of symbols and operators that SQL Server evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.

FETCH

Retrieves a specific row from a Transact-SQL server cursor.

FILE_ID

This function returns the file identification (ID) number for the given logical filename in the current database.

FILE_IDEX

Returns the file identification (ID) number for the specified logical file name of the data, log, or full-text file in the current database.

FILE_NAME

This function returns the logical filename for the given file identification (ID) number.

FILEGROUP_ID

This function returns the filegroup identification (ID) number for the given filegroup name.

FILEGROUP_NAME

This function returns the filegroup name for the given filegroup identification (ID) number.

FILEGROUPPROPERTY

This function returns the specified filegroup property value when given a filegroup and property name.

FILEPROPERTY

This function returns the specified filename property value when given a filename and property name.

float and real

Approximate number data types for use with floating-point numeric data. Floating-point data is approximate; not all values in the data type range can be precisely represented.

FLOOR

This function returns the largest integer less than or equal to the given numeric expression.

fn_get_sql

Returns the text of the SQL statement for the specified SQL handle.

fn_helpcollations

This function returns a list of all the collations supported by SQL Server 2005.

fn_listextendedproperty

This function returns extended property values of database objects.

fn_my_permissions

Returns a list of the permissions effectively granted to the principal on a securable.

fn_servershareddrives

This function returns the names of shared drives used by the clustered server.

fn_trace_geteventinfo

This function returns information about the events traced.

fn_trace_getfilterinfo

This function returns information about the filters applied to a specified trace.

fn_trace_getinfo

This function returns information about a specified trace or existing traces.

fn_trace_gettable

This function returns trace file information in a table format.

fn_virtualfilestats

This function returns 1/0 statistics for database files, including log files.

fn_virtualservernodes

This function returns the list of nodes on which the virtual server can run. Such information is useful in fail-over clustering environments.

FOR (XML)

FOR clause is used to specify either the BROWSE or the XML option. BROWSE and XML are unrelated options.

FORMATMESSAGE

Constructs a message from an existing message in sysmessages. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement; however, RAISERROR prints the message immediately, and FORMATMESSAGE returns the edited message for further processing.

FREETEXT

Is a predicate used to search columns containing characterbased data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally “wordbreaks” the freetext_string into a number of search terms and assigns each term a weight and then finds the matches.

FREETEXTTABLE

This function returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.

FROM

Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements.

FULLTEXTCATALOGPROPERTY

This function returns information about full-text catalog properties.

FULLTEXTSERVICEPROPERTY

This function returns information about full-text service-level properties.

GET CONVERSATION GROUP

Returns the conversation group identifier for the next message to be received, and locks the conversation group for the conversation that contains the message.

GET TRANSMISSION_STATUS

Returns the status for the last transmission for one side of a conversation.

GETANSINULL

This function returns the default nullability for the database for this session.

GETDATE

This function returns the current system date and time in the SQL Server standard internal format for datetime values.

GETUTCDATE

This function returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.

GO

Signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

GOTO

Alters the flow of execution to a label. The Transact-SQL statement(s) following GOTO are skipped, and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can be nested.

GRANT

Creates an entry in the security system that allows a user in the current database to work with data in the current database or execute specific Transact-SQL statements.

GROUP BY

Divides a table into groups. Groups can consist of column names or results or computed columns. For more information, see SELECT.

GROUPING

Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP

HAS_DBACCESS

This function returns information about whether the user has access to the specified database.

Has_Perms_By_Name

Evaluates the effective permission of the current user on a securable.

HashBytes

Gets the MD2, MD4, MD5, SHA, or SHA1 hash of its input.

HAVING

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. It is usually used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. For more information, see SELECT.

Hints

Hints are the options you can specify to SQL Server 2005’s query processor on SELECT, INSERT, UPDATE, or DELETE statements to override any execution plan the query optimizer might select for a query.

HOST_ID

This function returns the workstation identification number.

HOST_NAME

This function returns the workstation name.

IDENT_CURRENT

This function returns the last identity value generated for a specified table in any session and any scope.

IDENT_INCR

This function returns the increment value (returned as nume ric(@@MAXPRECISION,0)) specified during the creation of an identity column in a table or view that has an identity column.

IDENT_SEED

This function returns the seed value (returned as numeric (@@MAXPRECISION,0)) specified during the creation of an identity column in a table or a view that has an identity column.

IDENTITY (Property)

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

IDENTITY (Function)

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

IFELSE

Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate Transact-SQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).

Image

Information pertaining to the “image” datatype.

IN

Determines if a given value matches any value in a subquery or a list.

INDEXKEY_PROPERTY

This function returns information about the index key.

INDEXPROPERTY

This function returns the named index property value given a table identification number, index name, and property name.

INDEX_COL

This function returns the indexed column name.

INSERT

Adds a new row to a table or a view.

int, bigint, smallint, and tinyint

Exact number data types that use integer data.

INTO

Creates a new table in the SELECT statement to hold the data.

IS_MEMBER

Indicates whether the current user is a member of the specified Windows NT group or SQL Server role.

IS_SRVROLEMEMBER

Indicates whether the current user login is a member of the specified server role.

ISDATE

Determines whether an input expression is a valid date.

IS [NOT] NULL

Determines whether or not a given expression is NULL.

ISNULL

Replaces NULL with the specified replacement value.

ISNUMERIC

Determines whether an expression is a valid numeric type.

Key_GUID

Returns the GUID of a symmetric key.

Key_ID

Return the ID of a symmetric key.

KILL

Terminates a user process according to the system process ID (SPID) or unit of work (UOW). If the specified SPID or UOW has a lot of work to undo, the KILL command may take some time to complete, particularly when it involves rolling back a long transaction.

KILL QUERY NOTIFICATION SUBSCRIPTION

Removes query notification subscriptions from the instance. This statement can remove a specific subscription or all subscriptions.

KILL STATS JOB

Kills the asynchronous statistics update job.

LEFT

This function returns the part of a character string starting at a specified number of characters from the left.

LEN

This function returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

LIKE

Determines whether or not a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. Wildcard characters, however, can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the=and != string comparison operators. If any of the arguments are not of the character string data type, SQL Server 2005 converts them to the character string data type, if possible.

LOAD

Loads a backup copy of one of the following: Database, Transaction Log, Load Header Only. This statement is available for backward compatibility with earlier versions of SQL Server.

LOG

This function returns the natural logarithm of the given float expression.

LOG10

This function returns the base-10 logarithm of the given float expression.

LOWER

This function returns a character expression after converting uppercase character data to lowercase.

LTRIM

This function returns a character expression after removing leading blanks.

MAX

This function returns the maximum value in the expression.

MIN

This function returns the minimum value in the expression.

money and smallmoney

Monetary data types for representing monetary or currency values.

MONTH

This function returns an integer that represents the month part of a specified date.

MOVE COVERSATION

Moves the conversation to another group.

NCHAR

This function returns the Unicode character with the given integer code, as defined by the Unicode standard.

nchar and nvarchar

Character data types that are either fixed-length (nchar) or variable-length (nvarchar) Unicode data and use the UNICODE UCS-2 character set.

NEWID

Creates a unique value of type uniqueidentifier.

NEWSEQUENTIALID

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer.

NOT

Negates a Boolean input.

ntext, text, and image

Fixed- and variable-length data types for storing large nonUnicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

NULLIF

This function returns a null value if the two specified expressions are equivalent.

numeric

For more information about the numeric data type, see DECIMAL and NUMERIC.

OBJECT_DEFINITION

Returns the Transact-SQL source text of the definition of a specified object.

OBJECT ID

This function returns the database object identification number.

OBJECT_NAME

This function returns the database object name.

OBJECTPROPERTY

This function returns information about objects in the current database.

OBJECTPROPERTYEX

Returns information about schema-scoped objects in the current database. For a list of these objects, see sys.objects (Transact-SQL). OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped, such as data definition {language (DDL) triggers and event notifications.

OPEN

Opens a Transact-SQL server cursor and populates the cursor by executing the Transact-SQL statement specified on the DECLARE CURSOR or SET cursor_variable statement.

OPEN MASTERKEY, SYMMETRICKEY

Opens and/or decrypts the keys of the current database.

OPENDATASOURCE

Provides ad hoc connection information as part of a four-part object name without using a linked server name.

OPENQUERY

Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

OPENXML

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

OPENROWSET (SWITCH)

Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

OPTION

OR

Combines two conditions. When more than one logical operator is used in a statement, OR operators are evaluated after AND operators. However, you can change the order of evaluation by using parentheses.

ORDER BY

Specifies the sort order used on columns returned in a SELECT statement. For more information, see SELECT.

ORIGINAL_LOGIN

Returns the name of the login that connected to the instance of SQL Server.

OUTPUT

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable.

OVER

Determines the partitioning and ordering of the rowset before the associated window function is applied.

PARSENAME

This function returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name.

PATINDEX

This function returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

PERMISSIONS

This function returns a value containing a bitmap that indicates the statement, object, or column permissions for the current user.

PI

This function returns the constant value of PI.

POWER

This function returns the value of the given expression to the specified power.

Predicate

Is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, and the join conditions of FROM clauses.

PRINT

This function returns a user-defined message to the client

PUBLISHINGSERVERNAME

Returns the name of the originating Publisher for a published database participating in a database mirroring session

QUOTENAME

This function returns a Unicode string with the delimiters added to make the input string a valid SQL Server 2005 delimited identifier.

RADIANS

This function returns radians when a numeric expression, in degrees, is entered.

RAISERROR

This function returns a user-defined error message and sets a system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined, it is sent back to the client as a server error message.

RAND

This function returns a random float value from 0 through 1.

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

READTEXT

Reads text, ntext, or image values from a text, ntext, or image column, starting from a specified offset and reading the specified number of bytes.

real

For more information about the real data type, see float and real.

RECEIVE

Retrieves one or more messages from a queue. Depending on the retention setting for the queue, either removes the message from the queue or updates the status of the message in the queue.

RECONFIGURE

Updates the currently configured (the config_value column in the sp_configure result set) value of a configuration option changed with the sp_configure system stored procedure. Because some configuration options require a server stop and restart to update the currently running value, RECONFIGURE does not always update the currently running value (the run_value column in the sp_configure result set) for a changed configuration value.

REPLACE

Replaces all occurrences of the second given string expression in the first string expression with a third expression.

REPLICATE

Repeats a character expression for a specified number of times times.

RESTORE

Restores backups taken using the BACKUP command. For more information about database backup and restore operations see Chapter 7.

RESTORE FILELISTONLY

This function returns a result set with a list of the database and log files contained in the backup set.

RESTORE HEADERONLY

Retrieves all the backup header information for all backup sets on a particular backup device. The result from executing RESTORE HEADERONLY is a result set.

RESTORE LABELONLY

This function returns a result set containing information about the backup media identified by the given backup device.

RESTORE MASTER OR SERVICE MASTER KEY

Restores the master or service keys of a database.

RESTORE REWINDONLY

Rewinds and closes specified tape devices that were left open by BACKUP or RESTORE statements executed with the NOREWIND option. This command is supported only for tape devices.

RESTORE VERIFYONLY

Verifies the backup but does not restore the backup. Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, SQL Server 2005 returns the message: “The backup set is valid.”

RETURN

RETURN is an unconditional exit from a query or procedure that is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

REVERSE

This function returns the reverse of a character expression.

REVOKE

Removes a previously granted or denied permission from a user in the current database.

RIGHT

This function returns the part of a character string starting a specified number of integer_expression characters from the right.

ROLLBACK TRANSACTION

Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside a transaction.

ROLLBACK WORK

Rolls back a user-specified transaction to the beginning of a transaction.

ROUND

This function returns a numeric expression, rounded to the specified length or precision.

ROWCOUNT_BIG

This function returns the number of rows affected by the last statement executed. This function operates like @@ROWCOUNT, except that the return type of ROWCOUNT_BIG is bigint.

ROWNUMBER

RTRIM

This function returns a character string after truncating all trailing blanks.

SAVE TRANSACTION

Sets a savepoint within a transaction.

SCHEMA_ID

Returns the ID of a schema.

SCHEMA_NAME

Returns the name of a schema.

SCOPE_IDENTITY

This function returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module-a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Search Condition

Is a combination of one or more predicates using the logical operators AND, OR, and NOT.

SELECT @local_variable

Specifies that the given local variable (created using DECLARE @local_variable) should be set to the specified expression.

SELECT

Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables. The full syntax of the SELECT statement is complex, and it is fully documented in Books Online.

SEND

Sends a message, using an existing conversation.

SERVERPROPERTY

This function returns property information about the server instance.

SESSION_USER

Is a niladic function that allows a system-supplied value for the current session’s username to be inserted into a table when no default value is specified. Also allows the username to be used in queries, error messages, and so on.

SESSIONPROPERTY

This function returns the SET options settings of a session.

SET @local_variable

Sets the specified local variable, previously created with the DECLARE @local_variable statement, to the given value.

SET

The Transact-SQL programming language provides several SET statements that alter the current session handling of specific information.

SETUSER

Allows a member of the sysadmin fixed server role or db_owner fixed database role to impersonate another user (deprecated).

SHUTDOWN

Stops SQL Server.

SIGN

Returns the positive (+1), zero (0), or negative (1) sign of the specified expression.

SET ANSI_DEFAULTS

Controls a group of SQL Server 2005 settings that collectively specify some SQL-92 ((or later)) standard behaviors.

SET ANSI_NULL_DFLT_OFF

Alters the session’s behavior to override default nullability of new columns when the ANSI null default option for the database is true. For more information about setting the value for ANSI null default, see also sp_dboption.

SET ANSI_NULL_DFLT_ON

Alters the session’s behavior to override default nullability of new columns when the ANSI null default option for the database is false. For more information about setting the value for ANSI null default, see also sp_dboption.

SET ANSI_NULLS

Specifies SQL-92 ((or later)) compliant behavior of the Equal (=) and Not Equal to (<>) comparison operators when used with null values.

SET ANSI_PADDING

Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

SET ANSI_WARNINGS

Specifies SQL-92 (or later) and later standard behavior for several error conditions.

SET ARITHABORT

Terminates a query when an overflow or divide-by-zero error occurs during query execution.

SET ARITHIGNORE

Controls whether error messages are returned from overflow or divide-by-zero errors during a query.

SET CONCAT_NULL_YIELDS_NULL

Controls whether or not concatenation results are treated as null or empty string values.

SET CONTEXT_INFO

Associates up to 128 bytes of binary information with the current session or connection.

SET CURSOR_CLOSE_ ON_COMMIT

Controls whether or not a cursor is closed when a transaction is committed.

SET DATEFIRST

Sets the first day of the week to a number from 1–7.

SET DATEFORMAT

Sets the order of the date parts (month/day/year) for entering datetime or smalldatetime data.

SET DEADLOCK_PRIORITY

Controls the way the session reacts when in a deadlock situation. Deadlock situations arise when two processes have data locked, and each process cannot release its lock until other processes have released theirs.

SET DISABLE_DEF_CNST_CHK

Specified interim deferred violation checking; was used for efficiency purposes in SQL Server version 6..

SET FIPS_FLAGGER

Specifies checking for compliance with the FIPS 127–2 standard, which is based on the SQL-92 (or later) standard.

SET FMTONLY

This function returns only metadata to the client.

SET FORCEPLAN

Makes the SQL Server 2005 query optimizer process a join in the same order as tables appear in the FROM clause of a SELECT statement only.

SET IDENTITY_INSERT

Allows explicit values to be inserted into the identity column of a table.

SET IMPLICIT_TRANSACTIONS

Sets implicit transaction mode for the connection.

SET LANGUAGE

Specifies the language environment for the session. The session language determines the datetime formats and system messages.

SET LOCK_TIMEOUT

Specifies the number of milliseconds a statement waits for a lock to be released.

SET NOCOUNT

Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

SET NOEXEC

Compiles each query but does not execute it.

SET NUMERIC_ROUNDABORT

Specifies the level of error reporting generated when rounding in an expression causes a loss of precision.

SET OFFSETS

This function returns the offset (position relative to the start of a statement) of specified keywords in Transact-SQL statements to DB-Library applications.

SET PARSEONLY

Checks the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement.

SET QUERY_GOVERNOR_COST_LIMIT

Overrides the currently configured value for the current connection.

SET QUOTED_IDENTIFIER

Causes SQL Server 2005 to follow the SQL-92 (or later) rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks either can be Transact-SQL reserved keywords or can contain characters not usually allowed by the Transact-SQL syntax rules for identifiers.

SET REMOTE_PROC_TRANSACTIONS

Specifies that when a local transaction is active, executing a remote stored procedure starts a Transact-SQL distributed transaction managed by the Microsoft Distributed Transaction Manager (MS DTC).

SET ROWCOUNT

Causes SQL Server 2005 to stop processing the query after the specified number of rows are returned.

SET SHOWPLAN_ALL

Causes SQL Server 2005 not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

SET SHOWPLAN_XML

Causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document.

SET STATISTICS 10

Causes SQL Server 2005 to display information regarding the amount of disk activity generated by Transact-SQL statements.

SET STATISTICS PROFILE

Displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, triggers, and stored procedures.

SET STATISTICS TIME

Displays the number of milliseconds required to parse, compile, and execute each statement.

SET TEXTSIZE

Specifies the size of text and ntext data returned with a SELECT statement.

SET TRANSACTION ISOLATION LEVEL

Controls the default transaction locking behavior for all SQL Server 2005 SELECT statements issued by a connection.

SET XACT_ABORT

Specifies whether SQL Server 2005 automatically rolls back the current transaction if a Transact-SQL statement raises a run-time error.

SETUSER

Allows a member of the sysadmin fixed server role or db_owner fixed database role to impersonate another user.

SHUTDOWN

Immediately stops operation of SQL Server 2005.

SIGN

This function returns the positive (+1), zero (0), or negative (1) sign of the given expression.

SignByAsymKey

Signs a clear text message with an asymmetric key.

SignByCert

Signs a clear text message with a certificate.

SIN

This function returns the trigonometric sine of the given angle (In radians) in an approximate numeric (float) expression.

smalldatetime

For information about the smalldatetime data type, see datetime and small datetime.

smallint

For information about the smallint data type, see int.

smallmoney

For information about the smallmoney data type, see money and small money.

SOME | ANY

Compares a scalar value with a single-column set of values.

SOUNDEX

This function returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

SPACE

This function returns a string of repeated spaces.

sql_variant

A data type that stores values of various SQL Server-supported data types, except text, ntext, image, timestamp, and sql_variant.

SQL_VARIANT_PROPERTY

This function returns the base data type and other information about a sql_variant value.

SQUARE

This function returns the square of the given expression.

SQRT

This function returns the square root of the given expression.

STATS_DATE

This function returns the date that the statistics for the specified index were last updated.

STDEV

This function returns the statistical standard deviation of all values in the given expression.

STDEVP

This function returns the statistical standard deviation for the population for all values in the given expression.

STR

This function returns character data converted from numeric data.

STUFF

Deletes a specified length of characters and inserts another set of characters at a specified starting point.

SUBSTRING

This function returns part of a character, binary, text, or image expression. For more information about the valid SQL Server 2005 data types that can be used with this function, see Books Online.

SUM

This function returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

SUSER_ID

This function returns the user’s login identification number.

SUSER_NAME

This function returns the user’s login identification name.

SUSER_SID

This function returns the security identification number (SID) for the user’s login name.

SUSER_SNAME

This function returns the login identification name from a user’s security identification number (SID).

System Stored Procedures

Usage, Usage Tips, Suggestions, and References.

sp_add_alert

This procedure creates an alert.

sp_addalias

This procedure maps a login to a user in a database. sp_addalias is provided for backward compatibility. SQL Server version 7.0 provides roles and the ability to grant permissions to roles as an alternative to using aliases.

sp_addapprole

This procedure adds a special type of role in the current database used for application security.

sp_add_data_file_recover_suspect_db

This procedure adds a data file to a filegroup when recovery cannot complete on a database due to an insufficient space (1105) error on the filegroup. After the file is added, this stored procedure turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE ADD FILE.

sp_addextendedproc

This procedure registers the name of a new extended stored procedure to SQL Server.

sp_addextendedproperty

This procedure adds a new extended property to a database object. If the property already exists, the procedure fails.

sp_addgroup

This procedure creates a group in the current database. sp_ addgroup is included for backward compatibility. SQL Server version 7.0 uses roles instead of groups. Use sp_addrole to add a role.

sp_add_category

This procedure adds the specified category of jobs, alerts, or operators to the server.

sp_add_job

This procedure adds a new job executed by the SQL Server Agent service.

sp_add_jobschedule

This procedure creates a schedule for a job.

sp_add_jobserver

This procedure targets the specified job at the specified server.

sp_add_jobstep

This procedure adds a step (operation) to a job.

sp_addlinkedserver

This procedure creates a linked server, which allows access to distributed, heterogeneous queries against OLE DB data sources. After creating a linked server with sp_addlinkedserver, this server can then execute distributed queries. If the linked server is defined as SQL Server, remote stored procedures can be executed.

sp_addlinkedsrvlogin

This procedure creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server.

sp_add_log_file_recover_suspect_db

This procedure adds a log file to a filegroup when recovery cannot complete on a database due to an insufficient log space error (error number 9002). After the file is added, this stored procedure turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE ADD LOG FILE. See “Troubleshooting Recovery” in Books Online.

sp_addlogin

This procedure creates a new SQL Server login that allows a user to connect to an instance of SQL Server using SQL Server Authentication.

sp_add_log_shipping_database

This procedure specifies that a database on the primary server is being log shipped.

sp_add_log_shipping_plan

This procedure creates a new log shipping plan and inserts a row in the log_shipping_plans table.

sp_add_log_shipping_plan_database

This procedure adds a new database to an existing log shipping plan.

sp_add_log_shipping_primary

This procedure adds a new primary server to the log_shipping_primaries table.

sp_add_log_shipping_secondary

This procedure adds a secondary server to the log_shipping_ secondaries table.

sp_add_maintenance_plan

This procedure adds a maintenance plan and returns the plan ID.

sp_add_maintenance_plan_db

This procedure associates a database with a maintenance plan.

sp_add_maintenance_planjob

This procedure associates a maintenance plan with an existing job.

sp_addmessage

This procedure adds a new error message to the sysmessages table.

sp_add_notification

Sets up a notification for an alert.

sp_add_operator

This procedure creates an operator (notification recipient) for use with alerts and jobs.

sp_addremotelogin

This procedure adds a new remote login ID on the local server, allowing remote servers to connect and execute remote procedure calls.

sp_addrole

This procedure creates a new SQL Server role in the current database.

sp_addrolemember

This procedure adds a security account as a member of an existing SQL Server database role in the current database.

sp_addserver

This procedure defines a remote server or the name of the local SQL server. sp_addserver is provided for backward compatibility. Use sp_addlinkedserver.

sp_addsrvrolemember

This procedure adds a login as a member of a fixed server role.

sp_addtask

This procedure creates a scheduled task.

sp_addtype

This procedure creates a user-defined data type.

sp_add_targetservergroup

This procedure adds the specified server group.

sp_addumpdevice

This procedure adds a backup device to SQL Server.

sp_add_targetsvrgrp_member

This procedure adds the specified target server to the specified target server group.

sp_adduser

This procedure adds a security account for a new user in the current database. This procedure is included for backward compatibility. Use sp_grantdbaccess.

sp_altermessage

This procedure alters the state of a sysmessages error.

sp_apply_job_to_targets

This procedure applies a job to one or more target servers or to the target servers belonging to one or more target server groups.

sp_approlepassword

This procedure changes the password of an application role in the current database.

sp_attach_db

This procedure attaches a database to a server.

sp_attach_single_file_db

This procedure attaches a database having only one data file to the current server.

sp_autostats

This procedure displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.

sp_bindefault

This procedure binds a default to a column or to a user-defined data type.

sp_bindrule

This procedure binds a rule to a column or to a user-defined data type.

sp_bindsession

This procedure binds or unbinds a connection to other transactions in the same instance of Microsoft SQL Server 2005. A bound connection allows two or more connections to participate in the same transaction and share the transaction until a ROLLBACK TRANSACTION or COMMIT TRANSACTION is issued.

sp__can_tlog_be_applied

This procedure verifies that a transaction log can be applied to a database.

sp_catalogs

This procedure returns the list of catalogs in the specified linked server, which is equivalent to databases in SQL Server.

sp_certify_removable

This procedure verifies that a database is configured properly for distribution on removable media and reports any problems to the user.

sp_change_monitor_role

This procedure performs a role change on the log shipping monitor, setting the current secondary database to be a primary database.

sp_change_primary_role

This procedure removes the primary database from a log shipping plan.

sp_change_secondary_role

This procedure converts the secondary database of a log shipping plan into a primary database.

sp_change_users_login

This procedure changes the relationship between a SQL Server login and a SQL Server user in the current database.

sp_changedbowner

This procedure changes the owner of the current database.

sp_changegroup

This procedure changes the role membership for the security account of a user in the current database. This procedure is provided for backward compatibility. SQL Server version 7.0 uses roles instead of groups. Use sp_addrolemember instead.

sp_changeobjectowner

This procedure changes the owner of an object in the current database.

sp_column_privileges

This procedure returns column privilege information for a single table in the current environment.

sp_column_privileges_ex

This procedure returns column privileges for the specified table on the specified linked server.

sp_columns

This procedure returns column information for the specified tables or views that can be queried in the current environment.

sp_columns_ex

This function returns the column information, one row per column, for the given linked server table(s). The sp_columns_ex returns column information only for the given column if column is specified.

sp_configure

This procedure displays or changes global configuration settings for the current server.

sp_create_log_shipping_monitor_ account

This procedure creates the log_shipping_monitor_probe login on the monitor server, and assigns update permissions to the msdb.dbo.log_shipping_primaries and msdb.dbo.log_ shipping_secondaries tables.

sp_create_removable

This procedure creates a removable media database. It essentially creates three or more files (one for the system catalog tables, one for the transaction log, and one or more for the data tables) and places the database on those files.

sp_createstats

This procedure creates single-column statistics for all eligible columns for all user tables in the current database. The new statistic has the same name as the column on which it is created. The computed columns and columns of the ntext, text, or image data types cannot be specified as statistics columns. Columns already having statistics are not touched; for example, the first column of an index or a column with explicitly created statistics. The CREATE STATISTICS statement is executed for each column that satisfies the above restrictions. The option FULLSCAN is executed if fullscan is specified.

sp_cursor_list

This procedure reports the attributes of server cursors currently open for the connection.

sp_cycle_errorlog

This procedure closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created.

sp_databases

This procedure lists databases that reside in an instance of SQL Server, or the databases that are accessible through a database gateway.

sp_datatype_info

This procedure returns information about the data types supported by the current environment.

sp_dbcmptlevel

This procedure sets certain database behaviors to be compatible with the specified earlier version of SQL Server (versions 8, 7, and so on).

sp_dbfixedrolepermission

This procedure displays the permissions for each fixed database role.

sp_dboption

This procedure displays or changes database options. sp_dboption should not be used on either the master or tempdb databases. sp_dboption is supported for backward compatibility. You should use ALTER DATABASE to set database options.

sp_dbremove

Removes a database and all files associated with that database. This procedure is included for backward compatibility, and you should use the procedure sp_detach_ db to “unhinge” a database from SQL Server.

sp_defaultdb

This procedure changes the default database for a login.

sp_defaultlanguage

This procedure changes the default language of a login.

sp_define_log_shipping_monitor

This procedure sets up the log shipping monitor account on the monitor server.

sp_delete_alert

This procedure removes an alert.

sp_delete_backuphistory

This procedure deletes the entries in the backup and restore history tables for backup sets older than oldest_date. Because additional rows are added to the backup and restore history tables when a backup or restore operation is performed, sp_delete_backuphistory can be used to reduce the size of the history tables in the msdb database.

sp_delete_category

This procedure removes the specified category of jobs, alerts, or operators from the current server.

sp_deletejob

This procedure deletes a job.

sp_delete_jobschedule

This procedure removes a schedule from a job.

sp_delete_jobserver

This procedure removes the specified target server.

sp_delete_jobstep

This procedure removes a job step from a job.

sp_delete_log_shipping_database

This procedure deletes a database from the log_shipping_ databases table on the primary server.

sp_delete_log_shipping_plan

This procedure deletes a log shipping plan.

sp_delete_log_shipping_plan_ database

This procedure removes a database from a log shipping plan.

sp_delete_log_shipping_primary

This procedure deletes the primary server from the log_shipping_primaries table.

sp_delete_log_shipping_secondary

This procedure removes a secondary server from log_shipping_secondaries table.

sp_delete_maintenance_plan

This procedure deletes the specified maintenance plan.

sp_delete_maintenance_plan_db

This procedure disassociates the specified maintenance plan from the specified database.

sp_delete_maintenance_plan_job s

This procedure disassociates the specified maintenance plan from the specified job.

sp_delete_notification

This procedure removes all notifications sent to a particular operator in response to an alert.

sp_delete_operator

This procedure removes an operator.

sp_delete_targetserver

This procedure removes the specified server from the list of available target servers.

sp_delete_targetservergroup

This procedure deletes the specified target server group.

sp_delete_targetsvrgrp_member

This procedure removes a target server from a target server group.

sp_denylogin

This procedure prevents a Microsoft Windows NT user or group from connecting to Microsoft SQL Server.

sp_depends

This procedure displays information about database object dependencies (for example, the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure). It also references objects outside the current database that are not reported.

sp_describe_cursor

This procedure reports the attributes of a server cursor.

sp_describe_cursor_columns

This procedure reports the attributes of the columns in the result set of a server cursor.

sp_describe_cursor_tables

This procedure reports the base tables referenced by a server cursor.

sp_detach_dbsp_dropalias

This procedure detaches a database from a server and, optionally, runs UPDATE STATISTICS on all tables before detaching. It also removes an alias to a user in the current database from a login. The procedure sp_dropalias is provided for backward compatibility only. You should use roles and the sp_droprolemember stored procedure instead of aliases.

sp_dropapprole

This procedure removes an application role from the current database.

sp_dropdevice

This procedure drops a database device or backup device from SQL Server, deleting the entry from master.dbo.sysdevices.

sp_dropextendedproc

This procedure drops an extended stored procedure.

sp_dropextendedproperty

This procedure drops an existing extended property.

sp_dropgroup

This procedure removes a role from the current database. The procedure sp_dropgroup is provided for backward compatibility. In SQL Server version 7.0, groups are implemented as roles.

sp_droplinkedsrvlogin

This procedure removes an existing mapping between a login on the local server running SQL Server and a login on the linked server.

sp_droplogin

This procedure removes a SQL Server login, preventing access to SQL Server using that login name.

sp_dropmessage

This procedure drops a specified error message from the sysmessages system table.

sp_dropremotelogin

This procedure removes a remote login mapped to a local login used to execute remote stored procedures against the local server running SQL Server.

sp_droprole

This procedure removes a SQL Server role from the current database.

sp_droprolemember

This procedure removes a security account from a SQL Server role in the current database.

sp_dropserver

This procedure removes a server from the list of known remote and linked servers on the local SQL Server.

sp_dropsrvrolemember

This procedure removes a SQL Server login or a Microsoft Windows NT user or group from a fixed server role.

sp_droptask

This procedure is provided for backward compatibility only. For information about the Microsoft SQL Server version 7.0 replacement procedures see Books Online.

sp_droptype

This procedure deletes a user-defined data type from systypes.

sp_dropuser

This procedure removes a SQL Server user or Microsoft Windows NT user from the current database. sp_dropuser is provided for backward compatibility. Use sp_revokedbaccess to remove a user.

sp_dropwebtask

This procedure deletes a previously defined Web task.

sp_enumcodepages

This procedure returns a list of the code pages and character sets supported by sp_makewebtask.

sp_executesql

This procedure executes a T-SQL statement or batch that can be reused many times, or that has been built dynamically. The T-SQL statement or batch can contain embedded parameters.

sp_fkeys

This procedure returns logical foreign key information for the current environment. This procedure shows foreign key relationships including disabled foreign keys.

sp_foreignkeys

This procedure returns the foreign keys that reference primary keys on the table in the linked server.

sp_fulltext_catalog

This procedure creates and drops a full-text catalog, and starts and stops the indexing action for a catalog. Multiple full-text catalogs can be created for each database.

sp_fulltext_column

This procedure specifies whether or not a particular column of a table participates in full-text indexing.

sp_fulltext_database

This procedure initializes full-text indexing or removes all full-text catalogs from the current database.

sp_fulltext_service

This procedure changes Search Service (Full-text Search) properties.

sp_fulltext_table

This procedure marks or unmarks a table for full-text indexing.

sp_getapplock

This procedure places a lock on an application resource.

sp_getbindtoken

This procedure returns a unique identifier for the transaction. This unique identifier is referred to as a bind token. sp_getbindtoken returns a string representation to be used to share transactions between clients.

sp_get_log_shipping_monitor_info

This procedure returns status information about a “Log Shipping Pair.” A log shipping pair is a set of primary serverprimary database and secondary server-secondary database.

sp_grantdbaccess

This procedure adds a security account in the current database for a SQL Server login or Windows NT user or group, and enables it to be granted permissions to perform activities in the database.

sp_grantlogin

This procedure allows a Windows user or group account to connect to Microsoft SQL Server using Windows Authentication. See Chapter 5.

sp_help

This procedure reports information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type supplied by SQL Server.

sp_help_alert

This procedure reports information about the alerts defined for the server.

sp_help_category

This procedure provides information about the specified classes of jobs, alerts, or operators.

sp_helpconstraint

This procedure returns a list of all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint (for DEFAULT and CHECK constraints only).

sp_helpdb

This procedure reports information about a specified database or all databases.

sp_helpdbfixedrole

This procedure returns a list of the fixed database roles.

sp_helpdevice

This procedure reports information about SQL Server database files. sp_helpdevice is used for backward compatibility with earlier versions of SQL Server that used the term “device” for a database file.

sp_help_downloadlist

This procedure lists all rows in the sysdownloadlist system table for the supplied job, or all rows if no job is specified.

sp_helpextendedproc

This procedure displays the currently defined extended stored procedures and the name of the dynamic-link library to which the procedure (or function) belongs.

sp_helpfile

This procedure returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server.

sp_helpfilegroup

This procedure returns the names and attributes of filegroups associated with the current database.

sp_help_fulltext_catalogs

This procedure returns the ID, name, root directory, status, and number of full-text indexed tables for the specified fulltext catalog.

sp_help_fulltext_catalogs_cursor

This procedure makes use of a cursor to return the ID, name, root directory, status, and number of full-text indexed tables for the specified full-text catalog.

sp_help_fulltext_columns

This procedure returns the columns designated for full-text indexing.

sp_help_fulltext_columns_cursor

This procedure makes use of a cursor to return the columns designated for full-text indexing.

sp_help_fulltext_tables

This procedure returns a list of tables that are registered for full-text indexing.

sp_help_fulltext_tables_cursor

This procedure makes use of a cursor to return a list of tables that are registered for full-text indexing.

sp_helpgroup

This procedure reports information about a role, or all roles, in the current database. This procedure is included for backward compatibility with version 7.0 and can be executed against a version 7.0 database.

sp_helphistory

This procedure is provided for backward compatibility.

sp_helpjob

This procedure returns information about jobs that are used by SQL Server Agent service to perform automated activities in SQL Server.

sp_help_jobhistory

This procedure provides information about the jobs for servers in the multiserver administration domain.

sp_help_jobschedule

This procedure returns information about the scheduling of jobs used by SQL Server Enterprise Manager to perform automated activities.

sp_help_jobserver

This procedure returns information about the server for a given job.

sp_help_jobstep

This procedure returns information for the steps in a job used by SQL Server Agent service to perform automated activities.

sp_helpindex

This procedure reports information about the indexes on a table or view.

sp_helplanguage

This procedure reports information about a particular alternate language or about all languages.

sp_helplinkedsrvlogin

This procedure provides information about login mappings defined against a specific linked server used for distributed queries and remote stored procedures.

sp_helplogins

This procedure provides information about logins and the associated users in each database.

sp_help_maintenance_plan

This procedure returns information about the specified maintenance plan. If a plan is not specified, this stored procedure returns information about all maintenance plans.

sp_help_notification

This procedure reports a list of alerts for a given operator or a list of operators for a given alert.

sp_helpntgroup

This procedure reports information about Microsoft Windows NT groups with accounts in the current database.

sp_help_operator

This procedure reports information about the operators defined for the server.

sp_helpremotelogin

This procedure reports information about remote logins for a particular remote server, or for all remote servers, defined on the local server.

sp_helprole

This procedure returns information about the roles in the current database.

sp_helprolemember

This procedure returns information about the members of a role in the current database.

sp_helprotect

This procedure returns a report with information about user permissions for an object, or statement permissions, in the current database.

sp_helpserver

This procedure reports information about a particular remote or replication server, or about all servers of both types. Provides the server name; the server’s network name; the server’s replication status; the server’s identification number; collation name; and time-out values for connecting to, or queries against, linked servers.

sp_helpsort

This procedure displays the SQL Server sort order and character set.

sp_helpsrvrole

This procedure returns a list of the SQL Server fixed server roles.

sp_helpsrvrolemember

This procedure returns information about the members of a SQL Server fixed server role.

sp_helpstats

This procedure returns statistics information about columns and Indexes on the specified table.

sp_help_targetserver

This procedure lists all target servers.

sp_help_targetservergroup

This procedure lists all target servers in the specified group. If no group is specified, SQL Server returns information about all target server groups.

sp_helptask

This procedure is provided for backward compatibility only.

sp_helptext

This procedure prints the text of a rule, a default, or an unencrypted stored procedure, trigger, or view.

sp_helptrigger

This procedure returns the type or types of triggers defined on the specified table for the current database.

sp_helpuser

This procedure reports information about SQL Server users, Windows NT users, and database roles in the current database.

sp_indexes

This procedure returns index information for the specified remote table.

sp_indexoption

This procedure sets option values for user-defined indexes.

sp_invalidate_textptr

This procedure makes the specified in-row text pointer, or all in-row text pointers, in the transaction invalid. sp_invalidate_ textptr can be used only on in-row text pointers, which are from tables with the “text in row” option enabled.

sp_linkedservers

This procedure returns the list of linked servers defined in the local server.

sp_lock

This procedure reports information about locks.

sp_makewebtask

This procedure creates a task that produces an HTML document containing data returned by executed queries.

sp_manage_jobs_by_login

This procedure deletes or reassigns jobs that belong to the specified login.

sp_monitor

This procedure displays statistics about SQL Server.

sp_MShasdbaccess

This procedure lists the name and owner of all the databases to which the user has access.

sp_msx_defect

This procedure removes the current server from multiserver operations.

sp_msx_enlist

This procedure adds the current server to the list of target servers available for multiserver operations. Only a SQL Server version 7.0 database server running on Windows NT can be enlisted.

sp_OACreate

This procedure creates an instance of the OLE object on an instance of SQL Server.

sp_OADestroy

This procedure destroys a created OLE object.

sp_OAGetErrorInfo

This procedure obtains OLE Automation error information.

sp_OAGetProperty

This procedure gets a property value of an OLE object.

sp_OAMethod

This procedure calls a method of an OLE object.

sp_OASetProperty

This procedure sets a property of an OLE object to a new value.

sp_OAStop

This procedure stops the server-wide OLE Automation stored procedure execution environment.

sp_password

This procedure adds or changes a password for a SQL Server login.

sp_pkeys

This procedure returns primary key information for a single table in the current environment.

sp_primarykeys

This procedure returns the primary key columns, one row per key column, for the specified remote table.

sp_post_msx_operation

This procedure inserts operations (rows) into the sysdownloadlist system table for target servers to download and execute.

sp_processmail

This procedure uses extended stored procedures (xp_findnextmsg, xp_readmail, and xp_deletemail) to process incoming mail messages (expected to be only a single query) from the inbox for SQL Server. It uses the xp_sendmail extended stored procedure to return the result set to the message sender.

sp_procoption

This procedure sets procedure options.

sp_purgehistory

This procedure is provided for backward compatibility only.

sp_purge_jobhistory

This procedure removes the history records for a job.

sp_reassigntask

This procedure is provided for backward compatibility only.

sp_recompile

This procedure forces stored procedures and triggers to be recompiled the next time they are run.

sp_refreshview

This procedure refreshes the metadata for the specified view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

sp_releaseapplock

This procedure releases a lock on an application resource.

sp_remoteoption

This procedure displays or changes options for a remote login defined on the local server running SQL Server.

sp_remove_job_from_targets

This procedure removes the specified job from the given target servers or target server groups.

sp_remove_log_shipping_monitor

This procedure deletes the log shipping monitor information from the log_shipping_monitor table.

sp_rename

This procedure changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database.

sp_renamedb

This procedure changes the name of a database.

sp_resetstatus

This procedure resets the status of a suspect database.

sp_resolve_logins

This procedure resolves logins on the new primary server against logins from the former primary server.

sp_resync_targetserver

This procedure resynchronizes all multiserver jobs in the specified target server.

sp_revokedbaccess

This procedure removes a security account from the current database.

sp_revokelogin

This procedure removes the login entries from SQL Server for a Windows NT user or group created with sp_grantlogin or sp_denylogin.

sp_runwebtask

This procedure executes a previously defined Web job and generates the HTML document. The task to run is identified by the output file name, by the procedure name, or by both parameters.

sp_server_info

This procedure returns a list of attribute names and matching values for SQL Server, the database gateway, or the underlying data source.

sp_serveroption

This procedure sets server options for remote servers and linked servers.

sp_setapprole

This procedure activates the permissions associated with an application role in the current database.

sp_setnetname

This procedure sets the network names in sysservers to their actual network computer names for remote instances of SQL Server. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing invalid SQL Server identifiers.

sp_settriggerorder

This procedure specifies which AFTER triggers associated with a table will be fired first or last. The AFTER triggers that will be fired between the first and last triggers will be executed in undefined order.

sp_spaceused

This procedure displays the number of rows, disk space reserved, and disk space used by a table in the current database, or displays the disk space reserved and used by the entire database.

sp_special_columns

This procedure returns the optimal set of columns that uniquely identify a row in the table. Also returns columns automatically updated when any value in the row is updated by a transaction.

sp_sproc_columns

This procedure returns column information for a single stored procedure or user-defined function in the current environment.

sp_srvrolepermission

This procedure returns the permissions applied to a fixed server role.

sp_start_job

This procedure instructs SQL Server Agent to execute a job immediately.

sp_statistics

This procedure returns a list of all indexes and statistics on a specified table or indexed view.

sp_stop_job

This procedure instructs SQL Server Agent to stop the execution of a job.

sp_stored_procedures

This procedure returns a list of stored procedures in the current environment.

sp_tableoption

This procedure sets option values for user-defined tables. sp_tableoption may be used to turn on the text in row feature on tables with text, ntext, or image columns.

sp_table_privileges

This procedure returns a list of table permissions (such as INSERT, DELETE, UPDATE, SELECT, REFERENCES) for the specified table(s).

sp_table_privileges_ex

This procedure returns privileged information about the specified table from the specified linked server.

sp_tables

This procedure returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause).

sp_tables_ex

This procedure returns table information about the tables from the specified linked server.

sp_trace_create

This procedure creates a trace definition. The new trace will be in a stopped state.

sp_trace_generateevent

This procedure creates a user-defined event.

sp_trace_setevent

This procedure adds or removes an event or event column to a trace. sp_trace_setevent may be executed only on existing traces that are stopped (status is 0). SQL Server 2005 will return an error if this stored procedure is executed on a trace that does not exist or whose status is not 0.

sp_trace_setfilter

This procedure applies a filter to a trace. sp_trace_setfilter may be executed only on existing traces that are stopped (status is 0). SQL Server 2005 will return an error if this stored procedure is executed on a trace that does not exist or whose status is not 0.

sp_trace_setstatus

This procedure modifies the current state of the specified trace.

sp_unbindefault

This procedure unbinds (removes) a default from a column or from a user-defined data type in the current database.

sp_unbindrule

This procedure unbinds a rule from a column or a userdefined data type in the current database.

sp_update_alert

This procedure updates the settings of an existing alert.

sp_update_category

This procedure changes the name of a category.

sp_updateextendedproperty

This procedure updates the value of an existing extended property.

sp_update_job

This procedure changes the attributes of a job.

sp_update_jobschedule

This procedure changes the schedule settings for the specified job.

sp_update_jobstep

This procedure changes the setting for a step in a job that is used to perform automated activities.

sp_update_log_shipping_monitor_info

This procedure updates the monitoring information about a log shipping pair.

sp_update_log_shipping_plan

This procedure updates information about an existing log shipping plan.

sp_update_log_shipping_plan_ database

This procedure updates an existing database that is part of a log shipping plan.

sp_update_notification

This procedure updates the notification method of an alert notification.

sp_update_operator

This procedure updates information about an operator (notification recipient) for use with alerts and jobs.

sp_updatestats

This procedure runs UPDATE STATISTICS against all userdefined tables in the current database.

sp_update_targetservergroup

This procedure changes the name of the specified target server group.

sp_updatetask

This procedure is provided for backward compatibility only.

sp_validname

This procedure checks for valid SQL Server identifier names. All nonbinary and nonzero data, including Unicode data that can be stored by using the nchar, nvarchar, or ntext data types, are accepted as valid characters for identifier names.

sp_validatelogins

This procedure reports information about orphaned Windows NT users and groups that no longer exist in the Windows NT environment but still have entries in the Microsoft SQL Server system tables.

sp_who

This procedure provides information about current SQL Server users and processes. The information returned can be filtered to return only those processes that are not idle.

sp_xml_preparedocument

Reads the Extensible Markup Language (XML) text provided as input, then parses the text using the MSXML parser (Msxml2.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes (elements, attributes, text, comments, and so on) in the XML document.

sp_xml_removedocument

This procedure removes the internal representation of the XML document specified by the document handle and invalidates the document handle.

SYSTEM USER

This statement allows a system-supplied value for the current system username to be inserted into a table when no default value is specified.

table

The table is a special data type that can be used to store a result set f(or later) processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.

TAN

TAN returns the tangent of the input expression.

text

Text provides information about the text data type, see ntext, text, and image.

TEXTPTR

This function returns the text-pointer value that corresponds to a text, ntext, or image column in varbinary format. The retrieved text pointer value can be used in READTEXT, WRITETEXT, and UPDATE statements.

TEXTVALID

This is a text, ntext, or image function that checks whether a given text pointer is valid.

timestamp

This is a data type that exposes automatically generated binary numbers that are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

tinyint

See int, bigint, small int and tinyint.

TOP

Used to specifies that only the first set of rows specified in the variable will be returned from a query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Trace Flags

The trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are often used to diagnose performance issues or to debug stored procedures or complex computer systems.

Transactions

A transaction is a single unit of work. If a transaction is successful, all the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, all the data modifications are erased.

TRIGGER_NESTLEVEL

This function returns the number of triggers executed for the UPDATE, INSERT, or DELETE statement that fired the trigger. TRIGGER_NESTLEVEL is used in triggers to determine the current level of nesting.

TRUNCATE TABLE

This directive removes all rows from a table without logging the individual row deletes.

TRY

See CATCH.

TYPE_ID

Returns the ID of a type.

TYPE_NAME

Returns the unqualified name of the type.

TYPEPROPERTY

The function returns information about a data type.

UNICODE

This function returns the integer value, as defined by the Unicode standard, for the first character of the input expression.

UNION

This clauses combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union. For more information, see chapters Chapters 15–17.

uniqueidentifier

The globally unique identifier (GUID).

UPDATE, UPDATE()

Changes existing data in a table.

UPDATE STATISTICS

This statement caused an update of information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view. To create statistics on columns, see also CREATE STATISTICS.

UPDATETEXT

This statement updates an existing text, ntext, or image field. Use UPDATETEXT to change only a portion of a text, ntext, or Image column in place. Use WRITETEXT to update and replace an entire text, ntext, or image field.

UPPER

This function returns a character expression with lowercase character data converted to uppercase.

USE

This directive changes the database context to the specified database.

USER

USER allows a system-supplied value for the current user’s database username to be inserted into a table when no default value is specified.

USER_ID

This function returns a user’s database identification number.

USER_NAME

This function returns a user database username from a given identification number.

VAR

VAR returns the statistical variance of all values in the given expression.

varbinary

For information about the varbinary data type, see binary and varbinary.

varchar

For information about the varchar data type, see char and varchar.

VARP

This function returns the statistical variance for the population for all values in the given expression.

VerifySignedByCert, VerifySignedByAsmKey

Tests whether digitally signed data has been changed since it was signed.

WAITFOR

Specifies a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction.

WHERE

This statement specifies the condition for the rows returned by a query.

WHILE

This is a flow control statement that sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

WITH (Common Table Expression)

The common table expression (CTE) specifies a temporary named result set that can be used in a query within the execution scope of a SELECT, INSERT, A CTE can include references to itself; a recursive common table expression.

WITH XMLNAMESPACES

Declares a XML namespace.

WRITETEXT

This statement permits nonlogged, interactive updating of an existing text, ntext, or image column. This statement completely overwrites any existing data in the column it affects. WRITETEXT cannot be used on text, ntext, and image columns in views.

XACT_STATE

A scalar function that reports the transaction state of a session, indicating if the session has an active transaction, and if it is capable of being committed.

xml

The xml data type

xml_schema_namespace

This construct reconstructs all the schemas or a specific schema in the specified XML schema collection, and then returns an xml data type instance.

YEAR

This function returns an integer that represents the year part of a specified date.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net