SQL With Mangal

ORDER BY Enhancements in SQL Server Denali

2010-11-14T00:14:00.001+05:30

Today I am going to talk about one new feature that is been introduced in the SQL Server Denali. And that is: one interesting enhancement in ORDER BY clause. Now with ORDER BY clause you can also specify the OFFSET and FETCH options.

From Books Online of Denali:
OFFSET: Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.

FETCH: Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one.

This is how the new syntax of ORDER BY clause looks like in SQL Server Denali:
------------------------------------------------------------------------------------------------------
ORDER BY order_by_expression
    [ COLLATE collation_name ]
    [ ASC | DESC ]
    [ ,...n ]
[ <offset_fetch> ]
<offset_fetch> ::=
{
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}
------------------------------------------------------------------------------------------------------

Take a look at following example:

------------------------------------------------------------------------------------------------------
USE AdventureWorks
GO
SELECT VendorID
,Name
FROM Purchasing.Vendor
ORDER BY VendorID OFFSET 5 ROWS
GO
------------------------------------------------------------------------------------------------------

Output:

In the example I wrote 5 as OFFSET. You can see in the output 1st five rows are skipped and we got VendorIds starting from 6. Here SQL Server 1st orders the data on the Column specified in ORDER BY clause(i.e. VendorID). The next query uses the clause OFFSET 5 ROWS to skip the first 5 rows and return all remaining rows.

Now lets see the example of FETCH:

------------------------------------------------------------------------------------------------------
USE AdventureWorks
GO
SELECT VendorID
,Name
FROM Purchasing.Vendor
ORDER BY VendorID OFFSET 5 ROWS
FETCH NEXT 3 ROWS ONLY
GO
------------------------------------------------------------------------------------------------------

Output:

As you can SQL Server has fetched only 3 rows and that also after skipping 1st five rows. That is because I specified 5 as OFFSET and 3 as FETCH NEXT. Here FETCH NEXT 3 ROWS ONLY to limit the rows returned to 3 rows from the sorted result set.

Also:
1. offset_row_count_expression can be a variable, parameter, or constant scalar subquery. When a subquery is used, it cannot reference any columns defined in the outer query scope. That is, it cannot be correlated with the outer query.

2. ROW and ROWS are synonyms and are provided for ANSI compatibility.

3. In query execution plans, the offset row count value is displayed in the Offset attribute of the TOP query operator.

You can find all this information and more on: ORDER BY Clause (Transact-SQL)

Thanks
Mangal

Some Things About VIEWS

2010-09-01T09:17:00.001+05:30

Everybody has different views about VIEWS, that’s what make them interesting topic to discuss. Other thing is if you make some assumptions about VIEWS, they can lead you to problems. As my target audience is SQL beginners, today I’ll talk about few things about VIEWS so some of the obvious mistakes can be avoided. Actually there are so many things we can talk about VIEWS, but I leave them for future posts.

Today I’ll show what happens when you create a view using “SELECT * FROM TableName” and then ALTER the underlying table used in the VIEW. The normal assumption most of the SQL beginners make is: If you create a VIEW using “SELECT * FROM TableName”, all the changes made in the underlying table will automatically reflect in the VIEW as well.

Case 1: You add a column to the table.
Now here is a question for you: If you have a table named Employees and a VIEW created top of it with simple query “SELECT * FROM Employees”. Now if I add one column to Employees table, will that column appear in result if I execute query “SELECT * FROM View”?

I had ask this question many times in interviews; 80% of time I heard a thumping “Yes”. And most of the candidate were having experience of well over 3 years. Answer to above question is BIG NO. Let me explain this with actual example, 1st lets create some sample data.

------------------------------------------------------------------------------------------------------

-- Create the table with 4 columns
CREATE TABLE Employees
(
EmpID INT,
FirstName VARCHAR(6),
LastName VARCHAR(20),
ManagerID INT

)
GO

-- Populate the table with sample data
INSERT INTO Employees SELECT
1, 'Mangal', 'Pardeshi', 0 UNION ALL SELECT
2, 'Rahul', 'Sharma', 1 UNION ALL SELECT
3, 'Ajay', 'Varma',2
GO

-- Create a View with wild card *
CREATE VIEW Emp AS
SELECT *
FROM Employees
GO

-- Verify the data in the table and view
SELECT *
FROM Employees
GO
SELECT *
FROM Emp
GO
------------------------------------------------------------------------------------------------------

Data in the table and view:

As you can see the data in the Table and View is identical, so are the number of columns. Now lets add one column HireDate in the table and see what happens.

------------------------------------------------------------------------------------------------------
-- Add HireDate Column in Employees Table
ALTER TABLE Employees ADD HireDate DATETIME
GO

-- Update the HireDate column with some date
UPDATE Employees
SET HireDate = '20100827'
GO

-- Again Verify the data in table and View
SELECT *
FROM Employees
GO
SELECT *
FROM Emp
GO
------------------------------------------------------------------------------------------------------

As you see HireDate column didn’t appear in VIEW. So question arises: Why a VIEW doesn't get refresh when I add a column to the table?
Short answer is when you create the VIEW, the column information/definition of VIEW(metadata of VIEW) gets stored in system tables at the time of creation of VIEW. And that metadata doesn’t get refresh when you alter the underlying table. You have to explicitly refresh the metadata of VIEW.
So next question is: How to refresh the VIEW once you modified the underlying table?
There are 2 ways to refresh the VIEW:
1. Using the system stored procedure sp_refreshview
From Books Online - sp_refreshview: Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.
Syntax: EXECUTE sp_refreshview ‘viewname’

2. Or by executing ALTER VIEW statement. When you ALTER the VIEW, SQL Server will pick the latest column definition from underlying table and will update the VIEW metadata.

So in our case we can refresh the VIEW Emp by:

------------------------------------------------------------------------------------------------------
-- 1. using system stored procedure sp_refreshview
EXECUTE sp_refreshview 'Emp'
GO
-- 2. Or by executing the ALTER statement
ALTER VIEW Emp
AS
SELECT *
FROM Employees
GO

-- Verify the data in View
SELECT *
FROM Emp
GO
------------------------------------------------------------------------------------------------------

Note you don’t need to execute both the queries, either of the above can do the trick for you. Now you can see the HireDate column in Emp view as well:

Case 2: You drop a column from the table.
Similarly when you drop a column from the table, the VIEW definition doesn’t get updated even though you have used wild card “*” in VIEW definition. Now lets drop the column HireDate from the table and see what happens:
Note: In previous step I refreshed the View after adding the HireDate, so now HireDate is part of Emp view as well.

------------------------------------------------------------------------------------------------------
-- First Verify the data before dropping column
SELECT *
FROM Employees
GO
SELECT *
FROM Emp

-- Drop HireDate column from Table
ALTER TABLE Employees DROP COLUMN Hiredate
GO

-- Verify the data after dropping the column.
SELECT *
FROM Employees
GO
SELECT *
FROM Emp
GO
------------------------------------------------------------------------------------------------------

Now in this case HireDate column will get removed from table but the metadata of the VIEW still have its information stored so you will get the following error on selecting data from VIEW.
Msg 4502, Level 16, State 1, Line 1
View or function 'Emp' has more column names specified than columns defined.

The solution is, again refresh the VIEW definition by executing:

------------------------------------------------------------------------------------------------------
EXECUTE sp_refreshview 'Emp'
GO
------------------------------------------------------------------------------------------------------

Now you won’t get the same error.

Case 3: You drop one or more columns and add equal or more number columns to the table.
This case is even more dangerous as User selecting data from the VIEW can get wrong data under wrong columns and can create confusion. I won’t explain this in detail, just execute following queries and you will realize what I’m saying.

------------------------------------------------------------------------------------------------------
-- Drop column LastName from table
ALTER TABLE Employees DROP COLUMN LastName
GO
-- Add Column DeptName with default value 'IT'
ALTER TABLE Employees ADD DeptName VARCHAR(10) NOT NULL DEFAULT 'IT'
GO

-- Verify the data
SELECT *
FROM Employees
GO
SELECT *
FROM Emp
GO
------------------------------------------------------------------------------------------------------

Now if you see, the LastName column is not present in the table and you can also see the DeptName column in the table. And interesting observation with VIEW is, though the data is exactly matching with the table but columns names are not correct. We again need to refresh the VIEW to correct it.

------------------------------------------------------------------------------------------------------
EXECUTE sp_refreshview 'Emp'
GO

-- Verify the data
SELECT *
FROM Emp
GO
------------------------------------------------------------------------------------------------------

Back to normal now:

What is the solution?
Obvious prevention is don’t use wild card “*” while creating VIEWS. But even listing out columns is just a prevention or I’d say it is a good practice. Because even after listing out the columns, if you drop a column from the table that has been used in any VIEW you will still face problems.

The solution is creating the view using “WITH SCHEMABINDING” option.
From Books Online: Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

Let me show you one example with SCHEMABINDING:

------------------------------------------------------------------------------------------------------
-- Create View with SCHEMABINDING
CREATE VIEW NewEmp
WITH SCHEMABINDING
AS
SELECT EmpID,
FirstName
FROM dbo.Employees
GO

-- Verify the data
SELECT *
FROM NewEmp
GO

-- Now try to alter the table
ALTER TABLE Employees DROP COLUMN EmpID
GO
------------------------------------------------------------------------------------------------------

Now you will get the error:
Msg 5074, Level 16, State 1, Line 2
The object 'NewEmp' is dependent on column 'EmpID'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE DROP COLUMN EmpID failed because one or more objects access this column.

Basically WITH SCHEMABINDING has prevented the change that would affect the view definition.

Additional Information:
A] If you want see the all the dependant objects on particular table you can use following script:

------------------------------------------------------------------------------------------------------
SELECT *
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
ON so.object_id = sed.referencing_id
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('dbo.employees')
GO
------------------------------------------------------------------------------------------------------

B] The following example creates a script that refreshes the metadata for all views that have a dependency on table dbo.employees:

------------------------------------------------------------------------------------------------------
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
ON so.object_id = sed.referencing_id
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('dbo.employees')
GO
------------------------------------------------------------------------------------------------------

Source:
CREATE VIEW (Transact-SQL)
sp_refreshview (Transact-SQL)

Namaste!
- Mangal

Convert Seconds to HH:MM:SS

2010-08-24T23:05:00.001+05:30

Today 2 of my colleagues from reporting team had this requirement. They had one table where Seconds were stored as INT and in report they wanted to convert the seconds to HH:MM:SS format. They already had 1 solution ready with them. It was something like:

---------------------------------------------------------------------------------------------------------------

DECLARE @Seconds INT
SET @Seconds = 3800

SELECT CONVERT(VARCHAR(10),@Seconds/3600)
    +':'
    + RIGHT('00'+CONVERT(VARCHAR(2),(@Seconds%3600)/60),2)
    +':'
    + RIGHT('00'+CONVERT(VARCHAR(2),@Seconds%60),2) AS [HH:MM:SS]
GO
----------------------------------------------------------------------------------------------------------------- The output--
01:03:20

It was working fine, but my colleagues were looking for something different, something elegant. And I jumped on to help them. Now a days I hardly get any chance to write SQL, so i don’t let such opportunities go. I had one solution in mind using CONVERT and Style 108:

--------------------------------------------------------------------------------------------------------------- DECLARE @Seconds INT
SET @Seconds = 3800
SELECT CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108) AS [HH:MM:SS]
GO
---------------------------------------------------------------------------------------------------------------

But problem with the above query is, it fails when number of seconds are more than 86399(there are total 86400 seconds in a day). So if number of seconds are 86400; above query will show 00 hours instead of showing 24 hours. See following example:

--------------------------------------------------------------------------------------------------------------- DECLARE @Seconds INT
SET @Seconds = 86500
SELECT CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108) AS [HH:MM:SS]
GO
---------------------------------------------------------------------------------------------------------------

-- The output--
00:01:40

Here the expected output was 24:01:40. So I modified the above query a little and I came up with this:

---------------------------------------------------------------------------------------------------------------DECLARE @Seconds INT,@Hour VARCHAR(10)
SET @Seconds = 86500
SET @Hour = DATEDIFF(HH, 0, DATEADD(SS,@Seconds,0))
SELECT STUFF(CONVERT(VARCHAR(8), DATEADD(SS,@Seconds,0),108),1,2,@Hour) AS [HH:MM:SS]
GO
---------------------------------------------------------------------------------------------------------------

