Appendix B. Glossary | SQL Performance Tuning

This glossary contains only terms that specifically are used for SQL optimization. For terms that apply to the subject of SQL in general, consult the 1,000- term glossary on our Web site, ourworld. compuserve .com/homepages/OCELOTSQL/glossary.htm.

Before the definition there may be a "Used by" note. For example, "Used by: Microsoft, Sybase" indicates that Microsoft and Sybase authorities prefer the term and/or definition. The words "Used by: this book only" indicate a temporary and non-standard term that exists only for this book's purposes.

When a word has multiple meanings, the first definition is marked "[1]" and subsequent definitions are marked with incremented numbers . " See " and " see also " refer to other terms in this glossary.

Numbers/Symbols

_rowid: Used by: MySQL; See also [ row identifier ]

1NF: First normal form table, a table that contains only scalar values.

2NF: Second normal form table, a 1NF table that contains only columns that are dependent upon the entire primary key.

3NF: Third normal form table, a 2NF table whose non-key columns are also mutually independent; that is, each column can be updated independently of all the rest.

A

access plan: The plan used by the optimizer to resolve an SQL statement.

ADO: ActiveX Data Objects, an API that enables Windows applications to access a database.

aggregate function: See [ set function ]

API: Application Programming Interface, the method by which a programmer writing an application program can make requests of the operating system or another application.

applet: A Java program that can be downloaded and executed by a browser.

B

B-tree: A structure for storing index keys; an ordered, hierarchical, paged assortment of index keys. Some people say the "B" stands for "Balanced."

back compression: Making index keys shorter by throwing bytes away from the back.; See also [ compression ]; See also [ front compression ]

balanced tree: See [ B-tree ]

BDB: Berkeley DB, an embedded database system that is bundled with MySQL.

big-endian: A binary data transmission/storage format in which the most significant bit (or byte) comes first.

binary sort: A sort of the codes that are used to store the characters .

bitmap: Multiple lines of bits.

bitmap index: An index containing one or more bitmaps.; See also [ bitmap ]

bit vector: A line of bits.

block

Used by: Oracle

[1] Oracle-speak for the smallest I/O unit in a database. All other DBMSs use the word page.

[2] When Job #1 has an exclusive lock on Table1 and Job #2 tries to access Table1 , Job #2 must waitit is blocked.

C

cartesian explosion: The effect on the size of a cartesian join's product as the joined tables grow in size. During a join, the DBMS creates a temporary table to hold the join result. The temporary table's size is the product of the sizes of the two original tables, which means the processing time goes up geometrically if Table1 and Table2 get bigger.

cartesian join: The set of all ordered pairs {a , b} where a is a member of set A and b is a member of set B . In database terms, a cartesian product joins all rows in Table1 with all rows in Table2 . Thus if Table1 has the values {T_a1 , T_b1} and Table2 has the values {T_a2 , T_b2} then the cartesian product is {(T_a1 , T_a2) (T_a1 , T_b2) (T_b1 , T_a2) (T_b1 , T_b2)} .Cartesian products are useful for explanation, but when we see an operation which "goes cartesian," we usually criticize the optimizer. Also known as a cross join.

cartesian product: See [ cartesian join ]

case insensitive: A sort order that considers 'SMITH' and 'Smith' to be equal.

case sensitive: A sort order that considers 'SMITH' and 'Smith' to be two different strings.

CBO: Cost-based optimizer, an optimizer that uses volatile data (e.g., the row and column values that have been inserted) and an override (e.g., that the database contents are more important than the fixed optimizing assumptions) to determine the optimal query resolution plan. A cost-based optimizer is a type of rule-based optimizer that has additional volatile information available to it so that it can override a fixed assumption.

CGI: Common Gateway Interface, a standard way for a Web server to pass a Web user's request to an application program and to receive data back to forward to the user .

cluster

Used by: Microsoft, Sybase

A structure for storing data in a specific order; that is, an index that has data pages at the leaf layer. The main idea behind clusters is that all rows should be kept in order permanently according to some column value, such as the primary key.

clustered index: An index that the DBMS uses to determine the order of data rows, according to values in one or more columns, called the cluster key. With a strong-clustered index, the data pages are the index's leaves and are thus always in order. With a weak-clustered index, data pages are separate from index leaf pages and the rows need not be 100% in order. The terms weak clustered index and strong-clustered index are not common usage; they appear only in this book.

clustered key or clustered index key: The column chosen to be the index key for a clustered index.

