Simple Row Counter SQL?

[RobDog888]

Im not a db guru so wondering if any of you guys out there had a technique for adding a dynamic row id counter field that didnt use Count() or an Aggregate function?

[techgnome]

if you're using at least SQL Server 2010, you can use Row_Number() ... another technique I've used that's pretty universal is to dump the data into a temp table that has an identity/autoincrement field on it then return the data from there.

-tg

[RobDog888]

Thanks! We are using 2012 So far I got a non-impacting solution by using a variable counter in the return method in the model class. Its "ok" but not as elegant as could be I think.
Looks like row number may be more impactful on resources than using a counter variable in c# I assume?

[techgnome]

I've found row_number to be quick and pretty light on resources. i've used it to sort out and count millions of rows in SQL with minimal impact. if there is any overhead or impact, it's so minimal it's negligible. No more than a sort.

-tg

[RobDog888]

Its a HA website so performance is always being scrutinized. Its in a hierarchical master / detail grid for invoicing. The ID is for the line item numbers in the invoice detail which are for display purposes / easier reading.

Its complicated by the kendo grid we are using

        public static List<InvoiceDetail> GetDetailByInvoiceID(DatabaseController databaseController, long invoiceID, long firmID)
        {
            long i = 0;
            List<InvoiceDetail> invoiceDetails = new List<InvoiceDetail>();
            string sql = "SELECT ";
            sql += "[InvoiceDetailID], [InvoiceID], [FirmID], [CaseID], [CreatedDate], [CreatedBy], [UpdatedDate], [UpdatedBy], [Deleted], ";
            sql += "[Location], [InvoiceDetailDate], [DetailAmount] ";
            sql += "FROM [InvoiceDetail] ";
            sql += "WHERE [InvoiceID] = @invoiceID AND [FirmID] = @firmID AND [Deleted] = 0 ";
            sql += "ORDER BY [InvoiceDetailID] ASC;";
            SqlCommand command = new SqlCommand(sql, databaseController.connection);
            command.Parameters.Add("@invoiceID", SqlDbType.BigInt).Value = invoiceID;
            command.Parameters.Add("@firmID", SqlDbType.BigInt).Value = firmID;
            command.Prepare();
            SqlDataReader reader = databaseController.query(command);
            if (reader != null)
            {
                while (reader.Read())
                {
                    InvoiceDetail invoiceDetail = new InvoiceDetail();
                    invoiceDetail = InvoiceDetail.initWithReader(reader);
                    invoiceDetail.RowNumber = ++i;
                    invoiceDetails.Add(invoiceDetail);
                }
                reader.Close();
            }
            return invoiceDetails;
        }

[ChrisE]

well I don't know the Kendo Grid.


this is what I use for the DGV, it adds a Linenumber
see if you can't adapt it to the Kendo Grid

