dcsimg
March 27, 2017
Hot Topics:

Enhance Your Web Apps with T-SQL Tricks

  • September 26, 2007
  • By Andrew Bonslater
  • Send Email »
  • More Articles »

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.





Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel