Database Simplified Headline Animator

Database Simplified

Tuesday 28 June 2011

Use Of Magic Tables While Updating Or Deleting Records

 

The Following Post Shows how Magic Tables Help In Creating Data Audit On Updation, Insertion and Deletion.

image

Lets Create Another Table with Similar Structure To Keep Audit Of Above Table

CREATE TABLE [dbo].[Testing_Audit](
    [Id] [int],
    [EmpId] [varchar](10),
    [EmpName] [varchar](20),
    [Gender] [char](1),
    [Designation] [varchar](20),
    [Department] [varchar](20), 
    [Salary] [money],
    [UpdatedOn] [datetime],
    [EncodedOn] [datetime]
) 

image

Now Lets See How Magic Tables Pushes Data To Audit Table. In The Following Update Query I Am Updating Name Of Employee To Sandeep Kr Verma From Sandep Kumar. In Typical Audit I should be able to get History of Records effecting by Update or Delete.
See the Following Example How History Is Maintained In Audit Table On Updating Record.

Update Testing  Set EmpName='Sandeep Kr. Verma' 
Output Deleted.*,GETDATE() into Testing_Audit
where Id=1

image

Look at the highlighted line above(Plays Major Role)  which fetches Data From “Deleted Magic Table” and Inserts Into Audit Table.

Select * From Testing;
Select * From Testing_Audit;

image

As You Can see Above Results From Testing And Testing_Audit Table. So these Magic Tables Can Be Used For Various Purpose.

See The Following Example For Delete Audit.

Delete From Testing
Output Deleted.*,GETDATE() Into Testing_Audit
where Id=2

image

Lets Check Our Audit Table

Select * From Testing;
Select * From Testing_Audit;

image

As You can See On Deleting Record From Testing Table Record Moved To Audit Table With the Help Of “Deleted” Magic Tables.

Surprised smile

Wednesday 15 June 2011

Grouping Sets In Microsoft SQL Server 2008

GROUPING SETS in MSSQL is an Extension to “Group By” and Gives Ease Of Use and Flexibility to Define Grouping Expressions As Compared To “RollUp” and “Cube”

Let’s See The Following Table with Country, State And City Names

image

Now With The above Table If We Wish To Have Count Of City StateWise And CountryWise and Grant Count Of Cities For All Counties.

In Earlier Versions Of SQL We Used Group By and Union and Cube and Rollup To Achive This Now In SQL Server 2008 We Have GROUPING SETS.

Let’s See How this Helps.

SELECT CountryName,StateName ,COUNT(CityName) CityCount
FROM CityMaster
Group By
GROUPING Sets
(
(CountryName,StateName),CountryName,()
)
Order by CountryName desc,StateName Desc

 

image

To Replace the Nulls and Making Result More Readable You Can Put Some Conditions To Your Query:  See Following

 

SELECT Case when CountryName IS Null then 'All Country' else CountryName End as CountryName,
Case When StateName Is Null then 'Total' else StateName End as StateName,
COUNT(CityName) CityCount
FROM CityMaster
Group By
GROUPING Sets
(
(CountryName,StateName),CountryName,()
)
Order by CountryName desc,StateName Desc

 

image

The Advantage Of Using GROUPING SETS Is That you can define grouping expressions in Grouping Sets.

Surprised smile

Monday 13 June 2011

GROUP_CONCAT in Microsoft SQL Server

 

GROUP_CONCAT is built-in function in MySQL To Achieve Following.
This Blog Explains How We Can Achieve This In Microsoft SQL Server

In MySQL Following Query is Required.

SELECT Designation,GROUP_CONCAT(EmpName)AS EmployeeList
FROM Employee A
GROUP BY Designation

imageimageimage

Let’s See How we Can Achieve Above In Microsoft SQL Server

Select Designation,
Substring((Select ',' + EmpName  From Employee B Where B.Designation=A.Designation For XML Path('')),2,8000) As EmployeeList
From Employee A
Group By Designation

I have Used Substring Function To Remove Extra Comma From The Resultset

image

Smile

