Queries, Cursors and Views - Glossary

Aggregate

A query or part of a query that performs mathematical summing on one or more columns. Aggregates are used in conjunction with the GROUP BY clause.

Alias

An alternative name for a database object that makes referring to that object easier. For example, if you construct a query by specifying the fully-qualified name of the database( including server name, database name, owner, and table), it is easier to alias this long string of objects so that you do not have to specify it every time you access it's objects. Note, however, that an alias is useful only within a single Transact-SQL statement.

Batch

A set of Transact-SQL commands that are sent to the SQL Server engine, all at one time. Batches are separated by GO keywords. If there are no GO keywords in a set of Transact-SQL statements, they are all considered to be in one batch.

bind

To assign a rule or default to one or more columns in a table. This forces SQL Server 7 to apply the rule automatically to all values that are inserted or updated in the column.

Binding

The process of assigning a rule or default to a column in a table or user-defined type. If a rule or default is not bound to a database, it will have no influence over that database.

composite constraint

A constraint that applies to more than one column in a table. Also called a composite key.

composite key

A constraint that applies to more than one column in a table. Also referred to as a composite constraint.

constraint

Any limitation, such as an index, placed on a table. Constraints provide an alternative to rules, defaults, and triggers as a means of enforcing integrity (either relational or entity) within the database.

cursor

A virtual window that enables you to look at a specific number of rows in a result set.

Data Page

The internal location in which SQL Server stores data. Each data page has a capacity of 8 KB. When a page is filled, SQL Server automatically creates another.

Default

A database object that is used to insert a value into a column with which the default is bound, in case the value is omitted in a Transact-SQL statement.

Distributed query

A database query that accesses more than one data source. These data sources can be located on one or more servers, in one or more databases.

Dynamically

A process or statement that is constructed at run-time, as opposed to compile-time. Sometimes constructing SQL statements dynamically is necessary because the statement is constructed based on options chosen by the user.

Entity integrity

A condition relating to the quality of data in a table, in which every row is unique and every row can be pinpointed and accessed without involving any other row.

Executing

The process of sending a query to SQL Server for processing. This is synonymous with Issuing.

Forward-only

A forward-only cursor allows movement in a forward direction only.

Global

A global scope makes a variable available throughout an entire process.

Graphical User Interface (GUI)

The program elements involved in communicating with the user through visual cues, typically utilizing both the keyboard and mouse

GROUP BY

Specifies how a query should break, based on values that change in the columns represented after the GROUP BY keywords.

Heterogeneous

Queries that are constructed across one or more SQL Servers. A heterogeneous query can include data from other sources, such as text files, Oracle, or Informix.

index

A database object that helps speed queries by either instructing SQL Server to physically order records in a table or providing an execution path so that SQL Server (actually the optimizer) knows how to run the query.

index page

The internal location in which SQL Server stores an index. Each index page has a capacity of 8 KB. When a page is filled, SQL Server automatically creates another.

Inner join

A link between tables in a database that displays only the rows with a match (true value of the join condition) for both join tables. An inner join does not guarantee the return of every row of data that you expect will be returned.

Issuing

The process of sending a query to SQL Server for processing. This is synonymous with Executing.

leaf page

An index or data page that is full but that doesn’t point to a new page.

Literal value

A value that is actually specified in Transact-SQL code and not stored in a variable or table. Examples of literal values include "free", "tmann@thesqlsolution.com", 72, and 1283. Literal values that store character data are called string literals and are always enclosed in quotations.

Local

A local scope makes a variable available only to the procedure where it was created.

Membership

The actual records that make up a recordset in a cursor.

Meta-data

Information (data) about database structures. For example, the meta-data about a table includes its name, its column names, and the filegroups in which it resides. In other words, meta-data is data about data.

OLE DB provider

A low-level software interface that is written for a specific type of data source, such as a text file, or SQL Server. This interface is necessary so that database-specific implementations can be isolated from higher-level components.

Optimized

The process of determining the fastest possible way to execute a query. Optimization can be simple or complex, depending on the complexity of the query or stored procedure being issued.

ORDER BY

SQL keywords that specify the order in which the data returned from a query will be returned.

Outer join

A link between tables that guarantees the return of every row of data that you expect will be returned, but that also can require more resources in order to execute. Includes the left outer join, right outer join, and full outer join.

Query optimizer

The query optimizer is a part of the SQL Server engine, and determines the best way for SQL Server to execute a query. For example, it determines which index to use when executing a query.

Query plan

The method that SQL Server uses to access data in the fastest possible way.

Result set

The rows of data that a query returns.

Rule

A database object that forces SQL Server 7 to verify that values that are inserted or updated in a table conform to a specific criteria.

Scope

The length of time that a variable is available.

Scrolling

Movement through a recordset on a record-by-record basis, similar to the way you scroll through text in a computer document.

SQL Server role

A group of users who are allowed the same access permission to certain objects. There are two main types of roles that come pre-installed in SQL Server 7.0:..1. Fixed Server Role - allowed to perform administrative functions on specific, pre-defined categories of built-in objects..2. Fixed Database Role - allowed to perform database-specific tasks, such as INSERTING, UPDATING, DELETING, or SELECTING data.

stored procedure

A compiled set of SQL logic, stored within SQL Server, that can be called from within any SQL statement.

Subquery

A query within a query.

System catalogs

Also known as system tables, these tables store meta-data, which is data that describes other data.

Table scan

The Query Optimizer reads the complete contents of a table, instead of using an index.

ticks

Single quote marks used to enclose certain values within stored procedures.

unbind

To dissociate a rule from one or more columns in a table to which it is bound.

Unbinding

The process of unassigning a rule or default from a column in a table or user-defined type.

Union

A query that combines the results of multiple queries into a single result set.

Variable placeholder

need definition

view

A database object that enables you to define the columns and rows that a specific user can see. A view can also serve as a tool for enforcing security within your database.

virtual table

Another name for a view that enables you to look at one or more tables instead of having to query the tables themselves.