SQL Interview Question part 3

What is the difference between primary key and unique key?
1) By default Primary Key will generate Clustured Index whereas Unique Key will generate Non-Clustured Index.

2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t have duplicate values or any Null whereas SQL Server can have only one NULL.

3) A table can have only one PK but it can have any number of UNIQUE Keys.

4) Primary Key does not allow null values whereas unique constraint allow ‘single’ null value.

What is the difference between truncate table and delete table?
DELETE:  The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE:  TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

What is the temp table and table variable?
In SQL Server we know 3 kinds of temporary tables: Local Temp Tables, Global Temp Tables and Table Variables.

Local Temp Tables

The local temp table is the most commonly used temp table.

CREATE TABLE #TempTable

(ID INT IDENTITY(1,1) NOT NULL,

Description VARCHAR(10) NULL)

Global Temp Tables

Global temporary tables work just like local temporary tables (stored in TempDB, less locking necessary).

CREATE TABLE ##TempTable

(ID INT IDENTITY(1,1) NOT NULL,

Description VARCHAR(10) NULL)

Table Variable

Table variables are cleared automatically when the procedure, function or query goes out of scope.

What is Cursor?
A cursor is a set of rows together with a pointer that identifies a current row. Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis. 
Cursors extend result processing by:

·         Allowing positioning at specific rows of the result set.

·         Retrieving one row or block of rows from the current position in the result set.

·         Supporting data modifications to the rows at the current position in the result set.

·         Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.

·         Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.

Type of Cursors

Forward-only

·         A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor.

Static

·         The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. Static cursors detect few or no changes, but consume relatively few resources while scrolling.

·         The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set. A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement

Keyset

·         The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.

Dynamic

·         Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor.