In SQL Server, the RANK() 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.
Category: DBMS
Database Management Systems
How DENSE_RANK() Works in SQL Server
In SQL Server, the DENSE_RANK() 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 distinct ranks that come before the row.
This function is similar to RANK(), but without the gaps in the ranking values that can occur with RANK() when ties exist in the result set.
How to Include Results that Tie for Last Place when Using the TOP Clause in SQL Server
When using the TOP clause in a query in SQL Server, you may encounter occasions where two or more results tie for last place. You probably wouldn’t even know when this happens, because the default behavior of TOP is to return no more than the number of rows you specify.
The TOP clause accepts a WITH TIES argument that allows you to specify whether or not to include all results that tie for last place. Rows can tie for last place due to their ORDER BY column containing the same value. Using this argument may therefore result in more rows being returned than you actually specified.
Pagination in SQL Server using OFFSET/FETCH
Pagination is often used in applications where the user can click Previous/Next to navigate the pages that make up the results, or click on a page number to go directly to a specific page.
When running queries in SQL Server, you can paginate the results by using the OFFSET and FETCH arguments of the ORDER BY clause. These arguments were introduced in SQL Server 2012, therefore you can use this technique if you have SQL Server 2012 or higher.
In this context, pagination is where you divide the query results into smaller chunks, each chunk continuing where the previous finished. For example, if a query returns 1000 rows, you could paginate them so that they’re returned in groups of 100. An application can pass the page number and page size to SQL Server, and SQL Server can then use it to return just the data for the requested page.
Return a Percentage of a Result Set in SQL Server
In SQL Server, you can use the TOP clause to limit the rows returned from a query to a certain percentage of the result set.
For example, you could return the top 10% of the results, or whatever percentage you need.
Limit the Rows Returned in a SQL Server Query by using the TOP Clause
In SQL Server, you can use the TOP clause to limit the rows returned from a query result set. This clause provides similar functionality to LIMIT in MySQL, and ROWNUM in Oracle, although there are differences in how each of these work.
Below are examples of using the TOP clause to limit the result set in SQL Server.
Return All Foreign Keys & CHECK Constraints in a SQL Server Database (T-SQL Examples)
In SQL Server, you can use Transact-SQL to return a list of all foreign keys and CHECK constraints for the current database.
The examples on this page query two system views in order to retrieve this information: sys.foreign_keys and sys.check_constraints. You can query each one separately, or use UNION to display them all in a single result set.
Modify a CHECK Constraint in SQL Server using T-SQL
If you already have an existing CHECK constraint in SQL Server, but you need to modify it, you’ll need to drop it and recreate it. There’s no ALTER CONSTRAINT statement or anything similar.
So to “modify” an existing constraint:
- Drop the constraint using
ALTER TABLEwithDROP CONSTRAINT. - Create the new constraint using
ALTER TABLEwithADD CONSTRAINT.
Add a CHECK Constraint to an Existing Table in SQL Server (T-SQL)
This article demonstrates how to add a CHECK constraint to an existing table.
You can add a constraint to an existing table by using the ALTER TABLE statement along with the ADD CONSTRAINT argument. Examples below.
Rename a CHECK Constraint in SQL Server using T-SQL
You can use the sp_rename system stored procedure to rename a CHECK constraint in SQL Server.
The purpose of this stored procedure is to allow you to rename user-created objects in the current database. So you can also use it to rename other objects such as tables, columns, alias data types, etc.