Fixing Bars Height and Gap between Bars in SSRS Report

 Fixing Bars Height and Gap between Bars in SSRS Report

The Scenario where i faced this issue .

I was having a Report with some filter.Selecting values from filter was changing the bar height

and Space between them .

Below is the Solution to Fix Bar height and Space between the Bars in SSRS Report.

Select your chart.
Click on bars.
this will open a series  Properties as below screen shot 1.

Now in order to fix Bar size.

Series Properties.-Custom attribute Section.
Increase or Decrease the MaxPixel point as in below screenshot2-Highlited..

This will fix your height of barcharts It will not allow to increase height then Max limit.

Now in order to increase and Decrease space between bars.

Series Properties.-Custom attribute Section
Increase or Decrease the PointWidth  as in below screensho3.Highlited..

This will fix your space or Gap between barcharts.

Note : if you have multiple series in your chart change the above properties for all Series.

Difference between Read Committed and Read Uncommitted Isolation Level in SQL Server

Difference between Read Committed and Read Uncommitted Isolation Levels in SQL Server.

Isolation levels determine the behavior of concurrent users that read or write data.

A Reader is one who is selecting some records using a shared lock by default whereas writer mean the one who is making modification in the tables and required an exclusive lock.
We can control the way reader behave by setting different Isolation levels.

You can set type six types of isolation levels: READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ,  SERIALIZABLE, SNAPSHOT, and READ COMMITTED SNAPSHOT.

The Default Isolation Level is Read committed.You can override the default isolation level.

Below is the Explanation and difference between Read committed and Read Uncommitted Isolation levels.

A reader that doesn't ask for a shared lock can never be in conflict with a writer that is holding an exclusive lock.This means that that reader can read uncommitted changes (also known as dirty reads).

It also means that the reader won’t interfere with a writer that asks for an exclusive lock. In other words, a writer can change data while a reader that is running under the READ UNCOMMITTED isolation level reads data.

Deadlock in SQL with Example

Deadlock in SQL with Example

A Deadlock occur when processes blocks each other .for Eg 1st process is blocking 2nd  process 2nd process is blocking 3rd process and the last 3rd  process is blocking the 1st Process.
In this case the sql terminates one of the Session(Process with least work) and terminate the current session with below Error.

Transaction (Process ID 72) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In order to Demonstrate Deadlock in sql i will use two tables

--First Table 

CREATE TABLE [dbo].[Employee_Deadlock](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee_Deadlock] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 3000)

--Second Table

CREATE TABLE [dbo].[Employee_Deadlock1](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee_Deadlock1] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 3000)

Open a New a tab in sql editor -Tab 1

Run the below query

--Updating  first table without committing the Transactions
Begin Tran
Update [Employee_Deadlock] set empsalary=15000
where empname='amit'


Now open a new sql edition New query tab 2 

Run the below query

--Updating second table without committing the Transactions
Begin Tran
Update [Employee_Deadlock] set empsalary=15000
where empname='amit'



Now go to first Tab run the Below query

-- Extracting Data from table 2 which has not been committed in tab 1
select * from [Employee_Deadlock1]
where empname='amit'
commit Tran


After Running above query you will see that it will not show any result as Process 2 is already in Updating Process as we have not committed the Changes
This  arise the Blocking Situation.

Now Run the Below Query in tab 2

-- Extracting Data from Deadlocktable 1 which has not been committed in tab 2
select * from [Employee_Deadlock]
where empname='amit'
commit Tran


This will throw an Error Message

Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 72) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

This mean SQL server identified the Deadlock and terminated one of the blocking process with the above error.

--commit the Update Transaction in both tab to remove blocking

Begin Tran
Update [Employee_Deadlock] set empsalary=15000
where empname='amit'
commit tran


Begin Tran
Update [Employee_Deadlock] set empsalary=15000
where empname='amit'
commit tran

NOTE:DMV(Dynamic Management views) are there to track down deadlocks and  blocking process.

Merging Data in SQL 2008

Merging Data in SQL 2008

SQL Server 2008 introduced a new feature Merge.
Using Merge statement you can perform DML insert update delete in a single query based on conditions.

Below is the demonstration of Merge statement with Two tables EmployeeMerge and EmployeeMerge1.

