SQL Server : Situation to use GROUP BY ALL

An interesting question was raised by one of the attendee in my “SQL Server – Performance Tuning” session.

(i.e) Where GROUP BY ALL can be used in efficient manner ?

First of all, what is GROUP BY ALL?

GROUP BY ALL is similar to GROUP BY, but which includes all groups and result sets, even those that do not have any rows that meet the search condition specified in the WHERE clause.

Let us assume, we have two tables with the following records.

Table Creation Script :

CREATE TABLE [DBO].[City]
(
            [CityID]                INT                             IDENTITY(1,1)  NOT NULL,
            [CityName]          VARCHAR(100)                                    NOT NULL,
            CONSTRAINT       [PK_City] PRIMARY KEY ([CityID]),
)
GO

CREATE TABLE [DBO].[Employee]
(
             [EmpID]                INT                               IDENTITY(1,1)   NOT NULL,
             [EmpName]          VARCHAR(100)                                       NOT NULL,
             [Sex]                    BIT                                                         NOT NULL, --1 Male 0 Female
             [CityID]                INT                                                         NOT NULL,
             CONSTRAINT        [PK_Employee]   PRIMARY KEY ([EmpID]),
             CONSTRAINT        [FK_Employee_CityID] FOREIGN KEY ([CityID]) REFERENCES [City] ([CityID])
)
GO

Sample records for City:

INSERT INTO [City] ([CityName]) VALUES ('Chennai');
INSERT INTO [City] ([CityName]) VALUES ('Bangalore');
INSERT INTO [City] ([CityName]) VALUES ('Hyderabad');
INSERT INTO [City] ([CityName]) VALUES ('Pune');
INSERT INTO [City] ([CityName]) VALUES ('Mumbai');
INSERT INTO [City] ([CityName]) VALUES ('Delhi');
INSERT INTO [City] ([CityName]) VALUES ('Calcutta');

Sample records for Employee:

INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Siva',1,1);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Parvathi',0,1);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Swetha',0,4);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Swamy',1,4);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Joseph',1,6);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Gayatri',0,3);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Vivek',1,6);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Krishna',1,4);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Lakshmi',0,4);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Satheesh',1,5);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Kiran',1,3);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Suman',1,3);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Srinivas',1,5);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Priya',0,2);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Karthika',0,2);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Murali',1,5);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Vishal',1,5);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Sravya',0,3);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Shilpa',0,4);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Vishnu',1,5);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Ganapathy',1,1);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Matt',1,7);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Jonathan',1,7);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Tim',1,7);
INSERT INTO [DBO].[Employee] ([EmpName],[Sex],[CityID]) VALUES ('Arvind',1,7);

I hope, you have executed the above scripts without any errors.

Now, execute the below statement to list all the cities from [City] table.

SELECT [CityName] FROM [DBO].[City] WITH (NOLOCK);

Output would be :













Let us say, we need to check how many FEMALE employees are there in each city. For this we normally use GROUP BY clause.

SELECT
              C.[CityName] AS 'City Name',
              COUNT(E.EmpID) AS 'Female Count'
FROM
              [DBO].[Employee] AS E WITH (NOLOCK) INNER JOIN [DBO].[City] AS C WITH (NOLOCK) ON E.[CityID] = C.[CityID]
WHERE
              E.[Sex] = 0
GROUP BY
              C.[CityName]
ORDER BY
              C.[CityName]
GO

Output would be :

When you execute the above statement will give us only the city names which are having female employee. It’s because we are filtering only FEMALE employee in the WHERE CLAUSE.

But, we need to list all the city names even FEMALE employees are not existing. In this situation GROUP BY ALL is very useful.

SELECT
             C.[CityName] AS 'City Name',
             COUNT(E.EmpID) AS 'Female Count'
FROM
             [DBO].[Employee] AS E WITH (NOLOCK) INNER JOIN [DBO].[City] AS C WITH (NOLOCK) ON E.[CityID] = C.[CityID]
WHERE
             E.[Sex] = 0
