Sql Server Interview Question: Miscellaneous

What is entity?
In English Query, an entity is a real-world object, referred to by a noun (person, place, thing, or idea), such as people, products, shipments, cities, and so on. Entities are semantic objects.


Define entity integrity?
A state in which all the rows in a database have a not-null primary key value, all tables have primary keys, and no table has any duplicate primary key values. This ensures that there are no duplicate entries for anything represented in the database.


What is domain integrity?
An integrity mechanism that enforces the validity of entries for a given column. The mechanism, such as the CHECK constraint, can restrict the possible data values by data type, format, or range of values allowed.

Define data integrity?
A state in which all the data values stored in the database are correct. If incorrect data values have been stored in a database, the database is said to have lost data integrity.

Define primary key (PK)?
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.


What is Candidate key?

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;

What is Alternate keys?
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.


What is referenced key?
A primary key or unique key referenced by a foreign key.


Define referential integrity (RI)?
A state in which all foreign key values in a database are valid. For a foreign key to be valid, it must contain either the value NULL, or an existing key value from the primary or unique key columns referenced by the foreign key.

What is foreign key (FK)?
The column or combination of columns whose values match the primary key (PK) or unique key in the same or another table. Also called the referencing key.


Define foreign table?
A table that contains a foreign key.



UNIQUE constraints?
Constraints that enforce entity integrity on a nonprimary 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.

uniqueidentifier data type?
A data type containing a unique identification number stored as a 16-byte binary string used for storing a globally unique identifier (GUID).

What is candidate key?

A column or set of columns that have a unique value for each row in a table. Each candidate key value uniquely identifies a single row in the table. Tables can have multiple candidate keys. One candidate key in a table is specified by the database designer to be the primary key for the table, and any other candidate key is called an alternate key.


What is CHECK constraints?
Defines which data 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.

Define composite key?
A key composed of two or more columns.

Define table-level constraint?
Constraints that allow various forms of data integrity to be defined on one column (column-level constraint) or several columns (table-level constraints) when the table is defined or altered. Constraints support domain integrity, entity integrity, and referential integrity, as well as user-defined integrity.

What is declarative referential integrity (DRI)?
FOREIGN KEY constraints defined as part of a table definition that enforce proper relationships between tables. The constraints ensure that proper actions are taken when DELETE, INSERT, and UPDATE statements remove, add, or modify primary or foreign key values. The DRI actions enforced by FOREIGN KEY constraints can be supplemented with additional referential integrity logic defined in triggers on a table.

What is default?
A data value, option setting, collation, or name assigned automatically by the system if a user does not specify the value, setting, collation, or name. An action taken automatically at certain events if a user has not specified the action to take.

What is DEFAULT constraint?
A property defined for a table column that specifies a constant to be used as the default value for the column. If any subsequent INSERT or UPDATE statement specifies a value of NULL for the column, or does not specify a value for the column, the constant value defined in the DEFAULT constraint is placed in the column.

Define 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.


Define column-level constraint?
A constraint definition that is specified within a column definition when a table is created or altered. The constraint applies only to the associated column.]


integrity constraint?
A property defined on a table that prevents data modifications that would create invalid data.

What is key column?
A column referenced by a primary, foreign, or index key.


What is denormalize?
To introduce redundancy into a table in order to incorporate data from a related table. The related table can then be eliminated. Denormalization can improve efficiency and performance by reducing complexity in a data warehouse schema.

Define checkpoint?
An event in which the database engine writes dirty buffer pages to disk. Dirty pages are pages that have been modified, but the modifications have not yet been written to disk. Each checkpoint writes to disk all pages that were dirty at the last checkpoint and still have not been written to disk. Checkpoints occur periodically based on the number of log records generated by data modifications, or when requested by a user or a system shutdown.


Define Concurrency?
A process that allows multiple users to access and change shared data at the same time. SQL Server uses locking to allow multiple users to access and change shared data at the same time without conflicting with each other.


What is cascading delete?
An operation that deletes a row containing a primary key value that is referenced by foreign key columns in existing rows in other tables. On a cascade delete, all of the rows whose foreign key values reference the deleted primary key value are also deleted.

What is cascading update?
An operation that updates a primary key value that is referenced by foreign key columns in existing rows in other tables. On a cascade update, all of the foreign key values are updated to match the new primary key value.

Define clustered index?
An index in which the logical order of the key values determines the physical order of the corresponding rows in a table.