CREATE TABLE [dbo].[Employee_Merge](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Merge] ([Em
pid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee_Merge] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee_Merge] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee_Merge] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee_Merge] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 3000)


CREATE TABLE [dbo].[Employee_Merge1](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 15000)
INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'amit', 18000)
INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'vijay', 10000)
INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'suresh', 5000)
INSERT [dbo].[Employee_Merge1]  ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'Mukesh', 3000)

Select * from Employee_Merge

Empid EmpName EmpSalary
1 sumit 5000
3 amit 8000
5 vijay 10000
7 suresh 5000
9 Mukesh 3000

Select * from Employee_Merge1

Empid EmpName EmpSalary
1 sumit 15000
2 amit 18000
3 vijay 120000
4 suresh 5000
5 Mukesh 3000

--Merge Statement that will check condition and based on conditions it will perform DML Operations.

MERGE INTO [Employee_Merge] AS TargetTblT 
USING dbo.[Employee_Merge1] AS SourceTbl
 ON TargetTblT.empid = SourceTbl.empid 
 --First condition values of both tables matched and salary of Targertbl=10,000 then deleting that Record from Target table
WHEN MATCHED and TargetTblT.EmpSalary=10000 then delete
--Second Condition when Both empid matched then update the Target table with Source Table values
when Matched Then UPDATE SET 
 TargetTblT.[EmpName] = SourceTbl.[EmpName], 
 TargetTblT.[EmpSalary] = SourceTbl.[EmpSalary]
 --Third Condition where values of Source and Target Table doesn't matches Insert Record from source table to Target table
 WHEN NOT MATCHED 
 THEN INSERT ([Empid], [EmpName], [EmpSalary]) VALUES (SourceTbl.empid, SourceTbl.[EmpName], SourceTbl.[EmpSalary]);

Output 

Empid EmpName EmpSalary
1 sumit 15000
3 vijay 12000
7 suresh 5000
9 Mukesh 3000
2 amit 18000
4 suresh 5000

NOTE: If there is Matched condition with some other condition like in our code where salary =10,000 it should always be the first condition specified in  Matched like in our code and  never forget to add semi colon after termination of Match Statement.
other wise you will get the  below error.

Msg 10713, Level 15, State 1, Line 14

A MERGE statement must be terminated by a semi-colon (;).

Percentage Calculation of a Numeric column in SQL

Percentage Calculation of a Numeric column in SQL 

In below example we are calculating Salary percentage of Each Employee .

Sample Data

a-5000
b-8000
c- 10000
d-5000
e-3000

Formula for Percentage = SalaryofeachEmployee*100/sumofSalaryofEmployee
Eg -A Salary Percentage  %   =5000*100/23000 


So in the Above Records u can see that we want EmployeePercentage Salary for each employee

So we will create a sample Table with some employee Records and we will calculate there Salary Percent


CREATE TABLE [dbo].[Employee_SPercent](
[Empid] [int]  NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_SPercent] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee_SPercent] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee_SPercent] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee_SPercent] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee_SPercent] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 3000)


--Query that will Give SalaryPercent of Each Employee

select empname,empsalary, x.Totalsalary ,(EmpSalary /Totalsalary)*100 as SalaryPercent from [Employee_SPercent] join 
(select SUM(empsalary)Totalsalary from [Employee_SPercent])x on 1=1

Output
empname empsalary SalaryPercent
sumit 5000 16.1290322580645
amit 8000        25.8064516129032
vijay 10000 32.258064516129
suresh 5000 16.1290322580645

Mukesh 3000 9.67741935483871


If you want the percentage upto two round figure cast it to decimal

select empname,empsalary ,cast((EmpSalary /Totalsalary)*100 as decimal(16,2)) as SalaryPercent from [Employee_SPercent] join 
(select SUM(empsalary)Totalsalary from [Employee_SPercent])x on 1=1

Output
empname empsalary SalaryPercent
sumit 5000 16.13
amit 8000        25.81
vijay 10000 32.26
suresh 5000 16.13

Mukesh 3000 9.68

If you want percentage of each employee not salary then use below query