GROUP BY ALL
            C.[CityName]
ORDER BY
            C.[CityName]
GO

Output would be :














From the above result set, we can understand how GROUP BY ALL is used efficiently.

NOTE :

1. We cannot specify ALL with the CUBE or ROLLUP operators.
2. GROUP BY ALL is not supported in queries that access remote tables if there is also a WHERE clause in the query.

SQL Server : T-SQL features in SQL Server 2008 (Part 1)

Welcome to SQL Server 2008 T-SQL features buddy :)

Assign Default Values to Local Variables in SQL Server 2008

SQL Server 2008 allow us to assign a default value to a Local Variable when we DECLARE a variable itself instead of using separate SET statement :

DECLARE @iTest AS INT = 100, @vText AS VARCHAR(25) = 'SQL Server 2008';

SELECT @iTest AS Result1,@vText AS Result2;

The same logic can be done in SQL Server 2005 as below:

DECLARE @iTest AS INT, @vText AS VARCHAR(25);

SET @iTest = 1;

SET @vText = 'SQL Server 2005';

SELECT @iTest AS Result1,@vText AS Result2;

Compound assignment operators in SQL Server 2008

SQL Server 2008 introduces new 5 compound assignment operators :-

(a) += (plus equals)

(b) -= (minus equals)

(c) *= (multiplication equals)

(d) /= (division equals)

(e) %= (modulo equals)

These operators can be used in the SET clause of an UPDATE statement Or in a SET statement that assigns values to variables as shown below :

DECLARE @iTest AS INT = 10;

SET @iTest += 15;

SELECT @iTest AS Result1;

The result would be 25.

The same logic can be done in SQL Server 2005 as below:

DECLARE @iTest AS INT;

SET @iTest = 10;

SET @iTest = @iTest + 15;

SELECT @iTest AS Result1;

You could see in SQL Server 2008 directly we assigned 10 as default value to a Variable (@iTest)and added 15 to @iTest directly, whereas in SQL Server 2005 first declared a Variable and assigned 10 as initial value using SET statement and again added 15 to @iTest variable.

Using the above features, we can reduce some unnecessary code to improve the performance.

Keep follow my blog for other new SQL Server 2008 T-SQL features....... :)

SQL Server : Combining multiple DML (Data Manipulation Language) actions (INSERT, DELETE, UPDATE) into single SQL Statement using MERGE in SQL Server 2008

One of my SQL buddy got confused yesterday about MERGE functionality in SQL Server 2008, he rang me up and asked MERGE is used for merging multiple records into single?

I said NO, this confusion is not only with him which has with many SQL buddies. I have finally clarified his doubt with some examples which I would like to share with you all. :)

First of all, what is MERGE statement in SQL Server 2008?

MERGE is combining multiple DML (INSERT, UPDATE, DELETE) actions statements into single action, which improves performance and simplifying transactions. (This option is called UPSERT in other products like ORACLE, Sybase, DB2 etc.,).

Now, Let me explain, how to use MERGE statement instead of our normal DML statements. :)

We will assume that we have a table with the following records.

Sample table creation script :

CREATE TABLE [DBO].[Employee]
(
[EmpCode] VARCHAR(10) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL CONSTRAINT DF_Employee_Active DEFAULT 1, --1. ACTIVE 0. INACTIVE
CONSTRAINT [PK_Employee] PRIMARY KEY ([EmpCode]),
CONSTRAINT [CK_EEmployee_Active] CHECK ([Active] IN (0,1))
)
GO

Sample records creation script :

INSERT INTO
[DBO].[Employee] ([EmpCode],[EmpName])
VALUES
('E001','Sudhar'),
('E002','Siva'),
('E003','Areef'),
('E004','Jonathan'),
('E005','Matt'),
('E006','Mahesh')
GO

SELECT [EmpCode],[EmpName],[Active]  FROM  [DBO].[Employee] WITH (NOLOCK)
GO

After executing the above scripts, the output would be :

