Glossary

ADO (ActiveX Data Objects)

An easy-to-use application programming interface (API) that wraps OLE DB for use in languages, such as Visual Basic, Visual Basic for Applications, Active Server Pages, and Microsoft Internet Explorer Visual Basic Scripting.

aggregate functions

Functions that provide summary data over sets returning a singular value.

alert

A user-defined response to a SQL Server event. Alerts can either execute a defined task or send an email and/or a pager message to a specified operator.

alias

An alternative name for a table or column in expressions that is often used to shorten the name for subsequent reference in code, prevent possible ambiguous references, or provide a more descriptive name in the query output. An alias can also be an alternative name for a server.

ALTER

A command used to change a database object, such as a function or procedure. Using ALTER allows the object to be changed without losing permissions and other database settings.

analysis server

The server component of Analysis Services that is specifically designed to create and maintain multidimensional data structures and provide multidimensional data in response to client queries.

analysis services

A service that provides OLAP and data mining functionality for business intelligence solutions.

articles

Data structures made from selected columns from a table or from an entire table that need to be bundled into a publication to be used for replication. A publication is composed of one or more articles. An article represents some or all columns and some or all rows in a single table.

assembly

A managed application module that contains class metadata and managed code as an object in SQL Server. By referencing an assembly, CLR functions, CLR stored procedures, CLR triggers, user-defined aggregates, and user-defined types can be created in SQL Server.

attributes

Characteristics given to an entity, such as PhoneNumber and State; they are usually represented as rows inside an entity. An attribute in data modeling can be thought of as the columns of a table implemented in SQL Server.

backup

A copy of a database, filegroup, file, or transaction log that can be used to restore data, typically after a serious database error or a system failure. Backups can be used alone or as part of a sequence of backups.

batch

A collection of zero, one, or more T-SQL statements sent to SQL Server to be run together. Multiple batches can be combined in a single script or procedure, using the GO keyword to separate the batches.

BCP

A command prompt bulk copy utility that copies SQL Server data to or from an operating system file in a user-specified format.

binding

In SQL application programming interfaces (APIs), associating a result set column or a parameter with a program variable so that data is moved automatically into or out of a program variable when a row is fetched or updated.

blocked process

A process that cannot continue until a lock that another process holds is released.

books online

A comprehensive help facility and electronic reference manual.

built-in function

One of a group of predefined functions provided as part of the T-SQL and Multidimensional Expressions (MDX) languages.

cascading actions

Cascading delete or cascading update operations that either delete a row containing a primary key or update a primary key value referenced by foreign key columns in existing rows in other tables. On a cascading delete, all the rows whose foreign key values reference the deleted primary key value are also deleted. On a cascading update, all the foreign key values are updated to match the new primary key value.

CASE

A complex expression that handles multiple-branch conditional logic.

CAST

A function that converts data from one type to another and is based on the American National Standards Institute (ANSI) SQL-92 standard, as opposed to the CONVERT function.

CHECK constraint

A constraint that defines what values are acceptable in a column. You can apply CHECK constraints to multiple columns, and you can apply multiple CHECK constraints to a single column. When a table is dropped, CHECK constraints are also dropped.

client/server

A physically or logically implemented system in which a device or an application called the server requests services or data from another device or application and the server fulfills the request.

CLR (Common Language Runtime) function

A function that is created by referencing a SQL Server assembly. The implementation of the CLR function is defined in an assembly that is created in the .NET Framework CLR.

CLR (Common Language Runtime) stored procedure

A stored procedure that is created by referencing a SQL Server assembly. The implementation of the CLR stored procedure is defined in an assembly that is created in the .NET Framework CLR.

CLR (Common Language Runtime) trigger

A DML trigger or DDL trigger that is created by referencing a CLR assembly. The implementation of the CLR trigger is defined in an assembly that is created in the .NET Framework CLR.

CLR (Common Language Runtime) user-defined type

A user-defined data type that is created by referencing a CLR assembly. The implementation of the CLR user-defined type is defined in an assembly that is created in the .NET Framework CLR.

clustered index

