SQL-Server Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z


A

Top
Autoshrink option Tries to shrink the database without manual intervention. It does so five minutes after startup and every thirty minutes thereafter. The file is shrunk to a size where 25% of the file is unused space or to the size of the file when it was created, whichever is greater.

B

Top

Batch

A batch is a set of Transact-SQL statements that are submitted together and executed as a group. Use a GO statement in SQL Query Analyzer and the osql utility to signal the end of a batch. The scope of user-defined variables is limited to a batch.
Statements which must run in seperate batches:

  • CREATE PROCEDURE

  • CREATE VIEW

  • CREATE TRIGGER

  • CREATE RULE

  • CREATE DEFAULT
Bulk Changed Map page Information about extents modified by bulk operations since the last BACKUP LOG statement.

C

Top
Cascading Referential Integrity Cascading referential integrity automatically propagates changes to the database.
Check (Domain) Type of integrity: Domain
Specifies data values that are acceptable in a column.
Check (Referential) Type of integrity: Referential
Specifies data values that are acceptable in a column based on values in other columns in the same table.
  • Are Used with INSERT and UPDATE Statements
  • Can Reference Other Columns in the Same Table
  • Cannot:
    - Be used with rowversion data type
    - Contain subqueries

Clustered Index

In a clustured index, the leaf level is the actual data page. Data is physically stored on a data page in ascending order. The order of the values in the index pages is also ascenging.

  • Each Table Can Have Only One Clustered Index

  • the Physical Row Order of the Table and the Order of Rows in the Index Are the Same

  • Key Value Uniqueness is maintained Explicitly or Implicitly

  • Average Size is About 5% of the Table Size

  • During Index Creation SQL Server requires about 1.2 (1 = data and .2 = index) Times the Table Size.

Clustered Table

Table with a clustered index

Collation

A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared.
Composite Index Composite indexes specify more than one column as the kay value.
  • Maximum 16 Columns
  • Maximum Total Length is 900 Bytes
  • Define the Most Unique Column First
  • The WHERE Clause Must Reference the first Column in the Index For the Query Optimizer to use the Composite Index
Constraint Preferred method of enforcing data integrity.
Constrains types:

D

Top
Data Integrity Integrity types:
Data page Contains content other than text, ntext and image data.
Declarative Data Integrity Criteria that the data must meet as a part of an object definition. SQL-Server automatically ensures that the data conformes to the criteria.
Default Type of integrity: Domain
Specifies the value that will be provided for the column when a value has not been explicitly supplied in an INSERT statement.
  • Apply Only to INSERT statements
  • Only One DEFAULT Constraint Per Column
  • Cannot Be Used with IDENTITY Property or rowversion Data Type
  • Allow Some System-supplied Valies
  • Are defined once
  • Can be bound to one or more columns or user defined data types
Density Density is the average percentage of duplicate rows in an index. If the data or query is not very selective (low selectivity) you have a high amount of density.
Differential Changed Map page Information about extents that have changed since the last BACKUP DATABASE statement.

E

Top

Extent

8 Pages (64 KB), 16 extents per megabyte.
Mixed extent   Contains pages from one or more objects. Every table starts as a mixed extend.
Uniform etxent   All eight pages are allocated to a single object. They are used when tables or indexes need more than 64 KB of space,

F

Top
Fill Factor FILLFACTOR is a value from 1 through 100 that specifies the percentage of the index page to be left empty.
In other words: The FillFactor property exposes the percent of each page used to store index data when the index is created.
An attribute of an index that defines the amount of free space an each page of the index. FILLFACTOR accommodates future expansion of table data and reduces the potential of page splits.
Foreign Key Type of integrity: Referential
Defines a column or combination of columns with values that match the primary key of the same or another table.
  • Must Reference a PRIMARY KEY or UNIQUE constraint
  • Provide Single or Multicolumn Referential Integrity
  • Do Not Automatically Create Indexes
  • Users Must Have SELECT or REFERENCES Permissions on Referenced tables
  • Use Only REFERENCES Clause Within Same Table

G

Top
GAM page Global Allocation Map.
The GAM page is an allocation page that contains information about allocated extents. Page 2 of each file is a GAM page. SQL Server adds additional GAM pages as needed.

Each GAM page covers 63'904 extents, or nearly 4 GB of data. The GAM page contains one bit for each extent that it covers. the bit is set to 0 if the extent is allocated, and set to 1 if it is free.
Ghost Record Deleted rows from the leaf level of an index marked as invalid.

H

Top

Heap

Table with no clustered index.

Collection of data pages for a table.

I

Top
IAM page Index Allocation Map.
The IAM page is an allocation page that contains information about the extents that a table or index uses.

The IAM page contains the location of the eight initial pages and a bitmap of extents indicating which extents are in use for that object. A single IAM page can track up to 512'000 data pages (4'000 MB). SQL Server adds more IAM pages for larger tables.

Each object has at least one IAM for each file on which it has extends.
Index Node Page in an index.
Index page Contains index structures.

Indexed view

See materialized view
Information Schema Views There are 20 views:
  • CHECK_CONSTRAINTS
  • COLUMN_DOMAIN_USAGE
  • COLUMN_PRIVILEGES
  • COLUMNS
  • CONSTRAINT_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • DOMAIN_CONSTRAINTS
  • DOMAINS
  • KEY_COLUMN_USAGE
  • PARAMETERS
  • REFERENTIAL_CONSTRAINTS
  • ROUTINE_COLUMNS
  • ROUTINES
  • SCHEMATA
  • TABLE_CONSTRAINTS
  • TABLE_PRIVILEGES
  • TABLES
  • VIEW_COLUMN_USAGE
  • VIEW_TABLE_USAGE
  • VIEWS

