SQL Language

SQL Basics Overview

Index

  1. Creating and altering table records (DDL and DML)
  2. Constraints
  3. Joins
  4. Views
  5. Control flow constructs in SQL
  6. Using Local Variables
  7. Cursors
  8. Creating User Defined Data types
  9. Stored Procedures
  10. Functions
  11. Exception Handling in Procedures
  12. Triggers
  13. Indexing in SQL
  14. Transaction and Locking
  15. XML support in SQL Server

1. Creating and altering tables

Creating a Database

Syntax: create database database_name

  1. Create database dn016_346238  
Using database

Syntax: use database_name use dn016_346238

Creating schema

Syntax: create schema schema_name create schema cts
  1. creating table create table employee  
  2.   
  3. (  
  4.   
  5. id int primary key, name varchar(50), address nvarchar(max), mail_id nvarchar(50)  
  6.   
  7. )  
Inserting values to table
  1. insert into cts.employee values(346238,'prasanna','tiruvannamalai','[email protected]') insert into cts.employee values(346197,'lakshman','salem','[email protected]')  
  2.   
  3. insert into cts.employee (id,address) values (346200,'tiruvannamalai')  
Displaying all entries
  1. table select * from cts.employee  
Adding new column to the table (Altering the table data structure)
  1. alter table cts.employee add phone int  
Altering an existing column
  1. alter table cts.employee alter column cell nvarchar(15)  
Updating name with the given ID
  1. update cts.employee set name='lena prasanna' where id = 346238 update cts.employee set cell='9952941393' where id = 346238  
Renaming a particular column name
  1. table exec sp_rename 'cts.employee.phone','cell','column'  
Displaying top 2 records
  1. table select top 2 Id,name from cts.employee  
Displaying selected entries based on the conditions given
  1. select * from cts.employee where name='lakshman' and address='salem'  
Creating another table
  1. create table cts.employees  
  2. (  
  3.   
  4. empid varchar(10) primary key, empname varchar(200), deptid varchar(10), joining_date date,  
  5.   
  6. dob date, yrs_of_expr int,  
  7.   
  8. employee_category varchar(10)  
  9.   
  10. )  
Displaying all the entries in the table
  1. select * from cts.employees  
  2.   
  3. inserting values to table  
  4.   
  5. insert into cts.employees values(346238,'lena','dn','2012-08-28','1990-05-09',0,'programmer analyst trainee')  
  6.   
  7. insert into cts.employees values(346200,'kishore','dn','2012-08-28','1990-12-08',0,'programmer analyst trainee')  
  8.   
  9. insert into cts.employees values(346201,'prasanna','dn','2012-08-28','1990-07-10',0,'programmer analyst trainee')  
  10.   
  11. insert into cts.employees values(346197,'lakshman','dn','2012-08-28','1990-08-16',0,'programmer analyst trainee')  
Adding new column to the table (Altering the table data structure)  
  1. alter table cts.employees alter column employee_category varchar(50)  
Displaying details based upon a condition
  1. select empid,dob,joining_date from cts.employees where (year(joining_date))>2010 and (month(dob))>5  
Counting number of records in the employee table based upon a specified condition
  1. select COUNT(*) "no,ofemployees" from cts.employees where (year(joining_date))>2010 and (datename(mm,dob))='december'  
Displaying all entries with the difference between 2 dates is greater than 21
  1. select empid,dob,joining_date from cts.employees where DATEDIFF(yy,dob,joining_date) >21  
Deleting a specific entry from table
  1. DELETE FROM cts.Employees where dob='1990-07-10'  
T- SQL Operations 
  • Select , Order by, from, where clauses:

    • SELECT keyword is used to display all the entries specified next to it.

    • ORDER BY clause allows us to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.

    • FROM clause produces the tabular structure, the starting set of data on which all other operations in a SELECT statement are performed. Note that, FROM clause is the first clause that will be looked by the database system while parsing the SQL statement.

    • WHERE clause is always followed by the condition. It is used to filter the data set.

      Syntax

      SELECT columns FROM tables
      WHERE predicates
      ORDER BY column ASC/DESC;

  • Top clause:

    • TOP clause is used to specify the number of records to return.

      Syntax:

      SELECT TOP number|percent column_name(s) FROM table_name

  • Patterns and wildcards:

    • SQL patterns and wildcards can substitute for one or more characters when searching for data in a database.

      For example:

    • ‘%s’ - Displays all the strings that ends with ‘s’ o ‘s%’ – displays all the string that starts with ‘s’.

    • ‘%san%’ – Displays all the strings that has ‘san’ either in the front or at the back o ‘[san]%’ – displays all the strings that starts with s,a, and n.

    • IN CLAUSE in SQL is typically used in a WHERE clause. It is normally used when we want to create a subset of records from another set.

    • BETWEEN clause is used to select a range of values specified between the keyword ‘between’ & ‘and’.

    • '_00%' - Finds any values that have 00 in the second and third positions.

    • The following is the table which gives more explanation about the wildcards.

      Wild card Description
      % A substitute for zero or more characters.
      _ A substitute for exactly one character.
      [charlist] Any single character in charlist.
      [^charlist]
      (Or)
      [!charlist]       		Any single character in charlist

    Note: LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator:

    • The percent sign (%)
    • The underscore (_)

    String Functions

    There are some inbuilt functions in SQL that will be used to manipulate the strings. They are as follows:

    1. substring(string,startIndex,length) - Returns part of a string. For example, SELECT SUBSTRING('SQLServer', 4, 3) returns ‘Ser’.

    2. len(string) – Returns the length of the string. For example, SELECT LEN(‘Hello’) returns 5.

    3. upper(string) – Converts the given string to uppercase.

    4. lower(string) – Converts the given string to lowercase.

    5. replace(string1,String2,String3)- Replaces all occurrences of the string2 in the string1 with string3. For example, SELECT REPLACE('All Function' , 'All', 'SQL') will return ‘SQL Function’.

    6. ltrim(string) and rtrim(string) – ltrim(String) returns a string after removing leading blanks on the left side. For example, SELECT LTRIM(' sqlserver') returns 'sqlserver' (Remove left side space or blanks). Similarly, rtrim(String) returns a string after removing leading blanks on the right side. For example,

      SELECT RTRIM('sqlserver ') returns 'sqlserver' (Remove right side space or blanks).

    7. left(String,Integer) - Returns left part of a string with the specified number of characters. For example, SELECT LEFT('TravelYourself', 6) gives ‘Travel’.

    8. right(String,Integer)- Returns right part of a string with the specified number of characters. For example, SELECT RIGHT('TravelYourself', 6) gives ‘urself’

    Date Functions

    There are several functions that deals with date manipulation.They are:

    1. Getdate() and sysdatetime() –Returns current date and time from the SQL server.
    2. Dateadd(format,number,date) - Adds or subtracts a specified time interval from a date.
    3. Datediff(format,date1,date2) - Returns the time between two dates.
    4. Datepart(format,date) -Returns a single part of a date/time, such as year, month, day, hour, minute, etc.
    5. Datename(format,date) - Returns the name or value of the specified datepart of the date.

      format – yy/yyyy/mm/dd/d/m/ hh/mi/ss

2. Constraints

Points to remember

  • Constraints are used to limit the type of data that can go into a table.

  • Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

  • There are several constraints available in SQL. They are namely,

    a. Primary key
    b. Foreign
    c. Default key
    d. Unique
    e. Not null

  • Defining Primary key constraint:

    • Creating a column as a primary key:

      Syntax:

      Create table tablename
      (
      Columnname type primary key,
      ……
      )

      Note: If we add primary key constraint as said above, then we will not be able to drop them individually. Instead, we need to drop the entire table later if there is a change in the primary key column of the table.

    • Creating a primary key constraint at column level:

      Syntax:

      Create table tablename
      (
      Columnname type constraint constraint_name primary key,
      ……
      )

      Note: If we add primary key constraint as said above, then we can drop the primary key constraint whenever needed without needed to drop the table.

    • Creating a primary key constraint at table level:

      Syntax:

      Create table tablename
      (
      Columnname1 type1, Columnname2 type2,
      ……,
      ColumnnameN typeN,
      Constraint constraint_name primary key(columnnameN)
      )

      Note: If we add primary key constraint as said above, then we can drop the primary key constraint whenever needed without needed to drop the table.

    • Adding primary key constraint using ‘alter’ DDL command:

      Syntax:

      Alter table tablename add constraint constraint_name

      Primary key(col)

  • Defining Foreign key constraint:

    • Creating a column as a foreign key:

      Syntax:

      Create table tablename
      (
      Columnname type foreign key references constraint_name (pk_column),
      ……
      )

      Note: If we add foreign key constraint as said above, then we will not be able to drop them individually. Instead, we need to drop the entire table later if there is a change in the foreign key column of the table.

    • Creating a foreign key constraint at column level:

      Syntax:

      Create table tablename
      (
      Columnname type constraint constraint_name foreign key references Pk_table_name (pk_column) ,
      ……
      )

      Note: If we add foreign key constraint as said above, then we can drop the constraint whenever needed without needed to drop the table.

    • Creating a foreign key constraint at table level:

      Syntax:

      Create table tablename
      (
      Columnname1 type1, Columnname2 type2,
      ……,
      ColumnnameN typeN,
      Constraint constraint_name foreign key(columnnameN) references Pk_table_name (pk_column),
      )

      Note: If we add foreign key constraint as said above, then we can drop the constraint whenever needed without needed to drop the table.

    • Adding foreign key constraint using ‘alter’ DDL command:

      Syntax:

      Alter table tablename add constraint constraint_name
      Foreign key(column_name) references
      Pk_table_name(pk_column)
