A

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

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

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:

• Domain
  - Default, Check, Referential
• Entity
  - Primary Key, Unique
• Referential
  - Foreign Key, Check

D

Data Integrity

Integrity types:

• Declarative Data Integrity
• Procedural Data Intergity

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

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

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

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

Heap

Table with no clustered index.

Collection of data pages for a table.

I

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

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

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

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

Object

SQL-Server supports the following objects:

• Table
• Data Type
• View
• Stored Procedure
• Function
• Index
• Constraint
• Rule
• Default
• Trigger

P

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:

§         Pages that track space allocation
- PFS, GAM, SGAM, IAM

§         Pages that contain user and index data
- Data, Text/Image, Index

§         Other
- Bulk Changed Map, Differential Changed Map

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

Referential

Type of integrity: Domain
Specifies the data values that are acceptable to update, based on values in a column in another table.

Replication

• merge
• snapshot
• transactional

RID

Row Identifier.
Used to lock a single row within a table

S

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

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

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

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

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.