L

Top
Leaf Level The bottom level of a clustered or nonclustered index. The leaf level contains the actual data pages of the table. In a nonclustered index the leaf level either points to data pages or points to the clustered index (if one exists) rather than containing the data itself.

M

Top

Materialized view

Indexed view. The first index is always a clustered index. It has the same structure as a table that has a clustered index and is stored in the database.
Merge replication Merge replication allows various sites to work autonomously (online or offline) and merge data modifications made at multiple sites into a single, uniform result at a later time. The initial snapshot is applied to Subscribers and then SQL Server 2000 tracks changes to published data at the Publisher and at the Subscribers.
The data is synchronized between servers either at a scheduled time or on demand. Updates are made independently (no commit protocol) at more than one server, so the same data may have been updated by the Publisher or by more than one Subscriber.
Therefore, conflicts can occur when data modifications are merged.
Mixed extent See extent.

N

Top
Null block A null block is a variable-length set of bytes. It constists of 2 bytes storing the number of columns followed by a null bitmap inicating wether each individual column is null. The size of the null bitmap is equal to one bit per column, rounded to the nearest byte. One to eight columns require a 1-byte bitmap. Nine to sixteen columns require a 2-byte bitmap.

O

Top
Object SQL-Server supports the following objects:
  • Table
  • Data Type
  • View
  • Stored Procedure
  • Function
  • Index
  • Constraint
  • Rule
  • Default
  • Trigger

P

Top
PAD_INDEX The PAD_INDEX option specifies the percentage to which to fille the non-leaf -level index pages. You can use the PAD_INDEX only in when FILLFACTOR is specified, because the PAD_INDEX percentage is determined by the percentage value specified for FILLFACTOR.
  • Applies to Non-Leaf-Level Index Pages
  • If PAD_INDEX Is Not Specified, the Default Leaves Space for One Row Entry in Non-Leaf-Level Pages
  • Number of Rows on Non-Leaf-Level Pages Is Never Less than Two
  • PAD_INDEX Uses the FILLFACTOR Value
PAG Page number. The page is identified by a fileid:page combination, where fileid is the fileid in the sysfiles table, and page is the logical page number within that file.

Page

8 KB, 128 Pages per megabyte.

Type of pages:
Page Split The process of moving half the rows or entries in a full data or index page to two new pages to make room for a new row or index entry.
PFS page Page Free Space page. This page is an allocation page that contains information about free space available on the pages in a file. Page 1 of each file is a PFS page. SQL Server adds other PFS pages as needed.

Each PFS page can track 8'000 contiguos pages, which is nearly 64 MB of data. For each page, the PFS page contains a byte that tracks:

  • Wether the page has been allocated.
  • Wether the page is on a mixed or uniform extent.
  • An approximation of how much space is availale on the page.
Primary Key Type of integrity: Entity
Uniquely identifies each row - ensures that users do not enter duplicate values and that an index is created to enhance performance. NULL values are not allowed.
  • Only One PRIMARY KEY Constraint Per Table
  • Values Must Be Unique
  • NULL Values Are Not Allowed
  • Creates a Unique Index on Specified Column
Procedural Data Intergity Scripts that define both the criteria that the data must meet and enforce the criteria.
Use declarative data intergrity whenever possible.

R

Top
Referential Type of integrity: Domain
Specifies the data values that are acceptable to update, based on values in a column in another table.
Replication
RID Row Identifier.
Used to lock a single row within a table

S

Top
Selectivity Selectivity is derived from the percentage of rows in a table that are accessed or returned by a query.
SGAM Secondary Global Allocation Map.
This is an allocation page that contains information about allocated mixed extents. Page 3 of each file is a SGM page. SQL Server adds additional SGAM pages as needed.

SGAM pages track mixed extents that currently have at least one unused page. They cover 63'904 extents. A bit set to 0 indicates that an extent is either a uniform or a mixed extent without any free pages. A bit set to 1 indicates a mixed extent with one ore more free pages.
Snapshot replication Snapshot replication copies the entire publication from one SQL-Server computer to another.
Snapshot replication does not require continuous monitoring of changes because changes made to published data are not propagated to the Subscriber incrementally.

SQL Server Collation

Each SQL Server collation specifies three properties:

  • The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
  • The sort order to use for non-Unicode character data types (char, varchar, and text).
  • The code page used to store non-Unicode character data.

T

Top
Tabular Data Stream See TDS.
Text/Image page Contains text, ntext and image content.

TDS

Tabular Data Stream. SQL-Server specific application-level protocol.

  • TDS 8.0 - SQL Server 2000 Clients

  • TDS 7.0 - SQL Server 7.0 Clients

  • TDS 4.2 - SQL Server 6.5, 6.0 or 4.21 clients
Transactional replication With transactional replication, an initial snapshot of data is propagated to Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

U

Top
Uniform extent See extent.
Unique Type of integrity: Entity
Prevents duplication of alternate (non-primary) keys and ensures that an index is created to enhance performance. NULL values are allowed.
  • Allow One NULL Value
  • Allow Multiple UNIQUE Constraints on a Table
  • Defined with One or More Columns
  • Enforced with a Unique Index

V

Top
Variable block A variable block consists of two bytes that describe how many variable-length columns are present. An additional two bytes per column point to the end of each variable-length column. The variable block is ommited if there are no variable-length columns.

W

Top
WITH CHECK OPTION The WITH_CHECK_OPTION forces all data modification statements that are executed against the view to adhere to certain criteria. These criterias are specified within the SELECT statement that defines the view. If the changed values are out of the range of the view definition, SQL Server rejects the modifications.
If you use this clause, rows cannot be modified in a way that causes them to disappear from the view.