Defining Check Constraint:
  • Creating a column as check constraint:

    Syntax:

    Create table tablename
    (
    Columnname type check(condition),
    ……
    )

  • Creating a check constraint at column level:

    Syntax:

    Create table tablename
    (
    Columnname type constraint constraint_name check(condition),
    ……
    )

    Note: If we add primary key constraint as said above, then we can drop the primary key constraint whenever needed without needed to drop the table.

  • Creating a primary key constraint at table level:

    Syntax:

    Create table tablename
    (
    Columnname1 type1, Columnname2 type2,
    ……,
    ColumnnameN typeN,
    Constraint constraint_name check(condition)

    Note: Like primary key and foreign key constraint, there is no need to add extra column name for check constraint as the condition given to check constraint will be a column name.

  • Defining Default constraint:

    Syntax:

    Create table tablename
    (
    Columnname1 type1 default value1,
    Columnname2 type2 check(condition) default value2,
    Note: We can also combine check constraint with the default constraint.
    ….
    )

  • Defining default constraint using alter command:

    Syntax:

    Alter table tablename add constraint constraint_name
    Default default_value for column_name

  • Using in table level: o

    Syntax:

    Create table tablename
    (
    Column1 type1, Column2 type2,
    ….
    Constraint constraint_name default default_value for column
    )

  • To drop any constraint: o

    Syntax:

    Alter table tablename drop constraint constraint_name

Using database

  1. use 346238  
Check if the table already exists, if exist, drop the table,
  1. if OBJECT_ID('cts.Customer','U') is not null  
  2. drop table cts.Customer  
Creating table
  1. create table cts.Customer  
  2. (  
  3. c_id int,  
  4. c_name varchar(50), address varchar(max),  
  5. constraint pkey primary key(c_id)  
  6. )  
Check if the table already exist,
  1. if exist, drop the table. if OBJECT_ID('cts.orderid','U') is not null  
  2. drop table cts.orderid  
  3. creating table  
  4. create table cts.orderid  
  5. (  
  6. orderno int constraint p_key primary key,  
  7. If we use orderno int primary key, we cant modify. if we add it as a constraint, we can drop anytime we want to. So we used "orderno int constraint p_key primary key"  
  8. product_name varchar(50), c_id int  
  9. )  
Alter table definition by adding new column
  1. alter table cts.Customer add language_known varchar(50)   
  2. alter table cts.orderid add Product_Qty varchar(50)  
  3. alter table cts.orderid add Order_Price money  
Alter table to add constraint
  1. alter table cts.orderid add constraint f_key foreign key (c_id) references cts.Customer(c_id)   
  2. alter table cts.orderid add constraint def_key default 1000 for orderno  
  3. alter table cts.orderid add constraint check_key check (orderno>500)  
Alter table syntax to drop constraint
  1. alter table cts.orderid drop constraint def_key  
  2. alter table cts.orderid drop constraint f_key  
Inserting values into the table
  1. insert into cts.Customer values ('Kishore', 'Tamilnadu','Tamil');  
  2. insert into cts.Customer values ('Lenaprasanna','Andhrapradesh','Telugu');   
  3. insert into cts.Customer values ('Chanakya','Andhrapradesh','Telugu'); select * from cts.Customer;  
DDL command that deletes all the entries from the table
  1. truncate table cts.orderid;  
Constraints usage
  1. insert into cts.orderid values (1002,'Dictionary',1000,1,100.00) it will not get inserted as it violates check constraint. Recall that, Check constraint condition is orderno > 500  
  2.   
  3. insert into cts.orderid values (1000,'E-Learning',1002,3,5000.00) it will be inserted. select * from cts.orderid  
3. Joins

Concept
  • Querying result from multiple tables is the main concept of Joins.
  • It provides new visualized table records.

There are the following 3 types of joins::

  1. Inner join – default join is Inner join.
  2. Outer join

    a. Right Outer join or Right join
    b. Left Outer join or Left join
    c. Full Outer join

  3. Cross join

Inner join

  1. select c.c_id,c.c_name,c.language_known,o.orderno,o.product_name from cts.Customer c inner join cts.orderid o on c.c_id=o.c_id  
Left outer join
  1. select c.c_id,c.c_name,c.c_address,o.product_name from cts.Customer c left outer join cts.orderid o on c.c_id=o.c_id  
Inner join using and clause
  1. select c.c_id,c.c_name,o.product_name from cts.Customer c join cts.orderid o on c.c_id=o.c_id and c.c_address='Andhrapradesh'  
Alternate way for inner join using where clause
  1. select c.c_id,c.c_name,o.product_name from cts.Customer c join cts.orderid o on c.c_id=o.c_id where c.c_address='Andhrapradesh'  
To find who didn't bought product
  1. select o.c_id,o.product_name from cts.Customer c right join cts.orderid o on c.c_id=o.c_id where c.c_id is null  
Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

Some of the Aggregate Functions are: 
  1. count()
  2. avg()
  3. sum()
  4. max()
  5. min()

Examples

AVG() - Average value of columns.

  1. SELECT AVG(Salary) FROM CUSTOMERS  
  2.   
  3. Value = 4675  
COUNT() - number of rows.
  1. Select COUNT(*) FROM CUSTOMERS  
  2.   
  3. Value = 4  
  4.   
  5. SELECT COUNT (DISTINCT Salary) FROM CUSTOMERS  
  6.   
  7. Value = 3  
MAX() - Maximun or Highest number in a column.
  1. SELECT MAX(Salary) FROM CUSTOMERS  
  2.   
  3. Value = 5000  
MIN() - Minimum or Lowest number in a column.
  1. SELECT MIN(Salary) FROM CUSTOMERS  
  2.   
  3. Value = 4000  
SUM() - Total number in a column.
  1. SELECT SUM(Salary) FROM CUSTOMERS  
  2.   
  3. Value = 18500  
To find distinct number of customers
  1. select distinct COUNT(c.c_id) "No. of customers" from cts.Customer c join cts.orderid o on c.c_id=o.c_id  
Alternatively, the above statement can be written as:
  1. select distinct COUNT(c_id) "No. of distinct customers" from cts.orderid  
Sum function
  1. select SUM(Order_price) from cts.orderid where Order_Price>76  
  2.   
  3. select c_id,SUM(Order_Price),product_name "total amount" from cts.orderid group by c_id,product_name  
  4.   
  5. select c_id,SUM(Order_Price) from cts.orderid group by c_id having c_id > 1002  
  6.   
  7. select c_id,SUM(Order_Price) "Total price" from cts.orderid where c_id > 3 and Order_Price >100 group by c_id having SUM(Order_Price) > 10  
4.Views

Points to remember: 
  • In SQL, a view is a virtual table based on the result-set of an SQL statement.

  • A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

  • You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data was coming from one single table.

Creating a view

Syntax:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Updating a view

Syntax

ALTER VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Dropping a view

Syntax

DROP VIEW view_name

Example 1: Create a view that displays all the entries of employee table. Also display the view:

  1. create view employee as select * from cts.employee  
  2.   
  3. select * from employee  
Example 2: Create a view employee with attributes id, name where salary=55000 from employee table. Also display the view.
  1. create view employeeIDandName (id,name)as select id,name from cts.employee where salary=55000; select * from employeeIDandName  
Example 3: Create a view emp with name and with the condition that salary reduced by half from employee table.
  1. create view employeeHalfSalary (name,salary)  
  2. As  
  3. select name,salary/2 from cts.employee; select * from employeeHalfSalary;  
Example 4: Create a view that has all the details about the customer whose address is “chennai”.
  1. create view Viewcustomers  
  2. as  
  3. select * from customers where addres='Chennai'  
  4. select * from Viewcustomers  
Example 5: Alter the above view and make the salary to be twice the actual salary in employee table.
  1. alter view doubleEmpSalary(name,salary)as(select name,2*salary from cts.employee);  
  2. select * from doubleEmpSalary;  
Example 6: Drop the view created.
  1. drop view employee;  
5. Control Flow Construction in SQL

Using if statement
  1. if ((select id from cts.employee where name = 'lena prasanna') = 346238) begin  
  2. print 'Andhrapradesh'  
  3. end  
  4. else  
  5. begin  
  6. print 'Tamilnadu'  
  7. end  
Using while loop pints 5 tables till 100
  1. declare @count int   
  2. declare @num int  
  3. Note: every variables declared, should be initialized to any number set @num = 0  
  4. set @count = 1  
  5. while @num < 100 begin  
  6. set @num = 5 * @count print @num  
  7. set @count = @count + 1  
  8. end  
Using if exists statement
  1. if exists (select name from cts.employee where name='lena prasanna') print 'Andhrapradesh'  
  2. else  
  3. print 'Tamilnadu'  
Using switch case
  1. tax calculation  
Note: using syntax 1, we can’t evaluate an expression. Below is the example of using syntax 1. 
  1. Simple case expression function
    1. select name,States=case name when   
    2. 'harsha' then 'Andhrapradesh'  
    3.   
    4. when 'sivasaiprasad' then 'Andhrapradesh'   
    5. when 'lena prasanna' then 'Andhrapradesh'   
    6. when 'kishore'  
    7. then 'Tamilnadu'  
    8. when 'chanakya'   
    9. then 'Andhrapradesh' else 'karnataka'  
    10. end from cts.employee  
  2. METTL syntax if we use syntax 1
    1. select name,case name  
    2. when 'harsha'   
    3. then 'Andhrapradesh'   
    4. when 'sivasaiprasad'   
    5. then 'Andhrapradesh'  
    6.   
    7. when 'lena prasanna'   
    8. then 'Andhrapradesh' when 'kishore'   
    9. then 'Tamilnadu'  
    10.   
    11. when 'chanakya'   
    12. then 'Andhrapradesh'   
    13. else 'karnataka'  
    14.   
    15. end "States" from cts.employee  

Using syntax 2 , we can evaluate expression (search case expression function which evaluate result based on Boolean expression)

  1. select name, Tax_amount =case   
  2. when SALARY >40000   
  3. then '20000'  
  4. when SALARY <=40000 and SALARY <=30000   
  5. then '10000' else '7500'  
  6. end  
  7.   
  8. from cts.employee  
METTL syntax if we use syntax 2
  1. select name, case  
  2. when SALARY >40000   
  3. then '20000'  
  4. when SALARY <=40000 and SALARY <=30000   
  5. then '10000' else '7500'  
  6. end  
  7.   
  8. “Tax_amount” from cts.employee  
6. Using Local Variables
  1. use dn016_346238  
Find odd or even
  1. declare @num int; set @num=6;  
  2.   
  3. if (@num % 2 = 0)  
  4.   
  5. print 'Number is even';  
  6.   
  7. else  
  8.   
  9. print 'Number is odd';  
Calculate tax using basic pay
  1. declare @basicPay money; declare @tax money;  
  2.   
  3. set @basicPay=600000;  
  4.   
  5. if (@basicPay > 500000)  
  6.   
  7. set @tax= @basicPay * 20 /100; else if (@basicPay >300000)  
  8.   
  9. set @tax= @basicPay * 10 / 100; else if (@basicPay >200000)  
  10.   
  11. set @tax = @basicPay * 5/100;  
  12.   
  13. else  
  14.   
  15. set @tax = 0;  
  16.   
  17. print ' The tax amount for the given '+cast(@basicPay as nvarchar(50))+ ' is ' + convert(nvarchar(50),@tax);  
Selecting employee details whose ID is greater than 500 using local variables
  1. use dn016_346238  
  2.   
  3. declare @id int;  
  4.   
  5. set @id=500;  
  6.   
  7. select * from cts.Employee where EmpID > @id;  
7. Cursors

Note: Attributes with "Identity property" cannot be changed normally. This applies to cursors also.

Syntax for declaring cursors:

declare cursor_name cursor for

<TSQL Statements>

[For update (of column_name)]

Note: The contents within [] and () are optional.

Steps involved in cursors:

We need to follow 5 steps on dealing with cursors: 
  1. Declare Cursor
  2. Open Cursor
  3. Fetch from cursor
  4. Close Cursor
  5. Deallocate Cursor

We have 6 priority levels

  1. FIRST
  2. LAST
  3. NEXT
  4. PRIOR
  5. ABSOLUTE
  6. RELATIVE

Declaring cursors as a local variable

  1. DECLARE @varcursor CURSOR SET @varcursor = CURSOR FOR select * from cts.employee OPEN @varcursor  
  2.   
  3. FETCH @varcursor  
  4.   
  5. WHILE( @@FETCH_STATUS = 0)  
  6.   
  7. BEGIN  
  8.   
  9. FETCH @varcursor  
  10.   
  11. END  
  12.   
  13. CLOSE @varcursor  
  14.   
  15. DEALLOCATE @varcursor   
  • Type 1: Forward only Cursor

    Note: Update made using cursor, affects the cursor as well as in the main table.

    We cannot use the priority levels like FIRST, LAST, PRIOR, ABSOLUTE, RELATIVE. We can use only NEXT prioriy level in this type.

    Step 1: Declaring cursor

    Declare myCursor cursor myCursor is the cursor name. It is not a variable. By default, any cursor is a Forward-only cursor for:
    1. select * from cts.employee  
    Step 2: Opening cursor

    Cursor will be created only when step 2 is executed,
    1. Open myCursor  
    Step 3: Fetch records using my cursor.
    1. FETCH next FROM myCursor  
    Note: We can use "NEXT" only and we can't use other

    Intermediate step: update records
    1. update cts.employee set name= 'Lenaprasanna',address ='Andhrapradesh' where current of myCursor  
    Normal update query
    1. update cts.employee set name= 'Sriharsha',address ='Hyderabad' where id=346538  

    Step 4: Closing cursor.

    1. close myCursor  
    Step 5: Cursor Deallocation.
    1. deallocate myCursor  
    Forward Only Scroll

    We can use all the keywords like FIRST ,LAST, NEXT, PRIOR, ABSOLUTE, RELATIVE.

    Declaration of Forward only Scroll
    1. declare myCursor cursor scroll for  
    2.   
    3. select * from cts.employee  
    4.   
    5. open myCursor  
    Note: All the priority levels like FIRST, LAST, NEXT, PRIOR, ABSOLUTE, RELATIVE are available in the forward only scroll type cursor: 

    • FETCH first FROM myCursor - Retrieves first record.
    • FETCH last FROM myCursor- Retrieves last record.
    • FETCH next FROM myCursor -Retrieves next available record.
    • FETCH prior FROM myCursor - Moves one step back.

    ABSOLUTE - Moves to absolute position we specify

    • FETCH absolute -2 FROM myCursor - It moves 2 entries before.
    • FETCH absolute 3 FROM myCursor - It will move 2 steps ahead.
    • FETCH relative 3 FROM myCursor - Moves to the specified 3rd position from the current position.

    Updating records using cursor

    1. update cts.employee set name= 'Sivaprasad',address ='hyderabad' where current of myCursor  
    Displaying the updated table
    1. select * from cts.employee  
    Using forward only cursor

    To add a constraint that a particular column alone to be updated, we can redefine the declaration including “for update” keyword followed by the column names which we need to modify.

    Step 1:
    1. declare myCursor cursor for  
    2.   
    3. select * from cts.employee IT IS MANDATORY.. Since cursor is like pointer, it should be allowed to point to a particular table. Here it points to cts.employee table  
    For update of name, address this field is optional. Now, updating any fields other than name and address is not valid with this cursor.

    Step 2:
    1. open myCursor  
    Step 3:
    1. update cts.employee set name= 'SriHarshaChilla',address ='Andhrapradesh' where current of myCursor  
    Step 4 and Step 5 
    1. close myCursor   
    2. deallocate myCursor   
  • Type 2: Dynamic Cursor

    Note: Changes made using dynamic cursor affects the main table as well as the table created by the cursor in tempdb.

    Hence, Dynamic cursors are called "SENSITIVE CURSORS"

    Step 1: Cursor Declaration
    1. declare myCursor cursor dynamic for  
    2.   
    3. select * from cts.employee  
    Step 2: Opening cursor
    1. open myCursor  
    Step 3: Fetching using cursor

    Note: Except ABSOLUTE, the others like NEXT, PRIOR, FIRST, LAST, DYNAMIC can be used,
    1. FETCH next from myCursor   
    2. FETCH first FROM myCursor   
    3. FETCH last FROM myCursor   
    4. FETCH next FROM myCursor   
    5. FETCH prior FROM myCursor   
    6. FETCH relative 3 FROM myCursor  
    Updating entries using Dynamic Cursor
    1. update cts.employee set name='Lenaprasanna' where current of   
    2. myCursor update cts.employee set id=346200 where name = 'S.Kishore'  
    Step 4: Closing and freeing the resource .
    1. close myCursor  
    2. deallocate myCursor  
  • Type 3: Static Cursor

    Note: Changes made in the main table does not affect the cursor. Because this cursor retrieves the data from tempdb which initializes data from the table when the cursor is created and it never checks the current updated main table again even if the changes are made. Note that, this cursor is READ ONLY. Hence, Static cursors are called "INSENSITIVE CURSORS AND NOT UPDATABLE".

    FOR UPDATE is not valid for static cursors, so don't give for update keyword in the cursor declaration. All the priority levels can be used.

    Step1: Cursor Declaration.
    1. declare myCursor cursor static for  
    2.   
    3. select * from cts.employee  
    Step 2: Opening cursor.
    1. open myCursor  
    Step 3: Fetching using cursor.

    All priority levels like ABSOLUTE, NEXT, PRIOR, FIRST, LAST, DYNAMIC can be used.
    1. FETCH next from myCursor   
    2. FETCH first FROM myCursor   
    3. FETCH last FROM myCursor   
    4. FETCH next FROM myCursor   
    5. FETCH prior FROM myCursor  
    6.   
    7. FETCH absolute -2 FROM myCursor   
    8. FETCH absolute 3 FROM myCursor   
    9. FETCH relative 3 FROM myCursor  
    Updating entries using static cursor
    1. update cts.employee set name='Lenaprasanna' where current of myCursor this statement is INVALID as the cursor is READONLY. No update can be done with this cursor.  
    Step 4: Closing and deallocating the cursor close myCursor,
    1. deallocate myCursor  
  • Type 4: KEYSET DRIVEN CURSORS

    Note: These cursors are used to update any key column.

    Here, when we update any entry using cursor, unlike Dynamic cursor, this cursor will make the current pointing entry to be null and make the updated entry in the end of the base table.

    If we update using normal update query, the corresponding values pointed by the cursor becomes null and no actual entry will be added in the end of the table.

    Step 1: Cursor declaration
    1. declare myCursor cursor keyset for  
    2.   
    3. select * from cts.employee  
    Step 2: Opening cursor
    1. open myCursor  
    Step 3: Fetching using cursor.

    All priority levels like ABSOLUTE, NEXT, PRIOR, FIRST, LAST, DYNAMIC can be used. FETCH next from myCursor
    1. FETCH first FROM myCursor   
    2. FETCH last FROM myCursor   
    3. FETCH next FROM myCursor   
    4. FETCH prior FROM myCursor   
    5. FETCH absolute -2 FROM myCursor   
    6. FETCH absolute 3 FROM myCursor   
    7. FETCH relative 3 FROM myCursor  
    Updating entries using keyset Cursor
    1. update cts.employee set id=346534 where current of myCursor  
    Step 4: Closing and freeing the resource close myCursor.
    1. deallocate myCursor  

8. Creating user definded datatypes

  1. create type udtDescription from nvarchar(max);  
  2.   
  3. create table cts.UserDescription  
  4.   
  5. (  
  6.   
  7. UserName udtDescription, Passwd nvarchar(50)  
  8.   
  9. );  
  10.   
  11.   
  12. insert into cts.UserDescription values ('Lenaprasanna','12345678'); insert into cts.UserDescription values ('kishore','32445568'); select * from cts.UserDescription;   
9. Stored Procedures

Note: There are three types of stored procedures namely, 
  • System Stored procedures:
  • User defined Stored procedures
  • Extended Stored procedures

System Stored Procedures

Many administrative activities in the SQL Server are performed through a special kind of procedure known as a system stored procedure. For example, sys.sp_changedbowner is a system stored procedure. System stored procedures are physically stored in the Resource database and have the sp_ prefix. System stored procedures logically appear in the sys schema of every system- and user-defined database. In SQL Server 2008, GRANT, DENY, and REVOKE permissions can be applied to system stored procedures.

Some of the system Stored Procedures are sp_rename, sp_execute, etc.

Example

  • Changing the name of the column in the table using sp_rename stored procedure:
    1. EXEC sp_rename   
    2.   
    3. ‘dbo.Orders.Details’, Fully Qualified column name   
    4. ‘OrderDetails’, New column name   
    5. ‘COLUMN’ Object Type  
  • Changing the name of the table using sp_rename stored procedure:
    1. EXEC sp_rename   
    2.   
    3. ‘dbo.Orders’, Existing Table including Schema Name   
    4. ‘OrderDetails’, New column name   
    5.   
    6. ‘OBJECT’ Object Type  
  • Changing the index of the table using sp_rename stored procedure:
    1. EXEC sp_rename   
    2. 'dbo.Orders.PK__Orders__C3905BAF6A30C649', Existing Table including Schema Name   
    3.   
    4. 'IDX_PK_C_OrderID', Fully Qualified index name   
    5. ‘INDEX’ Object Type   
    Note: Procedure can be executed using EXEC command followed by the procedure name and the arguments (optional)

User Defined Stored Procedures

  • Simple procedure without arguments displays customer details,
    1. create procedure procCustomerList As  
    2.   
    3. Begin  
    4.   
    5. select id,address from cts.employee select empid,dob from cts.employees  
    6.   
    7. end  
    Procedure call
    1. exec procCustomerList  
  • Simple procedure with argument,

    Displays customer details for the given id.
    1. create procedure procCustomerList @id int,@empname varchar(30) As  
    2.   
    3. Begin  
    4.   
    5. select id,address from cts.employee where id=@id  
    6.   
    7. select empid,dob from cts.employees where empname=@empname  
    8.   
    9. end  
    procedure call
    1. exec procCustomerList 346238,'lenaprasanna'  
  • Procedure to find fibonacci of n numbers without recursion,
    1. create procedure fibonacci @num int as  
    2.   
    3. begin  
    4.   
    5. declare @fib1 int,@fib2 int,@fib int,@temp int set @fib1 = 0  
    6.   
    7. set @fib2 = 1 set @temp = 2 print @fib1 print @fib2  
    8.   
    9. while(@temp < @num) begin  
    10.   
    11. set @fib=@fib1+@fib2 print @fib  
    12.   
    13. set @fib1=@fib2 set @fib2=@fib  
    14.   
    15. set @temp = @temp + 1  
    16.   
    17. end  
    18.   
    19. end  
    Function call
    1. exec fibonacci 5  
  • Nested procedures
    1. procedure to find fibonacci with recursion  
    2.   
    3. alter procedure recursiveFibonacci @fib1 bigint, @fib2 bigint,@num bigint as  
    4.   
    5. begin  
    6.   
    7. if @num = 0 return  
    8.   
    9. begin  
    10.   
    11. if @fib1 = 0 print 0  
    12.   
    13. declare @temp bigint  
    14.   
    15. set @temp = @fib1 + @fib2 if @num <> 1  
    16.   
    17. print @temp set @num = @num - 1  
    18.   
    19. exec recursiveFibonacci @fib2,@temp,@num  
    20.   
    21. end  
    22.   
    23. end  
    Function call
    1. exec recursiveFibonacci 0,1,10  
    Note: If a procedure contains an output variable, use the steps given in the comments:
    1. declare @var int  
    2. exec recursiveFibonacci 0,1,8,@var output print @var  
  • HANDS ON Exercises

    Question 1: Procedure to insert entries when CustId and CompName is not NULL, else display respective error message.
    1. create procedure procCustIDCompNameVerify @custID int,@compName varchar(30),@contName varchar(30),@contTitle varchar(30),@address varbinary(max),@city varchar(30),@region varchar(30),@postalcode int,@country varchar(30),@phone bigint, @fax varchar(30),@NoOfOrder int as  
    2.   
    3. begin  
    4.   
    5. declare @tempCustId varchar(30)  
    6.   
    7. set @tempCustId = cast(@custID as varchar(30))  
    8.   
    9. if @tempCustId is not NULL and @compName is not NULL begin  
    10.   
    11. insert into Sales.Customers values(@tempcustID,@compName,@contName,@contTitle,@address,@city,@region,@postalcode,@c ountry,@phone,@fax,@nooforder)  
    12.   
    13. end  
    14.   
    15. if @tempcustId is NULL begin  
    16.   
    17. print 'ALERT:Cant insert. Customer ID is null'  
    18.   
    19. end  
    20.   
    21. if @compName is NULL  
    22.   
    23. begin  
    24.   
    25. print 'ALERT:Cant insert. Company name is null'  
    26.   
    27. end end  
    Function call
    1. exec procCustIDCompNameVerify 1,'cognizant','Lenaprasanna','ProgrammerAnalystTrainee',NULL,'Tenali','Andhrapradesh',531402,'India', 8144386636,'044-4120221',5  
    2.   
    3. select * from Sales.Customers  
    Question 2: Procedure that takes two parameters namely type and pattern as input and display first name or last name based upon the type provided as input.
    1. create procedure procPatternMatch @type nvarchar(30),@pattern nvarchar(30) as  
    2.   
    3. begin  
    4.   
    5. if( @type = 'FirstName' ) begin  
    6.   
    7. select * from HumanResource.Employees where FirstName like @pattern  
    8.   
    9. end  
    10.   
    11. else if (@type = 'LastName') begin  
    12.   
    13. select * from HumanResource.Employees where LastName like @pattern  
    14.   
    15. end  
    16.   
    17. end  
    Procedure call
    1. exec procPatternMatch 'FirstName','k%'   
    2. exec procPatternMatch 'LastName','_a_e_n'  

10. Functions

Type 1: Scalar valued function.

  • Case 1: Create a function without arguments.

    This function prints 124 always,

    create function display()
    returns int as
    1. begin  
    2.   
    3. declare @num int set @num=124 return @num  
    4.   
    5. end  
    Function call
    1. select dbo.display() or  
    2.   
    3. print dbo.display()  
    Function call with alias name
    1. select dbo.display() "My value"  
    2. droping a function  
    Drop function
    1. dbo.display  
  • Case 2: Create a function with single argument.

    This function concatenates '0' with the id passed to it.
    1. create function getdetails(@cust int) returns nvarchar(20)  
    2.   
    3. as begin  
    4.   
    5. return '0'+cast(@cust as nvarchar(20))  
    6.   
    7. end  
    Function call

    1. Direct call
      1. print dbo.getdetails(678)  
    2. Call from table entry without alias name.
      1. select name,id,dbo.getdetails(id) from cts.employee  
    3. Call from table entry with alias name
      1. select name,id,dbo.getdetails(id) "Call from function" from cts.employee  
  • Case 3: Create a function with two arguments.

    This function concatenates the employee name with the id passed to it,
    1. create function appendName(@id as int, @cus_name as varchar(50)) returns nvarchar(30)  
    2. as begin  
    3. return@ cus_name + convert(nvarchar(30), @id)  
    4. end  
    Function call

    1. With default arguments,
      1. select dbo.appendName(123,'lena')  
    2. For all table entries without alias name.
      1. select name,id,dbo.appendName(id,name) from cts.employee  
    3. For all table entries with alias name.
      1. select name,id,dbo.appendName(id,name) "Appended Name" from cts.employee  

    Exercise 1

    Calculate tax with salary from table:

    1. create  
    2. function calculate_tax(@sal as money) returns money  
    3. as begin  
    4. declare@ tax money set@ tax = 0  
    5. if@ sal > 50000 begin  
    6. set@ tax = @sal * 20 / 100 20 % tax of the salary  
    7. end  
    8. else if@ sal > 40000 begin  
    9. set@ tax = @sal * 10 / 100 10 % tax of the salary  
    10. end  
    11. else if@ sal > 30000 begin  
    12. set@ tax = @sal * 5 / 100 5 % tax of the salary  
    13. end  
    14. else begin  
    15. set@ tax = 0 no tax  
    16. if sal <= 30000  
    17. end  
    18. return@ tax  
    19. end  
    Function call 
    1. select name,SALARY,dbo.calculate_tax(SALARY) "Estimated Tax Amount" from cts.employee  
    Altering a function
    1. alter  
    2. function calculate_tax(@sal decimal(8, 2)) returns nvarchar(30)  
    3. as begin  
    4. declare@ tax decimal(8, 2) set@ tax = 0  
    5. if@ sal > 50000 begin  
    6. set@ tax = @sal * 20 / 100 20 % tax of the salary  
    7. end  
    8. else if@ sal > 40000 begin  
    9. set@ tax = @sal * 10 / 100 10 % tax of the salary  
    10. end  
    11. else if@ sal > 30000 begin  
    12. set@ tax = @sal * 5 / 100 5 % tax of the salary  
    13. end  
    14. else begin  
    15. set@ tax = 0 no tax  
    16. if sal <= 30000  
    17. end  
    18. return cast(@tax as nvarchar(20))  
    19. end  
    Function call
    1. select name,SALARY,dbo.calculate_tax(SALARY) "Estimated Tax Amount" from cts.employee   

Type 2: Inline table valued function

  • Case 1: Without arguments.
    1. displays id, name and address of all employees in the table create  
    2. function getEntryOfEmployee()  
    3. returns table as  
    4. return (select id, name, address from cts.employee where salary > 45000)  
  • Case 2: With arguments.
    1. create function getEntryOfEmployeeWithArg(@salCondition as money) returns table  
    2. as return  
    3. (  
    4.   
    5. select id,name,address from cts.employee where salary > @salCondition  
    6.   
    7. )  
    Function call
    1. select * from dbo.getEntryOfEmployee()  
    2. select * from dbo.getEntryOfEmployeeWithArg(346238)   
    3. select * from dbo.getEntryOfEmployeeWithArg(55000)  

Type 3: Multi statement table valued function

  1. alter  
  2. function addOrDisplayEntries(@ID int) returns@ employeelist table(id int, name varchar(50), salary money) as  
  3. begin  
  4. if@ ID is NULL begin  
  5. insert@ employeelist  
  6. select id, name, salary from cts.employee  
  7. end  
  8. else begin  
  9. insert into@ employeelist  
  10. select id, name, salary from cts.employee where id = @ID  
  11. end  
  12. return end  
Function call
  1. select * from dbo.addOrDisplayEntries(3455)  
  2. select * from dbo.addOrDisplayEntries (346238)  
Or

Call using a local
  1. variable declare @id int  
  2. set @id=346238  
  3. select * from dbo.addOrDisplayEntries (@id)  
Dropping a function
  1. drop function calculate_tax  
Note: While dropping a function, no need to include paranthesis () at the end of the function name.

Hands on exercises

Question 1: Function that returns the number of orders placed by the customer function definition.
  1. create  
  2. function NumberOfOrders(@cusId int)  
  3. returns nvarchar(30)  
  4. as begin  
  5. declare@ order int  
  6. From the table, get the count of the no.of orders placed by the customer select@ order = count(orderno) from cts.orderid where c_id = @cusId  
  7. return convert(nvarchar(20), @order) provides the no.of orders placed by the customer  
  8. end  
  9. function call  
  10. print dbo.NumberOfOrders(1000)  
  11. "No. Of Orders"  
Question 2: Function that takes ‘LongName’ or ‘ShortName’ as input and prints LastName of the employee if ‘ShortName’ is passed as an argument. Else this function will return the FirstName along with the LastName.

Function definition 
  1. create  
  2. function displayNames(@name nvarchar(9))  
  3. returns@ EmpTable table(ID int, Name nvarchar(50)) as  
  4. begin  
  5. if (@name = 'ShortName') begin  
  6. Initialize@ EmpTable with LastName  
  7. insert@ EmpTable select empid, LastName from cts.employees end  
  8. else if (@name = 'LongName') begin  
  9. Initialize@ EmpTable FirstName LastName  
  10. insert@ EmpTable select empid, (FirstName + ' ' + LastName) from cts.employees end  
  11. Provides the value of@ EmpTable as the result  
  12. return  
  13. end  
Function call
  1. select * from dbo.displayNames('ShortName')   
  2. select * from dbo.displayNames('LongName')  
Question 3: Functions that takes region as input and prints all the users in the particular region function definition.
  1. create  
  2. function displayOthers(@region as nvarchar(50))  
  3. returns table  
  4. as  
  5. return (Select all the users who all are currently in the given region select name from cts.employee where address = @region)  
Function call
  1. select * from displayOthers('Hyderabad')  
11. Exception handling in Store Procedure

Factorial example 
  • Case 1: Factorial using procedure - with Input parameter and without Output parameter.
    1. create procedure procFactorial@ num int  
    2. as  
    3. begin  
    4. try  
    5. declare@ count int, @result int set@ count = 1  
    6. set@ result = 1  
    7. while@ count <= @num begin  
    8. set@ result = @result * @count set@ count = @count + 1  
    9. end  
    10. select@ result "Factorial value"  
    11. end  
    12. try begin  
    13. catch  
    14. select ERROR_NUMBER(), ERROR_MESSAGE() end  
    15. catch  
    Procedure call
    1. exec procFactorial 5  
  • Case 2: Factorial using procedure - with Input and Output parameter.
    1. alter procedure procFactWithOutputParameter   
    2. @num int,@result numeric(38,0) output  
    3.   
    4. as begin try  
    5.   
    6. declare @count int set @result = 1 set @count = 1  
    7.   
    8. if incase we provide 0 as input, It should obviously return 1 SINCE 0! = 1 while @count <= @num  
    9.   
    10. begin  
    11.   
    12. set @result = @result * cast(@count as numeric(38,0))  
    13.   
    14. set @count = @count + 1  
    15.   
    16. end  
    17.   
    18. end try begin catch  
    19.   
    20. select ERROR_NUMBER() "ERROR NUMBER",ERROR_MESSAGE()"ERROR MESSAGE" end catch  
    Procedure call
    1. declare @inputvar int,@outputvar numeric(38,0) set @inputvar = 30  
    2. set @outputvar = 1  
    3. exec procFactWithOutputParameter @inputvar,@outputvar output select @inputvar "Input value",@outputvar "Factorial value"  
    Or
    1. print cast(@inputvar as varchar(4)) + '!=' + cast(@outputvar as varchar(4))  
  • Case 3: Function to find factorial - without recursion alter function funcFactorial (@num int)

    returns numeric(38,0)

    Note: Reason for using numeric(38,0) because if we enter any value more than 13! using int type, it will show arithmetic overflow error,
    1. as begin  
    2. declare@ count int  
    3. declare@ result numeric(38, 0) set@ count = 1  
    4. set@ result = 1  
    5. if incase we provide 0 as input, It should obviously  
    6. return 1  
    7. while@ count <= @num  
    8. begin  
    9. set@ result = @result * cast(@count as numeric(38, 0)) multiplication between int and numeric type.so typecast int to numeric as the result is of type numeric.  
    10. set@ count = @count + 1  
    11. end  
    12. return@ result  
    13. end  
    Function call
    1. select dbo.funcFactorial(30) "Factorial value"  
  • Case 4: Function to find factorial - with recursion.
    1. alter  
    2. function funcRecursiveFact(@num numeric(38, 0)) returns numeric(38, 0)  
    3. as begin  
    4. declare@ prod numeric(38, 0) if@ num <= 1  
    5. return 1  
    6. else set@ prod = cast(@num as numeric(38, 0)) * dbo.funcRecursiveFact(@num - 1) return@ prod  
    7. end  
    Function value
    1. select dbo.funcRecursiveFact(30) "Factorial value"  
  • Case 5: Creating a procedure in another procedure.
    1. create procedure procInProcFactorial@ inputval int, @result numeric(38, 0) output as  
    2. begin  
    3. declare@ init int set@ init = 1  
    4. exec procFactWithOutputParameter@ inputval, @result output  
    5. return@ result  
    6. end  
    7. declare@  
    8. var int  
    9. exec procInProcFactorial 5, @  
    10. var output select@  
    11. var "Factorial value"  
  • Case 6: Creating a procedure in another procedure.
    1. create procedure procfactorialwithoutput@ num int, @result numeric(38, 0) output as  
    2. begin  
    3. try  
    4. declare@ prod numeric(38, 0) set@ prod = 1  
    5. while (@num > 1) begin  
    6. set@ prod = @prod * @num set@ num = @num - 1  
    7. end  
    8. set@ result = @prod end  
    9. try  
    10. begin  
    11. catch  
    12. select ERROR_NUMBER(), ERROR_MESSAGE() end  
    13. catch  
    Second procedure
    1. alter proc procinprocfact@ max int, @result numeric(38, 0) output as  
    2. begin  
    3. declare@ init int set@ init = 0  
    4. while (@init <= @max) begin  
    5. exec procfactorialwithoutput@ init, @result output  
    6. print cast(@init as varchar(20)) + '!=' + cast(@result as varchar(max)) set@ init = @init + 1  
    7. end  
    8. end  
    Function call
  1. declare @var numeric(38,0)  
  2. exec procinprocfact 30,@var output  

12. Triggers

Type 1: DML triggers

  1. AFTER triggers:

    Concept: When someone tries to modify the entries of the table once the trigger is created, the newly entered entry will be reflected in the basetable.

    In case the data is inserted, "Inserted table" will hold the newly inserted value. In case the data is deleted, the old values that is currently being deleted will be moved to "Deleted table" and the same will be removed from the base table. In case the data is updated, the newly entered value is updated into the base table and the old value will be stored in the magic table called "Deleted" and the new value is stored in another magic table called "Inserted".

    We can use any of the entries in these 2 magic tables and we insert in the Auditing table.

    To drop the table named "AuditingTable", if exists prior to create it.
    1. if object_id('AuditingTable', 'u') is not null  
    2. drop table AuditingTable  
    3. create table AuditingTable(EmpID int, Trigger_msg nvarchar(max), Modified_date date  
    4.     if u want to print date and time, use the datatype "datetime")  
    To drop the existing trigger
    1. if OBJECT_ID('EmployeeTrigger','TR') is not null drop trigger EmployeeTrigger  
    Example 1: Creating trigger to an existing table called cts.employee.
    1. create trigger EmployeeTrigger  
    2.   
    3. on cts.employee after insert  
    4.   
    5. as begin  
    6.   
    7. declare @id int  
    8.   
    9. "Inserted" table is a magical table select @id=i.id from inserted i  
    10.   
    11.   
    12. -if u want to insert date and time together, use "sysdatetime()" insert into AuditingTable (EmpID,Trigger_Msg,Modified_date) values (@id,'New record inserted to cts.employee table',getDate())  
    13.   
    14. end  
    Trigger is called automatically when the data is inserted
    1. insert into cts.employee  
    2. values(4557,'Lenaprasanna','Andhrapradesh','[email protected]',9876544334,25000)  
    To view the inserted data
    1. select * from AuditingTable  
    Example 2: Create a single trigger that responds to update and delete.
    1. create trigger employeeUpdateDeleteTrigger on cts.employee  
    2. after update, delete as  
    3. begin  
    4. declare@ id int "Inserted"  
    5. table is a magical table select@ id = i.id from inserted i  
    6. insert into AuditingTable(EmpID, Trigger_Msg, Modified_date) values(@id, 'A record is modified in cts.employee table', getDate())  
    7. end  
    To call the trigger automatically when the following 2 statements are executed
    1. delete from cts.employee where id=4557  
    2. update cts.employee set name='chanakya' where id=346234  
    To view the deleted and updated data
    1. select * from AuditingTable  
    Example 3: Create a single trigger that responds to insert, update and delete.
    1. create trigger employeeInsUpdDelTrigger  
    2. on cts.employee  
    3. after insert, update, delete as  
    4. begin  
    5. declare@ id int "Inserted"  
    6. table is a magical table select@ id = i.id from inserted i "deleted"  
    7. table is an another magical table select@ id = i.id from deleted i  
    8. insert into AuditingTable(EmpID, Trigger_Msg, Modified_date)  
    9. values(@id, 'Insert,Delete or update action is perfomed in the table', getDate())  
    10. end  
    To call the trigger automatically when any of the following 3 statements are executed alone or in combinations
    1. insert into cts.employee values(4557, 'Lenaprasanna', 'Andhrapradesh', '[email protected]', 9876544334, 25000) delete from cts.employee where id = 4557  
    2. update cts.employee set name = 'chanakya'  
    3. where id = 346234  
    Note 1:

    Updating a row happens in two ways: Firstly, it will delete the corresponding entry from the basetable and put it into the "deleted" table (magic table) [Which means, deleted table will hold the old values] and second, the provided new entry is entered into the base table again, which will be reflected in the "inserted" magic table [which means, the "inserted table" will hold only the new values].

    The order at which the inserted and deleted tables are called in the trigger has an influence over the Auditing table.

    In the above example, we used the value of id from "inserted" table and passed the result on to a local variable called @id

    And in the next statement, we have over written the value of id from "deleted" table. which means now the variable @id will hold the old value.

    If the order of these 2 statements are interchanged, the @id value will hold the new value.

    Note 2:

    If we use the entry of "deleted table" for the trigger that is called by an insert action, it will return NULL.

    To view the Auditing table data
    1. select * from AuditingTable  
    To view the base table data
    1. select * from cts.employee  
  2. INSTEAD of Trigger

    Concept: When some one tries to modify the entries of the table once the trigger is created. The newly entered entries will not be reflected in the basetable but instead the two magical tables called "inserted" and "deleted" will hold the values as discussed in the AFTER TRIGGER.

    Note: Instead of trigger will have HIGH PRIORITY than AFTER trigger.

    Example 1:
    1. create trigger InsteadOfemployeeInsUpdDelTrigger on cts.employees  
    2. Instead Of insert, update, delete as  
    3. begin  
    4. declare@ id int "Inserted"  
    5. table is a magical table / select@ id = i.empid from inserted i "deleted"  
    6. table is an another magical table / select@ id = i.empid from deleted i  
    7. insert into AuditingTable(EmpID, Trigger_Msg, Modified_date) values(@id, 'Alert: some body tried to Modify it', getDate())  
    8. end  
    Calling InsteadOf trigger
    1. update cts.employees set empname='Lenaprasanna' where empid=346238  
    To view the Auditing table
    1. data select * from AuditingTable  
    To view the basetable data
    1. select * from cts.employees  

Syntax for Disabling / Enabling triggers

  • Case 1: DDL triggers

    disable trigger DDL_Trigger_Name on database

    enable trigger DDL_Trigger_Name on database

  • Case 2: DML triggers

    alter table tablename disable trigger DML_trigger_Name

Trigger hands on

Creating table consists of modified records

  1. create table auditing  
  2.   
  3. (  
  4.   
  5. id int,  
  6.   
  7. trigger_msg nvarchar(max), mod_date datetime  
  8.   
  9. )   
  1. Question 1:
    1. create trigger preventDeleteMoreThanOnceTrigger on Sales.OrderDetails instead of delete  
    2.   
    3. as begin  
    4.   
    5. declare @count int declare @id int  
    6.   
    7. select @count=count(*) from deleted print @count  
    8.   
    9. if @count<=1 begin  
    10.   
    11. select @id=i.OrderID from deleted i  
    12.   
    13. delete from Sales.OrderDetails where OrderID=@id  
    14.   
    15. end  
    16.   
    17. else  
    18.   
    19. print 'Alert: You attempted to delete more than one record at a time!!!!'  
    20.   
    21. end  
    Calling trigger
    1. delete from Sales.OrderDetails where productID=21  
  2. Question 2:
    1. create trigger preventModifyTrigger  
    2. on sales.customers  
    3. instead of update as  
    4. begin  
    5. declare@ id int;  
    6. declare@ comp varchar(30);  
    7. select@ id = i.CustomerId from inserted i;  
    8. select@ comp = i.CompanyName from inserted i;  
    9. if update(CustomerId) checks only the inserted table alone.It won’ t checks the basetable insert into auditing values(@id, 'The user is attempting      to modify  
    10.         customerId ',getdate()) else  
    11.         update Sales.Customers set CompanyName = @comp where CustomerId = @id end  
    Calling trigger
    1. update sales.customers set CompanyName = 'CTS'  
    2. where customerid = 61 update sales.customers set Customerid = 61 where companyName = 'CTS'  
    3. select * from sales.customers  
    4. select * from auditing  

Type 2: DDL TRIGGERS

Table structure

  1. create table ObjectTracking  
  2.   
  3. (  
  4.   
  5. TrackingID bigint identity(1,1),  
  6.   
  7. TrackingDate datetime NOT NULL default getdate(), DatabaseName varchar(500),  
  8.   
  9. EventType varchar(500), ObjectName varchar(500), ObjectType varchar(500), LoginName varchar(500), HostName varchar(500), SqlCommand nvarchar(max), ModifiedDateTime datetime  
  10.   
  11. )  
DDL trigger creation
  1. create trigger databaseTrigger on database  
  2.   
  3. for CREATE_PROCEDURE,CREATE_TABLE  
  4.   
  5. --Note: DDL_DATABASE_LEVEL_EVENTS - Defines all the DDL database events on CREATE_PROCEDURE,CREATE_TABLE,ALTER_PROCEDURE,DROP_PROCEDURE,ALTER_TABLE, DROP_TABLE,CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,CREATE_VIEW,ALTER_VIEW, DROP_VIEW,CREATE_INDEX,ALTER_INDEX,DROP_INDEX  

  7. as begin  
  8.   
  9. declare @data XML  
  10.   
  11. declare @HostName varchar(500) set @data = EVENTDATA()  
  12.   
  13. --assigning EVENTDATA() to a local variable @data.  
  14.   
  15. --Note: After the above assignment, calling EVENTDATA().value and @data.value will be same hereafter  
  16.   
  17. set @HostName = HOST_NAME()  
  18.   
  19. --HOST_NAME() retrieves the machine name. In this step, we are assigning HOST_NAME() to a local variable @HostName  
  20.   
  21. insert into ObjectTracking  
  22.   
  23. (  
  24.   
  25. DatabaseName,  
  26.   
  27. EventType,  
  28.   
  29. ObjectName,  
  30.   
  31. ObjectType,  
  32.   
  33. LoginName,  
  34.   
  35. HostName,  
  36.   
  37. SqlCommand, ModifiedDateTime  
  38.   
  39. )  
  40.   
  41. values(  
  42.   
  43. @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(500)'), --The first parameter in the @data.value has the entire path where the datas are available.[1] in the first parameter retrieves the current record. It returns in the XML format. The second parameter converts the XML type parameter to varchar(500)  
  44.   
  45. @data.value('(/EVENT_INSTANCE/EventType)[1]','varchar(500)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(500)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(500)'), @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(500)'), @HostName, @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(500)'), sysdatetime()  
  46.   
  47. )  
  48.   
  49. end  
Calling the trigger

The above trigger will be called when we create a new procedure or table and stores all the details in the ObjectTracking table.

Creating a new table to check the newly
  1. created trigger create table TriggerCheck(id int, name varchar(50))  
Displaying the ObjectTracking table entried
  1. select * from ObjectTracking  
13. Indexing in SQL

Note: This is the program given by our trainer Mr. Santhosh. This includes both the commands and its results
  1. use santhosh_dn016 go  
  2. CREATE TABLE employeeDetails(id INTEGER NOT NULL PRIMARY KEY, first_name VARCHAR(10), last_name VARCHAR(10), salary DECIMAL(10, 2), startDate DATETIME, region VARCHAR(10), city VARCHAR(20), managerid INTEGER);  
  3. GO  
  4. INSERT INTO employeeDetails VALUES(1, 'Jason', 'Martin', 5890, '2005-03-22', 'North', 'Vancouver', 3);  
  5. GO  
  6. --(1 rows affected)  
  7. INSERT INTO employeeDetails VALUES(2, 'Alison', 'Mathews', 4789, '2003-07-21', 'South', 'Utown', 4);  
  8. GO  
  9. --(1 rows affected)  
  10. INSERT INTO employeeDetails VALUES(3, 'James', 'Smith', 6678, '2001-12-01', 'North', 'Paris', 5);  
  11. GO  
  12. --(1 rows affected)  
  13. INSERT INTO employeeDetails VALUES(4, 'Celia', 'Rice', 5567, '2006-03-03', 'South', 'London', 6);  
  14. GO  
  15. --(1 rows affected)  
  16. INSERT INTO employeeDetails VALUES(5, 'Robert', 'Black', 4467, '2004-07-02', 'East', 'Newton', 7);  
  17. GO  
  18. --(1 rows affected)  
  19. INSERT INTO employeeDetails VALUES(6, 'Linda', 'Green', 6456, '2002-05-19', 'East', 'Calgary', 8);:  
  20. GO  
  21. --(1 rows affected)  
  22. INSERT INTO employeeDetails VALUES(7, 'David', 'Larry', 5345, '2008-03-18', 'West', 'New York', 9);  
  23. GO  
  24. --(1 rows affected)  
  25. INSERT INTO employeeDetails VALUES(8, 'James', 'Cat', 4234, '2007-07-17', 'West', 'Regina', 9);  
  26. GO  
  27. --(1 rows affected)  
  28. INSERT INTO employeeDetails VALUES(9, 'Joan', 'Act', 6123, '2001-04-16', 'North', 'Toronto', 10);  
  29. GO  
  30. --(1 rows affected)  
  31. select * from employeeDetails;  
  32. GO  
  33. --id first_name last_name salary start_Date region city managerid  
  34. -- -- -- -- -- - -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -  
  35. --1 Jason Martin 5890.00 2005 - 03 - 22 00: 00: 00.000 North Vancouver 3  
  36.     --2 Alison Mathews 4789.00 2003 - 07 - 21 00: 00: 00.000 South Utown 4  
  37.     --3 James Smith 6678.00 2001 - 12 - 01 00: 00: 00.000 North Paris 5  
  38.     --4 Celia Rice 5567.00 2006 - 03 - 03 00: 00: 00.000 South London 6  
  39.     --5 Robert Black 4467.00 2004 - 07 - 02 00: 00: 00.000 East Newton 7  
  40.     --6 Linda Green 6456.00 2002 - 05 - 19 00: 00: 00.000 East Calgary 8  
  41.     --7 David Larry 5345.00 2008 - 03 - 18 00: 00: 00.000 West New York 9  
  42.     --8 James Cat 4234.00 2007 - 07 - 17 00: 00: 00.000 West Regina 9  
  43.     --9 Joan Act 6123.00 2001 - 04 - 16 00: 00: 00.000 North Toronto 10  
  44.     --(9 rows affected)  
  45. CREATE INDEX i_empno ON employeeDetails(id);  
  46. CREATE NONCLUSTERED INDEX NI_ID_FirstName ON employeeDetails(ID, First_Name) GO  
  47. CREATE NONCLUSTERED INDEX NCI_FirstName ON employeeDetails(First_name ASC) GO  
  48. CREATE NONCLUSTERED INDEX NCI_FirstName ON employeeDetails(First_name ASC) WITH(DROP_EXISTING = ON)  
  49. GO: 54 | P a g e  
  50. CREATE NONCLUSTERED INDEX NCI_FirstName ON employeeDetails(ID, First_name DESC) WITH(DROP_EXISTING = ON)  
  51. GO  
  52. DROP INDEX employeeDetails.i_empno  
  53.     --The next statement is equivalent to the previous one--DROP INDEX i_empno ON employee  
  54. drop table employeeDetails;  
  55. GO  
The next statement is equivalent to the previous one DROP INDEX i_empno ON employee.
  1. drop table employeeDetails; GO  
14. TRANSACTION AND LOCKING

Notes to Remember: 
  • Transaction is a unit of work in the database.
  • Transaction commits the statements if there is no error in statements and becomes the permanent part of database.
  • Transaction is rolled back if there are errors in statements and all the data modifications are discarded.

Types of transaction:

  • Auto Commit:

    Each and every process is a transaction in SQL server.

  • Explicit:

    A transaction is explicitly started with 'BEGIN TRANSACTION' and ended with 'COMMIT' or 'ROLLBACK' .

  • Implicit:

    A transaction implicitly started and explicitly ended with ‘commit’ or ‘rollback’.

    In general, all the queries we have tried before like INSERT, UPDATE, DELETE are of type "AUTO COMMIT TRANSACTION"

    Note: @@trancount => Initially it will have the value 0.It is automatically incremented by 1 if it sees BEGIN TRANSACTION KEYWORD and decremented by 1 if it sees commit or rollback.

Example for explicit transaction

Note: Using ‘GO’ is one of the code standard and it is not mandatory always.

  1. begin transaction InsertTransaction GO  
  2. Insert into cts.employee values(346530, 'saisivaprasad', 'Hyderabad', '[email protected]', 9865778345, 55000)  
  3. commit transaction  
Example for rollback
  1. begin transaction InsTransRoll  
  2. Insert into cts.employee values(346530, 'Karthikeyan', 'Chennai', '[email protected]', 9865765545, 55000)  
Since the ID is a primary key and it is already present in the table, it will not insert the above mentioned value.

If we include this between begin transaction and rollback transaction, the SESSION itself will be completed and the changes won’t be reflected in the table.

rollback transaction

Example for Nested Transactions

Points to remember:

If any 1 of the inner transaction is rolled back, the outer transaction cannot be committed.

The outer transaction will be committed only when all the inner transactions are committed.
  1. create table TestTran  
  2. (  
  3.    cola int PRIMARY KEY, colb varchar(50)  
  4. )  
Note: Using "with" keyword, we can give a message to the clients/users. This is especially used for marking a transaction.

Example transaction with “with” keyword:

begin transaction OuterTransaction with mark go.

This statements sets @TRANCOUNT to 1
  1. print N 'Transaction count after BEGIN Outer Transaction is:' + cast(@@TRANCOUNT as nvarchar(10))  
  2. Insert into TestTran values(1, 'lena')  
  3. begin transaction InnerTransaction1  
This statements sets @TRANCOUNT to 2.
  1. go  
  2. print N 'Transaction count after BEGIN Inner Transaction1 is: ' + cast(@@TRANCOUNT as nvarchar(10))  
  3. Insert into TestTran values(2, 'kishore')  
  4. begin transaction InnerTransaction2 go  
This statements sets @TRANCOUNT to 3.

  1. print N 'Transaction count after BEGIN Inner Transaction2 is:' + cast(@@TRANCOUNT as nvarchar(10))  
  2. Insert into TestTran values(3, 'sivaprasad') commit transaction InnerTransaction2  
This statements sets @TRANCOUNT to 2.
  1. print N 'Transaction count after COMMIT Inner Transaction2 is:' + cast(@@TRANCOUNT as nvarchar(10))  
  2. go  
  3. commit transaction InnerTransaction1  
This statements sets @TRANCOUNT to 1.
  1. print N'Transaction count after COMMIT Inner Transaction1 is:' + cast(@@TRANCOUNT as nvarchar(10))  
  2. go  
  3. commit transaction OuterTransaction  
This statements sets @TRANCOUNT to 0.
  1. print N'Transaction count after COMMIT OuterTransaction is :' + cast(@@TRANCOUNT as nvarchar(10))  
  2. go  
Example for deadlock condition and non-deadlock condition:
  1. select * from cts.employee   
  2. select * from cts.employees   
  • Case a: Non - deadlock scenario:
    1. begin transaction DeadLock1  
    2.   
    3. update cts.employee set address = 'Madras' where id=34620  
    4.   
    5. --Syntax :waitfor delay 'time' will commit the transaction after the stipulated time  
    6.   
    7. given.  
    8.   
    9. waitfor delay '00:00:08' -- This will Commit the transaction after 8 seconds update cts.employees set empname='M Chanakya' where empid=346232  

    11. commit transaction Deadlock1  
    12.   
    13. begin transaction Deadlock2  
    14.   
    15. update cts.employee set address = 'Madras' where id=34620  
    16.   
    17. --Syntax :waitfor delay 'time' will commit the transaction after the stipulated time  
    18.   
    19. given.  
    20.   
    21. waitfor delay '00:00:15' -- This will Commit the transaction after 15 seconds update cts.employees set empname='M Chanakya' where empid=346232  
    22.   
    23. commit transaction Deadlock2 
  • Case b: Deadlock scenario:
    1. begin transaction DeadLock1  
    2.   
    3. update cts.employees set empname='M Chanakya' where empid=346232  
    4.   
    5. --Syntax :waitfor delay 'time' will commit the transaction after the stipuled time given. waitfor delay '00:00:08' -- This will Commit the transaction after 10 seconds  
    6.   
    7. update cts.employee set address = 'Madras' where id=34620 commit transaction Deadlock1  
    Output for Deadlock1:
    1. --(1 row(s) affected)  
    2.   
    3. --(1 row(s) affected)  
    4.   
    5. --Msg 1205, Level 13, State 51, Line 5  
    6.   
    7. --Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
    8.   
    9. begin transaction Deadlock2  
    10.   
    11. update cts.employee set address = 'Madras' where id=34620  
    12.   
    13. --Syntax :waitfor delay 'time' will commit the transaction after the stipuled time given. waitfor delay '00:00:10' -- This will Commit the transaction after 10 seconds  
    14.   
    15. update cts.employees set empname='M Chanakya' where empid=346232  
    16.   
    17. commit transaction Deadlock2  
    Output for Deadlock2:

    (1 row(s) affected)

    (1 row(s) affected)

    Note: If any error has occured during the process, just check @@trancount and ensure that it is 0 or not. If it is not 0, make it 0 by executing commit statement explicitly until the count becomes 0.

Avoiding Deadlock using exceptional handling

RETRY:

  1. begin transaction TransEvent begin try  
  2.   
  3. update cts.employee set address = 'Madras' where id=34620 --waitfor delay '00:00:15'  
  4.   
  5. update cts.employees set empname='M Chanakya' where empid=346232 end try  
  6.   
  7. begin catch  
  8.   
  9. if ERROR_NUMBER() = 1205 -- Note: This error number deals with deadlock. begin  
  10.   
  11. goto RETRY  
  12.   
  13. end end catch  
  14.   
  15. commit transaction TransEvent  
15. XML support in SQL Server

Points to remember: 
  • XML means “Extensible Markup Language”.
  • SQL supports XML.
  • In SQL, a data type called “xml” is available for XML documents.
  • In XML, all the elements should be properly nested.
  • XML is case – sensitive, so be careful while using tags.

XML Modes:

There are four XML modes namely,

  • FOR XML RAW --Columns in the table will become the attributes.
  • FOR XML PATH --Each entry will be enclosed in in-between <row> and </row> tag.
  • FOR XML AUTO --The <row> tag is replaced by the table name.
  • FOR XML_EXPLICIT

Note:

  • XML PATH (‘User_Defined_Name’) – Each entry will be enclosed inbetween the

    <User_Defined_Name> opening and closing tag. The ‘User_Defined_Name’ can be anything.

Note: On executing the following 3 statements, a link with the name <student/> will be shown. On clicking it, an XML page will be opened inside the SQL.

  1. declare @xmlVariable xml  
  2. set @xmlVariable='<student/>'; select @xmlvariable  
Create a Table with XML type
  1. create table employeeXML(ID xml, Name xml, Language_known xml, preferred_location xml, Qualification xml)  
Inserting data into the table 
  1. insert into employeeXML values(N '<id>346238</id>', N '<name>Lenaprasanna</name>', N '<language_known>Telugu</language_known>', N '<Preferred_Location>Hyderabad</Preferred_Location>', N '<Qualification>BTech. CSE. </Qualification>')  
  2. insert into employeeXML values(N '<id>346200</id>', N '<name>Kishore</name>', N '<language_known>Tamil</language_known>', N '<Preferred_Location>Chennai</Preferred_Location>', N '<Qualification>B.E. CSE. </Qualification>')  
  3. insert into employeeXML values(N '<id>346534</id>', N '<name>Chanakya</name>', N '<language_known>Telugu</language_known>', N '<Preferred_Location>Hyderabad</Preferred_Location>', N '<Qualification>BTech. CSE. </Qualification>')  
  4. insert into employeeXML values(N '<id>346538</id>', N '<name>Sri Harsha Chilla</name>', N '<language_known>Telugu</language_known>', N '<Preferred_Location>Hyderabad</Preferred_Location>', N '<Qualification>BTech. ECE. </Qualification>')  
  5. insert into employeeXML values(N '<id>346345</id>', N '<name>Sai Shiva Prasad</name>', N '<language_known>Telugu</language_known>', N '<Preferred_Location>Hyderabad</Preferred_Location>', N '<Qualification>BTech. ECE. </Qualification>')  
Using XML mode in XML table
  1. select * from employeeXML FOR XML RAW   
  2. select * from employeeXML FOR XML PATH  
  3. select * from employeeXML FOR XML PATH ('EmployeeTable')   
  4. select * from employeeXML FOR XML AUTO  
Using XML mode in normal table
  1. SELECT * from cts.Customer FOR XML RAW   
  2. SELECT * from cts.Customer FOR XML PATH   
  3. SELECT * from cts.Customer for XML AUTO  
Note: 
  • Creating XML document with many childs and sub-childs using XPATH may conserve lots of space.
  • Instead we can give them as an attributes. (ie.).

Instead of using like this

  1. <Employees>  
  2.     <Employee>  
  3.         <id>346238</id>  
  4.         <name>Lenaprasanna</name>  
  5.     </Employee>  
  6.     <Employee>  
  7.         <id>346200</id>  
  8.         <name>Kishore</name>  
  9.     </Employee>  
  10.     <Employee>  
  11.         <id>346534</id>  
  12.         <name>Chanakya</name>  
  13.     </Employee>  
  14. </Employees>  
We can give like this
  1. <Employees>  
  2.     <Employee id="346238" name="Lenaprasanna" />  
  3.     <Employee id="346200" name="Kishore" />  
  4.     <Employee id="346534" name="Chanakya" /> </Employees>  
XQuery methods in SQL: 
  1. value(): Retrieves the value that is present in the given path.
  2. exist(): Methods return 1 if data exists in the XML structure, else it will return 0.
  3. query(): Display all the elements with tags.
  4. node(): To access all the elements and access the values across the table.
  5. modify: To update the xml document content.

Example for using the above methods:

  • USING VALUE() METHOD:

    1. Getting the values of the first user
    1. declare@ xmlvar xml  
    2. set@ xmlvar = '<Employees> < Employee id = "346238"  
    3. name = "Lenaprasanna" / > < Employee id = "346200"  
    4. name = "Kishore" / > < Employee id = "346534"  
    5. name = "Chanakya" / > < Employee id = "346535"  
    6. name = "Lenaprasanna" / > < /Employees>'  
    7. select@ xmlVar.value('Employees[1]/Employee[1]/@name', 'varchar(20)') as "First Employee Name"  
    Output:

    First Employee Name
    Lenaprasanna

    2. Getting the id of the second user
    1. declare@ xmlvar xml  
    2. set@ xmlvar = '<Employees> < Employee id = "346238"  
    3. name = "Lenaprasanna" / > < Employee id = "346200"  
    4. name = "Kishore" / > < Employee id = "346534"  
    5. name = "Chanakya" / > < Employee id = "346535"  
    6. name = "Lenaprasanna" / > < /Employees>'  
    7. select@ xmlVar.value('Employees[1]/Employee[2]/@id', 'varchar(20)') as "Second Employee ID"  
    Output:

    Second Employee ID

    346200

  • USING EXIST() METHOD:

    1. Check whether the value exists or not:

    Syntax:

    select XMLColumn_Or_XmlVariable.exist('The full path')

    Case a: Check for existing record:
    1. declare@ xmlvar xml  
    2. set@ xmlvar = '<Employees> < Employee id = "346238"  
    3. name = "Lenaprasanna" / > < Employee id = "346200"  
    4. name = "Kishore" / > < Employee id = "346534"  
    5. name = "Chanakya" / > < Employee id = "346535"  
    6. name = "Lenaprasanna" / > < /Employees>'  
    7. select@ xmlVar.exist('/Employees/Employee[@id="346238"]') as "Is Exist?"  
    Output:

    Is Exist?
    -
    1

    Case b: Check for non-existing record:
    1. declare@ xmlvar xml  
    2. set@ xmlvar = '<Employees> < Employee id = "346238"  
    3. name = "Lenaprasanna" / > < Employee id = "346200"  
    4. name = "Kishore" / > < Employee id = "346534"  
    5. name = "Chanakya" / > < Employee id = "346535"  
    6. name = "Lenaprasanna" / > < /Employees>'  
    7. select@ xmlVar.exist('/Employees/Employee[@id="346538"]') as "Is Exist?"  
    Output:

    Is Exist?
    0

  • USING QUERY() METHOD:

    1. Retreives all the entries of Employees/Employee tag
    1. declare@ xmlvar xml  
    2. set@ xmlvar = '<Employees> < Employee id = "346238"  
    3. name = "Lenaprasanna" / > < Employee id = "346200"  
    4. name = "Kishore" / > < Employee id = "346534"  
    5. name = "Chanakya" / > < Employee id = "346535"  
    6. name = "Lenaprasanna" / > < /Employees>'  
    7. SELECT@ xmlVar.query('/Employees/Employee') as Result1  
    Output:

    Result1
    -
    <Employee id="346238" name="Lenaprasanna" /><Employee id="346200" name="Kishore" /><Employee id="346534" name="Chanakya" />

    2. Query that retrieves distinct name
    1. declare@ xmlvar xml  
    2. set@ xmlvar = '<Employees> < Employee id = "346238"  
    3. name = "Lenaprasanna" / > < Employee id = "346200"  
    4. name = "Kishore" / > < Employee id = "346534"  
    5. name = "Chanakya" / > < Employee id = "346535"  
    6. name = "Lenaprasanna" / > < /Employees>'  
    7. SELECT@ xmlvar.query('distinct-values(data(/Employees/Employee/@name))') as "Distinct names"  
    Output:

    Distinct names
    Lenaprasanna Kishore Chanakya

  • USING NODE() METHOD:

    1. Query that retreives all the id and name from the xml in a table form. declare @xmlvar xml
    1. set@ xmlvar = '<Employees> < Employee id = "346238"  
    2. name = "Lenaprasanna" / > < Employee id = "346200"  
    3. name = "Kishore" / > < Employee id = "346534"  
    4. name = "Chanakya" / > < Employee id = "346535"  
    5. name = "Lenaprasanna" / > < /Employees>'  
    6. select x.value('@id', 'int') as "EmployeeID", x.value('@name', 'varchar(30)') as "EmployeeName"  
    7. from@ xmlvar.nodes('/Employees/Employee') EmployeeXml(x)  
    Output:
    1. EmployeeID EmployeeName - 346238 Lenaprasanna  
    2. 346200 Kishore  
    3. 346534 Chanakya  
    4. 346535 Lenaprasanna  
    2. Retrieving the attributes of the parent tag and also of the current tag.
    1. declare @xmlvar xml  
    2.   
    3. set @xmlvar='<Employees> <Employee S.No="1">  
    4.   
    5. <Details id="346238" name="Lenaprasanna"/> </Employee>  
    6.   
    7. <Employee S.No="2">  
    8.   
    9. <Details id="346200" name="Kishore"/> </Employee>  
    10.   
    11. <Employee S.No="3">  
    12.   
    13. <Details id="346534" name="Chanakya"/> </Employee>  
    14.   
    15. <Employee S.No="4">  
    16.   
    17. <Details id="346535" name="Lenaprasanna"/> </Employee>  
    18.   
    19. </Employees>'  
    20.   
    21. select x.value('../@S.No','int') as "Serial Number",x.value('@id','int') as "EmployeeID", x.value('@name','varchar(30)') as "EmployeeName" from @xmlvar.nodes('/Employees/Employee/Details') EmployeeXml (x)  
    USING XML_EXPLICIT mode along with FOR XML:
    1. --Create table variables declare @Employee table   
    2.   
    3. (EmployeeID int NOT NULL, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, JobTitle varchar(50) NOT NULL)   
    4.   
    5. --    
    6.   
    7. declare @Language table (LanguageID int NOT NULL,   
    8.   
    9. LanguageName varchar(30) NOT NULL)   
    10.   
    11. --    
    12.   
    13. declare @EmployeeLanguage table (EmployeeID int NOT NULL, LanguageID int NOT NULL)   
    14.   
    15. --    
    16.   
    17. --  Populate table variables with sample data insert into @Language values (1, 'English')   
    18.   
    19. insert into @Language values (2, 'Spanish') insert into @Language values (3, 'French')   
    20.   
    21. insert into @Language values (4, 'Italian') insert into @Language values (5, 'Greek')   
    22.   
    23. --    
    24.   
    25. insert into @Employee values (101, 'Jane', 'Doe', 'Accountant - Level I') insert into @Employee values (102, 'John', 'Smith', 'Business Analyst')   
    26.   
    27. insert into @Employee values (103, 'Erin', 'Thopmson', 'Database Administrator')   
    28.   
    29. --    
    30.   
    31. insert into @EmployeeLanguage values (101, 1) insert into @EmployeeLanguage values (102, 1)   
    32.   
    33. insert into @EmployeeLanguage values (102, 2) insert into @EmployeeLanguage values (103, 3)   
    34.   
    35. insert into @EmployeeLanguage values (103, 4) insert into @EmployeeLanguage values (103, 5)   
    36.   
    37. --    
    38. --  Render as XML   
    39.   
    40. select 1 as Tag,

    41. NULL as Parent,  
    42.   
    43. NULL as 'Requisitions!1',  
    44.   
    45. NULL as 'Record!2!EmployeeID!element',  
    46.   
    47. NULL as 'Record!2!FirstName!element',  
    48.   
    49. NULL as 'Record!2!LastName!element', NULL as 'Record!2!JobTitle!cdata',  
    50.   
    51. NULL as 'Languages!3!language!element' union  
    52.   
    53. SELECT 2 as Tag, 1 as Parent, NULL, EmployeeID, FirstName, LastName, JobTitle, NULL  
    54.   
    55. FROM @Employee union all  
    56.   
    57. select 3 as Tag, 2 as Parent, NULL,  
    58.   
    59. e.EmployeeID,  
    60.   
    61. e.FirstName,  
    62.   
    63. e.LastName,  
    64.   
    65. e.JobTitle,  
    66.   
    67. l.LanguageName from @Employee e  
    68.   
    69. inner join @EmployeeLanguage el on e.EmployeeID = el.EmployeeID inner join @Language l on el.LanguageID = l.LanguageID  
    70.   
    71. order by 'Record!2!EmployeeID!element', 'Languages!3!language!element' for xml explicit  

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs