PIVOT –Fixed Column Cross Tab Query in Microsoft SQL Server 2005

Sometime we may need to see some data of one column as a column header and aggregated results in those columns as a data. There are many programmatic ways to cater this need. Herewith, I am going to explained Fixed Column Cross Tab results with PIVOT. I will write few more articles for other ways of doing the same task.

Here is the table, I will be using for demo.

Create Table SalesSummaryOfRegions

(

Item VARCHAR(10) NOT NULL,

State VARCHAR(10) NOT NULL,

TotalSales INT NOT NULL

)

INSERT INTO SalesSummaryOfRegions

SELECT ‘LAPTOP’,‘CA’,100 UNION ALL

SELECT ‘LAPTOP’,‘NJ’,1200 UNION ALL

SELECT ‘ADAPTER’,‘CA’,910 UNION ALL

SELECT ‘MOUSE’, ‘NY’,1100 UNION ALL

SELECT ‘MOUSE’,‘NY’,2000

Now, I want to get list of all the Items of NJ and CA with its total and average sales. It seems quiet easy as we can use simple GROUP BY clause and SUM aggregate function. But what if I want to see four column named Item, CA, NJ, Average.

Item column should contain all the Items we used to sell. CA column contain total of each item we sold in CA state in respective item’s row and same with NJ. Average column should contain average of particular item sold for both the state. Not you can say, its bit challenging. Yes, it is, if you are going to do it with sub query logic or cursor. But with the help of PIVOT in Microsoft SQL Server 2005, it is very simple. Have a look at following query.

SELECT Item,CA,NJ,(isnull(CA,0)+isnull(NJ,0))/2 as ‘Average’ FROM SalesSummaryOfRegions

PIVOT

(

sum(TotalSales) FOR state in (CA,NJ)

)AS pivo

In above query, we gave Item from SalesSummaryOfRegions table in select list but we don’t have CA and NJ column in our table. Rest assure, it won’t show you error as it was not written by mistake, it’s a use of PIVOT. You can see I use one aggregate function after PIVOT words in above query and it was used for state column. As will be showing average also, so there is one possibility of NULL value as respective value so I want to make sure that NULL value will be treated as 0 that is why I have used isnull() function.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

COMPUTE Clause in Microsoft SQL Server 2005 for sub total:

So far sub-total was very tedious task to do but with COMPUTE clause in SQL-Server 2005 it became fun. Don’t you believe me? Let me take you towards COMPUTER and you will believe me for sure.
COMPUTE clause is nothing but the aggregate query tacked on to the end of a normal query. This query simply returns the simple detail rows with the specific aggregate summary for that result set only.

Let us see it practically.

–CREATE on table for demonstration
create table BookAuthor
(
AuthorName VARCHAR(50),
BookCategory VARCHAR(10),
TotalBook INT
)
–Insert some records
INSERT INTO BookAuthor
SELECT ‘A’,‘C LANG’,2 UNION ALL
SELECT ‘B’,‘C LANG’,3 UNION ALL
SELECT ‘C’,‘SQL’,2 UNION ALL
SELECT ‘A’,‘SQL’,4

After creating above table and insert the records. What will we do??? If we wish to find which author wrote book for which category and total number of book written for every book category. Well, we have age old solution for this but I am going to show your new approach by COMPUTE clause.

SELECT AuthorName,BookCategory,TotalBook
FROM BookAuthor
ORDER BY BookCategory
COMPUTE Sum(totalBook)
BY BookCategory

Above query will return you all rows from table BookAuthor and will show your total per Book Category. Remember if you wish to use book category as group by (in BY clause) in COMPUTE clause you have to define it in order by of simple SELECT query as well.
If you will not mention Book Category in Order By clause of SELECT statement, you will be greeted by following error.
Msg 143, Level 15, State 1, Line 2
A COMPUTE BY item was not found in the order by list. All expressions in the compute by list must also be present in the order by list.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Create and Run a CLR SQL Server Aggregate function in SQL-Server 2005 from Visual Studio 2008:

