Category: Tutorials

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.

Encryption in SQL Server  :-

• If  view created with encryption then view defination will be hidden in information_schema views table.

Example  :-  create view v8 with encryption as select empno, ename, sal from emp

                                                             ↓

                         Select * from information_schema.views.

Drop views  in SQL:

Drop view v8

• If drop the views ,it’s not effected in the base table,only deleted from information_schema views

Note  :–      (I) If Base table is dropped, so views is not dropped. Because view is an independent object.

                      (II) But in case, the view is not accessed.

Schema Binding  in SQL:-

• When view created by schema binding then view becomes dependent object on base table.
• The base table can’t be dropped without dropping view.

Example  :- Create view v8 with schema binding as select empno,ename,sal,from dbo.emp

Note  :–   When schema binding is used then table name prefixed by user name. Example :- dbo.emp

Example  :-

•  Drop table emp
•          ↓
• Invalid because view is dependent obj,so we first drop the view in this Case.
•          ↓
• Drop view v8
•           ↓
• Drop table emp.

Synonym  in SQL  :–

• A synonym is another name(or) alternative name for a table.

• Synonyms are created

(i) If table name is lengthy.

(ii) To access table without owner name.

Syntax  :-

•                  Create synonyms <name> for <tabname>

Example  :-

•                  Create synonym s1 for emp.
•                   Select * from s1.

DBO user                                                  NIT   user

      ↓                                                                             ↓

 Table name                                                  Table name

       ↓                                                                            ↓

    Emp                                                                  Emp

       ↓                                                                            ↓

Grant select,insert on emp to NIT  (→)  Select * from dbo.emp

                                                                                       ↓

                                                                         Create synonym s1 for dbo.emp

                                                                                        ↓

{here,to access table without owner name}  ←   select * from s1

Difference between View and Synonym :-

                   View                                                                                  Synonym

View is a subset of a table.                                         Synonym is a mirror of a table.

View can be based on more than one table        Synonym is always based on one table.                       

Dropping Synonym in SQL :-

• Drop synonym s1.

Complex Views in SQL Server:-

Views are based on more than one table and also based on

• Group by clause
• Having clause
• Distinct clause
• Aggregate   function
• Expression.

Then view is said to be “complex view”.

Difference between simple view and complex view in sql:

Simple view

• It is updatable  {updatable means allow DML operations}

complex view

• but complex views are not updatable.    

Example : 

Create view v5 as select e.empno, e.ename, e.sal, d.deptno ,d.dname from  e.emp , d.dept.

• Query fired on view takes more times than query fired on base table.

                      Views degrades performance

• Select * from information_schema.views ( This maintains information about the views created by user)

Simple and Complex Views in SQL:-

Grant & Revoke Using grant & revoke we can restrict user operations.

Views Using views we can restrict data [particular column]

A view is based on one table is called “simple views”.

Syntax :-

Create view <name> [<with options>] as select statement [where<condition>]

Example :-

Select user_name –> Dbo

Example of simple view   :-

Create view  v1 As Select empno, ename, sal from emp.

When the above view is created,the select statement is stored in database that is why view is also called  “stored-query”.

• Grant select,insert,update,delete on v1 to NIT.
• Go to nit user
• Select * from v1

Once permission are granted to user the user can perform DML operators on base table through view.

{  Note  :-What ever changes the user, it is effected on base table {i:e.dbo}  }

Example  :-

• Create view v2 as (it can be created on base table only) select * from emp where dept no=10.
• Grant select, insert, update, delete on v2 to NIT. 
• Create  view v3 as Select empno, ename, sal, deptno, from emp where deptno=10.

with check option in view in SQL:-

• If view created with check option any DML operation through that view violates that where condition than DML operation is not accepted.

Example  :-  create view v4 As Select empno,ename,sal,deptno From  emp Where deptno =10 With check option

• Insert into v4 values(8787,’abc’,6000,10)
• Insert into v4 values (8788,’xyz’,9000,20) {invalid,because view created by with check option}

 DATABASE OBJECTS :-

 The different database objects are:

• Tables
• Views
• Synonyms
• Indexes
• Indexed views
• procedures
• Functions
• DB triggers

VIEWS   :-

•  A view is a “ subset of a table” and also called “virtual table” (or) “stored query”.

• Views  are mainly created for Security and Simplicity.

• By creating views, specific columns & specific rows can be granted to users.
• Views are two types  :-

(A)  SIMPLE VIEWS

(B)   COMPLEX VIEWS.

GRANT COMMAND :–   

Grant command is used to be permission on a table to users.

Syntax:-

Grant <privilages> on <tabname> To <users> [with grant option]

The privileges are: Select, insert, update, delete.

Grant select,insert on emp to NIT. (This command is dbo is give permission to NIT table for select & insert)