A type of index in which the logical order of key values determines the actual order of the data rows and keeps the data rows sorted. Using a clustered index causes the actual data rows to move into the leaf level of the index.

collation (sequence)

A set of rules that determine how data is compared, ordered, and presented. Character data is sorted using collation information, including locale, sort order, and case-sensitivity.

constraint

A property assigned to a table column that prevents certain types of invalid data values from being placed in the column. For example, a UNIQUE or PRIMARY KEY constraint prevents you from inserting a value that is a duplicate of an existing value; a CHECK constraint prevents you from inserting a value that does not match a search condition; and NOT NULL prevents you from inserting a NULL value.

CREATE

A command used to create a database object, such as a view or stored procedure.

cursor

A construct that holds a rowset from a SELECT statement, which can then be stepped through row-by-row for various operations.

data mart

A subset of the contents of a data warehouse. A data mart tends to contain data focused at the department level or on a specific business area.

data warehouse

A database specifically structured for query and analysis. A data warehouse typically contains data representing the business history of an organization.

database lock

The largest of locking increments, affecting an entire database.

DDL (Data Definition Language)

A language, usually part of a database management system, that is used to define all attributes and properties of a database, especially row layouts, column definitions, key columns (and sometimes keying methodology), file locations, and storage strategy.

deadlock

A state in which two users or processes cannot continue processing because they each have a resource that the other needs.

DELETE

A T-SQL statement that can be used to delete data from a table. A fast way to delete all rows is to use TRUNCATE TABLE.

denormalization

The process of adding planned redundancy to an already fully normalized data model.

derived table

A table created by using a SELECT statement in parentheses in a FROM clause.

deterministic

A characteristic of a function that means the function always returns the same output when presented with the same input. Mathematical functions, such as SQRT, are deterministic because they always return the same output, given the same input.

distributed partitioned view

A view that collects data from two or more instances of SQL Server.

distributor

In SQL Server, the server that contains the distribution database, data history, and transactions used in replication; as its name implies, its job is to distribute data to subscribers.

DML (Data Manipulation Language)

The subset of SQL statements used to retrieve and manipulate data.

DROP

A command used to drop a database object, such as a view or stored procedure. Using DROP removes all the permissions for the object, as well as the object itself. For example, the DROP VIEW statement is used to remove a view or indexed view from the database. Dropping a view removes the definition of a view from the database and an entry in the sysobjects while not affecting the underlying tables and views.

encryption

A method for keeping sensitive information confidential by changing data into an unreadable form.

execution plan

A method in which the query optimizer has chosen to execute a SQL operation.

extended stored procedure

A function in a dynamic link library (DLL) that is coded using the SQL Server Extended Stored Procedure application programming interface (API). The function can then be invoked from T-SQL by using the same statements that are used to execute T-SQL stored procedures. Extended stored procedures can be built to perform functionality not possible with T-SQL stored procedures.

extent lock

A lock covering eight contiguous data or index pages.

federated database servers

A set of linked servers that shares the processing load of data by hosting partitions of a distributed partitioned view.

filegroups

In SQL Server, a named collection of one or more files that forms a single unit of allocation. Filegroups are also used for administration of a database.

FILLFACTOR

An attribute of an index that defines the amount of free space allotted to each page of the index. FILLFACTOR can be used to allocate space for future expansion. FILLFACTOR is a value from 1 through 100 that specifies the percentage of the index page to be left empty.

filter

A set of criteria that controls the set of records returned as a result set. Filters can also define the sequence in which rows are returned.

foreign key

A column or multiple columns whose values match the primary key of another table. Foreign keys help in the relational process between two entities by connecting the foreign attribute in the child entity to a primary key in a parent entity.

fragmentation

A process that occurs when data modifications are made. It is possible to reduce fragmentation and improve read-ahead performance by dropping and re-creating a clustered index.

FROM

The part of the SELECT statement that specifies the tables being accessed. Specifying what tables are being accessed is compulsory for any SELECT data retrieval statement.

Full-Text catalog

A special storage space used to house Full-Text indexes. By default, all Full-Text indexes are housed in a single catalog.

