Enhance Your Web Apps with T-SQL Tricks

Wednesday Sep 26th 2007 by Andrew Bonslater
Share:

Take full advantage of the new T-SQL functionality to simplify and enhance your projects.

With all of the new functionality that's been released with T-SQL for SQL Server 2005, you now have more options that make performing complex tasks very simple with very few lines of code. Explore a couple of tasks that are becoming quite common in many development tasks and learn about tips for making them as simple as possible. I'll first cover a way to optimize GridView paging by creating a paged search in your T-SQL query. With only a few changes to your current T-SQL queries, you can utilize the ASP .NET ObjectDataSource to retrieve only the data needed for the current page and nothing more.

Next, you'll go over a simple and effective way to randomize your data sets without doing any complex tasks. Randomizing your dataset can be a fun and cool way to spice up your pages by displaying a new set of results every time a user views your page.

Optimizing GridView Paging with T-SQL

It is very common for a developer to populate an ASP .NET GridView by writing a query to pull back an entire dataset and let the GridView handle the paging. This method is not a problem when you are only returning a small set of data, but can be a huge performance hog when dealing with hundreds or thousands of rows. Wouldn't it make more sense to retrieve only the data that you need to display? Let me show you a simple way of creating a T-SQL query that pulls only the data that you need for each page in the GridView. I will assume that you already know how to set up your GridView to allow paging.

Creating a Paged Search

The first step to creating a paged search would be to write your search query that pulls back your entire result set. Use the Ranking function to create a row number column that will determine which set of data needs to be returned for the current page. The Ranking function is new to T-SQL in SQL Server 2005. It returns a ranking value for each row in your result set.

SELECT     PurchaseOrder.Id, Customer.Id, Customer.FirstName,
           Customer.LastName, ROW_NUMBER()
           OVER(ORDER BY PurchaseOrder.OrderDate DESC) as RowNum
FROM       Customer LEFT JOIN
           PurchaseOrder ON Customer.Id = PurchaseOrder.CustomerId
WHERE      PurchaseOrder.OrderDate BETWEEN DATEADD
           (getDate(), day, -31) AND getDate()

Now that you have your entire result set, you will wrap it with an outer query that pulls back the data that you need for the current page. Part of the query will include a parameter for the Max number of rows to display per page and a parameter for the Start Row index. The Start Row index will be provided by the GridView's ObjectDataSource. I will show you how to pass the Start Row index to your query from the ObjectDataSource.

-- Parameters
@MaxRows INT = 10,
@StartRowIndex INT

SELECT * FROM
(
   SELECT     PurchaseOrder.Id, Customer.Id, Customer.FirstName,
              Customer.LastName, ROW_NUMBER()
              OVER(ORDER BY PurchaseOrder.OrderDate DESC) as RowNum
   FROM       Customer LEFT JOIN
              PurchaseOrder ON Customer.Id = PurchaseOrder.CustomerId
   WHERE      PurchaseOrder.OrderDate BETWEEN DATEADD (getDate(),
              day, 1) AND getDate()
) as CustomerOrders
WHERE         RowNum BETWEEN @ StartRowIndex + 1 AND @
              StartRowIndex + @in_MaxRows

Now, go to the ObjectDataSource to look at how you can get the Start Row index. Take a look at the example ObjectDataSource code below. You will notice two properties that are set, StartRowIndexParameterName and MaximumRowsParameterName. These two properties will take care of setting the parameters that are needed to get the rows to be displayed.

<asp:ObjectDataSource ID="odsBlogSearch" runat="server"
   EnablePaging="True" MaximumRowsParameterName="MaxRows"
   OldValuesParameterFormatString="original_{0}"
   SelectCountMethod="GetCountSearch"
SelectMethod="GetDataPagedSearch"
   StartRowIndexParameterName="StartRowIndex"
TypeName="SampleSite.Web.Orders.OrdersTableAdapters.POTableAdapter">
   <SelectParameters>
      <asp:Parameter Name="MaxRows" Type="Int32" />
      <asp:Parameter Name="StartRowIndex" Type="Int32" />
   </SelectParameters>
</asp:ObjectDataSource>

One other thing you need to do is tell the ObjectDataSource the total results there are so that the proper page numbers will be displayed. You set another property, called SelectCountMethod, that will query the database for a row count of the results. The query will be a scalar query that will return your row count. The GridView will use this row count to determine how many pages to display on the GridView's pager. If you have a label on your page that displays the total number of results, you also can use this query to populate it.

SELECT     COUNT(*)AS Total
FROM       Customer LEFT JOIN
           PurchaseOrder ON Customer.Id = PurchaseOrder.CustomerId
WHERE      PurchaseOrder.OrderDate BETWEEN DATEADD
           (getDate(), day, 1) AND getDate()

Using this method results in a paged GridView that is much more efficient then pulling the entire result set into the GridView.

Using T-SQL to Return a Randomized Dataset

Another common occurrence that many developers struggle with is creating a simple randomized dataset. There are a few ways that this can be done, but most of them involve creating functions and manipulating the Random function to make this work; this can become very complex.

I am going to show you a quick and easy way to create a randomized dataset all in T-SQL. You will use the NEWID() function, instead of the RAND() function, to generate a random ID. You will NOT use the RAND() function because putting the RAND() function as a column in your SELECT statement will return the exact same value for each row, making randomization of your query results not possible. Instead, you will cast your NEWID() as BINARY, and then cast as an INTEGER. Doing this results in an integer value based on the random ID. You can add this random number as a column in your SELECT statement, and do an outer SELECT to sort on the random number. Take a look at my example below.

Your first step is to create your SELECT statement with a column for the Random ID. The Random ID column will be used to randomize the order that the rows will be retrieved in your next step.

SELECT CAST(CAST(NEWID() AS BINARY(4)) AS INT) AS Random, Id
FROM Customer
WHERE Active = 1
ORDER BY DateCreated DESC

The query above returns a set of results with a column that contains a random integer value for each row. To make this result set get returned in a random order, you need to wrap this query with an outer query that will order the results by the Random column. The inner query can be used just like a table, so you can join it to other tables in your query to return additional information.

SELECT TOP 8 Subquery.Id, Subquery.FirstName, Subquery.LastName,
   Address.City
FROM
(
   SELECT CAST(CAST(NEWID() AS BINARY (4)) AS INT) AS Random, Id,
      FirstName, LastName
   FROM Customer
   WHERE Active = 1
   ORDER BY DateCreated DESC
) AS SubQuery LEFT JOIN
Address.Id
ORDER BY Random

That's all it takes to make your T-SQL query randomized. Every time you call this query, it will return a new set of results.

Summary

This article demonstrates ways of using some of the new T-SQL features to increase performance and enhance your .NET web applications. There are still many other uses for these features that allow .NET and T-SQL to work harmoniously. I recommend that you take full advantage of the new T-SQL functionality to simplify and enhance your project.

About the Author

Andrew Bonslater (MCAD, MCSD) is a solutions developer for mid to large-sized organizations. He is a thought leader with Crowe Chizek in Indianapolis, Indiana. Andrew specializes in Web-Based solutions, Reach him at abonslater@crowechizek.com.

Article edited by Chad Campbell

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved