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

Create database dn016_346238

Using database

Syntax: use database_name use dn016_346238

Creating schema

Syntax: create schema schema_name create schema cts

creating table create table employee

(

id int primary key, name varchar(50), address nvarchar(max), mail_id nvarchar(50)

)

Inserting values to table

insert into cts.employee values(346238,'prasanna','tiruvannamalai','[email protected]') insert into cts.employee values(346197,'lakshman','salem','[email protected]')

insert into cts.employee (id,address) values (346200,'tiruvannamalai')

Displaying all entries

table select * from cts.employee

Adding new column to the table (Altering the table data structure)

alter table cts.employee add phone int

Altering an existing column

alter table cts.employee alter column cell nvarchar(15)

Updating name with the given ID

update cts.employee set name='lena prasanna' where id = 346238 update cts.employee set cell='9952941393' where id = 346238

Renaming a particular column name

table exec sp_rename 'cts.employee.phone','cell','column'

Displaying top 2 records

table select top 2 Id,name from cts.employee

Displaying selected entries based on the conditions given

select * from cts.employee where name='lakshman' and address='salem'

Creating another table

create table cts.employees
(

empid varchar(10) primary key, empname varchar(200), deptid varchar(10), joining_date date,

dob date, yrs_of_expr int,

employee_category varchar(10)

)

Displaying all the entries in the table

select * from cts.employees

inserting values to table

insert into cts.employees values(346238,'lena','dn','2012-08-28','1990-05-09',0,'programmer analyst trainee')

insert into cts.employees values(346200,'kishore','dn','2012-08-28','1990-12-08',0,'programmer analyst trainee')

insert into cts.employees values(346201,'prasanna','dn','2012-08-28','1990-07-10',0,'programmer analyst trainee')

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)  

alter table cts.employees alter column employee_category varchar(50)

Displaying details based upon a condition

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

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

select empid,dob,joining_date from cts.employees where DATEDIFF(yy,dob,joining_date) >21

Deleting a specific entry from table

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

use 346238

Check if the table already exists, if exist, drop the table,

if OBJECT_ID('cts.Customer','U') is not null
drop table cts.Customer

Creating table

create table cts.Customer
(
c_id int,
c_name varchar(50), address varchar(max),
constraint pkey primary key(c_id)
)

Check if the table already exist,

if exist, drop the table. if OBJECT_ID('cts.orderid','U') is not null
drop table cts.orderid
creating table
create table cts.orderid
(
orderno int constraint p_key primary key,
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"
product_name varchar(50), c_id int
)

Alter table definition by adding new column

alter table cts.Customer add language_known varchar(50)
alter table cts.orderid add Product_Qty varchar(50)
alter table cts.orderid add Order_Price money

Alter table to add constraint

alter table cts.orderid add constraint f_key foreign key (c_id) references cts.Customer(c_id)
alter table cts.orderid add constraint def_key default 1000 for orderno
alter table cts.orderid add constraint check_key check (orderno>500)

Alter table syntax to drop constraint

alter table cts.orderid drop constraint def_key
alter table cts.orderid drop constraint f_key

Inserting values into the table

insert into cts.Customer values ('Kishore', 'Tamilnadu','Tamil');
insert into cts.Customer values ('Lenaprasanna','Andhrapradesh','Telugu');
insert into cts.Customer values ('Chanakya','Andhrapradesh','Telugu'); select * from cts.Customer;

DDL command that deletes all the entries from the table

truncate table cts.orderid;

Constraints usage

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

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

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

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

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

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

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.

SELECT AVG(Salary) FROM CUSTOMERS

Value = 4675

COUNT() - number of rows.

Select COUNT(*) FROM CUSTOMERS

Value = 4

SELECT COUNT (DISTINCT Salary) FROM CUSTOMERS

Value = 3

MAX() - Maximun or Highest number in a column.

SELECT MAX(Salary) FROM CUSTOMERS

Value = 5000

MIN() - Minimum or Lowest number in a column.

SELECT MIN(Salary) FROM CUSTOMERS

Value = 4000

SUM() - Total number in a column.

SELECT SUM(Salary) FROM CUSTOMERS

Value = 18500

To find distinct number of customers

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:

select distinct COUNT(c_id) "No. of distinct customers" from cts.orderid

Sum function

select SUM(Order_price) from cts.orderid where Order_Price>76

select c_id,SUM(Order_Price),product_name "total amount" from cts.orderid group by c_id,product_name

select c_id,SUM(Order_Price) from cts.orderid group by c_id having c_id > 1002

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:

create view employee as select * from cts.employee

select * from employee

Example 2: Create a view employee with attributes id, name where salary=55000 from employee table. Also display the view.

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.

create view employeeHalfSalary (name,salary)
As
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”.

create view Viewcustomers
as
select * from customers where addres='Chennai'
select * from Viewcustomers

Example 5: Alter the above view and make the salary to be twice the actual salary in employee table.

alter view doubleEmpSalary(name,salary)as(select name,2*salary from cts.employee);
select * from doubleEmpSalary;

Example 6: Drop the view created.

drop view employee;

5. Control Flow Construction in SQL

Using if statement

if ((select id from cts.employee where name = 'lena prasanna') = 346238) begin
print 'Andhrapradesh'
end
else
begin
print 'Tamilnadu'
end

Using while loop pints 5 tables till 100

declare @count int
declare @num int
Note: every variables declared, should be initialized to any number set @num = 0
set @count = 1
while @num < 100 begin
set @num = 5 * @count print @num
set @count = @count + 1
end