Public Class Form1

    Private mRandomClass As New Random()

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dtb As New System.Data.DataTable
        dtb.Columns.Add("Column1", GetType(Integer))
        dtb.Columns.Add("Column2", GetType(Double))
        dtb.Columns.Add("Column3", GetType(Date))
        dtb.Columns.Add("Column4", GetType(Decimal))
        dtb.Columns.Add("Column5", GetType(Integer))
        dtb.Columns.Add("Column6", GetType(Integer))

        For i As Integer = 0 To 10
            Dim Zahl As Double = mRandomClass.Next(100, 10000000)
            Dim Datum As Date = Date.Now.AddSeconds(Convert.ToDouble(mRandomClass.Next(1, 1000000)))
            Dim Geld As Decimal = mRandomClass.Next(1, 100000000)
            dtb.LoadDataRow(New Object() {1 + i, _
                                                   Zahl.ToString("#,##0.00"), _
                                                   Datum.ToString("dd.MM.yyyy HH:mm:ss"), _
                                                   Geld.ToString("#,##0.00"), _
                                                   mRandomClass.Next, _
                                                   mRandomClass.Next}, _
                                               True)
        Next
        Dim dvw As DataView = dtb.DefaultView
        dvw.Sort = "Column3 ASC"
        Dim dtbSorted As DataTable = dvw.ToTable()
        DataGridView1.DataSource = dtbSorted
        Me.DataGridView1.Columns(4).DefaultCellStyle.Format = "c"
    End Sub

    Private Sub DataGridView1_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
        RownumberToHeader(CType(sender, DataGridView), e, Color.Brown)
    End Sub

    Public Sub RownumberToHeader(ByVal dgv As DataGridView, _
   ByVal e As DataGridViewCellPaintingEventArgs, _
   Optional ByVal nColor As Object = Nothing, _
   Optional ByVal lastRowOnly As Boolean = False, _
   Optional ByVal selRowOnly As Boolean = False)
        Dim d As New Date
        Dim w As Boolean, numColor As Color, dFont As Font

        With dgv
            If .RowCount < 2 Then Exit Sub

            Dim font As Font = .RowHeadersDefaultCellStyle.Font
            dFont = New Font(font.FontFamily, font.Size, FontStyle.Bold)

            Dim rc As String = Format(.RowCount, " #,##0")

            Dim maxSize As SizeF = New SizeF(TextRenderer.MeasureText(rc, dFont).Width, _
              TextRenderer.MeasureText(rc, dFont).Height)

            .RowHeadersWidth = CInt(maxSize.Width + 25)

            numColor = .RowHeadersDefaultCellStyle.ForeColor
            If nColor IsNot Nothing Then numColor = CType(nColor, Color)

            Dim k As Short = CShort(IIf(.AllowUserToAddRows = True, 2, 1))
            Dim ri As Integer = 0

            w = (e.ColumnIndex = -1 And e.RowIndex >= ri)
            If lastRowOnly Then
                ri = .RowCount - k
                w = (e.ColumnIndex = -1 And e.RowIndex >= ri)
            ElseIf selRowOnly And dgv.SelectedRows.Count > 0 Then
                ri = .SelectedRows(0).Index
                w = (e.ColumnIndex = -1 And e.RowIndex = ri)
            End If
        End With

        If w Then
            If dgv.Rows(e.RowIndex).IsNewRow = False Then
                With e
                    Dim rowStr As String = Format(.RowIndex + 1, " #,##0")


                    Dim StringSize As SizeF = New SizeF( _
                      TextRenderer.MeasureText(rowStr, dFont).Width, _
                      TextRenderer.MeasureText(rowStr, dFont).Height)

                    .PaintBackground(.ClipBounds, False)
                    .PaintContent(.ClipBounds)

                    Using brush As New SolidBrush(numColor)
                        .Graphics.DrawString((rowStr).ToString, dFont, brush, _
                          .CellBounds.Right - StringSize.Width, _
                          .CellBounds.Top + (.CellBounds.Height - StringSize.Height) / 2)
                    End Using

                    .Handled = True
                End With
            End If
        End If
    End Sub

HTH

[GaryMazzone]

I've used Row_Number() Partition BY () on HA systems for years now. It was used on on-line reservations systems, financial systems with out a performance impact that caused issues

[FunkyDexter]

Looks like row number may be more impactful on resources than using a counter variable in c# I assume?

Depends on the guts of the server vs the client(s) so it's pretty much impossible to say. Neither is free but the Row_Number function is pretty light weight (as TG says, it basically resolves as a sort) and will be resolved on the DB server which is likely to have plenty of clout. Putting your logic in the client effectively distributes the processing, which is good, but does so to machines that are likely to be less gutsy than a server. Really you have to try out both and see what you get.

If it's purely for display purposes across a small number of rows I'm kinda inclined to say keep it in the UI layer of the client but that's driven more by separation of concerns than performance.

[RobDog888]

well I don't know the Kendo Grid.


this is what I use for the DGV, it adds a Linenumber
see if you can't adapt it to the Kendo Grid

