Dropping a primary key in any RDBMS is a critical operation that requires careful consideration, especially when there are related foreign keys in other tables.
The decision to drop a primary key is not to be taken lightly.
That said, this article walks through the process of how to drop a primary key in SQL Server, while highlighting important considerations.
Continue readingIf you’re getting an error that reads something like “No column name was specified for column 2 of ‘SalaryTotals’” in SQL Server, it could be that you’re running a subquery or CTE, but you’re not providing a column name for an aggregate function, or for the XML or JSON result of the CTE.
To fix this issue, use an alias to define a column name for all aggregate functions and XML/JSON outputs in the subquery or CTE. Or if it’s a CTE, you can specify the column name using the CTE syntax.
Continue readingIf you’re getting an error that reads “Invalid column name ‘$action’” in SQL Server, it could be that you’re using the $action argument of the OUTPUT clause for an operation that doesn’t support this argument.
Or it could be that the operation does support the argument, but you’re referencing it with the wrong syntax.
The $action argument can only be used with MERGE operations, and it must be done correctly.
To fix this issue, be sure to only use the $action argument in MERGE operations and to reference it with the correct syntax.
The MERGE statement in SQL Server allows us to perform INSERT, UPDATE, and DELETE operations in a single query. This makes it an efficient way to synchronize two tables, typically between a source and a target, based on a defined condition. Rather than running separate queries to handle insertions, updates, and deletions, we can combine all of these operations into one statement; the MERGE statement.
If you’re getting SQL Server error 2715, which reads something like “Column, parameter, or variable #1: Cannot find data type SERIAL“, it appears that you’re trying to define a column to have a data type that’s not supported in SQL Server.
To fix this issue, be sure to use a supported data type for all columns.
Continue readingSQL 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().
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.
When we run an UPDATE statement in SQL, we might not always be interested in which rows or values were updated. But there may be times when we need to examine this data, or log it somewhere like in a separate table.
This is where SQL Server’s OUTPUT clause can help tremendously.
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).
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