-- The output--
24:01:40

Just in case you want to run above query on table:

--------------------------------------------------------------------------------------------------------------- SELECT STUFF(CONVERT(VARCHAR(8), DATEADD(SS, Seconds,0),108),1,2, DATEDIFF(HH, 0, DATEADD(SS, Seconds,0))) AS [HH:MM:SS]
FROM TableName
GO
---------------------------------------------------------------------------------------------------------------

I don’t know which approach is better, rather you tell me which one you liked. Looking at them I think both the queries will give almost identical performance, just that 1st query looks like a Mathematical solution while my approach looks like a SQL solution. If you know any other approach please feel free to share.

Namaste!
- Mangal

SQL Myth: Primary Key and Clustered Index

2010-08-20T23:54:00.001+05:30

After remaining quiet for almost a year I’m back with what I enjoy the most, talking about SQL and sharing whatever little knowledge I have. Many SQL Developers have this misconception: “Primary key => Clustered Index: Only a Clustered Index can exist on a Primary key column”. On numerous occasions I had tough times explaining that this is not the case every time, you can create a Non-Clustered Index on a primary key column. But if this hot discussion is going on across a coffee table and I’m away from Computer I get helpless. So finally I decided to write about this.

You can create a Non-Clustered Index on primary key column. Or if I try to put this in Myth Buster words “A primary key column can exist/survive without a Clustered Index” Yes it is a fact that PRIMARY KEY constraints default to CLUSTERED Index. But it doesn’t mean that you CAN’T create a non-clustered index on a Primary Key Column. And also you can create a Clustered Index on a non-primary key column. Let me show you this with some simple examples.

Case 1: 1st lets see what happens when you specify only PRIMARY KEY and nothing else. In this case YES, by default Clustered Index will be created on Primary Key Column.


USE tempdb GO CREATE TABLE MyTable1 ( Id INT PRIMARY KEY, Dates DATETIME ) GO

USE tempdb
GO

CREATE TABLE MyTable1
(
Id INT PRIMARY KEY,
Dates DATETIME
)
GO

You can see as expected a clustered Index got created on ID column.

Case 2: But by just adding a NONCLUSTERED word in front of primary key you can tell SQL Server to create a Non Clustered Index instead of a default Clustered one.

Here you go:


USE tempdb GO CREATE TABLE MyTable2 ( Id INT PRIMARY KEY NONCLUSTERED, Dates DATETIME ) GO

USE tempdb
GO

CREATE TABLE MyTable2
(
Id INT PRIMARY KEY NONCLUSTERED,
Dates DATETIME
)
GO

As you can clearly see in the image a non-clustered index got created on ID column which is also a primary key.

Case 3: Now here is the small trick, you can force SQL Server to create a non-clustered index on a primary key column even without writing NONCLUSTERED in front of it. Yes, there is exception to the rule “PRIMARY KEY constraints default to CLUSTERED Index” even if you don’t specify NONCLUSTERED. Question is, How? Well by simply creating a Clustered Index on another column while creating the table.


USE tempdb GO CREATE TABLE MyTable3 ( Id INT PRIMARY KEY, Dates DATETIME UNIQUE CLUSTERED ) GO

USE tempdb
GO

CREATE TABLE MyTable3
(
Id INT PRIMARY KEY,
Dates DATETIME UNIQUE CLUSTERED
)
GO

See the Image, a Non Clustered got created on ID column and a Clustered on Dates column.

The obvious question will be, why SQL Server didn’t create the Clustered on Id column this time? Answer is very simple, if you know the basic rule “You can have only one Clustered Index on a table”. And since in the CREATE statement you forced SQL Server to create a clustered index on dates columns SQL Server had no choice but to create a non clustered on Id column.

What we learned today?
Honestly speaking, I didn’t tell anything new. Experts/people with good knowledge about SQL Server already knew this. But interesting thing we can learn here – yes there are some DEFAULTs set by SQL Server, but that doesn’t stop you from telling SQL Server “Boss enough of your DEFAULTs, now let me take the control”. Actually in early days of learning SQL we all get into this habit of relying on DEFAULTs set by SQL Server. And we get so used to them that we start considering them as RULES that can’t be broken.

Actually I think there is no harm in taking little bit extra effort and writing some extra keywords and telling SQL Server this is what I want or this is what is expected.

You also learned how to create a Clustered Index on a column of your choice. This can be very useful when you don’t want a Clustered Index on a primary key especially in cases like where you are using GUID as a primary key(I hate them) and you want Clustered Index to be created on some other column.

Namaste!
- Mangal.

Microsoft SQL Server 2008 R2 CTP

2009-08-12T18:36:00.001+05:30

The first community technology preview (CTP) of Microsoft SQL Server 2008 R2 available for download for MSDN and TechNet subscribers.

For more details about SQL Server 2008 R2 and for other related links See - http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx

If you have any questions regarding SQL Server 2008 R2 you can visit the forums - http://social.msdn.microsoft.com/Forums/en-US/category/sqlserverprerelease

ROLLUP and ORDER BY

2009-08-11T12:51:00.001+05:30

In one of my previous posts, I discussed on how useful GROUPING function is while writing a ROLLUP/CUBE query. One more way GROUPING function can help you is – in ordering the results returned by ROLLUP/CUBE queries.

The reason I’m writing this post is, sometime back I seen somebody writing a weird ORDER BY statement to get the desired ordering after writing a query using a ROLLUP operator. First he didn’t use the GROUPING function in SELECT statement and 2nd his ORDER BY was something like :


ORDER BY CASE ColumnName WHEN 'Total Of ColumnName' THEN 'Zzzzzzzz' ELSE ColumnName END

Just to get sub total returned by ROLLUP at the bottom of result set. Well of course if he had the knowledge about GROUPING then he wouldn’t have written such CASE statement in ORDER BY.

As you may know that GROUPING function returns 1 when the row is added by either the CUBE or ROLLUP operator, and 0 when the row is not the result of CUBE or ROLLUP. So you can easily use this property of the GROUPING for ordering the result set.

Lets have a look at the following example, 1st create some sample data.


-- create sample table Sales CREATE TABLE Sales ( ID INT, FName VARCHAR(30), Zone VARCHAR(30), Sale INT ) GO -- Load sample data INSERT INTO Sales SELECT 1, 'Mangal', 'East', 20 UNION ALL SELECT 2, 'Mangal', 'East', 150 UNION ALL SELECT 3, 'Mangal', 'West', 50 UNION ALL SELECT 4, 'Ram', 'East', 45 UNION ALL SELECT 5, 'Ram', NULL, 80 UNION ALL SELECT 6, 'Ram', NULL, 40 UNION ALL SELECT 7, 'Sachin', 'West', 50 UNION ALL SELECT 8, 'Sachin', 'West', 40 GO -- Test sample data SELECT Id, FName, Zone, Sale FROM Sales GO

-- create sample table Sales

CREATE TABLE Sales
(
ID INT,
FName VARCHAR(30),
Zone VARCHAR(30),
Sale INT
)
GO

-- Load sample data

INSERT INTO Sales SELECT
1, 'Mangal', 'East', 20 UNION ALL SELECT
2, 'Mangal', 'East', 150 UNION ALL SELECT
3, 'Mangal', 'West', 50 UNION ALL SELECT
4, 'Ram', 'East', 45 UNION ALL SELECT
5, 'Ram', NULL, 80 UNION ALL SELECT
6, 'Ram', NULL, 40 UNION ALL SELECT
7, 'Sachin', 'West', 50 UNION ALL SELECT
8, 'Sachin', 'West', 40
GO

-- Test sample data

SELECT Id, FName, Zone, Sale
FROM Sales
GO

The sample data :

ID	FName	Zone	Sale
1	Mangal	East	20
2	Mangal	East	150
3	Mangal	West	50
4	Ram	East	45
5	Ram	NULL	80
6	Ram	NULL	40
7	Sachin	West	50
8	Sachin	West	40

And here is the expected output :

FName	Zone	Total
Mangal	East	170
Mangal	West	50
Mangal	All Zone	220
Ram	East	45
Ram	Unknown	120
Ram	All Zone	165
Sachin	West	90
Sachin	All Zone	90
All Names	All Zone	475

As you can see in the expected output, all the FNames are ordered in ascending order and their total SUM is at the bottom, same for the Zone column. For ordering the result in that way just use the GROUPING(column_name) in ORDER BY just before the column_name. See the following query, esp the ORDER BY clause:


SELECT CASE GROUPING(fname) WHEN 1 THEN 'All Names' ELSE ISNULL(Fname, 'Unknown') END AS FName, CASE GROUPING(Zone) WHEN 1 THEN 'All Zone' ELSE ISNULL(Zone, 'Unknown') END as Zone, SUM(Sale) AS Total FROM Sales GROUP BY Fname, Zone WITH ROLLUP ORDER BY GROUPING(fname),FName,GROUPING(Zone),Zone

Simple, isn’t it? Now you don’t need to write a CASE statement in ORDER BY, just use the GROUPING function. If you will be doing the ORDERING in application layer, then you will need to get in the GROUPING(fname) and GROUPING(zone) column in the SELECT list as well.

Mangal

TSQL Challenges

2009-08-07T22:27:00.001+05:30

I just like to thank Jacob Sebastian who is a fellow SQL Server MVP and founder of the www.tsqlchallenges.com for offering me an opportunity to be part of the TSQL Challenges team. I’m really happy to be a part of a team consists of people like Jacob, Alejandro Messa, Peter Larsson (all 3 are SQL Server MVPs), Adam Haines (a moderator of MSDN SQL Server forums), Rui Carvalho and many other talented people.

Here is a brief description about TSQL Challenges site: TSQL Challenges constantly aim at helping people to enhance their SET based query writing skills. With TSQL Challenges, sometimes you learn stuff that you don’t know, sometimes you will see better ways of doing stuff that you already know and sometimes you will be able to use your expertise to help others to learn TSQL querying skills. Even SQL Server experts love TSQL Challenges because every challenge inspires them to come up with new better ways of solving the given problem.

The Mission: The entire “TSQL Challenge” team will focus on fulfilling our mission; “helping people to enhance their SET based query writing skills”. We will come up with more and more interesting TSQL Challenges that encourages you to look for alternate logics and inspires you to think outside the regular thought process.

I would like to invite my readers to participate in a TSQL Challenge - www.tsqlchallenges.com

Also like to thank Jacob again for a warm welcome and kind word he has put in introduction post - Introducing new “TSQL Challenge” Team Members

So I hope I will come with up some interesting SQL puzzles that will challenge your SQL skills and also you will a fun solving them.

Mangal

SQL Server 2008 Service Pack (SP) 1 on Microsoft Update as a Required Automatic Update

2009-08-04T15:40:00.001+05:30

SQL Server 2008 Service Pack 1 will soon be available through Automatic Update starting from September.

For the latest information you can read it from SQL Server Setup blog - SQL Server 2008 Service Pack (SP) 1 on Microsoft Update as a Required Automatic Update

For better understanding of Automatic Update see - Update Your PC Automatically

UNION Vs UNION ALL

2009-07-22T13:50:00.001+05:30

Many times you may have heard this “Use UNION ALL over UNION whenever possible.” The question arises - why? To answer this question in one statement - UNION ALL performs faster compare to UNION.
Then again question arises - Why UNION ALL performs faster? Also - Why whenever possible, why not always?

Let me answer the 2nd question 1st – Though both UNION and UNION ALL combines the results of two or more queries into a single result set, there is fundamental difference between these two. UNION returns only DISTINCT result set, while UNION ALL returns basically all rows available, this includes duplicates.

Lets see the following example:


-- create 2 tables A and B. CREATE TABLE A ( ID INT, Names VARCHAR(10) ) GO CREATE TABLE B ( ID INT, Names VARCHAR(10) ) GO -- insert data into table A INSERT INTO A VALUES(1,'Mangal'); INSERT INTO A VALUES(5,'Sham'); INSERT INTO A VALUES(2,'Ram'); -- insert data into table B INSERT INTO B VALUES(2,'Ram'); INSERT INTO B VALUES(3,'Shiv'); INSERT INTO B VALUES(4,'John'); -- test sample data SELECT id, Names FROM A GO SELECT id, Names FROM B GO

