Static and Dynamic Cursor in SQL Server – SQL Server Tutorial

Static and Dynamic Cursor in SQL Server  :-

Static Cursor in SQL Server with Example :

  • If the cursor is declared with static any changes make to the base table changes are not reflected to result-set.

Example  :-

  •                  Emp                             Resultset
  •      Name          sal                       name          sal
  •          A             5000                    A              5000
  •                              ↓                                             ↓
  •                           Update                              5000

Dynamic Cursor in SQL Server with Example   :-

  • If the cursor is declared with dynamic so any changes make to the base table the changes are automatically reflected to result set.

Example  :-       

                   emp                                   resultset

            Name      sal                          name     sal

              A          5000                         A        5000

                            ↓ update                             ↓

                           6000                            6000

Example : program  :-

                   Declare c1 cursor static [(or) dynamic]

                        For select sal  from emp  where empno=7788

                   Declare  @sal   smallmoney

                     Open c1

        Update emp  set sal =5000              (=6000 if dynamic)

                         Where empno=7788

   Fetch next from c1 into @sal

    Printt @sal

      Close c1

     Deallocate c1.

Output :-  4000 (if static)

                   6000(if dynamic)

Scroll Cursor in SQL Server – SQL Server Tutorial

Scroll Cursor in SQL Server :-

Example  :-

Declare c1 cursor scroll for select  statement

By default cursors supports forward only scrolling and supports fetch next statement.

If cursor is declared with scroll option it can fetch both forward & backward scrolling, and also supports all fetch statement like fetch next prior, first,last, absolute n, relative n.

Absolute n:- Means starting from first record fetch the ‘n’th record.

Example  :-

1

2

3                 absolute 4

4                  Ans: 5th record.

5

Relative n :- means   fetches the ‘n’ th record starting from current record.

Example  :- 

           Declare c1 cursor scroll

                For select ename from emp

           Declare @ename varchar(20)

             Open c1

          Fetch first from c1 into @ename

          Print @ename

           Fetch  next from c1 into @ename

            Print @ename.

         Fetch absolute 5 from c1 into @ename

           Print @ename

         Fetch relative 5 from c1 into @ename

           Print @ename

            Fetch last from c1 into @ename

               Print @ename

           Fetch prior from c1 into @ename

           Print @ename

             Close c1

             Deallocate c1.

Write a program to display every 4th record in emp table?

      Declare c1 cursor scroll

             For select ename from emp

      Declare @ename varchar (20)

         Open c1

     Fetch first from c1 into @ename

        While (@@ fetch_status==0)

          Begin

                Print @ename

               Fetch relative 3 from c1 into @ename

            End

          Close c1

         Deallocate c1.

@@fetch_status in SQL Server – SQL Server Tutorial

@@fetch_status in SQL Server :-

  • This is one system variable which returns status of the fetch statement  i:e fetch is successful or not.
  • If fetch is successful returns 0, otherwise non-zero value.

Write a program to display all employee names & salaries :-

Declare c1 cursor for

        Select ename,sal from emp

Declare @ename varchar (20)

Declare  @sal smallmoney

 Open c1

Fetch next from c1 into @ename,@sal

       While (@@fetch_status=0)

              Begin

              Print  @ename +’earns’+cast(@sal as varchar)

    Fetch next from c1 into @ename,@sal

             End

         Close c1

     Deallocate   c1. 

            Student                                                       Result

Sno     sname     s1   s2   s3                    sno    stot       savg     snes

 1            X         50   60    70

 2            Y         60   75     82

  • Create table student (sno int,sname varchar(20),  S1 int,s2  int  ,s3 int)
  • Create table result (sno int,stot int,savg int,snes  char(4)).
  • Insert into student values(1 ,’A”,50,60,70)
  • Insert into student values (2,’B’,60,52,75)
  • Select * from student.

Program  :

                       Declare c1 cursor

                      For select sno,s1,s2,s3 from student.

                     Declare @sno int,@s1 int ,@s2 int,@s3 int

                      Declare @ stot int,@savg int

                       Declare @snes  char(4).

                 Open c1

                     Fetch next from c1 into @sno,@s1,@s2,@s3

                         While (@@ fetch_status==0)

                                 Begin

                       Set @stot=@s1+@s2+@s3

                        Set @savg  =ceiling((@stot)/3)

                           If @s1>35  and @s2>35 and @s3>35

                            Set @snes=’pass’

                              Else

                             Set @snes =’fail’

                    Insert into result values (@sno,@stot,@savg,@snes)

                     Fetch next from c1 into @sno,@s1,@s2,@s3

                       End

                      Close c1

                       Deallocate c1.

Output :-                   sno         stot     savg     snes                 [select * from result]

                                          1             A       55          pass

                                         2              B        60         pass

Cursors in SQL Server – SQL Server Tutorial

Cursor in SQL Server   :–

  • A cursor is a pointer to resultset which contains set of records return by the select statement.
  • Using the cursor we can process the record in resultset.
  • cursor are used to process the multiple records.
  • Cursor will reduce no. of trips to database server.

