Saturday 18 April 2015

Handling Multi Record Sets from SP

Case Scenario

We have a Stored procedure and the stored procedure have two select statement on it and return different result sets. We want to store the first result set in a temporarily table and work with it.  It looks interesting.

But here we mentioned the First Result Set. Not the second result set. To play with any result sets we must go to the CLR options. But with first record sets it little bit easy.

Let’s Take an Example  

Step-1 [ Base Table ]

We have two base table mentioned here

SELECT * FROM MYTbl_Employee;

ID            EmpName                           GERADE
1              Joydeep Das                          NULL
3              Rajesh Das                            NULL
8              Rani Mukharjee                    NULL

SELECT * FROM tbl_EMPDTLREC;

EMPID  EMPNAME                          GRADE                  SAL
101         Joydeep Das                           A                             5000
102         Chandan Bannerjee                A                             6000
103         Sukamal Jana                          B                             2000
104         Rabi Mukharjee                      A                             4000
105         Madhurima Das                      C                             1000

Step-2 [ Now Crete a Stored Procedure with two Table ]

IF OBJECT_ID(N'dbo.proc_abc', N'P')IS NOT NULL
   BEGIN
      DROP PROCEDURE [dbo].[proc_abc];
   END
GO

CREATE PROCEDURE [dbo].[proc_abc]
AS
BEGIN
   SELECT * FROM MYTbl_Employee;
   SELECT * FROM tbl_EMPDTLREC;
END

Step-3 [ Now Create a Temp Table  For First Result Set ]

CREATE TABLE #temp_Output
    (ID INT, EmpName VARCHAR(50), GERADE CHAR(1));

Step-4 [ Now try to Insert records Directly into Temp Table ]

INSERT INTO #temp_Output
EXEC proc_abc

Its gives an Error
Msg 213, Level 16, State 7, Procedure proc_abc, Line 6
Column name or number of supplied values does not match table definition.

Step-5 [ Now try it with OPENROWSET() ]

INSERT INTO #temp_Output
SELECT a.*
FROM
OPENROWSET
('SQLOLEDB',
 'SERVER=JOYDEEP-LAPTOP\JOYDEEPSQL12;Trusted_Connection=yes;',
 'set fmtonly off;
 exec PRACTICE_DB.dbo.proc_abc')AS a

Step-6  [ Observation ]
SELECT * FROM #temp_Output;

ID            EmpName                           GERADE
1              Joydeep Das                          NULL
3              Rajesh Das                             NULL
8              Rani Mukharjee                     NULL



Please note that, if have you any alternate options please make a comments. Please do not modify the stored procedure for that.



Hope you like it.





Posted by: MR. JOYDEEP DAS

Sunday 12 April 2015

TempDB for Performance Part-II

Introduction

One of my article is related to TempDB for Performance, published on 28th Feb 2015. Those who are not read it yet can read it from


Now the question came to mid how to change the location of the TempDB from current location to other drive. Here in this article, I am providing the T-SQL command for that. We are not going to discuss about the performance related factors of TempDB. As you can find it form our previous article mentioned above.

In Which Drive My TempDB is Currently Located

USE tempdb
GO

EXEC sp_helpfile;



So we find that the MDF and LDF file of the temp DB is located on
C:\Program Files\Microsoft SQL Server\MSSQL11.JOYDEEPSQL12\MSSQL\DATA

How We Move the TempDB to Another Drive
Suppose we want to move the TempDB from current location to E:\TEMPDB\

USE MASTER
GO

ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'E:\TEMPDB\tempdb.mdf')
GO

ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'E:\TEMPDB\templog.ldf')
GO




Hope you like it.




Posted by: MR. JOYDEEP DAS

Thursday 9 April 2015

PAGEDATA T-SQL in SQL 2012

Introduction
In this article we are trying to discuss about a new feature of Microsoft SQL server called page data. Hope you will find it informative.

What the Page Data is
When we are taking about front-end grid to display data we have limitation of spaces, so we represent it via paging in the grid. The paging system is a feature of grid that we used in our front-end. But when we move throw pages we fired the SQL Statement to retrieve data from our database. Suppose our grid can display five data at a time, so we retrieve 1 to 5 records first from database and when the user click on the next page we retrieve records from 6 to 10 and so on.

How We do it

Step-1 [ Create the Base Table  ]

IF OBJECT_ID(N'dbo.tbl_ItemMast', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_ItemMast];
   END
GO

CREATE TABLE [dbo].[tbl_ItemMast]
   (
      ITEMCD      BIGINT        NOT NULL PRIMARY KEY,
        ITEMNAME    VARCHAR(50)   NOT NULL
   );
GO

