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?
- 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.
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.
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.
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?
24 thoughts on “Interview Questions on sql server -Part-1”
gr8 work Johari. Really helpful to prepare for interviews.
My name is vishvadeepak tripathi, really very helpful but i want to contact you personally if u don't mind.
my email is email@example.com
You can contact me at firstname.lastname@example.org
This type of question is very helpful for the knowledge
This is the great helpful for interview this type of question gr8 work.
gr8 work sir but there is a mistake pls refer this..QUESTION NUM 5) 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. QUESTION NUM6) 2)Primary key do not allow NULL values but a Unique key allow ONE NULL VALUE.