Full-Text index

A special index that efficiently tracks the words you're looking for in a table. It helps in enabling special searching functions that differ from those used in regular indexes.

GROUP BY

The DML operator that creates aggregated sets from a single SELECT statement.

horizontal partitioning

Segmenting a single table into multiple tables based on selected rows. Each of the multiple tables has the same columns but fewer rows.

HTML (Hypertext Markup Language)

A system of marking up, or tagging, a document so that it can be published on the World Wide Web. Documents prepared in HTML include reference graphics and formatting tags. HTML documents are viewed through a web browser (such as Microsoft Internet Explorer).

identity

A column in a table that has been assigned the IDENTITY property, which generates unique incremental numbers.

IN operator

The operator that compares a single value to a set and returns true if the single value occurs within the set.

index

In a relational database, a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The PRIMARY KEY/UNIQUE constraint automatically causes an index to be built. In full-text searches, a full-text index stores information about significant words and their location within a given column.

indexed view

A view that has an index defined onto it. Indexes on views enable view result sets to be stored in the database's physical storage after an index is created. In contrast, in a non-indexed view, the view is activated at runtime, and the result set is dynamically built.

INSERT

A T-SQL command that is used to add one or more records to a table.

INSERT INTO

A T-SQL statement that can be used to insert rows of data into a table when needed.

INSTEAD OF TRigger

A trigger that replaces the action that an INSERT, DELETE, or UPDATE trigger might take.

job

A specified series of operations, called steps, that a SQL Server agent performs sequentially.

join

To combine data in two tables based on matching values found in each of the tables.

key

A column or group of columns that uniquely identifies a row (PRIMARY KEY), defines the relationship between two tables (FOREIGN KEY), or is used to build an index.

latency

The amount of time that elapses when a data change is completed at one server and when that change appears at another within a replication architecture (for example, the time between when a change is made at a publisher and when it appears at the subscriber).

LIKE

A predicate that is used to search through character strings by specifying a search string. A LIKE search is primarily used for searches based on wildcard characters, such as the percent sign (%).

linked server

A database object that represents a particular data source and the attributes, including security and collation attributes, necessary to access the data source.

local partitioned view

A partitioned view in which all member tables reside on the local instance of SQL Server.

lock

A method of ensuring concurrency. Locking enables users to temporarily check out an object, preventing other users from changing the object, for the purpose of ensuring consistency.

log file

A file or set of files containing a record of the modifications made in a database.

log shipping

A process that performs copying, at regular intervals, of the log backup from a read-write database to one or more remote server instances.

master database

The database that controls the operation of each instance of SQL Server. It is installed automatically with each instance of SQL Server and keeps track of user accounts, remote user accounts, and remote servers that each instance can interact with. It also tracks ongoing processes, configurable environment variables, system error messages, tapes and disks available on the system, and active locks.

merge replication

The process of transferring data from the publisher to the subscriber, allowing the publisher and subscriber to update data while connected or disconnected and then merging the updates after they are both connected. Merge replication begins with a snapshot. Thereafter, no data is replicated until the publisher and subscriber do a merge. The merge can be scheduled or done via an ad hoc request. Merge replication's main benefit is that it supports subscribers who are not on the network much of the time. Transactions that are committed, however, may be rolled back as the result of conflict resolution.

metadata

Information about the properties of data, such as the type of data in a column (numeric, text, and so on) or the length of a column. Metadata can also be information about the structure of data or information that specifies the design of objects, such as cubes or dimensions.

Model database

A database that is installed with SQL Server that provides the template for new user databases. SQL Server creates a new database by copying the contents of the Model database and then expanding it to the size requested.

msdb database

A database that the SQL Server agent uses for scheduling alerts and jobs and for recording server operator information.

nonclustered index

An index in which the logical order of the index is different from the physical, stored order of the rows on disk. In contrast to clustered indexes, nonclustered indexes are totally separated from the actual data rows, causing an unsorted order of data based on nonclustered keys. Nonclustered indexes differ from clustered indexes at the leaf level. The leaf level of a nonclustered index contains the key value and the row locator. The row locator is either the physical row address (if there is no clustered index) or the clustered index key value (if a clustered index exists).

