Category: 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  :

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.

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:-

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 :

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:

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.

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 

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.

EXPORTING AND IMPORTING Data:

The different facilities provided by sql server to do export & import.

1. Export and import wizard
2. Bcp [bulk copy program]
3. Bulk insert [sql 2008]

(I) SQL Server Export and Import Wizard :-

Data is converted into file when we want to transfer data from one location to another.

 To do export  :

                        Select DB in object explorer

                             (example: DB 11 AM)

                                                 ↓

                                           Right click

                                                  ↓

                                             Select task

                                                   ↓

                                               Export data.

                          Wizard opens → click next→

Data Source : Select data source as SQL native client.

 Server name  : Name of computer where sql server install.

Authentication in SQL Server :

• Window
• Sql server authentication

Database  : Name of database

Choose a destination :  Target file -> Flat file destination

File name  : c:\emp.txt -> Next

•                  Copy data from one or more file view
•                  Configure flat file destination
•                  Source table (or) view [mydb] [dbo].[emp]
•                  Row  divimiter      
•                  Column divimiter             

BCP-BULK Copy Program :

It is command line utility[which runs on command prompt]

Syntax   :- BCP   dbtable/query in/out/query out target file options.

Out :  Use out option to export the data to text file.

In :   Use in option to import the data from text file.

EXPORTING AND IMPORTING:-

BULK INSERT  (SQL 2008) :

Syntax  :-

Bulk insert into cust from ‘c:\cust .txt’ With (columnterminator : ‘,’Rowterminator  :’\n’).

                                                                   Export                   Import    

Import & export wizard                           yes                             yes

Bcp                                                               yes                             yes

Bulk insert                                                   no                               yes

Types of Indexes in SQL Server  :-

1. Simple index
2. Composite index
3. Unique index
4. Non clustered index
5. Clustered index 

(I) Simple index in SQL  :-

• If index is  created on one column,then it is called ”simple index”.

Syntax  : Create index <name> on<tabname>(colname).

Example :-  Create index i1 or emp(sal)

Note  :-    Query execution steps  :- 

• Query parsed → parsing means → compare the query
• Query optimized  → optimization means → preparing difference.
• Query executed  →  plans executing the query,estimating the cost each plan .

Indexes are created by user and used by system.
We can’t see the values of index.

(II) Composite index  in SQL  :-

• Index is created on more than one column,then it is called “composite index”.

Example :- Create index i2 on emp [deptno,job] (where we use this above index means as follows)

Select * from emp order by dept no,job

Select * from emp where deptno=10 and job=’clerk’

Note  : SQL server doesn’t perform composite index on ’OR’ operator.

(III) UNIQUE INDEX in SQL:-

Unique index doesn’t allow duplicate values in the column on which index is created.

Create unique index  i3 on emp (ename)

Different methods to enforce uniqueness

• Declare pk/unique constraint
• Create unique index.
• Create  db trigger.
• Declare column with identity.

When primary key declare then sql server unique index Primary key columns are automatically index by  SQL Server.by default SQL Server creates unique index on primary key column.

Indexes in SQL Server :-

• Index is a database object used to make data retrieval fast.
• In database indexes are created on particular column.
• The column on which the index is created, it’s called “index-key”.
• index created on salary column looks as follows.

In sql server, searching for a particular record is of two types.

•  Table scan
•  Index scan

(I) Table Scan in SQL Server  :-

• In table scan, each & every record as to be compared.

Then SQL server scan the entire file.

• It consumes more times.

(II) Index Scan in SQL Server :-

• In index scan, on Average SQL Server scans only half of the file.
• Which is  much faster than table scan.

SQL Server uses Index in the following Cases  :–

(I) If where condition (or) order by clauses is based on the column on which the index is created.

Example  :-  i1(sal) Select * from emp  where sal > 2000

SQL server doesn’t following index in the following cases:-

(I) If where clause (or) order by clause is based on the column or which the index is not available.

Example :-  i1(sal) select * from emp where ename=’smith’.

(II) Where clause is based on not equal to [<>] operation, in this case also sql server doesn’t use index scan.

Example  :-  i1(sal) select * from emp where sal<>4000

Creating number of indexes or particular table degrades performance of DML operations.

To maintain the balance so created indexes on columns frequently accessed in where clause & order by clause.

  OLTP                              OLAP

Get “in”                             Get “out”

Insert                                 Select.

• Indexes are less used OLTP, widely used OLAP.