Thursday 9 June 2011

How To Remove Duplicate Records From A Table

 

Consider Following Table For Example Which Contains Duplicate Country Names :

Select * From Country_Master

Go

image

 

Let’s See How Many Duplicate Records Each Country Contains.

Select CountryName,COUNT(CountryName) as NoOfRecords from Country_Master
Group By CountryName
Go

image

 

Now You Can Use Following Query to Remove Duplicate Rows

Declare @CountryName Varchar(100)
Declare @Records Int
Declare DelDuplicate Cursor 

For
Select CountryName,COUNT(CountryName)-1 as NoOfRecords  --Get Count Of (Records Country Wise)-1
from Country_Master
Group By CountryName
Having Count(CountryName)>1 -- Include Only Those Records which are More then 1

Open DelDuplicate
Fetch DelDuplicate Into @CountryName,@Records
While @@FETCH_STATUS=0
    Begin
        Set RowCount @Records -- Set RowCount (Total Record Country Wise) - 1
            Delete From Country_Master Where CountryName=@CountryName -- Delete Duplicate Records
        Set RowCount 0 -- Reset Record To Default
        Fetch DelDuplicate Into @CountryName,@Records
    End 
Close DelDuplicate 
DeAllocate DelDuplicate

image

 

As You Can See the Major Role Here Is Played By RowCount

For Example When I Set RowCount = 1 and you run Select Command On table Then Irrespective of No Of Records In Table. Result will Show only 1 Row and Even  if You Delete From Table only 1 row Will Be Deleted.

Default Value Of RowCount is 0.

Which Returns All The Rows Of Table in Select Statement.

 

Now Let’s See What happened to the Table containing Duplicate Country Names

image

 

Open-mouthed smile

Monday 6 June 2011

Difference Between IDENT_CURRENT(), @@IDENTITY AND SCOPE_IDENTITY()

 

IDENT_CURRENT(‘TABLENAME’)

This will return the last inserted identity of table.

SCOPE: This function is not limited to any “SCOPE OR SESSION”. But it is Table Specific.

See the Following Syntax.

Select IDENT_CURRENT(‘TableName’) – Parameter

Returns the Last Inserted Identity of Table (Whose Name Is Supplied As Parameter)

If Two Users Are Inserting Rows into Same Table and At Any Point Both The Users are Trying to find out Identity of table then both will get the same. i..e. Last Inserted Identity.

@@IDENTITY

This will return Identity Generated in any table within the Current Session.

SCOPE : Current Connection Or Current Session

Syntax

Select @@IDENTITY

Since there is no parameter is passed as we have In IDENT_CURRENT function. so its not table specific. This will return last inserted identity in any table in the current session

For Example If You have two tables TableA and TableB, Both the Table has Identity Column.

TableA has a Trigger which insert value into TableB.

Now If User Insert Data Into TableA

And User runs Select @@IDENTITY To find out last inserted Identity. So This will Return Identity From TableB not From TableA. As I Said @@IDENTITY is not limited to current execution scope. It is limited to current session.

SCOPE_IENTITY()

This will return Identity Generated in any table within the Execution Scope.

SCOPE : Execution Scope (Can Be Inside Store Procedure or Triggers)

Syntax

Select SCOPE_IENTITY()

For Example If You have two tables TableA and TableB, Both the Table has Identity Column.

TableA has a Trigger which insert value into TableB.

Now If User Insert Data Into TableA

And User runs Select SCOPE_IENTITY() To find out last inserted Identity. So This will Return Identity From TableA not From TableB. As SCOPE_IENTITY()  Limited to Current Execution Scope.

 

 

 

 

 

Thursday 2 June 2011

Use of XML DataType | How to Insert or Update multiple Rows From GridView to Stored Procedure

 

How to Insert All the Rows From Following GridView To Database Table.

If you see, Generally we find most of the people writing For or While Loops and multiple iterations to Insert Rows Into Database.

The above Approach causes Multiple Hits to database which may degrade the performance of database.

Let see how XML Datatype can Help us in Optimizing this task.