collating sequence: See [ collation ]

collation: A set of rules that determines the result when character strings are compared.

composite index: See [ compound index ]

composite table: A table that contains column values derived from two or more other tables.

compound index: An index whose keys contain values derived from more than one data column.

compression: Making index keys shorter by throwing bytes away from the front or from the back.; See also [ back compression ]; See also [ front compression ]

concurrency: The ability of multiple transactions to share the same database simultaneously .

concurrent transactions: Two transactions that have overlapping start or end times. To prevent concurrent transactions from interfering with each other, the DBMS may arrange a lock.

connection pooling: A facility that allows connections to a data source to be stored and reused. A Java term.

constant propagation: Substituting a constant value for an expression.

control interval: See [ page ]

cost: The total execution time required for an SQL statement.

cost-based optimizer: See [ CBO ]

covering index: An index that contains every column in the select list of a query.

cursor: A marker that indicates the current position within a result set.

D

data block: See also [ block ]; See also [ page ]

data change statement: An SQL statement that changes data; INSERT, UPDATE, or DELETE.

data source: A repository for storing data. An ODBC/JDBC term.

DBA: Database Administrator, the individual who directs or performs all activities related to maintaining a successful database environment.

dbc: Database connection, an ODBC resource.

DBMS: Database Management System, a program that lets one or more computer users create and access the data in a database.

dbspace: Used by: Informix; See also [ tablespace ]

DDL: Data Definition Language, SQL statements that define or destroy data objects, like CREATE TABLE, ALTER TABLE, and DROP TABLE.

deadlock: A condition that arises when two or more transactions are waiting for one another to release locks.

denormalize: Break the normalization rules deliberately, in an attempt to gain speed or save space.

denormalization: See [ denormalize ]

denormalizing: See [ denormalize ]

dense index: See [ unique index ]

density (of an index or table)

The reciprocal of the count of the number of distinct keys. For example, suppose you have these key values: {A,C,C,D,D} . The number of distinct keys, that is, the number that a SELECT DISTINCT ... statement would return, is three: {A,C,D} . Now recall from your arithmetic classes that reciprocal = 1/Nthat is, the reciprocal of a number N is the number that would yield one when multiplied by N . The reciprocal of 3 is 1/3; therefore, the density for our example is 1/3. It's possible to get a preliminary guess of the number of rows that column1 = <literal> will return by multiplying the table's cardinality by the density of the index.

WARNING: The following definitions of density from vendor manuals or other texts are imprecise or confusing: "the average number of rows which are duplicates" (Sybase); "density is inversely proportional to index sensitivity" (various).

dependence: A concept used in normalization. If the value of column1 uniquely determines the value of column2 , then column2 is functionally dependent on column1 . If the value of column1 limits the possible values in column2 to a specific set, then column2 is set dependent on column1 .

desc: Descriptor for SQL statement parameters, an ODBC resource.

deterministic (function): A function that always generates the same outputs, given the same inputs.

dictionary sort: A sort that results in a list that is very close to what one expects to see in an English dictionary.

dictionary sort with tie-breaking: A dictionary sort with multiple passes to sort accented characters differently from unaccented characters and uppercase letters differently from lowercase letters .

Dirty Read: A problem arising with concurrent transactions. The Dirty Read problem occurs when a transaction reads a row that has been changed but not committed by another transaction. The result is that Transaction #2's work is based on a change that never really happened . You can avoid Dirty Read by using an isolation level of READ COMMITTED or higher.

DML: Data Manipulation Language, SQL statements that manipulate data, like INSERT, UPDATE, and DELETE.

driven table: See [ inner table ]

driver: The table that the DBMS examines first when evaluating a join or subquery expression.

driving table: See [ outer table ]

DSS: Decision Support System, a computer application that provides support for business decision making.

E

elevator seeking: Travelling through a disk's physical locations in the manner of an elevator, instead of jumping backward or forward for each request, an operating system term.

Enterprise Bean: A server component written in Java.

env: Environment, an ODBC resource.

equijoin: A join using the equals operator in the join expression.

escalation: The act of consolidating several real or potential small-grain locks into a single big-grain lock. Concurrency gets worse , but overhead gets better.

exclusive (lock mode): A lock that may not coexist with any other lock on the same object.

expanding update: A data-change statement that increases the size of a row.

extent: A group of pages that are allocated together, as part of the initial creation of an object or when an existing extent becomes full.; See also [ read group ]

F

fetch loop: A loop that contains calls to a function that takes a row from a result set.