select EmpName , count(*) * 100 / sum(count(*)) over() as percnt
from [Employee_SPercent]
group by Empname

Output

EmpName percnt

amit         20

Mukesh 20

sumit 20

suresh 20

vijay 20

NOTE: The formula of calculating percentage can change according to the requirement.

Trigger on Delete with Example in SQL

Trigger on Delete with Example in SQL

In my Previous post i wrote on Triggers and showed an example of how DML Trigger Insert works.
In this post  will show how an Example of DML Delete trigger.
In case you want to know about DML Insert Trigger Read my Previos post .

http://sqlandssrssolutions.blogspot.in/2014/01/trigger-on-insert-with-example-in-sql.html

Consider a scenario where you want to have keep track of  deleted records from a table like an Audit table so the audit table will capture  the deleted values which occured in the base table.

--Base Table Script with some Records

CREATE TABLE [dbo].[EmployeeBasicDetails](
 [Empid] [int] NOT NULL,
 [EmpName] [nvarchar](50) NULL,
 [EmpSalary] [float] NULL
 ,Emp_UserName nvarchar(100),
 Emp_Password nvarchar(10)
) ON [PRIMARY]
GO


INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (1, N'sumit', 5000,'Sumit_1','1235')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary] ,Emp_UserName,Emp_Password) VALUES (4, N'amit', 9000,'amit_4','121')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (5, N'vijay', 10000,'vijay-5','111')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (7, N'suresh', 5000 ,'suresh-7','120')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (9, N'Mukesh', 130,'mukesh-9','132')

select * from [EmployeeBasicDetails]


--Audit table script which will store Deleted values from Employee Basic Detail Table.

CREATE TABLE [dbo].[EmployeeBasicDetails_Audit](
 [Empid] [int] NOT NULL,
 [EmpName] [nvarchar](50) NULL,
 [EmpSalary] [float] NULL
 ,Emp_UserName nvarchar(100),
 Emp_Password nvarchar(10)
) ON [PRIMARY]
GO

--Script of DML Delete Trigger that generate an action and put all the deleted values in EmployeeAudit Table

Create trigger T_delete on [EmployeeBasicDetails]
for delete
as begin
insert into [EmployeeBasicDetails_Audit] 
select * from deleted  
end


Now to test trigger we will delete a record from EmployeeBasicDetails table

delete from EmployeeBasicDetails where EmpName ='sumit'

--verifty Result
--Deleted row moved to Audit Table

Select * from [EmployeeBasicDetails_Audit] 

OUTPUT:

Empid EmpName EmpSalary Emp_UserName Emp_Password
1       sumit                   5000                  Sumit_1                   1235

--No Record with EmpName Sumit in Base Table
select * from [EmployeeBasicDetails] where EmpName ='sumit'

NOTE : In SQL 2008 Microsoft has introduced Change Data Capture to capture all the DML operations(Insert,Update and Delete) happend in the Master or baseTables.
We can use CDC also to keep track of Changes done in Master or base tables.

Trigger on Insert with Example in SQL

Trigger on Insert with Example in SQL

 A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.

 DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

 We can have Triggers in Both DML as DDL Operations.

There are two type of Triggers.

1-After Triggers-(After Insert,After Delete and After Update)
2-Instead of Triggers-(Instead of Insert,Instead of Delete and Instead of Udpate)

 Below is the Example of DML after Insert Trigger with Insert Action fires an automatic event which will insert into another table.

 Like  we have two tables Employee basic Details and EmployeeUserDetails and we want like whenever we will insert any Records in EmployeeBasicDetails
 at the same time it will insert the EmployeeUserName and Password in EmployeeUserDetails Table. As we want EmployeeUserName and Password column in both the Table.


 --EmployeeBasicDetails Table Script
 CREATE TABLE [dbo].[EmployeeBasicDetails](
 [Empid] [int] NOT NULL,
 [EmpName] [nvarchar](50) NULL,
 [EmpSalary] [float] NULL
 ,Emp_UserName nvarchar(100),
 Emp_Password nvarchar(10)
) ON [PRIMARY]
GO

--Employee User Details Table
CREATE TABLE [dbo].[EmployeeUserDetails](
 Emp_UserName nvarchar(100),
 Emp_Password nvarchar(10)
) ON [PRIMARY]
GO

