Reading Time: 7 minutesThe SQL Server “Key Lookup” operation is something you may see when looking at the execution plan of a query. Maybe you’re trying to optimize the query to run a bit faster and noticing that a lookup operation is taking a LARGE amount of the query processing time. Or maybe Continue Reading
Don’t make this OUTER JOIN mistake!
Reading Time: 4 minutesThe OUTER JOIN operations, such as LEFT JOIN and RIGHT JOIN, are very important to know when it comes to querying Microsoft SQL Server databases. There is one important factoid you should know if you are using an OUTER JOIN in combination with the IS NULL predicate. Let’s create a Continue Reading
Why can’t I add a foreign key constraint? ANSWERED!
Reading Time: 4 minutesWhen 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 minutesHow 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 minutesThe 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 minutesHere’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 minutesThe 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 minutesBelieve 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
Dynamic SQL: Explained for Beginners
Reading Time: 6 minutesThere may be times when you need to write a query and you need to make certain parts of that query dynamic, meaning the query might not be exactly the same every time you run it. SQL Server has a great tool we can use to create these dynamic queries. Continue Reading
The TOP 10 SQL Server String Functions You Should Know!
Reading Time: 8 minutesString manipulation is something you will likely find yourself doing at some point in your career as a database professional. Luckily, SQL Server has many great system functions we can use to make the task of string searching and manipulation very easy. In this week’s tutorial, we’ll cover the TOP Continue Reading