Using if exists statement

if exists (select name from cts.employee where name='lena prasanna') print 'Andhrapradesh'
else
print 'Tamilnadu'

Using switch case

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
   

   select name,States=case name when
   'harsha' then 'Andhrapradesh'
   
   when 'sivasaiprasad' then 'Andhrapradesh'
   when 'lena prasanna' then 'Andhrapradesh'
   when 'kishore'
   then 'Tamilnadu'
   when 'chanakya'
   then 'Andhrapradesh' else 'karnataka'
   end from cts.employee

2. METTL syntax if we use syntax 1
   

   select name,case name
   when 'harsha'
   then 'Andhrapradesh'
   when 'sivasaiprasad'
   then 'Andhrapradesh'
   
   when 'lena prasanna'
   then 'Andhrapradesh' when 'kishore'
   then 'Tamilnadu'
   
   when 'chanakya'
   then 'Andhrapradesh'
   else 'karnataka'
   
   end "States" from cts.employee

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

select name, Tax_amount =case
when SALARY >40000
then '20000'
when SALARY <=40000 and SALARY <=30000
then '10000' else '7500'
end

from cts.employee

METTL syntax if we use syntax 2

select name, case
when SALARY >40000
then '20000'
when SALARY <=40000 and SALARY <=30000
then '10000' else '7500'
end

“Tax_amount” from cts.employee

6. Using Local Variables

use dn016_346238

Find odd or even

declare @num int; set @num=6;

if (@num % 2 = 0)

print 'Number is even';

else

print 'Number is odd';

Calculate tax using basic pay

declare @basicPay money; declare @tax money;

set @basicPay=600000;

if (@basicPay > 500000)

set @tax= @basicPay * 20 /100; else if (@basicPay >300000)

set @tax= @basicPay * 10 / 100; else if (@basicPay >200000)

set @tax = @basicPay * 5/100;

else

set @tax = 0;

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

use dn016_346238

declare @id int;

set @id=500;

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

DECLARE @varcursor CURSOR SET @varcursor = CURSOR FOR select * from cts.employee OPEN @varcursor

FETCH @varcursor

WHILE( @@FETCH_STATUS = 0)

BEGIN

FETCH @varcursor

END

CLOSE @varcursor

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:
  

  select * from cts.employee

  Step 2: Opening cursor
  
  Cursor will be created only when step 2 is executed,
  

  Open myCursor

  Step 3: Fetch records using my cursor.
  

  FETCH next FROM myCursor

  Note: We can use "NEXT" only and we can't use other
  
  Intermediate step: update records
  

  update cts.employee set name= 'Lenaprasanna',address ='Andhrapradesh' where current of myCursor

  Normal update query
  

  update cts.employee set name= 'Sriharsha',address ='Hyderabad' where id=346538

  Step 4: Closing cursor.
  

  close myCursor

  Step 5: Cursor Deallocation.
  

  deallocate myCursor

  Forward Only Scroll
  
  We can use all the keywords like FIRST ,LAST, NEXT, PRIOR, ABSOLUTE, RELATIVE.
  
  Declaration of Forward only Scroll
  

  declare myCursor cursor scroll for
  
  select * from cts.employee
  
  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
  

  update cts.employee set name= 'Sivaprasad',address ='hyderabad' where current of myCursor

  Displaying the updated table
  

  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:
  

  declare myCursor cursor for
  
  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:
  

  open myCursor

  Step 3:
  

  update cts.employee set name= 'SriHarshaChilla',address ='Andhrapradesh' where current of myCursor

  Step 4 and Step 5 

  close myCursor
  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
  

  declare myCursor cursor dynamic for
  
  select * from cts.employee

  Step 2: Opening cursor
  

  open myCursor

  Step 3: Fetching using cursor
  
  Note: Except ABSOLUTE, the others like NEXT, PRIOR, FIRST, LAST, DYNAMIC can be used,
  

  FETCH next from myCursor
  FETCH first FROM myCursor
  FETCH last FROM myCursor
  FETCH next FROM myCursor
  FETCH prior FROM myCursor
  FETCH relative 3 FROM myCursor

  Updating entries using Dynamic Cursor
  

  update cts.employee set name='Lenaprasanna' where current of
  myCursor update cts.employee set id=346200 where name = 'S.Kishore'

  Step 4: Closing and freeing the resource .
  

  close myCursor
  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.
  

  declare myCursor cursor static for
  
  select * from cts.employee

  Step 2: Opening cursor.
  

  open myCursor

  Step 3: Fetching using cursor.
  
  All priority levels like ABSOLUTE, NEXT, PRIOR, FIRST, LAST, DYNAMIC can be used.
  

  FETCH next from myCursor
  FETCH first FROM myCursor
  FETCH last FROM myCursor
  FETCH next FROM myCursor
  FETCH prior FROM myCursor
  
  FETCH absolute -2 FROM myCursor
  FETCH absolute 3 FROM myCursor
  FETCH relative 3 FROM myCursor

  Updating entries using static cursor
  

  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,
  

  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
  

  declare myCursor cursor keyset for
  
  select * from cts.employee

  Step 2: Opening cursor
  

  open myCursor

  Step 3: Fetching using cursor.
  
  All priority levels like ABSOLUTE, NEXT, PRIOR, FIRST, LAST, DYNAMIC can be used. FETCH next from myCursor
  

  FETCH first FROM myCursor
  FETCH last FROM myCursor
  FETCH next FROM myCursor
  FETCH prior FROM myCursor
  FETCH absolute -2 FROM myCursor
  FETCH absolute 3 FROM myCursor
  FETCH relative 3 FROM myCursor

  Updating entries using keyset Cursor
  

  update cts.employee set id=346534 where current of myCursor

  Step 4: Closing and freeing the resource close myCursor.
  

  deallocate myCursor

