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

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)

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.
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

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?

 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 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

 

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions and tagged . Bookmark the permalink.

24 Responses to Interview Questions on sql server -Part-1

  1. Raman says:

    gr8 work Johari. Really helpful to prepare for interviews.
    keep posting

  2. Anonymous says:

    My name is vishvadeepak tripathi, really very helpful but i want to contact you personally if u don't mind.
    my email is vishvadeepak21@gmail.com

  3. Vivek Johari says:

    Thanks Vishvadeepak
    You can contact me at vivekjohari@gmail.com

  4. Vivek Johari says:

    Thanks Raman

  5. newidea says:

    This type of question is very helpful for the knowledge

  6. newidea says:

    This is the great helpful for interview this type of question gr8 work.

  7. karthik says:

    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.

  8. Pingback: drug and alcohol treatment centers in southern california

  9. Pingback: 바카라

  10. Pingback: Crystal Meth kaufen

  11. Pingback: www.balimap.co

  12. Pingback: Automotive Brake Fluid Market Trends in the Market 2017-2027

  13. Pingback: Traction Motors Market Size: A Guide to Competitive Landscape and Key Players Analysis 2015 – 2025

  14. Pingback: http://dzikimnich.pl/

  15. Pingback: Travel tips

  16. Pingback: indoqq

  17. Pingback: jaguarqq

  18. Pingback: www.2015louisvuittonoutlet.net

  19. Pingback: ウォーターサーバー

  20. Pingback: satta king

  21. Pingback: اخبار

  22. Pingback: Freelance WordPress Developer London

  23. Pingback: sahabatqq

  24. Pingback: Mila

Leave a Reply