You might be aware with Aggregate function like MIN, MAX, SUM, COUNT etc. there are many types of Aggregate function supported by Microsoft SQL Server 2005 along with CLR Aggregate function. This is new and amazing feature which helps you create your own customize AGGREGATE function with the power of CLR and use it in SQL Server 2005. Isn’t it amazing? Yes, It is. Let us have a journey of creating CLR SQL Server Aggregate function to implement it in SQL Server 2005.
I have created COUNT aggregate function. It is actually inbuilt function but this is to give demo of creating aggregate function in SQL-Server 2005.
We will first create new project from Visual Studio 2008:
1) File->New->Project
2) From Visual C# tree on left panel select Database option
3) Select “SQL Server Project” from right side panel.
4) Give the name to your project. I gave “TestAggregate”
5) Click “OK”
6) Now from your solution explorer , right click on project name, select “Add->New ->Add Item”
7) Select “Aggregate” (as we are going to create aggregate function)
8) Give name to it. I gave “TestAGGR” then click “ADD” button.
After following above steps, you will see TestAGGR.cs file with some necessary namespace and methods.
Here is the brief description of those methods.
INIT(): will be executed once per aggregation
Accumulate(): will accumulate core logic and run per records.
Merge(): merge data from different method together.
Terminate(): finally returns the value.
Along with above methods, you can create methods as per your needs. Below is the copy of my TestAGGR.CS file. Have a look
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]

public struct TestAGGR
{

public void Init()
{
count = 0;
}

public void Accumulate(int Value)
{
count += 1;
}

public void Merge(TestAGGR value)
{
Accumulate(value.Terminate());
}

public int Terminate()
{
return count;
}

// This is a place-holder member field
private int count;

public int Getcount()
{
return count;
}

}
Once you done the above code in visual studio, build the project and copy the TestAggregate.dll from your BIN folder of your project and put it in “D”drive.
Now, we have done with Visual Studio and we will be moving to SQL-Server 2005 SSMS.
–enable your server for clr enable
–it is disable by default
sp_configure ‘clr enable’, 1
reconfigure

–create assembly of your DLL file
create assembly TestAggregate
authorization dbo
from ‘D:\TestAggregate.dll’
with permission_set = safe
go

–create aggregate function of your DLL
CREATE AGGREGATE TestAggregate(@val Int)
RETURNS Int
EXTERNAL NAME TestAggregate.TestAGGR
go

–run your query and enjoy result
SELECT dbo.testaggregate(run) FROM PERSONALSCORE

I was Inspired to write this article by TECHNET and MSDN website of MICROSOFT.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

In general analysis and reporting purpose, sometime we need to find department name, total salary paid to each department, total number of project department is working for and average salary being paid to each department. This is data administrative department may frequently asked for. Let us have a practical example of the same with GROUP BY clause, AGGREGATE function and SubQuery. For the demonstration purpose, we need to create following three tables and have to insert some data in it.

Create Table Department

(

deptNo INT IDENTITY(1,1) NOT NULL,

deptName VARCHAR(15) NOT NULL,

Description VARCHAR(50) NULL

)

Create Table Employee

(

empNo INT IDENTITY(1,1) NOT NULL,

empName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL,

Salary INT NOT NULL

)

Create Table Project

(

projNo INT IDENTITY(1,1) NOT NULL,

projName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL

)

Create Table Department

(

deptNo INT IDENTITY(1,1) NOT NULL,

deptName VARCHAR(15) NOT NULL,

Description VARCHAR(50) NULL

)

INSERT INTO Department(deptName,Description)

SELECT ‘MIS1’,‘MIS1’ UNION ALL

SELECT ‘MIS2’,‘MIS2’

Create Table Employee

(

empNo INT IDENTITY(1,1) NOT NULL,

empName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL,

Salary INT NOT NULL

)

INSERT INTO Employee(empName,deptNo,Salary)

SELECT ‘Ritesh’,1,50000 UNION ALL

SELECT ‘Bihag’,2,29000 UNION ALL

SELECT ‘Rajan’,1,30000 UNION ALL

SELECT ‘Alka’,2,19000

Create Table Project

(

projNo INT IDENTITY(1,1) NOT NULL,

projName VARCHAR(50) NOT NULL,

deptNo INT NOT NULL

)

INSERT INTO Project(projName,deptNo)

SELECT ‘A’,1 UNION ALL

SELECT ‘B’,2 UNION ALL

SELECT ‘C’,1

Now, we are going to find department name, salary paid to each department, project – department is working for and last but not least average salary paid to each department in order to find efficiency of department.

SELECT Dept.deptname, emp.salary, prj.[Count],emp.salary/prj.[count] as ‘AverageSalary/DEPT’

FROM Department Dept,

(SELECT deptNo, sum(salary) salary FROM Employee GROUP BY deptNo) emp,