SO we want whenever we add an employee with all his details in EmployeeBasicDetails Table a Trigger fires and it will save the EmpuserName and Password into EmployeeUserDetails table

--Trigger on insert 

Create trigger T_insert on [EmployeeBasicDetails]
for insert
as begin
Declare @EmpUserName nvarchar(100)
Declare @Emppassword nvarchar(100)
select @EmpUserName =Emp_UserName,@Emppassword= Emp_Password from inserted 
insert into [EmployeeUserDetails] values(@EmpUserName,@Emppassword)
end

Now we will insert records in EmployeeBasicDetails table and at the same time trigger will insert EmpuserName and Password into EmployeeUserDetails table.

--inserting Records in EmployeeBasicDetails which will fire an insert event in --EmployeeUserdetails tables also

INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (1, N'sumit', 5000,'Sumit_1','1235')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary] ,Emp_UserName,Emp_Password) VALUES (4, N'amit', 9000,'amit_4','121')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (5, N'vijay', 10000,'vijay-5','111')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (7, N'suresh', 5000 ,'suresh-7','120')
INSERT [dbo].[EmployeeBasicDetails] ([Empid], [EmpName], [EmpSalary],Emp_UserName,Emp_Password) VALUES (9, N'Mukesh', 130,'mukesh-9','132')

--verify Result

select * from [EmployeeBasicDetails]

Output:
Empid EmpName EmpSalary Emp_UserName Emp_Password
1 sumit 5000 Sumit_1 1235
4 amit 9000 amit_4 121
5 vijay 10000 vijay-5 111
7 suresh 5000 suresh-7 120
9 Mukesh 130 mukesh-9 132


Select * from [EmployeeUserDetails]

Output:
Emp_UserName Emp_Password
Sumit_1 1235
amit_4 121
vijay-5 111
suresh-7 120
mukesh-9 132

So everytime any insert operation will happen in EmployeeBasicDetails table it will fire the Trigger to insert UserName and Password in Userdetails table too.

NOTE: Inserted and Deleted tables used in Triggers are termed as Magic Tables.

Ranking with Aggregate function Replacing Group by in SQL

Ranking with Aggregate function Replacing Group by in SQL

As we all knows the Importance of Group by clause in SQL which is used for Grouping of Data by one or more Columns but with Ranking function we can Replace Group by in many Conditions.

--We will take an Example of Employee table

CREATE TABLE [dbo].[Employee1](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee1] ON
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'sumit', 6000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'amit', 9000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, N'vijay', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, N'suresh', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 130)
SET IDENTITY_INSERT [dbo].[Employee1] OFF

Select * from Employee1


So in the above table we can see that we have different salaries for same Employee so first we will apply
aggregate function with group by to get max salaries min salaries sum of Salaries count of salary Group by Employees.

--Query to Extract Employee Salaries(using aggregate functions Min,Max,Avg,Sum and Count) 
select EmpName,SUM(EmpSalary)TotalSalary,AVG(EmpSalary)AvgSalary,COUNT(EmpSalary)CountofSalary
,MIN(EmpSalary)MinSalary,MAX(EmpSalary) MaxSalary from employee1
group by EmpName 

Output:
EmpName TotalSalary AvgSalary CountofSalary MinSalary MaxSalary
amit 17000 8500 2 8000 9000
Mukesh 130 130 1 130 130
sumit 11000 5500 2 5000 6000
suresh 10000 5000 2 5000 5000
vijay 15000 7500 2 5000 10000

Now we will Replace Group by  Using Ranking function and achieve the same Result.

--Ranking Function Replacing Group by 

SELECT distinct EmpName
    ,SUM(EmpSalary) OVER(PARTITION BY EmpName) AS TotalSalary
    ,AVG(EmpSalary) OVER(PARTITION BY EmpName) AS AvgSalary
    ,COUNT(EmpSalary) OVER(PARTITION BY EmpName) As CountofSalary
    ,MIN(EmpSalary) OVER(PARTITION BY EmpName) AS MinSalary
    ,MAX(EmpSalary) OVER(PARTITION BY EmpName) AS MaxSalary
     ,SUM(EmpSalary) OVER(PARTITION BY [EmpDepartment]) AS TotalSalarybyDept