file: A group of contiguous extents.

FILLFACTOR

Used by: Informix, Ingres, Microsoft, Sybase

Percent (of a page) to fill.

G

granularity (of a lock): The size of the locked areadatabase, file, table, page, row, or column.

GUID

Used by: Microsoft

Global Unique Identifier, the Microsoft unique-key generator.

H

hash: A number (often a 32-bit integer) that is derived from column values using a lossy compression algorithm. DBMSs occasionally use hashing to speed up access, but indexes are a more common mechanism.

hash join

A method for producing a joined table. Given two input tables Table1 and Table2 , processing is as follows :

(a) For each row in Table1 , produce a hash. Assign the hash to a hash bucket.

(b) For each row in Table2 , produce a hash. Check if the hash is already in the hash bucket. If it is: there's a join. If it is not: there's no join.

heap: A structure for storing data in an unstructured manner. When you add something to a heap, it goes wherever there is free space, which probably means at the end. Existing data is not moved to make free space available for new data.

heap-organized table: See [ heap ]

histogram: Detailed information on the distribution of values over a column; information stored for the sake of the optimizer.

hoisting: Taking an invariant assignment out of a loop.

host program: A computer program, written in a non-SQL language, containing calls to an SQL API or containing embedded SQL statements.

hot spot: A page in either the index or data file that every job wants to access at the same time.

I

impedance: A lack of correspondence between two data types, one of which is being assigned to the other.

index-organized table: Used by: Oracle; See also [ cluster ]

inner table: The table in the inner loop of a nested-loop join. When you write an SQL statement with an inner join, the inner table is determined by the DBMS based on its join strategy for that statement. When you write an outer join, though, the order of the join determines the inner table: for the join expression Table1 LEFT JOIN Table2 the inner table must be Table2 and for Table1 RIGHT JOIN Table2 the inner table must be Table1 .

in-place update

Used by: Microsoft, Sybase

A data change that does not cause a row to move.

J

JDBC: Java Database Connectivity, an API for SQL programs.

JNDI: Java Naming and Directory Interface, an API that provides naming and directory services for Java programs.

join: The relational operator that allows data from multiple tables to be combined. A join matches rows of multiple tables based on columns with common values.

join expression: The WHERE clause search condition that tells the DBMS over which columns it is to join two tables.

join index: An index that contains keys from more than one table, interspersed, to aid with join expressions.

JVM: Java Virtual Machine, a platform-independent programming language that converts Java bytecode into machine language and executes the result.

L

latch: A low-level on-off mechanism that ensures two processes or threads can't access the same object at the same time.; See also [ lock ]

leaf (page of an index): A page at the bottom level of a B-tree (the page at the top level is the root). Typically a leaf page contains pointers to the data pages (if it's a non-clustered index) or to the data itself (if it's a clustered index).

little-endian: A binary data transmission/storage format in which the least significant bit (or byte) comes first.

load: See [ bulk INSERT ]

LOB: Large object, for example, a BLOB (binary large object) or a CLOB (character large object).

lock: A method the DBMS uses to prevent concurrent transactions from interfering with one another. Physically, a lock is one of three things: a latch, a mark on the wall, or a RAM record.

locking level: See [ granularity (of a lock) ]

lock mode: The type of lock a DBMS has arranged for a transaction. Options are exclusive, shared, or update.

lock record: See [ RAM record ]

lock type: See [ lock mode ]

Lost Update: A problem arising with concurrent transactions. The Lost Update problem occurs when two transactions read and make a change to the same row. When the first transaction COMMITs, its change is lost because the second transaction's change supersedes it. The result is as if Transaction #1's change never happened. You can avoid Lost Update by using an isolation level of READ UNCOMMITTED or higher.

LRU: Least-Recently-Used, an algorithm that replaces the page that hasn't been accessed for the longest time

M

mark on the wall: An ITL slot or mark put against a row by the DBMS. By putting a mark right beside the row accessed by a transaction, the DBMS ensures that no other transaction has to spend much time checking whether the row is locked.; See also [ lock ]

materialization: Temporarily writing rows; usually this means physically copying data from the DBMS server to the client. To evaluate some expressions, the DBMS will create a temporary table, put the rows from the original table(s) into the temporary table, and select from the temporary copy; this is known as materialization.

materialize: See [ materialization ]

