Interview Questions on sql server -Part-1

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. Read more about Normalization 

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

 

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)

Read more about Normal forms

 

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.

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

Read more about 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?

 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 a Unique key is created, a non-clustered index is created by default.
Read more about difference between Primary key and Unique key 

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.

Read more about Check constraints9. 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 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.

Read more about difference between Delete and Truncate Command

10. What is the Identity property?

Read about the Identity Property

Related articles

Interview questions on Sql server – Part 2 

Interview questions on Sql server – Part 3