Performing INSERT and UPDATE operations on a table using single MERGE statement :

Let us see, how to use MERGE statement to update a record. For example I am going to rename the employee's name for employee  'E003'.

DECLARE @Input_EmpCode VARCHAR(10) = 'E003'
DECLARE @Input_EmpName VARCHAR(100) ='Areef Ali Sheik'
DECLARE @Input_Active BIT = 1

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active]);

The output would be :

MERGE statement in Stored Procedure :

CREATE PROCEDURE [DBO].[STP_Employee]
(
@Input_EmpCode VARCHAR(10) , @Input_EmpName VARCHAR(100),
@Input_Active BIT = 1
)
AS

SET NOCOUNT ON;

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active]);

GO

Execute the below scripts to INSERT or UPDATE records :
EXEC [DBO].[STP_Employee] @Input_EmpCode = 'E003',@Input_EmpName ='Areef Ali Sheik', @Input_Active =1
GO

EXEC [DBO].[STP_Employee] @Input_EmpCode = 'E007',@Input_EmpName ='Sathish', @Input_Active =1
GO

Output would be :

Displaying affected records using MERGE OUTPUT clause statement :

DECLARE @Input_EmpCode VARCHAR(10) = 'E008'
DECLARE @Input_EmpName VARCHAR(100) ='Sivakumar Vellingiri'
DECLARE @Input_Active BIT = 1

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date;

Recording DML changes into history table using MERGE OUTPUT clause statement :

History table creation script :

CREATE TABLE [DBO].[EmployeeHistory]
(
[Action] VARCHAR(10) NOT NULL,
[EmpCode] VARCHAR(10) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL,
[Previous_EmpName] VARCHAR(100) NULL,
[Previous_Active] BIT NULL,
[Modified_Date] DATETIME NOT NULL,
[Modified_By] NCHAR(100) NOT NULL
)
GO

For this example, I am going to update one existing employee and inserting one new employee record.

Updating existing employee :

DECLARE @Input_EmpCode VARCHAR(10) = 'E002'
DECLARE @Input_EmpName VARCHAR(100) ='Sivakumar Vellingiri'
DECLARE @Input_Active BIT = 1

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date,SYSTEM_USER AS Modified_User INTO [EmployeeHistory];

Creating new employee :

DECLARE @Input_EmpCode VARCHAR(10) = 'E009'
DECLARE @Input_EmpName VARCHAR(100) ='Krishna Priya'
DECLARE @Input_Active BIT = 1

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date,SYSTEM_USER AS Modified_By INTO [EmployeeHistory];

Displaying records from history table :

SELECT
[Action],[EmpCode],[EmpName],[Active],[Previous_EmpName],[Previous_Active],[Modified_Date],[Modified_By]
FROM
[EmployeeHistory] WITH (NOLOCK)
GO

As we all know that the above functionality can be achieved using TRIGGERS, but MERGE OUTPUT made us very simple and improves the performance too. :)

Note :
(a). This example will work only in SQL Server 2008 and above versions.
(b). Target Table : The table or view against which the data rows from SOURCE TABLE are matched based on search condition. The target is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement. 
(c). USING : Specifies the data source that is matched with the data rows in target table based on merge search condition. The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. Table source can be a remote table or a derived table that accesses remote tables.
(d). OUTPUT : Returns a row for every row in TARGET TABLE that is updated, inserted or deleted.

For additional information, please refer http://technet.microsoft.com/en-us/library/bb510625.aspx

SQL Server : Displaying records with Sequential Numbering / Group wise Sequential Number / specific range of records in SQL Server using ROW_NUMBER() function.

One of my SQL buddy called yesterday and asked me, how to display the records with Serial Numbers in SQL Server.  I gave the solution over phone which I want to share with you guys too. :)

Assume that we have a table with the following records.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 




How to display the records with record number :

Now, let me show you how to display the same records with Serial Number (Record Number) using ROW_NUMBER() function.

