What is an Identity?

•         Identity (or AutoNumber) is a column that automatically generates numeric values.
•          A start and increment value can be set, but most DBA leave these at 1.
•          A GUID column also generates numbers; the value of this cannot be controlled.
•          Identity/GUID columns do not need to be indexed.
•          SELECT @@IDENTITY - returns the last IDENTITY value produced on a connection
•          SELECT IDENT_CURRENT ('tablename') - returns the last IDENTITY value produced in a table
•          SELECT SCOPE_IDENTITY() - returns the last IDENTITY value produced on a connection.

What is Normalization in SQL Server?

In relational database design, the process of organizing data to minimize redundancy is called normalization. It usually involves dividing a database into 2 or more tables and defining relationships between tables. Objective is to isolate data so that additions, deletions, and modifications can be made in just one table. 

Benefits:-

•          Eliminate data redundancy
•          Improve performance
•          Query optimization
•          Faster update due to less number of columns in one table
•          Index improvement

There are multiple forms of Normalizations in a database.

•          First Normal Form (1NF)
•          Second Normal Form (2NF)
•          Third Normal Form (3NF)
•          Fourth Normal Form (4NF -BCNF NF)

First normal form (1NF)

Eliminate duplicative columns from the same table.

·         Create separate tables for each group of related data and identify each row with a unique column or set of columns.

·         Remove repetitive groups

·         Create Primary Key

Name	State	Country	Phone1	Phone2	Phone3
John	101	1	488-511-3258	781-896-9897	425-983-9812
Bob	102	1	861-856-6987
Rob	201	2	587-963-8425	425-698-9684
PK			[ Phone Nos ]
?				?
ID	Name	State	Country	Phone
1	John	101	1	488-511-3258
2	John	101	1	781-896-9897
3	John	101	1	425-983-9812
4	Bob	102	1	861-856-6987
5	Rob	201	2	587-963-8425
6	Rob	201	2	425-698-9684

 Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

• ·         Meet all the requirements of the first normal form.
• ·         Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• ·         Create relationships between these new tables and their predecessors through the use of foreign keys.   
• Remove columns which create duplicate data in a table and related a new table with Primary Key – Foreign Key relationship.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

• ·         Meet all the requirements of the second normal form.
• ·         Remove columns that are not dependent upon the primary key.

  Country can be derived from State also… so removing country

ID	Name	State	Country
1	John	101	1
2	Bob	102	1
3	Rob	201	2

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

•  Meet all the requirements of the third normal form.
• A relation is in 4NF if it has no multi-valued dependencies.

If PK is composed of multiple columns then all non-key attributes should be derived from FULL PK only. If some non-key attribute can be derived from partial PK then remove it

 The 4NF also known as BCNF NF

TeacherID	StudentID	SubjectID	StudentName
101	1001	1	John
101	1002	2	Rob
201	1002	3	Bob
201	1001	2	Rob
TeacherID	StudentID	SubjectID	StudentName
101	1001	1	X
101	1002	2	X
201	1001	3	X
201	1002	2	X