The Knowledge Foundation

Tuesday, 24 July 2018

SQL Server CURSOR

Cursor is a database object which fetch data row by row basis. Cursor is use for data manipulation and data processing purpose by fetching data by performing loop. Cursor copy data to a temporary table for looping purpose, the temporary table is automatically created on temdb database.Cursor is a part of transaction SQL(T- SQL), here is the step to create a cursor

1)Declare cursor
2)Open cursor
3)Fetch record from cursor
4)Process your statement with the record
5)Fetch next record from the cursor
6)Close cursor
7)Deallocate cursor

Here is the syntax of a cursor

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]   
     [ FORWARD_ONLY | SCROLL ]   
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]   
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]   
     [ TYPE_WARNING ]   
     FOR select_statement   
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

Declare cursor declared the cursor variable.

Select statement initialize the variable.Open cursor is opened for storing data retrieved from the result set.

Fetch statement fetch row from the cursor one by one.

Close release data and locking.

Deallocate destroy the cursor variable and release all the memory.

We have created a table and corresponding data to do some sample example.

CREATE TABLE student

(

id INT NOT NULL,

name VARCHAR (500) NOT NULL,

age INT NOT NULL,

adress VARCHAR (500) NOT NULL,

date_of_birth DATETIME NOT NULL,

height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',12,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',13,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',12,'34,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',13,'35,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',15,'36,student street','07/08/2018',14

UNION

SELECT 1,'John8',14,'34,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',12,'35,student street','09/08/2018',15

UNION

SELECT 1,'John10',15,'36,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',16,'34,student street','11/08/2018',16

Here is example of a cursor

DECLARE db_cursor

CURSOR FOR SELECT name, age, date_of_birth FROM student

DECLARE @Name VARCHAR(256);

DECLARE @Age INT;

DECLARE @dateOfBirth DATE;

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @Name, @Age, @dateOfBirth;

WHILE @@FETCH_STATUS = 0

BEGIN

--your own statment

print CONVERT(VARCHAR,@Name)+' '+CONVERT(VARCHAR,@Age)+CONVERT(VARCHAR,@dateOfBirth)

FETCH NEXT FROM db_cursor INTO @Name, @Age, @dateOfBirth;

END;

CLOSE db_cursor;

DEALLOCATE db_cursor;

OUTPUT
John1 122018-01-08
John10 152018-10-08
John11 162018-11-08
John2 132018-02-08
John3 122018-03-08
John4 132018-04-08
John5 122018-05-08
John6 132018-06-08
John7 152018-07-08
John8 142018-08-08
John9 122018-09-08

cursor can be nested, cursor can be declared under another cursor. Below is the example of nested cursor.

DECLARE db_cursor

CURSOR FOR SELECT name, age, date_of_birth FROM student

DECLARE @Name VARCHAR(256);

DECLARE @Age INT;

DECLARE @dateOfBirth DATE;

DECLARE @l_Name VARCHAR(256);

DECLARE @l_Age INT;

DECLARE @l_dateOfBirth DATE;

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @Name, @Age, @dateOfBirth;

WHILE @@FETCH_STATUS = 0

BEGIN

Print 'Age:'+CONVERT(VARCHAR,@Age)

------------------Nested Cursor---------------------------------

DECLARE db_nested

CURSOR FOR SELECT name, age, date_of_birth

FROM student2 where age=@Age

OPEN db_nested;

FETCH NEXT FROM db_nested INTO @l_Name, @l_Age, @l_dateOfBirth;

WHILE @@FETCH_STATUS = 0

BEGIN

Print CONVERT(VARCHAR,@l_Name)+' '+CONVERT(VARCHAR,@l_Age)+' '+CONVERT(VARCHAR,@l_dateOfBirth)

FETCH NEXT FROM db_nested INTO @l_Name, @l_Age, @l_Age;

END;

CLOSE db_nested;

DEALLOCATE db_nested;

--------------------------------------------------------

FETCH NEXT FROM db_cursor INTO @Name, @Age, @dateOfBirth;

END;

CLOSE db_cursor;

DEALLOCATE db_cursor;

Output:

Age:12

Age:15

John4 15 2018-04-08

Age:16

John5 16 2018-05-08

Age:13

John2 13 2018-02-08

Age:12

Age:13

John2 13 2018-02-08

Age:12

Age:13

John2 13 2018-02-08

Age:15

John4 15 2018-04-08

Age:14

John3 14 2018-03-08

Age:12

You can specify some additional parameter during cursor declaration.

Static Cursors :A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor.

Dynamic Cursors:A dynamic cursor allows you to see the data up dation, deletion and insertion in the data source while the cursor is open.

Forward Only Cursors:A forward only cursor is the fastest cursor among the all cursors.Forward Only Cursors doesn't support backward scrolling.

you should keep in mind that a cursor fetch data row by row basis, you can do row by row validation, computation, manipulation of data in each row. Cursor copy data to a temporary table for looping purpose, if this data is small, that is not an issue, but for the large data it is costly, put an extra burden on CPU is the cursor to manage cursor. That is why cursor is generally used for small record and forth large record, loop is used.

SQL Server Table Variable

Table variable is used to store record in particular scope. A table variable can be used in database functions , store procedure , Trigger etc.

A table variable can performed data manipulation operation like insert , update , delete. A table variable can be used for joining purpose also (INNER JOIN ,OUTER JOIN,LEFT JOIN, RIGHT JOIN).

Below is the syntax of table variable.

DECLARE @VARIABLE_NAME TABLE

(

COLUMN_NAME DATATYPE CONSTRAIN,

...............................

)

Below is the example of table variable.

DECLARE @MyTable TABLE

(

StudentID int IDENTITY,

StudenNAME VARCHAR(50),

ROLL INT

)

Below is example of table variable insert ,update and delete.

insert

DECLARE @MyTable TABLE

(

StudentID int IDENTITY,

StudenNAME VARCHAR(50),

ROLL INT

)

INSERT INTO @MyTable(StudenNAME,ROLL)

VALUES('John',32)

UPDATE

DECLARE @MyTable TABLE

(

StudentID int IDENTITY,

StudenNAME VARCHAR(50),

ROLL INT

)

INSERT INTO @MyTable(StudenNAME,ROLL)

SELECT 'John',32

UNION ALL

SELECT 'John2',33

UPDATE @MyTable

SET StudenNAME='John3'

WHERE ROLL=32

SELECT * FROM @MyTable

StudentID StudenNAME ROLL

1 John3 32
2 John2 33

Below is the example of table variable is used in a function.

CREATE FUNCTION dbo.GetStudentList()

RETURNS int

BEGIN

DECLARE @c INT

DECLARE @MyTable TABLE

(

StudentID int IDENTITY,

StudenNAME VARCHAR(50),

ROLL INT

)

INSERT INTO @MyTable(StudenNAME,ROLL)

SELECT 'John',32

UNION ALL

SELECT 'John2',33

SELECT @c=COUNT(1) FROM @MyTable

RETURN @c;

END;

SELECT dbo.GetStudentList()

Output :2

Table variable is widely used for its flexibility and performance. Table variable can be used as a input parameter of function also.

Table variable scope is limited, you cannot access table variables out of its scope. If you use table variable in a function or stored procedure, after execution of function or stored procedure , you will not get the table variable. If you use table variable in a query, you will not get table variable after the execution of query.

Table variable have wide functionality, you can use identity, check constant, default value but you will not able to create index as it is not a physical table.

Here is the example of default and identity is used in table variables.

DECLARE @MyTable TABLE

(

StudentID int IDENTITY,

StudenNAME VARCHAR(50),

ROLL INT,

School VARCHAR(50) DEFAULT 'My Favourite School'

)

Table variables is faster than temporary table as Table variable use least overhead. But you cannot perform transaction management with table variables.

Variable is generally used for small data for temporary storage purpose. SQL server temporary table has no limit of size, but for large data temp table is used.

The Knowledge Foundation

Tuesday, 24 July 2018

SQL Server CURSOR

SQL Server Table Variable

বাঙালির বেড়ানো সেরা চারটি ঠিকানা

Labels

Report Abuse