8. Creating user definded datatypes

create type udtDescription from nvarchar(max);

create table cts.UserDescription

(

UserName udtDescription, Passwd nvarchar(50)

);


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:
  

  EXEC sp_rename
  
  ‘dbo.Orders.Details’, Fully Qualified column name
  ‘OrderDetails’, New column name
  ‘COLUMN’ Object Type

• Changing the name of the table using sp_rename stored procedure:
  

  EXEC sp_rename
  
  ‘dbo.Orders’, Existing Table including Schema Name
  ‘OrderDetails’, New column name
  
  ‘OBJECT’ Object Type

• Changing the index of the table using sp_rename stored procedure:
  

  EXEC sp_rename
  'dbo.Orders.PK__Orders__C3905BAF6A30C649', Existing Table including Schema Name
  
  'IDX_PK_C_OrderID', Fully Qualified index name
  ‘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,
  

  create procedure procCustomerList As
  
  Begin
  
  select id,address from cts.employee select empid,dob from cts.employees
  
  end

  Procedure call
  

  exec procCustomerList

• Simple procedure with argument,
  
  Displays customer details for the given id.
  

  create procedure procCustomerList @id int,@empname varchar(30) As
  
  Begin
  
  select id,address from cts.employee where id=@id
  
  select empid,dob from cts.employees where empname=@empname
  
  end

  procedure call
  

  exec procCustomerList 346238,'lenaprasanna'

• Procedure to find fibonacci of n numbers without recursion,
  

  create procedure fibonacci @num int as
  
  begin
  
  declare @fib1 int,@fib2 int,@fib int,@temp int set @fib1 = 0
  
  set @fib2 = 1 set @temp = 2 print @fib1 print @fib2
  
  while(@temp < @num) begin
  
  set @fib=@fib1+@fib2 print @fib
  
  set @fib1=@fib2 set @fib2=@fib
  
  set @temp = @temp + 1
  
  end
  
  end

  Function call
  

  exec fibonacci 5

• Nested procedures
  

  procedure to find fibonacci with recursion
  
  alter procedure recursiveFibonacci @fib1 bigint, @fib2 bigint,@num bigint as
  
  begin
  
  if @num = 0 return
  
  begin
  
  if @fib1 = 0 print 0
  
  declare @temp bigint
  
  set @temp = @fib1 + @fib2 if @num <> 1
  
  print @temp set @num = @num - 1
  
  exec recursiveFibonacci @fib2,@temp,@num
  
  end
  
  end

  Function call
  

  exec recursiveFibonacci 0,1,10

  Note: If a procedure contains an output variable, use the steps given in the comments:
  

  declare @var int
  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.
  

  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
  
  begin
  
  declare @tempCustId varchar(30)
  
  set @tempCustId = cast(@custID as varchar(30))
  
  if @tempCustId is not NULL and @compName is not NULL begin
  
  insert into Sales.Customers values(@tempcustID,@compName,@contName,@contTitle,@address,@city,@region,@postalcode,@c ountry,@phone,@fax,@nooforder)
  
  end
  
  if @tempcustId is NULL begin
  
  print 'ALERT:Cant insert. Customer ID is null'
  
  end
  
  if @compName is NULL
  
  begin
  
  print 'ALERT:Cant insert. Company name is null'
  
  end end

  Function call
  

  exec procCustIDCompNameVerify 1,'cognizant','Lenaprasanna','ProgrammerAnalystTrainee',NULL,'Tenali','Andhrapradesh',531402,'India', 8144386636,'044-4120221',5
  
  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.
  

  create procedure procPatternMatch @type nvarchar(30),@pattern nvarchar(30) as
  
  begin
  
  if( @type = 'FirstName' ) begin
  
  select * from HumanResource.Employees where FirstName like @pattern
  
  end
  
  else if (@type = 'LastName') begin
  
  select * from HumanResource.Employees where LastName like @pattern
  
  end
  
  end

  Procedure call
  

  exec procPatternMatch 'FirstName','k%'
  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
  

• begin
  
  declare @num int set @num=124 return @num
  
  end

  Function call
  

  select dbo.display() or
  
  print dbo.display()

  Function call with alias name
  

  select dbo.display() "My value"
  droping a function

  Drop function
  

  dbo.display

