Category Archives: Sql Server

Execute an sql query as string using sp_executesql and store the result in a variable

Small but useful tip for Sql Server 2012.

Here is a simple example for how we can execute an sql string query, execute it and store the result in a variable for further use.

declare @query nvarchar(1000), @result datetime

set @query = ‘select @result = GETDATE()’

EXEC sp_executesql @query,
N’@result datetime OUTPUT’,
@result OUTPUT

print CAST(@result as nvarchar(100))

Advertisements

How to find query/stored procedure used in an SSRS report

If yor are troubleshooting an SSRS report which you have not created by yourself. Finding it’s location may take sometime even though you know that issue is with the query. Hence to find the query/stored procs used in the report we can directly query ReportServer database and get those details from it’s report dataset.

SELECT Name As ReportName,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportInXMLFormat
FROM ReportServer.dbo.Catalog
where Type = 2 
order by Name

it will give an XML result which will look like this.

<DataSets>
    <DataSet Name="EmployeeDetails">
        <Query>
            <CommandType>StoredProcedure</CommandType>
            <CommandText>usp_getEmpDetails</CommandText>
            <QueryParameters>
                <QueryParameter Name="@EMPLOYEEID">
                    <Value>=Parameters!EMPID.Value</Value>
                </QueryParameter>               
            </QueryParameters>
            <DataSourceName>DVEMP02</DataSourceName>
        </Query>
        .
        .
        .
   </DataSet>
    <DataSet Name="Department">
        <Query>
            <DataSourceName>DVEMP02</DataSourceName>
            <CommandText>
                SELECT DepartmentId AS ID, DeparmentName 
                FROM Department               
            </CommandText>
            <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
        </Query>
    </DataSet>
    .

Query tag will tel you about the sql query and stored proc used in the reports. You can start troubleshooting right away if it’s a data or a query issue.

I hope it helps!

GridView with built in CustomPaging – Asp.Net 4.5

Every web developer hates Gridview ViewState specially when its large in size. Solution to this is to create a custom paging solution. but it was too much of work. You need to Create a custom control and then handling all the combinations of click on page links. Styling of the pager was also not standard.

Asp.Net 4.5 resolved this by providing a Gridview attribute AllowCustomPaging = “true” which allow us to utilize built in gridview paging with custom paging logic. There is one more property VirtualItemCount added to gridview which should be set to the total number of records.

Below is the simple example. It seems so easy that you can do it right away in your existing code with minimal changes.

UI Code:


 <asp:GridView ID="gvPersons" runat="server" 
  AllowCustomPaging="true"
  AllowPaging="True"           AutoGenerateColumns="False" PageSize="20"
  OnPageIndexChanging="gvPersons_PageIndexChanging">

 <Columns>
 <asp:BoundField DataField="BusinessEntityID" HeaderText="Product"
 SortExpression="BusinessEntityID" />

 <asp:BoundField DataField="PersonType" HeaderText="PersonType"
 ReadOnly="True" SortExpression="PersonType" />

 <asp:BoundField DataField="FirstName" HeaderText="FirstName"
 SortExpression="FirstName" />

 <asp:BoundField DataField="LastName" HeaderText="LastName"
 SortExpression="LastName" />

 </Columns>
 <PagerStyle HorizontalAlign="Right" />
 <PagerSettings Mode="Numeric" />
 </asp:GridView>

Custom Paging Stored Procedure in Sql server 2012

This query is the heart of this custom paging logic. Sql Server 2012 provides simply “Awesome” way of writing a very complex query (in earlier versions).


CREATE PROCEDURE usp_GetPersonsPaged
	@StartRowIndex  int,
	@MaximumRows	int
AS
BEGIN
	
	SET NOCOUNT ON;
	    DECLARE @lastRowIndex int
		SET @lastRowIndex = (@StartRowIndex + @MaximumRows) - 1;
		 

	 SELECT   [BusinessEntityID]
		 ,[PersonType]     
		 ,[FirstName]     
		 ,[LastName]  FROM [AdventureWorks2012].Person.Person
	 ORDER BY BusinessEntityID
	 OFFSET @StartRowIndex ROWS
	 FETCH NEXT @lastRowIndex ROWS ONLY;	

