Feeds:
Posts
Comments

Archive for July, 2012

SQL SERVER 2012 – sys.dm_exec_describe_first_result_set – dynamic management function

Posted in Functions, SQL SERVER, tagged on July 29, 2012| 2 Comments »

In this article we will discuss the new dynamic management function namely “sys.dm_exec_describe_first_result_set”. The functionality of this function is almost similar  to “sp_describe_first_result_set” system stored procedure, but the implementation (Syntax) and usage is different.

Syntax :

sys.dm_exec_describe_first_result(@tsql, @params, @include_browse_information)

“sp_describe_first_result_set” provides the detailed meta data on the basis of different@include_browse_information parameters. But lets say if we need only two columns from this meta data info and you would like to query it like a table, both requirements are not fulfilled by “sp_describe_first_result_set” stored procedure, because it does not return the data as a tabular form and also you cannot query only few columns. In this case you can use “sys.dm_exec_describe_first_result_set” function to fulfill both your requirements in addition to the “sp_describe_first_result_set” functionality.

Let me explain the difference with the help of simple example :


---sp_describe_first_result_set
USE AdventureWorks2012;
GO
EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM HumanResources.Department'

---sys.dm_exec_describe_first_result_set
USE AdventureWorks2012;
GO
SELECT [column_ordinal],[name],[system_type_id]   FROM sys.dm_exec_describe_first_result_set
(N'SELECT * FROM HumanResources.Department', null, 0) ;

--Output

Summary :
This function has the same functionality as “sp_describe_first_result_set” but the two main differences are given below :

  1. This function can filter the meta data column as per the requirement.
  2. This function returns the data in a tabular form, so you can utilize it in any other function or procedure.

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – sp_describe_first_result_set – System Stored Procedure

Posted in Functions, SQL SERVER, System Stored Procedure, tagged , on July 4, 2012| 10 Comments »

In SQL Server 2012 , Microsoft has provided a very helpful new system stored procedure namely “sp_describe_first_result_set”. Like name, like Work.. As the name goes, so is the performance. It gives the detailed meta data(Schema) information for the first possible result set. Note that it works only in SQL Server 2012. If you need to get the meta data info in SQL Server 2005/2008, you need to use “SP_help” and with very limited info availability.

Syntax :

sp_describe_first_result_set [ @tsql = ] N'Transact-SQL_batch'
[ , [ @params = ] N'parameters' ]
[ , [ @browse_information_mode = ]  ] ]

Lets make some simple examples to check its functionality  :

EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM HumanResources.Department'
GO
--OUTPUT

As I discussed, you can view the  output as a detailed meta data.

Now, let me explain why “first possible result set,” name is given to this procedure, with the help of a simple example.
Given below are two queries and it will return two sets of result as well.

SELECT * FROM [HumanResources].[Department];
SELECT * FROM [HumanResources].[Employee];

But, if we pass both the queries to “sp_describe_first_result_set” to provide meta data, it will provide the meta data of the first query only and according to this functionality this name (sp_describe_first_result_set) is given to this stored procedure. Lets execute this scenario.

EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM [HumanResources].[Department];
SELECT * FROM [HumanResources].[Employee];'
--Output

Now, lets look at the different option of @browse_information_mode in sp_describe_first_result_set

Lets create a view to explain the different option of @browse_information_mode.

Use AdventureWorks2012
GO
Create View [HumanResources].[vDepartment]
AS
SELECT [DepartmentID] as [ID]
,[Name] as [Department Name]
FROM [HumanResources].[Department]
--Output

Now, lets execute sp_describe_first_result_set with the different option of @browse_information_mode.

@browse_information_mode=0

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=0
GO
-- When @browse_information_mode=0, it will give you the meta data but no source data available in this option.

@browse_information_mode=1

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=1
GO
-- When @browse_information_mode=1, it will give you the meta data along with the source info but the source details will be based on this view's table.

@browse_information_mode=2

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=2
GO
-- When @browse_information_mode=2, it will give you the meta data along with the source info but the source details will be based on this view.

Reference : MSDN

Read Full Post »

SQL Server 2012 – Error Handling – Using THROW

Posted in Errors, SQL SERVER, tagged on July 3, 2012| 4 Comments »

Whenever we write a code, we need to do the error handling in it and as much as the handling is strong, it is as simple to trap the bug in the system. SQL Server 2012 provided a new and a flexible way of doing the error handling with the additional features to make it effective and efficient.

First, I will discuss how we were doing error handling in 2005/2008 and what was missing in RAISERROR and why we need this new functionality of THROW.

-- Error HANDLING in SQL Server 2005/2008
BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

Print ERROR_NUMBER() --Actuall Error Number
Print ERROR_LINE()   --Actuall ErrorLine

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, -- Message.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
--OUTPUT

8134  — Actual Error Number
4     — Actual Error Line Number
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.

If you look at the error detail, you get the feeling that RAISERROR detail is unable to handle the error properly. It displays the information message like  Divide by zero error encountered which is correct but the rest of the info like error number 50000 & line number 19 is incorrect because these are not the actual error & line numbers respectively.

Now, lets try the same in SQL SERVER 2012 with THROW and notice the accuracy.

-- Error HANDLING in SQL Server 2012
BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY
BEGIN CATCH
THROW
END CATCH;

--OUTPUT

Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

So, what is it that I did in SQL Server 2012 ? I just replaced all error handling with one keyword “THROW” and it returns more accurate result Like error number 8134 and line number 4  to the caller, than the RAISERROR. Also, it reduces the number of codes.

Read Full Post »

SQL SERVER 2012– Executing Stored Procedure with Result Sets

