Reading Time: 4 minutes When attempting to create a foreign key constraint on a table, you may receive an error message very similar to the following: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “constraint_name”. The conflict occurred in database “DatabaseName”, table “TableName”, column ‘ColumnName’. This may be happening because you are Continue Reading
How to remove trailing/leading whitespace in SQL
Reading Time: 2 minutes How do we remove whitespace from a character string in SQL Server? It’s easy, just use the LTRIM/RTRIM string manipulation function! Take the following silly example: We have a large amount of leading whitespace in our string. To remove that whitespace, we can put this string in the LTRIM system Continue Reading
SQL Server TRY_CONVERT: Explained with Examples
Reading Time: 2 minutes The SQL Server TRY_CONVERT system function is a great alternative to the standard CONVERT function. We use CONVERT and TRY_CONVERT to change an expression from one data type to another “on the fly“. This begs the question: What’s the difference? TRY_CONVERT will return NULL if the conversion fails. Here is Continue Reading
SQL Server Nested Transactions: What’s the deal?
Reading Time: 4 minutes Here’s the deal with nested transactions: There really isn’t a concept of “nested transactions” in SQL Server. That is, an inner transaction that can operate independently of an outer transaction. Let’s work through an example using the content from an Orders table: The following code is a very simple example Continue Reading
SQL Server CHOOSE: Explained
Reading Time: 3 minutes The SQL Server CHOOSE function is a simple decision structure you can use to simplify an otherwise complex expression. The syntax for CHOOSE is extremely simple: CHOOSE(<integer>, <expression1>, <expression2>,……<expressionN>) All you do it outline an integer as the first parameter, followed by a comma-separated list of expressions. The expressions can Continue Reading
SQL Server Ternary Operator: The IIF function
Reading Time: 2 minutes Believe it or not, SQL Server has a ternary operator like many other computer languages out there. Like other ternary operators, it can shorten the syntax of a very simple decision structure to just one line of code. The SQL Server ternary operator is the IIF function. Here is the Continue Reading