-- create 2 tables A and B.
CREATE TABLE A
(
ID INT,
Names VARCHAR(10)
)
GO
CREATE TABLE B
(
ID INT,
Names VARCHAR(10)
)
GO
-- insert data into table A
INSERT INTO A VALUES(1,'Mangal');
INSERT INTO A VALUES(5,'Sham');
INSERT INTO A VALUES(2,'Ram');

-- insert data into table B
INSERT INTO B VALUES(2,'Ram');
INSERT INTO B VALUES(3,'Shiv');
INSERT INTO B VALUES(4,'John');

-- test sample data
SELECT id, Names
FROM A
GO
SELECT id, Names
FROM B
GO

Here is how the data of the table A and B looks like :

Note that id=2 and names=Ram is there in both the tables. That will help us in understanding the difference between UNION and UNION ALL. Now lets execute the following 2 queries, 1st is with UNION and 2nd is with UNION ALL. –


-- with UNION SELECT id, Names FROM A UNION SELECT id, Names FROM B GO -- with UNION ALL SELECT id, Names FROM A UNION ALL SELECT id, Names FROM B GO

The result:

Observations :
1. 1st query with UNION returns 5 rows, and UNION ALL query returns 6 rows.
2. Row for ID=2(for Ram) appears twice in UNION ALL result set.
3. Result set for UNION is sorted on ID column. For UNION ALL all the rows of table A appeared 1st followed by rows of table B(no sort).

As you can see, UNION eliminates any duplicate rows from final result set while UNION ALL returns basically all rows available including duplicates. That is the cause of UNION being slow. For each row UNION operator checks whether the entire row exists in previous rows or not. And for making this validation UNION by default 1st sort the result set on the 1st available column of the result set. In our example UNION has sorted the result set on ID column even though I haven’t specified any ORDER BY clause. If you see Name “Sham” (which is in table A) appeared last in the UNION result because it has the highest id 5 while it appeared on 2nd row of UNION ALL result. A look at the query execution plan can help you visualizing it better :

As you can see cost of the UNION query is 73% compare to 27% for UNION ALL. And measure reason being the “Distinct Sort” that UNION operator performs to sort and to eliminate the duplicate rows. While UNION ALL doesn’t really bother about sort and duplicates. And that is why UNION is slow compare to UNION ALL.

So again going back to question – why not use UNION ALL always? And one more question to be added - when to use which one?

- You should use UNION when you don’t want the duplicates in your final result set, and you are not sure (or may be you are sure) that duplicate records exists in the different queries involved in the UNION.

- You should be using UNION ALL when :
1. You are not bothered about the duplicate rows in the result.
2. You are sure there are no duplicates in different queries involved in UNION. e.g. if you are combining results from 2 or more different years(sales orders) with each query reruns result for individual year with some unique id for each row. Or combining result for 2 or more different departments.

All this long I’m talking about UNION and UNION ALL as if they are 2 different things all together. Are they? Not exactly. Reason I’m saying this because, when one of my friend asked me about UNION ALL and I advised him to look into the Books online, and he came back complaining me that “books online doesn’t say anything about UNION ALL”. Reason – he was thinking that Books online must be having some separate section dedicated to UNION ALL, as if it is different from UNION.

Actually, the ALL is just an optional argument in the UNION syntax. For more on UNION you can refer the books online - http://msdn.microsoft.com/en-us/library/ms180026.aspx.

Mangal

DELETE Vs TRUNCATE

2009-07-03T11:21:00.001+05:30

I know this has been done many times, but still here is something from my side. What is the difference between TRUNCATE and DELETE?

Well one reason I wanted to write this post was, on so many of the blogs, forum threads I keep seeing statements like “TRUNCATE cannot be rolled back”. This is also one of the most frequently asked questions in Interviews. And since my target audience is people who are just started learning SQL (and NOT SQL Experts), I thought I should write this one.

Instead of just focusing on ROLLBCAK I will try to cover most of the points related with TRUNCATE and DELETE.

Remove Data : First thing first, both can be used to remove the rows from table.
But a DELETE can be used to remove the rows not only from a Table but also from a VIEW or the result of an OPENROWSET or OPENQUERY subject to provider capabilities.

FROM Clause : With DELETE you can also delete rows from one table/view/rowset_function_limited based on rows from another table by using another FROM clause. In that FROM clause you can also write normal JOIN conditions. Actually you can create a DELETE statement from a SELECT statement that doesn’t contain any aggregate functions by replacing SELECT with DELETE and removing column names.
With TRUNCATE you can’t do that.

WHERE : A TRUNCATE cannot have WHERE Conditions, but a DELETE can. That means with TRUNCATE you can’t delete a specific row or specific group of rows.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause.

Performance : TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
And one of the reason is locks used by either statements. The DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.

Transaction log : DELETE statement removes rows one at a time and makes individual entries in the transaction log for each row.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

Pages : After a DELETE statement is executed, the table can still contain empty pages.
TRUNCATE removes the data by deallocating the data pages used to store the table data.

Trigger : TRUNCATE does not activate the delete triggers on the table. So you must be very careful while using TRUNCATE. One should never use a TRUNCATE if delete Trigger is defined on the table to do some automatic cleanup or logging action when rows are deleted.

Identity Column : If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used.
DELETE doesn’t reset the identity counter. So if you want to retain the identity counter, use DELETE instead.

Replication : DELETE can be used against table used in transactional replication or merge replication.
While TRUNCATE cannot be used against the tables involved in transactional replication or merge replication.
Rollback : DELETE statement can be rolled back.
TRUNCATE can also be rolled back provided it is enclosed in a TRANSACTION block and session is not closed. Once session is closed you won't be able to Rollback TRUNCATE.

Restrictions : The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.
And if DELETE is used against View, that View must be an Updatable view.
TRUNCATE cannot be used against the table used in Indexed view.
TRUNCATE cannot be used against the table referenced by a FOREIGN KEY constraint, unless a table that has a foreign key that references itself.

Source :
DELETE - http://msdn.microsoft.com/en-us/library/ms189835.aspx
TRUCNATE - http://msdn.microsoft.com/en-us/library/ms177570.aspx

PIVOT Multiple Columns

2009-06-26T16:26:00.001+05:30

In one of my previous post I showed you how to UNPIVOT multiple columns. On similar lines I also wanted to write on “How to PIVOT multiple columns?”, so this post was due for some time. Actually I was looking for some efficient way of doing it. Limitation of PIVOT operator is, it supports pivoting only on a single column. But you can always have multiple PIVOT operators in the FROM clause. I was trying to create a PIVOT query with multiple columns with multiple PIVOT operators. But at the end of it I found that our old fashioned CASE expression is performing much better than a multiple PIVOT operator query.

Even though I’m writing this post on how to write a multiple PIVOT operator query, my suggestion is use CASE expressions instead for getting better performance. Though personally I like to avoid CASE also. Normally I like to do it in Reporting Services, by creating a Matrix report. Now a days almost all Reporting Tools provides you an option of creating Matrix report. And good thing about Matrix report is unlike PIVOT operator you don’t need to hard code any column value.

If you try to write a PIVOT query with 2 PIVOT operators, and use same column in FOR clause you will get an error : Invalid column name NameOfColumn.

Or if you use same column, but by declaring it again and using a different alias name, you still get an error : The column name ValueOfColumn specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

So what’s the solution? Solution is, declare the same column again, change the values in the column by some constant(you can add some constant, or you can concat some identifier ) and assign a new alias name to column.

Lets see the following example, I have used the AdventureWorks database of SQL Server 2005.


USE AdventureWorks GO SET ANSI_WARNINGS OFF SELECT CustomerId, SUM([Q2001]) AS Qty2001, SUM([Q2002]) AS Qty2002, SUM([V2001]) AS Val2001, SUM([V2002]) AS Val2002 FROM ( SELECT H.CustomerId, SUM(D.OrderQty) AS TotalQty, SUM(D.LineTotal) AS TotalVal, 'Q'+CONVERT(VARCHAR(4),H.OrderDate,120) AS QYear, 'V'+CONVERT(VARCHAR(4),H.OrderDate,120) AS VYear FROM Sales.SalesOrderDetail AS D INNER JOIN Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId WHERE D.ProductId=771 AND H.OrderDate >='20010101' AND H.OrderDate <'20030101' GROUP BY H.CustomerId, CONVERT(VARCHAR(4),H.OrderDate,120) )Main PIVOT ( SUM(TotalQty) FOR QYear IN ([Q2001],[Q2002]) ) PQ PIVOT ( SUM(TotalVal) FOR VYear IN ([V2001],[V2002]) ) PV GROUP BY CustomerId ORDER BY CustomerId GO

USE AdventureWorks
GO
SET ANSI_WARNINGS OFF
SELECT CustomerId,
        SUM([Q2001]) AS Qty2001,
        SUM([Q2002]) AS Qty2002,
        SUM([V2001]) AS Val2001,
        SUM([V2002]) AS Val2002
FROM (
        SELECT     H.CustomerId,
                SUM(D.OrderQty) AS TotalQty,
                SUM(D.LineTotal) AS TotalVal,
                'Q'+CONVERT(VARCHAR(4),H.OrderDate,120) AS QYear,
                'V'+CONVERT(VARCHAR(4),H.OrderDate,120) AS VYear
        FROM Sales.SalesOrderDetail AS D INNER JOIN
        Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId
        WHERE D.ProductId=771
        AND H.OrderDate >='20010101'
        AND H.OrderDate <'20030101'
        GROUP BY H.CustomerId,
                CONVERT(VARCHAR(4),H.OrderDate,120)
    )Main
PIVOT
    (
        SUM(TotalQty)
        FOR QYear IN ([Q2001],[Q2002])
    ) PQ
PIVOT
    (
        SUM(TotalVal)
        FOR VYear IN ([V2001],[V2002])
    ) PV
GROUP BY CustomerId
ORDER BY CustomerId
GO

The query returns total quantity and line amount for year 2001 and 2002 for the product id 771 for all customers. If look at the query carefully in Main sub query, CONVERT(VARCHAR(4),H.OrderDate,120) this convert statement will take out the Year part from the OrderDate column. I have declared the same column twice, at first I concatenated Q to the Year, and at second time I concatenated the V. Just execute the Main sub query, so it will be easy to understand for you.


SELECT H.CustomerId, SUM(D.OrderQty) AS TotalQty, SUM(D.LineTotal) AS TotalVal, 'Q'+CONVERT(VARCHAR(4),H.OrderDate,120) AS QYear, 'V'+CONVERT(VARCHAR(4),H.OrderDate,120) AS VYear FROM Sales.SalesOrderDetail AS D INNER JOIN Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId WHERE D.ProductId=771 AND H.OrderDate >='20010101' AND H.OrderDate <'20030101' GROUP BY H.CustomerId, CONVERT(VARCHAR(4),H.OrderDate,120)

SELECT     H.CustomerId,
                SUM(D.OrderQty) AS TotalQty,
                SUM(D.LineTotal) AS TotalVal,
                'Q'+CONVERT(VARCHAR(4),H.OrderDate,120) AS QYear,
                'V'+CONVERT(VARCHAR(4),H.OrderDate,120) AS VYear
FROM Sales.SalesOrderDetail AS D INNER JOIN
Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId
WHERE D.ProductId=771
                AND H.OrderDate >='20010101'
                AND H.OrderDate <'20030101'
GROUP BY H.CustomerId,
                CONVERT(VARCHAR(4),H.OrderDate,120)

Now we have 2 columns, with different values, and we can use them in different PIVOT with same effect, and that’s what I have done in my 1st query.

Here is a CASE expression version of same query, which gives much better performance if you scale it for large amount data.


USE AdventureWorks GO SELECT H.CustomerId, SUM(CASE YEAR(H.OrderDate) WHEN 2001 THEN D.OrderQty END) AS Qty2001, SUM(CASE YEAR(H.OrderDate) WHEN 2002 THEN D.OrderQty END) AS Qty2002, SUM(CASE YEAR(H.OrderDate) WHEN 2001 THEN D.LineTotal END) AS Val2001, SUM(CASE YEAR(H.OrderDate) WHEN 2002 THEN D.LineTotal END) AS Val2002 FROM Sales.SalesOrderDetail AS D INNER JOIN Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId WHERE D.ProductId=771 AND H.OrderDate >='20010101' AND H.OrderDate <'20030101' GROUP BY H.CustomerId ORDER BY H.CustomerId GO