Step-2 [ Insert Some Records in our Base Table ]

INSERT INTO [dbo].[tbl_ItemMast]
       (ITEMCD, ITEMNAME)
VALUES (1, 'Tooth Paste'), (2, 'Tooth Brush'),
       (3, 'Banana'), (4, 'Apple'), (5, 'Orange'),
       (6, 'Saving Lootion'), (7, 'Oil'), (8, 'Saving Cream'),
       (9, 'Cake'), (10, 'Rice');
GO

Step-3 [ How we Retrieve Records Before MS SQL Server 2012 ]

SELECT a.*
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY ITEMCD)AS RNUM, ITEMNAME
        FROM   [dbo].[tbl_ItemMast])AS a
WHERE  a.RNUM >=1 AND a.RNUM<=5;


Output :



SELECT a.*
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY ITEMCD)AS RNUM, ITEMNAME
        FROM   [dbo].[tbl_ItemMast])AS a
WHERE  a.RNUM >=5 AND a.RNUM<=10;

Output:



Step-4 [ How we retrieve records in SQL Server 2012 ]

SELECT *
FROM   [dbo].[tbl_ItemMast]
ORDER BY ITEMCD
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

Output:



SELECT *
FROM   [dbo].[tbl_ItemMast]
ORDER BY ITEMCD
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

Output:






Hope you like it.




Posted by: MR. JOYDEEP DAS

Monday 6 April 2015

T-SQL ROLLUP and CUBE

Introduction

First of all I salute all the new and reward MVP in the month of April 2015. Congratulating them for their achievement. Hope the community can learn a lot of new thing from them.
After my blog related to “Group By Grouping Set” request came from my friends circle to completing the article by providing something related to CUBE, ROLLUP and COMPUTE. So in this article we are trying to learn something related to it. Hope it will be informative.

CUBE and ROLLUP
Generally CUBE and ROLLUP is used in reporting purpose and they do the Subtotal and Grand total. CUBE generates a result set that shows aggregates for all combinations of values in the selected columns and ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Let’s take an Example to understand it
We have a table like this.

Class
Section
Roll
Marks
1
A
1
40
1
A
2
30
1
A
3
20
1
B
1
40
1
B
2
30
1
B
3
30
2
A
1
20
2
A
2
60
2
A
3
40
2
B
1
20
2
B
2
30
2
B
3
20

If we make the WITH ROLLUP we can get the Output like this

Class
Section
Marks

1
A
90

1
B
100

1
NULL
190
90 + 100
2
A
120

2
B
70

2
NULL
190
120 + 70
NULL
NULL
380
190 + 190



If we make the WITH CUBE we can get the Output like this

Class
Section
Marks

1
A
90

1
B
100

1
NULL
190
90 + 100
2
A
120

2
B
70

2
NULL
190
120 + 70
NULL
NULL
380
190 + 190
NULL
A
210
90   +  120
NULL
B
170
100 +  70

Let’s take a practical example

Step- 1 [ Create Base Table ]

IF OBJECT_ID(N'[dbo].[tbl_EXAMPLETABLE]', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_EXAMPLETABLE];
   END
GO

CREATE TABLE [dbo].[tbl_EXAMPLETABLE]
    (
        CLASS        INT       NOT NULL,
        SECTION      CHAR(1)   NOT NULL,
        ROLL         INT       NOT NULL,
        MARKS        INT       NOT NULL
   );   

Step-2 [ Insert Records in Base Table ]

INSERT INTO [dbo].[tbl_EXAMPLETABLE]
    (CLASS, SECTION, ROLL, MARKS)
VALUES(1,  'A', 1, 40),
      (1,  'A',   2, 30),
      (1,  'A',   3, 20),
      (1,  'B',   1, 40),
      (1,  'B',   2, 30),
      (1,  'B',   3, 30),
      (2,  'A',   1, 20),
      (2,  'A',   2, 60),
      (2,  'A',   3, 40),
      (2,  'B',   1, 20),
      (2,  'B',   2, 30),
      (2,  'B',   3, 20);


Step – 4 [ Make the WITH ROLLUP ]

SELECT   CLASS, SECTION, SUM(MARKS) AS MARKS
FROM     [dbo].[tbl_EXAMPLETABLE]
GROUP BY CLASS, SECTION WITH ROLLUP;

Output



Step-5 [ Make the WITH CUBE  ]

SELECT   CLASS, SECTION, SUM(MARKS) AS MARKS
FROM     [dbo].[tbl_EXAMPLETABLE]
GROUP BY CLASS, SECTION WITH CUBE;

Output






Hope you like it.





Posted by: MR. JOYDEEP DAS