Define composite index?
An index that uses more than one column in a table to index data.

What is leaf level?
The bottom level of a clustered or nonclustered index. In a clustered 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.

What is key range lock?
A lock used to lock ranges between records in a table to prevent phantom insertions or deletions into a set of records. Ensures serializable transactions.

Define keyset-driven cursor?
A cursor that shows the effects of updates made to its member rows by other users while the cursor is open, but does not show the effects of inserts or deletes.

Define static cursor?
A cursor that shows the result set exactly as it was at the time the cursor was opened. Static cursors do not reflect updates, deletes, or inserts made to underlying data while the cursor is open. They are sometimes called snapshot cursors.

Define dynamic cursor?
A cursor that can reflect data modifications made to the underlying data while the cursor is open. Updates, deletes, and inserts made by users are reflected in the dynamic cursor.

What is forward-only cursor?
A cursor that cannot be scrolled; rows can be read only in sequence from the first row to the last row.

What is fragmentation?
Occurs when data modifications are made. You can reduce fragmentation and improve read-ahead performance by dropping and re-creating a clustered index.

What is full-text catalog?
Stores all of the full-text indexes for tables within a database.

What is full-text enabling?
The process of allowing full-text querying to occur on the current database.

What is full-text index?
The portion of a full-text catalog that stores all of the full-text words and their locations for a given table.

What is full-text query?
As a SELECT statement, a query that searches for words, phrases, or multiple forms of a word or phrase in the character-based columns (of char , varchar , text , ntext , nchar , or nvarchar data types). The SELECT statement returns those rows meeting the search criteria.

What is full-text service?
The SQL Server component that performs the full-text querying.

Define function?
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.

Define Transaction?
A group of database operations combined into a logical unit of work that is either wholly committed or rolled back. A transaction is atomic, consistent, isolated, and durable.

transaction log
A database file in which all changes to the database are recorded. It is used by SQL Server during automatic recovery.

transaction rollback?
Rollback of a user-specified transaction to the last savepoint inside a transaction or to the beginning of a transaction.

write-ahead log?
A transaction logging method in which the log is always written prior to the data.

Define concurrency
A process that allows multiple users to access and change shared data at the same time. SQL Server uses locking to allow multiple users to access and change shared data at the same time without conflicting with each other.

What is dirty pages?
Buffer pages that contain modifications that have not been written to disk.

Define dirty read?
Reads that contain uncommitted data. For example, transaction1 changes a row. Transaction2 reads the changed row before transaction1 commits the change. If transaction1 rolls back the change, transaction2 has read a row that never logically existed.

isolation level?
The property of a transaction that controls the degree to which data is isolated for use by one process and guarded against interference from other processes. Setting the isolation level defines the default locking behavior for all SELECT statements in your SQL Server session.

Savepoint?
A marker that allows an application to roll back part of a transaction if a minor error is encountered. The application must still commit or roll back the full transaction when it is complete.

shared lock?
A lock created by nonupdate (read) operations. Other users can read the data concurrently, but no transaction can acquire an exclusive lock on the data until all the shared locks have been released.

What is exclusive lock?
A lock that prevents any other transaction from acquiring a lock on a resource until the original lock on the resource is released at the end of the transaction. An exclusive lock is always applied during an update operation (INSERT, UPDATE, or DELETE).

Define explicit transaction?
A group of SQL statements enclosed within transaction delimiters. The first delimiter must be either BEGIN TRANSACTION or BEGIN DISTRIBUTED TRANSACTION, and the end delimiter must be one of the following:
COMMIT TRANSACTION
COMMIT WORK
ROLLBACK TRANSACTION
ROLLBACK WORK
SAVE TRANSACTION.

implicit transaction?
A connection option in which each SQL statement executed by the connection is considered a separate transaction.

incremental update?
The set of operations that either adds new members to an existing cube or dimension, or adds new data to a partition. One of three processing options for a cube or partition. One of two processing options for a dimension.


Define deadlock?
A situation when two users, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each user would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

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

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.

index page?
A database page containing index rows.

clustered index?
An index in which the logical order of the key values determines the physical order of the corresponding rows in a table

nonrepeatable read?
When a transaction reads the same row more than one time, and between the two (or more) reads, a separate transaction modifies that row. Because the row was modified between reads within the same transaction, each read produces different values, which introduces inconsistency.

normalization rules?
A set of database design rules that minimizes data redundancy and results in a database in which the database engine and application software can easily enforce integrity.