• Case 2: Create a function with single argument.
  
  This function concatenates '0' with the id passed to it.
  

  create function getdetails(@cust int) returns nvarchar(20)
  
  as begin
  
  return '0'+cast(@cust as nvarchar(20))
  
  end

  Function call
  
  
  1. Direct call
     

     print dbo.getdetails(678)

  2. Call from table entry without alias name.
     

     select name,id,dbo.getdetails(id) from cts.employee

  3. Call from table entry with alias name
     

     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,
  

  create function appendName(@id as int, @cus_name as varchar(50)) returns nvarchar(30)
  as begin
  return@ cus_name + convert(nvarchar(30), @id)
  end

  Function call
  
  
  1. With default arguments,
     

     select dbo.appendName(123,'lena')

  2. For all table entries without alias name.
     

     select name,id,dbo.appendName(id,name) from cts.employee

  3. For all table entries with alias name.
     

     select name,id,dbo.appendName(id,name) "Appended Name" from cts.employee

  Exercise 1
  
  Calculate tax with salary from table:
  

  create
  function calculate_tax(@sal as money) returns money
  as begin
  declare@ tax money set@ tax = 0
  if@ sal > 50000 begin
  set@ tax = @sal * 20 / 100 20 % tax of the salary
  end
  else if@ sal > 40000 begin
  set@ tax = @sal * 10 / 100 10 % tax of the salary
  end
  else if@ sal > 30000 begin
  set@ tax = @sal * 5 / 100 5 % tax of the salary
  end
  else begin
  set@ tax = 0 no tax
  if sal <= 30000
  end
  return@ tax
  end

  Function call 

  select name,SALARY,dbo.calculate_tax(SALARY) "Estimated Tax Amount" from cts.employee

  Altering a function
  

  alter
  function calculate_tax(@sal decimal(8, 2)) returns nvarchar(30)
  as begin
  declare@ tax decimal(8, 2) set@ tax = 0
  if@ sal > 50000 begin
  set@ tax = @sal * 20 / 100 20 % tax of the salary
  end
  else if@ sal > 40000 begin
  set@ tax = @sal * 10 / 100 10 % tax of the salary
  end
  else if@ sal > 30000 begin
  set@ tax = @sal * 5 / 100 5 % tax of the salary
  end
  else begin
  set@ tax = 0 no tax
  if sal <= 30000
  end
  return cast(@tax as nvarchar(20))
  end

  Function call
  

  select name,SALARY,dbo.calculate_tax(SALARY) "Estimated Tax Amount" from cts.employee

Type 2: Inline table valued function

• Case 1: Without arguments.
  

  displays id, name and address of all employees in the table create
  function getEntryOfEmployee()
  returns table as
  return (select id, name, address from cts.employee where salary > 45000)

• Case 2: With arguments.
  

  create function getEntryOfEmployeeWithArg(@salCondition as money) returns table
  as return
  (
  
  select id,name,address from cts.employee where salary > @salCondition
  
  )

  Function call
  

  select * from dbo.getEntryOfEmployee()
  select * from dbo.getEntryOfEmployeeWithArg(346238)
  select * from dbo.getEntryOfEmployeeWithArg(55000)

Type 3: Multi statement table valued function

alter
function addOrDisplayEntries(@ID int) returns@ employeelist table(id int, name varchar(50), salary money) as
begin
if@ ID is NULL begin
insert@ employeelist
select id, name, salary from cts.employee
end
else begin
insert into@ employeelist
select id, name, salary from cts.employee where id = @ID
end
return end

Function call

select * from dbo.addOrDisplayEntries(3455)
select * from dbo.addOrDisplayEntries (346238)

Or

Call using a local

variable declare @id int
set @id=346238
select * from dbo.addOrDisplayEntries (@id)

Dropping a function

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.

create
function NumberOfOrders(@cusId int)
returns nvarchar(30)
as begin
declare@ order int
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
return convert(nvarchar(20), @order) provides the no.of orders placed by the customer
end
function call
print dbo.NumberOfOrders(1000)
"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 

create
function displayNames(@name nvarchar(9))
returns@ EmpTable table(ID int, Name nvarchar(50)) as
begin
if (@name = 'ShortName') begin
Initialize@ EmpTable with LastName
insert@ EmpTable select empid, LastName from cts.employees end
else if (@name = 'LongName') begin
Initialize@ EmpTable FirstName LastName
insert@ EmpTable select empid, (FirstName + ' ' + LastName) from cts.employees end
Provides the value of@ EmpTable as the result
return
end

Function call

select * from dbo.displayNames('ShortName')
select * from dbo.displayNames('LongName')

Question 3: Functions that takes region as input and prints all the users in the particular region function definition.

create
function displayOthers(@region as nvarchar(50))
returns table
as
return (Select all the users who all are currently in the given region select name from cts.employee where address = @region)

Function call

select * from displayOthers('Hyderabad')

11. Exception handling in Store Procedure

Factorial example 

• Case 1: Factorial using procedure - with Input parameter and without Output parameter.
  

  create procedure procFactorial@ num int
  as
  begin
  try
  declare@ count int, @result int set@ count = 1
  set@ result = 1
  while@ count <= @num begin
  set@ result = @result * @count set@ count = @count + 1
  end
  select@ result "Factorial value"
  end
  try begin
  catch
  select ERROR_NUMBER(), ERROR_MESSAGE() end
  catch

  Procedure call
  

  exec procFactorial 5

• Case 2: Factorial using procedure - with Input and Output parameter.
  

  alter procedure procFactWithOutputParameter
  @num int,@result numeric(38,0) output
  
  as begin try
  
  declare @count int set @result = 1 set @count = 1
  
  if incase we provide 0 as input, It should obviously return 1 SINCE 0! = 1 while @count <= @num
  
  begin
  
  set @result = @result * cast(@count as numeric(38,0))
  
  set @count = @count + 1
  
  end
  
  end try begin catch
  
  select ERROR_NUMBER() "ERROR NUMBER",ERROR_MESSAGE()"ERROR MESSAGE" end catch

  Procedure call
  

  declare @inputvar int,@outputvar numeric(38,0) set @inputvar = 30
  set @outputvar = 1
  exec procFactWithOutputParameter @inputvar,@outputvar output select @inputvar "Input value",@outputvar "Factorial value"

  Or
  

  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,
  

  as begin
  declare@ count int
  declare@ result numeric(38, 0) set@ count = 1
  set@ result = 1
  if incase we provide 0 as input, It should obviously
  return 1
  while@ count <= @num
  begin
  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.
  set@ count = @count + 1
  end
  return@ result
  end

  Function call
  

  select dbo.funcFactorial(30) "Factorial value"

