Overview

Table 1-1 provides a brief description of the commands in this chapter.

**Table 1-1: Transact-SQL commands**
Command	Description
alter database	Increases thhe amount of space allocated to a database. Allowed with an archive database.
alter encryption key	Changes the default property of key. Creates and drops key copies for users. Changes owner of key. Recovers lost passwords.
alter role	Defines mutually exclusive relationships between roles, adds, drops, and changes passwords for roles.
alter table	Adds new columns; adds, changes, or drops constraints, changes constraints; partitions or unpartitions an existing table.Creates computed columns. Encrypts or decrypts existing data. Adds encrypted columns. Adds, drops, or replaces decrypt defaults for encrypted columns. Changes the encryption key on an encrypted column.
begin...end	Encloses a series of SQL statements so that control-of-flow language, such as if...else, can affect the performance of the whole group.
begin transaction	Marks the starting point of a user-defined transaction.
break	Causes an exit from a while loop. break is often activated by an if test.
checkpoint	Writes all dirty pages (pages that have been updated since they were last written) to the database device. Allowed with an archive database.
close	Deactivates a cursor.
commit	Marks the ending point of a user-defined transaction.
compute clause	Generates summary values that appear as additional rows in the query results.
connect to...disconnect	Specifies the server to which a passthrough connection is required.
continue	Causes the while loop to restart. continue is often activated by an if test.
create archive database	Creates an archive database.
create database	Creates a new database.
create default	Specifies a value to insert in a column (or in all columns of a user-defined datatype) if no value is explicitly supplied at insert time.
create encryption key	Creates a named key for column encryption.
create existing table	Confirms that the current remote table information matches the information that is stored in column_list, and verifies the existence of the underlying object.
create function	Creates a user-defined function, which is a saved Transact-SQL routine that returns a specified value.
create function (SQLJ)	Creates a user-defined function by adding a SQL wrapper to a Java static method.
create index	Creates an index on one or more columns in a table. Creates a function-based index.
create plan	Creates an abstract query plan.
create procedure	Creates a stored procedure that can take one or more user-supplied parameters.
create procedure (SQLJ)	Creates a SQLJ stored procedure by adding a SQL wrapper to a Java static method.
create proxy_table	Creates a proxy table without specifying a column list. Component Integration Services derives the column list from the metadata it obtains from the remote table.
create role	Creates a user-defined role.
create rule	Specifies the domain of acceptable values for a particular column or for any column of a user-defined datatype.
create schema	Creates a new collection of tables, views, and permissions for a database user.
create service	Wraps the supplied SQL statement in a stored procedure with the specified name and parameters.
create table	Creates new tables and optional integrity constraints. Creates a table containing computed columns and function-based indexes. Creates a table containing encrypted columns and decrypt defaults.
create trigger	Creates a trigger, which is a type of stored procedure often used for enforcing integrity constraints. A trigger executes automatically when a user attempts a specified data modification statement on a specified table.
create view	Creates a view, which is an alternative way of looking at the data in one or more tables.
dbcc	Checks the logical and physical consistency of a database. Use dbcc regularly as a periodic check or if you suspect any damage.
deallocate cursor	Makes a cursor inaccessible and releases all memory resources committed to that cursor. Allowed with an archive database.
declare	Declares the name and type of local variables for a batch or procedure.
declare cursor	Defines a cursor. Allowed with an archive database.
delete	Removes rows from a table.
delete statistics	Removes statistics from the sysstatistics system table.
disk init	Makes a physical device or file usable by Adaptive Server.
disk mirror	Creates a software mirror that immediately takes over when the primary device fails.
disk refit	Rebuilds the master database’s sysusages and sysdatabases system tables from information contained in sysdevices. Use disk refit after disk reinit as part of the procedure to restore the master database.
disk reinit	Rebuilds the master database’s sysdevices system table. Use disk reinit as part of the procedure to restore the master database.
disk remirror	Reenables disk mirroring after it is stopped by failure of a mirrored device or temporarily disabled by the disk unmirror command.
disk resize	Dynamically increases the size of database devices.
disk unmirror	Disables either the original device or its mirror, allowing hardware maintenance or the changing of a hardware device.
drop database	Removes one or more databases from an Adaptive Server. Allowed with an archive database.
drop default	Removes a user-defined default.
drop encryption key	Removes an encryption key and its key copies.
drop function	Removes one or more user-defined functions from the current database.
drop function (SQLJ)	Removes a SQLJ function.
drop index	Removes an index from a table in the current database.
drop procedure	Removes user-defined stored procedures.
drop role	Removes a user-defined role.
drop rule	Removes a user-defined rule.
drop service	Removes a user-defined Web service from the current database. Both the metadata and the corresponding stored procedure are removed.
drop table	Removes a table definition and all of its data, indexes, triggers, and permission specifications from the database.
drop trigger	Removes a trigger.
drop view	Removes one or more views from the current database.
dump database	Makes a backup copy of the entire database, including the transaction log, in a form that can be read in with load database. Dumps and loads are performed through Backup Server.
dump transaction	Makes a copy of a transaction log and removes the inactive portion.
execute	Runs a system procedure, a user-defined stored procedure, or a dynamically constructed Transact-SQL command. Allowed with an archive database.
fetch	Returns a row or a set of rows from a cursor result set. In scrollable cursors, fetch orientation keywords specify the position of the row to fetch. Allowed with an archive database.
goto label	Branches to a user-defined label.
grant	Assigns permissions to users or to user-defined roles.
group by and having clauses	Used in select statements to divide a table into groups and to return only groups that match conditions in the having clause.
if...else	Imposes conditions on the execution of a SQL statement.
insert	Adds new rows to a table or view.
kill	Kills a process.
load database	Loads a backup copy of a user database, including its transaction log. Allowed with an archive database.
load transaction	Loads a backup copy of the transaction log. Allowed with an archive database.
lock table	Explicitly locks a table within a transaction. Allowed with an archive database.
mount	Attaches the database to the destination or secondary Adaptive Server.
online database	Marks a database available for public use after a normal load sequence and, if needed, upgrades a loaded database and transaction log dumps to the current version of Adaptive Server. Allowed with an archive database.
open	Opens a cursor for processing.
order by clause	Returns query results in the specified columns in sorted order.
prepare transaction	Used by DB-Library™ in a two-phase commit application to see if a server is prepared to commit a transaction.
print	Prints a user-defined message on the user’s screen.
quiesce database	Suspends and resumes updates to a specified list of databases.
raiserror	Prints a user-defined error message on the user’s screen and sets a system flag to record that an error condition has occurred.
readtext	Reads text, unitext, and image values, starting from a specified offset and reading a specified number of bytes or characters. Allowed with an archive database.
reconfigure	Currently has no effect; included to allow existing scripts to run without modification. In earlier versions, you were required to execute reconfigure after sp_configure, to implement new configuration parameter settings.
remove java	Removes one or more Java-SQL classes, packages, or JARs from a database. Use when Java is enabled in the database.
reorg	Reclaims unused space on pages, removes row forwarding, or rewrites all rows in the table to new pages, depending on the option used.
return	Exits from a batch or procedure unconditionally, optionally providing a return status. Statements following return are not executed.
revoke	Revokes permissions or roles from users or roles.
rollback	Rolls a user-defined transaction back to the last savepoint inside the transaction or to the beginning of the transaction.
rollback trigger	Rolls back the work done in a trigger, including the update that caused the trigger to fire, and issues an optional raiserror statement.
save transaction	Sets a savepoint within a transaction.
select	Retrieves rows from database objects. Allowed with an archive database.
set	Sets Adaptive Server query-processing options for the duration of the user’s work session. Can be used to set some options inside a trigger or stored procedure. Can also be used to activate or deactivate a role in the current session.
set encryption passwd	Sets a password for a named key or column at the session level.
setuser	Allows a Database Owner to impersonate another user.
shutdown	Shuts down Adaptive Server or a Backup Server™. This command can be issued only by a System Administrator.
truncate table	Removes all rows from a table.
union operator	Returns a single result set that combines the results of two or more queries. Duplicate rows are eliminated from the result set unless the all keyword is specified.
unmount	Shuts down the database and drops it from the Adaptive Serve, and deactivates and drops devices.
update	Changes data in existing rows, either by adding data or by modifying existing data; updates all statistics information for a given table; updates information about the number of pages in each partition for a partitioned table; updates information about the distribution of key values in specified indexes.
update all statistics	Updates all statistics information for a given table.
update index statistics	Updates the statistics for all columns in an index.
update statistics	Updates information about the distribution of key values in specified indexes, for all columns in an index, table, or partition.
update table statistics	Updates statistics that are stored in systabstats table, such as rowcount, cluster ratios, and so on
use	Specifies the database with which you want to work. Allowed with an archive database.
waitfor	Specifies a specific time, a time interval, or an event for the execution of a statement block, stored procedure, or transaction.
where clause	Sets the search conditions in a select, insert, update, or delete statement.
while	Sets a condition for the repeated execution of a statement or statement block. Statements execute repeatedly, as long as the specified condition is true.
writetext	Permits nonlogged, interactive updating of an existing text, unitext, or image column.