Row index and Row number

Question

Row index and Row number

hazeenahazu

SSC Veteran

Points: 227
More actions
January 12, 2015 at 9:56 pm

#302379

Is there any difference between Row index and row number in sql?

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no such thing as a 'row index' in SQL. Did you mean an identity column? Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 2

I've seen the output of ROW_NUMBER() referred to as row index (incorrectly), but other than that, I'm with Gail. I don't understand the question.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hazeenahazu SSC Veteran Points: 227 More actions · Answer 3

I thought row number and row index are different concepts .. That's why I ask this question .. Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Well, they are. In the sense that Row Number exists and Row Index doesn't.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hazeenahazu SSC Veteran Points: 227 More actions · Answer 5

hazeenahazu

SSC Veteran

Points: 227

January 13, 2015 at 5:30 am

#1769769

okay.. Thank you ..:-)

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 6

Unless we're looking at some of kind of terminology mix up. Maybe you meant RID, or Row Identifier, which is a marker placed on rows in a heap table. Just guessing to try to help.

What did you think each of these things did?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hazeenahazu SSC Veteran Points: 227 More actions · Answer 7

I got some idea about row number and row Id as :

row id is the unique index number of every row of table maintaining by database automatically and

row number is the sequential number of rows in the resultset object.

But thought rowIndex is another concept ..

My question was about that concept . If its not exists , its cleared ...:-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

hazeenahazu (1/13/2015)
I got some idea about row number and row Id as :
row id is the unique index number of every row of table maintaining by database automatically and
row number is the sequential number of rows in the resultset object.

Neither of those is true.

SQL doesn't keep a unique number for each row of each table. You, as the developer, can set a column to be an identity which means it's auto-incremented for new rows. It's still not unique.

Rows in a resultset don't get a sequential number automatically. You can use something like Row_Number as a column in the resultset with a sequential number based on the grouping and ordering specified in the function.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hazeenahazu SSC Veteran Points: 227 More actions · Answer 9

"You, as the developer, can set a column to be an identity which means it's auto-incremented for new rows. It's still not unique. "

- Did you mean the primary key as an identity column ?

like,

CREATE TABLE dbo.Tmp_Names

(

Id int NOT NULL PRIMARY KEY IDENTITY(1, 1),

Name varchar(50) NULL

)

Then why its not unique?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

That one is unique, because it's a PRIMARY KEY. It's the PRIMARY KEY which forces the column to be unique, not the identity. Identity just provides incrementing values.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hazeenahazu SSC Veteran Points: 227 More actions · Answer 11

I am a little confused about those things ..:-(

Then how does we create this identity column ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

What?

Your example above created a column with identity, so why are you asking how to create a column with identity?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Row index and Row number

Cookies on SQLServerCentral