• Case 4: Function to find factorial - with recursion.
  

  alter
  function funcRecursiveFact(@num numeric(38, 0)) returns numeric(38, 0)
  as begin
  declare@ prod numeric(38, 0) if@ num <= 1
  return 1
  else set@ prod = cast(@num as numeric(38, 0)) * dbo.funcRecursiveFact(@num - 1) return@ prod
  end

  Function value
  

  select dbo.funcRecursiveFact(30) "Factorial value"

• Case 5: Creating a procedure in another procedure.
  

  create procedure procInProcFactorial@ inputval int, @result numeric(38, 0) output as
  begin
  declare@ init int set@ init = 1
  exec procFactWithOutputParameter@ inputval, @result output
  return@ result
  end
  declare@
  var int
  exec procInProcFactorial 5, @
  var output select@
  var "Factorial value"

• Case 6: Creating a procedure in another procedure.
  

  create procedure procfactorialwithoutput@ num int, @result numeric(38, 0) output as
  begin
  try
  declare@ prod numeric(38, 0) set@ prod = 1
  while (@num > 1) begin
  set@ prod = @prod * @num set@ num = @num - 1
  end
  set@ result = @prod end
  try
  begin
  catch
  select ERROR_NUMBER(), ERROR_MESSAGE() end
  catch

  Second procedure
  

  alter proc procinprocfact@ max int, @result numeric(38, 0) output as
  begin
  declare@ init int set@ init = 0
  while (@init <= @max) begin
  exec procfactorialwithoutput@ init, @result output
  print cast(@init as varchar(20)) + '!=' + cast(@result as varchar(max)) set@ init = @init + 1
  end
  end

  Function call
  

declare @var numeric(38,0)
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.
   

   if object_id('AuditingTable', 'u') is not null
   drop table AuditingTable
   create table AuditingTable(EmpID int, Trigger_msg nvarchar(max), Modified_date date
       if u want to print date and time, use the datatype "datetime")

   To drop the existing trigger
   

   if OBJECT_ID('EmployeeTrigger','TR') is not null drop trigger EmployeeTrigger

   Example 1: Creating trigger to an existing table called cts.employee.
   

   create trigger EmployeeTrigger
   
   on cts.employee after insert
   
   as begin
   
   declare @id int
   
   "Inserted" table is a magical table select @id=i.id from inserted i
   
   
   -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())
   
   end

   Trigger is called automatically when the data is inserted
   

   insert into cts.employee
   values(4557,'Lenaprasanna','Andhrapradesh','[email protected]',9876544334,25000)

   To view the inserted data
   

   select * from AuditingTable

   Example 2: Create a single trigger that responds to update and delete.
   

   create trigger employeeUpdateDeleteTrigger on cts.employee
   after update, delete as
   begin
   declare@ id int "Inserted"
   table is a magical table select@ id = i.id from inserted i
   insert into AuditingTable(EmpID, Trigger_Msg, Modified_date) values(@id, 'A record is modified in cts.employee table', getDate())
   end

   To call the trigger automatically when the following 2 statements are executed
   

   delete from cts.employee where id=4557
   update cts.employee set name='chanakya' where id=346234

   To view the deleted and updated data
   

   select * from AuditingTable

   Example 3: Create a single trigger that responds to insert, update and delete.
   

   create trigger employeeInsUpdDelTrigger
   on cts.employee
   after insert, update, delete as
   begin
   declare@ id int "Inserted"
   table is a magical table select@ id = i.id from inserted i "deleted"
   table is an another magical table select@ id = i.id from deleted i
   insert into AuditingTable(EmpID, Trigger_Msg, Modified_date)
   values(@id, 'Insert,Delete or update action is perfomed in the table', getDate())
   end

   To call the trigger automatically when any of the following 3 statements are executed alone or in combinations
   

   insert into cts.employee values(4557, 'Lenaprasanna', 'Andhrapradesh', '[email protected]', 9876544334, 25000) delete from cts.employee where id = 4557
   update cts.employee set name = 'chanakya'
   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
   

   select * from AuditingTable

   To view the base table data
   

   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:
   

   create trigger InsteadOfemployeeInsUpdDelTrigger on cts.employees
   Instead Of insert, update, delete as
   begin
   declare@ id int "Inserted"
   table is a magical table / select@ id = i.empid from inserted i "deleted"
   table is an another magical table / select@ id = i.empid from deleted i
   insert into AuditingTable(EmpID, Trigger_Msg, Modified_date) values(@id, 'Alert: some body tried to Modify it', getDate())
   end

   Calling InsteadOf trigger
   

   update cts.employees set empname='Lenaprasanna' where empid=346238

   To view the Auditing table
   

   data select * from AuditingTable

   To view the basetable data
   

   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

create table auditing

(

id int,

trigger_msg nvarchar(max), mod_date datetime

)