What is NULL?
An entry that has no explicitly assigned value. NULL is not equivalent to zero or blank. A value of NULL is not considered to be greater than, less than, or equivalent to any other value, including another value of NULL.


What is equijoin?
A join in which the values in the columns being joined are compared for equality, and all columns are included in the results.

Define inner join?
An operation that retrieves rows from multiple source tables by comparing the values from columns shared between the source tables. An inner join excludes rows from a source table that have no matching rows in the other source tables.

left outer join?
A type of outer join in which all rows from the left-most table in the JOIN clause are included. When rows in the left table are not matched by rows in the right table, all result set columns that come from the right table are assigned a value of NULL.

right outer join?
A type of outer join in which all rows in the right-most table in the JOIN clause are included. When rows in the right table are not matched in the left table, all result set columns that come from the left table are assigned a value of NULL.

What is full outer join?
A type of outer join in which all rows in all joined tables are included, whether they are matched or not.

join condition?
A comparison clause that specifies how tables are related by their join columns.

full-text index?
The portion of a full-text catalog that stores all of the full-text words and their locations for a given table.

heterogeneous data?
Data stored in multiple formats. For example, data stored in a SQL Server database, a text file, and an Excel spreadsheet.

What is error log?
A text file that records system information from SQL Server.

Define Data Control Language (DCL)?
The subset of SQL statements used to control permissions on database objects. Permissions are controlled using the GRANT and REVOKE statements.

What is data definition language (DDL)?
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.
Miscellaneous
A set of system tables, stored in a catalog, that includes definitions of database structures and related information, such as permissions.
What is data dictionary view?
A system table.

What is data explosion?
The exponential growth in size of a multidimensional structure, such as a cube, due to the storage of aggregated data.

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.

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

system databases?
A set of four databases present in all instances of SQL Server that are used to store system information:
The master database stores all instance-level meta data, and records the location of all other databases.
The tempdb database stores transient objects that only exist for the length of a single statement or connection, such as worktables and temporary tables or stored procedures.
The model database is used as a template for creating all user databases.
The msdb database is used by the SQL Server Agent to record information on jobs, alerts, and backup histories.

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

table scan?
A data retrieval operation where the database engine must read all the pages in a table to find the rows that qualify for a query.

many-to-many relationship?
A relationship between two tables in which rows in each table have multiple matching rows in the related table. Many-to-many relationships are maintained by using a third table called a junction table and adding the primary key columns from each of the other two tables to this table.

many-to-one relationship?
A relationship between two tables in which one row in one table can relate to many rows in another table.
one-to-many relationship?
In relational databases, a relationship between two tables in which a single row in the first table can be related to one or more rows in the second table, but a row in the second table can be related only to one row in the first table.

one-to-one relationship?
In relational database, a relationship between two tables in which a single row in the first table can be related only to one row in the second table, and a row in the second table can be related only to one row in the first table.

ODBC data source?
The location of a set of data that can be accessed using an ODBC driver. Also, a stored definition that contains all of the connection information an ODBC application requires to connect to the data source.

ODBC driver?
A dynamic-link library (DLL) that an ODBC-enabled application, such as Excel, can use to access an ODBC data source. Each ODBC driver is specific to a database management system (DBMS), such as SQL Server, Access, and so on.

online analytical processing (OLAP)?
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.


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

Open Database Connectivity (ODBC)?
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 Organization for Standardization (ISO) standards for a database Call Level Interface (CLI).

SQL collation?
A set of SQL Server 2000 collations whose characteristics match those of commonly-used code page and sort order combinations from earlier versions of SQL Server. SQL collations are compatibility features that let sites choose collations that match the behavior of their earlier systems.

Subquery?
A SELECT statement nested inside another SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

Correlated subqueries?
the subquery depends on the outer query for its values
In the subqueries previously discussed, SQL evaluates the subquery once, substitutes the result of the subquery in the search condition, and evaluates the outer-level SELECT based on the value of the search condition. You can also write a subquery that SQL may need to re-evaluate as it examines each new row (WHERE clause) or group of rows (HAVING clause) in the outer-level SELECT. This is called a correlated subquery.

Correlated names and references
Example: Correlated subquery in a WHERE Clause
Example: Correlated subquery in a HAVING Clause
Example: Correlated subquery in select-list
Example: Correlated subqueries in an UPDATE statement
Example: Correlated subqueries in a DELETE statement
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafycorrs.htm