Which of the following ranking functions distribute the rows in an ordered partition into a specified number of groups

Which of the following ranking functions distribute the rows in an ordered partition into a specified number of groups?
A. ROW_NUMBER
B. RANK
C. DENSE_RANK
D. NTILE
E. None of the Above

Ans :- D

Explanation:-
The NTILE ranking function distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

The ROW_NUMBER ranking function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

The RANK ranking function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. Just like the DENSE_RANK, in the RANK ranking function, if two or more rows tie for a rank, each tied rows receive the same rank. Unlike the DENSE_RANK, the RANK ranking function does not always return consecutive integers.

The DENSE_RANK ranking function returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. If two or more rows tie for a rank in the same partition, each tied rows receive the same rank.

Comments

Post a Comment

Popular posts from this blog

Google to FTS Syntax Cheat Sheet

Image
OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut
Read more

What is difference between DELETE and TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command. TRUNCATE : TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE cannot be rolled back. TRUNCATE is DDL Command. TRUNCATE Resets identity of the table DELETE : DELETE removes rows one at a time...
Read more

@@trancount

@@trancount is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless you have used the fairly exotic SAVE TRANSACTION ), and forces @@trancount to 0, regards of the previous value. When you exit a stored procedure, if @@trancount does not have the same value as it had when the procedure commenced execution, SQL Server raises error 266. This error is not raised, though, if the procedure is called from a trigger, directly or indirectly. Neither is it raised if you are running with SET IMPLICIT TRANSACTIONS ON .
Read more