Normalization and DeNormalization,fulltext definition functions,find index, non index tables in sql server

Normalization

In Relational database design,the process of organizing data to minimize redundancy is called Normalization.

DeNormalization

It is the process of optimize the performance of a database by adding redundant data.

Different Normalization Forms

1 NF

Eliminate Repeating groups

2NF

Eliminate Redundant Data

3NF

Eliminate Columns which are not dependent on key column

BCNF

Eliminate non-trivial dependencies in candidate key attribute then separate it

4NF

Isolate independent multiple relationship

5NF

Isolate semantically related multiple relationship(Separate Many-Many Relationship)

ONF

A Model is limited as express object role model

DKNF

A model free from all modification anomalies


Trick to find all index tables in a database

SELECT NAME FROM SYS.TABLES WHERE OBJECTPROPERTY(OBJECT_ID,'ISINDEXED')=1


Trick to find all non-index tables in a database


SELECT NAME FROM SYS.TABLES WHERE OBJECTPROPERTY(OBJECT_ID,'ISINDEXED')=0


Fulltext definition functions with example


SELECT EMP_NAME FROM EMPLOYEE , CONTAINSTABLE(EMPLOYEE,EMP_NAME,'CHANDR') AS TabName


SELECT EMP_NAME FROM EMPLOYEE WHERE FREETEXT(EMP_NAME,'CHAN')

SELECT EMP_NAME FROM EMPLOYEE WHERE CONTAINS(EMP_NAME,'CHAN')

SELECT RANK()OVER(ORDER BY Description),Description FROM EMPLOYEE

How to find current version of the sql server

SELECT @@VERSION