Contents1. DBMS Vs RDBMS2. Normalization3. SQL Server Version4. What is SQL and its Commands.5. SQL DataTypesWhat is DBMS?DBMS is the software program that is used to manage all the database that are stored on the network or system hard disk.=>In dbms, there is no relationship concept.=>It can't implement constraints in table.=>It supports Single User only.=>It treats Data as Files internally.=>Examples: FoxPro,Ms Excel.What is RDBMS?
RDBMS is the database system in which the relationship among different tables are maintained.=>It is used to establish the relationship concept between two database objects, i.e, tables=>It implement constraints in table=>It supports multiple users.=>It treats data as Tables internally=>examples: SQL Server,Sybase,Oracle, MySQL, DB2,Ms AccessKey Difference between DBMS and RDBMS:
The key difference is that RDBMS (relational database management system) applications store data in a tabular form, while DBMS applications store data as files.Does that mean there are no tables in a DBMS?There can be, but there will be no “relation” between the tables, like in a RDBMS.Is access database a RDBMS?Access fulfills all 12 rules of CODD, so from this point of view yes it’s truly RDBMS.Normalization :Normalization or data normalization is a process to organize the data into tabular format (database tables). A good database design includes the normalization, without normalization a database system may slow, inefficient and might not produce the expected result. Normalization reduces the data redundancy and inconsistent data dependency.Is the step by step process of organizing data to minimize data redundancy (duplication of data),which ensures data consistency.Types :1st NF >Data in each column should be atomic (no multiple values in cell) , no repeating columns(eg : empname1,empname2)2nd NF >Conditions in first normalization should be met and , move the redundant data (repeated data) to separate table , and create relationship between them .3rd NF >(1st NF + 2 nd NF) + all columns attributes should only depend on primary key columnsBCNF > Boyce Code Normal FormA database table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key.The process of converting the table into BCNF is as follows:1. Remove the non trival functional dependency.2. Make separate table for the determinants.4th NF >A database table is said to be in 4NF if it is in BCNF and primary key has one-to-one relationship to all non keys fields or We can also said a table to be in 4NF if it is in BCNF and contains no multi-valued dependencies.The process of converting the table into 4NF is as follows:1. Remove the multivalued dependency.2. Make separate table for multivalued Fields.5th NF >Fifth Normal Form (5NF)A database table is said to be in 5NF if it is in 4NF and contains no redundant values or We can also said a table to be in 5NF if it is in 4NF and contains no join dependencies.The process of converting the table into 5NF is as follows:1. Remove the join dependency.2. Break the database table into smaller and smaller tables to remove all data redundancy.
SQLServer Versions:Quick summary:
SQL Server 2016
codename ?
codenameHekatonSQL14
codename Denali
codename Kilimanjaro
codename Katmai
codename Yukon
codename Shiloh
codename Sphinx
>DML : Data Manipulation LanguageUsed for managing data wit in Schema Objects.Select,insert,Update,Delete>DDL : Data Definition Language.Used to define DB structure or schemaCreate ,Alter,Truncate ,Drop,Rename.>DCL : Data Control LanguageGrant,Revoke.>TCL : Transaction Control Language.Used to control set of transactions.Commit,Rollback.What are Defaault Database in SQL ?>Master DB>MS DB>MODEL DB>TEMP DB>Resource DB – Read only system Db , hidden from users.What can SQL Do ?>It can create new DB and new tables on a DB>It can execute queries against a DB.>It can retrieve data from a DB.>It can Insert,Update,Delete records from a DB.>It can create Stored Procedures in a DB.>It can set Permissions on tables,Procedures and Views.SQL Server Data TypesString types:
SESSION 2
Contents
Ø Create DataBase
Ø Use DataBase
Ø Rename DataBase
Ø Drop DataBase
Ø Create Table
Ø Rename Table
Ø Insert into Table
Ø Selecting a Table
Ø Alter Add new column
Ø Alter Drop Colum
Ø Update
Ø Alter Change datatype Of Column
Ø Delete Table
Ø Drop Table
Ø Select Statement (using Where)
Ø Update Statement (using Where)
Ø SQL Aggregate Functions
Ø SQL Scalar Functions
Ø Mathematical Functions
Ø Trignometrical Functions
Ø String Functions
Ø Date Functions
Ø System Functions
Ø Identity
Ø Diff b/w @@identity ,Scope Identity
,Ident_Current
Ø Transaction in SQL
Ø Bulk Copy
Ø Alias Name
Ø Top Clause
Ø Distinct
Ø Operators in Sql
Query's……..
Ø -- database
Create database demo
use demo
Sp_renamedb olddb,newdb
Drop daatabase db
Ø --creating a
table
create table
Center_details(CenterName nvarchar(100) NOT NULL,RegNo int,Location nvarchar(100))
Ø --Rename Table
sp_rename
oldTablename,NewTablename
]
Ø --To View all the Tables of Db
SP_Tables
Ø --To View all the Columns of the
Table
SP_Columns TblName
Ø --Rename Column of a Table
Sp_rename ‘tblname.columnname’,’newcolumnname’
Ø --inserting into table
Ø insert into
Center_details values('A',1,'Chennai')
Ø insert Center_details values('B',2,'Madurai')
Ø insert into
Center_details(CenterName,RegNo,Location) values('C',3,'Covai')
Ø insert Center_details (CenterName,RegNo,Location) values('D',4,'Trichy')
Ø insert into
Center_details values('F',1)
Ø
Ø insert Center_details(CenterName,RegNo) values('E',5)
Ø insert Center_details(CenterName,RegNo,Location) values('A',1,'Chennai'),('B',2,'Madurai'),('C',3,'Covai'),('D',4,'Trichy')
Ø --Select
select*from
Center_details
select centername,regno
from Center_details
select distinct
centername from Center_details
select distinct
centername,regno from
Center_details
SELECT * FROM Center_details WHERE
centername='A' AND Regno=1
SELECT * FROM Center_details WHERE
centername='A' or Regno=4
SELECT * FROM Center_details WHERE
centername='A' AND (Location='Chennai' OR Location='Madurai')
SELECT * FROM Center_details WHERE
centername='A' or (Location='Chennai' OR Location='Madujhcgkhfdrai')
select*from
Center_details order by
centername
select*from
Center_details order by
centername asc
select*from
Center_details order by
centername desc
select top
2*from
Center_details
nocount
rowcount
select*from tbsample
select*from student
set nocount
on
set nocount
off
set rowcount
5
set rowcount
0
Ø --Delete
delete Center_details
delete from
Center_details
delete Center_details where
RegNo=4
delete from Center_details where CenterName='C'
Ø --Altering a table
Ø add new column
alter table
Center_details add Fees int
Ø drop new column
alter table
Center_details drop column
Fees
Ø change datatype of a column
alter table
Center_details alter column
regno varchar(100)
alter table
Center_details alter column
CenterName varchar(100) null
alter table
Center_details alter column
CenterName varchar(100) not null
Ø --Updating Table
Update Center_details
set CenterName = ‘Zion Heights’
Update Center_details
set CenterName = ‘Zion Heights’ where regno=5
Update Center_details
set CenterName = ‘Zion Heights’ where regno=5 and
Location=’Chennai’
Ø --dropping a table Truncate Table
drop table
Center_details
Ø --Truncate Table
Truncate table
Center_details
Difference between delete and truncate :
DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is only
Possible when we use it in transaction and not committing the transaction done.
DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.
DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.
Identity….
create table EMPloyee(empno int identity(1,1),empname varchar(100),empaddress varchar(100))
insert EMPloyee values('A','Chennai')
insert EMPloyee values('B',’Coimbatore')
select*from EMPloyee
drop table EMPloyee
insert EMPloyee values('C','Madurai')
Truncate table EMPloyee
Allow insert into identity field
You can alllow insert to the identity field by setting IDENTITY_INSERT ON for a particular table as shown:
- SET IDENTITY_INSERT Customer ON
Disallow insert into identity field
You can also disalllow insert to the identity field by setting IDENTITY_INSERT OFF for a particular table as shown:
- SET IDENTITY_INSERT Customer OFF
Insert Value to Identity field
Now, lets see how to insert our own values to identity field ID with in the Customer table.
- SET IDENTITY_INSERT Customer ON
- INSERT INTO Customer(ID,Name,Address) VALUES(3,'Rahul','Noida')
- INSERT INTO Customer(ID,Name,Address) VALUES(4,'Rahul','Noida')
- SET IDENTITY_INSERT Customer OFF
- INSERT INTO Customer(Name,Address) VALUES('Rita','Noida')
After Inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF.
How to Reseed identity Column
DBCC CHECKIDENT (EMPloyee,RESEED, 1)
Difference
between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT…
@@IDENTITY
It returns the last identity value generated for any
table in the current session, across all scopes, suppose we create an insert trigger on table which inserts a row in another
table with generate an identity column, then @@IDENTITY returns that
identity record which is created by trigger.
SCOPE_IDENTITY
It returns the last identity value generated for any table in
the current session and the current scope.Let me explain this... suppose we
create an insert trigger on table which inserts a row in another table with
generate an identity column, then
SCOPE_IDENTITY result is not affected but if a trigger or a user defined
function is affected on the same table that produced the value returns that
identity record then SCOPE_IDENTITY returns
that identity record which is created by trigger or a user defined function.
IDENT_CURRENT
It returns the last identity value generated for a specific
table in any session and any scope . In other words, we can say it is not
affected by scope and session, it only depends on a particular table and
returns that table related identity value which is generated in any session or
scope.
- The
@@identityfunction returns the last identity created in the same session. - The
scope_identity()function returns the last identity created in the same session and the same scope. - The
ident_current(name)returns the last identity created for a specific table or view in any session. - The
identity()function is not used to get an identity, it's used to create an identity in aselect...intoquery.
The session is the database connection. The scope is the current query or the current stored procedure.
CREATE TABLE Parent(id int IDENTITY);
CREATE TABLE Child(id int IDENTITY(100,1));
CREATE TRIGGER Parentins ON
Parent FOR INSERT
AS
BEGIN
INSERT Child
DEFAULT VALUES
END;
--End of trigger
definition
SELECT id FROM Parent;
--id is empty.
SELECT id FROM Child;
--ID is empty.
--Do the
following in Session 1
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the
value 100. This was inserted by the trigger.*/
SELECT SCOPE_IDENTITY();
/* Returns the
value 1. This was inserted by the
INSERT statement
two statements before this query.*/
SELECT IDENT_CURRENT('Child');
/* Returns value
inserted into Child, that is in the trigger.*/
SELECT IDENT_CURRENT('Parent');
/* Returns value
inserted into Parent.
This was the
INSERT statement four statements before this query.*/
-- Do the
following in Session 2.
SELECT @@IDENTITY;
/* Returns NULL
because there has been no INSERT action
up to this point
in this session.*/
SELECT SCOPE_IDENTITY();
/* Returns NULL
because there has been no INSERT action
up to this point
in this scope in this session.*/
SELECT IDENT_CURRENT('Child');
/* Returns the last value inserted into Child.*/
TCL in SQL….
create table EMPloyee(empno int identity(1,1),empname varchar(100),empaddress varchar(100))
drop table EMPloyee
insert EMPloyee values('A','X')
insert EMPloyee values('B','Z')
select*from EMPloyee
BEGIN TRAN
DELETE EMPloyee WHERE empno = 10
IF @@ROWCOUNT =1
BEGIN
COMMIT TRAN
PRINT 'EMPLOYEE
DELETED'
END
ELSE
BEGIN
ROLLBACK TRAN
PRINT 'ROLLED
BACK'
END
we use
savepoints in TCL to
save a particular transactions.
If we use savepoint it saves the transaction
that takes place below it.
select*from EMPloyee
BEGIN TRAN
SAVE TRAN E1
DELETE EMPloyee WHERE EmpNO=4
SAVE TRAN E2
DELETE EMPloyee WHERE EmpNO=8
SELECT * FROM EMPloyee
ROLLBACK TRAN E2
Bulkcopy-
within a database
'COPYING a table
into another table'
select *into
duplicte_tble from
old_table.
'COPYING only the
structure of a table'
select *into
duplicte_tble from
old_table where 1=2.
'The TOP Clause'
'The TOP clause is
used to specify the number of records to return.
The TOP clause can
be very useful on large tables with thousands
of records.
Returning a large number of records can impact
on performance.
Note: Not all
database systems support the TOP clause.'
SELECT TOP 2 * FROM employee
SELECT TOP 50 PERCENT * FROM employee
'ALIAS'
SELECT
column_name(s)
FROM table_name
AS alias_name
SELECT *from employee as emp
SELECT
column_name AS alias_name
FROM table_name
select desg as work from employee
SELECT po.o_id, p.LastName, p.FirstName
FROM Persons AS p,Orders AS po
WHERE p.LastName='siva' AND p.FirstName='sakthi'
DISTINCT Statement
'SELECT DISTINCT
Statement'
In a table, some of the columns may contain duplicate values.
This is not a problem,
however, sometimes you will want to list only
the different (distinct) values in a table.
The DISTINCT
keyword can be used to return
only distinct (different) values.
Syntax:
SELECT DISTINCT column_name(s)
FROM table_name
select distinct desg from
employee
Ø --Built
in System Functions..
Ø '1.Mathametical
Function'
select abs(236473)
select ceiling(74.1)
select floor(74.5)
select exp(1)
select round(74.2868,3)
select round(74.2818,3)
select round(74.2868,1)
select round(74.2862,3)
select round(74.2865,4)
Ø '2.Trignometric
Function'
select sin(90)
select cos(45)
select Tan(90)
select log(10)
'3.String Function'
select ascii('B')
'American Standard Code for Information
Interchange'
'ANSI-American National Standards Institute'
select char(97)
select reverse('sample')
select substring('welcome',4,4)
select substring('welcome',4,2)
select substring('welcome',4,5)
select substring('welcome',4,1)
select substring('welcome',4,3)
select stuff('ram',2,1,'sri')
select stuff('ram',2,2,'sri')
select stuff('rama',2,3,'sri')
select stuff('ram',1,0,'sri')
select replicate('good',20)
select (' Gokul)
select ('
Gokul SP')
select ltrim(' Gokul)
select rtrim('Gokul ')
Ø '4.Date
Functions'
select getdate()
select dateadd(yy,3,getdate())
select datediff(dd,3,getdate())
select datediff(hh,'2010-08-31',getdate())
select datediff(yy,'2010-08-31',getdate())
select datediff(mm,'2010-08-31',getdate())
select datediff(dd,'2010-08-31',getdate())
select datepart(dd,getdate())
select day(getdATE())
select MONTH(getdATE())
select YEAR(getdATE())
Ø '5.System
Functions 0--> False ,1-->True'
select db_id('Master')
select db_id('sourcedb')
select db_name('23')
select isdate('2/29/2000')
select isdate('2/2')
select isnumeric('4')
select isnumeric('w')
select suser_id('public')
select suser_name(1)
Ø '6.Aggregate
Function'
select * from student
select count(id) from student
select count(*) from student
select max(id) from student
select min(id) from student
select avg(id) from student
select sum(id) from student
Ø 7.Scalar
Functions'
Select UPPER('gokul')
Select Lower('GOKUL')
SELECT LEN('GOKUL')
SELECT
ROUND(25.9056,3)
Operators Used in SQL…
Comparison Operator : Used
for comparing the values .
= , <> , != , < ,> , <= ,
=> , !< ,!>
Range Operator : is
used in a where clause to select a range of data between two values.
Between , Not
Between
List Operator : allows you to
specify multiple values in a where clause.
IN , Not IN
String Operator : is used in a
where Clause to search for a specified pattern in a column from a table.
LIKE NOT LIKE
SELECT * FROM employee WHERE EmployeeName LIKE
'%'
SELECT * FROM employee WHERE EmployeeName LIKE
's%'
SELECT * FROM employee WHERE
EmployeeName LIKE '%m'
SELECT * FROM employee WHERE EmployeeName LIKE
'_a%'
SELECT * FROM employee WHERE EmployeeName LIKE
'[a-k]%'
SELECT * FROM employee WHERE EmployeeName LIKE
'_[a-k]%'
SELECT * FROM employee WHERE EmployeeName LIKE
'[^a-k]%'
SELECT * FROM employee WHERE EmployeeName LIKE
'_[^a-k]%'
Logical Operator : is used to
filter records bsed on one or more condition .
AND = Both Condition should be True .
OR = If any One Condition is True .
Not = If Condition is False .
Contents :
1.Group By Statements
2. Primary Key ,Forreign
Key
3. Constrainst – Primary
Key constraint , Foreign Key constraint , Unique Constraint,
Check Constraint , Default Constraint , Not
Null
4. RULE
5. JOIN in SQL.
6. Views
7. Sub Query
8. Temporary Tables.
Group By Statements
Group by clause is used for grouping the
records of the database table according to our need. We use having clause to
filter data that we get from group by clause . Having clause operates only on
group by clause means to use having clause we need to use group by clause
first. Lets go through both the clauses.
Group By Clause
Group By clause is
used for grouping the records of the database table(s).This clause creates a
single row for each group and this process is called aggregation. To use group by clause we have to use at least one
aggregate function in Select statement. We can use group by clause without where clause.
Syntax :
SELECT Col1, Aggreate_function(col2)
FROM Table_Name
WHERE Condition
GROUP BY Col1, Col2
create table Empdata25 (id int,EmpName char(20),salary bigint,city char(15))
insert Empdata25 values(104,'Dinesh',15000,'Chennai')
insert Empdata25 values(105,'Priya',16000,'Madurai')
insert Empdata25 values(106,'Gokul',27000,'Covai')
select * from Empdata25
update Empdata25 set salary=25000 where EmpName='C'
Having Clause
This clause operates only on group rows
of table(s) and act as a filter like as where clause. We use having clause to
filter data that we get from group by clause. To use having clause we need to
use group by clause first.
select EmpName from Empdata25 group by EmpName.
select * from Empdata25
select EmpName,sum(Salary) from Empdata25 group by EmpName
select * from Empdata25
select EmpName,Min(Salary)as MinimumSalary from
Empdata25 group by
EmpName
select EmpName,Max(Salary)as MinimumSalary from
Empdata25 group by
EmpName
select EmpName,Sum(Salary)as MinimumSalary from
Empdata25 group by
EmpName
select EmpName,Avg(Salary)as MinimumSalary from
Empdata25 group by
EmpName
select EmpName,Count(Salary)as MinimumSalary from
Empdata25 group by
EmpName
select * from Empdata25
select EmpName,min(Salary)as MinimumSalary from
Empdata25 group by
EmpName having min(Salary)>15000
select EmpName,min(Salary)as MinimumSalary from
Empdata25 group by
EmpName having EmpName='priya' or EmpName='Gokul'
select EmpName,min(Salary)as MinimumSalary from
Empdata25 where EmpName='a' or EmpName='Gokul'
select EmpName,min(Salary)as MinimumSalary from
Empdata25 where
EmpName='a' or EmpName='Gokul' group by EmpName
select EmpName,min(Salary)as MinimumSalary from
Empdata25 group
by EmpName where
EmpName='a' or EmpName='Gokul'
select EmpName,min(Salary)as MinimumSalary from
Empdata25 group by
EmpName having EmpName='a'
select EmpName,Avg(Salary)as MinimumSalary from
Empdata25 group by
EmpName
select EmpName,Count(Salary)as MinimumSalary from
Empdata25 group by
EmpName
select EmpName,min(Salary)as MinimumSalary from
Empdata25 group by
EmpName having min(Salary)>20000
select * from Empdata25
select EmpName,min(Salary)as MinimumSalary from
Empdata25 where EmpName='Gokul' or EmpName='priya' group by EmpName having min(Salary)>20000
select EmpName,min(Salary)as MinimumSalary from
Empdata25 where EmpName in('Gokul','Dinesh') group by EmpName having min(Salary)>15000
Points :
- To use Group By Clause, we need to use at least one aggregate function
- All columns that are not used by aggregate function(s) must be in the Group By list
- We can use Group By Clause with or without Where Clause.
- To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause
Keys :
A key is a single or
combination of multiple fields in a table. Its is used to fetch or retrieve
records/data-rows from data table according to the condition/requirement.
Keys are also used to
create relationship among different database tables or views.
Types of SQL
Keys:
We have following types of keys in SQL which
are used to fetch records from tables and to make relationship among tables or
views.
Super Key:
Super key is a set of one or more than one
keys that can be used to identify a record uniquely in a table.Example :
Primary key, Unique key, Alternate key are subset of Super Keys.
Candidate
Key:
A Candidate Key is a
set of one or more fields/columns that can identify a record uniquely in a
table. There can be multiple Candidate Keys in one table. Each Candidate Key
can work as Primary Key.
Primary Key:
Primary key is a set of
one or more fields/columns of a table that uniquely identify a record in
database table. It can not accept null, duplicate values. Only one Candidate
Key can be Primary Key.
Alternate
key:
A Alternate key is a
key that can be work as a primary key. Basically it is a candidate key that
currently is not primary key.
Composite/Compound
Key:
Composite Key is a combination of more than
one fields/columns of a table. It can be a Candidate key, Primary key.
Unique Key:
Unique key is a set of one or more
fields/columns of a table that uniquely identify a record in database table. It
is like Primary key but it can accept only one null value and it can not have
duplicate values.
Foreign Key:
Foreign Key is a field in database table that
is Primary key in another table. It can accept multiple null, duplicate values.
--Primary Key
CREATE TABLE Persons
(
P_Id int ,
FirstName varchar(255),
City varchar(255)
)
select*from Persons
drop table Persons
insert Persons values('','','Covai')
insert Persons values(12,'SaranRaj','Kadaloor')
insert Persons values(13,'Gokul','Chennai')
delete Persons where P_Id=0 or P_Id=4
alter table persons add primary key(P_Id)
alter table persons alter column firstname varchar(255) not null
-- Unique Key
CREATE TABLE Persons
(
P_Id int unique,
FirstName varchar(255),
City varchar(255)
)
CREATE TABLE Persons
(
P_Id int unique,
FirstName varchar(255) unique,
City varchar(255)
)
select*from Persons
drop table Persons
insert Persons values('4','aaa','')
insert Persons values(2,'SaranRaj','Kadaloor')
insert Persons values(3,'Gokul','Chennai')
alter table persons add unique(p_id)
alter table persons add unique(p_id,firstname)
-- Foreign Key
CREATE TABLE Customer
(
P_Id int primary key,
FirstName varchar(255),
City varchar(255)
)
CREATE TABLE orders
(
O_Id int,
OrderName varchar(255),
OrdersId int foreign key references
Customer(p_id)
)
insert Customer values(1,'Peter','Chennai')
insert Customer values(2,'Anil','Chennai')
insert Customer values(3,'Ravi','Tirchi')
insert orders values(101,'Jean',1)
insert orders values(102,'SHOES',2)
insert orders values(103,'Shirt',3)
insert orders values(104,'Mobile',1)
insert orders values(105,'Belts',2)
insert orders values(105,'Belts',5)
select*from Customer
select*from orders
select firstname,City,ordername from Customer join
orders on P_Id=OrdersId
where FirstName='peter'
drop table Customer
drop table orders
Constraints :
CREATE TABLE Persons
(
P_Id int constraint ck primary key,
FirstName varchar(255),
City varchar(255),
)
drop Persons
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255),
constraint ck primary key(p_id)
)
drop Persons
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255),
constraint ck primary key(p_id,firstname)
)
drop Persons
CREATE TABLE Persons--can not
(
P_Id int constraint ck primary key,
FirstName varchar(255)constraint ck primary key,
City varchar(255)
)
drop Persons
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255)
)
drop Persons
--Adding and
dropping constraints by query
alter table persons add constraint ck primary
key(p_id)
alter table persons add constraint ck primary
key(p_id,firstname)
alter table persons drop constraint ck
alter table persons drop ck
CREATE TABLE Persons
(
P_Id int constraint ck unique,
FirstName varchar(255),
City varchar(255),
)
CREATE TABLE Persons
(
P_Id int constraint ck unique,
FirstName varchar(255)constraint ck1 unique,
City varchar(255),
)
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255),
constraint ck unique(p_id,firstname)
)
CREATE TABLE Persons
(
P_Id int constraint ck primary key,
FirstName varchar(255)constraint ck1 unique,
City varchar(255),
)
alter table persons add constraint ck unique(p_id)
alter table persons add constraint ck unique(p_id,firstname)
alter table persons drop constraint ck
alter table persons drop ck
CREATE TABLE Persons
(
P_Id int CHECK (P_Id>1),
FirstName varchar(255),
City varchar(255),
)
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
CREATE TABLE Persons
(
P_Id int constraint ck CHECK (P_Id>0),
FirstName varchar(255),
City varchar(255),
)
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255),
constraint ck CHECK (P_Id>0)
)
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255),
constraint ck CHECK (P_Id>0 and firstname='Gokul')
)
insert Persons values(1,'Gokul','Chennai')
delete Persons
drop table Persons
select*from Persons
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255) DEFAULT 'Chennai'
)
insert Persons(P_Id,FirstName) values(1,'Gokul')
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255) constraint ck DEFAULT 'Chennai'
)
CREATE TABLE Persons
(
P_Id int constraint ck DEFAULT 101,
FirstName varchar(255),
City varchar(255) constraint ck1 DEFAULT 'Chennai'
)
insert Persons(FirstName) values('Gokul')
alter table persons add constraint ck default
'chennai' for
city
alter table persons add constraint ck default
'chennai' for
city,constraint
ck1 default 1 for
p_id
CREATE TABLE Persons
(
P_Id int constraint ck primary key,
FirstName varchar(255),
City varchar(255),
)
CREATE TABLE Orders
(
O_Id int ,
OrderNo int,
Order_Id int constraint ck FOREIGN KEY REFERENCES Persons(P_Id)
)
alter table Orders add constraint ck foreign
key(Order_Id) references persons(p_id)
drop table Persons
select*from Persons
select*from orders
Rule…
The rule
constraint is
used to insert
only a specified range of
values into a column.
create table test1(id int constraint
pk_test primary key,sname varchar(50))
select *from test1
CREATE TABLE Persons
(
P_Id int,
FirstName varchar(255),
City varchar(255)
)
create rule ruleforpersons
as @range between 100 and 1000
sp_bindrule
ruleforpersons,'Persons.p_id'
insert into Persons values(101,'siva','A')
insert into Persons values(102,'sakthi','B')
insert into Persons values(55,'sachin','C')
insert into Persons values(1500,'Ranbhir','D')
sp_unbindrule 'Persons.p_id'
drop rule ruleforpersons
Joins…
join
(or) inner join
--outer join
right join (or) right outer join
left join (or) left outer join
full join (or)full outer join
cross join
--self join :
table joined itself
create table join1(Empid int primary key,EmpName varchar(100),EmpSalary int)
create table join2(Mangid int primary key,MangName varchar(100),MangSalary int)
insert join1(empid,empname,empsalary) values(1,'A',10000),(2,'B',12000),(3,'C',14000),(5,'F',17000),(7,'H',19000)
insert join2(mangid,mangname,mangsalary) values(1,'Aa',11000),(2,'Bb',13000),(4,'Ee',16000),(6,'Gg',18000),(7,'Hh',20000)
select*from join1 join join2 on Empid=Mangid
select*from join1 inner join join2 on Empid=Mangid
select*from join1 inner join join2 on join1.Empid=join2.empid
select*from join1 as j1 inner join join2 as j2 on j1.Empid=j2.empid
select*from join1 right join join2 on Empid=Mangid
select*from join1 right outer join join2 on Empid=Mangid
select*from join1 left join join2 on Empid=Mangid
select*from join1 left outer join join2 on Empid=Mangid
select*from join1 full join join2 on Empid=Mangid
select*from join1 full outer join join2 on Empid=Mangid
select*from join1
select*from join2
select*from join1 cross join join2--not in real time
select*from join1
select*from join2
select empid,empname,mangid,mangname,empsalary + mangsalary as 'Total Cost' from
join1 cross join
join2
create table selftable(EmployeeId
int,EmployeeName
varchar(100),ManagerId int,ManagerName varchar(100))
insert selftable(EmployeeId,EmployeeName,ManagerId,ManagerName) values(1,'A',1,'Aa'),(2,'B',2,'Bb'),(3,'C',4,'Dd'),(5,'F',6,'Gg'),(7,'H',5,'Ff')
select*from selftable
select a.employeeid,a.employeename,b.managerid,b.managername from
selftable a join selftable b on a.EmployeeId=b.ManagerId
select a.employeeid,a.employeename,b.managerid,b.managername from
selftable a right join
selftable b on a.EmployeeId=b.ManagerId
select a.employeeid,a.employeename,b.managerid,b.managername from
selftable a left join
selftable b on a.EmployeeId=b.ManagerId
select a.employeeid,a.employeename,b.managerid,b.managername from
selftable a full join
selftable b on a.EmployeeId=b.ManagerId
select a.employeeid,a.employeename,b.managerid,b.managername from
selftable a, selftable b where a.EmployeeId=b.ManagerId
Views…
-Views are virtual tables that are
compiled at runtime. The data present in the view are not physically stored but
are stored on the underlying base table of the view.
It can be created on one or more
database tables. Generally We put those columns in views that we need to
retrieve/query view like a table.
We can make index triggers on views.
Uses :
In sql server we make views for
security purpose , since it restricts the user to view the selected column(s)
or fields of the tables.
Views shows only those columns that
are present in the query which is used to make views , they provide column wise
security.
One more advantage of views is that
data abstraction,since the end user is not a-ware of all the data present in the
database tables.
-Syntax :
Create view viewname
As
Select columnname1,columnname2…. From
tblname
-Indexed View or Materialized Views :
A standard or Non – Indexed View , is
just a Stored SQL Query.When we try to retrieve dta from view , that data is
actually retrieved from underlying base table. So a view is just virtual Table,it does not store any data by
default.
How ever , when you create an index
on view , the view gets materialized i.e it’s now capable of storing data. In
sql server we call them as indexed views.
Steps To Create Indexed View :
View should be created with SchemaBinding (we cannot change or modify the underlying objects that could affect , functions , stored procedures or views) Option.
Ø If an aggregate function is in the select list , and if there is
possibility for it to return Null ,We Should
Provide a replacement Value for it.
To Replce NULL Values :
Eg. ISNULL((SUMofProducts),o)
as TotalSales
Ø If GroupBy is Specified , the View Select List must contain a
count_Big(*) expression
Ø The base tables in the view , should should be refrenced with 2 part Name
, i.e dbo.TableName
Eg:
Select (ISNULL(SUMofProducts),o) as
TotalSales , count_Big(*) as TotalTransction from dbo.Table1 join
Dbo.tble2 on
dbo.table1.prodid =dbo.table2.CustID groupby Name
-
-
Views are Updatable :
We can Insert , Update and Delete a
Created View which then affects the underlying Base Table , when the View is
creted with one table.
But When we are deling with more than
1 table for creating View , then when updating that view will not return the
results as expected.
Limitation Of Views :
You Cannot pass parameters to a view
.
Rules and Default cannot be
associated with view , because they do not store data by default.
Order by clause is invalid in views
unless Top or for XML is also specified.
Views Cannot be based on Temporary
Tables.
Alter View
viewname
Drop View
Viewname
create table sourcetable(deptid
int primary key,deptname varchar(100))
create table Destinationtable(id
int,name varchar(100),salary int,gender varchar(10),departmentid int foreign key references
sourcetable(deptid))
create view vn as select*from sourcetable
alter view vn as select*from Destinationtable where
name='john'
drop view vn
select * from vn where deptname='it'
create view vn as select id,name,salary,gender,deptname from
sourcetable join Destinationtable
on
sourcetable.deptid=destinationtable.id
create view vn as select id,name,salary,gender,deptname from
sourcetable join Destinationtable
on
sourcetable.deptid=destinationtable.id where sourcetable.deptname='IT'
create view vn as select id,name,gender,deptname from sourcetable join
Destinationtable
on
sourcetable.deptid=destinationtable.id
create view vn as select deptname,COUNT(deptid)as totalemployees from sourcetable join
Destinationtable
on
sourcetable.deptid=destinationtable.id group by deptname
**************************************************************************************************************************
update vn set name='John' where name='johny'
delete vn where id=6
insert vn values(6,'Jeny',3500,'Female',4)
**************************************************************************************************************************
create table productsource(productid
int primary key,productname varchar(100),unitprice int)
create table productdestination(pid
int foreign key references
productsource(productid),quantitysold
int)
SELECT * FROM productsource
SELECT * FROM
productdestination
insert
productsource values(1,'A',20)
insert
productsource values(2,'B',14)
insert
productsource values(3,'C',11)
insert
productsource values(4,'D',10)
insert
productdestination values(1,10)
insert
productdestination values(3,23)
insert
productdestination values(4,21)
insert
productdestination values(2,12)
insert
productdestination values(1,13)
insert
productdestination values(3,12)
insert
productdestination values(4,13)
insert
productdestination values(1,11)
insert
productdestination values(2,12)
insert
productdestination values(1,14)
create view vnDEMO with schemabinding as select productname,UNITPRICE
from dbo.productsource
insert into vnDEMO vAlues(6,'E',50)
create view vn with schemabinding as select productname,sum(isnull(quantitysold*unitprice,0))as totalsales,COUNT_BIG(*) as
totaltransactions from dbo.productsource join
dbo.productdestination
on dbo.productdestination.pid=dbo.productsource.productid group by productname
create unique clustered index ix on vnDEMO(productname)
select *from vnDEMO
Stored procedures…..
A stored procedure is group of T-SQL (Transact SQL) statements. If you have a situation, where you write the same query over and over again, you can save that specific query as a stored procedure and all by just its name.
Creating a simple stored procedure without any parameters: This stored procedure, retrieves Name and Gender of all the employees. To create a stored procedure we use,CREATE PROCEDURE or CREATE PROC statement.
Create Procedure spGetEmployees
as
Begin
Select Name, Gender from tblEmployee
End
Note: When naming user defined stored procedures, Microsoft recommends not to use"sp_" as a prefix. All system stored procedures, are prefixed with "sp_". This avoids any ambiguity between user defined and system stored procedures and any conflicts, with some future system procedure.
To execute the stored procedure, you can just type the procedure name and press F5, or use EXEC or EXECUTE keywords followed by the procedure name as shown below.
1. spGetEmployees
2. EXEC spGetEmployees
3. Execute spGetEmployees
Note: You can also right click on the procedure name, in object explorer in SQL Server Management Studio and select EXECUTE STORED PROCEDURE.
Creating a stored procedure with input parameters: This SP, accepts GENDER and DEPARTMENTID parameters. Parameters and variables have an @ prefix in their name.
Create Procedure spGetEmployeesByGenderAndDepartment
@Gender nvarchar(50),
@DepartmentId int
as
Begin
Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId
End
To invoke this procedure, we need to pass the value for @Gender and @DepartmentId parameters. If you don't specify the name of the parameters, you have to first pass value for @Gender parameter and then for @DepartmentId.
EXECUTE spGetEmployeesByGenderAndDepartment 'Male', 1
On the other hand, if you change the order, you will get an error stating "Error converting data type varchar to int." This is because, the value of "Male" is passed into @DepartmentId parameter. Since @DepartmentId is an integer, we get the type conversion error.
spGetEmployeesByGenderAndDepartment 1, 'Male'
When you specify the names of the parameters when executing the stored procedure the order doesn't matter.
EXECUTE spGetEmployeesByGenderAndDepartment @DepartmentId=1, @Gender ='Male'
To view the text, of the stored procedure
1. Use system stored procedure sp_helptext 'SPName'
OR
2. Right Click the SP in Object explorer -> Scrip Procedure as -> Create To -> New Query Editor Window
To change the stored procedure, use ALTER PROCEDURE statement:
Alter Procedure spGetEmployeesByGenderAndDepartment
@Gender nvarchar(50),
@DepartmentId int
as
Begin
Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId order by Name
End
To encrypt the text of the SP, use WITH ENCRYPTION option. Once, encrypted, you cannot view the text of the procedure, using sp_helptext system stored procedure. There are ways to obtain the original text, which we will talk about in a later session.
Alter Procedure spGetEmployeesByGenderAndDepartment
@Gender nvarchar(50),
@DepartmentId int
WITH ENCRYPTION
as
Begin
Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId
End
To delete the SP, use DROP PROC 'SPName' or DROP PROCEDURE 'SPName'
How to Decrypt
Alter the procedure
removing the encryption keyword as below
Alter Procedure spGetEmployeesByGenderAndDepartment
@Gender nvarchar(50),
@DepartmentId int
as
Begin
Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId
End
Alter Procedure spGetEmployeesByGenderAndDepartment
@Gender nvarchar(50),
@DepartmentId int
as
Begin
Select Name, Gender from tblEmployee Where Gender = @Gender and DepartmentId = @DepartmentId
End
Stored Procedure with Out Parameter
To create an SP with output parameter, we use the keywords OUT or OUTPUT. @EmployeeCount is an OUTPUT parameter. Notice, it is specified with OUTPUT keyword.
Create Procedure spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output
as
Begin
Select @EmployeeCount = COUNT(Id)
from tblEmployee
where Gender = @Gender
End
To execute this stored
procedure with OUTPUT parameter
1. First initialise a variable of the same datatype as that of the output parameter. We have declared @EmployeeTotal integer variable.
2. Then pass the @EmployeeTotal variable to the SP. You have to specify the OUTPUTkeyword. If you don't specify the OUTPUT keyword, the variable will be NULL.
3. Execute
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Female', @EmployeeTotal output
Print @EmployeeTotal
If you don't specify the OUTPUT keyword, when executing the stored procedure, the @EmployeeTotal variable will be NULL. Here, we have not specified OUTPUT keyword. When you execute, you will see '@EmployeeTotal is null' printed.
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Female', @EmployeeTotal
if(@EmployeeTotal is null)
Print '@EmployeeTotal is null'
else
Print '@EmployeeTotal is not null'
You can pass parameters in any order, when you use the parameter names. Here, we are first passing the OUTPUT parameter and then the input @Gender parameter.
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender @EmployeeCount = @EmployeeTotal OUT, @Gender = 'Male'
Print @EmployeeTotal
The following system stored procedures, are extremely useful when working procedures.
sp_help SP_Name : View the information about the stored procedure, like parameter names, their datatypes etc. sp_help can be used with any database object, like tables, views, SP's, triggers etc. Alternatively, you can also press ALT+F1, when the name of the object is highlighted.
sp_helptext SP_Name : View the Text of the stored procedure
sp_depends SP_Name : View the dependencies of the stored procedure. This system SP is very useful, especially if you want to check, if there are any stored procedures that are referencing a table that you are abput to drop. sp_depends can also be used with other database objects like table etc.
Note: All parameter and variable names in SQL server, need to have the @symbol.
1. First initialise a variable of the same datatype as that of the output parameter. We have declared @EmployeeTotal integer variable.
2. Then pass the @EmployeeTotal variable to the SP. You have to specify the OUTPUTkeyword. If you don't specify the OUTPUT keyword, the variable will be NULL.
3. Execute
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Female', @EmployeeTotal output
Print @EmployeeTotal
If you don't specify the OUTPUT keyword, when executing the stored procedure, the @EmployeeTotal variable will be NULL. Here, we have not specified OUTPUT keyword. When you execute, you will see '@EmployeeTotal is null' printed.
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender 'Female', @EmployeeTotal
if(@EmployeeTotal is null)
Print '@EmployeeTotal is null'
else
Print '@EmployeeTotal is not null'
You can pass parameters in any order, when you use the parameter names. Here, we are first passing the OUTPUT parameter and then the input @Gender parameter.
Declare @EmployeeTotal int
Execute spGetEmployeeCountByGender @EmployeeCount = @EmployeeTotal OUT, @Gender = 'Male'
Print @EmployeeTotal
The following system stored procedures, are extremely useful when working procedures.
sp_help SP_Name : View the information about the stored procedure, like parameter names, their datatypes etc. sp_help can be used with any database object, like tables, views, SP's, triggers etc. Alternatively, you can also press ALT+F1, when the name of the object is highlighted.
sp_helptext SP_Name : View the Text of the stored procedure
sp_depends SP_Name : View the dependencies of the stored procedure. This system SP is very useful, especially if you want to check, if there are any stored procedures that are referencing a table that you are abput to drop. sp_depends can also be used with other database objects like table etc.
Note: All parameter and variable names in SQL server, need to have the @symbol.
Advantages of using stored procedures
The following advantages of using Stored
Procedures over adhoc queries (inline SQL)
1. Execution plan retention
and reusability - Stored Procedures are compiled and their execution plan is
cached and used again, when the same SP is executed again. Although adhoc
queries also create and reuse plan, the plan is reused only when the query is
textual match and the datatypes are matching with the previous call. Any change
in the datatype or you have an extra space in the query then, a new plan is
created.
2. Reduces network traffic - You
only need to send, EXECUTE SP_Name statement, over the network, instead of the
entire batch of adhoc SQL code.
3. Code reusability and
better maintainability - A stored procedure can
be reused with multiple applications. If the logic has to change, we only have
one place to change, where as if it is inline sql, and if you have to use it in
multiple applications, we end up with multiple copies of this inline sql. If
the logic has to change, we have to change at all the places, which makes it
harder maintaining inline sql.
4. Better Security - A
database user can be granted access to an SP and prevent them from executing
direct "select" statements against a table. This is fine grain
access control which will help control what data a user has access to.
5. Avoids SQL Injection
attack - SP's prevent sql injection attack.
create table empsp (id bigint,name varchar(50),Sex varchar(50))
select * from empsp
--Sp without
Parameters
create procedure proname
as
begin
select * from empsp
end
exec proname
--Sp with input
Parameters
create procedure inputpro
@empid bigint
as
begin
select * from empsp where id=@empid
end
exec inputpro 1
--Sp with Output
Parameters
create Procedure spGetEmployeeCountByGender
@sex nvarchar(20),
@EmployeeCount int Output
as
Begin
Select
@EmployeeCount = COUNT(Id)
from empsp
where Sex = @sex
End
exec
spGetEmployeeCountByGender 'female',?
--1) First
initialise a variable of the same datatype as that of the output parameter. We
have declared
--2. Then pass
the @EmployeeTotal variable to the SP. You have to specify the OUTPUTkeyword.
If you don't specify the OUTPUT keyword, the variable will be NULL.
--3. Execute
Declare
@EmployeeTotal int
Execute
spGetEmployeeCountByGender 'Female',
@EmployeeTotal output
Print
@EmployeeTotal
Triggers in -SQL
Types of DML Triggers
1. After Trigger (using FOR/AFTER CLAUSE)
This trigger fires after SQL Server completes the execution of the action successfully that fired it.
Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.
2. Instead of Trigger (using INSTEAD OF CLAUSE)
This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delet to the table.
Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
Inserted, Deleted Logical(Magical)table in SQL Server
There are Inserted and Deleted logical tables in SQL Server. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.
Use of logical tables
Basically, logical tables are used by triggers for the following purpose:
1. To test data manipulation errors and take suitable actions based on the errors.
2. To find the difference between the state of a table before and after the data modification and take actions based on that difference.
Inserted logical Table
The Inserted table holds the recently inserted or updated values means new data values. Hence newly added and updated records are inserted into the Inserted table.
Suppose we have Employee table as shown in fig. Now We need to create two triggers to see data with in logical tables Inserted and Deleted.
Deleted logical Table
The Deleted table holds the recently deleted or updated values means old data values. Hence old updated and deleted records are inserted into the Deleted table.
CREATE TABLE Employee_Demo
(
Emp_ID int identity,
Emp_Name varchar(55),
Emp_Sal decimal (10,2)
)
-- Now Insert records
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);
Insert into Employee_Demo values ('Riyaz',1400);
--Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create table Employee_Demo_Audit
create table Employee_Demo_Audit(
Emp_ID int,
Emp_Name varchar(55),
Emp_Sal decimal(10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime)
After Insert Trigger
-- Create trigger on table Employee_Demo for Insert statement
CREATE TRIGGER trgAfterInsert
on Employee_Demo
FOR INSERT
AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='New employee record inserted';
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
After Update Trigger
Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
FOR UPDATE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i; if update(Emp_Name)
set @audit_action='Update Record --- After Update Trigger.';
if update (Emp_Sal)
set @audit_action='Update Record --- After Update Trigger.';
insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER UPDATE trigger fired.'
After Delete Trigger
-- Create trigger on table Employee_Demo for Delete statement
CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
FOR DELETE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
select @audit_action='Deleted -- After Delete Trigger.';
insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER DELETE TRIGGER fired.'
--Output will be
Instead of Insert Trigger
-- Create trigger on table Employee_Demo for Insert statement
create TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
INSTEAD OF Insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
BEGIN
BEGIN TRAN
SET NOCOUNT ON
if(@emp_sal<=1000)
begin
RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
else begin Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal);
Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());
COMMIT;
PRINT 'Record Inserted -- Instead Of Insert Trigger.'
END
END
insert into Employee_Demo values ('gokul',900)
select * from Employee_Demo_Audit
Instead of Update Trigger
-- Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
INSTEAD OF Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
BEGIN
BEGIN TRAN
if(@emp_sal>=1000)
begin
RAISERROR('Cannot update records where salary < 1000'); ROLLBACK; end
else begin
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
COMMIT;
PRINT 'Record Updated -- Instead Of Update Trigger.';
END
END
1. update Employee_Demo set Emp_Sal = '1400' where emp_id = 6
2. update Employee_Demo set Emp_Sal = '900' where emp_id = 6
3. select * from Employee_Demo
4. select * from Employee_Demo_Audit
Instead of Delete Trigger
-- Create trigger on table Employee_Demo for Delete statement
CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
INSTEAD OF DELETE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
BEGIN TRAN if(@empsal>1200) begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else begin
delete from Employee_Demo where Emp_ID=@empid;
COMMIT;
insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.' end END
1. DELETE FROM Employee_Demo where emp_id = 1
2. DELETE FROM Employee_Demo where emp_id = 3
3. select * from Employee_Demo
4. select * from Employee_Demo_Audit
SQL Server User Defined Functions :
Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).
These functions are created by user in system database or in user defined database. We three types of user defined functions.
Scalar Function
User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.
--Create a table
CREATE TABLE Employee
(
EmpID int PRIMARY KEY,
FirstName varchar(50) NULL,
LastName varchar(50) NULL,
Salary int NULL,
Address varchar(100) NULL,
)
--Insert Data
Insert into -Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
--See created table
Select * from Employee
--Create function to get emp full name
Create function fnGetEmpFullName
(
@FirstName varchar(50),
@LastName varchar(50)
)
returns varchar(101)
As
Begin return (Select @FirstName + ' '+ @LastName);
end
--Calling the above created function
Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee
Examplescreate function fn(@dob date)
returns int
as
begin
declare @age int
set @age=DATEDIFF(YEAR,@dob,GETDATE())-
case
when (MONTH(@dob)>MONTH(GETDATE()) or (MONTH(@dob)=MONTH(GETDATE())
and day(@dob)>DAY(getdate())))
then 1
else 0
end
return @age
end
--how to execute the function
select Name,ID,DOB,dbo.fn(DOB)as Age from tblemployee
select dbo.fn('12/23/2000')
*********************************************************************
create function salary(@payrate float)
returns float
as
begin
return(@payrate*8*10)
end
select dbo.salary(1000)
--or
declare @payrate float
set @payrate=dbo.salary(125.50)
print @payrate
***********************************************************************
create function addition(@a int,@b int)
returns int
as
begin
return(@a+@b)
end
select dbo.addition(1,2)
drop functionaddition,salary
Inline Table-Valued Function
User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.
--Create function to get employees
Create function fnGetEmployee()
returns Table
As
return (Select * from Employee)
--Now call the above created function
Select * from fnGetEmployee()
Multi-Statement Table-Valued Function
User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.
--Create function for EmpID,FirstName and Salary of Employee
Create function fnGetMulEmployee()
returns @Emp Table
(
EmpID int,
FirstName varchar(50),
Salary int
)
As
begin
Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
--Now update salary of first employee
update @Emp set Salary=25000 where EmpID=1;
--It will update only in @Emp table not in Original Employee table
return
end
--Now call the above created function
Select * from fnGetMulEmployee()
--Now see the original table. This is not affected by above function update command
Select * from Employee
Note:
Unlike Stored Procedure, Function returns only single value.
Unlike Stored Procedure, Function accepts only input parameters.
Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
Like Stored Procedure, Function can be nested up to 32 level.
User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.
User Defined Function can't returns XML Data Type.
User Defined Function doesn't support Exception handling.
User Defined Function can call only Extended Stored Procedure.
User Defined Function doesn't support set options like set ROWCOUNT etc.
SubQuery
What is a SubQuery?
SubQuery can be treated as a ‘query on query’. A subquery is the inner query which provides a targeted result to the outer main query . We can try few examples to learn it
Example:
select employee name with its manager name
Hide Copy Code
select emp.name,(select mgr.name from employee AS mgr where emp.mgrid=mgr.empid) from employee AS emp
We mostly see subqueries in where clause like – select employees having average salary
Hide Copy Code
select * from employee where salary=(select AVG(salary) from employee)
What is Correlated SubQuery?
A correlated sub-query is a sub-query that uses values from the outer query in its WHERE clause.Let’s try with an example
select employees having salary greater than average salary of employees of department ‘IT’
Hide Copy Code
select * from employee where salary=(select AVG(salary) from employee where department=’IT’)
The main difference is that the subquery will be executed for each row before the result can be used by outer query.
Why do we require SubQuery or advantages of SubQuery?
SubQuery holds the results like a temporary table which can be used by outer query.
SubQuery are easier to understand
SubQuery breaks down a complex query into small and simple queries.
SubQuery are easy to use as a replacement of joins.There is no major difference in performance.
SubQuery Rules
A subquery is subject to the following restrictions:
- Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query
- If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output
- The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
- If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
- The ntext, text, and image data types cannot be used in the select list of subqueries.
- Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
- The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
- The COMPUTE and INTO clauses cannot be specified.
- ORDER BY can only be specified when TOP is also specified.
- A view created by using a subquery cannot be updated.
- The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are the same as those for a standard select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.
ref:http://msdn.microsoft.com/en-us/library/ms189543(v=sql.105).aspx
Join Vs SubQueries
I was looking for this answer and though it’s not a verified answer but yes,it’s true in most cases. refer this :http://stackoverflow.com/questions/2577174/join-vs-subquery
In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.
In JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.
The good thing in sub-queries is that they are more readable than JOINs: that’s why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too.
create table Student (Studentid int identity(1,1), Firstname nvarchar(200),
Lastname nvarchar(200),Email nvarchar(100))
create table Course (Courseid int identity(1,1), Coursename nvarchar(250), CourseAdmin int)
insert into Student values ('Atul','Bajaj', 'atul@abc.com')
insert into Student values ('Vivek','Johari', 'vivek@abc.com' )
insert into Student values ('Ankur','Johari', 'ankur@abc.com' )
insert into Student values ('Tarveen', 'Kaur', 'Tarveen@abc.com')
Insert into Course values('Oracle',2)
Insert into Course values('Automation',4)
Insert into Course values('Java',2)
Insert into Course values('QTP',4)
select*from Student
select*from Course
select Firstname+' '+Lastname as fullname from student where studentid =
(select CourseAdmin from course where coursename ='Oracle')
--select Firstname+' '+Lastname from student where studentid in (2)
************************************************************************************************************
create table StudentCourse(StudentCourseid int identity(1,1), Studentid int, Courseid int)
Insert into StudentCourse values(1,3)
Insert into StudentCourse values(2,1)
Insert into StudentCourse values(3,2)
Insert into StudentCourse values(4,4)
select*from StudentCourse
select*from Student
select*from Course
select Firstname, lastname from student where studentid in
(select studentid from studentcourse where courseid in
(select courseid from course where coursename='Oracle'))
--select Firstname, lastname from student where studentid in
--(select studentid from studentcourse where courseid in
--(1))
select*from Student
select*from Course
select Coursename ,Courseadmin,(select Firstname+' '+Lastname from student
where studentid=Course.courseadmin)as CourseAdminName from course.
Difference between Subquery, Nested Subquery and Correlated Subquery
Query: - Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.
Suppose we have a two tables Student and courses whose structure is given below:-
create table Student (Studentid int identity(1,1), Firstname nvarchar(200),Lastname nvarchar(200),Email nvarchar(100))
create table Course (Courseid int identity(1,1), Coursename nvarchar(250),CourseAdmin int)
Now suppose we insert the following data into these tables:-
For table Student
insert into Student values ('Atul','Bajaj', 'atul@abc.com' )
insert into Student values ('Vivek','Johari', 'vivek@abc.com' )
insert into Student values ('Ankur','Johari', 'ankur@abc.com' )
insert into Student values ('Tarveen', 'Kaur', 'Tarveen@abc.com')
For table Course
Insert into Course values('Oracle',2)
Insert into Course values('Automation',4)
Insert into Course values('Java',2)
Insert into Course values('QTP',4)
Now the query to see all the data from the table student and course is given below:-
Select * from student
Select * from Course
Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.
For example, if we want to find the name of the course Admin of the course “Oracle”, then the following subquery will be used:-
select Firstname+' '+Lastname from student where studentid in (selectcourseadminid from course where coursename ='Oracle')
Result:-
In this example, the sql statement “select courseadminid from coursewhere coursename ='Oracle'” is a subquery.
Nested Subquery:-If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.
Let us suppose we have another table called “StudentCourse” which contains the information, which student is connected to which Course. The structure of the table is:-
create table StudentCourse( StudentCourseid int identity(1,1), Studentid int,Courseid int)
The Query to insert data into the table “Studentcourse” is
Insert into StudentCourse values(1,3)
Insert into StudentCourse values(2,1)
Insert into StudentCourse values(3,2)
Insert into StudentCourse values(4,4)
Note: - We don’t need to insert data for the column StudentCourseid since it is an identity column.
Now, if we want to get the list of all the student which belong to the course “Oracle”, then the query will be,
select Firstname, lastname from student where studentid in (select studentidfrom studentcourse where courseid in (select courseid from course wherecoursename='Oracle'))
Result:-
In this example we use the nested subquery since the subquery “select courseidfrom course where coursename='Oracle'” is itself contained in the another subquery(Parent Subquery) “select studentid from studentcourse wherecourseid in (select courseid from course where coursename='Oracle')”.
Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.
Suppose we want to get the details of the Courses (including the name of their course admin) from the Course table, we can use the following query:-
select Coursename ,Courseadminid,(select Firstname+' '+Lastname fromstudent where studentid=Course.courseadminid)as CourseAdminName fromcourse
Result:-
Here in this example the “select Firstname+' '+Lastname from student wherestudentid=Course.courseadminid” is called the correlated subquery since the outcome of this subquery is depends on the column courseadminid of the parent query. This means that the correlated subquery will be executed for each row selected by the parent query.
It is not necessary that the column on which the correlated query is depended is included in the selected columns list of the parent query. For example the below query will also works even the column courseadminid on which the correlated query is depends , is not included in the selected columns list of the parent query.
select Coursename ,(select Firstname+' '+Lastname from student wherestudentid=Course.courseadminid)as CourseAdminName from course
Results:-
SESSION 5
SQL Server - Cursors
Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row.
Life Cycle of Cursor
Declare Cursor
A cursor is declared by defining the SQL statement that returns a result set.Open
A Cursor is opened and populated by executing the SQL statement defined by the cursor.Fetch
When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.Close
After data manipulation, we should close the cursor explicitly.Deallocate
Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.
Cursor is a pointer for easy traversal.
There are two types of cursor.
1.ANSI Cursor
--> Single row
Forward only Cursor -- It will return -1 (You can move next(forward)only
2.Enhanced Cursor
scroll cursor--> Multiple rows.It can move either direction.
You can use Next,Prior,last,first,Absoloute,relative
Query
create table empsiva3(empno int,ename varchar(20))
insert into empsiva3 values(1,'siva')
insert into empsiva3 values(2,'Ranbhir')
insert into empsiva3 values(3,'Imrankhan')
insert into empsiva3 values(4,'sakthi')
insert into empsiva3 values(5,'A')
insert into empsiva3 values(6,'B')
insert into empsiva3 values(7,'C')
insert into empsiva3 values(8,'D')
insert into empsiva3 values(9,'E')
insert into empsiva3 values(10,'F')
select * from empsiva3
Declare A cursor
for select * from empsiva3
open A
fetch next from A --> Run this for mutiple times...
close A
deallocate A
'Scroll Cursor'
'You can use Next,Prior,last,first,Absoloute,relative '
Declare A scroll cursor
for select * from empsiva3
open A
fetch next from A
fetch first from A
fetch last from A
fetch prior from A
fetch absolute 10 from A
fetch relative 2 from A
close A
deallocate A
SQL Coalesce function
COALESCE "returns the first nonnull expression among its arguments..
create table tblforcoalesce(id int,Firstname varchar(100),MiddleName varchar(100),LastName varchar(100))
insert tblforcoalesce values(1,'A','B',null)
insert tblforcoalesce values(2,'A',null,'B')
insert tblforcoalesce values(3,null,'B','C')
insert tblforcoalesce values(4,'A',null,null)
insert tblforcoalesce values(5,null,null,'D')
insert tblforcoalesce values(6,null,null,null)
select*from tblforcoalesce
select id,coalesce(firstname,middlename,lastname)as Name from tblforcoalesce
Indexes
are database objects which can be created in one or more columns . It will Improve the performance of data retrieval .
Types
1 - Clustered Index:
A Primary key created for that column will create a clustered index for that column . A table can have only one clustered index on it.
create table tblemp (id int , name varchar(15))
create index empindexname
On tblemp (id)
drop index empindexname on tblemp
2 - Non Clustered Index:
are database objects which can be created in one or more columns . It will Improve the performance of data retrieval .
Types
1 - Clustered Index:
A Primary key created for that column will create a clustered index for that column . A table can have only one clustered index on it.
create table tblemp (id int , name varchar(15))
create index empindexname
On tblemp (id)
drop index empindexname on tblemp
2 - Non Clustered Index:
is useful for columns that have some repeated values . Non Cluster Index are not automatically created.
Rank function skips ranking(s) if there is a tie where as Dense_Rank will not.
For example : If you have 2 rows at rank 1 and you have 5 rows in total.
Syntax :
A table or view can contain the following types of indexes:
- Clustered
- Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
- The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
- Nonclustered
- Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
- The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
- You can add nonkey columns to the l-eaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.
What is the difference between a Clustered and Non Clustered Index?
A clustered index determines the order in which the rows of a table are stored on disk. If a table has a clustered index, then the rows of that table will be stored on disk in the same exact order as the clustered index. An example will help clarify what we mean by that.
An example of a clustered index
-Suppose we have a table named Employee which has a column named EmployeeID. Let’s say we create a clustered index on the EmployeeID column. What happens when we create this clustered index? Well, all of the rows inside the Employee table will be Physically – sorted (on the actual disk) – by the values inside the EmployeeID column. What does this accomplish? Well, it means that whenever a lookup/search for a sequence of EmployeeID’s is done using that clustered index, then the lookup will be much faster because of the fact that the sequence of employee ID’s are physically stored right next to each other on disk – that is the advantage with the clustered index. This is because the rows in the table are sorted in the exact same order as the clustered index, and the actual table data is stored in the leaf nodes of the clustered index.
Remember that an index is usually a tree data structure – and leaf nodes are the nodes that are at the very bottom of that tree. In other words, a clustered index basically contains the actual table level data in the index itself. This is very different from most other types of indexes as you can read about below.
When would using a clustered index make sense?
Let’s go through an example of when and why using a clustered index would actually make sense. Suppose we have a table named Owners and a table named Cars. This is what the simple schema would look like – with the column names in each table:
Owners Owner_Name Owner_Age Cars Car_Type Owner_Name
Let’s assume that a given owner can have multiple cars – so a single Owner_Name can appear multiple times in the Cars table.
Now, let’s say that we create a clustered index on the Owner_Name column in the Cars table. What does this accomplish for us? Well, because a clustered index is stored physically on the disk in the same order as the index, it would mean that a given Owner_Name would have all his/her car entries stored right next to each other on disk. In other words, if there is an owner named “Joe Smith” or “Raj Gupta”, then each owner would have all of his/her entries in the Cars table stored right next to each other on the disk.
Now, let’s say that we create a clustered index on the Owner_Name column in the Cars table. What does this accomplish for us? Well, because a clustered index is stored physically on the disk in the same order as the index, it would mean that a given Owner_Name would have all his/her car entries stored right next to each other on disk. In other words, if there is an owner named “Joe Smith” or “Raj Gupta”, then each owner would have all of his/her entries in the Cars table stored right next to each other on the disk.
When is using a clustered index an advantage?
Well, suppose that there is a frequently run query which tries to find all of the cars belonging to a specific owner. With the clustered index, since all of the car entries belonging to a single owner would be right next to each other on disk,the query will run much faster than if the rows were being stored in some random order on the disk. And that is the key point to remember!
Why is it called a clustered index?
In our example, all of the car entries belonging to a single owner would be right next to each other on disk. This is the “clustering”, or grouping of similar values, which is referred to in the term “clustered” index.
Note that having an index on the Owner_Name would not necessarily be unique, because there are many people who share the same name. So, you might have to add another column to the clustered index to make sure that it’s unique.
What is a disadvantage to using a clustered index?
A disadvantage to using a clustered index is the fact that if a given row has a value updated in one of it’s (clustered) indexed columns what typically happens is that the database will have to move the entire row so that the table will continue to be sorted in the same order as the clustered index column. Consider our example above to clarify this. Suppose that someone named “Rafael Nadal” buys a car – let’s say it’s a Porsche – from “Roger Federer”. Remember that our clustered index is created on the Owner_Name column. This means that when we do a update to change the name on that row in the Cars table, the Owner_Name will be changed from “Roger Federer” to “Rafael Nadal”.
But, since a clustered index also tells the database in which order to physically store the rows on disk, when the Owner_Name is changed it will have to move an updated row so that it is still in the correct sorted order. So, now the row that used to belong to “Roger Federer” will have to be moved on disk so that it’s grouped (or clustered) with all the car entries that belong to “Rafael Nadal”. Clearly, this is a performance hit. This means that a simple UPDATE has turned into a DELETE and then an INSERT – just to maintain the order of the clustered index. For this exact reason, clustered indexes are usually created on primary keys or foreign keys, because of the fact that those values are less likely to change once they are already a part of a table.
A comparison of a non-clustered index with a clustered index with an example
As an example of a non-clustered index, let’s say that we have a non-clustered index on the EmployeeID column. A non-clustered index will store both the value of the EmployeeID AND a pointer to the row in the Employee table where that value is actually stored. But a clustered index, on the other hand, will actually store the row data for a particular EmployeeID – so if you are running a query that looks for an EmployeeID of 15, the data from other columns in the table like EmployeeName, EmployeeAddress, etc. will all actually be stored in the leaf node of the clustered index itself.
This means that with a non-clustered index extra work is required to follow that pointer to the row in the table to retrieve any other desired values, as opposed to a clustered index which can just access the row directly since it is being stored in the same order as the clustered index itself. So, reading from a clustered index is generally faster than reading from a non-clustered index.
A table can have multiple non-clustered indexes
A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes.
Why can a table have only one clustered index?
Because a clustered index determines the order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored? Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.
Summary of the differences between clustered and non-clustered indexes
Here’s a summary of the differences:
- A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.
- Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speeds up retrieval because the data lives close to each other on disk. Also, if data is accessed in the same order as the clustered index, the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index.
- A clustered index can be a disadvantage because any time a change is made to a value of an indexed column, the subsequent possibility of re-sorting rows to maintain order is a definite performance hit.
- A table can have multiple non-clustered indexes. But, a table can have only one clustered index.
- Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the clustered index actually stores the row-level data in it’s leaf nodes.
create table tblindex(id int,name varchar(100),city varchar(100))
CREATE INDEX ix
ON table_name (column_name)
CREATE INDEX ix
ON tblindex (city)
CREATE INDEX ix1
ON tblindex (city desc)
select city from tblindex
drop index ix1 on tblindex
(or)
drop index tblindex.ix1
create table tblindex(id int,name varchar(100),city varchar(100)primary key)
CREATE clustered INDEX cix
ON tblindex (city)
CREATE nonclustered INDEX ncix
ON tblindex (id)
CREATE nonclustered INDEX ncix1
ON tblindex (city)
create unique index uix
on tblindex(city)
create unique index uix1
on tblindex(id)
create unique index uix2
on tblindex(id)with (ignore_dup_key=on)
Union and Union All
UNION and UNION ALL operators in SQL Server, are used to combine the result-set of two or more SELECT queries.
create table customers(customerid int,customername nvarchar(100),city nvarchar(100))
create table suppliers(supplierid int,suppliername nvarchar(100),city nvarchar(100))
insert customers values(1,'A','Zz')
insert customers values(2,'B','Y')
insert customers values(3,'C','X')
insert suppliers values(3,'Z','A')
insert suppliers values(2,'Y','B')
insert suppliers values(1,'X','C')
delete customers where customername='Aa'
select*from customers
select*from suppliers
SELECT customerid,customername FROM customers
UNION
SELECT supplierid,suppliername FROM suppliers
SELECT customerid,customername FROM customers
UNION All
SELECT supplierid,suppliername FROM suppliers
SELECT customerid,customername FROM customers
UNION
SELECT supplierid,suppliername FROM suppliers
order by customername
SELECT customerid,customername FROM customers
UNION All
SELECT supplierid,suppliername FROM suppliers
order by customername
SELECT customerid,customername FROM customers
where customerid=1
UNION
SELECT supplierid,suppliername FROM suppliers
where supplierid=1
order by customername
Differences between UNION and UNION ALL (Common Interview Question)
UNION removes duplicate rows, where as UNION ALL does not. When use UNION, to remove the duplicate rows, sql server has to to do a distinct sort, which is time consuming. For this reason, UNION ALL is much faster than UNION.
Note: If you want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.
Note: For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be same
Note: If you want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.
Note: For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be same
Difference between JOIN and UNION
JOINS and UNIONS are different things. However, this question is being asked very frequently now. UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries in the union, where as JOINS, retrieve data from two or more tables based on logical relationships between the tables. In short, UNION combines rows from 2 or more tables, where JOINS combine columns from 2 or more table.
JOINS and UNIONS are different things. However, this question is being asked very frequently now. UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries in the union, where as JOINS, retrieve data from two or more tables based on logical relationships between the tables. In short, UNION combines rows from 2 or more tables, where JOINS combine columns from 2 or more table.
Rank and Dense_Rank functions
- Returns a rank starting at 1 based on the ordering of rows imposed by the ORDER BY clause
- ORDER BY clause is required
- PARTITION BY clause is optional
- When the data is partitioned, rank is reset to 1 when the partition changes
Rank function skips ranking(s) if there is a tie where as Dense_Rank will not.
For example : If you have 2 rows at rank 1 and you have 5 rows in total.
RANK() returns - 1, 1, 3, 4, 5
DENSE_RANK returns - 1, 1, 2, 3, 4
Syntax :
RANK() OVER (ORDER BY Col1, Col2, ...)
DENSE_RANK() OVER (ORDER BY Col1, Col2..)
SQl Script to create Employees table
RANK() and DENSE_RANK() functions without PARTITION BY clause : In this example, data is not partitioned, so RANK() function provides a consecutive numbering except when there is a tie. Rank 2 is skipped as there are 2 rows at rank 1. The third row gets rank 3.
DENSE_RANK() on the other hand will not skip ranks if there is a tie. The first 2 rows get rank 1. Third row gets rank 2.
Create Table Employees
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)
Go
Insert Into Employees Values (1, 'Mark', 'Male', 8000)
Insert Into Employees Values (2, 'John', 'Male', 8000)
Insert Into Employees Values (3, 'Pam', 'Female', 5000)
Insert Into Employees Values (4, 'Sara', 'Female', 4000)
Insert Into Employees Values (5, 'Todd', 'Male', 3500)
Insert Into Employees Values (6, 'Mary', 'Female', 6000)
Insert Into Employees Values (7, 'Ben', 'Male', 6500)
Insert Into Employees Values (8, 'Jodi', 'Female', 4500)
Insert Into Employees Values (9, 'Tom', 'Male', 7000)
Insert Into Employees Values (10, 'Ron', 'Male', 6800)
Go
RANK() and DENSE_RANK() functions without PARTITION BY clause : In this example, data is not partitioned, so RANK() function provides a consecutive numbering except when there is a tie. Rank 2 is skipped as there are 2 rows at rank 1. The third row gets rank 3.
DENSE_RANK() on the other hand will not skip ranks if there is a tie. The first 2 rows get rank 1. Third row gets rank 2.
SELECT Name, Salary, Gender,
RANK() OVER (ORDER BY Salary DESC) AS [Rank],
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees
RANK() and DENSE_RANK() functions with PARTITION BY clause : Notice when the partition changes from Female to Male Rank is reset to 1
SELECT Name, Salary, Gender,
RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC) AS [Rank],
DENSE_RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC)
AS DenseRank
AS DenseRank
FROM Employees
Use case for RANK and DENSE_RANK functions : Both these functions can be used to find Nth highest salary. However, which function to use depends on what you want to do when there is a tie. Let me explain with an example.
If there are 2 employees with the FIRST highest salary, there are 2 different business cases
Though we have 2 Employees with the FIRST highest salary. Dense_Rank() function returns, the next Salary after the tied rows as the SECOND highest Salary
You can also use RANK and DENSE_RANK functions to find the Nth highest Salary among Male or Female employee groups. The following query finds the 3rd highest salary amount paid among the Female employees group
Row_Number function without PARTITION BY : In this example, data is not partitioned, so ROW_NUMBER will provide a consecutive numbering for all the rows in the table based on the order of rows imposed by the ORDER BY clause.
NTILE function without PARTITION BY clause : Divides the 10 rows into 3 groups. 4 rows in first group, 3 rows in the 2nd & 3rd group.
DECLARE @intInput INT
SET @intInput = 2-
SELECT CASE(@intInput) WHEN 1 THEN 'One' WHEN 2 THEN 'Two'
WHEN 3 THEN 'Three' ELSE 'Your message.' END
If there are 2 employees with the FIRST highest salary, there are 2 different business cases
- If your business case is, not to produce any result for the SECOND highest salary, then use RANK function
- If your business case is to return the next Salary after the tied rows as the SECOND highest Salary, then use DENSE_RANK function
WITH Result AS
(
SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS Salary_Rank
FROM Employees
)
SELECT TOP 1 Salary FROM Result WHERE Salary_Rank = 2
Though we have 2 Employees with the FIRST highest salary. Dense_Rank() function returns, the next Salary after the tied rows as the SECOND highest Salary
WITH Result AS
(
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) ASSalary_Rank
FROM Employees
)
SELECT TOP 1 Salary FROM Result WHERE Salary_Rank = 2
You can also use RANK and DENSE_RANK functions to find the Nth highest Salary among Male or Female employee groups. The following query finds the 3rd highest salary amount paid among the Female employees group
WITH Result AS
(
SELECT Salary, Gender,
DENSE_RANK() OVER (PARTITION BY Gender ORDER BY Salary DESC)
AS Salary_Rank
FROM Employees
)
SELECT TOP 1 Salary FROM Result WHERE Salary_Rank = 3
AND Gender = 'Female'
Row_Number function in SQL Server
- Returns the sequential number of a row starting at 1
- ORDER BY clause is required
- PARTITION BY clause is optional
- When the data is partitioned, row number is reset to 1 when the partition changes
Row_Number function without PARTITION BY : In this example, data is not partitioned, so ROW_NUMBER will provide a consecutive numbering for all the rows in the table based on the order of rows imposed by the ORDER BY clause.
SELECT Name, Gender, Salary,
ROW_NUMBER() OVER (ORDER BY Gender) AS RowNumber
FROM Employees
Please note : If ORDER BY clause is not specified you will get the following error
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY
Row_Number function with PARTITION BY : In this example, data is partitioned by Gender, so ROW_NUMBER will provide a consecutive numbering only for the rows with in a parttion. When the partition changes the row number is reset to 1.
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY
Row_Number function with PARTITION BY : In this example, data is partitioned by Gender, so ROW_NUMBER will provide a consecutive numbering only for the rows with in a parttion. When the partition changes the row number is reset to 1.
SELECT Name, Gender, Salary,
ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Gender) ASRowNumber
FROM Employees
Use case for Row_Number function : Deleting all duplicate rows except one from a sql server table.
NTILE function in SQL Server :
- ORDER BY Clause is required
- PARTITION BY clause is optional
- Distributes the rows into a specified number of groups
- If the number of rows is not divisible by number of groups, you may have groups of two different sizes.
- Larger groups come before smaller groups
- NTILE(2) of 10 rows divides the rows in 2 Groups (5 in each group)
- NTILE(3) of 10 rows divides the rows in 3 Groups (4 in first group, 3 in 2nd & 3rd group)
SQL Script to create Employees table
Create Table Employees
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)
Go
Insert Into Employees Values (1, 'Mark', 'Male', 5000)
Insert Into Employees Values (2, 'John', 'Male', 4500)
Insert Into Employees Values (3, 'Pam', 'Female', 5500)
Insert Into Employees Values (4, 'Sara', 'Female', 4000)
Insert Into Employees Values (5, 'Todd', 'Male', 3500)
Insert Into Employees Values (6, 'Mary', 'Female', 5000)
Insert Into Employees Values (7, 'Ben', 'Male', 6500)
Insert Into Employees Values (8, 'Jodi', 'Female', 7000)
Insert Into Employees Values (9, 'Tom', 'Male', 5500)
Insert Into Employees Values (10, 'Ron', 'Male', 5000)
GoNTILE function without PARTITION BY clause : Divides the 10 rows into 3 groups. 4 rows in first group, 3 rows in the 2nd & 3rd group.
SELECT Name, Gender, Salary,
NTILE(3) OVER (ORDER BY Salary) AS [Ntile]
FROM Employees
What if the specified number of groups is GREATER THAN the number of rows
NTILE function will try to create as many groups as possible with one row in each group.
With 10 rows in the table, NTILE(11) will create 10 groups with 1 row in each group.
NTILE function will try to create as many groups as possible with one row in each group.
With 10 rows in the table, NTILE(11) will create 10 groups with 1 row in each group.
SELECT Name, Gender, Salary,
NTILE(11) OVER (ORDER BY Salary) AS [Ntile]
FROM Employees
NTILE function with PARTITION BY clause : When the data is partitioned, NTILE function creates the specified number of groups with in each partition.
The following query partitions the data into 2 partitions (Male & Female). NTILE(3) creates 3 groups in each of the partitions.
The following query partitions the data into 2 partitions (Male & Female). NTILE(3) creates 3 groups in each of the partitions.
SELECT Name, Gender, Salary,
NTILE(3) OVER (PARTITION BY GENDER ORDER BY Salary) AS [Ntile]
FROM Employees
Case Statements:
It is used to provide , if then else type of logic in sql statements .
SET @intInput = 2-
SELECT CASE(@intInput) WHEN 1 THEN 'One' WHEN 2 THEN 'Two'
WHEN 3 THEN 'Three' ELSE 'Your message.' END
Transactions in SQL Server
What is a Transaction?
A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
Transaction processing follows these steps:
1. Begin a transaction.
2. Process database commands.
3. Check for errors.
If errors occurred,
rollback the transaction,
else,
commit the transaction
Let's understand transaction processing with an example. For this purpose, let's Create and populate, tblMailingAddress and tblPhysicalAddress tables
Create Table tblMailingAddress
(
AddressId int NOT NULL primary key,
EmployeeNumber int,
HouseNumber nvarchar(50),
StreetAddress nvarchar(50),
City nvarchar(10),
PostalCode nvarchar(50)
)
Insert into tblMailingAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')
Create Table tblPhysicalAddress
(
AddressId int NOT NULL primary key,
EmployeeNumber int,
HouseNumber nvarchar(50),
StreetAddress nvarchar(50),
City nvarchar(10),
PostalCode nvarchar(50)
)
Insert into tblPhysicalAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')
An employee with EmployeeNumber 101, has the same address as his physical and mailing address. His city name is mis-spelled as Londoon instead of London. The following stored procedure 'spUpdateAddress', updates the physical and mailing addresses. Both the UPDATE statements are wrapped between BEGIN TRANSACTIONand COMMIT TRANSACTION block, which in turn is wrapped between BEGIN TRY andEND TRY block.
So, if both the UPDATE statements succeed, without any errors, then the transaction is committed. If there are errors, then the control is immediately transferred to the catch block. The ROLLBACK TRANSACTION statement, in the CATCH block, rolls back the transaction, and any data that was written to the database by the commands is backed out.
Create Procedure spUpdateAddress
as
Begin
Begin Try
Begin Transaction
Update tblMailingAddress set City = 'LONDON'
where AddressId = 1 and EmployeeNumber = 101
Update tblPhysicalAddress set City = 'LONDON'
where AddressId = 1 and EmployeeNumber = 101
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End
Let's now make the second UPDATE statement, fail. CITY column length in tblPhysicalAddress table is 10. The second UPDATE statement fails, because the value for CITY column is more than 10 characters.
Alter Procedure spUpdateAddress
as
Begin
Begin Try
Begin Transaction
Update tblMailingAddress set City = 'LONDON12'
where AddressId = 1 and EmployeeNumber = 101
Update tblPhysicalAddress set City = 'LONDON LONDON'
where AddressId = 1 and EmployeeNumber = 101
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End
Now, if we execute spUpdateAddress, the first UPDATE statements succeeds, but the second UPDATE statement fails. As, soon as the second UPDATE statement fails, the control is immediately transferred to the CATCH block. The CATCH block rolls the transaction back. So, the change made by the first UPDATE statement is undone.
A transaction is a group of commands that change the data stored in a database. A transaction, is treated as a single unit. A transaction ensures that, either all of the commands succeed, or none of them. If one of the commands in the transaction fails, all of the commands fail, and any data that was modified in the database is rolled back. In this way, transactions maintain the integrity of data in a database.
Transaction processing follows these steps:
1. Begin a transaction.
2. Process database commands.
3. Check for errors.
If errors occurred,
rollback the transaction,
else,
commit the transaction
Let's understand transaction processing with an example. For this purpose, let's Create and populate, tblMailingAddress and tblPhysicalAddress tables
Create Table tblMailingAddress
(
AddressId int NOT NULL primary key,
EmployeeNumber int,
HouseNumber nvarchar(50),
StreetAddress nvarchar(50),
City nvarchar(10),
PostalCode nvarchar(50)
)
Insert into tblMailingAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')
Create Table tblPhysicalAddress
(
AddressId int NOT NULL primary key,
EmployeeNumber int,
HouseNumber nvarchar(50),
StreetAddress nvarchar(50),
City nvarchar(10),
PostalCode nvarchar(50)
)
Insert into tblPhysicalAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')
An employee with EmployeeNumber 101, has the same address as his physical and mailing address. His city name is mis-spelled as Londoon instead of London. The following stored procedure 'spUpdateAddress', updates the physical and mailing addresses. Both the UPDATE statements are wrapped between BEGIN TRANSACTIONand COMMIT TRANSACTION block, which in turn is wrapped between BEGIN TRY andEND TRY block.
So, if both the UPDATE statements succeed, without any errors, then the transaction is committed. If there are errors, then the control is immediately transferred to the catch block. The ROLLBACK TRANSACTION statement, in the CATCH block, rolls back the transaction, and any data that was written to the database by the commands is backed out.
Create Procedure spUpdateAddress
as
Begin
Begin Try
Begin Transaction
Update tblMailingAddress set City = 'LONDON'
where AddressId = 1 and EmployeeNumber = 101
Update tblPhysicalAddress set City = 'LONDON'
where AddressId = 1 and EmployeeNumber = 101
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End
Let's now make the second UPDATE statement, fail. CITY column length in tblPhysicalAddress table is 10. The second UPDATE statement fails, because the value for CITY column is more than 10 characters.
Alter Procedure spUpdateAddress
as
Begin
Begin Try
Begin Transaction
Update tblMailingAddress set City = 'LONDON12'
where AddressId = 1 and EmployeeNumber = 101
Update tblPhysicalAddress set City = 'LONDON LONDON'
where AddressId = 1 and EmployeeNumber = 101
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End
Now, if we execute spUpdateAddress, the first UPDATE statements succeeds, but the second UPDATE statement fails. As, soon as the second UPDATE statement fails, the control is immediately transferred to the CATCH block. The CATCH block rolls the transaction back. So, the change made by the first UPDATE statement is undone.
Cast and Convert functions in SQL Server
To convert one data type to another, CAST and CONVERT functions can be used.
Syntax of CAST and CONVERT functions from MSDN:
CAST ( expression AS data_type [ ( length ) ] )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
From the syntax, it is clear that CONVERT() function has an optional style parameter, where as CAST() function lacks this capability.
Syntax of CAST and CONVERT functions from MSDN:
CAST ( expression AS data_type [ ( length ) ] )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
From the syntax, it is clear that CONVERT() function has an optional style parameter, where as CAST() function lacks this capability.
No comments:
Post a Comment