materialized view: A view whose rows take up space. When you select from a view, the DBMS can elect to do one of two things: (a) it can get the rows from the original table, convert any derived columns, and pass the results to the application or (b) it can create a temporary table and put the rows from the original table(s) into the temporary table, then select from the temporary copy. The latter case results in a materialized view. Materialization is often necessary when there is no one-to-one correspondence between the original table's rows and the view's rows (because there is a grouping) or when many tables are affected and concurrency would be harmed (because there is a join).

merge join: See [ sort-merge join ]

merge scan: See [ sort-merge join ]

method: A procedure associated with a Java class or interface.

migrated row: A row at the end of the table due to an expanding update overflow.

migration: The process of finding a home for an expanding update. When a page overflows due to a data change that increases the length of a variable-length column, a row must be shifted to another page. In practice, a pointer to the overflow page is left in the place of the original row.; See also [ shift ]

multi-column index: See [ compound index ]

MVCC: Multi Version Concurrency Control, a mechanism that sometimes doesn't use shared locks for rows.; See also [ versioning ]

N

NaN: Not a Number. In the IEEE definition for floating-point numeric representation, a NaN is a number that cannot be represented (such as the result after a division by zero) and is thus assigned a special code.

nested-loop join

A method for producing a joined table. Given two input tables Table1 and Table2 , processing is as follows:

 for (each row of Table1) {   for (each row of Table2) {     compare and produce joined (Table1, Table2) row   } }

Notice the for loop nested within a for loop.

niladic function: A function that has no arguments, for example, SQL's CURRENT_DATE and CURRENT_USER.

non-clustered index: An index with a structure completely separate from the data rows it indexes; normally a B-tree. The lowest rows of a non-clustered index contain the index key values, with each key value pointing to the data rows that contain that key. The data rows are not stored in an order based on the index key, but rather in a heap.

non-deterministic (function): A function that may generate different outputs each time it is run, even if the inputs are always the same.

Non-repeatable Read: A problem arising with concurrent transactions. The Non-repeatable Read problem occurs when a transaction reads a row twice, once before and once after another transaction does a data change. The result is that Transaction #1 gets conflicting data from its reads. You can avoid Non-repeatable Read by using an isolation level of REPEATABLE READ or higher.

normalization: The process of designing a database so that its tables follow the rules specified by relational theory. In practice, this usually means that all database tables are in third normal form.; See also [ 1NF ]; See also [ 2NF ]; See also [ 3NF ]

normalize: To rearrange columns within tables or move columns from one table to another, as part of the database design process, according to rules based on relational theory. In a normalized table, one set of columns is the primary key (which uniquely identifies a row of the table) and all other columns are functionally dependent upon the entire primary key.; See also [ 1NF ]; See also [ 2NF ]; See also [ 3NF ]

normalizing: See also [ normalization ]; See also [ normalize ]

NTFS: NT File System, the file system that the Microsoft Windows NT operating system uses for storing and retrieving files.

O

ODBC: Open Database Connectivity, an API for SQL programs.

OLTP: Online Transaction Processing, a type of computer processing in which the system responds immediately to user requests.

optimistic locking: Locking that assumes conflict is unlikely . Generally , this means avoiding locks and checking for conflict between two transactions only after data changes have been made.

outer table: The table in the outer loop of a nested-loop join. When you write an SQL statement with an inner join, the outer table is determined by the DBMS based on its join strategy for that statement. When you write an outer join, though, the order of the join determines the outer table: for the join expression Table1 LEFT JOIN Table2 the outer table must be Table1 and for Table1 RIGHT JOIN Table2 the outer table must be Table2 .

out-of-place update: Used by: Microsoft, Sybase A data change that causes a row to move.

out-to-in: A plan to process a subquery; for each row in the outer query, lookup in the inner query.

P

packed decimal: A number representation where each number is expressed as a sequence of decimal digits and then each decimal digit is encoded as a four-bit binary number (or nibble). In some cases, the rightmost nibble contains the sign (positive or negative).

page: A fixed-size hopper that stores rows of data or index keys; a minimal unit for disk I/O. Depending on the DBMS, a page is also called a data block, a block, a blocking unit, a control interval, or a row group.

page read: A transfer from disk to memory.

partition

Used by: everyone except Informix

A group of contiguous extents. Often a partition is a file, but it doesn't have to be.

Informix calls this a fragment.

partitioning

Used by: everyone except Informix

The process of splitting a database object (usually a tablespace, table, or index) into two or more physical locations, or partitions, that is, a splitting of a logical group of pages (for example, the pages of a table) into chains or files which are physically removed from each other, perhaps on separate disks. Informix calls this fragmentation.