FROM employee1 

GO

Output:
EmpName TotalSalary AvgSalary CountofSalary MinSalary MaxSalary
amit 17000 8500 2 8000 9000
Mukesh 130 130 1 130 130
sumit 11000 5500 2 5000 6000
suresh 10000 5000 2 5000 5000
vijay 15000 7500 2 5000 10000


Using Ranking Function we get the same result which we were getting with Group By clause..

Inserting Xml data into SQl table Using Stored Procedure in SQL 2008

Inserting Xml data into SQl table Using Stored Procedure in SQL 2008

Below is the Scripts of Inserting an xml Data into a table.Tested and verified in sql 2008 R2.

First we will create a table with some Records

--Table Script with Few Records.

CREATE TABLE [dbo].[Employee1](

[Empid] [int] IDENTITY(1,1) NOT NULL,

[EmpName] [nvarchar](50) NULL,

[EmpSalary] [float] NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[Employee1] ON

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'sumit', 6000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'amit', 9000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, N'vijay', 5000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, N'suresh', 5000)

INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 130)

SET IDENTITY_INSERT [dbo].[Employee1] OFF

Now we will generate xml of these records.

Run below query to Generate xml of above table Employee1 Records.

--Test will be the Root Node of XML you can give any Name

select * from Employee1   for Xml AUTO,elements,root('Test')

Output--XML

<Test>

  <Employee1>

    <Empid>1</Empid>

    <EmpName>sumit</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>2</Empid>

    <EmpName>sumit</EmpName>

    <EmpSalary>6.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>3</Empid>

    <EmpName>amit</EmpName>

    <EmpSalary>8.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>4</Empid>

    <EmpName>amit</EmpName>

    <EmpSalary>9.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>5</Empid>

    <EmpName>vijay</EmpName>

    <EmpSalary>1.000000000000000e+004</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>6</Empid>

    <EmpName>vijay</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>7</Empid>

    <EmpName>suresh</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>8</Empid>

    <EmpName>suresh</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>9</Empid>

    <EmpName>Mukesh</EmpName>

    <EmpSalary>1.300000000000000e+002</EmpSalary>

  </Employee1>

</Test>

Now we will Truncate data of our Employee1 table to insert Records back using xml data

truncate table employee;

--Below is the procedure that will take xml as input string and will insert the Records back into Employee1 table after trucating the table.

--Procedure to insert xml data in sql table

Create PROCEDURE [dbo].[Proc_Insertxml] (

 @xmlstring XML 

)

AS

BEGIN

INSERT INTO  [Employee1](

EmpName ,

EmpSalary 

)

SELECT

[Table].[Column].value('EmpName [1]', 'varchar(50)') as ' EmpName ',

[Table].[Column].value(' EmpSalary [1]', 'varchar(100)') as ' EmpSalary'

 FROM @xmlstring.nodes('/ Test / Employee1') as [Table]([Column])

END

--Execute Procedure passing above xml to put all records back after truncating the table

exec [Proc_Insertxml] '<Test>

  <Employee1>

    <Empid>1</Empid>

    <EmpName>sumit</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>2</Empid>

    <EmpName>sumit</EmpName>

    <EmpSalary>6.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>3</Empid>

    <EmpName>amit</EmpName>

    <EmpSalary>8.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>4</Empid>

    <EmpName>amit</EmpName>

    <EmpSalary>9.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>5</Empid>

    <EmpName>vijay</EmpName>

    <EmpSalary>1.000000000000000e+004</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>6</Empid>

    <EmpName>vijay</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>7</Empid>

    <EmpName>suresh</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>8</Empid>

    <EmpName>suresh</EmpName>

    <EmpSalary>5.000000000000000e+003</EmpSalary>

  </Employee1>

  <Employee1>

    <Empid>9</Empid>

    <EmpName>Mukesh</EmpName>

    <EmpSalary>1.300000000000000e+002</EmpSalary>

  </Employee1>

</Test>'

--verify the Records

 Select * from Employee1

Output:

 Empid EmpName EmpSalary

1 sumit          5000

2 sumit         6000

3 amit           8000

