2. What are the normal forms and what are the different normal forms of Normalization?
- First Normal Form (INF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce- Codd Normal Form
- Forth Normal Form (4NF)
- Fifth Normal Form(5NF)
3. What is the Denormalization?
Primary Key can be defined while creating a table with Create Table command or it can be added with the Alter table command.
(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.
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.
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 constraints
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?