To use cursor follow below steps :-

  1. Declare cursor
  2. Open cursor
  3.  Fetch records from cursor
  4. Close cursor
  5.  Deallocate cursor.

(I) Declare the cursor in SQL Server examples :-

Syntax  :-     Declare<name> cursor[options] for select statement.

Example  :-   Declare c1 cursor for select * from emp.

When cursor is declared ,so context area is created in client side.

(II) Open cursor in SQL Server examples :-

Syntax :-  Open<cursor name>

Example :-     open c1

when cursor is opened

  • (A) The select statement is submitted to database server.
  • (B) The records return by the select statement are loaded in context area.
  • (C) cursor will be pointing to the  that context area.

(III) Fetch records from cursor in SQL Server examples :-

The fetch statement is used to fetch record from the context area and load the record into variables.

Syntax  :- Fetch next/prior/first/last/absolute n/relative n from<cursor> into <variables>

Example  :-  fetch next from c1 into @eno @ename @sal

Fitch statement fitches only are record but process multiple records fetch statement should placed inside the loop.

(IV) Close Cursor in SQL Server examples :-

Syntax  :      close <cursor>

Example  :     close c1.

(V) Deallocate cursor in SQL Server examples :-

Syntax  : deallocate <cursor name>

Example  :-  deallocate c1

The memory allocates for the cursor  is released.

Control Statements in SQL Server – SQL Server Tutorial

Control Statements in SQL Server  :

Conditional Statements in SQL Server  :-

(A)

if <cond>
              Begin
                                Statements
              End.

Note  :-  If more than one statements is executed begin & end is required ,else optional.

(B)

if <cond>
               Begin
                                Statements
                End
Else
               Begin
                                Statements
                End.

(C)

if <cond>
              Begin
                                    Statements
             End
Else if <cond>
              Begin
                                      Statements
                End
Else
                Begin
                                   Statements
                 End.

(D)

if <cond>
                Begin
If <cond>
                Begin
                                 Statements
                End
Else
                 Begin
                                    Statements
                 End
Else
                  Begin
                                  Statements
                  End.

Loops in SQL Server :-

While    <condition>
Begin
Statements
End
Write a program to input an empno & check the employee commission,if the commission is null update the commission to rs 500 otherwise increment the commission by 200.

Declare @eno int

Declare  @comm smallmoney

Set @eno=7566

Select @comm =comm from emp

Where empno=@eno

If @ comm is null

Update emp set comm =500 where empno=@eno

Else

Update emp set comm=comm+200 where empno=@eno.

Input an empno update the employee salary based on the job of employee

Clerk   10%

Salesman 15%

Manager   20%

Others       5%

Answer : Declare @eno int,@job varchar

Set @eno=7566

Select @job=job from emp where empno= @eno

If @ job=’clerk’

Update emp set sal=sal*1.1 where empno=@eno

Else if @job=’salesman’

Update emp set sal=sal*1.15 where empno=@eno

Else if @job =’manager’

Update  emp set sal =sal*1.2 where empno=@eno

Else

Update emp set sal =sal *1.05 where empno =@eno

ACCT-MAST Table :-

Accno          Acname         Bal

1                    A               5000

2                    B                 6000

ACCT-TRANS  Table  :-

TRID        TDATE       TAMT       TTYPE        ACCNO

1000            10               1000            D                 1

1001            10               2000             W               2

Write a program  to do bank transaction :    

Declare    @acno     int,@bal    money

Declare     @ttype     char(1),     @tamt money

Set @acno=1

Set @ttype=’w’

Set  @tamt =1000

Select @bal =bal from acct-mast where accno=@acno

If @ttype=’w’ and  @tamt>@bal

Print  ‘insufficient balance’

Else if @ttype=’w’ and @tamt<@bal

Begin

Update acc-mast set bal=bal-@tamt

Where accno=@acno

Insert into acct-trans(type,tdate,tamt,accno)

Else if @ ttype =’D’

Begin

Update acct-mast set bal =bal@tamt

Where  accno=@acno.

Insert into acct-trans(ttype,tdate,tamt,accno)

Values(‘D’,getdate(),@tamt,@acno)

End

Else

Print ‘invalid transaction type’

Lib_mast :   

Bno          btitle      bauthor         issued

1               dbms      xyz                no

2                java       abc                yes

Lib_trans :

Bno      doi       dod(date of due)

1           11/11      21/11

Write a program to process library transaction:

  • Create table lib_mast (bno varchar(20), btitle  varchar (20) bauthor varchar(20),issued char (1))
  • Create table lib_trans(bno varchar(20),doi date time ,dod date time)
  • Insert into lib_mast values(1,’dbms’,’abc’,’n’)

Program :

Declare @dno int,@iss char (1)

Declare @dod date time

Set @bno=1

Select @iss=issued from lib_mast

Where bno=@bno.

If @iss=’y’

Print ‘book alredy issued’

Else

Begin

Set @dod=dateadd(dd,10,getdata())

Insert into lib_trans values (@bno,getdate(),@dod)