PCTFREE

Used by: IBM, Oracle

Percent (of a page) to leave free.

Q

query: A SELECT statement.

R

RAM record: Used by: this book only. The usual mechanism for locks of rows, pages, or tables, a RAM record is a permanent memory area containing data that describes which objects are locked as well as how the objects are locked (e.g., shared, exclusive, update, etc.).

RBO: Rule-based optimizer, an optimizer that arranges execution plans using fixed data and assumptions, such as statement syntax and existence of indexes.

R-C

Used by: this book only

Non-standard abbreviation for the READ COMMITTED transaction isolation level.

read: See [ page read ]

READ COMMITTED: A transaction isolation level that ensures mandatory shared locks but allows them to be released before the transaction ends. READ COMMITTED tells the DBMS you want it to allow reading of rows that have been written by other transactions only after they have been committed.

read group: A group of contiguous pages that are read together.

read-only transaction: A transaction that doesn't do a data change.

READ UNCOMMITTED: A transaction isolation level that ensures no locks are issued and no locks are checked during the transaction. READ UNCOMMITTED tells the DBMS you want it to allow reading of rows that have been written but not committed by other transactions.

REPEATABLE READ: A transaction isolation level that ensures mandatory shared locks, which will not be released until the transaction ends. REPEATABLE READ tells the DBMS it must not allow a situation where one transaction gets two sets of data from two reads of the same row because a second transaction changed that row between the two reads.

response time: The time a statement takes to execute.

restrict: Decide which rows will be operated on (using a predicate); a relational-theory term. The restrict operation picks the rows that will be in the result set by evaluating an SQL statement's search condition. Also known as filter.

restrictive expression: An expression which causes a restrict, that is, the part of an SQL statement's search condition that tells the DBMS which rows belong in the result set.

result set: What a SELECT statement (or an equivalent to a SELECT, such as a catalog function or a stored procedure that contains a SELECT) returns.

result set size: The number of rows in a result set, that is, the numbers of rows selected.

RID: Used by: IBM, Microsoft, Sybase; See also [ row identifier ]

RLE

Used by: InterBase

Run-Length Encoding, a compression method that converts consecutive identical characters into a code consisting of the character and the number marking the length of the run. For example, if a string is 'AAAAA' then the storage is (5)(A) .

root (node of an index): A B-tree index is hierarchical, with a top page (where searching starts) containing keys that point to a mid page, which contains keys that point to a leaf page. The top page is known as the root, as if the tree is upside down with the leaves at the bottom and the root at the top.

row group: See [ page ]

ROWID: Used by: Informix, Oracle; See also [ row identifier ]

row identifier: An identifier that uniquely describes a row of a table or index to the DBMS; generally a physical address.; See also [ row locator ]

row locator: The pointer from an index key to a data row.; See also [ row identifier ]

rowset size: The number of rows of a result set that can be fetched at once.

RPC: Remote Procedure Call, a protocol that enables two computers on a network to request and send services to one another without having to understand network details.

R-R

Used by: this book only

Non-standard abbreviation for the REPEATABLE READ transaction isolation level.

R-U

Used by: this book only

Non-standard abbreviation for the READ UNCOMMITTED transaction isolation level.

rule-based optimizer: See [ RBO ]

S

S

Used by: this book only

Non-standard abbreviation for the SERIALIZABLE transaction isolation level.

SCN

Used by: Oracle

System Change Number, a number, internal to Oracle, that is incremented over time as data changes are written to the log.

search condition: A predicate, or combination of predicates, that returns true/false/unknown ; generally found in an SQL WHERE clause.

second normal form: See [ 2NF ]

selectivity (of an index)

The number of distinct values divided by the total number of values. For example, if the values are {A,A,B,B} then the number of distinct values, that is, the number that a SELECT DISTINCT . . . statement would return, is two, while the total number of values is four.

So selectivity is 2/4 in this case. Selectivity is usually expressed as a percentage ("selectivity is 50%"), but some express it as a ratio ("selectivity is 0.5") instead.

WARNING: The following definitions of selectivity from vendor manuals or other texts are imprecise or confusing: "the number of rows in the table divided by the number of distinct values" (Oracle); "the ratio of duplicate key values in an index" (Sybase).

WARNING: The phrase "high selectivity" means either "a large selectivity number" or "a small selectivity number" depending on who is saying it.

select list: Everything between the keyword SELECT and the keyword FROM in a SELECT statement.

