Basic database terms

Primary Key
A column or set of columns that uniquely identify 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 of 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 of 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.

Triggers
Triggers A stored procedure that executes when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.

Index
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 of a table is automatically indexed. In full-text search, a full-text index stores information about significant words and their location within a given column. Clustered index An index in which the logical order of the key values determines the physical order of the corresponding rows in a table. Nonclustered index An index in which the logical order of the index is different than the physical, stored order of the rows on disk.

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.

Stored Procedures
A precompiled collection of Transact-SQL statements stored under a name and processed as a unit. SQL Server supplies stored procedures for managing SQL Server and displaying information about databases and users. SQL Server-supplied stored procedures are called system-stored procedures.

Functions
A piece of code that operates as a single logical unit. A function is called by name, accepts optional input parameters, and returns a status and optional output parameters. Many programming languages support functions, including C, Visual Basic, and Transact-SQL. Transact-SQL supplies built-in functions, which cannot be modified, and supports user-defined functions, which can be created and modified by users.

View
A database object that can be referenced the same way as a table in SQL statements. Views are defined using a SELECT statement and are analogous to an object that contains the result set of this statement.