Tag: what is

How SHOWPLAN_XML Works in SQL Server

Posted on by Ian

In SQL Server, you can use the SET SHOWPLAN_XML statement to return detailed information about how a T-SQL statement will be executed, in the form of a well-defined XML document.

It’s similar to SHOWPLAN_ALL, except that SHOWPLAN_ALL returns its data set of rows that form a hierarchical tree.

You can set SHOWPLAN_XML to either ON or OFF.

When SHOWPLAN_XML is ON, all subsequent T-SQL statements are not executed. Instead SQL Server returns execution information for the statement (without executing it).

Continue reading →

3 Ways to use ALL in SQL Server

Posted on by Ian

You might be familiar with the ALL option in SQL Server. Perhaps you’ve used it along with the UNION operator to include any duplicates that might be returned in the result set.

But did you know that ALL can also be used in two other contexts?

ALL can be used in the following three contexts:

  • As an argument to the SELECT clause.
  • As an argument to the UNION clause.
  • As a logical operator when comparing a scalar value with a single-column set of values.

Examples of each of these contexts follows.

Continue reading →

SQL Server ALL Operator Explained

Posted on by Ian

In SQL Server, the ALL operator can be used with a subquery to compare a scalar value with a single-column set of values returned by the subquery.

It’s also true that the SELECT clause and UNION operator both accept an ALL argument, although this usage has a different purpose (allows duplicates in the result set).

Below are examples of using the ALL operator with a subquery.

Continue reading →

What is a Comparison Operator?

Posted on by Ian

Comparison operators are an important part of most programming languages.

Comparison operators are used to compare two expressions. The result is either true or false. It could also be unknown. This could also be represented by either 1, 0, or NULL, depending on the language. These are typically known as “Boolean expressions”.

When used with databases, comparison operators can be used inside your SQL queries to filter data to a certain criteria.

Continue reading →

How IIF() Works in SQL Server

Posted on by Ian

In SQL Server, the IIF() function (not to be confused with the IF statement) is a conditional function that returns the second or third argument based on the evaluation of the first argument.

It’s a shorthand way for writing a CASE expression. It’s logically equivalent to CASE WHEN X THEN Y ELSE Z END assuming IIF(X, Y, Z).

IIF() is an abbreviation for Immediate IF.

Continue reading →

How the IF Statement Works in SQL Server

Posted on by Ian

Most (probably all) programming languages include some form of the IF statement that allows programmers to write conditional code. That is, code that will execute only if a certain condition is true.

It’s a very simple concept. Basically it goes like this:

“If this, do that.”

Most languages simply call it IF, but some have their own twist on the name (for example, in ColdFusion/CFML, it’s called CFIF).

In any case, they essentially do the same thing.

In SQL Server (or more precisely, its programming language T-SQL) it’s called IF.

Continue reading →