1. Question 1:
   

   create trigger preventDeleteMoreThanOnceTrigger on Sales.OrderDetails instead of delete
   
   as begin
   
   declare @count int declare @id int
   
   select @count=count(*) from deleted print @count
   
   if @count<=1 begin
   
   select @id=i.OrderID from deleted i
   
   delete from Sales.OrderDetails where OrderID=@id
   
   end
   
   else
   
   print 'Alert: You attempted to delete more than one record at a time!!!!'
   
   end

   Calling trigger
   

   delete from Sales.OrderDetails where productID=21

2. Question 2:
   

   create trigger preventModifyTrigger
   on sales.customers
   instead of update as
   begin
   declare@ id int;
   declare@ comp varchar(30);
   select@ id = i.CustomerId from inserted i;
   select@ comp = i.CompanyName from inserted i;
   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
           customerId ',getdate()) else
           update Sales.Customers set CompanyName = @comp where CustomerId = @id end

   Calling trigger
   

   update sales.customers set CompanyName = 'CTS'
   where customerid = 61 update sales.customers set Customerid = 61 where companyName = 'CTS'
   select * from sales.customers
   select * from auditing

Type 2: DDL TRIGGERS

Table structure

create table ObjectTracking

(

TrackingID bigint identity(1,1),

TrackingDate datetime NOT NULL default getdate(), DatabaseName varchar(500),

EventType varchar(500), ObjectName varchar(500), ObjectType varchar(500), LoginName varchar(500), HostName varchar(500), SqlCommand nvarchar(max), ModifiedDateTime datetime

)

DDL trigger creation

create trigger databaseTrigger on database

for CREATE_PROCEDURE,CREATE_TABLE

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

as begin

declare @data XML

declare @HostName varchar(500) set @data = EVENTDATA()

--assigning EVENTDATA() to a local variable @data.

--Note: After the above assignment, calling EVENTDATA().value and @data.value will be same hereafter

set @HostName = HOST_NAME()

--HOST_NAME() retrieves the machine name. In this step, we are assigning HOST_NAME() to a local variable @HostName

insert into ObjectTracking

(

DatabaseName,

EventType,

ObjectName,

ObjectType,

LoginName,

HostName,

SqlCommand, ModifiedDateTime

)

values(

@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)

@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()

)

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

created trigger create table TriggerCheck(id int, name varchar(50))

Displaying the ObjectTracking table entried

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

use santhosh_dn016 go
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);
GO
INSERT INTO employeeDetails VALUES(1, 'Jason', 'Martin', 5890, '2005-03-22', 'North', 'Vancouver', 3);
GO
--(1 rows affected)
INSERT INTO employeeDetails VALUES(2, 'Alison', 'Mathews', 4789, '2003-07-21', 'South', 'Utown', 4);
GO
--(1 rows affected)
INSERT INTO employeeDetails VALUES(3, 'James', 'Smith', 6678, '2001-12-01', 'North', 'Paris', 5);
GO
--(1 rows affected)
INSERT INTO employeeDetails VALUES(4, 'Celia', 'Rice', 5567, '2006-03-03', 'South', 'London', 6);
GO
--(1 rows affected)
INSERT INTO employeeDetails VALUES(5, 'Robert', 'Black', 4467, '2004-07-02', 'East', 'Newton', 7);
GO
--(1 rows affected)
INSERT INTO employeeDetails VALUES(6, 'Linda', 'Green', 6456, '2002-05-19', 'East', 'Calgary', 8);:
GO
--(1 rows affected)
INSERT INTO employeeDetails VALUES(7, 'David', 'Larry', 5345, '2008-03-18', 'West', 'New York', 9);
GO
--(1 rows affected)
INSERT INTO employeeDetails VALUES(8, 'James', 'Cat', 4234, '2007-07-17', 'West', 'Regina', 9);
GO
--(1 rows affected)
INSERT INTO employeeDetails VALUES(9, 'Joan', 'Act', 6123, '2001-04-16', 'North', 'Toronto', 10);
GO
--(1 rows affected)
select * from employeeDetails;
GO
--id first_name last_name salary start_Date region city managerid
-- -- -- -- -- - -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
--1 Jason Martin 5890.00 2005 - 03 - 22 00: 00: 00.000 North Vancouver 3
    --2 Alison Mathews 4789.00 2003 - 07 - 21 00: 00: 00.000 South Utown 4
    --3 James Smith 6678.00 2001 - 12 - 01 00: 00: 00.000 North Paris 5
    --4 Celia Rice 5567.00 2006 - 03 - 03 00: 00: 00.000 South London 6
    --5 Robert Black 4467.00 2004 - 07 - 02 00: 00: 00.000 East Newton 7
    --6 Linda Green 6456.00 2002 - 05 - 19 00: 00: 00.000 East Calgary 8
    --7 David Larry 5345.00 2008 - 03 - 18 00: 00: 00.000 West New York 9
    --8 James Cat 4234.00 2007 - 07 - 17 00: 00: 00.000 West Regina 9
    --9 Joan Act 6123.00 2001 - 04 - 16 00: 00: 00.000 North Toronto 10
    --(9 rows affected)
CREATE INDEX i_empno ON employeeDetails(id);
CREATE NONCLUSTERED INDEX NI_ID_FirstName ON employeeDetails(ID, First_Name) GO
CREATE NONCLUSTERED INDEX NCI_FirstName ON employeeDetails(First_name ASC) GO
CREATE NONCLUSTERED INDEX NCI_FirstName ON employeeDetails(First_name ASC) WITH(DROP_EXISTING = ON)
GO: 54 | P a g e
CREATE NONCLUSTERED INDEX NCI_FirstName ON employeeDetails(ID, First_name DESC) WITH(DROP_EXISTING = ON)
GO
DROP INDEX employeeDetails.i_empno
    --The next statement is equivalent to the previous one--DROP INDEX i_empno ON employee
