In SQL Server the sp_server_info system stored procedure returns a list of attribute names and matching values for SQL Server, the database gateway, or the underlying data source. It returns a subset of the information provided by SQLGetInfo in ODBC.
Basically, it allows you to see information about SQL Server.
If you’ve encountered error message 7325 in SQL Server “Objects exposing columns with CLR types are not allowed in distributed queries” it’s probably because you’re trying to run a distributed query against a table that contains one or more columns with CLR types.
For example, you could be querying a table that uses a geography or geometry data type in one or more of its columns. These data types are implemented as .NET common language runtime (CLR) data types in SQL Server. And as the error message says, “Objects exposing columns with CLR types are not allowed in distributed queries”.
Fortunately, there’s an easy fix for this issue. And the answer is provided with the full error message.
As I write this, there are two releases of SQL Server that can be run on a Mac: SQL Server 2017 and SQL Server 2019 Preview. This article explains how to run both of these on a Mac so that they run simultaneously. No need to uninstall one before you install the other.
The key is to use a different TCP port number for each instance (the TCP port that the containers are mapped to on the host machine). If you don’t do this, you’ll get an error. SQL Server uses port 1433 by default, which is fine to use for one of your instances, but not both. Therefore you’ll need to change this for at least one of your installations.
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).
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.
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.
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.
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.
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.
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).