Computed Columns and Row Numbering

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Computed Columns and Row Numbering

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2011-11-21 : 13:59:50

I'm trying to define a computed column that will contain an auto-incremented number(Row Number).

For Ex: I have table A

ID ClaimId TableId Value RowNumber
1 1 1 334 1
2 1 1 324 2
3 2 2 22 1
4 2 2 34 2
5 2 2 56 3

So I want Row Number to be computed column as it is automatically numbered based on ClaimId and TableId...Row_Number() Over(Partition by ClaimId,TableId). So I delete any row...it should automatically number for other column.

Please help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-21 : 14:16:38

You cannot use row_number() function in the expression for a computed column. You can create a view which has the numbered column - in the view definition, you can use the row_number() function.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2011-11-21 : 14:20:57

Can you please show me how ? I would really appreciate....Thanks Sunita

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-11-21 : 14:35:18

[CODE]CREATE VIEW vMyTable
as
select row_number() over(order by SomeColumnOrColumns), *
from MyTable[/CODE]

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-21 : 14:35:34

If you have table A already defined then you write something like...

Creat View Vw_Table_A
As Select *, Row_Number() Over(Partition by ClaimID, TableID Order by ClaimID, TableID) as RowNumber
From Table_A

That should get you started.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2011-11-21 : 15:46:04

The answer is still not clear. I will need function which will give automatic rownumbering based on ClaimId and TableId and I want as a Computed Column. Please advise. I don't think view will work for Computed Column.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-21 : 15:52:54

Once you create the view as Bustaz and DP described, you will then change your queries to query the view rather than the table. Here is a simplified example of a table and a view built on top of that.

CREATE TABLE dbo.Test(id1 INT, id2 INT);
GO

CREATE VIEW dbo.MyViewOnTopOfTheTable
AS
  SELECT id1, id2, ROW_NUMBER() OVER (PARTITION BY id1 ORDER BY id2) AS RowNumber
  FROM Test;
GO

INSERT INTO dbo.Test
SELECT 1,1 UNION ALL
SELECT 1,20 UNION ALL
SELECT 1,7 UNION ALL
SELECT 2,11 UNION ALL
SELECT 2,17 UNION ALL
SELECT 2,8 UNION ALL
SELECT 5,7;
GO

SELECT * FROM dbo.MyViewOnTopOfTheTable

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-21 : 15:58:38

Your original table will not have the RowNumber column, the view will. If you want someone to see that data have them query off of the view.

The only way to do it in your original table (and a possibly dangerous way) would be to set a trigger to happen every time a row is deleted. Then you SET the RowNumber value by using the function.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2011-11-21 : 16:08:18

Thanks Sunita and DP....I would want it as a computed Column so I don't have to update Row number in my table with view. Can I do it?
When the row is deleted, I would want it to automatically figure out correct row number and put it. Thanks.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2011-11-21 : 21:23:20

Any Idea

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-21 : 21:26:33

How big is your table expected to be? Is performance a concern at all? Renumbering the existing rows anytime data is updated or deleted will be a huge performance hit if the table is sizable.

Why do you require the actual data to be stored like this? It is NOT a good design.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SCHEMA
Posting Yak Master

192 Posts

Posted - 2011-11-22 : 11:03:31

Ok...but is it possible?? Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-22 : 11:19:42

quote:
Originally posted by SCHEMA

Ok...but is it possible?? Thanks

Yes

SCHEMA
Posting Yak Master

192 Posts

Posted - 2011-11-22 : 11:28:34

How??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 03:21:22

This is the presentation issue that should be done in your front end application or using row_number() on the fly in the select statement

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources