March 30, 2010 at 12:37 am
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
March 31, 2010 at 2:46 am
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
March 31, 2010 at 3:10 am
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
March 31, 2010 at 3:12 am
with (nolock) just stocks the query locking the table. this is essential if you are querying a live database!! 🙂
March 31, 2010 at 3:20 am
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
March 31, 2010 at 3:29 am
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?
March 31, 2010 at 5:35 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 6:19 am
I see what your saying and it makes sense.... I will proceed with the reading!! thanks
March 31, 2010 at 6:53 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 6:54 am
thanks very much; Im sure there will be something 🙂
September 28, 2010 at 11:29 am
I was searching for this, Once you saw the value of RID it clears you lof of things regarding RID
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply