Both primary and unique keys enforce uniqueness of a column on which they are defined. but by default primary key creates a clustered index on the column, whiles unique creates a nonclustered index by default. another major difference is that, primary key doesn't allow NULLS, but unique key allows one NULL only
Q. What is the difference between UNION and JOINS
A join selects columns from 2 or more tables. a union selects rows
Q. What is a join and list different types of joins
Joins are used to retrieve data from two or more tables based on logical
relationships between the tables.
Types of joins: INNER joins, OUTTER joins, CROSS joins. outter joins are further classified as LEFT OUTTER JOINS, RIGHT OUTTER JOINS and FULL OUTTER JOINS.
Q. What is a referencial integrity
It refers to the consistency that must be maintained b/n primary and foreign keys, ie. every foreign key must have a corresponding primary key value
Q. How to determine the service pack currently installed on SQL Server
@@Version
Q. What is the use of SCOPE_IDENTITY () function
Returns the most recently created identity values for the table in the current execution scope
Q. What is the difference b/n DELETE TABLE and TRUNCATE TABLE commands
DELETE TABLE cmd removes the rows from a table based on the condition that we provide with a WHERE clause. TRUNCATE will actually remove all the rows from a table and there will be no data in the table after we run the truncate cmd. DELETE TABLE can be rolled back
Q. What are the constraints
It defines rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. 5 types of constraints are NOT NULL, CHECK, UNIQUE, PRIMARY KEY AND FOREIGN KEY
Q. What is a foreign key and a primary key
PRIMARY KEY constraints identify the column or set of columns whose
values uniquely identify a row in a table and FOREIGN KEY constraints identify the
relationships between tables. . A FOREIGN KEY is a column or a combination of columns used to establish and enforce a link between the data in two tables and are used in conjunction with the primary keys on a referenced table.
Q. What is a user defined function
A user-defined function is a subroutine that is made up of one or more
Transact-SQL statements and can be used to encapsulate code for reuse. It can be used to run aggregate calculation
Q. What is a self join? Explain it with an example
Self join is just like any other join, except that two instances of the same table will be joined in a query. Eg employee table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees you need a self join
Q. What is a stored procedure
A stored procedure is a set of Transact-SQL statements compiled into a single
execution plan and can return data as output parameters, which can return either data or a
cursor variable;
Q. What is a cascading referential integrity
A cascading referential integrity constraints allows for the automatic
updating of primary key tools by defining the actions SQL Server 2000 takes when a
user deletes or updates a key to which existing foreign keys point.
Q. What is denormalization and why would you use it
Controlled introduction of redundancy of data in the design. It would help improve query performance as the number of joins could be reduced.
Q. What is an index:
In databases, indexes improve query response time by allowing for quicker
retrieval of data in a table by not having to scanning the entire table. An index in a
database is a list of values in a table with the storage locations of rows in the table that
contain each value.
A clustered index is a special type of index that reorders the way records in the table are physically stored. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
Q. What type of index is created by default on a primary key
Clustered
Q. Can you create a nonclustered index on a primary key
Yes
Q. Would you always create a clustered index on a primary key
No
Q. Define a candidate key, alternate key and composite key
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key
Q. ISOLATION in SQL
A. The Read Committed Isolation Model is SQL Server’s default behavior. In this model, the database does not allow transactions to read data written to a table by an uncommitted transaction. This model protects against dirty reads, but provides no protection against phantom reads or non-repeatable reads.
The Read Uncommitted Isolation Model offers essentially no isolation between transactions. Any transaction can read data written by an uncommitted transaction. This leaves the transactions vulnerable to dirty reads, phantom reads and non-repeatable reads.
The Repeatable Read Isolation Model goes a step further than the Read Committed model by preventing transactions from writing data that was read by another transaction until the reading transaction completes. This isolation model protect against both dirty reads and non-repeatable reads.
The Serializable Isolation Model uses range locks to prevent transactions from inserting or deleting rows in a range being read by another transaction. The Serializable model protects against all three concurrency problems.
The Snapshot Isolation Model also protects against all three concurrency problems, but does so in a different manner. It provides each transaction with a "snapshot" of the data it requests. The transaction may then access that snapshot for all future references, eliminating the need to return to the source table for potentially dirty data.
If you need to change the isolation model in use by SQL Server, simply issue the command:
SET TRANSACTION ISOLATION LEVEL
where is replaced with any of the following keywords:
READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT
Q. Describe Normalization.
Database Normalization : Basically, it's the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
Benefits of normalizing your database will include:
- Avoiding repetitive entries.
- Reducing required storage space.
- Preventing the need to restructure existing tables to accommodate new data.
- Increased speed and flexibility of queries, sorts, and summaries.
Following are the three normal forms :-
First normal form (1NF) lays the groundwork for an organised database design:
Ensure that each table has a primary key: minimal set of attributes which can uniquely identify a record.
Eliminate repeating groups (categories of data which would seem to be required a different number of times on different records) by defining keyed and non-keyed attributes appropriately.
Atomicity: Each attribute must contain a single value, not a set of values.
'First normal form' depends on functional dependency formula f(x)=y. For every value of x there is value for y.
Second normal form (2NF) If a table has a composite key, all attributes must be related to the whole key:
The database must meet all the requirements of the first normal form.
The relational schema should not have any partial functional dependency i.e. No proper subset of the primary key should derive a functional dependency belonging to the same schema. For example, consider functional dependencies FD:{AB->C, A->D, C->D} here AB is the primary key, as A->D this relational schema is not in 2NF.
Third normal form (3NF) requires that data stored in a table be dependent only on the primary key, and not on any other field in the table.
The database must meet all the requirements of the first and second normal form.
All fields must be directly dependent on the primary key field. Any field which is dependent on a non-key field which is in turn dependent on the Primary Key (ie a transitive dependency) is moved out to a separate database table.
Boyce-Codd normal form (or BCNF) requires that there be no non-trivial functional dependencies of attributes on something other than a superset of a candidate key (called a superkey).
