Tag: how to

Install SQL Server 2019 on a Mac

Posted on by Ian

I previously explained how to install SQL Server on a Mac via a Docker container. When I wrote that, SQL Server 2017 was the latest version of SQL Server, and it had just been made available for Linux and Docker (which means that you can also install it on MacOS systems).

In late 2018, Microsoft announced SQL Server 2019 Preview, and subsequently announced general release in late 2019. The installation process for SQL Server 2019 is exactly the same as for SQL Server 2017. The only difference is that you need to use the container image for SQL Server 2019 instead of the 2017 image. Here I show you how to do that.

Also, if you already have SQL Server 2017 installed, and you want to install SQL Server 2019 without removing the 2017 version, you’ll need to allocate a different port number on your host. I show you how to do that too.

But first… if you’re using a Mac with the M1 chip, see How to Install SQL Server on an M1 Mac (ARM64).

Continue reading →

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

Posted on by Ian

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.

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 →

How to Find All Constraint Violations in a SQL Server Database

Posted on by Ian

You can run the DBCC CHECKCONSTRAINTS console command to return a list of all constraint violations in a SQL Server database.

This command checks the integrity of a specified constraint or all constraints on a specified table in the current database. It returns any foreign key and CHECK constraint violations that it finds.

You can use the ALL_CONSTRAINTS option to check both enabled and disabled constraints. If you omit this, then only enabled constraints are returned (unless you explicitly specify a constraint to check, in which case it will be returned regardless of whether it’s enabled or disabled).

Continue reading →

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

Posted on by Ian

If you find yourself in the situation where you need to disable a foreign key constraint in SQL Server, here’s how to do that using Transact-SQL.

This will allow you to enter data without being restricted by the foreign key. Obviously, you wouldn’t do this unless you had a very good reason to do so. Foreign keys enforce referential integrity, so disabling them has the potential to create all sorts of issues.

Continue reading →