USE AdventureWorks
GO
SELECT     H.CustomerId,
               SUM(CASE YEAR(H.OrderDate)
                      WHEN 2001
                      THEN D.OrderQty
                      END) AS Qty2001,
               SUM(CASE YEAR(H.OrderDate)
                      WHEN 2002
                      THEN D.OrderQty
                      END) AS Qty2002,
              SUM(CASE YEAR(H.OrderDate)
                     WHEN 2001
                     THEN D.LineTotal
                     END) AS Val2001,
              SUM(CASE YEAR(H.OrderDate)
                     WHEN 2002
                     THEN D.LineTotal
                     END) AS Val2002
FROM Sales.SalesOrderDetail AS D INNER JOIN
Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId
WHERE D.ProductId=771
          AND H.OrderDate >='20010101'
          AND H.OrderDate <'20030101'
GROUP BY H.CustomerId
ORDER BY H.CustomerId
GO

You can test the performance of both queries. If you want to scale it for larger data you can remove the WHERE conditions added by me. Total execution time for CASE query is almost half to that of PIVOT query.

TCP Port of SQL Server

2009-06-18T11:19:00.001+05:30

By default the default TCP Port for SQL Server is 1433. It can be changed for security purpose to minimize the potential threat of a hacker trying to access through the default port number. But that is whole together a different story.

Every now and then somebody posts this question on forums- how can I tell what port my SQL Server instance is running on? As sometimes while accessing SQL Server remotely you may need to add the TCP Port in the connection string.

There are more than 1 way to find out the answer. From SQL Server Error Logs, Configuration Manager, Command Prompt, Registry Editor. But today I will share a simple T-SQL script with you, from which you can easily find out the TCP port SQL Server instance is running on.

First lets see in regedit where to look for the Port number.
The location of port number for SQL Server 2005 -

For default instance : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

For Named Instance :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ [Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp

If you don’t know how to open the regedit :
1. Click on Start and then on Run.
2. In run type regedit and click on Ok.

After checking out the port number manually now lets see the query I created. It uses a non-documented extended stored procedure xp_regread. Basically it is helpful in reading from the registry via Transact-SQL. Since it is a non-documented extended stored procedure you won’t find any reference about xp_regread in Books Online. If you want to know more about it, best thing you can do is Google for xp_regread. So here is the script which will give you the TCP port SQL Server is running on :


DECLARE @TcpPort VARCHAR(5) ,@RegKey VARCHAR(100) IF @@SERVICENAME !='MSSQLSERVER' BEGIN SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp' END ELSE BEGIN SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP' END EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE' ,@key = @RegKey ,@value_name = 'TcpPort' ,@value = @TcpPort OUTPUT SELECT @TcpPort AS PortNumber ,@@SERVERNAME AS ServerName ,@@SERVICENAME AS ServiceName

DECLARE @TcpPort VARCHAR(5)
,@RegKey VARCHAR(100)

IF @@SERVICENAME !='MSSQLSERVER'
    BEGIN
        SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp'
    END
    ELSE
    BEGIN
        SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'
    END

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpPort'
    ,@value = @TcpPort OUTPUT

SELECT @TcpPort AS PortNumber
,@@SERVERNAME AS ServerName
,@@SERVICENAME AS ServiceName

You will get the output something like this :

Other simple way of finding out the TCP port is from the Configuration Manager.
1. Start >> All Program >> Microsoft SQL Server 2005 >> Configuration Tools >> SQL Server Configuration Manager .
2. In the configuration manager click on SQL Server 2005 Network Configuration.
3. Then click on the Instance name for which you want to find out the Port Number.
4. Double click on TCP/IP.
5. Finally on IP Addresses, and you will see something like :

Alternating Row Background Color in Reports

2009-06-12T13:31:00.001+05:30

Now lets see how to get alternating row background colors in Reports. This is one of the 1^st typical report enhancement you might be doing once you started working on Reporting Services. See the following report :

This can be achieved very easily in SSRS with the help of IIF and RowNumber function for a simple report with no groupings. You can use a background color expression as:


= IIF(RowNumber(Nothing) Mod 2, "White","Gainsboro")

It becomes little complicated in cases of Matrix and when some groupings are involved. In that case use following expression for the details row within a group:


= IIF(RunningValue(Fields!Some_Field.Value,Count,"Group_Name") Mod 2, "White","Gainsboro")

Here instead of RowNumber function I have used he RunningValue function. Just remember to replace Some_Field with actual column name and Group_Name with actual Group Name in your report.

Multiplying Column Values

2009-06-04T15:30:00.001+05:30

Well we all know how to add the values in a column using SUM and GROUP BY. But what about multiplying the column values? And also how to calculate running multiplication on a column similar to Running Totals?

Sometime back I came across this interesting question on MSDN SQL Server forums - How to multiply all values in a table column?

I don't know the reason of such requirement, but very interesting problem though. Of course there is no such built-in function in SQL Server to do it for you. But after thinking on it for few minutes, I realize that if you still remember the basics of Mathematics, it is not that difficult to do in T-SQL.

If you remember

A * B = ANTILOG ( LOG (A) + LOG(B) )

So looking at it again, it is very straight forward using LOG, ANTILOG and SUM in SQL Server. (For doing ANTILOG you have EXP function in SQL Server.) Let me show you how :

Lets create some sample data first.


-- Create sample table CREATE TABLE Scores ( ID INT IDENTITY, Category VARCHAR(1), ) GO -- Load sample data into table INSERT INTO Scores(Category, Score) SELECT 'A', 4 UNION ALL SELECT 'A', 5 UNION ALL SELECT 'A', 2 UNION ALL SELECT 'B', 5 UNION ALL SELECT 'B', 5 UNION ALL SELECT 'B', 2 UNION ALL SELECT 'B', 3 GO -- test the sample data SELECT Id, Category, Score FROM Scores GO

The sample data :

ID	CATEGORY	SCORE
1	A	4
2	A	5
3	A	2
4	B	5
5	B	5
6	B	2
7	B	3

Multiplying Column Values: And here is the query to calculate the Total Product of Score Column for each Category.


SELECT Category, SUM(Score) as TotalSum,EXP(SUM(LOG(Score))) as TotalProduct FROM Scores GROUP BY Category

Result :

Category	TotalSum	TotalProduct
A	11	40
B	15	150

Second column you have normal total SUM for the Score column and in 3^rd column you have the total product of the Score column for each Category.

Calculating The Running Multiplication: After multiplying the column values, next question is how to calculate the Running Product similar to Running totals? See the following table -

ID	Category	Score	RunningProduct
1	A	4	4
2	A	5	20
3	A	2	40
4	B	5	5
5	B	5	25
6	B	2	50
7	B	3	150

The logic is similar to what I just mentioned for Multiplying Rows and adding the Running Total logic to query.


SELECT O.Id, O.Category, O.Score ,( SELECT EXP ( SUM ( LOG (I.Score) ) ) FROM Scores I WHERE I.Category = O.Category AND I.Id <= O.Id ) as RunningProduct FROM Scores O

But remember this solution only works for positive numbers, if column contains negative values then you will need to modify the query using ABS function and taking into account number of negative values. Well but at least you will get a start from this post.

Expiration Date of SQL Server Evaluation Edition

2009-05-22T18:45:00.001+05:30

If you have installed the Evaluation / Trial Version of SQL Server, you may like to find out what is the expiry date of the Trial Version. The Trial Edition or Evaluation Edition of SQL Server will expire exactly 180 days after the install date. Using it after the 180 days period is violation of MICROSOFT evaluation license terms. Anyways after 180 days when an install of Evaluation Edition Expires, the SQL Server services stop functioning. So you must purchase the license for SQL Server and upgrade the Evaluation copy before the expiry date of the Evaluation.

But then how to find out the exact expiry date of Trial Version? There are 2 ways to find out

From installation logs
From Management Studio

From installation logs
A summary.txt file is get created during the installation of SQL Server. So this summary.txt can give you the exact date and time of the SQL Server installation. And the trial software will automatically expire after six months. You can find summary.txt file under


<Drive>\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

At the end of the file you can see something like


Setup succeeded with the installation, inspect the log file completely for status on all the components. Time : Tue Dec 30 15:59:55 2008

Setup succeeded with the installation, inspect the log file completely for status on all the components.

Time : Tue Dec 30 15:59:55 2008

So after 180 days counting from 30 Dec 2008 i.e. 28 Jun 2009 the SQL Server Evaluation Edition will be expired.

From Management Studio
Now this is very simple. Open the Management Studio, click on the “Help” of Menu Bar and then on “About”. A new window will appear, where you will see some thing like:


Microsoft SQL Server Management Studio (expires in 102 days)

See the Image :

Calculating Running Totals

2009-05-20T15:20:00.001+05:30

In one of my previous posts I wrote about Calculating Running Totals using a SELF JOIN. But after realizing that, SELF JOIN is not efficient way of calculating Running Totals I thought of writing this post, with another technique without using Cursor.

In this post I'll show you how to Calculate Running Totals without using a cursor. On the same line with my previous post, the different scenarios I’ll be covering in this post are -

Running Total for all records.
Running Total on each Group of Records.
Running Total on each Date.

Here is our sample table :

ShipId	OrderId	ShipDate	Quantity
1	1	2009-02-01 12:06:16.820	10
2	1	2009-02-02 16:16:16.820	15
3	2	2009-02-01 13:26:16.820	20
4	2	2009-02-03 17:40:16.820	12
5	2	2009-02-04 13:05:16.820	15
6	3	2009-02-03 14:25:16.820	25
7	3	2009-02-04 15:50:16.820	50
8	3	2009-02-05 19:40:16.820	30
9	4	2009-02-06 15:30:16.820	20
10	4	2009-02-07 16:20:16.820	10
11	5	2009-02-05 17:05:16.820	35

Create Sample Data:

-- Create Table
CREATE TABLE Sales
( ShipId INT,
OrderId INT,
ShipDate DateTime,
Quantity INT )
GO
-- Load Sample Data
INSERT INTO Sales SELECT
1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT
2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT
3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT
4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT
5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT
6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT
7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT
8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT
9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT
10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT
11,5, 'Feb 05 2009 05:05:16:820PM',35

Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I'll be using a correlated sub query to calculate the Running Totals for the all the records. So the query will be :


SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity ,(SELECT SUM(I.Quantity) FROM Sales I WHERE I.ShipId <= O.ShipId ) as RunningTotal FROM Sales O ORDER BY O.ShipId

Here is the result returned by above query :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	45
4	2	2009-02-03 17:40:16.820	12	57
5	2	2009-02-04 13:05:16.820	15	72
6	3	2009-02-03 14:25:16.820	25	97
7	3	2009-02-04 15:50:16.820	50	147
8	3	2009-02-05 19:40:16.820	30	177
9	4	2009-02-06 15:30:16.820	20	197
10	4	2009-02-07 16:20:16.820	10	207
11	5	2009-02-05 17:05:16.820	35	242

Case II: Running Total on each Group of Records.
Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one condition in WHERE clause of , that will be - I.OrderId = O.OrderId. So the final query will be:


SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity ,(SELECT SUM(I.Quantity) FROM Sales I WHERE I.OrderId = O.OrderId AND I.ShipId <= O.ShipId ) as RunningTotal FROM Sales O ORDER BY OrderId

And the Output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	20
4	2	2009-02-03 17:40:16.820	12	32
5	2	2009-02-04 13:05:16.820	15	47
6	3	2009-02-03 14:25:16.820	25	25
7	3	2009-02-04 15:50:16.820	50	75
8	3	2009-02-05 19:40:16.820	30	105
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	30
11	5	2009-02-05 17:05:16.820	35	35

Case III: Running Total on each Date.
Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our WHERE clause, observe them carefully and try to understand what I’m trying to do :


SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity ,(SELECT SUM(I.Quantity) FROM Sales I WHERE I.shipDate <= O.Shipdate AND DateAdd(dd, DateDiff (dd, 0, I.Shipdate), 0) = DateAdd(dd, DateDiff (dd, 0, O.Shipdate) ,0) ) as RunningTotal FROM Sales O ORDER BY O.ShipDate

And the output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
3	2	2009-02-01 13:26:16.820	20	30
2	1	2009-02-02 16:16:16.820	15	15
6	3	2009-02-03 14:25:16.820	25	25
4	2	2009-02-03 17:40:16.820	12	37
5	2	2009-02-04 13:05:16.820	15	15
7	3	2009-02-04 15:50:16.820	50	65
11	5	2009-02-05 17:05:16.820	35	35
8	3	2009-02-05 19:40:16.820	30	65
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	10

Mangal Pardeshi

GROUPING Function SQL Server

2009-05-15T15:36:00.001+05:30

If you are going to use ROLLUP or CUBE operators in future or used it in past, then this is the post you must read.

Well GROUPING is one of the least used function in T-SQL, even in cases where it should be used. And so many times instead of using GROUPING function we follow some wrong/bad practice by using ISNULL only. I’ll just give you a small example of it in this post.

First lets understand what the GROUPING function is – From Books on line

GROUPING (Transact-SQL) : Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.

Syntax : GROUPING ( <column_expression> )

Arguments <column_expression> : Is a column or an expression that contains a column in a GROUP BY clause.

To put it in simple words : GROUPING is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.

Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.

So after reading it from the books on line “what the GROUPING function is”, you might like to ask me where it can be useful? And what is wrong practice I was talking about at the start of this post?

Well answer to 1^st question is, it is very useful while using ROLLUP or CUBE operator. Especially when you have some NULL values in any of the columns used in GROUP BY clause. Basically it helps us to identify which row is added by CUBE or ROLLUP operator. Need of this function arises because when CUBE or ROLLUP operator adds the subtotals and totals rows to result SQL Server returns NULL in related GROUP columns. And in such cases it is difficult to differentiate between actual NULL values from the column and the NULL returned by CUBE or ROLLUP operator.

Now to understand how it works and the wrong practice I was talking about, lets see following example.

For example I'll take a simplified table of sales representatives and and their Sales in respective Zones.


-- create sample table Sales CREATE TABLE Sales ( ID INT, FName VARCHAR(30), Zone VARCHAR(30), Sale INT ) GO -- Load sample data INSERT INTO Sales SELECT 1, 'Mangal', 'East', 20 UNION ALL SELECT 2, 'Mangal', 'East', 150 UNION ALL SELECT 3, 'Mangal', 'West', 50 UNION ALL SELECT 4, 'Ram', 'East', 45 UNION ALL SELECT 5, 'Ram', NULL, 80 UNION ALL SELECT 6, 'Ram', NULL, 40 UNION ALL SELECT 7, 'Sachin', 'West', 50 UNION ALL SELECT 8, 'Sachin', 'West', 40 GO -- Test sample data SELECT Id, FName, Zone, Sale FROM Sales GO

-- create sample table Sales

CREATE TABLE Sales
(
ID INT,
FName VARCHAR(30),
Zone VARCHAR(30),
Sale INT
)
GO

-- Load sample data

-- Test sample data

SELECT Id, FName, Zone, Sale
FROM Sales
GO

The sample data :

ID	FName	Zone	Sale
1	Mangal	East	20
2	Mangal	East	150
3	Mangal	West	50
4	Ram	East	45
5	Ram	NULL	80
6	Ram	NULL	40
7	Sachin	West	50
8	Sachin	West	40

Now here is what we normally do when somebody asks us to summarize the sale column on FName with subtotals for each Zone : We write a SELECT query with GROUP BY FName, Zone WITH ROLLUP. And when we see NULLS coming in Zone/FName column because of ROLLUP we add a ISNULL function on Zone column (in this example we'll add ISNULL only on Zone column, as I have intentionally added some NULLS in Zone column's sample data ). And that is where we go wrong, the Wrong Practice I was talking about.


SELECT Fname, ISNULL(Zone, 'All Zone') as Zone, SUM(Sale) AS Total FROM Sales GROUP BY Fname, Zone WITH ROLLUP ORDER BY FName

The output :

FName	Zone	Total
NULL	All Zone	475
Mangal	East	170
Mangal	West	50
Mangal	All Zone	220
Ram	All Zone	120
Ram	East	45
Ram	All Zone	165
Sachin	West	90
Sachin	All Zone	90

As you can see in the result, for Mangal and Sachin everything looks fine, but for Ram there are two “All Zone” in Zone column. Well 1st 'All Zone' is because of actual NULL value in Zone column and 2nd All Zone is sub total returned by ROLLUP operator for all Zones for Ram. But ISNULL is not capable of differentiating between these two NULLS and blindly replaces them with 'All Zone'. And that's when GROUPING function comes in for our help. Now 1st just see what GROUPING returns, execute the following query -


SELECT FName,Zone, SUM(Sale) AS Total, GROUPING(Zone) as 'Grouping' FROM Sales GROUP BY FName, Zone WITH ROLLUP ORDER BY FName

And the result :

FName	Zone	Sale	Grouping
NULL	NULL	475	1
Mangal	East	170	0
Mangal	West	50	0
Mangal	NULL	220	1
Ram	NULL	120	0
Ram	East	45	0
Ram	NULL	165	1
Sachin	West	90	0
Sachin	NULL	90	1

In above result wherever you see 1 in Grouping column, that means that row is added by ROLLUP operator. And wherever you see 0 in Grouping column, that means that row is not returned by ROLLUP. So now identifying between NULL by ROLLUP and NULL from column becomes easy for us. Now we can use this GROUPING indicator in our query, to identify the NULL rows added by ROLLUP operator, and NULLS from the column.

So our final query will be :


SELECT Fname,CASE GROUPING(Zone) WHEN 1 THEN 'All Zone' ELSE ISNULL(Zone, 'Unknown') END as Zone, SUM(Sale) AS Total FROM Sales GROUP BY Fname, Zone WITH ROLLUP ORDER BY FName

And the output:

FName	Zone	Total
NULL	All Zone	475
Mangal	East	170
Mangal	West	50
Mangal	All Zone	220
Ram	Unknown	120
Ram	East	45
Ram	All Zone	165
Sachin	West	90
Sachin	All Zone	90

So now, all NULLS from column are replaced by 'Unknown' and all subtotals added by ROLLUP comes with 'All Zone' in Zone column.

The GROUPING function becomes even more useful while using CUBE operator.

Mangal Pardeshi

Query to fetch the Yesterday's Data

2009-04-29T17:05:00.001+05:30

Seems to be easy question. And many of you may be already knew 2-3 ways of doing it. Here is the query I use normally to get the all the data from the previous day(Yesterday).

SELECT Id, OrderId, CustomerId
FROM Orders
WHERE Date >= DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0)
AND Date < DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)