END

Code Behind:

Nothing changes here. Only your data access layer method returns Maximum rows equal to page size.

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                gvPersons.VirtualItemCount = Person.GetTotalPersonCount() ;
                BindGrid(0,gvPersons.PageSize);                
            }
        }

        private void BindGrid(int pageIndex,int pageSize)
        {            
            gvPersons.DataSource = Person.GetPersonListPaged(pageIndex, gvPersons.PageSize);
            gvPersons.DataBind();
        }

        protected void gvPersons_PageIndexChanging(object sender, GridViewPageEventArgs e)
        { 
            gvPersons.PageIndex = e.NewPageIndex;
            BindGrid(gvPersons.PageIndex,gvPersons.PageSize);
        }

Data Access Code:

you can use AdventureWorks Sample DB for Sql server 2012 to check out performance.


public class Person
    {
        public int BusinessEntityID { get; set; }

        public string PersonType { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }


        public static List<Person> GetPersonListPaged(int startIndex, int maxPazeSize)
        {
            List<Person> persons = new List<Person> ();

            SqlConnection con = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                con.ConnectionString = "data source=.\\sqlserver2012;initial catalog=AdventureWorks2012;integrated security=True;";

                cmd.Connection = con;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "usp_GetPersonsPaged";

                cmd.Parameters.AddWithValue("StartRowIndex", startIndex);
                cmd.Parameters.AddWithValue("MaximumRows"  , maxPazeSize);
                con.Open();
                
                SqlDataReader dr;
                dr = cmd.ExecuteReader();
                
                while (dr.Read())
                {
                    Person p = new Person() {BusinessEntityID=dr.GetInt32(0),PersonType=dr.GetString(1),FirstName=dr.GetString(2),LastName=dr.GetString(3) };

                    persons.Add(p);                    
                }                
                                
                con.Close();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                con.Dispose();
                cmd.Dispose();
            }

            return persons;
        }



        public static int GetTotalPersonCount()
        {
            int totalCount = 0;

            SqlConnection con = new SqlConnection();
            SqlCommand cmd = new SqlCommand();            
            try
            {
                con.ConnectionString = "data source=.\\sqlserver2012;initial catalog=AdventureWorks2012;integrated security=True;";
                
                cmd.Connection = con;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select count(1) from [AdventureWorks2012].[Person].[Person];";
                con.Open();
                totalCount = (int)cmd.ExecuteScalar();

                con.Close();
            }
            catch (Exception ex)
            {


            }
            finally
            {
                con.Dispose();
                cmd.Dispose();
            }

            return totalCount;
        }
    }
}

I hope it helps!!

Custom paging query comparison Sql server 2000 , 2005 and 2012 (denali)

Custom paging is a very common requirement for any web application. It was always performance enhancer for our old applications. Linq and generic collections enabled us to process and query the result sets in memory. However, if the query can fetch only desired number of records with a better query performance, we need not perform any manipulation later on.

I am going to describe the custom paging query which had evolved with every Sql server upgrade. I will compare Sql Server 2000, 2005 and 2012 (denali) version to showcase the performance improvement. Sample DB isAdventureWorks.

Sql Server 2000: In 2000 we had to create a temp table before we skip certain number of records and fetch required number of records only. Temp table creation and inner join degrades the query performance here.


CREATE PROCEDURE GetPersonPaged_Sql2000
(
@startRowIndex int,
@pageSize int
)
AS
BEGIN
 SET NOCOUNT ON;

DECLARE @lastRowIndex int

SET @lastRowIndex = (@startRowIndex + @pageSize) - 1;