(SELECT deptNo, count(*) [Count] FROM Project GROUP BY deptNo) prj

WHERE Dept.deptNo=emp.deptNo and emp.deptNo=prj.deptNo

Above query has used simple SUM and COUNT aggregate function with subquery as table. As I gave example for Aggregate function, let me write down something more for an Aggregate function for those who are new to it.

Aggregate function will return only single row with computed value which summarizes the original result set. It will not return all row in return set. When you use aggregate function first result set returns the row set based on FROM clause and WHERE condition and then filtered records only will be aggregated.

Aggregate functions are expressions so it is mandatory to give column name to it explicitly or else it will return NULL as a column name, if you observe we gave name to all aggregated column like ‘AverageSalary/DEPT’. If we wouldn’t have done that, it would have return NULL as column name.

Aggregate function will not handle precision of numeric column, it is completely based on the source column. However, you can convert the results to other data type as well.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery can be used as a derived table in FORM clause of the T-SQL query. Believe me this is really powerful approach and can bring you out of some difficult situation. I have already previously written some articles on Subquery, you can refer those for getting basic idea about subquery, correlated subquery,CTE etc.

Let us create two tables for demonstration and insert some data in it.

–Create first table

CREATE TABLE CricketerDetails

(

ID INT IDENTITY(1,1) CONSTRAINT pk_cricketID PRIMARY KEY NOT NULL,

Name VARCHAR(25) NOT NULL,

Country VARCHAR(10) NOT NULL

)

–Insert data into first table

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Sachin Tendulkar’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Steve Waugh’,‘Australia’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Saurav Ganguly’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Jaisurya’,‘Sri Lanka’)

–Create second table

CREATE TABLE PersonalScore

(

MatchName VARCHAR(15) NOT NULL,

Run INT NOT NULL,

CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)

)

–Insert data into second table

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-1998’,190,1)

After finishing above task, let us see how we can get cricketer name, country and his score with simple JOIN clause.

SELECT C.Name,c.country,MAX(p.Run)

FROM PersonalScore p join CricketerDetails c

ON p.cricketerid=c.id

GROUP BY C.name,c.country

We have achieved whatever we wanted. If you observed we have to put every column in group by clause which is not included in AGGREGATE function. In this case, those are Name and Country. What if you don’t want to put those two columns in group by clause as this is something messy sometime. Let us see how can achieve the same results with the help of derived subquery as table.

SELECT C.Name,c.country,p.Run

FROM CricketerDetails c

JOIN

(

SELECT Cricketerid,MAX(run) as Run FROM PersonalScore GROUP BY CricketerID

) p

ON c.ID=p.CricketerID

See, we have used subqery in JOIN clause rather than any table and achieved the same results. This technique of relational algebra may prove very powerful sometime in difficult situation.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT) Difference of INTERSECT and INNER JOIN:

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT):

You might have read my previous articles on JOIN as per those articles; JOIN is multiplication of data whereas UNION is addition.

UNION does nothing other than stack the data of multiple result sets in one result sets. While using the UNION you have to consider few things like: column name, number and aliases should be mentioned in first part of the SELECT. ORDER BY clause will sort the complete result sets, it will use the column name of first SELECT statement and should be write down after the last SELECT statement.

Let us create two demo tables to use UNION.

–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16)

)

–create second table for demo

CREATE TABLE BIKE

(

ID int IDENTITY(1,1),

BikeName VARCHAR(16)

)

–Insert records in CAR table

INSERT INTO CAR

SELECT ‘HONDA crv’ UNION ALL

SELECT ‘Mahi. Raunalt’ UNION ALL

SELECT ‘Test’

–Isert records in BIKE table.

INSERT INTO BIKE

SELECT ‘HONDA Karishma’ UNION ALL

SELECT ‘Bajaj DTSI’ UNION ALL

SELECT ‘Test’

Now, both the select statement of above two tables will be merged and will return single result sets.

–use of union all

SELECT ID,CarName FROM CAR

UNION ALL

SELECT ID,BikeName FROM BIKE

Order by CarName

Above query returns all the records of both tables. Though we gave ORDER BY for CarName but it will sort complete second column which include some data of bikeName as well. You cannot give ORDER BY on BikeName column.

Above query was just an example, you can use UNION for less than or equal to 256 tables. This is not at all small amount.