Public Class Form1

    Private mRandomClass As New Random()

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dtb As New System.Data.DataTable
        dtb.Columns.Add("Column1", GetType(Integer))
        dtb.Columns.Add("Column2", GetType(Double))
        dtb.Columns.Add("Column3", GetType(Date))
        dtb.Columns.Add("Column4", GetType(Decimal))
        dtb.Columns.Add("Column5", GetType(Integer))
        dtb.Columns.Add("Column6", GetType(Integer))

        For i As Integer = 0 To 10
            Dim Zahl As Double = mRandomClass.Next(100, 10000000)
            Dim Datum As Date = Date.Now.AddSeconds(Convert.ToDouble(mRandomClass.Next(1, 1000000)))
            Dim Geld As Decimal = mRandomClass.Next(1, 100000000)
            dtb.LoadDataRow(New Object() {1 + i, _
                                                   Zahl.ToString("#,##0.00"), _
                                                   Datum.ToString("dd.MM.yyyy HH:mm:ss"), _
                                                   Geld.ToString("#,##0.00"), _
                                                   mRandomClass.Next, _
                                                   mRandomClass.Next}, _
                                               True)
        Next
        Dim dvw As DataView = dtb.DefaultView
        dvw.Sort = "Column3 ASC"
        Dim dtbSorted As DataTable = dvw.ToTable()
        DataGridView1.DataSource = dtbSorted
        Me.DataGridView1.Columns(4).DefaultCellStyle.Format = "c"
    End Sub

    Private Sub DataGridView1_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
        RownumberToHeader(CType(sender, DataGridView), e, Color.Brown)
    End Sub

    Public Sub RownumberToHeader(ByVal dgv As DataGridView, _
   ByVal e As DataGridViewCellPaintingEventArgs, _
   Optional ByVal nColor As Object = Nothing, _
   Optional ByVal lastRowOnly As Boolean = False, _
   Optional ByVal selRowOnly As Boolean = False)
        Dim d As New Date
        Dim w As Boolean, numColor As Color, dFont As Font

        With dgv
            If .RowCount < 2 Then Exit Sub

            Dim font As Font = .RowHeadersDefaultCellStyle.Font
            dFont = New Font(font.FontFamily, font.Size, FontStyle.Bold)

            Dim rc As String = Format(.RowCount, " #,##0")

            Dim maxSize As SizeF = New SizeF(TextRenderer.MeasureText(rc, dFont).Width, _
              TextRenderer.MeasureText(rc, dFont).Height)

            .RowHeadersWidth = CInt(maxSize.Width + 25)

            numColor = .RowHeadersDefaultCellStyle.ForeColor
            If nColor IsNot Nothing Then numColor = CType(nColor, Color)

            Dim k As Short = CShort(IIf(.AllowUserToAddRows = True, 2, 1))
            Dim ri As Integer = 0

            w = (e.ColumnIndex = -1 And e.RowIndex >= ri)
            If lastRowOnly Then
                ri = .RowCount - k
                w = (e.ColumnIndex = -1 And e.RowIndex >= ri)
            ElseIf selRowOnly And dgv.SelectedRows.Count > 0 Then
                ri = .SelectedRows(0).Index
                w = (e.ColumnIndex = -1 And e.RowIndex = ri)
            End If
        End With

        If w Then
            If dgv.Rows(e.RowIndex).IsNewRow = False Then
                With e
                    Dim rowStr As String = Format(.RowIndex + 1, " #,##0")


                    Dim StringSize As SizeF = New SizeF( _
                      TextRenderer.MeasureText(rowStr, dFont).Width, _
                      TextRenderer.MeasureText(rowStr, dFont).Height)

                    .PaintBackground(.ClipBounds, False)
                    .PaintContent(.ClipBounds)

                    Using brush As New SolidBrush(numColor)
                        .Graphics.DrawString((rowStr).ToString, dFont, brush, _
                          .CellBounds.Right - StringSize.Width, _
                          .CellBounds.Top + (.CellBounds.Height - StringSize.Height) / 2)
                    End Using

                    .Handled = True
                End With
            End If
        End If
    End Sub

HTH

Thanks Chris but its an asp.net mvc website with the telerik/kendo grid which renders different than a winforms grid. It calls the controller method I posted to get its datasource at the client level

[RobDog888]

I've used Row_Number() Partition BY () on HA systems for years now. It was used on on-line reservations systems, financial systems with out a performance impact that caused issues

Thanks Gary, I think you guys are right on the minimal performance hit with Row_Number(). I will test it out once the page is complete and do a comparison

[RobDog888]

Depends on the guts of the server vs the client(s) so it's pretty much impossible to say. Neither is free but the Row_Number function is pretty light weight (as TG says, it basically resolves as a sort) and will be resolved on the DB server which is likely to have plenty of clout. Putting your logic in the client effectively distributes the processing, which is good, but does so to machines that are likely to be less gutsy than a server. Really you have to try out both and see what you get.

If it's purely for display purposes across a small number of rows I'm kinda inclined to say keep it in the UI layer of the client but that's driven more by separation of concerns than performance.

Since the "counter" code is in the Controller its processed on the server.