drop table employeeDetails;
GO

The next statement is equivalent to the previous one DROP INDEX i_empno ON employee.

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.

begin transaction InsertTransaction GO
Insert into cts.employee values(346530, 'saisivaprasad', 'Hyderabad', '[email protected]', 9865778345, 55000)
commit transaction

Example for rollback

begin transaction InsTransRoll
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.

create table TestTran
(
   cola int PRIMARY KEY, colb varchar(50)
)

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

print N 'Transaction count after BEGIN Outer Transaction is:' + cast(@@TRANCOUNT as nvarchar(10))
Insert into TestTran values(1, 'lena')
begin transaction InnerTransaction1

This statements sets @TRANCOUNT to 2.

go
print N 'Transaction count after BEGIN Inner Transaction1 is: ' + cast(@@TRANCOUNT as nvarchar(10))
Insert into TestTran values(2, 'kishore')
begin transaction InnerTransaction2 go

This statements sets @TRANCOUNT to 3.

print N 'Transaction count after BEGIN Inner Transaction2 is:' + cast(@@TRANCOUNT as nvarchar(10))
Insert into TestTran values(3, 'sivaprasad') commit transaction InnerTransaction2

This statements sets @TRANCOUNT to 2.

print N 'Transaction count after COMMIT Inner Transaction2 is:' + cast(@@TRANCOUNT as nvarchar(10))
go
commit transaction InnerTransaction1

This statements sets @TRANCOUNT to 1.

print N'Transaction count after COMMIT Inner Transaction1 is:' + cast(@@TRANCOUNT as nvarchar(10))
go
commit transaction OuterTransaction

This statements sets @TRANCOUNT to 0.

print N'Transaction count after COMMIT OuterTransaction is :' + cast(@@TRANCOUNT as nvarchar(10))
go

Example for deadlock condition and non-deadlock condition:

select * from cts.employee
select * from cts.employees

• Case a: Non - deadlock scenario:
  

  begin transaction DeadLock1
  
  update cts.employee set address = 'Madras' where id=34620
  
  --Syntax :waitfor delay 'time' will commit the transaction after the stipulated time
  
  given.
  
  waitfor delay '00:00:08' -- This will Commit the transaction after 8 seconds update cts.employees set empname='M Chanakya' where empid=346232
  
  commit transaction Deadlock1
  
  begin transaction Deadlock2
  
  update cts.employee set address = 'Madras' where id=34620
  
  --Syntax :waitfor delay 'time' will commit the transaction after the stipulated time
  
  given.
  
  waitfor delay '00:00:15' -- This will Commit the transaction after 15 seconds update cts.employees set empname='M Chanakya' where empid=346232
  
  commit transaction Deadlock2

• Case b: Deadlock scenario:
  

  begin transaction DeadLock1
  
  update cts.employees set empname='M Chanakya' where empid=346232
  
  --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
  
  update cts.employee set address = 'Madras' where id=34620 commit transaction Deadlock1

  Output for Deadlock1:
  

  --(1 row(s) affected)
  
  --(1 row(s) affected)
  
  --Msg 1205, Level 13, State 51, Line 5
  
  --Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  
  begin transaction Deadlock2
  
  update cts.employee set address = 'Madras' where id=34620
  
  --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
  
  update cts.employees set empname='M Chanakya' where empid=346232
  
  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:

begin transaction TransEvent begin try

update cts.employee set address = 'Madras' where id=34620 --waitfor delay '00:00:15'

update cts.employees set empname='M Chanakya' where empid=346232 end try

begin catch

if ERROR_NUMBER() = 1205 -- Note: This error number deals with deadlock. begin

goto RETRY

end end catch

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.

declare @xmlVariable xml
set @xmlVariable='<student/>'; select @xmlvariable

Create a Table with XML type

create table employeeXML(ID xml, Name xml, Language_known xml, preferred_location xml, Qualification xml)

Inserting data into the table 

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>')
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>')
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>')
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>')
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

select * from employeeXML FOR XML RAW
select * from employeeXML FOR XML PATH
select * from employeeXML FOR XML PATH ('EmployeeTable')
select * from employeeXML FOR XML AUTO

Using XML mode in normal table

SELECT * from cts.Customer FOR XML RAW
SELECT * from cts.Customer FOR XML PATH
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

<Employees>
    <Employee>
        <id>346238</id>
        <name>Lenaprasanna</name>
    </Employee>
    <Employee>
        <id>346200</id>
        <name>Kishore</name>
    </Employee>
    <Employee>
        <id>346534</id>
        <name>Chanakya</name>
    </Employee>
</Employees>

We can give like this