Make it more readable and scalable using variable

DECLARE @YesterDay DATETIME, @Today DATETIME
SET @YesterDay = DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0)
SET @Today = DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)

SELECT Id, OrderId, CustomerId
FROM Orders
WHERE Date >= @YesterDay
AND Date < @Today

Logic : Basically above WHERE conditions is like - Greater than Equal to Yesterday and Less than Today.

This DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) gives Yesterday's date with time as 00:00:00.

And this DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0) gives today's date with time as 00:00:00.

You can do a quick check by executing -

SELECT DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) AS YDay
,DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0) as ToDay

Reason for doing this way instead of using some obvious ones – As you may heard this from many SQL experts, “Whenever possible never use CONVERT/CAST or any functions in WHERE clause on table's column.”

Question is Why?

Well whenever you use the any function on a column , SQL Server no longer uses the Index define on particular column. And use of a CAST or CONVERT functions slows down the query execution considerably.

You may say, I used the DateAdd and DateDiff function in the WHERE clause. Yes, I used them, but NOT on Date column from the table. I used them with the Getdate function which is more of a constant and NOT part of the table's column.

Query Optimization Tip :

Whenever possible never use CONVERT/CAST or any functions in WHERE clause on table's column.

In future I'll have some more posts on Date comparison in WHERE condition. Also some more simple tricks to optimized the everyday's queries.

Mangal Pardeshi
SQL MVP

SQL Server 2008 Service Pack 1 is Released

2009-04-08T17:33:00.001+05:30

Hi All,

Service Pack 1 for SQL Server 2008 is now available.

You can download it from - SQL Server 2008 Service Pack 1

For more information about SQL Server 2008 Service Pack 1, please review the Release Notes.

You can also follow the following blog for more details -SQL Server Setup

Regards

Mangal Pardeshi

UNPIVOT Multiple Columns

2009-04-01T00:37:00.001+05:30

Unlike PIVOT which de-normalizes the data, UNPIVOT is more helpful in getting your data back to a more normalized form.

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

For a single column UNPIVOT examples and for more on PIVOT and UNPIVOT you can read from Microsoft Books Online - Using PIVOT and UNPIVOT

In this article I’ll try to show you how to UNPIVOT multiple columns in a single query. There is simple trick to do that, if you are familiar with the UNPIVOT operator you will find it easy to understand.

First lets create some sample data :

-- Create sample table
CREATE TABLE Suppliers
(Id INT,
Product VARCHAR(500),
Supplier1 VARCHAR(500),
Supplier2 VARCHAR(500),
Supplier3 VARCHAR(500),
City1 VARCHAR(500),
City2 VARCHAR(500),
City3 VARCHAR(500)
)
GO

-- Load Sample data
INSERT INTO Suppliers SELECT
1, 'Car', 'Tata Motors', 'Bajaj', 'Mahindra', 'Jamshedpur','Pune','Mumbai'
UNION ALL SELECT
2, 'Bike', 'Bajaj', 'Hero Honda', 'Suzuki', 'Pune', 'New Delhi', 'Chandigarh'
UNION ALL SELECT
3, 'Cycle', 'Hercules', 'Hero', 'Atlas', 'Mumbai', 'Banglaore', 'Pune'
GO

-- Test sample data
SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3
FROM Suppliers
GO

Here is how our sample table looks like :

Id	Product	Supplier1	Supplier2	Supplier3	City1	City2	City3
1	Car	TataMotors	Bajaj	Mahindra	Jamshedpur	Pune	Mumbai
2	Bike	Bajaj	Hero Honda	Suzuki	Pune	New Delhi	Chandigarh
3	Cycle	Hercules	Hero	Atlas	Mumbai	Bangalore	Pune

And as you can see here we have 2 columns which needs to be UNPIVOT, first is Supplier and second is there respective Cities. If it was single column to be UNPIVOTed it could have been a straight forward task. Thing is you can write a query with 2 UNPIVOT operators, but question is how to JOIN two UNPIVOT sub queries correctly so we will get the each supplier with its respective city next to it in second column?

As I said earlier there is simple trick to do that. See the following query and its WHERE condition :

SELECT Id,
    Product,
    ROW_NUMBER()OVER(Partition By Id Order By Suppliers) as SuppId,
    SupplierName,
    CityName
FROM
(
SELECT Id, Product, Supplier1, Supplier2, Supplier3, City1, City2, City3
FROM Suppliers
) Main

UNPIVOT
(
SupplierName FOR Suppliers IN (Supplier1, Supplier2, Supplier3)
) Sup

UNPIVOT
(
CityName For Cities IN (City1, City2, City3)
) Ct

WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1)

And here is the Output of the query :

Id	Product	SuppId	SupplierName	CityName
1	Car	1	Tata Motors	Jamshedpur
1	Car	2	Bajaj	Pune
1	Car	3	Mahindra	Mumbai
2	Bike	1	Bajaj	Pune
2	Bike	2	Hero Honda	New Delhi
2	Bike	3	Suzuki	Chandigarh
3	Cycle	1	Hercules	Mumbai
3	Cycle	2	Hero	Bangalore
3	Cycle	3	Atlas	Pune

So the trick was this where condition -

WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1)

There are other ways also to UNPIVOT the multiple columns, may be some other time I’ll post them too.

Source of inspiration for this post is following thread on MSDN forums - http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/54626179-ee9f-4db7-a31e-c02104bdd6beAt

At the end of it, OP also come up with his own solution he got from SQLTeam’s forums.

Mangal Pardeshi

RunningValue Function (Reporting Services) – Calculating Running Totals