SELECT
ROW_NUMBER() OVER (ORDER BY [CityName] ASC ) AS 'Record Number',
[CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
GO

You can see the records with serial number as below.

How to display group wise record number:

In a similar way, we can reset the record numbers based on group. Here, I am going to display the record numbers based on StateName wise.


SELECT
ROW_NUMBER() OVER ( PARTITION BY [StateName] ORDER BY [CityName]) AS 'Record Number', [CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
ORDER BY
[StateName],[CityName]


Now,  see the group wise record number result below.

How to display specific range of records from the result set:

Sometimes, It may require to display the records for some specific range. Let us say, I want to display the records range between 5 to 10.


WITH CTECity (RecordNumber,CityName, StateName)
AS
(SELECT
ROW_NUMBER() OVER (ORDER BY [CityName] ASC ) AS RecordNumber,
[CityName],[StateName]
FROM
[DBO].[City] WITH (NOLOCK)
)
SELECT
[CityName],[StateName]
FROM
CTECity
WHERE
[RecordNumber] BETWEEN 5 AND 10
GO

Now, See the result below and you can compare the result with my first example.

 
 
 
 
 
 
 
Note :

(a). This example will work only in SQL Server 2005 and above versions.
(b). ROW_NUMBER() function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
(c). For my last example, I used Common Table Expression (CTE) which was introduced in SQL Server 2005 and it’s a "temporary result set" that exists only within the scope of a single SQL statement.

Convert SQL Server Table Data Into Comma Separated Values (CSV) Format

--=============================================
--Creating a table to store some sample records
--=============================================

CREATE TABLE [DBO].[MEmployee]
(
[EmpCode] INT IDENTITY (1,1) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL CONSTRAINT DF_MEmployee_Active DEFAULT 1, --1. ACTIVE 0. INACTIVE
CONSTRAINT [PK_MEmployee] PRIMARY KEY ([EmpCode]),
CONSTRAINT [CK_MEmployee_Active] CHECK ([Active] IN (0,1))
)
GO

--==================================================
--Inserting some test records into [MEmployee] Table
--==================================================

INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Siva',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Parvathi',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Surya',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Satya',1)
GO
INSERT INTO [DBO].[MEmployee] ([EmpName],[Active]) VALUES ('Ajay',0)
GO

--======================================
--Selecting the records from [MEmployee]
--======================================

SELECT * FROM [DBO].[MEmployee]
GO

The output would be :











--====================================================
--Generating the records in the Comma Separated Format
--====================================================

DECLARE @Result VARCHAR(MAX)
--==============================================
--To display the field heading in the first row
--==============================================
SET @Result = 'EmpCode,EmpName,Active'
SELECT
@Result = COALESCE(@Result + '|','') + CONVERT(VARCHAR(10),[EmpCode]) +','+[EmpName]+','+CONVERT(VARCHAR(2),[Active])
FROM
[DBO].[MEmployee]

PRINT @Result

See the result as shown below:





Note :
1. COALESCE() returns the first NOTNULL expression among its arguments.
2. "|" is used for row delimiter.

How to Call External Applications like MS Excel, MS Word etc., from SQL Server Management Studio (SSMS)

Many of us are thinking that SQL Server Management Studio (SSMS) is used only for database administration, but also can be used to call the external applications like MS Excel, MS Word, Calculator etc.,

For this example, let me explain, how to call MS Excel from SSMS.

Step 1 : Open SQL Server Management Studio(SSMS)

Step 2 : Go to Tools --> External Tools Menu (As shown below)













Step 3 :

(i) In the Title text box, type the name you want to appear in the Menu contents List. eg. "Excel"

(ii) In the Command text box, type the program name. Include the path to the executable file.
For example "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE"

(iii) In the Arguments text box, type the program arguments if necessary.

(iv) In the Initial directory text box, type the program's initial directory if necessary.

(v) Click OK





















Go to Tools Menu, Now you can see "Excel" menu is added (As shown below).















Click and see the magic, the similar way you can add other your frequently used applications in SSMS. I hope this will help you to save your valuable time.