Is it possible to make an Expression Column in a view to show row number?

Question

I have an Id column in this view, but it jumps from 40,000 to 7,000,000. I don't want my crazy stored procedure to loop untill it reaches 7,000,000 so i was wondering if i could create a column that was the row number. It would be an expression of some sort, but I don't know how to make it. Please assist! Thank you in advance.

Answer 1

Relational tables have no row numbers. You can project a row number into a result by using the built in ROW_NUMBER() OVER (ORDER BY ...) funtion.

Your procedure has many, many problems. It uses loop @counter as lookup key (!!!). It assumes key stability between iteration (ie. assumes @counter+1 is the next key, ignoring any concurent insert/delete). It assumes stability inside the loop (no transactions, no locking to ensure the validity of EXISTS).

What you're tryign to do is try to emulate a keyset driven cursor. Just use a keyset cursor.

Answer 2

You really should do what you're doing with updates, not loops. But if you insist...

declare @ID int
declare @LastID int
select @LastID = 0

while (1 = 1)
  begin
    select @ID = min(Id)
    from [vCategoryClaimsData]
    where Id > @LastID

    -- if no ID found then we've reached the end of the table
    if @ID is null break

    -- look up the data for @ID
    SELECT @claim_Number = dbo.[vCategoryClaimsData].[Claim No],     
    ...
    where Id = @LastID


    -- do your processing here
    ...


    -- set @LastID to the ID you just processed 
    select @LastID = @ID

  end

Make sure the Id column is indexed. This will allow skipping the non-sequential Id values.

That being said, it looks like the processing you're doing could be handled with update statements. That would be much more efficient, and eliminate many of the problems others have brought up.

Answer 3

If you can, try and re-write the stored procedure to use sets versus row based processing.

To do what you need, you'll use the ROW_NUMBER function. To do this, I've provided some sample code below.

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.IDRownumbersView') IS NOT NULL
    DROP VIEW5 dbo.IDRownumbersView

IF OBJECT_ID('tempdb.dbo.IDRownumbersTable') IS NOT NULL
    DROP TABLE dbo.IDRownumbersTable

CREATE TABLE dbo.IDRownumbersTable
    (
    RowID int PRIMARY KEY CLUSTERED
    ,CharValue varchar(5)
    ,DateValue datetime 
    )

INSERT INTO IDRownumbersTable VALUES (10, 'A', GETDATE())
INSERT INTO IDRownumbersTable VALUES (20, 'B', GETDATE())
INSERT INTO IDRownumbersTable VALUES (30, 'C', GETDATE())
INSERT INTO IDRownumbersTable VALUES (40, 'D', GETDATE())
INSERT INTO IDRownumbersTable VALUES (50, 'E', GETDATE())
INSERT INTO IDRownumbersTable VALUES (100, 'F', GETDATE())
INSERT INTO IDRownumbersTable VALUES (110, 'G', GETDATE())
INSERT INTO IDRownumbersTable VALUES (120, 'H', GETDATE())
GO 

CREATE VIEW dbo.IDRownumbersView

AS

SELECT ROW_NUMBER() OVER (ORDER BY RowID ASC) AS RowNumber
    ,RowID
    ,CharValue
    ,DateValue
FROM dbo.IDRownumbersTable
GO

SELECT * FROM dbo.IDRownumbersView