nondeterministic

A characteristic of a function that means the function can return different results when provided with the same input. For example, the RAND function is nondeterministic because it returns a different randomly generated number each time it is called.

normalization

Developed by Dr. E. F. Codd in 1970, the process of simplifying data and database design to achieve maximum performance and simplicity. This process involves the removal of useless and redundant data.

ODBC (Open Database Connectivity)

A data access application programming interface (API) that supports access to any data source for which an ODBC driver is available. ODBC is aligned with the American National Standards Institute (ANSI) and International Standards Organization (ISO) standards for a database call level interface (CLI).

OLAP (Online Analytical Processing)

A technology that uses multidimensional structures to provide rapid access to data for analysis. The source data for OLAP is commonly stored in data warehouses in a relational database.

OLE DB

A COM-based application programming interface (API) for accessing data. OLE DB supports accessing data stored in any format (databases, spreadsheets, text files, and so on) for which an OLE DB provider is available.

OLTP (Online Transaction Processing)

A data processing system designed to record all the business transactions of an organization as they occur. An OLTP system is characterized by many concurrent users actively adding and modifying data.

operator

An individual who can potentially receive messages from SQL Server via email, pager, or Net send.

ORDER BY

A substatement found in the SELECT statement that is used to order the rows in a result set in either descending or ascending (DESC and ASC) order.

page lock

A lock that covers 8KB of data.

partitioned view

A table that has been replaced with multiple, smaller tables. Each smaller table has the same format as the original table, but with a subset of the data. Each partitioned table has rows allocated to it based on some characteristic of the data, such as specific key ranges. The rules that define into which table the rows go must be unambiguous. For example, a table is partitioned into two tables. All rows with primary key values lower than a specified value are allocated to one table, and all rows equal to or greater than the value are allocated to the other. Partitioning can improve application processing speeds and reduce the potential for conflicts in multisite update replication. You can improve the usability of partitioned tables by creating a view. The view, created by a union of SELECT operations on all the partitioned tables, presents the data as if it all resided in a single table.

primary key

A column or set of columns uniquely identifying all the rows in a table. Primary keys do not allow null values. No two rows can have the same primary key value; therefore, a primary key value always uniquely identifies a single row. More than one key can uniquely identify rows in a table; each of these keys is called a candidate key. Only one candidate can be chosen as the primary key of a table; all other candidate keys are known as alternate keys. Although tables are not required to have primary keys, it is good practice to define them. In a normalized table, all the data values in each row are fully dependent on the primary key. For example, in a normalized employee table that has EmployeeID as the primary key, all the columns should contain data related to a specific employee. This table does not have the column DepartmentName because the name of the department is dependent on a department ID, not on an employee ID.

profiler

A SQL tool that captures SQL Server events from a server. The events are saved in a trace file that you can later analyze or use to replay a specific series of steps when you want to diagnose a problem.

publication

A container for articles that are capable of being replicated. A publication, which may include one or more articles, is the basic unit of replication. A publication has a single, specific replication type: either snapshot, transactional, or merge. When a subscriber chooses a publication, all the articles contained within the publication are part of the subscription.

publisher

In respect to replication, the server that produces data so that it can be replicated to subscribers.

query optimizer

The SQL Server database engine component responsible for generating efficient execution plans for SQL statements.

RAID (Redundant Array of Independent Disks)

A disk system that comprises multiple disk drives (an array) to provide higher performance, reliability, storage capacity, and lower cost. Fault-tolerant arrays are categorized in six RAID levels: 0 through 5. Each level uses a different algorithm to implement fault tolerance.

RDBMS (Relational Database Management System)

A system that organizes data into related rows and columns. SQL Server is an RDBMS.

rebuilding indexes

A process that helps collect the defragmented pages of information and bring index data back to its original form. Rebuilding indexes increases the overall performance by making it easier for SQL Server to read pages to get data.

recompile

To provide effective processing, the queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

reconfigure