4 amit           9000

5 vijay          10000

6 vijay          5000

7 suresh 5000

8 suresh       5000

9 Mukesh    130

Note: IN case your xml is very large its better to put the xml into a variable with xml data type and execute the procedure passing that parameter name to the Procedure like below

Declare @Textxml xml

set @Textxml='<Test>your xml.....<Test>'

  exec [Proc_Insertxml] Textxml

Current Year Calender Script in SQL

Current Year Calender Script in SQL

Below we have two Sql Scripts:

1-Return all Dates and days of a Year same as calender.
2-Return all Months first date along with days Name. i.e 2014-01-01 ,2014-02-01 and so on....

-- Query that will show all dates and days of current year

DECLARE @DateFrom smalldatetime,@DateTo smalldatetime,@DayName nvarchar(100)
SET @DateFrom=cast(DATENAME(yy,getdate()) as DATE)
SET @DateTo=    dateadd(day,-1,dateadd(mm,12,cast(DATENAME(yy,getdate()) as DATE)))

declare  @t table (dt datetime,Days  nvarchar(100))
WHILE (@DateFrom <=@DateTo)
BEGIN
insert into @t
select @DateFrom, datename(WEEKDAY ,@DateFrom)
SET @DateFrom = dateadd(day,1,@DateFrom)
END
select dt  as Dates,Days from @t  

Output -365 Rows

Dates Days
2014-01-01 00:00:00.000 Wednesday
2014-01-02 00:00:00.000 Thursday
2014-01-03 00:00:00.000 Friday
2014-01-04 00:00:00.000 Saturday
2014-01-05 00:00:00.000 Sunday
2014-01-06 00:00:00.000 Monday
2014-01-07 00:00:00.000 Tuesday
2014-01-08 00:00:00.000 Wednesday
2014-01-09 00:00:00.000 Thursday
2014-01-10 00:00:00.000 Friday
2014-01-11 00:00:00.000 Saturday
2014-01-12 00:00:00.000 Sunday
2014-01-13 00:00:00.000 Monday
2014-01-14 00:00:00.000 Tuesday
2014-01-15 00:00:00.000 Wednesday
2014-01-16 00:00:00.000 Thursday
2014-01-17 00:00:00.000 Friday
2014-01-18 00:00:00.000 Saturday
..............................................................................

-- Query that will show all Months 1 date along with Days Name of current year (i.e 2014-01-01 ,2014-02-01 and so on....)

DECLARE @DateFrom smalldatetime,@DateTo smalldatetime,@DayName nvarchar(100)
SET @DateFrom=cast(DATENAME(yy,getdate()) as DATE)
SET @DateTo=    dateadd(day,-1,dateadd(mm,12,cast(DATENAME(yy,getdate()) as DATE)))
declare  @t table (dt datetime,Days  nvarchar(100))
WHILE (@DateFrom <=@DateTo)
BEGIN
insert into @t
select @DateFrom, datename(WEEKDAY ,@DateFrom) 
SET @DateFrom = dateadd(mm,1,@DateFrom)
END
select dt as 'Months',days from @t  

Output

Months                                    Days
2014-01-01 00:00:00.000 Wednesday
2014-02-01 00:00:00.000 Saturday
2014-03-01 00:00:00.000 Saturday
2014-04-01 00:00:00.000 Tuesday
2014-05-01 00:00:00.000 Thursday
2014-06-01 00:00:00.000 Sunday
2014-07-01 00:00:00.000 Tuesday
2014-08-01 00:00:00.000 Friday
2014-09-01 00:00:00.000 Monday
2014-10-01 00:00:00.000 Wednesday
2014-11-01 00:00:00.000 Saturday
2014-12-01 00:00:00.000 Monday

Difference between @@Identity,Scope_Identity() and Ident_Current in SQL

Difference between @@Identity,Scope_Identity() and Ident_Current()

Most of the developers are aware of this fact that above three @@Identity,Scope_Identity() and Ident_Current returns the Last value inserted.
.
So below i will explain the Difference between all 3 with Examples

--Creating sample table tbl1 with identity field

CREATE TABLE tbl(Empid int IDENTITY)

INSERT tbl1 DEFAULT VALUES