--Create a table variable
 DECLARE @TempItems TABLE
 (
 ID int IDENTITY,
 BusinessEntityID int,
 firstname varchar(200),
 MiddleName varchar(200)
 )
 -- Insert the rows from tblItems into the temp. table
 INSERT INTO @TempItems (BusinessEntityID,firstname,MiddleName)
 SELECT BusinessEntityID,firstname,MiddleName FROM Person.Person
 order by BusinessEntityID

-- Now, return the set of paged records
 SELECT p.* FROM @TempItems t
 INNER JOIN Person.Person p ON
 p.BusinessEntityID = t.BusinessEntityID
 WHERE ID BETWEEN @startRowIndex AND @lastRowIndex
 order by BusinessEntityID

END

Sql Server 2005: With Ranking functions introduced in 2005 upgrade it was easier to get a row number for each record (Please note that Sql Server never had any keyword RowNum like Oracle). ROW_NUMBER() is the function which helps sorting and numbering any result set. Please refer to http://msdn.microsoft.com/en-us/library/ms186734.aspx to know more about this function.
New implementation was even better using Common table expression CTE. Refer http://msdn.microsoft.com/en-us/library/ms190766.aspxto read more about CTE.

CREATE PROCEDURE GetPersonPaged_Sql2005
(
@startRowIndex int,
@pageSize int
)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @lastRowIndex int

SET @lastRowIndex = (@startRowIndex + @pageSize) - 1;

;with CTE
 AS
 (
 select BusinessEntityID,firstname,MiddleName , ROW_NUMBER() OVER (order by BusinessEntityID ) as rownum
 from Person.Person
 )
 select * from CTE
 where rownum between @startRowIndex AND @lastRowIndex

END

It had improved the query performance by more than 80% than the Sql 2000 query. You can test the same by comparing execution plans.
Finally the winner is :

Sql Server 2012:It introduced new keywords like OFFSET and ROWS. It not only reduces the number of lines of code but improve the query performance by more than 50% when compared to Sql 2005 query using CTE.


CREATE PROCEDURE GetPersonPaged_Sql2012
(
@startRowIndex int,
@pageSize int
)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @lastRowIndex int

SET @lastRowIndex = (@startRowIndex + @pageSize) - 1;

SELECT * FROM Person.Person
 ORDER BY BusinessEntityID
 OFFSET @startRowIndex ROWS
 FETCH NEXT @lastRowIndex ROWS ONLY;

END

Conclusion:    2012 query is the best of all three in terms of ease, lines of code and performance. If you are still using the old style query(even after upgrading to next version of sql server) for custom paging, this is high time you should switch to query with new syntax which is best suited for that particular version.

Note: I did not cover Sql server 2008 as it did not add any features which could add any value to custom paging querying.

I hope it is useful!

Custom Paging stored procedure in Sql Server – Getting total number of records with CTE

To implement custom paging using in asp.net gridview/listview/formview controls is very common. Main concept is to fetch records from database which are shown on the page when loaded. clicking on page number will again fetch next set of records.

Here are the steps we need to take

  1. We need to write a stored proc which accepts PageSize and PageIndex as input parameters. these params help in getting the start index and endindex of the result set.
  2. Next step is to create a custom pager control which needs total number of records returned by the same (well, ideally) stored proc so we put an output param say TotalRecords to the same SP.
  3. Here is the main query using Common Table expression introduced in Sql Server 2005

    ;with CTE AS
    (
    select ProductId,Name,<strong> count(*) over(partition by '') as TotalRecords</strong> , ROW_NUMBER() over( order by productid) as rownum
    from Products
    )
    
    select * from CTE where rownum between @StartIndex and @EndIndex
    

    Here the interesting part is getting total number of records in the same query while ranking each row with a rownumber. By earlier techniques we need to get total number of records in a seperate query/udf. It really saved me a lot of time and improved the query performance as well.
    In Sql Server 2012 “De nali” version it is going to be even easier to skip certain number of rows and take only specified number of records by using OffSet keyword. I am yet trying to explore those new features. I hope to share more about it in future.

    I hope it helps!