Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.
ROW_NUMBER () OVER ([] )
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
RANK () OVER ([] )
Returns the rank of each row within the partition of a result set.
DENSE_RANK () OVER ([] )
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
NTILE (integer_expression) OVER ([] )
Distributes the rows in an ordered partition into a specified number of groups.
What is NOLOCK?
Using the NOLOCK query optimizer hint is generally considered good practice inorder to improve concurrency on a busy system.When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which meansthat another process could be updating the data at the exact time you are reading it. Therare no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) lockThis means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the readshave completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay (blocking).
|
No responses found. Be the first to respond and make money from revenue sharing program.
|