Tag: t-sql

RPAD() Alternative: Applying Right Padding in SQL Server

Posted on by Ian

The SQL rpad() function has been widely implemented across many major RDBMSs, including MySQL, Oracle, PostgreSQL, and MariaDB, to name just a few. But when it comes to SQL Server, we have a problem. SQL Server doesn’t currently provide us with an rpad() function.

But that’s not to say we can’t apply right padding in SQL Server. SQL Server still provides us with enough tools to get the job done. With a bit of work, we can get a similar result to what we might be able to achieve with rpad(). It may not be as elegant as a simple rpad() function, but at least it’s an option.

Continue reading →

How to Effectively “Back Up” All Deleted Rows When Using DELETE in SQL Server

Posted on by Ian

Deleting rows in a SQL database can sometimes be a nerve-racking experience. What if you’re deleting the wrong rows? Or what if the business later tells you they want their data back?

Fortunately SQL Server provides us with an easy way to essentially “back up” any rows affected by a DELETE operation to a table.

This article looks at using the OUTPUT ... INTO clause to save a copy of deleted rows to another table.

Continue reading →

Fix “Violation of PRIMARY KEY constraint” in SQL Server (Error 2627)

Posted on by Ian

If you’re getting an error that reads something like “Violation of PRIMARY KEY constraint ‘PK_CatId’. Cannot insert duplicate key in object ‘dbo.Cats’. The duplicate key value is (1)” in SQL Server, it’s because you’re trying to insert a duplicate value into a primary key column.

A primary key cannot contain duplicate values.

To fix this issue, you’ll need to change the value you’re trying to insert into the primary key column.

Continue reading →

Get the IDENTITY Values that were Generated by an INSERT Statement in SQL Server

Posted on by Ian

The good thing about having IDENTITY columns in SQL Server is that they automatically generate a unique value for every row that’s inserted into a table. This saves us from having to insert our own unique values, and I’m not going to complain about that.

But obvious question you might ask; What if I need to know the value that was generated for each column I just inserted?

Fortunately, the solution may be easier than you think!

Continue reading →

Fix Error 137 “Must declare the scalar variable” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 137 that goes something like “Must declare the scalar variable…“, it’s probably because you’re referring to a variable that hasn’t been declared.

If the variable has been declared, it’s possible you’re referring to it incorrectly in the code.

When we use a variable in SQL Server, we must declare the variable first.

To fix this issue, declare the variable. Also be sure to use the right syntax when using it.

Continue reading →

How to Capture All Rows Deleted by a DELETE Statement in SQL Server

Posted on by Ian

Any time you prepare to delete data from a database, you will probably wonder whether or not to log the deleted data somewhere… just in case.

One way to do this is to construct a SELECT statement to identify the rows you want to delete, and then use it to copy those rows into a separate log table, before doing the actual delete. If you’re using SQL Server, another option is to use the OUTPUT clause. This article discusses the later.

Continue reading →

Identify a Table’s Primary Key’s Index in SQL Server

Posted on by Ian

There are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key’s index.

For example, we may want to identify the index’s name if we need to disable the primary key for some reason (we disable primary keys by disabling their index).

The query below can help if you need to find the name of a primary key’s index for a given table.

Continue reading →

Fix “Syntax ‘*’ is not allowed in schema-bound objects” in SQL Server (Error 1054)

Posted on by Ian

If you’re getting an error that reads “Syntax ‘*’ is not allowed in schema-bound objects” in SQL Server, it appears that you’re using the asterisk wildcard (*) to select all columns when trying to define a schema-bound object (like a schema-bound view).

As the error message states, this is not allowed.

To fix this issue, explicitly name each column.

Continue reading →

How to Disable a Constraint in SQL Server

Posted on by Ian

Disabling constraints in SQL Server can be useful for various operations, such as bulk data loading or certain maintenance tasks. But not all constraints are created equal. The method we use to disable a constraint, and whether that’s even possible, depends on the type of constraint.

In this article, we’ll explore how to disable different types of constraints and consider the potential impacts.

Continue reading →