<Employees>
    <Employee id="346238" name="Lenaprasanna" />
    <Employee id="346200" name="Kishore" />
    <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
  

  declare@ xmlvar xml
  set@ xmlvar = '<Employees> < Employee id = "346238"
  name = "Lenaprasanna" / > < Employee id = "346200"
  name = "Kishore" / > < Employee id = "346534"
  name = "Chanakya" / > < Employee id = "346535"
  name = "Lenaprasanna" / > < /Employees>'
  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
  

  declare@ xmlvar xml
  set@ xmlvar = '<Employees> < Employee id = "346238"
  name = "Lenaprasanna" / > < Employee id = "346200"
  name = "Kishore" / > < Employee id = "346534"
  name = "Chanakya" / > < Employee id = "346535"
  name = "Lenaprasanna" / > < /Employees>'
  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:
  

  declare@ xmlvar xml
  set@ xmlvar = '<Employees> < Employee id = "346238"
  name = "Lenaprasanna" / > < Employee id = "346200"
  name = "Kishore" / > < Employee id = "346534"
  name = "Chanakya" / > < Employee id = "346535"
  name = "Lenaprasanna" / > < /Employees>'
  select@ xmlVar.exist('/Employees/Employee[@id="346238"]') as "Is Exist?"

  Output:
  
  Is Exist?
  -
  1
  
  Case b: Check for non-existing record:
  

  declare@ xmlvar xml
  set@ xmlvar = '<Employees> < Employee id = "346238"
  name = "Lenaprasanna" / > < Employee id = "346200"
  name = "Kishore" / > < Employee id = "346534"
  name = "Chanakya" / > < Employee id = "346535"
  name = "Lenaprasanna" / > < /Employees>'
  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
  

  declare@ xmlvar xml
  set@ xmlvar = '<Employees> < Employee id = "346238"
  name = "Lenaprasanna" / > < Employee id = "346200"
  name = "Kishore" / > < Employee id = "346534"
  name = "Chanakya" / > < Employee id = "346535"
  name = "Lenaprasanna" / > < /Employees>'
  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
  

  declare@ xmlvar xml
  set@ xmlvar = '<Employees> < Employee id = "346238"
  name = "Lenaprasanna" / > < Employee id = "346200"
  name = "Kishore" / > < Employee id = "346534"
  name = "Chanakya" / > < Employee id = "346535"
  name = "Lenaprasanna" / > < /Employees>'
  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
  

  set@ xmlvar = '<Employees> < Employee id = "346238"
  name = "Lenaprasanna" / > < Employee id = "346200"
  name = "Kishore" / > < Employee id = "346534"
  name = "Chanakya" / > < Employee id = "346535"
  name = "Lenaprasanna" / > < /Employees>'
  select x.value('@id', 'int') as "EmployeeID", x.value('@name', 'varchar(30)') as "EmployeeName"
  from@ xmlvar.nodes('/Employees/Employee') EmployeeXml(x)

  Output:
  

  EmployeeID EmployeeName - 346238 Lenaprasanna
  346200 Kishore
  346534 Chanakya
  346535 Lenaprasanna

  2. Retrieving the attributes of the parent tag and also of the current tag.
  

  declare @xmlvar xml
  
  set @xmlvar='<Employees> <Employee S.No="1">
  
  <Details id="346238" name="Lenaprasanna"/> </Employee>
  
  <Employee S.No="2">
  
  <Details id="346200" name="Kishore"/> </Employee>
  
  <Employee S.No="3">
  
  <Details id="346534" name="Chanakya"/> </Employee>
  
  <Employee S.No="4">
  
  <Details id="346535" name="Lenaprasanna"/> </Employee>
  
  </Employees>'
  
  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:
  

  --Create table variables declare @Employee table
  
  (EmployeeID int NOT NULL, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, JobTitle varchar(50) NOT NULL)
  
  --
  
  declare @Language table (LanguageID int NOT NULL,
  
  LanguageName varchar(30) NOT NULL)
  
  --
  
  declare @EmployeeLanguage table (EmployeeID int NOT NULL, LanguageID int NOT NULL)
  
  --
  
  --  Populate table variables with sample data insert into @Language values (1, 'English')
  
  insert into @Language values (2, 'Spanish') insert into @Language values (3, 'French')
  
  insert into @Language values (4, 'Italian') insert into @Language values (5, 'Greek')
  
  --
  
  insert into @Employee values (101, 'Jane', 'Doe', 'Accountant - Level I') insert into @Employee values (102, 'John', 'Smith', 'Business Analyst')
  
  insert into @Employee values (103, 'Erin', 'Thopmson', 'Database Administrator')
  
  --
  
  insert into @EmployeeLanguage values (101, 1) insert into @EmployeeLanguage values (102, 1)
  
  insert into @EmployeeLanguage values (102, 2) insert into @EmployeeLanguage values (103, 3)
  
  insert into @EmployeeLanguage values (103, 4) insert into @EmployeeLanguage values (103, 5)
  
  --
  --  Render as XML
  
  select 1 as Tag,
  NULL as Parent,
  
  NULL as 'Requisitions!1',
  
  NULL as 'Record!2!EmployeeID!element',
  
  NULL as 'Record!2!FirstName!element',
  
  NULL as 'Record!2!LastName!element', NULL as 'Record!2!JobTitle!cdata',
  
  NULL as 'Languages!3!language!element' union
  
  SELECT 2 as Tag, 1 as Parent, NULL, EmployeeID, FirstName, LastName, JobTitle, NULL
  
  FROM @Employee union all
  
  select 3 as Tag, 2 as Parent, NULL,
  
  e.EmployeeID,
  
  e.FirstName,
  
  e.LastName,
  
  e.JobTitle,
  
  l.LanguageName from @Employee e
  
  inner join @EmployeeLanguage el on e.EmployeeID = el.EmployeeID inner join @Language l on el.LanguageID = l.LanguageID
  
  order by 'Record!2!EmployeeID!element', 'Languages!3!language!element' for xml explicit