A command used to update the currently configured value of a configuration option changed with the sp_configure system stored procedure.

recordset

The ActiveX Database Objects (ADO) object used to contain a result set. A recordset also exhibits cursor behavior, depending on the recordset properties set by an application. ADO recordsets are mapped to OLE DB rowsets.

recovery model

A database property that controls the basic behavior of backup and restore operations for a database. For instance, the recovery model controls how transactions are logged, whether the transaction log requires backing up, and what kinds of restore operations are available.

recursive trigger

A trigger that updates, deletes, or inserts data into its own table or another table, which houses a trigger, and then fires another trigger.

relational database

A collection of information organized in tables. Each table models a class of objects of interest to the organization. Each column in a table models an attribute of the object. Each row in a table represents one entity in the class of objects modeled by the table. Queries can use data from one table to find related data in other tables.

replication

A set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Replication Monitor

A tool that provides a systemic view of replication activity, focusing on the movement of data between the publisher and the subscribers. Replication Monitor is a tool for watching real-time activity, troubleshooting problems, and analyzing past replication activity.

roll back

To reverse changes made by transactions that were uncommitted at the point in time to which a database is being recovered.

roll forward

To apply logged changes to data in a roll forward set to bring the data forward in time.

rowset

The OLE DB object used to contain a result set. It also exhibits cursor behavior, depending on the rowset properties set by an application.

schema

In the SQL-92 standard, a collection of database objects that are owned by a single user and form a single namespace. A namespace is a set of objects that cannot have duplicate names. For example, two tables can have the same name only if they are in separate schemas; no two tables in the same schema can have the same name. In T-SQL, much of the functionality associated with schemas is implemented by database user IDs. In database tools, a schema is also the catalog information that describes the objects in a schema or database. In analysis services, a schema is a description of multidimensional objects, such as cubes and dimensions.

SCHEMABINDING

An option for a user-defined function or a view that prevents changes to the objects referenced by the function or view unless you first drop the view. This makes the views and functions more reliable because they can rely on their database objects always being present.

scope

The lifetime of an object. Specifically, a variable has a scope within a single batch, which means it ceases to exist outside the batch.

script

A collection of batches, usually stored in a text file.

SELECT

The T-SQL statement that is used to return data to an application or another T-SQL statement or to populate a cursor. The SELECT statement returns a tabular result set consisting of data that is typically extracted from one or more tables. The result set contains data from only those rows that match the search conditions specified in the WHERE or HAVING clauses.

SET

The statement used to alter environment settings for a session.

snapshot replication

A type of replication in which data and database objects are distributed by copying published items via the distributor and on to the subscriber exactly as they appear at a specific moment in time. Snapshot replication provides the distribution of both data and structure (tables, indexes, and so on) on a scheduled basis. It can be thought of as a "whole table refresh." No updates to the source table are replicated until the next scheduled snapshot.

SNMP (Simple Network Management Protocol)

A protocol that is used for troubleshooting and querying TCP/IP servers.

SQL (Structured Query Language)

A language used to insert, retrieve, modify, and delete data in a relational database. SQL also contains statements for defining and administering the objects in a database. SQL is the language supported by most relational databases, and is the subject of standards published by the International Standards Organization (ISO) and the American National Standards Institute (ANSI). SQL Server uses a version of the SQL language called T-SQL.

SQL (Structured Query Language) Profiler

A tool used to trace SQL Server activity.

statement permissions

An attribute that controls whether a user can execute CREATE or BACKUP statements.

statistics

Information about the distribution of the key values in each index and uses these statistics to determine what index(es) to use in query processing.

stored procedure

A collection of T-SQL statements with a well-defined set of inputs, called input parameters, and a well-defined set of outputs, which may be output parameters, return values, or cursors. Stored procedures allow the encapsulation of various database operations.

string concatenation

Combining of two strings, such as the results of the first name and last name columns. String concatenation can be performed using the plus (+) operator.

subscriber

The server that receives replicated data (in the form of publications) from the publisher.

System Monitor

The performance monitoring tool available in Windows 2000 and later operating systems. Historically also known as Performance Monitor (PerfMon).

