Effective Paging with Large Amount of Data in ASP.NET

Normally we are using Gridview control for Data Binding in ASP.NET.
Below is the sample code for Data Binding in Asp.Net using Gridview Control.

      Sub BindData()
                    Dim con As SqlConnection, sqldad As SqlDataAdapter, ds As DataSet
                    Dim strcon As String
                    Dim server As String
                    Dim database_name, user_name, user_password As String
                    Try
                        server = "your server name"
                        database_name = "database name"
                        user_name = "database user name"
                        user_password = "database password"
                        strcon = "SERVER=" & server & ";DATABASE=" & database_name & 
                                  ";UID=" & user_name & ";PWD=" & user_password
                        con = New SqlConnection(strcon)
                        sqldad = New SqlDataAdapter("select * from Employee", strcon)
                        ds = New DataSet
                        sqldad.Fill(ds)
                        Gridview1.DataSource = ds.Tables(0)
                        Gridview1.DataBind()
                   
                    Catch ex1 As SqlException
                        Response.Write(ex1.Message)
                    Catch ex As Exception
                        Response.Write(ex.Message)
                    End Try
             End Sub      
        

To enable paging for Gridview, just set AllowPaging property to True and give page size.
Assume page size is 10 and we need to implement the gridview’s PageIndexChanging method.

        Protected Sub Gridview1_PageIndexChanging(ByVal sender As Object, ByVal  e As
             System.Web.UI.WebControls.GridViewPageEventArgs) Handles Gridview1.PageIndexChanging
             Gridview1.PageIndex = e.NewPageIndex
             BindData()	
        End Sub
        

That’s it, Gridview paging is working.
It is ok for small amount of data,but it is notsuitable for bulk data.

Why because, for every page it binds total records and showing only particular page records.

Take for example, if our query giving the 10,000 records and we want to show 10 records at a time. What we are doing , we are binding 10,000 records for each requet and we are showing 10 records only. i.e, we are Unneceesarly binding 9990 records. It is giving the more load on Database server for every page request and on webserver also.(because we are binding 10,000 records show just 10 records per page)

Is there any solution to avoid to binding unnecessary records.
Yes, we have a solution by using custom paging with SQL Server 2005.