sequencing: Forcing rows to be close together by using SERIAL data types or IDENTITY columns or auto-incrementing objects.

SERIALIZABLE: A transaction isolation level that ensures the DBMS will lock paths to objects, not just objects, during the course of a transaction. SERIALIZABLE tells the DBMS you want it to execute concurrent transactions in a manner that produces the same effect as a serial execution of those transactions.

serialized transaction: A transaction during which other users are prevented from making data changes.

set dependent: A concept used in normalization. If the value of column1 limits the possible values in column2 to a specific set, then column2 is set dependent on column1 .

set function: A function that takes a collection of values and returns a single value as a result. The SQL Standard set functions are AVG, COUNT, MAX, MIN, and SUM.

set operator: An operator that merges two or more sets. The SQL Standard set operators are EXCEPT, INTERSECT, and UNION.

shared (lock mode): A lock that may coexist with any number of other shared locks, or with one update lock, on the same object.

shift: Movement of rows caused by a change in the row size. When the length of a row is changed due to an UPDATE or DELETE operation, that row and all subsequent rows on the page may have to be moved or shifted.; See also [ migration ]

shrinking update: A data-change statement that decreases the size of a row.

skew: An observation about the distribution of values in a set. If value1 , value2 , and value3 each occur five times, there is no skew: the values are evenly distributed. On the other hand, if value1 occurs five times and value2 occurs ten times and value3 occurs 100 times, there is skew: the values are unevenly distributed.

sort key

Used by: this book only

A string with a series of one-byte numbers that represents the relative ordering of characters.

sort-merge join

A method for producing a joined table. Given two input tables Table1 and Table2 , processing is as follows:

(a) Sort Table1 rows according to join-column values.

(b) Sort Table2 rows according to join-column values.

SPL

Used by: Informix

Stored Procedure Language, Informix term for stored procedures.

splitting (an index): A process whereby the DBMS makes a newly inserted or updated key fit into the index when the key won't fit in the current page. To make the key fit, the DBMS splits the page: it takes some keys out of the current page and puts them in a new page.

statistics: Volatile data about the database, stored in the system catalog so that the optimizer has access to it.

stmt: Statement container, an ODBC resource.

strong-clustered index: Used by: this book only; See also [ clustered index ]

subquery: A SELECT within another SQL statement, usually within another SELECT.

subselect: See [ subquery ]

synchronized (method): An attribute of a Java method that provides concurrency control among multiple threads sharing an object.

syntax

Used by: this book only

A choice of words and their arrangement in an SQL statement.

T

table scan: A search of an entire table, row by row.

tablespace: A file or group of files that contain data.

third normal form: See [ 3NF ]

throughput: The number of operations the DBMS can do in a time unit.

tid: Used by: Ingres; See also [ row identifier ]

transaction: A series of SQL statements that constitute an atomic unit of work: either all are committed as a unit or they are all rolled back as a unit. A transaction begins with the first statement since the last transaction end and finishes with a transaction end (either COMMIT or ROLLBACK) statement.

transform: The process of rewriting an SQL statement to produce the same result, but with different syntax. When two SQL statements have different syntax but will predictably and regularly produce the same outputs, they are known as transforms of one another.

transitively dependent: A concept used in normalization. If column2 is dependent on column1 and column3 is dependent on column2 , then it is also true that column3 is dependent on column1 ; the Law of Transitivity applies to dependence too.

U

UDT: User-defined data type, a data type defined by a user using the SQL CREATE TYPE statement.

Unicode: A character encoding. Used in Java and sometimes used by DBMSs to support the SQL NCHAR/NCHAR VARYING data type.

unique index: An index with perfect selectivity, that is, an index with no duplicate values allowed. Standard SQL allows multiple NULLs in a unique index, since a NULL is not considered to be equal to any other value, including another NULL. Many DBMSs, however, accept only one NULL in a unique index. Some DBMSs won't allow even a single NULL.

uniquifier

Used by: Microsoft

A 4-byte value added to a clustered index key to make it unique

update (lock mode): A lock that may coexist with any number of shared locks, but not with another update lock nor with an exclusive lock on the same object.

URL: Uniform Resource Locator, the electronic address for an Internet site.

V

vector: A one-dimensional array.

versioning: A mechanism that sometimes doesn't use shared locks for rows. Versioning is also known as Multi Version Concurrency Control (MVCC).

W

weak-clustered index: Used by: this book only; See also [ clustered index ]