Update lib_mast set issued =’y’

Where bno=@bno

Database Programming in SQL Server – SQL Server Tutorial

Database Programming in SQL Server :-

  • To do database programming SQL statements must be embedded in TSQL block.
  • The following SQL statements can be embedded in TSQL block.

(I) All DML commands

(II) All TCL commands

(III) DRL command

  • Syntax of the select statement:

Select @ var1=col1, @var2=col2 ——– from <tabname> [where<condition>]

Example   :    Select @ eno=empno, @ename=ename from emp where empno=7369

                              Select @sal=sal from emp where,empno=@eno

                              Print  ‘employee earning’ +cast (@sal as varchar)

Write a program to input an employee number & calculate the employee experience.

                    Declare  @eno int,@doj datetime

                    Declare @ expr int

                    Set @ eno =7566

                    Select @doj=hiredate from emp where empno=@eno

                    Set @expr=abs (datediff(yy,setdate() ,@doj))

                   Print ‘employee working since ‘+ cast(@expr as varchar)+’years’

                   Output  → employee working since 28 years.

T-SQL Programming in SQL Server – SQL Server Tutorial

T-SQL Programming in SQL Server:-

TSQL program is called “TSQL BLOCK”.

TSQL Blocks are two types:-

(I) Anonymous blocks

(II) Named blocks:                                        

  1. Procedures
  2. Functions
  3. DB triggers

(I) Anonymous Blocks in SQL Server :-

Declare statement :

  • It is used to declare a variable.

Syntax : declare @ varname data type.

Example  :-  

Declare @ X int

Declare @ s varchar(2) ,@ d date time.

Set statement  :-

  • It is used to assign value to a variable.

       Set @ varname = value

       Set @ x=100

Print statement :-

  • It is used to print messages (or) variables.

Print ‘hello’

declare @x int, @y int, @z int

Set @ x=10

Set @ y =20

Set @z=@x+@y

Print ’sum of two nos =’+cast(@z as varchar)

Backup and Restore in SQL Server – SQL Server Tutorial

Backup and Restore in SQL Server :

Backup in SQL Server:-

To take backup of the database

  • Select the database
  • Right click
  • Select task
  • Select backup.
  • Select the database : MYDB
  • Backup :  Full, Differential, Transaction log.
  • Full → complete backup
  • Deferential means backup since backup. Since last backup what new objects are copied in backup.
  • Transaction log  : only transactions are copied backup copy.
  • Backup component : database
  • Backup set will  expire:  After :2 days.

Restoring the Database in SQL Server  :-

To restore the database:

  • Select database 
  •  Right click
  • Restore database option
  • To database : DB15
  • form device  → Path
  • Goto option → change the filename (or) path.

Indexed Views in SQL Server – SQL Server Tutorial

Indexed Views in SQL Server:

A view whose query result is stored in database is called “indexed view”

In oracle is called “materialized view”.

Index views are created mainly for two reasons:

  1. To improve performance of aggregate operation.
  2. To create local copy for remote databases.

These two operations are most expensive.

Index views occupies memory as it containing data.

Difference between view and index view

View:

  1. When View is created the query is stored in Database
  2. A View doesn’t contains any data.

Index View:

  1. When Index view is created the query result stored in Database
  2.  Index views contains data. 

Follows 2 steps index viewed  :-

  1. Create view with schema binding.
  2. Create index on that view.

Create View with Schema Binding :

Create view indv1 with schemabinding As Select deptno,sum(isnull(sal,0))sumsal, Count_big(*) emps From dbo.emp Group by    deptno

Create Index on that View :

Create unique clustered index on group by column I:e   depno.

  • Create unique clustered index  ill on indv1(depno)
  • Select * from indv1
  • Update emp set sal =sal+1000 where deptno =10.

After creating indexed view if we make any changes to base table the changes are reflected to index view automatically.

Clustered and Non Clustered Indexes in SQL Server – SQL Server Tutorial

TYPES OF INDEXES  :-

Clustered and non clustered indexes:

  • Clustered and non clustered indexes.
  • If order of the values in table and order of the values in index its not same,then index is called”non-cluster index”.
  • By default SQL Server creates non clustered index.

Clustered index :

  • If order of the values in table and if order of the values in index is same then index is called “clustered index”.
  • Here index stores records and record are arranged based on the column on which clustered index is created.
  • To create clustered index then use keyword ”clustered”.
  • We can create only one clustered index pin table.
  • By default sql server create clustered unique index on primery key column.
  • If we want to arrange records & doesn’t allow duplicate values then  use clustered unique index.

– Create table xyz(xint,yint,zint)
– Create clustered index i10 on xyz(z).
– Insert into xyz values(1,2,10)
– Insert into xyz values (4,3,12).

Select * from xyz 

X Y Z
1  2  10
4 3 12

How to drop index :

– Drop index xyz.i10

– Drop index xyz .i11

  • User can’t drop  indexes primary key column. When primary key is dropped the correspond index is also dropped.
  • Query menu -> Display estimated execution plan.