Intersection Union: Intersection Union find common row in both data sets. As soon as you read this statement, you will say, ohh, this could be done by INNER JOIN as well. So, the answer is INNER JOIN matches two table horizontally and INTERSECTION matches two datasets vertically. There is one more difference in INTERSECTION and INNER JOIN is that, Intersection query will see NULL as common and includes the row in the intersection and INNER JOIN will not even includes two different row with NULL value in result sets.

Now, have a look at INTERSET usage in below query.

–use of intersect union

SELECT ID,CarName FROM CAR

INTERSECT

SELECT ID,BikeName FROM BIKE

Order by CarName

You will get only one row which is same in both the table.

EXCEPT: Except (a.k.a. DIFFERENCE UNION) finds records exists in one data sets and not available in another datasets. In above case we have three records in CAR table but third record is exist in BIKE table as well so EXCEPT will display only first two records. Have a look at below query.

–use of EXCEPT

SELECT ID,CarName FROM CAR

EXCEPT

SELECT ID,BikeName FROM BIKE

Order by CarName

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:
I wrote my previous article about different type of JOIN in SQL-Server 2005. Due to the space constraint in that article, complex example has not been given there, only few simple examples. I felt to explode this topic further by giving solution to some really needed business logic.
If you want to create report for blood testing laboratory, who used to check samples of employee of different companies for different purposes. Following should be the table structure, we will be creating three table. One will store information about Order received from different companies. Another one will be the detail of that order, means which company sent how much samples of how much employee?? And the last one is further more details about how much test should be performs on which sample???
Let us create three tables.
–CREATING FIRST ORDER TABLE
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CompanyName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
Now, child table of above.
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[OrderDetails] Script Date: 03/08/2009 12:37:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SampleOfEmployee] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SampleReceivedDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[SampleNumber] ASC
)
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [fk_orderid] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [fk_orderid]
One more child table.
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[sampledetails] Script Date: 03/08/2009 12:43:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sampledetails](
[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TestType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED
(
[SampleNumber] ASC,
[TestType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
Now, this is time to insert few records on above tables.
–Received three order from two company
INSERT INTO Orders
SELECT ‘L1000’,‘2009-03-01’,‘Test Inc.’ UNION ALL
SELECT ‘L1001’,‘2009-03-02’,‘Zeel Inc.’ UNION ALL
SELECT ‘L1002’,‘2009-03-04’,‘ABC Inc.’
–received two sample for first order, one sample for second order
–sample for third order yet to receive
INSERT INTO OrderDetails
SELECT ‘L1000’,‘L1000-01’,‘James’,‘2009-03-01’ UNION ALL
SELECT ‘L1000’,‘L1000-02’,‘John’,‘2009-04-01’ UNION ALL
SELECT ‘L1001’,‘L1001-01’,‘Smita’,‘2009-03-05’
–details of sample, which test suppose to be performed
–on which sample
INSERT INTO SampleDetails
SELECT ‘L1000-01’,‘Cancer’ UNION ALL
SELECT ‘L1000-01’,‘AIDS’ UNION ALL
SELECT ‘L1000-02’,‘BP’ UNION ALL
SELECT ‘L1001-01’,‘AIDS’ UNION ALL
SELECT ‘L1001-01’,‘Cancer’
Well, now we are ready to pull data out of three tables with different type of JOIN which we have seen in my previous article.
NOTE: many different logic can be used to perform following tasks, I am giving this just to explain the use of JOIN otherwise there are some more facility which can handle all of the below situation other than join.
If you want to get OrderID, OrderDate, Company (who gave the order??), SampleNumber, EmployeeName (for which employee company has sent the sample??), TestType (which test to be performed in above tables?)
You can use following query which has used one LEFT OUTER JOIN and one INNER JOIN.
SELECT O.OrderID,O.OrderDate,O.CompanyName,
OD.SampleofEmployee,OD.SampleNumber,SD.TestType
FROM
ORDERS AS O
LEFT OUTER JOIN
ORDERDETAILS AS OD
ON
O.OrderID=OD.OrderID
INNER JOIN
SampleDetails AS SD
ON
OD.SampleNumber=SD.SampleNumber
If there is a need to find how many samples came from which company. Use following query with aggregate function COUNT.
SELECT
O.CompanyName,Count(SampleNumber) as ‘TOTAL’
FROM
ORDERS AS O
LEFT OUTER JOIN
ORDERDETAILS AS OD
ON
O.OrderID=OD.OrderID
GROUP BY O.CompanyName
ORDER BY TOTAL DESC
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:
Merging data is heart of SQL and it shows the depth of relational algebra as well as the power and flexibility of SQL. SQL is excellent at selecting the data and SQL Developer can be master by understanding the relational algebra.
Join seems known technology but it is fun all time. Developer can make very creative query with joins by making data twist and shout. Pulling the data out of join could be the answer of very big code of programming language with iteration. It is much faster than any loop in your code. I promise you will have fun by learning this powerful technique.
SQL-Server 2005 is relational database so you always need to join more than one table in order to get complete information out of data tables. Since, tables are self explanatory itself, order of table doesn’t matter.
Here are the types of available joins in SQL-Server 2005:
Note: AdventureWorks database is going to be used throughout in this chapter.
Inner Join: It’s a very common and frequently needed join. Common intersection will be pulled out in this kind of join. It means, it will compare the result sets of first table with result sets of second table based on common fields or based on primary key and foreign key relationship. Below given is an example of Inner Join.
SELECT Sales.SalesPerson.SalesPersonID,
Sales.SalesPersonQuotaHistory.SalesQuota
FROM Sales.SalesPerson
INNER JOIN
Sales.SalesPersonQuotaHistory
ON
Sales.SalesPerson.SalesPersonID=Sales.SalesPersonQuotaHistory.SalesPersonID
You can get join two table with SalesPersonID in above case as both have common field.
(Part of Outer Join) Left Outer Join: Include all rows from the left side table no matter whether matching rows are exists in right side table or not. It means that Outer join is an extended version of Inner Join as it not only gives your common intersection but it do gives non matching data from left side or right side along with intersection.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Left Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Employee table is joined with EmployeeAddress table based on EmployeeID. Left out join has been used so it will list all employees, no matter where it meets associated entry in EmployeeAddress.
(Part of Outer Join) Right Outer Join: Include all rows from the right side table no matter whether matching rows are exists in left side table or not. It means that Outer join is an extended version of Inner Join as it not only gives your common intersection but it do gives non matching data from left side or right side along with intersection.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Right Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Employee table is joined with EmployeeAddress table based on EmployeeID. Right outer join has been used so it will list all employees’ Address, no matter where it meets associated entry in EmployeeAddress.
(Part of Outer Join) Full Outer Join: Includes all rows from both tables regardless of match exists. It means it a union distinct of Left Outer Join and Right Outer Join.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Full Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Self Join: A self-join refers back to itself. You can use this type of unary relationship for recursive purpose. Like employee table to find boss.
–creating table for self join
create table emp
(
ID int Identity(1,1),
Name Varchar(15),
BossID int
)
GO
–Inserting some records in above table.
Insert Into emp
SELECT ‘DM’,0 UNION ALL
SELECT ‘MA’,1 UNION ALL
SELECT ‘UC’,2 UNION ALL
SELECT ‘LU’,2
GO
–equi join which will find boss name for every employee, DM is a main boss in this case.
SELECT e.ID,e.Name,ep.Name as ‘boss name’
FROM emp e JOIN emp ep
on
e.bossid=ep.id
GO
There are few more types of join like theta join, equi join, natural join etc. which I will explain in coming article.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Delete many (multiple) records in bunch: SQL-Server 2005

Delete many (multiple) records in bunch: SQL-Server 2005
I had one task few months back. I was asked to delete 3 million records from one table containing total of 8 million records. You may think, ohh delete….. simple, execute delete statement with condition and sit back on your chair with relax. J Its not that simple, If I would have done it, I would have lost my database due to many reasons. I don’t want to go in details about those reasons. Let us focus on our main topic.

USE adventureworks
GO
— creating one temporary table from AdventureWork database
— to perform delete operation.
SELECT * into AdventureWorks.DBO.TempPurchaseOrderDetail FROM Purchasing.PurchaseOrderDetail
–Above query has created 8788 row in my temp table
GO

–Wants to delete rocords based on where condition of
–following query….
— it has 6624 RECORDS in my table
SELECT * FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000
Now, we will create one stored procedure which will delete all records from my table which will meet specific conditions.

CREATE PROC DeleteInBunch
@NumOfRow INT –number given for this variable, will be used with TOP in DELETE statement
AS
SET NOCOUNT ON
–INFINITE loop to iterate until and unless it will finish deleting records
WHILE(1=1)
BEGIN
DELETE TOP (@NumOfRow) FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000
–LOOP will not break until we will reach to @@ROWCOUND=0
IF @@ROWCOUNT=0
BEGIN
PRINT(‘Records have been delete!!!!’)
BREAK
END
END

So, finally we have created stored procedure; now let us see whether it actually works!!!
–SP will delete all records for specific condition in the bunch of 100
EXEC DeleteInBunch 100

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:
Storing image in sql-server 2005 database is quiet simple task. All you need to deal with binary datatype. One more benefit you will get by storing image in database is that you are free from burden of managing lots of image folder and its path consistency.
Herewith, I am describing one simple web application in ASP.NET which will store the image in database and will retrieve it right from there.
Let us start our journey to this interesting topic.
First we will create one table in AdventureWorks database or in any other DB you have.
use adventureworks
GO
CREATE TABLE [dbo].[ImageStore] (
[ID] [int] NOT NULL ,
[ImageContent] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Note: You can create couple more field in above table in order to get file name and its extension. I will be using “.JPG” extension in my code while retrieving the image but you can make it more dynamic.
Now, we have done with SQL-Server and let us move to our Visual Studio 2008. We will be creating one new web application of C#.
As soon as you will get your web form, draw one button on that with text property to “Upload Image”.
After setting text property of button, double click on that so it will open code behind of C# with click event button. Before we go further, uses following namespaces in your code behind as those are mandatory.
using System.Drawing;
using System.Data.SqlClient;
using System.IO;
Now, let us move to uploading the file.
Note: I will be going to upload one file from my “C” drive, you can make it more dynamic by using open file dialog box and select file from users computer.
Once you set the reference of above given name spaces, you have to write the following code in button1’s click event.
protected void Button1_Click(object sender, EventArgs e)
{
string strImageName= @”C:\Conversion.jpg”;
Bitmap bNewImage = new Bitmap(strImageName);

FileStream fs = new FileStream(strImageName, FileMode.Open, FileAccess.Read);

//creating byte array to read image
byte[] bImage = new byte[fs.Length];

//this will store conversion.jp in bImage byte array
fs.Read(bImage, 0, Convert.ToInt32(fs.Length));
fs.Close();
fs = null;

//open the database using odp.net and insert the data
string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;
Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);
conn.Open();

string strQuery;
strQuery = “insert into [dbo].[ImageStore](id,[ImageContent]) values(“ + “1,” + ” @pic)”;

SqlParameter ImageParameter= new SqlParameter();
ImageParameter.SqlDbType = SqlDbType.Image;
ImageParameter.ParameterName = “pic”;
ImageParameter.Value = bImage;

SqlCommand cmd = new SqlCommand(strQuery, conn);
cmd.Parameters.Add(ImageParameter);
cmd.ExecuteNonQuery();

Response.Write(“Image has been added to database successfully”);
cmd.Dispose();
conn.Close();
conn.Dispose();
}
So far, you have finished half of the journey. You have select the image from your hard drive, convert it in byte and insert it in SQL-Server table, now we are going to do reverse procedure to get image back.
Now, create another button on the same web form and set the text property with “Retrieve Image” value and also put one image box or grid or whatever tool in which you want to retrieve the image or else, you can simply save it at your hard drive. I am saving it in my hard drive with .JPG extension, you can make more dynamic as I specified in starting of this article.
Finally, write down the image retrieval code in button2’s click event as follows.
protected void Button2_Click(object sender, EventArgs e)
{
string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;
Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);
conn.Open();

//selecting image from sqldataadapter to dataset.
SqlDataAdapter sdImageSource = new SqlDataAdapter();
sdImageSource.SelectCommand = new SqlCommand(“SELECT * FROM [dbo].[ImageStore]”, conn);

DataSet dsImage = new DataSet();
sdImageSource.Fill(dsImage);

string strfn = Convert.ToString(DateTime.Now.ToFileTime());
strfn = @”D:\StoreIMG\” + strfn + “.jpg”;
FileStream fs = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write);

//retrieving binary data of image from dataset to byte array
byte[] blob = (byte[])dsImage.Tables[0].Rows[0][“imageContent”];
//saving back our image to D:\StoreIMG folder
fs.Write(blob, 0, blob.Length);
fs.Close();
fs = null;
}
Note: You have to have write permission in D:\StoreIMG for ASP.Net user or else it will show you an error of permission.
Hope you have enjoyed it!!!!
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah