Table 1-1: Transact-SQL commands
Command
|
Description
|
alter database
|
Increases the amount of space allocated
to a database.
|
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.
|
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.
|
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 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 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 (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 table
|
Creates new tables and optional integrity
constraints.Creates a table containing computed columns and function-based
indexes.
|
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.
|
declare
|
Declares the name and type of local variables
for a batch or procedure.
|
declare cursor
|
Defines a cursor.
|
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.
|
drop default
|
Removes a user-defined default.
|
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 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.
|
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.
|
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.
|
load transaction
|
Loads a backup copy of the transaction
log.
|
lock table
|
Explicitly locks a table within a transaction.
|
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.
|
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.
|
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.
|
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.
|
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.
|
use
|
Specifies the database with which you
want to work.
|
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.
|