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

Remove duplicate records from table in sql server

Query shows how to select duplicate records with SL No.


SELECT ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME) SLNO,EMP_NAME FROM EMPLOYEE



Query for remove duplicate records from table


WITH REMDUPREC
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_NAME) SLNO,EMP_NAME FROM EMPLOYEE
)

DELETE FROM REMDUPREC WHERE SLNO>1

delete from MyTable
where uniqueField not in
(select min(uniqueField) from MyTable T2
where T2.dupField=MyTable.dupField)

OPENQUERY AND OPENROWSET IN SQL SERVER

OPENQUERY: Used to execute the query passed on the linked server.

Syntax: OPENQUERY (Linked_server_identifier, query).

It can also be refernced from a FROM clause of selecte query.

e.g. Select * from OPENQUERY (Oracleserver, ‘select fname, FROM Employee);

OPENROWSET: Used to access tables in a linked server. It can be used one time for accessing remote data by using OLE DB. It includes all the information required to access the remote data.

Syntax:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

Advantages and disadvantages of views in sql server

Advantages of view

1. Data Security

2. Simplicity

3. No Space

4. Hide data and columns from base tables

5. You can easily use a view to create new information out of existing data, without changing the (legacy) database structure

6. Complex queries can be stored in the from of view

Disadvantages of view

1. Querying from view takes more time than directly querying from the table

2. It will have to do the query a new, every time it is opened

3. when table is not there view will not work or view become inactive

4. View can be create only if there is SELECT Permission on its base table

5. views can not derived the data from temp tables

6.Query defining view can not include ORDER BY,COMPUTE,INTO Keyword

7.You can not define full text index definitions on views