How to get rowid in sql server 2005

Question

Post reply

How to get rowid in sql server 2005

Viewing 11 posts - 31 through 40 (of 40 total)

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

sandeepkcitore Valued Member Points: 59 More actions · Answer 1

use CTE's Below is the syntax

With CTE As

(

Select *,Row_Number() Over(Partition By Col1,Col2 Order by Col1) From TableName

)

Select * From CTE

tom.newman 88696 Old Hand Points: 319 More actions · Answer 2

If you want to get the rowid for the use in a paging query then use row_count()

declare @FromRow int

declare @ToRow int

set @FromRow = (@PageNo-1) * @pageSize

set @ToRow = @FromRow + @pageSize;

set @Fromrow = @FromRow + 1

with <name> as

(

select <field1>, <fieldn>, row_number() Over (order by <field> desc) as 'RowNumber'

from table with (nolock)

where <expression>

)

select * from <name> where RowNumber between @FromRow and @ToRow

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

tom.newman 88696 (3/31/2010)
from table with (nolock)

Why?

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

tom.newman 88696 Old Hand Points: 319 More actions · Answer 4

with (nolock) just stocks the query locking the table. this is essential if you are querying a live database!! 🙂

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

So locks are bad, but potentially inconsistent data and incorrect results are OK?

Nolock is not a free lunch, it is not a 'required' hint when querying a production database. It's a hint that can be useful in some circumstances when you know that the side effects of it won't be a problem.

See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

It is not 'required when querying a live database'. Well written queries and effective indexing are. If you have queries causing severe locking, one of those is probably missing.

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

tom.newman 88696 Old Hand Points: 319 More actions · Answer 6

Thanks for that; as you can tell Im a developer not a DBA! trying to get more familiar with the aspects of querying SQL databases. at the moment reading T-SQL Querying!! back to basics which should give me a good foundation.

Are there any advantages of using pivot or apply compared to a join? joins create virtual tables which is a combination of both tables, then filtered down. As far as I can tell you are applying tables values to the left or right table.

Are there any performance advantages to using the new SQL features?

Paul White SSC Guru Points: 150468 More actions · Answer 7

tom.newman 88696 (3/31/2010)
...joins create virtual tables which is a combination of both tables, then filtered down.

Only logically. Can you imagine how slow queries against large tables would be if execution always started with a full cartesian join before being filtered? The query optimization process selects a close-to-optimal strategy to return results logically described by the query. The availability of appropriate indexes and up to date statistics is important here. All should become clearer as you progress through your reading material 🙂

Are there any performance advantages to using the new SQL features? Are there any advantages of using pivot or apply compared to a join?

It all depends.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

tom.newman 88696 Old Hand Points: 319 More actions · Answer 8

I see what your saying and it makes sense.... I will proceed with the reading!! thanks

Paul White SSC Guru Points: 150468 More actions · Answer 9

tom.newman 88696 (3/31/2010)
I see what your saying and it makes sense.... I will proceed with the reading!! thanks

No worries. If you come across anything that puzzles you, post something in a new thread - we are all very keen to share our opinions on things 😉

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

tom.newman 88696 Old Hand Points: 319 More actions · Answer 10

thanks very much; Im sure there will be something 🙂

Krishnaraj9 SSC Eights! Points: 959 More actions · Answer 11

http://sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-New-(undocumented)-physical-row-locator-function.aspx

I was searching for this, Once you saw the value of RID it clears you lof of things regarding RID