Author: Ian

A Comparison of 6 SQL Rank Functions

Posted on by Ian

SQL rank functions can be handy things to have when analysing data. Most major RDBMSs implement a similar bunch of ranking functions, usually with the same names. These rank functions allow us to assign rankings to rows based on specific criteria.

In this article, we’ll look at six commonly used SQL ranking functions, and observe how they differ. We’ll throw them all together into a single query and see their results side by side.

The rank functions in question are: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK(), and CUME_DIST().

Continue reading →

Fix “A MERGE statement must be terminated by a semi-colon (;)” in SQL Server (Error 10713)

Posted on by Ian

If you’re getting SQL Server error 10713 that reads “A MERGE statement must be terminated by a semi-colon (;)“, it’s because you’re running a MERGE statement without terminating it with a semi-colon.

The MERGE statement requires a semi-colon at the end of it in order to correctly terminate the statement.

To fix this issue, put a semi-colon at the end of your MERGE statement.

Continue reading →

Fix “Cannot insert explicit value for identity column in table” in SQL Server (Error 544)

Posted on by Ian

If you’re getting an error that reads “An explicit value for the identity column in table ‘Dogs’ can only be specified when a column list is used and IDENTITY_INSERT is ON” in SQL Server, it appears that you’re trying to insert a value into an IDENTITY column.

Specifically, you’re trying to do that while the IDENTITY_INSERT option is set to OFF.

To fix this issue, either enable IDENTITY_INSERT before inserting the value, or omit the value from your list of values to insert (and let the IDENTITY column do it’s thing).

Continue reading →

4 Ways to List All Indexes in a SQL Server Database

Posted on by Ian

By default, SQL Server creates indexes automatically when we do things like create certain constraints. We also have the option of creating indexes separately for our own (usually performance related) reasons. Either way, there may be times where we need to check what indexes we have in our database.

In this article, we’ll explore four ways to retrieve information about all indexes in a SQL Server database.

Continue reading →

Fix Error 1909 “Cannot use duplicate column names in index” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 1909 that reads something like “Cannot use duplicate column names in index. Column name ‘FirstName’ listed more than once“, it appears that you’re trying to create an index, but you’re listing the same column multiple times.

Each column can only be listed once in the index definition.

To fix this issue, make sure each column is listed only once.

Continue reading →

What Does “Schema-Bound” Mean in SQL Server?

Posted on by Ian

In SQL Server, “schema-bound” refers to a specific feature that ensures a SQL object, such as a view or function, is tightly linked to the schema of the underlying tables or other database objects it references. When a view or function is schema-bound, the underlying tables or objects cannot be altered in a way that would break the view or function’s dependencies.

Continue reading →

3 Ways to Concatenate Strings in PostgreSQL

Posted on by Ian

When working with databases (and software in general), string concatenation is the operation of joining character strings end-to-end. For example if we have two words, we can combine them into one.

PostgreSQL provides us with multiple ways to concatenate strings. Below are two functions and one operator that we can use to concatenate strings in PostgreSQL.

Continue reading →