2009-03-21T22:50:00.001+05:30

In one of my previous posts I talked about Calculating Running Totals in T-SQL. Now I will show you how you can calculate the running totals in Sql Server Reporting Services(SSRS). Yes, it is very much possible with RunningValue function in SSRS and also very easy compare to doing it in SQL.

The syntax for RunningValue function goes like this -

RunningValue(expression, function, scope)

expression : The expression on which to perform the aggregation, for example, [Quantity].
function : The name of the aggregate function to apply to the expression, for example- Sum.
scope :The name of a dataset, data region, group or Nothing.

See the table below and RunningTotal column which is Cumulative SUM of Quantity column.

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	45
4	2	2009-02-03 17:40:16.820	12	57
5	2	2009-02-04 13:05:16.820	15	72
6	3	2009-02-03 14:25:16.820	25	97
7	3	2009-02-04 15:50:16.820	50	147
8	3	2009-02-05 19:40:16.820	30	177
9	4	2009-02-06 15:30:16.820	20	197
10	4	2009-02-07 16:20:16.820	10	207
11	5	2009-02-05 17:05:16.820	35	242

For achieving that in SSRS, add extra column in your Report’s Layout Page and put following expression in it-

=RunningValue(Fields!Quantity.Value, Sum, Nothing)

Yes, it is easy and simple, isn’t it? But what about when you want the RunningTotals for group of records? Like for above example you want the Running Totals for each orderId separately. See the table below :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	20
4	2	2009-02-03 17:40:16.820	12	32
5	2	2009-02-04 13:05:16.820	15	47
6	3	2009-02-03 14:25:16.820	25	25
7	3	2009-02-04 15:50:16.820	50	75
8	3	2009-02-05 19:40:16.820	30	105
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	30
11	5	2009-02-05 17:05:16.820	35	35

Now for achieving that you need do followings :

First go to Report Layout page and add a new Group. Give some name to the group. If you don’t gave any name the default name would be something like “table1_Group1”. Just note that. And add OrderId to “Group On” expression.
Now change the expression at RunningTotal column as

=RunningValue(Fields!Quantity.Value,SUM,"table1_Group1")

Now if you have noticed I have replaced the scope Nothing to Group name “table1_Group1” .

Well after adding the extra group in the report you might see blank spaces after every OrderId change. If you don’t want those blanks and want continuous report, then go to the property page of the new group added and set Visible to “false”.

Mangal Pardeshi

SQL MVP

Script All the Stored Procedures in The Database

2009-03-16T17:24:00.001+05:30

In Sql Server 2005 and 2008 you can script the stored procedure in Management Studio by right clicking on Store Procedure name and clicking on “Script Store Procedure as” and then “Create To”.

But if you want to script all the Stored Procedures in the database programmatically, then here is the simple T-SQL query for it -

To script All the Stored Procedures in the Database :

SELECT    O.Name as ProcName
        ,M.Definition as CreateScript
        ,O.Create_Date
        ,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.objects as O
ON M.object_id = O.object_id
WHERE O.type = 'P'

If the Stored Procedure is created with ENCRYPTION option then you will get the NULL in the definition column.

Similarly,

To script All the Views in the Database :

To script All the Functions in the Database :

For scripting all Triggers small modification is required, instead of sys.objects I joined the sys.triggers with sys.sql_modules.

To script All the Triggers in the Database :

SELECT    O.Name as ProcName
        ,M.Definition as CreateScript
        ,O.Create_Date
        ,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id

Mangal Pardeshi
SQL MVP

How To Split A Comma Delimited String

2009-03-03T18:35:00.001+05:30

In one of my previous posts I wrote about “How to Create a Comma Delimited List”. Now I’ll show you an example with reverse action, that is Splitting a Comma Delimited String or array() of values.

There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab.

For example here is our Sample Table -

Id	AllNames
1	A,B,C
2	A,B
3	X,Y,Z

And here is the expected output -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

Create Sample Data :

-- Create Table for Sample Data
CREATE TABLE Test
(
ID INT,
AllNames VARCHAR(100)
)
GO
-- Load Sample Data
INSERT INTO test SELECT
1, 'A,B,C' UNION ALL SELECT
2, 'A,B' UNION ALL SELECT
3, 'X,Y,Z'
GO

-- Verify the Sample Data
SELECT Id, AllNames
FROM Test

And here is the query for How to split a comma delimited string :

;WITH Cte AS

(
    SELECT
        id,
        CAST('<M>' + REPLACE( Allnames, ',' , '</M><M>') + '</M>' AS XML) AS Names
    FROM Test
)
SELECT
    ID,
    Split.a.value('.', 'VARCHAR(100)') AS Names
FROM Cte
CROSS APPLY Names.nodes('/M') Split(a)

Acknowledgement : Well seriously don’t know. Somewhere I came across this solution while answering / participating on MSDN Sql Server Forums. So credit goes to my fellow Moderators/answrers on MSDN Forums.

Actually whatever expertise I gained on T-SQL and Sql Server is by participating / answering to the posts on online forums. I’m one of those freaks who hate to read books ;)

- Mangal Pardeshi

How to Disable Right Click on your blog

2009-03-01T00:11:00.002+05:30

Apart from writing articles on Sql every now and then I would also like to share some tips on blog writing with my fellow bloggers. To start with I’ll share a simple a trick on “How to disable the Right Click” on your blog, so that others can’t EASILY copy contents from your blog. Well I think don’t need to write in detail why the hell you want to do that, so here you go -

How to Disable Right Click on your blog :

1. Go to Layout page of your Blog Dashboard.

2. Click on Edit HTML tab.

3. And find <body>

4. Add oncontextmenu="return false;" after the body so it will look like this -

5. Just check ones and if you don't get any error messages, save the template.

That’s it!! Now right click is disable from blog and nobody will be able do a simple right click and copy contents or photos from your blog.

Though with above trick right click is disable still it is not that impossible to copy contents or photos from your blog.

- Mangal Pardeshi.

Create A Comma Delimited List From a Column

2009-02-23T17:32:00.003+05:30

In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values).

For example here is our Sample Table -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

And here is the expected output -

Id	AllNames
1	A, B, C
2	A, B
3	X, Y, Z

Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter.

Create Sample Data -

--Create sample table
CREATE TABLE Test
(
Id INT,
Names VARCHAR(100)
)
GO
-- Load sample data
INSERT INTO Test SELECT
1,'A' UNION ALL SELECT
1,'B' UNION ALL SELECT
1,'C' UNION ALL SELECT
2,'A' UNION ALL SELECT
2,'B' UNION ALL SELECT
3,'X' UNION ALL SELECT
3,'Y' UNION ALL SELECT
3,'Z'
GO

SQL Server 2005 / SQL Server 2008 Solution for Comma delimiter.
1st Solution -