Case I-Single Table Insert without Trigger
--Run queries all will return 1 
SELECT @@IDENTITY

SELECT SCOPE_IDENTITY()

SELECT IDENT_CURRENT('tbl' )

Case II-Table with Trigger 

Now we will add a trigger in tbl that will have an insert action in table 2

--creating table 2
CREATE TABLE tbl0(Empid int IDENTITY(10,5))--start with 10 with increament of 5

--creating trigger to check behaviour of all 3

create trigger T_ins on Tbl
for insert
as begin
insert tbl0 default values
end

INSERT tbl DEFAULT VALUES

--This returns  last value inserted across scope(second scope i.e trigger tbl0) which  is tbl0 values 10
SELECT @@IDENTITY

--This returns the value inserted into identity column in curent scope that is tbl value 2
SELECT SCOPE_IDENTITY()

-This returns the last identity value generated for a specific table in any session and any scope
SELECT IDENT_CURRENT('tbl' ) --2
SELECT IDENT_CURRENT('tbl0' )--10

Importing data from CSV File to SQL

Importing data from Notepad File to SQL Table

Below are the Steps of importing CSV (comma Separated file) i.e Text file into SQL Table

First we will create a notepad file with employee Data below

ID,Name,Salary
1,Amit,5000
2,Sumit,6000
3,Raj,8000
4,vijay,9000
5,suresh,10000
6,,5000
7,,5000
8,,5000
9,vinod,130

Save it as Notepad file in any Drive with name Employee.txt .

Now creating a Employee table to insert CSV file values with same no. of columns as in notepad file

CREATE TABLE [dbo].[Employee_import](
[Empid] int,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]

GO

