Skip to main content

Paging in MS SQL Server 2005/2008 using ROW_NUMBER()

Sometimes we need to implement paging functionality in our database. We can use ROW_NUMBER() function for this purpose. ROW_NUMBER function is one of the ranking function of SQL Server. Here is an example of ROW_NUMBER -

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY EmpID) AS Row, * FROM Employee AS tbl ) WHERE Row >= 1 AND Row <= 10

ROW_NUMBER is used for assigning sequential numbers to the records of record-set. In the above query a sequel number is assigned to each record of the Employee table.

Here we have used following two clause -
  1. OVER clause - It is used for specifying the ordering of records before assigning the sequence to each record.
  2. ORDER clause -  In order clause we define column or columns which is used to order the records in the record-sets. In our case we have used EmpId with ORDER clause, so first each record of Employee table is first ordered  according to EmpId and then sequence number is assigned to each record.
*PARTITION clause can also be used along with OVER and ORDER clause (optional). Partition clause allows user to create some partition or sub record-sets as per the given column.

Once a sequence number is assigned to each record of the Employee table, then we can use each Row of record-set as one page.

Comments

Popular posts from this blog

Show Image on canvas HTML5

Here is the sample code to select image from system and display it on html5 canvas and javascript: <input type= "file" id= "selectedImage" /> <canvas id= "myCanvas" width= "500" height= "500" > </canvas> Javascript code: $( "#selectedImage" ).change( function (e) { var URL = window .URL; var url = URL.createObjectURL(e.target.files[ 0 ]); img.src = url; img.onload = function () { var canvas = document .getElementById( "myCanvas" ); var ctx = canvas.getContext( "2d" ); var imgSize = calculateAspectRatioFit(img.width, img.height, canvas.clientWidth, canvas.clientHeight); ctx.clearRect( 0 , 0 , canvas.width, canvas.height); ctx.drawImage(img, 0 , 0 , imgSize.width, imgSize.height); } }); function calculateAspectRatioFit(srcWidth, srcHeight, maxWidth, maxHeight) { var ratio = Math .min(maxWi

Pass byte array from C# to C++ and vice-versa

Pass byte array from C# to C++                           If you want to pass a byte array to native DLL as parameter, you can use the Intptr to do this, please check the demo below. /C++ API code: TestDLL_API void TestArrayPara (BYTE * pArray, int nSize) { for ( int i= 0 ; i<nSize; i++) printf( "%d\n" , pArray[i]); } //C# code: class TestClass { [DllImport(@"TestDll.dll")] public static extern void TestArrayPara (IntPtr pArray, int nSize); public static void Test () { byte [] array = new byte [ 16 ]; for ( int i = 0 ; i < 16 ; i++) { array[i] = ( byte )(i + 97 ); } int size = Marshal.SizeOf(array[ 0 ]) * array.Length; IntPtr pnt = Marshal.AllocHGlobal(size); try { // Copy the array to unmanaged memory. Marshal.Copy(array, 0 , pnt, array

Display Tooltip for Combo Box item C#.NET Winforms

In windows form combo box control sometimes while adidng items dynamically we have items whose width is greater than width of combox box control. In this case for making UI more user friendly we can show tooltip over such item. Here is the sample C# code to display such tooltip:  Add a Tooltip control on the form.  Add following code : this . combo_box1 . DropDownStyle = System . Windows . Forms . ComboBoxStyle . DropDownList; this . combo_box1 . DrawMode = DrawMode . OwnerDrawFixed; this . combo_box1 . DrawItem += new DrawItemEventHandler(combo_box1_DrawItem); this . combo_box1 . DropDownClosed += new EventHandler(combo_box1_DropDownClosed); this . combo_box1 . MouseLeave += new EventHandler(combo_box1_Leave); void combo_box1_DrawItem( object sender, DrawItemEventArgs e) { if (e . Index < 0 ) { return ; } string text = combo_box1 . GetItemText(combo_box1 . Items[e . Index