SELECT T1.Id
,AllNames = SubString (( SELECT ', ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

If the column “Names” doesn’t contain any spaces in between its values then here is another solution -

2nd solution -

SELECT T1.Id
,AllNames = REPLACE (( SELECT T2.Names AS 'data()'
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), ' ', ', ')
FROM Test as T1
GROUP BY Id

Advantage with 2nd solution is that if the data type of column whose value you want to concatenate is INT or any other noncharacter data type you don’t need to CONVERT it into varchar data type. In 1st solution you will need to convert the column to Varchar if its original data type is numeric. But 2nd solution will fail in case there are some in-between spaces in column values.

Now if you want to create the list using other delimiter, replace comma from above query with your required delimiter.

E.g. for Vertical Bar

SELECT T1.Id
,AllNames = SubString (( SELECT '| ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

Mangal Pardeshi

SQL MVP

SQL Server Versions and Editions.

2009-02-20T18:40:00.008+05:30

Lets see how to find which Sql Server Version is installed and Service Packs applied on your machine-

To know Execute

SELECT @@version

If you are on Sql Server 2000 or later use following which will give you a more elegant output -

SELECT SERVERPROPERTY('productversion') as VersioNumber
,SERVERPROPERTY ('productlevel') as SPLevel
,SERVERPROPERTY ('edition') as ServerEdition

And here is detailed chart which will show you SQL Server version numbers and the corresponding product or service pack level -

Sr No	Version Number	Release	SP Level
10.1	10.0.1600.22	SQL Server 2008	RTM
-	-	-	-
9.1	9.0.1399.00	SQL Server 2005	RTM
9.2	9.0.2047.00	SQL Server 2005	SP1
9.3	9.0.3042.00	SQL Server 2005	SP2
9.4	9.0.4035.00	SQL Server 2005	SP3
-	-	-	-
8.1	8.00.194.00	SQL Server 2000	RTM
8.2	8.00.384.00	SQL Server 2000	SP1
8.3	8.00.534.00	SQL Server 2000	SP2
8.4	8.00.760.00	SQL Server 2000	SP3
8.5	8.00.2039.00	SQL Server 2000	SP4
-	-	-	-
7.1	7.00.623.00	SQL Server 7.0	RTM
7.2	7.00.699.00	SQL Server 7.0	SP1
7.3	7.00.842.00	SQL Server 7.0	SP2
7.4	7.00.961.00	SQL Server 7.0	SP3
7.5	7.00.1063.00	SQL Server 7.0	SP4
-	-	-	-
6.51	6.50.201.00	SQL Server 6.5	RTM
6.52	6.50.213.00	SQL Server 6.5	SP1
6.53	6.50.240.00	SQL Server 6.5	SP2
6.54	6.50.258.00	SQL Server 6.5	SP3
6.55	6.50.281.00	SQL Server 6.5	SP4
6.56	6.50.415.00	SQL Server 6.5	SP5
6.57	6.50.416.00	SQL Server 6.5	SP5a
6.58	6.50.479.00	SQL Server 6.5	SP5a update
-	-	-	-
6.01	6.00.121.00	SQL Server 6.0	RTM
6.02	6.00.124.00	SQL Server 6.0	SP1
6.03	6.00.139.00	SQL Server 6.0	SP2
6.04	6.00.151.00	SQL Server 6.0	SP3

- Mangal Pardeshi

Excel 2007 Linked Server

2009-02-20T12:15:00.008+05:30

Lets see How to create a linked server with Excel 2007. Well most of the stuff you'll find on the net on how to create a linked Server is using Microsoft Jet 4.0 OLE DB Provider which is no longer able to connect to Excel 2007.

So here you go...
We will use "Microsoft.ACE.OLEDB.12.0" which come with Office 2007 pack. If you don't have it on Provider's list you can download it from 2007 Office System Driver: Data Connectivity Components

After downloading and Installing the ACE driver you can create the Linked server as

exec sp_addLinkedServer
@server= 'XLFile',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'E:\Mangal.xls',
@provstr = 'Excel 12.0; HDR=No'

If you have the Column names in Excel do HDR=yes.

Now you can query your Excel just like any table - SELECT * FROM XLFile...[sheet1$]

Or if you don't want to create a Linked Server, and just want to execute the Excel then do -
SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=E:\Mangal.xls;HDR=No', 'Select * from [Sheet1$]' )
And if you execute the above query and you get the error
Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off. That means first you need to enable the 'Ad Hoc Distributed Queries'.

For that first execute the following scripts. 
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
For Creating Linked Server from Management Studio:
Open the Management studio, connect to your server. And follow the steps
1. Go to "Server Objects" then Right CLick on "Linked Servers" and write "New Linked Server".
2. Give any name to your linked server. In our example I've given "XL".
3. Now select Server Type as "Other Data Source". Now select the provider"Microsoft Office 12.0 Access Database Engine OLE DB Provider."
4. Product Name : ACE 12.0
5. Date Source : E:\Mangal.xls --- Here you need to put the path of your Excel Sheet.
e.g. C:\Folder\New.xls Provider String : Excel 12.0; HDR=No 
Again remember if you have column names in Excel then HDR=Yes.

- Mangal Pardeshi.

Calculating Running Totals in SQL

2009-02-03T14:06:00.013+05:30

NOTE: After realizing that, SELF JOIN is not efficient way of calculating Running Totals so I added another post on how to Calculate Running Totals using a co-related sub-query. So instead of using the technique I used in this post you should use technique mentioned in my new post - How to Calculating Running Totals

In this article I will show you a few different techniques for calculating Running Total on multiple rows. The different scenarios I’ll be covering in this post are -

Running Total for all records.
Running Total on each Group of Records.
Running Total on each Date.

Here is our sample table :

ShipId	OrderId	ShipDate	Quantity
1	1	2009-02-01 12:06:16.820	10
2	1	2009-02-02 16:16:16.820	15
3	2	2009-02-01 13:26:16.820	20
4	2	2009-02-03 17:40:16.820	12
5	2	2009-02-04 13:05:16.820	15
6	3	2009-02-03 14:25:16.820	25
7	3	2009-02-04 15:50:16.820	50
8	3	2009-02-05 19:40:16.820	30
9	4	2009-02-06 15:30:16.820	20
10	4	2009-02-07 16:20:16.820	10
11	5	2009-02-05 17:05:16.820	35

Create Sample Data:

-- Create Table
CREATE TABLE Sales
( ShipId INT,
OrderId INT,
ShipDate DateTime,
Quantity INT )
GO
-- Load Sample Data

INSERT INTO Sales SELECT
1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT
2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT
3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT
4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT
5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT
6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT
7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT
8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT
9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT
10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT
11,5, 'Feb 05 2009 05:05:16:820PM',35

Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I will JOIN the Sales table with itself. So the query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal
FROM Sales A INNER JOIN Sales B
ON A.ShipId >= B.ShipId
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity

Here is the result returned by above query :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	45
4	2	2009-02-03 17:40:16.820	12	57
5	2	2009-02-04 13:05:16.820	15	72
6	3	2009-02-03 14:25:16.820	25	97
7	3	2009-02-04 15:50:16.820	50	147
8	3	2009-02-05 19:40:16.820	30	177
9	4	2009-02-06 15:30:16.820	20	197
10	4	2009-02-07 16:20:16.820	10	207
11	5	2009-02-05 17:05:16.820	35	242

Case II: Running Total on each Group of Records.

Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one AND condition in ON clause, that will be - A.OrderId = B.OrderId. So the final query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B
ON A.ShipId >= B.ShipId
AND A.OrderId = B.OrderId
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity
ORDER BY OrderId

And the Output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	20
4	2	2009-02-03 17:40:16.820	12	32
5	2	2009-02-04 13:05:16.820	15	47
6	3	2009-02-03 14:25:16.820	25	25
7	3	2009-02-04 15:50:16.820	50	75
8	3	2009-02-05 19:40:16.820	30	105
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	30
11	5	2009-02-05 17:05:16.820	35	35

Case III: Running Total on each Date.

Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our ON clause, observe them carefully and try to understand what I’m trying to do :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal
FROM Sales A INNER JOIN Sales B
ON A.shipDate >= B.Shipdate
AND DateAdd(dd, DateDiff (dd, 0, A.Shipdate), 0) = DateAdd(dd, DateDiff (dd, 0, B.Shipdate) ,0)
GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity
ORDER BY A.ShipDate

And the output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
3	2	2009-02-01 13:26:16.820	20	30
2	1	2009-02-02 16:16:16.820	15	15
6	3	2009-02-03 14:25:16.820	25	25
4	2	2009-02-03 17:40:16.820	12	37
5	2	2009-02-04 13:05:16.820	15	15
7	3	2009-02-04 15:50:16.820	50	65
11	5	2009-02-05 17:05:16.820	35	35
8	3	2009-02-05 19:40:16.820	30	65
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	10

Hopefully these examples will help you understand different methods that can be used to calculate running totals.

- Mangal Pardeshi

RPAD and LPAD Functions in Sql Server

2009-01-27T21:13:00.003+05:30

In Sql Server we don’t have RPAD and LPAD functions as they are in Oracle. But it is not that difficult to implement the same thing in Sql Server. With the help of RIGHT and LEFT functions we can achieve that easily. Let’s see how…

First create this table:
CREATE TABLE Temp(Id INT)
GO

Now insert the table Temp with some sample data:
INSERT INTO Temp SELECT
1 UNION SELECT
2 UNION SELECT
12 UNION SELECT
123 UNION SELECT
1234 UNION SELECT
12345
GO

And here is our RPAD and LPAD query

SELECT ID
,RIGHT( '00000' + CONVERT(VARCHAR(5), Id), 5) as LPAD_Example
,LEFT( CONVERT (VARCHAR(5), Id)+'00000' , 5) as RAPD_Example
FROM Temp

--END--

- Mangal Pardeshi

Linked Server Error : Msg 7399, Level 16, State 1

2009-01-09T15:27:00.001+05:30

In this post I'll show you a simple solution to a very common error you get while running a query against a linked Server.
You create a Linked Server with Flat Files, Excel or DBF in SQL Server using Microsoft.Jet.OLEDB.4.0 and when you try run a query against it you get the error
--Error--
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server " " returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "".

Well whatever study I have done on this particular error, I have observed that you will get this error only when try to query from a remote machine. And this is something to do with the Security Context of the Linked Server.

Solution to this problem is very simple. Simply follow steps -
1.Open the Management Studio and navigate to Server Objects and then to Linked Server.
2.Right click on your Linked Server Name, and click on Properties.
3.Go to Security Page. Now for solving above problem you have 2 option, you can try any of the below 2 option.

A] Select the option “Be made without using a security context” by clicking on radio button.

OR

B] Select the last option “Be made using this security context ”.
As soon as click on the radio button before “Be made using this security context ”, the“Remote login” and “With password” text boxes become active to be filled in.
Now in the “Remote login”, enter Admin as the login user. Leave the password text box as blank.
Now just click on OK, and now can run the query against your linked server without any error.

- Mangal Pardeshi.

How to delete the Duplicate Rows from SQL Server table

2009-01-08T20:45:00.000+05:30

Lets see how to delete the duplicate Rows from SQL Server table.

Sample Table:

ID	NAMES	CODE
1	Mangal	101
1	Mangal	101
1	Mangal	101
2	Ricky	102
3	Brian	103
4	shiv	104
4	Shiv	104
5	Kunal	105
5	Kunal	105
6	Kate	106

As you can see in above table, record with Id 1 has come thrice, and ID 4 and 7 has come twice.

Desired Output: after removing duplicates -

ID	NAMES	CODE
1	Mangal	101
2	Ricky	102
3	Brian	103
4	Shiv	104
5	Kunal	105
6	Kate	106

Create And Load Sample Data -

-- Create Sample table
CREATE TABLE Temp
(
Id INT,
Names VARCHAR(100),
Code INT
)
GO
-- Load sample data
INSERT INTO Temp SELECT
1, 'Mangal', 123 UNION ALL SELECT
1, 'Mangal', 123 UNION ALL SELECT
1, 'Mangal', 123 UNION ALL SELECT
2, 'Ricky', 134 UNION ALL SELECT
3, 'Brian', 435 UNION ALL SELECT
4, 'Shiv', 223 UNION ALL SELECT
4, 'Shiv', 223 UNION ALL SELECT
5, 'Kunal', 654 UNION ALL SELECT
5, 'Kunal', 654 UNION ALL SELECT
6, 'Kate', 611
GO

Now first lets see how to find duplicate rows. With SQL Server 2005/2008 and Row_Number function, it has become very easy.

To Find Duplicate rows :

WITH Cte AS
(
SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq
FROM TEMP
)
SELECT DISTINCT ID, Names, Code
FROM Cte
WHERE Seq > 1

To Delete the Duplicate Rows :

WITH Cte AS
(
SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq
FROM TEMP
)
DELETE FROM cte
WHERE Seq > 1

- Mangal Pardeshi

Removing Time from DateTime column in Sql Server 2005

2008-12-27T23:01:00.000+05:30

Well this is most common question I answered many times on Sql Server MSDN forums.

How to remove Time from Datetime column in Sql Server 2005 and 2000?

Actually this is one of the biggest drawbacks in Sql server till the Sql Server 2008 happened; that we don’t have only Date datatype. In Sql server 2000 and 2005 we have Datetime datatype, which always come with time part attached with Date. Thankfully in Sql Server 2008 we have simple Date datatype.

Let it be, we’ll see how to remove Time part while querying the Datetime column. Actually it is quite easy with simple CONVERT function. The Sql syntax for it is as follows –

SELECT CONVERT ( varchar(12), DateColumn[, Style] ) as Date

FROM TableName.

Ahh, actually I wanted to create a chart out of it, but with Blogger you can’t create one, so I ended up writing this long list. Here I used getdate function; you can put the Datetime Column name in place of getdate() of your table in following examples.

Format: mm/dd/yy
SELECT CONVERT( Varchar(12), GetDate(),1)
Output: 12/27/08

Format: mm/dd/yyyy
SELECT CONVERT(Varchar(12),GetDate(),101)
Output: 12/27/2008

Format: yy.mm.dd
SELECT CONVERT(Varchar(12),GetDate(),2)
Output: 08.12.27

Format: yyyy.mm.dd
SELECT CONVERT(Varchar(12),GetDate(),102)
Output: 2008.12.27

Format: dd/mm/yy
SELECT CONVERT(Varchar(12),GetDate(), 3)
Output: 27/12/08

Format: dd/mm/yyyy
SELECT CONVERT(Varchar(12),GetDate(), 103)
Output: 27/12/2008

Format: dd.mm.yy
SELECT CONVERT(Varchar(12),GetDate(), 4)
Output: 27.12.08

Format: dd.mm.yyyy
SELECT CONVERT(Varchar(12),GetDate(),104)
Output: 27.12.2008

Format: dd-mm-yy
SELECT CONVERT(Varchar(12),GetDate(), 5)
Output: 27-12-08

Format: dd-mm-yyyy
SELECT CONVERT(Varchar(12),GetDate(),105)
Output: 27-12-2008

Format: dd mon yy
SELECT CONVERT(Varchar(12),GetDate(), 6)
Output: 27 Dec 08

Format: dd mon yyyy
SELECT CONVERT(Varchar(12),GetDate(),106)
Output: 27 Dec 2008

Format: mon dd, yy
SELECT CONVERT(Varchar(12),GetDate(),7)
Output: Dec 27, 08

Format: mon dd, yyyy
SELECT CONVERT(Varchar(12),GetDate(),107)
Output: Dec 27, 2008

Format: mm-dd-yy
SELECT CONVERT(Varchar(12),GetDate(),10)
Output: 12-27-08

Format: mm-dd-yyyy
SELECT CONVERT(Varchar(12),GetDate(),110)
Output: 12-27-2008

Format: yy/mm/dd
SELECT CONVERT(Varchar(12),GetDate(),11)
Output: 08/12/27

Format: yyyy/mm/dd
SELECT CONVERT(Varchar(12),GetDate(),111)
Output: 2008/12/27

Format: yymmdd
SELECT CONVERT(Varchar(12),GetDate(),12)
Output: 081227

Format: yyyymmdd
SELECT CONVERT(Varchar(12),GetDate(),112)
Output: 20081227

Well I already wrote one blog entry on how to convert date in required format.
Date Formats In SQL SERVER.

For more on CONVERT see
CAST and CONVERT (Transact-SQL) Books Online

- Mangal Pardeshi.

Linked Server With DBF Files

2008-12-27T12:16:00.002+05:30

In my previous post I wrote about Linked Server with Excel. Now we will see how to create a linked server for DBF files. That is accessing and querying DBF files from SQL Server Management Studio.

First I’ll show you how to set up the Linked Server from Management Studio.

Open the Management Studio and navigate to Server Objects and then to Linked Server. Right click on the Linked Servers node and select “Create New Linked Server”.
Enter in the name of your linked server in the text box that appears next to the “Linked Server” label. This can be any name that you would recognize to describe the object.

Then under Server Type, choose the “Other Data Source” Radio button. And select the provider “Microsoft Jet 4.0 OLE DB Provider” from the list.
For “Product Name” enter “Microsoft Jet”
In the “Data Source” text box, enter the full folder path to your dbf files. For mine I entered this: “D:\DBF Project\Data”.
For the “Provider String” enter “dBASE 5.0”

Then go to the security page. Here you have 2 options.
Either select “Be made without using a security context”
OR
Select “Be made using this security context” radio button found near the bottom of the window. The “Remote login” and “With password” text boxes become active to be filled in.
In the “Remote login”, enter “Admin” as the login user. Leave the password text box blank. Well I don’t know the exact answer why this needs; I tried searching on Net but couldn’t find the answer, but this how it works.

And say “ok”, now your Linked Server DBF files is ready. Just expand the Linked Server and then now created Linked server, after expanding the Default you will see Tables and Views. Expand the Tables node to see a list of table objects for the folder.

Now you can query the DBF files just like any other table from management studio. Now open the new query window to write a query on DBF files. Just remember you have to include the linked server and table name in a four part address. Namely, [LinkedServername]…[TableName].

So your select query will be like
SELECT * FROM [Linked_Server_Name]…[TableName]

e.g.
SELECT * FROM DbfLinked…Items.

Here DbfLinked is the linked server name I gave and “Items” is the table name. Observe that there are 3 dots between linked server name and table name.

We can use almost any select statement to retrieve our dbf data. For example JOINS with other tables in Sql Server or with other DBF tables, WHERE conditions, GROUP BY, HAVING, ORDER BY, even the UPDATE, DELETE or for that matter any valid Sql statement you normally do with tables.

Same Linked Server you can create using Sql Statement.
-- To create Linked Server With DBF Files
EXEC master.dbo.sp_addlinkedserver
@server = 'DbfLinked',
@srvproduct = 'Microsoft Jet',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'D:\DBF Project\Data',
@provstr = 'dBASE 5.0'

/* Now use either of two secuirty Context*/
-- To select security Context “Be made without using a security context”

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword = NULL

-- Or using “Be made using this security context”
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = ''

- Mangal Pardeshi.

How To Enable Xp_CmdShell in Sql Server?

2008-12-21T18:32:00.000+05:30

Today I will show you how to enable the Xp_CmdShell extended stored procedure in Sql Server 2005 and 2008.
Well normal error message you'll get when Xp_CmdShell is not enabled on your Sql Server and you to try execute some Windows commands using Xp_CmdShell is

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

You can enable the Xp_CmdShell using 2 ways, either by executing T-sql statements or from
"Surface Area Configuration Manager". We'll see both of them.

A. Enable Xp_Cmdshell from Management Studio.
For enabling Xp_CmdShell from Management Studio you need to execute following code.

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

B. Enable the Xp_CmdShell from "Surface Area Configuration Manager."

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Features.
3. Click on xp_cmdshell and tick on checkbox of "Enable the xp_cmdshell".

- Mangal Pardeshi.

How to configure SQL Server 2005 to allow remote connections

2008-12-21T17:36:00.000+05:30

By default, SQL Server 2005 does not allow remote connections. To configure SQL Server 2005 to allow remote connections, we need to follow the steps:

Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
Turn on the SQL Server Browser service. (If you are using Named Instance or Sql Express.)
Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

So we will see how to achieve above 3 points one by one.

A] How to Enable remote connections for SQL 2005?
1. Click on Start >> Programs >> Microsoft SQL Server 2005 >> Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

B] How to Enable the SQL Server Browser service?

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections.