So below is the Query to insert Data from CSV to SQL Tables

 BULK INSERT [Employee_import]
    FROM 'E:\Employee.txt'
    WITH 
    ( 
        FIRSTROW = 2, 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

select * from Employee_import  --to verify result

I have placed my notepad file in E : drive so, change to the place where you have stored your notepad file.

Firstrow=2 means the first row in the notepad file is having column names so we dont want that   inserted insert will start from row 2.

FieldTeminator=, as field are separated by comma
RowsTerminator=\n as Rows are separated with a new lines.

Note: You can also use SQL Import and Export wizard and SSIS to import or Export Data from CSV file to SQL.

Calling Function Inside a Stored Procedure in SQL

Calling Function Inside a Stored Procedure in SQL

Below is the Script of Calling a function inside a Stored Procedure.

The Scenario is like we have a function Salary which is performing some calculation on input Salary
in order to get the Exact salary.

Then we have a Procedure that is inserting employeeName and the Calculated Salary by the function in the Employee Table.


--Table Script values will be inserted through Procedure

CREATE TABLE [dbo].[Employee1](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]

GO

--function script that will take Salary as Input and return the calculated salary

create function Salary (@salary float)
returns int
as
begin 
declare @Hra float
set @Hra=@salary /10
declare @Lta float
set @lta=@salary/5
set @salary =@salary+@hra+@lta
return @salary
end

--Stored Procedure Script that will insert into Employee1table with calculated Salary

create proc Employee_insert
(
@EmpName nvarchar(100),
@EmpSalary float)
as begin
insert into Employee1 
values(@EmpName ,dbo.Salary(@EmpSalary))
end

--Executing Procedure
Exec  Employee_insert 'vinod' ,100

OUTPUT

empid empname EmpSalary
1           vinod               130 

Testing Function 

select dbo.salary(100)

Output -130

So the Calculated Salary by the function has been inserted into Employee1 table using Stored Procedure.

NOTE: Above Examples shows how to call a Function inside a Stored Procedure but you cannot can a Stored Procedure inside a function.

Update Top 10 in SQL

Update Top 10  in SQL 

Update query update all values of a column when no where condition is defined.

I met a scenario when in my table there were thousands of records and  in one column who has  lots of null values and the table was not having any autoincreamented field.
I have to update some of Null Records with some values ,other null records with other values and so on....

So below are the steps to update top 10 or 100 or any number of records of a column

--Scenario 1 update using Update statement

create table Tbl1 (C1 int, c2 varchar(100))

select * from Tbl1 

insert into Tbl1 values(2,'4'),(3,'6'),(4,'10')

insert into Tbl1 values (5,null),(6,null),(9,null)

Now we want to update col2 having null but the restriction is we don't' have any autoincreament column based on which we can update
here the table has less record so you can update by adding where condition but when there are very large no. of records and you have to update top record then you have to use below query


--Its necessary to have brackets in top clause when using with update

update top(2) Tbl1 set c2 ='10'

 (This case is used when you want to update null values with limitations of rows)

Now we can also update remaining null fields using cte

--Update using Cte

with cte as (

select * from Tbl1 where c2 is null
)

update cte set c2 =20

NOTE : Update through CTE can only be posible when CTE select query is fetching data from single table (not using Joins).

DML Operations on view affecting BaseTable in SQl

DML operations on view affecting BaseTable in SQl

Any DML operation(insert ,update delete) on view affect the base table only when the view is from only one base table.and it should not be custom field like created using Aggregate function(sum,max....).Secondaly, if a view is created using join then the view will not be updatable view.

Below  is sample data with example

--first sample table

create table Tbl1 (C1 int, c2 varchar(100))

--second sample table

create table Tbl2 ( c3 int ,c4 varchar(100))

go

--view created with only 1 table

create view vw1 as select * from Tbl1 

--inserting through view 

insert into Vw1 values (1,'2')

--insert successfully showing record in both table and view

select * from Vw1 

select * from Tbl1 

--Second view created using joins

create view Vw2 as Select tbl1.C1   ,c2 =c4 from Tbl1  join tbl2 on tbl1.C1  =tbl2 .c3 

go

--now try to insert  in second view

insert into Vw2   values (1,'4')

Msg 4405, Level 16, State 1, Line 2

View or function 'Vw2' is not updatable because the modification affects multiple base tables.

--insert record in table 2

insert into tbl2   values (1,'4')

select * from vw2

--one record 

--Now we will try  to delete record from both view

--first view created with one table

delete from Vw1 where C1=1

succesfully

--second view created using joins

delete from Vw2 where C1=1

Msg 4405, Level 16, State 1, Line 2

View or function 'Vw2' is not updatable because the modification affects multiple base tables.

so above example have given a clear image that you can perform insert update delete in a view only when it is created using a single table. u can't update a view created using Multiple tables.

Script of Creating Database and Tables at Runtime Using Procedure

Script of Creating Database and Tables at Runtime Using Procedure

This was a Requirement from one of my  friend who asked me to create a Procedure that will create Database and tables at Runtime.So, below Procedure will take DBName as Input Parameter and Create Database with the same name, I am creating one table inside Procedure you can add multiple according to your requirement.

--Script of Creating Database and Tables at Runtime Using Procedure 
--Tested  in SQL 2008 R2
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[Proc_CreateDbObject] 
@DbName nvarchar(100) 
AS
BEGIN
DECLARE @cmd varchar(8000)
DECLARE @CreateDB varchar(100)
DECLARE @ErrMsg varchar(150)

-- SET NOCOUNT ON added to prevent returning no. of records
SET NOCOUNT ON;

IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @DbName)
BEGIN
SET @CreateDB = N'CREATE DATABASE ' + @DBName
EXEC(@CreateDB)
END
ELSE
BEGIN
SET @ErrMsg = N'Database ' + @DBName + ' already exists. '
RAISERROR(@ErrMsg, 16, 1)
RETURN -1
END

BEGIN TRAN

SET @cmd = N'CREATE TABLE [' + @DBName + N'].' + N'[dbo].[Employee](
[empid] [int] IDENTITY(1,1)  NOT NULL,
[empname] [varchar](50) NULL,
[address] [varchar](50) NULL,
[email] [varchar](50) NULL,
[phonenumber] [varchar](50) NULL)
'

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while creating datbase objects.', 16, 1)
SET @CreateDB = 'DROP DATABASE ' + @DBName
EXEC(@CreateDB)
RETURN -1
END
ELSE
BEGIN
COMMIT TRAN
PRINT N'Created successfully'
PRINT N''
RETURN 0
END
end

--Execute Procedure
exec [Proc_CreateDbObject] 'test_DB'

output: Created successfully