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 the amount of space allocated to a database.

alter role

Defines mutually exclusive relationships between roles and 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.

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.

case

Allows SQL expressions to be written for conditional values. case expressions can be used anywhere a value expression can be used.

checkpoint

Writes all dirty pages (pages that have been updated since they were last written) to the database device.

close

Deactivates a cursor.

coalesce

Allows SQL expressions to be written for conditional values. coalesce expressions can be used anywhere a value expression can be used; alternative for a case expression.

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)

create index

Creates an index on one or more columns in a table.

create plan

Creates an abstract query plan.

create procedure

Creates a stored procedure that can take one or more user-supplied parameters.

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.

create trigger

Creates a trigger, 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

Database Consistency Checker (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 increase the size of database devices, rather than initializing a new device

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 a Adaptive Server.

drop default

Removes a user-defined default.

drop function (SQLJ)

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.

goto label

Branches to a user-defined label.

grant

Assigns permissions to users or to user-defined roles.

grant dbcc

Allows the System Administrator to grant access on certain dbcc commands.

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

You can use the insert command to create a new file directory. To do so, use only the filename, filetype and content columns. You specify “DIR” as the filetype, then filename is created as a directory.

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.

nullif

Allows SQL expressions to be written for conditional values. nullif expressions can be used anywhere a value expression can be used; alternative for a case expression.

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 column(s) 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 and image values, starting from a specified offset and reading a specified number of bytes or characters.

reconfigure

The reconfigure command currently has no effect; it is included to allow existing scripts to run without modification. In previous releases, reconfigure was required after the sp_configure system procedure 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.

revoke dbcc

Allows the System Administrator to revoke access on some dbcc commands.

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.

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.

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. The statement(s) execute repeatedly, as long as the specified condition is true.

writetext

Permits non-logged, interactive updating of an existing text or image column.