How to Include Results that Tie for Last Place when Using the TOP Clause in SQL Server

by

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.

Read more

Pagination in SQL Server using OFFSET/FETCH

by

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.

Read more

Return All Foreign Keys & CHECK Constraints in a SQL Server Database (T-SQL Examples)

by

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.

Read more

Modify a CHECK Constraint in SQL Server using T-SQL

by

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:

  1. Drop the constraint using ALTER TABLE with DROP CONSTRAINT.
  2. Create the new constraint using ALTER TABLE with ADD CONSTRAINT.

Read more

How to Restore Trust in a Foreign Key Constraint in SQL Server (T-SQL Examples)

by

In SQL Server, a foreign key constraint (and a CHECK constraint) can be either trusted or not trusted.

When a constraint is trusted, this means that the constraint has been verified by the system. When it’s not trusted, the constraint has not been verified by the system.

Basically, when you have an untrusted constraint, you could also have invalid data in your database. By this I mean you could have data that violates the constraint.

This means that you’re no longer maintaining referential integrity within your relationships, which is not normally good practice when looking after a relational database in production.

In this article I’ll check my existing constraints for their “trustworthiness”, and then I’ll update them to become trustworthy once again.

Read more