Pagination in SQL Server using OFFSET/FETCH

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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.

Continue reading →

Modify a CHECK Constraint in SQL Server using T-SQL

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

If you have a foreign key constraint in SQL Server that is currently disabled, you can use the code below to re-enable it.

When you enable a foreign key constraint, you have the option to specify whether or not to check any existing data in the table. This also applies when you enable a CHECK constraint.

Below are code examples of enabling a foreign key constraint, while specifying each of these different options.

Continue reading →