T-SQL (Transact-SQL)

The language containing the commands used to administer instances of SQL Server, create and manage all objects in an instance of SQL Server, and insert, retrieve, modify, and delete all data in SQL Server tables. T-SQL is an extension of the language defined in the SQL standards published by the International Standards Organization (ISO) and the American National Standards Institute (ANSI).

table

A two-dimensional object consisting of rows and columns that is used to store data in a relational database. Each table stores information about one of the types of objects modeled by the database.

table lock

A lock on a table, including all data and indexes.

TCP/IP (Transmission Control Protocol/Internet Protocol)

An industry-standard network protocol used by most companies for internetworking computer equipment.

tempdb

The database that provides a storage area for temporary tables, temporary stored procedures, and other temporary working storage needs.

TOP

A keyword that can be used in conjunction with the SELECT statement to select the top n rows or a percentage of the result set rows.

trace

The SQL Profiler method for recording server events.

trace flags

Flags that can be enabled to aid in troubleshooting.

transactional replication

A type of replication in which data and database objects are distributed by first applying an initial snapshot at the subscriber and then later capturing transactions made at the publisher and propagating them to individual subscribers. Transactional replication, as with all other replication types, begins with a synchronizing snapshot. After the initial synchronization, transactions, which are committed at the publisher, are automatically replicated to the subscribers.

trigger

A stored procedure that is fired when data is modified from a table using any of the three modification statements DELETE, INSERT, or UPDATE. FOR and AFTER are synonymous and are usually implied when referring to triggers rather than INSTEAD OF TRiggers. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.

UNION operator

An operator that can combine two SELECT statements that have identical column numbers and types into one large rowset.

UNIQUE constraint

A constraint that enforces entity integrity on a non-primary key. UNIQUE constraints ensure that no duplicate values are entered and that an index is created to enhance performance.

UNIQUE index

An index in which no two rows are permitted to have the same index value, thus prohibiting duplicate index or key values. The system checks for duplicate key values when the index is created and checks each time data is added with an INSERT or UPDATE statement.

UPDATE

A statement used to modify one or more data values in an existing row or rows. Sometimes, the term update refers to any data modification, including INSERT, UPDATE, and DELETE operations.

UPDATE STATISTICS

A command that updates statistical information for an index. Index statistics need to be up-to-date for the optimizer to decide on the fastest route of access.

updatable subscribers

Subscribers that are capable of updating and modifying data when it is replicated. This option can be used with snapshot replication and transactional replication. A transactional or snapshot publication may allow updatable subscribers. Changes made on the subscriber's replica are propagated to the publisher either in real time via DTC or near real time via a queue.

user

A databasewide security context.

user-defined function

A collection of T-SQL statements with a well-defined set of input parameters but only one outputwhich can be a scalar value or a table. User-defined functions allow the encapsulation of various logical and database operations but cannot be used to make changes to a database.

variable

A construct that can temporarily hold values for use in a T-SQL batch.

view

A relational database object that can be referenced and built by using SELECT statements to join data from one or more base tables. Views are similar to tables in that data can be retrieved and modified and indexes can be built.

WHERE

A substatement found in the SELECT statement that uses any of various filter conditions, such as BETWEEN, IN, and LIKE, to limit the number of rows retrieved.

Windows application log

The operating system event log used to record application events sent by SQL Server services.

WITH ENCRYPTION

A clause that protects the definition of a view. If you specify WITH ENCRYPTION, you encrypt the definition of your view because you may not want users to display it. Encrypting using WITH ENCRYPTION disallows anyone from using sp_heptext to display your view or viewing it via the Enterprise Manager.

WITH SCHEMABINDING

An option that specifies that the view be bound to the schema. You need to specify WITH SCHEMABINDING when you want to create views with indexes. Also, when WITH SCHEMABINDING is specified, you have to adhere to the owner.object syntax when referencing tables or views in the creation of a view.

XML (Extensible Markup Language)

A hypertext programming language used to describe the contents of a set of data and how the data should be output to a device or displayed in a web page. XML is used to move data between systems.