image

 

Write a For loop to Generate String As Following

<table>

<productid>17</productid>

<productname>Alice Mutton</productname>

<unitprice>39.0000</unitprice>

</table>

If you See Its Simple HTML with Opening and closing tags. Just Loop it and you  will have Following String.

Each row should have Opening <Table> and closing </Table> Tags with  Opening and Closing Field Tags.

string mystr=string.Empty;

mystr=’<table>

<productid>17</productid><productname>Alice Mutton</productname>

<unitprice>39.0000</unitprice>

</table>

<table>

<productid>3</productid><productname>Aniseed Syrup</productname>

<unitprice>10.0000</unitprice>

</table>

<table>

<productid>40</productid><productname>Boston Crab Mutton</productname>

<unitprice>18.4000</unitprice>

</table>

<table>

<productid>60</productid><productname>Camembert Pierrot</productname>

<unitprice>34.0000</unitprice>

</table>

<table>

<productid>18</productid><productname>Carnarvon Tigers</productname>

<unitprice>62.5000</unitprice>

</table>’

Now You can Pass this String as XML Parameter To Stored Procedure Which Executes Following Query To Extract Result From XML Parameter.

Declare @xmlData Xml;

Set @xmlData=mystr;   -- Assigning XML String to XML DataType

 

How to Read Data From Xml Variable

Note : Field name as Case Sensitive and DataType Should Match.

Select x.data.value('productid[1]','int') as ProductId,
x.data.value('productname[1]','varchar(50)') as ProductName,
x.data.value('unitprice[1]','decimal(12,4)') as UnitPrice
From @xmlData.nodes('/table') as x(data)

 

 

image

Note :  As Xml Doesn’t Support Following Symbols

& , < , >

We Need Replace These Symbols In XmlString Before Passing string Value to Store Procedure Parameter  or XMLVariable.

With Following Characters

&  --  &amp;

<  --   &lt;

>  --  &gt;

the Output of Query can be Used To Update and Insert Data In a Table

 

 

Hot smile

Wednesday 1 June 2011

Create Dynamic Pivot Table In MS SQL Server

 

--Create Table PivotTable


Create Table PivotTable(ProductName Varchar(100),MarketName Varchar(100),Price int)
Go

--insert Values Into Table
Insert Into PivotTable
Values
('Orange','Reliance Fresh',100),
('Mango','Big Bazar',120),
('Orange','Big Apple',30),
('Banana','CO-MART',150),
('Mango','CO-MART',75),
('Grape','Mandi',75)

Go


--Declaration Of Variable For Dynamic columns.

The Following Query is to Demonstrate Total Number Of columns That will Appear as column In Pivot Result.

Declare @PivotCols Varchar(2000)   
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
Select @PivotCols

Go

Dynamic columns Result Looks Like Following

image

 

Generate A Dynamic SQL To Get Dynamic Pivot


Declare @PivotCols Varchar(2000)   
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot   
exec('Select * From PivotTable as tmp pivot (sum(price) for  MarketName in ('+@PivotCols+')) as tbl')

Go

Or

We Can Also Create Stored Procedure To Generate Results.

Create Procedure Usp_GetPivotTable
As
Declare @PivotCols Varchar(2000)   
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot   
exec('Select * From PivotTable as tmp pivot (sum(price) for  MarketName in ('+@PivotCols+')) as tbl')
Go

 

Run The Query Or Execute the Store Procedure

Declare @PivotCols Varchar(2000)   
Set @PivotCols=''
Select @PivotCols=(Select Distinct '['+ MarketName +'],' from PivotTable for Xml Path(''))
Set @PivotCols=SUBSTRING(@PivotCols,1,len(@PivotCols)-1)
--Dynemic Query To Run Pivot   
exec('Select * From PivotTable as tmp pivot (sum(price) for  MarketName in ('+@PivotCols+')) as tbl')

--Or

Exec Usp_GetPivotTable

Go

 

image

 

Now We can Add More Rows To PivotTable And See the Result with Added Columns And Rows Automatically.

Surprised smile