## Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

### Interview Questions on sql server -Part-1

1. What is the Normalization?
Normalization:-Normalization can be defined as the process of organization the data to reduce the redundant table data to the minimum. This process is carried out by dividing the database into two or more than two tables and defining relationship between them so that deletion, updation and insertion can be made to just one table and it can be propagated to other tables through defined relationships.

2. What are the normal forms and what are the different normal forms of Normalization?

Normal Forms: - The normal form can be refers to the highest normal form condition that it meets and hence indicates the degree to which it has been modified. The normal forms are:-

1. First Normal Form (INF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce- Codd Normal Form
5. Forth Normal Form (4NF)
6. Fifth Normal Form(5NF)

3. What is the Denormalization?
Denormalization:- Denormalization can be defined as the process of moving from higher normal form to a lower normal forms in order to speed up the database access.

4. What is the Primary key?
Primary Key: - Primary key is used to uniquely identify a row in a table. A table can have only one primary key. Primary keys don’t allow null values. The data help across the primary key column must be unique. It can be defined at the column level or at the table level.
Primary Key can be defined while creating a table with Create Table command or it can be added with the Alter table command.
Syntax for Primary Key

(ColumnName) (DataType) ((Size)) Primary Key

5. What is the Unique key?
Unique key:-Unique key constraint enforces that the column can contains only unique values on which unique constraints is defined. This column can contain multiple null values but all not null values must be unique. Unique Constraint can be defined either at the column level or at the table level.

6. What is the difference between Primary key and Unique Key?

1)There can be only one Primary key possible in a table but there can be many unique keys possible in a table.
2)Primary key do not allow NULL values but a Unique key allow one NULL value.If we try to insert NULL value into the primary key, we get an error message.
3)When a Primary key is created, a clustered index is made by default but if an Unique key is created, a non-clustered index is created by default.

7. What is the Foreign Key?
Foreign Key:- Foreign key is used to prevent destroying the link between two tables. In foreign key, the table (Child table) in which the foreign key is defined points to the primary column of another table (Master table). A foreign key can points to the primary column of the same table. In this Foreign key relationship is said to be defined within the same table. Due to foreign key relationship, a value from the primary column of the master table can not be deleted until its all references from the child tables are deleted. Also a new value in the column in which primary key is defined can not be inserted until the value is already existed in the primary column of the master table.

8. What is the check constraints?
Check constraints:- Check constraints are the user defined business rules which can be applied to the database table column. For example a check constraint on the column “Salary” of the table Employee salary can be defined which state that no employee can have salary less than 5000.

9. What is the difference between Delete command and Truncate command?
1) Delete command maintained the logs files of each deleted row but Truncate command do not maintain the logs files for each deleted row but maintains the record for deallocation of the datapages in the log files.The deallocation of the datafiles means that the data rows still exists in the data pages but the extends have marked as empty for reuse.

2) Truncate command is much faster than delete command.

3) You can use Where clause in case of Delete command to delete a particular row but in case of Truncate command you have to delete the data from all the row since Where clause is not work with Truncate command.

4) Triggers is fired in case of Delete command only and they are not fired when Truncate command is used.

5) Truncate command resets the Identity property to its initial value whereas Delete command do not resets the Identity property of the column.

6) Delete is a DML command and Truncate is a DDL command.

10. What is the Identity property?

Related articles

Interview questions on Sql server - Part 2