What is What? Part-II

WHAT IS PAGE?

‘Page’ is fundamental unit of Data in SQL. An each page has 8KB size. The arrangement of continuous eight page is called as ‘Extend’. Therefore, 64KB is called as ‘Extend’.

Pages are used for storing the Data. In other hand, I/O operations has been occurred on this page. On the Page, the page header (96B) is followed by rows of Data. And Page ends with Row offset in descending order.

WHAT IS INDEX?

Index is used for retrieving Data Fast. Index is working like our book index page it contains where the Data are stored.

Two types of indexes are used in SQL Server.

1.       Clustered Index

2.       Non - Clustered Index

Clustered Index :

                In this Index, Data are stored by order (providing in index definition) during insertion.

E.g.:  if we create an index with descending order of Name and Insert the names as following order:

Srinath,Mano,Pugal,Ranjith, then you select the table it looks like,

Employee Id	Name	Phone Number	Salary
1	Srinath	000 000 0000	200000
4	Ranjith	333 333 3333	100000
3	Pugal	222 222 2222	100000
2	Mano	111 111 1111	200000

               

 Non - Clustered Index:

                In this Index, Data are stored by Actual Insertion. But it Keeps row id and values separately in order of index definition.

Each Filtration is runs on index table then it gets the rowid and the fetch the corresponding rows original table

Rowid	Value(Name column)
000A1	Srinath
000A4	Ranjith
000A3	Pugal
000A2	Mano

  

WHAT IS CURSOR?

                Cursor is used for process a single row instead of all rows at a time. Cursor gets the result set from Select query of the Table(s). In T-SQL or Select query, all the rows are processed as a set or unit. So, Cursor is known as ‘Performance Killer’.

Before using the Cursor, we must create the memory space for the Cursor and then open it too. At last closing and decollate the memory is good one. Actually it is called as ‘Life cycle of Cursor’.

If we fetch the rows of table (given in Table example), it looks like,

Employee Id	Name	Phone Number	Salary
1	Srinath	000 000 0000	200000

After fetching a row, we can do our business logics. For example, update the Salary with 30 % of current Salary. But, using Cursor is not advisable.

Employee Id	Name	Phone Number	Salary
2	Mano	111 111 1111	200000

Employee Id	Name	Phone Number	Salary
3	Pugal	222 222 2222	100000

Employee Id	Name	Phone Number	Salary
4	Ranjith	333 333 3333	100000

WHAT IS FUNCTION?

                SQL allows to create a function, called ‘User Define Functions’ without the DDL queries. Function only accepts the Input parameters and return value. We can define the function as three types based on the how was returned.

Function would be called from anywhere of the Queries (Column, System Function, ‘Where’ clause) is based on its type.

WHAT IS STORED PROCEDURE?

                SP means, storing the set of rules (steps) which will be used for Defined process. So we can do all business logics. Means, not like Function we can do all DDL queries and I/O variable(s) are available.

                For example, if we want to SP for Hike process with,

1.       Increase the salary by 30 % of current Salary

2.       Insert the highly paid employee(s) details into another table. Then SP looks like,

Update Table set Salary += 30 % where salary is not null

Insert into Table values (Select max (Salary))

Employee Id	Name	Phone Number	Salary
1	Srinath	000 000 0000	260000
2	Mano	111 111 1111	260000
3	Pugal	222 222 2222	130000
4	Ranjith	333 333 3333	130000

Incremented Salary table of each employee. And the highly paid employee’s details are inserted into another table, which is following on:

Employee Id	Name	Phone Number	Salary
1	Srinath	000 000 0000	260000
2	Mano	111 111 1111	260000

                So, if we defined the Hike (or any other) process as a SP, then we just run that for avoiding manual errors.

DB Constraints

1. PRIMARY KEY

·         In Each Table there is only one Primary key will be Accepted.

·         If you need to use more than one column as primary key called Composite Primary key.

·         One table’s Primary key is used as another table’s Foreign Key.

·         If you give a columns as Primary key that column does not allow Null values.

·         If you give a columns as Primary key that column does not allow Duplicate values.

Syntax: 

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> <Column_name>

E.g.: 

Type 1:

            Create table Author (

                        AuthorCode varchar (20) constraint <Constraint_name> Primary key,

                        Name varchar (20)

)

Type 2:           

Create table Author (

                        AuthorCode varchar (20),

                        Name varchar (20)

Primary key (AuthorCode)

)

Type 3:

                        Alter table Author add Primary key (AuthorCode)

NOTE: The Constraint is identified by Specified name (given by you) or default name (given by DB). Names are used for manipulate the Constraints as ALTER, DELETE.

COMPOSITE PRIMARY KEY: 

Type 1:

Create table Author (

                        AuthorCode varchar (20),

                        Name varchar (20)

Primary key (AuthorCode, Name)

)          

Type 2:

                        Alter table Author add Primary key (AuthorCode, Name)

2. FOREIGN KEY 

·         In Each Table there is Multiple Foreign key columns has been accepted.

·         One table’s Primary key is used as another table’s Foreign key.

·         The foreign key datatype and length should be same as primary key column value.

·         If you give a columns as Foreign key that column allow Null values.

·         If you give a columns as Foreign key that column allow Duplicate values.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> (Column_name) References <Primary key table name> (Column_name)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20),

                        AuthorCode (20) references Author (AuthorCode)

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Foreign key (AuthorCode) references Author (AuthorCode)

)

Type 3:

                        Alter table Author add Foreign key (AuthorCode) references Author (AuthorCode)

Type 4:

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20),

                        Name varchar (20)

Foreign key (AuthorCode, Name)

)          

Type 5:

Alter table Book add Foreign key (AuthorCode, Name) references Author (AuthorCode, Name) 

3. UNIQUE

·         In Each Table there is Multiple Unique columns has been accepted.

·         If you give a columns as Unique that column allow Null values only once.

·         If you give a columns as Unique that Does Not allow Duplicate values.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> (Column_name) References <Primary key table name> (Column_name)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20) Unique,

                        AuthorCode varchar (20)

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Unique (BookName)

)

Type 3:

                        Alter table Author add Unique (AuthorCode)

Type 4:

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20),

                        Name varchar (20)

Unique (AuthorCode, Name)

)          

Type 5:

Alter table Book add Unique (AuthorCode, Name)

4. CHECK 

·         In Each Table there is Multiple Check columns has been accepted.

·         If you give a columns as Check that column you can define a validation.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> constraint

(Column_name_with_condition)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

                        Subject varchar (20) Check (Subject in (‘Tamil’, ‘English’)),

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Subject varchar (20),

Check (Subject in (‘Tamil’, ’English’)),

)

Type 3:

Alter table Author add Check (Subject in (‘Tamil’, ‘English’)) 

5. NOT NULL

·         In Each Table there is Multiple Not Null columns has been accepted.

·         If you give a columns as Not Null that column does not allow Null values.

·         If you give a columns as Not Null that column allow Duplicate values. 

Syntax:

Alter table <table_name> Alter Column <Column_name> <Datatype> Not Null.

E.g.:

Type 1:

            Create table Book (

                        BookName varchar (20) Not Null,

                        AuthorCode (20) references Author (AuthorCode)

)

Type 2:

                        Alter table Author alter column AuthorCode varchar (30) Not Null

Thanks and Regards, 

Pugal.