Posted in SQL SERVER, System Stored Procedure, tagged on July 2, 2012| 9 Comments »

In Sql server 2012,  Microsoft has introduced a very nice and interesting feature and that is execute stored procedure with result set.  With the help of feature, now we can change the column name & data type of the stored procedure result set.

Before SQL server 2012, we were doing the same but it was a lengthy procedure. First we had to create a temporary table and then execute the stored procedure and insert the result set in the temporary table and then select it.

Today, we will discuss the different aspects of this new feature.

Let me explain it with simple examples:

Example 1: (Stored procedure with Single Result set)

Use AdventureWorks2012
GO
Create PROCEDURE SP_ResultSet_Example1
as
Select [DepartmentID]
,[Name]
,[ModifiedDate]
from  [HumanResources].[Department]
GO
EXEC SP_ResultSet_Example1
GO

In the above example, we have three columns in the result set
[DeprtmentID],[Name],[ModifiedDate]

But, I would like to rename the column name and change the data type of Modified Date column in the result set like this
[Deprtment ID],[Department Name], [Modified Date]

Use AdventureWorks2012
GO
EXEC SP_ResultSet_Example1
WITH RESULT SETS
(
( [Department ID] int NOT NULL,
[Department name] Name NOT NULL,
[Modified Date] varchar(11) NOT NULL
));
GO
--Result

Example 2: (Stored procedure with Multiple Result set)

The same way we can do it for multiple result sets also. Given below is the example.

--Without resultset
Use AdventureWorks2012
GO
Create PROCEDURE SP_ResultSet_Example2
as
Select [DepartmentID]
,[Name]
,ModifiedDate
from  [HumanResources].[Department]
Select 'Total' as [Total] ,Count(*) as [Count] from [HumanResources].[Department]
GO
EXEC SP_ResultSet_Example2
GO

--With resultset
Use AdventureWorks2012
GO
EXEC SP_ResultSet_Example2
WITH RESULT SETS
(
( [Department ID] int NOT NULL,
[Department name] Name NOT NULL,
[Modified Date] varchar(11) NOT NULL
),
(
[Total] varchar(5) NOT NULL,
[Department Count] Int NOT NULL
));

Limitations :

  1. You cannot change the data type to any incompatible data type.

For example: Cannot change from varchar to int.

  1. You cannot reduce or increase the number of columns in result set.



Summary:

In this article, we discussed how we can rename and change the data type of the stored procedure output. This feature is very handy especially for SQL Server Integration Services (SSIS) developers when they need to rename/ change the column name and data type respectively in the result  set.

Reference : MSDN

Read Full Post »

SQL Paging in SQL Server 2012 using ORDER BY OFFSET and FETCH NEXT

Posted in Solutions, SQL SERVER, tagged on July 1, 2012| 10 Comments »

In SQL Server 2012, Microsoft has introduced a lot of new T-SQL features and enhancements and one of the best features is Paging. We have been doing this data paging in previous versions of SQL server by writing a stored procedure or a complex query.
Here is a sample, how we were using data paging in SQL Server 2005/ 2008.

USE AdventureWorks
GO
SELECT *
FROM   (SELECT ROW_NUMBER() OVER(ORDER BY EmployeeID) AS
rownumber, [FirstName], [LastName],[JobTitle] FROM HumanResources.vEmployee) AS Salaries1
WHERE  rownumber >= 10 AND rownumber = 10 AND rownumber

But, now it is very easy to use and implement paging in SQL Server 2012. We only need to set two keywords (OFFSET, FETCH NEXT) with Order By Clause and we can get our required records.

Lets Proceed first with each keyword.

Order By Offset :

USE AdventureWorks2012
GO
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
OFFSET 10 ROWS

If we use offset with order by clause, the query excludes the number of records we mentioned in OFFSET n Rows. In the above example, we used OFFSET 10 ROWS so, SQL will exclude first 10 records from the result and display the rest of all records in the defined order.

Order By Offset With FETCH NEXT :

USE AdventureWorks2012
GO
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID

OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

If we use offset with fetch next, we can define how many records we need to exclude. Also we can define that after exclusion how many records we need to pick up. In the above example, SQL excludes first 10 records and will pick up 10 records afterwards.
In other words, we can say that whenever we need to do paging we need 2 things. 1st, the page no. and 2nd the no. of records in each page. Here OFFSET is used for page number and FETCH NEXT is the number of records in each page.

Order By Fetch Next Rows Only:
If, we use Fetch Next with order by clause only without Offset, SQL will generate an error. We cannot use Fetch Next without Offset.

USE AdventureWorks2012
go
Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
--OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

Given below is a simple stored procedure to perform paging in SQL server 2012.

USE AdventureWorks2012
go
Create Procedure dbo.Sp_Data_Paging
@PageNo int,
@RecordsPerPage int
AS

Select BusinessEntityID,[FirstName], [LastName],[JobTitle]
from HumanResources.vEmployee
Order By BusinessEntityID
OFFSET (@PageNo-1)*@RecordsPerPage ROWS
FETCH NEXT @RecordsPerPage ROWS ONLY
GO
Sp_Data_Paging 1,10 --First Page
GO
--Result


Sp_Data_Paging 2,10 --2nd Page
--Result
GO


Sp_Data_Paging 3,10 --3rd Page
--Result
GO

Conclusion :

These two keywords OFFSET and FETCH NEXT clause give boost to data pagination in SQL server 2012.
It also improves performance (because it picks up only certain records from the database) and reduces the number of codes and effort. Now the developers can do the data paging not only from front end (. NET) but also from the back end.

Read Full Post »