go to NIT table                     

Select * from emp

• user         user
•  dbo            nit 
•  granted        granted

Grant select on emp to NIT,X,Y -> This command is grant to more tables.

WITH  GRANT OPTION  :-

If  permissions are granted with grant option then grantee can also grant permission to other users.

Example :-   grant  select, insert on emp to nit with grant option.

User                 user           user      

 dbo                  nit                X

    ↓                    ↑                  ↑           → with grant option

——–emp—————emp—-

           Table                     (select,insert)

      (select,insert)

REVOKE COMMAND  :-

Revoke command is used to take back the permission from user.

Syntax:-

Revoke <privilage> on <tabname>from <users>

In dbo user  : 

Example :-  revoke select,insert on emp from nit cascade

Note :-  

When you give the with grant option,you must specify the cascade.

When you give the grant option only,you mentioned only revoke.

Revoke can be used to particular privileges take back the permission from user.

                           ↓

Revoke      insert  on  emp     from      nit

  Dbo                              NIT

  Emp                              emp

   ↑                                    ↑

   ———-emp————–  
                               ↓

If  you want to see the date in NIT table

                               ↓

Select * from dbo_emp

DATABASE SECURITY  :-

The different facilities in SQL Server to provide security :-

1. Logins  and users
2. Great & revoke commands
3. Views.

(I) Logins & users  :-

Creating logins :-

In sql server the users are two types  :

1. Window user
2. Sql Server user

(i) Creating windows user :-

• Open control panel
•               ↓
• Open user accounts
•                 ↓
• click create a new A/C – option
•                 ↓
• Enter user name
•                 ↓
• Click next
•                 ↓
•  Select limited
•                 ↓
• Click create A/C button

If this user wants to connect to sql server,then first the user has login to the operating system & open sql server & select window Authentication.

(2)  SQL SERVER   :-

Only system adminstrator[sa] is having permission to create logins & users
to create a new login,

•               In object explorer
•                         ↓
•               Select properties
•                          ↓
•               Select server roles
•                           ↓
•                And select system admin

Creating Users  :-

After connecting to sql server to access database are user must created in th database.
Only sa can create users.

• To create a user,expanded database.
•           ↓
•  Expanding  searify folder,
•            ↓
• Select users
•             ↓
•  Select new user
•              ↓
• Give user name:NSI
•               ↓
•  Login name :scott
•                ↓
•    Click   ok

Select user_name ( ).
To give the permission to user,so

•       Select the User
•           ↓
• Right click select properties

•            ↓
• Database role membership
•            ↓
• Select the permission given to user
•            ↓
• Click ok

Example:  Db_ddladmsn

                          ↓

                         Ok

DATABASE TRANSACTIONS :-

A transaction is a unit of work,mainly consist of DML operations which must be made permanent as a whole and must be under as a whole.

SQL server runs in 3 types of transaction modes :-

• Auto commit mode [default]
• Implicit transaction mode.
• Explicit transaction mode.

(I) Auto commit mode :- {default}

• In auto commit mode,the transaction are starts with “DML operations”.
• After executing dml command the transaction ends automatically with “commit”.
• In auto commit mode,user can’t control transaction.

(II) Implicit transaction mode :–

• In implicit transaction mode a transaction starts with DML operation & A transaction ends with “commit/rollback”.

 Example  :–

Insert → transaction starts

Update

Commit→ transaction ends

• If transaction ends with “commit” then the operators performed in transaction(or)recorded in the database.

Example :-

Insert → transaction starts

Update

Rollback → transaction ends

• If transaction ends with rollback the changes mode to the database one
• To run SQL Server in implicit transaction mode executing the following command :-

Set implicit_transaction on

(III) Explicit transaction mode :-

• To run sql server in implicit transaction mode executing the following command.

Set implicit_transaction on

• If u want to run on auto commit mode.

Set implicit_transaction off

• In this trasanction starts with begin tansaction statement and ends with commit/rollback transaction.

Example   :-

(1)  Begin transaction

Insert

Update

Commit transaction

(2) begin transaction

Insert

Update

Rollback transaction

• Auto commit             implicit                                  explicit 
• Update                              Update                                      begin transaction
•                                             Commit/rollback                  update
•                                                                                                   Commit transaction/rollback Transaction

 Example  :-  Insert  transaction starts

Update

Commit  transaction ends

Rollback  transaction ends → Invalid [useless]

Note:-  If commit is ends,after rollback is useless similarly if rollback is ends,after commit is useless.

SAVE TRANSACTION  :-

• The save transaction is used to break a long transaction into small parts.
• Using this we can cancel part of the transaction:

Example  :-

                  Insert

                  Update

                   Save transaction st1

                   Update  

                   Delete

                   Rollback transaction st1

Advantage   :

“Only part of the transaction can be cancelled”