1. Click on Start >>Programs >> Microsoft SQL Server 2005 >> Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.

Note: When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.

C] How to Create exceptions in Windows Firewall?

If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open, and then click OK. Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.

Note: The path may be different depending on where SQL Server 2005 is installed.

The source - http://support.microsoft.com/kb/914277

- Mangal Pardeshi.

Check Constraint to allow alphabets only in Sql Server

2008-12-08T21:58:00.000+05:30

How to create a check constraint to allow alphabets i.e. A-Z characters only or alphabets + numbers with no special characters in Sql Server?
I think many of us have faced this problem sometimes. Here is the scenario, you have column with varchar/char datatype and you don’t want user to enter any numbers or any Special characters like @, #, $, *. And sometimes characters + numbers with no special characters. And we don’t know any efficient way to handle and we end up writing a long NOT LIKE check constraint.
So let’s see how to write a simple CHECK CONSTARINT for allowing only alphabets and numbers.

First I’ll show you how to write a WHERE conditions for such different cases, so you will get an idea.
Let’s create 1 temporary table and populate it with some data

CREATE TABLE #temp
(ID varchar(50))
GO

INSERT INTO #TEMP SELECT
'Mangal' UNION SELECT
'Mangal Pardeshi' UNION SELECT
'Mangal19' UNION SELECT
'19' UNION SELECT
'@Mangal***' UNION SELECT
'@#$%^&*' UNION SELECT
'Mangal19**'
GO

Now our sample table #temp is ready.
SELECT * FROM #TEMP

-- Output --

@#$%^&*
@Mangal***
19
Mangal
Mangal Pardeshi
Mangal19
Mangal19**
--------------

Case 1 : We just want to get rows with only Alphabets (A-Z characters) with no numbers and Special characters.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z]%'

-- Output --
Mangal
--------------

Now if you notice here ‘Mangal Pardeshi’ din’t get selected as it contains a SPACE in between. To avoid that we need include a single space in our condtion '%[^A-Z]%'. Now we will put '%[^A-Z ]%'.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z ]%'

-- Output --
Mangal
Mangal Pardeshi
--------------

Case 2 : Now we want select all the rows with alphabets + numbers or any of the both with no special characters.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z0-9 ]%'

-- Output --
19
Mangal
Mangal Pardeshi
Mangal19
--------------
Case 3 : Now we want characters + numbers or characters or numbers or a special character *. So I just add a * in our condition.

SELECT id
FROM #TEMP
WHERE id NOT LIKE '%[^A-Z0-9* ]%'

-- Output --
19
Mangal
Mangal Pardeshi
Mangal19
Mangal19**
---------------
Same way you can inculde any special character. You just need to add that character into our original condition.

Now writing a Check Constraint is very simple.

For allowing Alphabets only

ALTER TABLE TableName ADD CONSTRAINT Only_Characters CHECK ColumnName NOT LIKE '%[^A-Z ]%'
**Remember to add extra space.

For alphabets + Numbers

ALTER TABLE TableName ADD CONSTRAINT Only_Characters_And_Numebrs CHECK ColumnName NOT LIKE '%[^A-Z0-9 ]%'

Hopefuly this post will help many of you.

- Mangal Pardeshi.

How to create a time dimension in SQL Server

2008-12-06T12:09:00.003+05:30

In this post I’ll help you creating a Time Dimesnion as it is very much required if you are working on Sql Server Analysis Services. Until now you need to write a complex Sql procedure running into loops for creating a large Time Dimension. But with Sql Server 2005 and Common Table Expressions (CTE) it is very easy to create your own Time Dimension. A time Dimension is also called as Calendar Table by many.
When I started working on SSAS 2005 I also searched for a good Time Dimension, but now I have created my own Time Dimension with following Script.

Following script creates a table named TimeDiemension and populates it from 2000-01-01 to 2020-12-31. You can easily put your required dates.
Even though following query may look little big but believe me, it's nothing but a simple SELECT statement with CTE.
------- TimeDimension -------

WITH Mangal as
(
SELECT Cast ('2000-01-01' as DateTime) Date --Start Date
UNION ALL
SELECT Date + 1
FROM Mangal
WHERE Date + 1 < = '2020-12-31' --End date
)

SELECT Row_Number() OVER (ORDER BY Date) as DateId
,Date
,YEAR (date) as Year
,DatePart ( qq, date) as Quarter
,MONTH (date) as Month_Number_Year
,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as Month_Number_Of_Quarter
,DatePart (wk, Date) as Week_Number_Of_Year
,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as Week_Number_Of_Quarter
,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as Week_Number_Of_Month
,DatePart (dy, date) as Day_Number_Of_Year
,datediff(dd,dateadd(qq,datediff(qq,0,date),0),date)+1 as Day_Number_Of_Quarter
,DAY (date) as Day_Number_Of_Month
,DatePart (dw, date) as Day_Number_Of_Week
,DateName (mm, date) as Month_Name
,LEFT ( DateName (mm, date), 3) Month_Name_Abbreviation
,DateName (dw, date) as Day_Name
,LEFT (DateName (dw, date), 3) as Day_Name_Abbreviation
,CONVERT(VarChar(10),date,112) as YYYYMMDD
,CONVERT(VarChar(10),date,111) as [YYYY/MM/DD]
,CONVERT(VarChar(11),date,100) as [mon dd yyyy]
,CONVERT(VarChar(11),date,120) as [yyyy-mm-dd]

INTO TimeDimension -- Name of the Table

FROM Mangal

OPTION (MAXRECURSION 0)

---- Script Ends Here ----
Now your TimeDimension is ready. Do a simple
SELECT * FROM TimeDimension
for a check.
Hopefully you will find this script helpful. Any questions and suggestions are welcome.
- Mangal Pardeshi.

Saving the Result of a query to a Text File in Sql Server

2008-12-03T21:49:00.000+05:30

After being a part of the Sql Server MSDN forum for a quite a long time, One question I answered many time is “How to save the output of query to a text file in Sql Server?”
So thought of sharing a simple solution with you using BCP utility.

A simple query to save output of query to a text file is goes like this –

EXEC master..XP_CmdShell 'BCP "SELECT * FROM Database.dbo.TableName" queryout "c:\Mangal.txt" -c -T'

If you execute the above query and get the following error –

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Then first you need to enable the xp_cmdshell procedure. To enable the xp_cmdshell execute -

sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure Xp_CmdShell,1
GO
RECONFIGURE WITH OVERRIDE
GO

Again back to BCP, Here
-c : Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \n (new line character) as the row terminator.

-T : Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

You can save the file to txt, xls, dbf, xml formats also.

There are so many other options available with BCP command.
e.g.
With –U and –P you can provide the username and password when you are not using the Windows Authentication (Trusted Connection).

For more on BCP utility you can read from Microsoft’s Books Online.
BCP Utility Books Online

Idea for this post was just to provide a simple solution on how to save the result of a query to a file?.

Well there is another way of directly sending the result of query to a file is by setting the “Result To Text” in management studio.
For that do –
Open the Management Studio.
In menu bar click to “Query” >> “Result to” and then select “Result To Text.”

Hopefully many of you find this post helpful.

- Mangal

Linked Server With Excel.

2008-11-26T21:22:00.001+05:30

In this part I’ll help you in creating a Linked Server with Excel. Or simply how to import/query Excel in Sql Server.

In Sql Server 2005 you need to enable the 'Ad Hoc Distributed Queries'. For that first execute the following scripts.
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

And now here go
1.
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=E:\Mangal.xls;Extended Properties=Excel 8.0')...Sheet1$
2.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', Sheet1$)
3.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Here Excel 8.0 means this is applicable to Excel 2002, Excel 2000, or Excel 97 workbook.
Database means path of your excel book and its name.

Now you can do any Sql query with excel that you usualy do with any table. For example you can join excel with other tables, you can insert excel rows in other tables.
Lets say you want to insert first 3 columns of Excel sheet into table Customers.
You can do
INSERT INTO customers (CustId, CustomerName, Address )
SELECT Custid, CustomerName, Address
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Or to join a table with Excel

SELECT A.ColumnName, E.ColumnName
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]' ) E
INNER JOIN Sql_Table A
ON a.id=e.id

- Mangal Pardeshi.

Some Useful Views And SPs in Sql Server.

2008-11-23T17:02:00.000+05:30

Here is the good collection of views and Store Procedure which can be very useful.
Applicable for Sql Server 2005 and +

VIEWS : To use all the views you just need to do SELECT * FROM VIEW_NAME
e.g SELECT * FROM sys.databases

sys.databases : Lists all the databases in Sql Server.
sys.tables : Lists all the Tables in the database.
sys.views : Lists all the views in the database.
sys.procedures : Lists all the Procedures in the database.
sys.triggers : Lists all the Triggers in the database.

sys.columns : Lists all the columns of tables in database.
sys.syscolumns : Lists all the columns in database including of those SP.
sys.key_constraints : Lists all primary key or unique constraints in database. For primary key TYPE = 'PK' and for unique keys TYPE = 'UQ'
sys.check_constraints : Lists all the Check Constraints in the database.
sys.default_constraints : Lists all the Default Constarints in the database.
sys.foreign_keys : Lists all the Foreign Keys in the database.

sys.syslogins : Lists all the login names in server.
sys.sql_logins : Lists all the Sql Authentication Logins in server.
sys.sysusers : Lists all the users in database.

-- I'll Add some more very soon.

- Mangal Pardeshi.