{"id":1854,"date":"2014-08-13T00:00:00","date_gmt":"2014-08-13T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/questions-about-t-sql-expressions-you-were-too-shy-to-ask\/"},"modified":"2026-05-14T08:30:19","modified_gmt":"2026-05-14T08:30:19","slug":"questions-about-t-sql-expressions-you-were-too-shy-to-ask","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/questions-about-t-sql-expressions-you-were-too-shy-to-ask\/","title":{"rendered":"Questions about T-SQL Expressions You Were Too Shy to Ask"},"content":{"rendered":"\n
\n

Expressions in T-SQL. Have any questions you’ve been too shy to ask? Robert Sheldon is here to help…<\/h4>\n

Part of the ‘Too Shy to Ask’ series on Simple Talk – click here for more<\/a>.<\/em><\/p>\n

The Questions<\/h2>\n
    \n
  1. What exactly is a T-SQL expression<\/a><\/li>\n
  2. I want to display outputted numbers as currency, with commas and dollar signs. Is that possible in T-SQL?<\/a><\/li>\n
  3. I see the plus sign (+) used in a variety of ways in T-SQL script, but I can’t always make sense of how SQL Server arrives at the results it does. Is there a “right way” to use plus signs?<\/a><\/li>\n
  4. The NULLIF expression makes no sense. I would think you’d use it to somehow ferret out a NULL value or to test a condition for a NULL value. But it appears to merely return a NULL if two values are equal. Is that all it’s doing?<\/a><\/li>\n
  5. Can you use expressions in data manipulation language (DML) statements?<\/a><\/li>\n
  6. The CASE expression allows me to leave out the ELSE clause. Is that important to include it?<\/a><\/li>\n
  7. Can you shed light on the order in which the database engine processes the components in an expression?<\/a><\/li>\n
  8. I get confused on how to treat constants in my expressions. Is there a trick in using them?<\/a><\/li>\n
  9. Are there any advantages to using a COALESCE expression rather than a CASE expression when checking for the first non-NULL value in a list of values?<\/a><\/li>\n
  10. I’m working on a query that uses the NOT IN operator in the WHERE clause. The operator checks values in a list returned by a subquery. When the returned list includes a NULL value, the query returns an empty resultset, even though I know I should be seeing results. Any idea what might be happening?<\/a><\/li>\n
  11. What the heck is a ‘modulo’?<\/a><\/li>\n
  12. Are all WHEN and ELSE clauses in a CASE expression evaluated if the first WHEN clause evaluates to TRUE?<\/a><\/li>\n
  13. What’s the difference between the two ‘not equal to’ operators (<> and !=)?<\/a><\/li>\n
  14. I’ve come across operators such as += in variable assignment SET statements. What do they mean?<\/a><\/li>\n<\/ol>\n

    What exactly is a T-SQL expression?”<\/h3>\n
    \n

    An expression is a block of code in a T-SQL statement that is processed as a unit in order to return a scalar (single) data value. Each expression is made up of one or more types of components, including constants, columns, variables, operators, scalar functions, and scalar subqueries. SQL Server supports the use of expressions within different types of T-SQL statements and within various parts of those statements. For example, you can define expressions in the SELECT<\/code>, WHERE<\/code>, GROUP<\/code> BY<\/code>, and ORDER<\/code> BY<\/code> clauses of a SELECT<\/code> statement.<\/p>\n

    The following T-SQL code contains a variety of expressions throughout the SET<\/code> and SELECT<\/code> statements:<\/p>\n

    \t\t\tDECLARE @date DATETIME;\n\t\t\tSET @date = GETDATE()\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 Name + ':' + SUBSTRING(ProductNumber, 4, 4) AS ProdName,\n\t\t\t\u00a0 ReorderPoint,\n\t\t\t\u00a0 CONVERT(INT, ROUND(ReorderPoint * 1.1, 0)) AS NewPoint,\n\t\t\t\u00a0 SafetyStockLevel - ReorderPoint AS PointDiff,\n\t\t\t\u00a0 CASE\n\t\t\t\u00a0\u00a0\u00a0 WHEN Color IS NULL THEN 'N\/A' \n\t\t\t\u00a0\u00a0\u00a0 ELSE Color\n\t\t\t\u00a0 END AS ProdColor,\n\t\t\t\u00a0 DATEDIFF(mm, SellStartDate, @date) AS MonthDiff\n\t\t\tFROM Production.Product\n\t\t\tWHERE ProductID BETWEEN 316 AND 320;\n\t\t\t\u00a0\n\t\t<\/pre>\n
    The first expression shows up in the SET<\/code> statement, after the equal sign. It is simply the GETDATE()<\/code> function. An expression is often made up of only a single element, such as a column, constant, or in this case, function.<\/p>\n
    Next we move to the SELECT<\/code> statement’s select list. The first expression we find defines the ProdName<\/code> column. The expression concatenates the Name<\/code> column with the last four digits of the ProductNumber<\/code> column. The expression includes two concatenation operators (+<\/code>), one literal string value (:<\/code>), and the  SUBSTRING<\/code> scalar function, which extracts the last four digits from the ProductName<\/code> column.<\/p>\n
    The next expression in the select list is the ReorderPoint<\/code> column. The expression and name of the column are one and the same. This is followed by an expression that defines the  NewPoint<\/code> column. The expression multiplies the ReorderPoint<\/code> value by  1.1<\/code>, uses the ROUND<\/code> function to round the result to a whole number, and then uses the CONVERT()<\/code> function to convert the value to an integer.<\/p>\n
    Next we have an expression that defines the PointDiff<\/code> column. In this case, the expression merely subtracts the ReorderPoint<\/code> value from the SafetyStockLevel<\/code> value, using the subtract operator to calculate the difference.<\/p>\n
    Our next expression defines the ProdColor<\/code> column. Here we use a CASE<\/code> expression to provide a default value should a NULL<\/code> be returned.<\/p>\n
    The final expression in the select list defines the MonthDiff<\/code> column. The expression uses the DATEDIFF<\/code> function to calculate the difference, in months, between the SellStartDate<\/code> value and the @date<\/code> variable value.<\/p>\n
    The next expression is in the search condition of the WHERE<\/code> clause. The expression compares the ProductID<\/code> value to the range of values defined by the BETWEEN<\/code> operator, the AND<\/code> operator, and the literal values  316<\/code> and 320<\/code>. You can use various operators to link expression elements together in this way, creating one large expression that is synthesized into a single unit that returns a scalar value. Note that the search condition can also be considered to be three expressions linked together by the BETWEEN<\/code> and the AND<\/code> operators: one column and two numerical constants.<\/p>\n
    Because of the expressions specified throughout the T-SQL code, our SELECT<\/code> statement returns the following results:<\/p>\n\n\n\n\n\n\n\n\n
    \n
    ProdName<\/b><\/p>\n<\/td>\n
    		\n
    ReorderPoint<\/b><\/p>\n<\/td>\n
    		\n
    NewPoint<\/b><\/p>\n<\/td>\n
    		\n
    PointDiff<\/b><\/p>\n<\/td>\n
    		\n
    ProdColor<\/b><\/p>\n<\/td>\n
    		\n
    MonthDiff<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Blade:2036<\/p>\n<\/td>\n
    		\n
    600<\/p>\n<\/td>\n
    		\n
    660<\/p>\n<\/td>\n
    		\n
    200<\/p>\n<\/td>\n
    		\n
    N\/A<\/p>\n<\/td>\n
    		\n
    145<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Crankarm:5965<\/p>\n<\/td>\n
    		\n
    375<\/p>\n<\/td>\n
    		\n
    413<\/p>\n<\/td>\n
    		\n
    125<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n
    		\n
    145<\/p>\n<\/td>\n<\/tr>\n
    \n
    ML Crankarm:6738<\/p>\n<\/td>\n
    		\n
    375<\/p>\n<\/td>\n
    		\n
    413<\/p>\n<\/td>\n
    		\n
    125<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n
    		\n
    145<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Crankarm:7457<\/p>\n<\/td>\n
    		\n
    375<\/p>\n<\/td>\n
    		\n
    413<\/p>\n<\/td>\n
    		\n
    125<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n
    		\n
    145<\/p>\n<\/td>\n<\/tr>\n
    \n
    Chainring Bolts:2903<\/p>\n<\/td>\n
    		\n
    750<\/p>\n<\/td>\n
    		\n
    825<\/p>\n<\/td>\n
    		\n
    250<\/p>\n<\/td>\n
    		\n
    Silver<\/p>\n<\/td>\n
    		\n
    145<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0Clearly, expressions play an important role in a T-SQL statement. They can be as simple the ones shown here or far more complex.<\/p>\n
    What we’ve not covered, however, is the FROM<\/code> clause. In this case, the FROM<\/code> clause includes only the schema and table name. In some cases, the FROM<\/code> clause might include a table expression,<\/i> such as a simple table-valued variable or an operand in an APPLY<\/code> clause. However, those types of expressions are usually specifically referred to as table expressions<\/i>. When the term expression<\/i> is used by itself, it’s normally referring to a scalar expression, such as those highlighted in the example above.<\/p>\n
    To know when and where you can use expressions, you should refer to the statement syntax in SQL Server Books Online. Not only will this show you where you can create expressions, but also whether there are any limitations on the expressions you create.<\/p>\n<\/div>\n
    “I want to display outputted numbers as currency, with commas and dollar signs. Is that possible in T-SQL?”<\/h3>\n
    \n
    Yes, it’s possible, but before we go into how that is done, be aware that such operations are generally best done by the application at the presentation level, rather than at the database level. Still, there are times when you are required to convert data into its representational form. So let’s look at how to format your currency.<\/p>\n
    Prior to SQL Server 2012, to get data into a currency format (at least for currencies with a structure similar to the US dollar), you could cast the value to the MONEY<\/code> type, if necessary, and then use the CONVERT<\/code> function to convert the value to a character type, as shown in the following example:<\/p>\n
    DECLARE \n\t\t\t\u00a0 @var1 NVARCHAR(20) = '555555555.5555',\n\t\t\t\u00a0 @var2 DECIMAL(20,4) = 555555555.5555,\n\t\t\t\u00a0 @var3 MONEY = 555555555.5555;\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 '<\/pre>\n
    Each expression in the SELECT<\/code> list references one of the variables as its source data and is made up of multiple components. What provides the final format, however, is the CONVERT<\/code> function. The function requires that the source data be either the MONEY<\/code> or SMALLMONEY<\/code> type. When you call the function, you specify a second argument along with the source value. That argument determines the format of the data. In this case, we specify 1<\/code> so the outputted data includes commas every three digits to the left of the decimal point. We also concatenate the value with a dollar sign to give it that final touch, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    amount1<\/b><\/p>\n<\/td>\n
    		\n
    amount2<\/b><\/p>\n<\/td>\n
    		\n
    amount3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    $555,555,555.56<\/p>\n<\/td>\n
    		\n
    $555,555,555.56<\/p>\n<\/td>\n
    		\n
    $555,555,555.56<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0Overall, this isn’t too painful a process, unless you’re working with currencies that follow a different format, such as the Swiss franc, which takes apostrophes rather than commas. In such cases, you might have to add yet another function, such as REPLACE<\/code>, to substitute the commas with apostrophes.<\/p>\n
    Fortunately, since the release of SQL Server 2012, you’ve been able to instead use the FORMAT<\/code> function to output your numbers to a currency format. The FORMAT<\/code> function returns a value in a specified format, based on a specified culture. To demonstrate how this works, let’s first recast our preceding expressions into ones that use the FORMAT<\/code> function to configure the data as US dollar amounts:<\/p>\n
    \t\t\tDECLARE \n\t\t\t\u00a0 @var1 NVARCHAR(20) = '555555555.5555',\n\t\t\t\u00a0 @var2 DECIMAL(20,4) = 555555555.5555,\n\t\t\t\u00a0 @var3 MONEY = 555555555.5555;\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 FORMAT(CAST(@var1 AS MONEY), 'c', 'en-us') AS amount1,\n\t\t\t\u00a0 FORMAT(@var2, 'c', 'en-us') AS amount2,\n\t\t\t\u00a0 FORMAT(@var3, 'c', 'en-us') AS amount3;\n\t\t\t\u00a0\n\t\t<\/pre>\n
    Notice that we must first cast the character data (@var1<\/code>) to the MONEY<\/code> data type, but we can use the DECIMAL<\/code> variable (@var2<\/code>) as is. The second FORMAT<\/code> argument (c<\/code>) specifies that we’re formatting currency, and the third argument specifies the US symbol (en-us<\/code>) for the cultural context. The SELECT<\/code> statement returns the same results as the previous SELECT<\/code> statement, with all values returned as the NVARCHAR<\/code> type.<\/p>\n
    However, with the FORMAT<\/code> function, we can specify other cultures. For example, the following example uses the German symbol (de-de<\/code>) to provide the cultural context:<\/p>\n
    \t\t\tDECLARE \n\t\t\t\u00a0 @var1 NVARCHAR(20) = '555555555.5555',\n\t\t\t\u00a0 @var2 DECIMAL(20,4) = 555555555.5555,\n\t\t\t\u00a0 @var3 MONEY = 555555555.5555;\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 FORMAT(CAST(@var1 AS MONEY), 'c', 'de-de') AS amount1,\n\t\t\t\u00a0 FORMAT(@var2, 'c', 'de-de') AS amount2,\n\t\t\t\u00a0 FORMAT(@var3, 'c', 'de-de') AS amount3;\n\t\t<\/pre>\n
    Now our results are specific to how the euro is represented in Germany, as shown in the following table:<\/p>\n\n\n\n\n
    \n
    amount1<\/b><\/p>\n<\/td>\n
    		\n
    amount2<\/b><\/p>\n<\/td>\n
    		\n
    amount3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>\n
    		\n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>\n
    		\n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0Certainly, the FORMAT<\/code> function is a handy tool if you want your results to be displayed in a specific currency. But just to reiterate, such formatting is generally handled by the calling application, with the source data retrieved in its raw state.<\/p>\n<\/div>\n
    “I see the plus sign (+) used in a variety of ways in T-SQL script, but I can’t always make sense of how SQL Server arrives at the results it does. Is there a “right way” to use plus signs?”<\/h3>\n
    \n
    SQL Server is a tricky devil. As you’ve discovered, plus signs have all sorts of meaning in T-SQL. You can use them to concatenate string values, add numerical values together, or indicate that a numerical value is a positive number, rather than negative. The following SELECT<\/code> statement demonstrates how to both concatenate and add values:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 FirstName + ' ' + LastName AS FullName,\n\t\t\t\u00a0 FORMAT(SalesLastYear, 'c', 'en-us') AS SalesLastYear,\n\t\t\t\u00a0 FORMAT(SalesYTD, 'c', 'en-us') AS SalesYTD,\n\t\t\t\u00a0 FORMAT(SalesLastYear + SalesYTD, 'c', 'en-us') AS TwoYearSales\n\t\t\tFROM Sales.vSalesPerson\n\t\t\tWHERE TerritoryName IS NOT NULL;\n\t\t<\/pre>\n
    For the FullName<\/code> column, we’ve used the plus sign to concatenate the FirstName<\/code> and  LastName<\/code> columns with a space in between. For the TwoYearSales<\/code> column, we’ve used the plus sign to add the SalesLastYear<\/code> and  SalesYTD<\/code> columns together. In both cases, the database engine knows when to concatenate the values or when to add them together, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    SalesLastYear<\/b><\/p>\n<\/td>\n
    		\n
    SalesYTD<\/b><\/p>\n<\/td>\n
    		\n
    TwoYearSales<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Blythe<\/p>\n<\/td>\n
    		\n
    $1,750,406.48<\/p>\n<\/td>\n
    		\n
    $3,763,178.18<\/p>\n<\/td>\n
    		\n
    $5,513,584.66<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Mitchell<\/p>\n<\/td>\n
    		\n
    $1,439,156.03<\/p>\n<\/td>\n
    		\n
    $4,251,368.55<\/p>\n<\/td>\n
    		\n
    $5,690,524.58<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    $1,997,186.20<\/p>\n<\/td>\n
    		\n
    $3,189,418.37<\/p>\n<\/td>\n
    		\n
    $5,186,604.57<\/p>\n<\/td>\n<\/tr>\n
    \n
    Garrett Vargas<\/p>\n<\/td>\n
    		\n
    $1,620,276.90<\/p>\n<\/td>\n
    		\n
    $1,453,719.47<\/p>\n<\/td>\n
    		\n
    $3,073,996.36<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi Reiter<\/p>\n<\/td>\n
    		\n
    $1,849,640.94<\/p>\n<\/td>\n
    		\n
    $2,315,185.61<\/p>\n<\/td>\n
    		\n
    $4,164,826.55<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    $1,927,059.18<\/p>\n<\/td>\n
    		\n
    $1,352,577.13<\/p>\n<\/td>\n
    		\n
    $3,279,636.31<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu Ito<\/p>\n<\/td>\n
    		\n
    $2,073,506.00<\/p>\n<\/td>\n
    		\n
    $2,458,535.62<\/p>\n<\/td>\n
    		\n
    $4,532,041.62<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jos\u00e9 Saraiva<\/p>\n<\/td>\n
    		\n
    $2,038,234.65<\/p>\n<\/td>\n
    		\n
    $2,604,540.72<\/p>\n<\/td>\n
    		\n
    $4,642,775.37<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Campbell<\/p>\n<\/td>\n
    		\n
    $1,371,635.32<\/p>\n<\/td>\n
    		\n
    $1,573,012.94<\/p>\n<\/td>\n
    		\n
    $2,944,648.25<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete Mensa-Annan<\/p>\n<\/td>\n
    		\n
    $0.00<\/p>\n<\/td>\n
    		\n
    $1,576,562.20<\/p>\n<\/td>\n
    		\n
    $1,576,562.20<\/p>\n<\/td>\n<\/tr>\n
    \n
    Lynn Tsoflias<\/p>\n<\/td>\n
    		\n
    $2,278,548.98<\/p>\n<\/td>\n
    		\n
    $1,421,810.92<\/p>\n<\/td>\n
    		\n
    $3,700,359.90<\/p>\n<\/td>\n<\/tr>\n
    \n
    Rachel Valdez<\/p>\n<\/td>\n
    		\n
    $1,307,949.79<\/p>\n<\/td>\n
    		\n
    $1,827,066.71<\/p>\n<\/td>\n
    		\n
    $3,135,016.50<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jae Pak<\/p>\n<\/td>\n
    		\n
    $1,635,823.40<\/p>\n<\/td>\n
    		\n
    $4,116,871.23<\/p>\n<\/td>\n
    		\n
    $5,752,694.62<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ranjit Varkey Chudukatil<\/p>\n<\/td>\n
    		\n
    $2,396,539.76<\/p>\n<\/td>\n
    		\n
    $3,121,616.32<\/p>\n<\/td>\n
    		\n
    $5,518,156.08<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    There’s no real mystery here. When we use the plus sign with only character data, the database engine concatenates the values. In this case, the plus sign is considered a string concatenation operator. When we use the plus sign with numerical values, the database engine adds the values together. Under these circumstances, the plus sign is considered an addition operator.<\/p>\n
    Not surprisingly, when we mix and match the types of data, our results are less predictable. Take a look at the following example:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 NVARCHAR(10) = 'TestVar',\n\t\t\t\u00a0 @var2 CHAR(2) = 'CX',\n\t\t\t\u00a0 @var3 SMALLINT = 300,\n\t\t\t\u00a0 @var4 DECIMAL(10,4) = 1000.0001,\n\t\t\t\u00a0 @var5 BIT = 1,\n\t\t\t\u00a0 @var6 MONEY = 999.99,\n\t\t\t\u00a0 @var7 DATETIME = '2014-07-25 00:00:00.000';\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 + @var2 AS var1var2, \n\t\t\t\u00a0 @var3 + @var6 AS var3var6,\n\t\t\t\u00a0 @var4 + @var5 AS var4var5,\n\t\t\t\u00a0 @var7 + @var3 AS var7var3,\n\t\t\t\u00a0 @var6 + @var7 AS var6var7;\n\t\t<\/pre>\n
    As you can see, we’ve declared seven variables, each defined with a different type. We then use the plus sign to combine the variables in different ways, giving us the following results:<\/p>\n\n\n\n\n
    \n
    var1var2<\/b><\/p>\n<\/td>\n
    		\n
    var3var6<\/b><\/p>\n<\/td>\n
    		\n
    var4var5<\/b><\/p>\n<\/td>\n
    		\n
    var7var3<\/b><\/p>\n<\/td>\n
    		\n
    var6var7<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    TestVarCX<\/p>\n<\/td>\n
    		\n
    1299.99<\/p>\n<\/td>\n
    		\n
    1001.0001<\/p>\n<\/td>\n
    		\n
    2015-05-21 00:00:00.000<\/p>\n<\/td>\n
    		\n
    2017-04-19 23:45:36.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As we would expect, when we pair @var1<\/code> with @var2<\/code>, both character types, we end up with a concatenated string. When we pair @var3<\/code> to @var6<\/code>, both numerical types, the values are added together, even though @var3<\/code> and @var6<\/code> are different types.<\/p>\n
    Now we get to our third pairing: @var4<\/code> +<\/code> @var5<\/code>. This time around, we’re adding a DECIMAL<\/code> value to BIT<\/code> value. However, the database engine still treats both variables as numerical values and adds them together, even though SQL Server documentation suggests that you can’t do this with the BIT<\/code> type. (And really, why would you?)<\/p>\n
    The next item in the select list, @var7<\/code> +<\/code> @var3<\/code>, pairs a DATETIME<\/code> value with a SMALLINT<\/code> value. This time, we get a date that is 300 days later than the original date. When your expression includes the plus sign, the database engine defaults to the data type with the highest precedence, in this case,  DATETIME<\/code>. Based on the type, the engine determines whether to use the plus sign to concatenate values or add them together. If the data type with the highest precedence is a numeric data type, the engine attempts to add the values. In this case, the engine adds the SMALLINT<\/code> value to the DATETIME<\/code> value as the specified number of days. (A DATETIME<\/code> value is actually stored as two integers.)<\/p>\n
    Finally, we pair a MONEY<\/code> date type with the DATETIME<\/code> date type (@var6<\/code> +<\/code> @var7<\/code>). Once again, DATETIME<\/code> takes precedence over MONEY<\/code>, so the database engine adds 999.99 days to our original date to come up with a date in 2017.<\/p>\n
    We can verify how data type precedence works with plus signs by using the SQL_VARIANT_PROPERTY<\/code> function to retrieve the data type of the expressions that pair the different variables:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 NVARCHAR(10) = 'TestVar',\n\t\t\t\u00a0 @var2 CHAR(2) = 'CX',\n\t\t\t\u00a0 @var3 SMALLINT = 300,\n\t\t\t\u00a0 @var4 DECIMAL(10,4) = 1000.0001,\n\t\t\t\u00a0 @var5 BIT = 1,\n\t\t\t\u00a0 @var6 MONEY = 999.99,\n\t\t\t\u00a0 @var7 DATETIME = '2014-07-25 00:00:00.000';\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 SQL_VARIANT_PROPERTY(@var1 + @var2, 'basetype') AS var1var2, \n\t\t\t\u00a0 SQL_VARIANT_PROPERTY(@var3 + @var6, 'basetype') AS var3var6,\n\t\t\t\u00a0 SQL_VARIANT_PROPERTY(@var4 + @var5, 'basetype') AS var4var5,\n\t\t\t\u00a0 SQL_VARIANT_PROPERTY(@var7 + @var3, 'basetype') AS var7var3,\n\t\t\t\u00a0 SQL_VARIANT_PROPERTY(@var6 + @var7, 'basetype') AS var6var7;\n\t\t<\/pre>\n
    The SELECT<\/code> statement returns the returned data type for each expression, as shown in the following table:<\/p>\n\n\n\n\n
    \n
    var1var2<\/b><\/p>\n<\/td>\n
    		\n
    var3var6<\/b><\/p>\n<\/td>\n
    		\n
    var4var5<\/b><\/p>\n<\/td>\n
    		\n
    var7var3<\/b><\/p>\n<\/td>\n
    		\n
    var6var7<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    nvarchar<\/p>\n<\/td>\n
    		\n
    money<\/p>\n<\/td>\n
    		\n
    decimal<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0In each case, the expression returns the type with the highest precedence and determines how the plus sign will be used.<\/p>\n
    So far in our examples, the database engine has been able to add or concatenate values without a problem because the engine could implicitly convert the data type with the lowest precedence to the type with the highest. But this isn’t always the case:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 NVARCHAR(10) = 'TestVar',\n\t\t\t\u00a0 @var3 SMALLINT = 300;\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 + @var3 AS var1var3;\n\t\t<\/pre>\n
    This time around, we’re using the plus sign to pair a NVARCHAR<\/code> value with a SMALLINT<\/code> value. Because SMALLINT<\/code> has precedence over NVARCHAR<\/code>, the database engine attempts to convert the value TestVar<\/code> to the SMALLINT<\/code> type, which of course is not possible. Consequently, the database engine returns the following error message:<\/p>\n
    \t\t\tMsg 245, Level 16, State 1, Line 5\n\t\t\tConversion failed when converting the nvarchar value 'TestVar' to data type smallint.\n\t\t<\/pre>\n
    The only way we can pair a numerical value with a string value is to concatenate them. In order to do so, we must explicitly convert the SMALLINT<\/code> value to a character data type:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 NVARCHAR(10) = 'TestVar',\n\t\t\t\u00a0 @var2 SMALLINT = 300;\n\t\t\t\u00a0\n\t\t\tSELECT @var1 + CAST(@var2 AS NVARCHAR(10));\n\t\t<\/pre>\n
    Now our SELECT<\/code> statement returns the value TestVar300<\/code>.<\/p>\n
    There’s one other use of the plus sign that you should be aware of-as a unary plus operator. SQL Server supports two types of unary operators, plus and minus, which are used to designate whether a numeric value is positive or negative:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 DECIMAL(10,2) = +123.45,\n\t\t\t\u00a0 @var2 DECIMAL(10,2) = -123.45;\n\t\t\t\u00a0\n\t\t\tSELECT @var1 AS var1, @var2 AS var2;\n\t\t<\/pre>\n
    All we’ve done here is to designate our two literal values as positive and negative numbers. The SELECT<\/code> statement returns the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    123.45<\/p>\n<\/td>\n
    		\n
    -123.45<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0Be aware, however, that you cannot use the positive unary operator to convert a negative number to a positive number. For that, you need to use the ABS<\/code> mathematical function.<\/p>\n<\/div>\n
    “The NULLIF expression makes no sense. I would think you’d use it to somehow ferret out a NULL value or to test a condition for a NULL value. But it appears to merely return a NULL if two values are equal. Is that all it’s doing?”<\/h3>\n
    \n
    That’s part of what it’s doing. When a NULLIF<\/code> expression compares two values, it returns the first value if the two values are not equal. If they are equal, the expression returns a NULL<\/code> value of the same data type as the first specified value. Here’s what a NULLIF<\/code> expression looks like in action:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 VARCHAR(20) = 'valueA',\n\t\t\t\u00a0 @var2 VARCHAR(20) = 'valueB';\n\t\t\t\u00a0\n\t\t\tSELECT NULLIF(@var1, @var2);\n\t\t<\/pre>\n
    In this case, the NULLIF<\/code> expression returns valueA<\/code> because the two values are not equal. If they were equal, the returned value would be NULL<\/code>. In addition, the returned value would be NULL<\/code> if the first value were NULL<\/code>.<\/p>\n
    When you know how a NULLIF<\/code> expression works, it doesn’t seem so bad, although its use still has a tendency to cause confusion. Luckily, we can achieve the same results by using a CASE<\/code> statement:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 VARCHAR(20) = 'valueA',\n\t\t\t\u00a0 @var2 VARCHAR(20) = 'valueB';\n\t\t\t\u00a0\n\t\t\tSELECT \n\t\t\t\u00a0 CASE\n\t\t\t\u00a0\u00a0\u00a0 WHEN @var1 = @var2 THEN NULL\n\t\t\t\u00a0\u00a0\u00a0 ELSE @var1\n\t\t\t\u00a0 END;\n\t\t<\/pre>\n
    The advantage of the CASE<\/code> statement is that it’s a lot easier to understand. Sure, NULLIF<\/code> provides simpler syntax, but if it creates confusion, what’s the point?<\/p>\n<\/div>\n
    “Can you use expressions in data manipulation language (DML) statements?”<\/h3>\n
    \n
    You can use expressions wherever T-SQL syntax permits them, including DML statements. Take a look at part of the syntax for the UPDATE<\/code> statement’s SET<\/code> clause:<\/p>\n
    SET { column_name = { expression | DEFAULT | NULL }<\/pre>\n
    \u00a0According to SQL Server Books Online, the expression placeholder can be a “variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value.” This pretty much defines the term expression<\/i> as we understand it. Note, however, that any expression you define must conform to the rules associated with the particular T-SQL statement in which the expression is defined. For example, an UPDATE<\/code> statement’s UPDATE<\/code> clause can include the TOP<\/code> clause, as shown in the following syntax:<\/p>\n
    UPDATE [ TOP ( expression ) [ PERCENT ] ]<\/pre>\n
    \u00a0In this case, expression “specifies the number or percent of rows that will be updated.” In other words, you cannot specify a non-numerical string value as your expression, as you can with other types of expressions. That said, it’s still considered an expression.<\/p>\n
    So, yes, you can use an expression in a DML statement. In fact, they’re used all the time. Let’s look at an example. Suppose we create the following table in our database and insert a row of data:<\/p>\n
    \t\t\tIF OBJECT_ID('Inventory', 'U') IS NOT NULL\n\t\t\tDROP TABLE Inventory;\n\t\t\tGO\n\t\t\t\u00a0\n\t\t\tCREATE TABLE Inventory\n\t\t\t(\n\t\t\t\u00a0 ProductID INT NOT NULL IDENTITY PRIMARY KEY,\n\t\t\t\u00a0 InStock INT NOT NULL,\n\t\t\t\u00a0 OnOrder INT NOT NULL\n\t\t\t);\n\t\t\tGO\n\t\t\t\u00a0\n\t\t\tINSERT INTO Inventory(InStock, OnOrder)\n\t\t\tVALUES(20, 50);\n\t\t<\/pre>\n
    Now let’s use an UPDATE<\/code> statement to modify that row of data:<\/p>\n
    \t\t\tDECLARE \n\t\t\t\u00a0 @AmtReceived INT = 47;\n\t\t\t\u00a0\n\t\t\tUPDATE Inventory\n\t\t\tSET\n\t\t\t\u00a0 InStock = InStock + @AmtReceived,\n\t\t\t\u00a0 OnOrder = OnOrder - @AmtReceived\n\t\t\tWHERE ProductID = 1;\n\t\t\t\u00a0\n\t\t\tSELECT * FROM Inventory\n\t\t\tWHERE ProductID = 1;\n\t\t<\/pre>\n
    Our SET<\/code> clause is updating two columns. Each SET<\/code> definition includes an expression that either adds the @AmtReceived<\/code> value to the target column or subtracts that value. If we run the SELECT<\/code> statement after the table has been updated, we come up with the following results:<\/p>\n\n\n\n\n
    \n
    ProductID<\/b><\/p>\n<\/td>\n
    		\n
    InStock<\/b><\/p>\n<\/td>\n
    		\n
    OnOrder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    67<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you’re writing T-SQL statements, you’re writing expressions, even if you don’t realize it. That’s why it’s important to refer back to the statement’s syntax whenever you’re uncertain how to define a particular element. Expressions are supported throughout most statements. The syntax for the UPDATE<\/code> statement, for example, includes the expression placeholder in 10 different places, which suggests that many of us might not be using some statements to their fullest capacity.<\/p>\n<\/div>\n
    \u00a0“The CASE expression allows me to leave out the ELSE clause. Is it that important to include it?”<\/h3>\n
    \n
    Yes, but…. The ELSE<\/code> clause specifies the value to return if no other comparison operations (WHEN...THEN<\/code>) evaluate to true. If you omit the ELSE<\/code> clause, the database engine instead returns a NULL<\/code> value. A NULL<\/code> value might be fine, if that’s what you want. But what if it’s not? Take a look at the following example, which uses a CASE<\/code> expression to determine how to return an employee’s full name:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 BusinessEntityID AS EmployeeID,\n\t\t\t\u00a0 CASE\n\t\t\t\u00a0\u00a0\u00a0 WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName\n\t\t\t\u00a0\u00a0\u00a0 WHEN LEN(MiddleName) = 1 THEN FirstName + ' ' + MiddleName + '. ' + LastName\n\t\t\t\u00a0\u00a0\u00a0 ELSE FirstName + ' ' + MiddleName + ' ' + LastName\n\t\t\t\u00a0 END AS FullName,\n\t\t\t\u00a0 TerritoryName AS Territory\n\t\t\tFROM Sales.vSalesPerson\n\t\t\tWHERE TerritoryName IN ('central', 'northeast', 'southeast');\n\t\t<\/pre>\n
    If the MiddleName<\/code> column is NULL<\/code>, that column is not included in the expression. Only the first and last names are concatenated. If the MiddleName<\/code> value is an initial, a period is added to the initial and concatenated with the first and last names. Otherwise, all three names are concatenated, as shown in the following results:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    Tsvi Michael Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now let’s modify the CASE<\/code> expression by removing the ELSE<\/code> clause:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 BusinessEntityID AS EmployeeID,\n\t\t\t\u00a0 CASE\n\t\t\t\u00a0\u00a0\u00a0 WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName\n\t\t\t\u00a0\u00a0\u00a0 WHEN LEN(MiddleName) = 1 THEN FirstName + ' ' + MiddleName + '. ' + LastName\n\t\t\t\u00a0 END AS FullName,\n\t\t\t\u00a0 TerritoryName AS Territory\n\t\t\tFROM Sales.vSalesPerson\n\t\t\tWHERE TerritoryName IN ('central', 'northeast', 'southeast');\n\t\t<\/pre>\n
    This time a NULL<\/code> is returned if the MiddleName<\/code> column has a value that is more than an initial, as the following table shows:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly, you would not want to delete the ELSE<\/code> clause in this instance. You could, however, add a WHEN...THEN<\/code> condition that replaces the ELSE<\/code> clause, in which case, you’re still returning NULL<\/code> of none of the conditions are met, which might be fine under some circumstances. Consider the following example:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 FirstName + ' ' + LastName AS FullName,\n\t\t\t\u00a0 CASE\n\t\t\t\u00a0\u00a0\u00a0 WHEN SalesQuota > 250000 THEN 'true'\n\t\t\t\u00a0\u00a0\u00a0 WHEN SalesQuota <= 250000 THEN 'false'\n\t\t\t\u00a0 END AS HigherQuota\n\t\t\tFROM Sales.vSalesPerson\n\t\t\tWHERE CountryRegionName = 'United States';\n\t\t<\/pre>\n
    If the SalesQuota<\/code> value is great than 250,000, a true<\/code> is returned. If the value is less than or equal to 250,000, we get a false<\/code>. However, if the column is NULL<\/code>, neither condition can be true, so the CASE<\/code> expression returns a NULL<\/code>, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    HigherQuota<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Stephen Jiang<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Blythe<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Mitchell<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi Reiter<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu Ito<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Campbell<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete Mensa-Annan<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Syed Abbas<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Amy Alberts<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    You can choose to include an ELSE<\/code> clause and then provide a default value, such as N\/A<\/code> (or even NULL<\/code>), whether or not you think it’s necessary. In fact, many developers think you should never omit the ELSE<\/code> clause, even if you believe it is impossible for one of the WHEN...THEN<\/code> conditions not to be met. Someone might make a change to the database that affects the statement or your thinking about possible outcomes might not be correct.<\/p>\n<\/div>\n
    “Can you shed light on the order in which the database engine processes the components in an expression?”<\/h3>\n
    \n
    The way in which an expression’s elements are processed depends on several factors: their listed order, whether any subset of elements is enclosed in parentheses, and what operators are used to connect the various elements. Parentheses group elements together to ensure that those elements are processed as a unit before being incorporated into the rest of the expression. In addition, the expression as a whole adheres to the concept of operator precedence,<\/i> in which the operator types determine the order in which the expression is evaluated. For example, the multiply and division arithmetic operators take precedence over the greater than (><\/code>) and lesser than (<<\/code>) comparison operations, which take precedence over logical operators such as AND<\/code> or ANY<\/code>. (You can find details about operator precedence in the MSDN topic “Operator Precedence<\/a>.”)<\/p>\n
    Let’s start with a few simple arithmetic-based expressions to give you a sense of how this works. The following example declares several INT<\/code> variables, assigns an expression to each one, and returns the values from all three:<\/p>\n
    \t\t\tDECLARE @num1 INT = 3 * 2 + 5 - 7;\n\t\t\tDECLARE @num2 INT = 3 * (2 + 5) - 7;\n\t\t\tDECLARE @num3 INT = 3 * (2 + 5 - 7);\n\t\t\t\u00a0\n\t\t\tSELECT \n\t\t\t\u00a0 @num1 AS num1,\n\t\t\t\u00a0 @num2 AS num2,\n\t\t\t\u00a0 @num3 AS num3;\n\t\t<\/pre>\n
    All we’re doing in each expression is multiplying and adding and subtracting values. However, because we use parentheses in the second and third expressions, our results come up quite different:<\/p>\n\n\n\n\n
    \n
    num1<\/b><\/p>\n<\/td>\n
    		\n
    num2<\/b><\/p>\n<\/td>\n
    		\n
    num3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The @num1<\/code> expression is fairly straightforward. We multiple 3 by 2, add 5, and subtract 7, which gives us a total of 4.<\/p>\n
    However, in the @num2<\/code> expression, we enclose the 2<\/code> +<\/code> 5<\/code> elements in parentheses, so they’re processed first, giving us a total of 7. The 7 is then multiplied by the first 3, giving us 21. From there, we subtract 7, giving us a total of 14.<\/p>\n
    In the @num3<\/code> expression, the parentheses enclose the 2<\/code> +<\/code> 5<\/code> -<\/code> 7<\/code> elements, which are processed before the other elements. Because this returns 0, 3 is multiplied by 0, giving us a final result of 0.<\/p>\n
    Now let’s look at another example, which again declares three variables and applies different expressions to them:<\/p>\n
    \t\t\tDECLARE @SalesLastYear INT = 2500;\n\t\t\tDECLARE @SalesYTD INT = 1500;\n\t\t\tDECLARE @SalesQuota INT = 3000;\n\t\t\t\u00a0\n\t\t\tSELECT \n\t\t\t\u00a0 @SalesQuota * 2 - @SalesLastYear + @SalesYTD * 2 AS SalesDiff1,\n\t\t\t\u00a0 (@SalesQuota * 2) - (@SalesLastYear + (@SalesYTD * 2)) AS SalesDiff2;\n\t\t<\/pre>\n
    Here’s what are results look like:<\/p>\n\n\n\n\n
    \n
    SalesDiff1<\/b><\/p>\n<\/td>\n
    		\n
    SalesDiff2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    6500<\/p>\n<\/td>\n
    		\n
    500<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0The first expression includes no parentheses. However, the multiply operator takes precedence over the addition and subtraction operators, so the multiplication is performed first. That means we first multiply the SalesQuota<\/code> value by 2 and the SalesYTD<\/code> value by 2, giving us the expression 6000<\/code> -<\/code> 2500<\/code> +<\/code> 3000<\/code>, which equals 6500.<\/p>\n
    The second expression does use parentheses. The first set of parentheses returns a value of 6000. The second set of parentheses actually contains a third set, and those are the elements evaluated first, before the outer set of elements. After they’re evaluated, that part of the expression enclosed in the second set of parentheses becomes @SalesLastYear<\/code>  +<\/code> 3000<\/code>, giving us a total of 5500. That 5500 is then subtracted from the 6000, which is how we end up with 500.<\/p>\n
    Now let’s look at an expression that incorporates character data as well as numerical data. In the following SELECT<\/code> statement, our WHERE<\/code> clause includes both the OR<\/code> and the AND<\/code> logical operators:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 Name AS ProductName,\n\t\t\t\u00a0 StandardCost,\n\t\t\t\u00a0 ListPrice,\n\t\t\t\u00a0 ProductLine\n\t\t\tFROM Production.Product\n\t\t\tWHERE ListPrice > StandardCost * 2.5\n\t\t\t\u00a0 AND ProductLine = 'r' OR ProductLine = 't';\n\t\t<\/pre>\n
    The WHERE<\/code> clause search condition contains three predicates separated by the AND<\/code> operator and the OR<\/code> operator. In its current state, the search condition states that the ListPrice<\/code> value must be greater than 2.5 times the StandardCost<\/code> value and<\/i> the ProductLine<\/code> value must equal r<\/code> or<\/i> the ProductLine<\/code> value must equal t<\/code>. According to the rules of operator precedence, the database engine first evaluates the AND<\/code> logical operator and then the OR<\/code> logical operator. Because of the placement of the operators, the first two predicates must both evaluate to true or<\/i> the third predicate must evaluate to true. In other words, all returned rows must meet the first two conditions or meet the third condition. As a result, the statement returns 59 rows that qualify. The following table shows part of the results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    StandardCost<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n
    		\n
    ProductLine<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Front Wheel<\/p>\n<\/td>\n
    		\n
    96.7964<\/p>\n<\/td>\n
    		\n
    218.01<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Rear Wheel<\/p>\n<\/td>\n
    		\n
    108.7844<\/p>\n<\/td>\n
    		\n
    245.01<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-Panniers, Large<\/p>\n<\/td>\n
    		\n
    51.5625<\/p>\n<\/td>\n
    		\n
    125.00<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bottle Cage<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, M<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, L<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 60<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Touring Frame – Yellow, 62<\/p>\n<\/td>\n
    		\n
    199.8519<\/p>\n<\/td>\n
    		\n
    333.42<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 46<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 50<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 54<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 46<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 50<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 54<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Suppose what we were really after was to return rows that met the first condition and<\/i> either the second or<\/i> third condition. In this case, we would have to modify our search condition slightly:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 Name AS ProductName,\n\t\t\t\u00a0 StandardCost,\n\t\t\t\u00a0 ListPrice,\n\t\t\t\u00a0 ProductLine\n\t\t\tFROM Production.Product\n\t\t\tWHERE ListPrice > StandardCost * 2.5\n\t\t\t\u00a0 AND (ProductLine = 'r' OR ProductLine = 't');\n\t\t<\/pre>\n
    Notice that the second and third predicates are now enclosed in parentheses. Consequently, the database engine will first evaluate them as a unit. That means for all rows returned, ListPrice<\/code> must be greater than 2.5 times the value of StandardCost<\/code> and<\/i> ProductLine<\/code> must equal r<\/code> or<\/i> t<\/code>. The following table shows all the results now returned by the query.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    StandardCost<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n
    		\n
    ProductLine<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bottle Cage<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, M<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, L<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Tire Tube<\/p>\n<\/td>\n
    		\n
    1.4923<\/p>\n<\/td>\n
    		\n
    3.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Tire Tube<\/p>\n<\/td>\n
    		\n
    1.8663<\/p>\n<\/td>\n
    		\n
    4.99<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Tire<\/p>\n<\/td>\n
    		\n
    8.0373<\/p>\n<\/td>\n
    		\n
    21.49<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    ML Road Tire<\/p>\n<\/td>\n
    		\n
    9.3463<\/p>\n<\/td>\n
    		\n
    24.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Tire<\/p>\n<\/td>\n
    		\n
    12.1924<\/p>\n<\/td>\n
    		\n
    32.60<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Tire<\/p>\n<\/td>\n
    		\n
    10.8423<\/p>\n<\/td>\n
    		\n
    28.99<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0Notice that we now have only nine rows and that each row meets both conditions for the ListPrice<\/code> and ProductLine<\/code> columns. By applying the parentheses, we have better controlled how the rules of operator precedence are applied.<\/p>\n<\/div>\n
    “I get confused on how to treat constants in my expressions. Is there a trick in using them?”<\/h3>\n
    \n
    A constant is a literal value that is either part of an expression or is the entire expression. The way in which you treat constants depends on the type of data. As a general rule, you enclose character data in single quotes, but leave numerical data without quotes, although it’s not quite as straightforward as this. Before we go into the oddities, though, let’s look at a few examples of character constants. The following T-SQL declares several character variables and then retrieves their values:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 VARCHAR(20) = 'some value',\n\t\t\t\u00a0 @var2 VARCHAR(20) = '',\n\t\t\t\u00a0 @var3 NVARCHAR(20) = N'some value + 1',\n\t\t\t\u00a0 @var4 NVARCHAR(20) = N'12345';\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 AS var1,\n\t\t\t\u00a0 @var2 AS var2,\n\t\t\t\u00a0 @var3 AS var3,\n\t\t\t\u00a0 @var4 AS var4;\n\t\t<\/pre>\n
    By default, when you’re working with character string constants, you enclose them in singe quotes. If you’re working with Unicode strings, you precede the opening quote with an uppercase N. Anything enclosed in the quotes is considered part of that string, even if the characters are numbers, operators, or other types of symbols, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    some value<\/p>\n<\/td>\n
    		\u00a0<\/td>\n\n
    some value + 1<\/p>\n<\/td>\n
    		\n
    12345<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0In SQL Server, we can instead enclose character strings in double quotes by setting the QUOTED_IDENTIFIER<\/code> option to  OFF<\/code>, as shown in the following example:<\/p>\n
    \t\t\tSET QUOTED_IDENTIFIER OFF;\n\t\t\tGO\n\t\t\t\u00a0\n\t\t\tDECLARE\n\t\t\t\u00a0 @var1 VARCHAR(20) = \"its value\",\n\t\t\t\u00a0 @var2 VARCHAR(20) = \"it's a value\",\n\t\t\t\u00a0 @var3 VARCHAR(20) = 'it''s a value';\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 AS var1,\n\t\t\t\u00a0 @var2 AS var2,\n\t\t\t\u00a0 @var3 AS var3;\n\t\t\t\u00a0\n\t\t\tSET QUOTED_IDENTIFIER ON;\n\t\t\tGO\n\t\t<\/pre>\n
    When the QUOTED_IDENTIFIER<\/code> option is set to OFF<\/code>, we can use double quotes just like single quotes, as is the case with the @var1<\/code> declaration. The advantage of using double quotes is that you can pass in a special character, such as an apostrophe, without having to escape it by adding a second apostrophe, as we had to do for @var3<\/code>. The SELECT<\/code> statement now returns the results shown in the following table:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    Var3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    its value<\/p>\n<\/td>\n
    		\n
    it’s a value<\/p>\n<\/td>\n
    		\n
    it’s a value<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Be aware, however, that the QUOTED_IDENTIFIER<\/code> option has other implications besides being able to use double quotes for string constants. For example, the option affects the way identifiers are handled in T-SQL. In addition, the SQL Server Native Client Provider and ODBC driver set this option to ON<\/code>, which can impact your code if you had set the option to OFF<\/code>. For these reasons you’re usually better off leaving the option set to ON<\/code>.<\/p>\n
    As previously mentioned, you typically don’t enclose numerical constants in quotes, even if they include currency symbols or scientific notation. For example, the following T-SQL declares four types of numerical variables, sets their values, and retrieves those values:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 INT = 147,\n\t\t\t\u00a0 @var2 DECIMAL(10, 4) = 10.58,\n\t\t\t\u00a0 @var3 MONEY = $68.24,\n\t\t\t\u00a0 @var4 FLOAT = 87.9e;\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 AS var1,\n\t\t\t\u00a0 @var2 AS var2,\n\t\t\t\u00a0 @var3 AS var3,\n\t\t\t\u00a0 @var4 AS var4;\n\t\t<\/pre>\n
    If we had enclosed the constants in quotes, the database engine would have interpreted the values as character data and have tried to convert it to the type of the applicable variable. For the first three variables, an implicit conversion would have worked fine, but it would mean unnecessary work on the part of the database engine. For the last variable, the engine would have returned the following error:<\/p>\n
    \t\t\tMsg 8114, Level 16, State 5, Line 2\n\t\t\tError converting data type varchar to float.\n\t\t<\/pre>\n
    When we specify a numerical constant without quotes, the database engine assumes a numerical type consistent with the value. For example, the value 147<\/code> is assumed to be an INT<\/code>, so no implicit conversion is necessary. The following table shows the results returned by the  SELECT<\/code> statement.<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    147<\/p>\n<\/td>\n
    		\n
    10.5800<\/p>\n<\/td>\n
    		\n
    68.24<\/p>\n<\/td>\n
    		\n
    87.9<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Even with numeric constants, an implicit conversion might be called for. In the following SELECT<\/code> statement, the constant 1000<\/code> is compared the value in the ListPrice<\/code> column, which is configured with the MONEY<\/code> data type:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 Name AS ProductName,\n\t\t\t\u00a0 ListPrice\n\t\t\tFROM Production.Product\n\t\t\tWHERE ListPrice > 1000\n\t\t\t\u00a0 AND Color = 'blue';\n\t\t<\/pre>\n
    The 1000<\/code> is treated as an INT<\/code> value, so it must be implicitly converted to the MONEY<\/code> type. But that’s not all what’s going on in the WHERE<\/code> clause. Notice that it also includes a string constant (blue<\/code>). The constant is compared to the Color<\/code> column, which is configured with the NVARCHAR<\/code> data type. This means that the database engine must convert the VARCHAR<\/code> string to the NVARCHAR<\/code> type, which gives us the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 46<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 50<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 54<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 60<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 60<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 46<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 50<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 54<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 60<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 46<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 50<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 54<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Like string constants, date and time values are also enclosed in single quotes, even though the values are stored as integers, as you can see in the following example:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 DATETIME = 'August 1, 2014',\n\t\t\t\u00a0 @var2 DATETIME = '2014-07-28 16:24:27.293',\n\t\t\t\u00a0 @var3 DATE = '7\/30\/2014',\n\t\t\t\u00a0 @var4 TIME = '14:05:55.001';\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 AS var1,\n\t\t\t\u00a0 @var2 AS var2,\n\t\t\t\u00a0 @var3 AS var3,\n\t\t\t\u00a0 @var4 AS var4;\n\t\t<\/pre>\n
    The date and time data types will accept various formats when passing in a constant, as long as the value is enclosed in quotes. The following table shows the results returned by the SELECT<\/code> statement:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2014-08-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    2014-07-28 16:24:27.293<\/p>\n<\/td>\n
    		\n
    2014-07-30<\/p>\n<\/td>\n
    		\n
    14:05:55.0010000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0You should also be aware of how to treat constants when working with some of the other data types. For example, a VARBINARY<\/code> value takes an 0x<\/code> prefix but is not enclosed in quotes. The same goes for a BIT<\/code> value. However, a UNIQUEIDENTIFIER<\/code> value is enclosed in quotes, as shown in the following T-SQL:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 VARBINARY(20) = 0x14Ad,\n\t\t\t\u00a0 @var2 BIT = 1,\n\t\t\t\u00a0 @var3 UNIQUEIDENTIFIER = 'CC421A37-E462-4AE0-8451-38F837FC5A1A';\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 AS var1,\n\t\t\t\u00a0 @var2 AS var2,\n\t\t\t\u00a0 @var3 AS var3;\n\t\t<\/pre>\n
    \u00a0Our SELECT<\/code> statement now returns the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    0x14AD<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    CC421A37-E462-4AE0-8451-38F837FC5A1A<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0Again, we generally enclose string values in quotes and leave the quotes off for numerical values, but as you’ve seen, some areas can be somewhat gray. If you get confused, check out the MSDN topic “Constants<\/a>.”<\/p>\n<\/div>\n
    “Are there any advantages to using a COALESCE expression rather than a CASE expression when checking for the first non-NULL value in a list of values?”<\/h3>\n
    \n
    A COALESCE<\/code> expression is a syntactic shortcut for a CASE<\/code> expression. That means, when the query optimizer gets ahold of a COALESCE<\/code> expression, it rewrites it as a CASE<\/code> one, so in that sense, they’re one in the same. Let’s look at a couple of examples. In the first one, we use COALESCE<\/code> to return the first column that is not a NULL<\/code> value:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 Name AS ProductName,\n\t\t\t\u00a0 Size,\n\t\t\t\u00a0 SizeUnitMeasureCode AS SizeUnit,\n\t\t\t\u00a0 WeightUnitMeasureCode AS WeightUnit,\n\t\t\t\u00a0 COALESCE(Size, SizeUnitMeasureCode, WeightUnitMeasureCode) AS FirstNotNull\n\t\t\tFROM Production.Product\n\t\t\tWHERE ProductID = 828;\n\t\t<\/pre>\n
    For this particular product, the Size<\/code> column and SizeUnitMeasureCode<\/code> column each contains a NULL<\/code> value, but the WeightUnit<\/code> column contains an actual value, G<\/code>, so the COALESCE<\/code> expression returns that value, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Size<\/b><\/p>\n<\/td>\n
    		\n
    SizeUnit<\/b><\/p>\n<\/td>\n
    		\n
    WeightUnit<\/b><\/p>\n<\/td>\n
    		\n
    FirstNotNull<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Rear Wheel<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    G\u00a0<\/p>\n<\/td>\n
    		\n
    G\u00a0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0Now let’s look at what happens when we change the  COALESCE<\/code> expression to a CASE<\/code> expression:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 Name AS ProductName,\n\t\t\t\u00a0 Size,\n\t\t\t\u00a0 SizeUnitMeasureCode AS SizeUnit,\n\t\t\t\u00a0 WeightUnitMeasureCode AS WeightUnit,\n\t\t\t\u00a0 CASE\n\t\t\t\u00a0\u00a0\u00a0 WHEN Size IS NOT NULL THEN Size\n\t\t\t\u00a0\u00a0\u00a0 WHEN SizeUnitMeasureCode IS NOT NULL THEN SizeUnitMeasureCode\n\t\t\t\u00a0\u00a0\u00a0 WHEN WeightUnitMeasureCode IS NOT NULL THEN WeightUnitMeasureCode\n\t\t\t\u00a0\u00a0\u00a0 ELSE NULL\n\t\t\t\u00a0 END AS FirstNotNull\n\t\t\tFROM Production.Product\n\t\t\tWHERE ProductID = 828;\n\t\t<\/pre>\n
    The SELECT<\/code> statement returns the same results as the previous example. That said, the COALESCE<\/code> expression is a lot simpler, although the logic of the CASE<\/code> statement is easier to understand. In some cases, you might find that it’s better to use CASE<\/code> so your intent is clear to other developers.<\/p>\n
    One other consideration with COALESCE<\/code>. Developers sometimes run into problems with COALESCE<\/code> expressions because of data type issues. The value returned by COALESCE<\/code> is based on the data type of the input value with the highest precedence, which might or might not be the returned value. If an implicit conversion is required, and such a conversion is not permitted, COALESCE<\/code> will return an error. See the article “Questions about SQL Server Data Types You were Too Shy to Ask<\/a>” for more details about this particular issue.<\/p>\n
    All that said, figuring out the logic behind COALESCE<\/code> is not too difficult, so choosing between CASE<\/code> and COALESCE<\/code> often comes down to nothing more than personal preference.<\/p>\n<\/div>\n
    “I’m working on a query that uses the NOT IN operator in the WHERE clause. The operator checks values in a list returned by a subquery. When the returned list includes a NULL value, the query returns an empty resultset, even though I know I should be seeing results. Any idea what might be happening?”<\/h3>\n
    \n
    Using NOT<\/code> IN<\/code> can be tricky, especially when NULL<\/code> values are involved. Case in point. The following SELECT<\/code> statement includes a WHERE<\/code> clause that checks for specific colors:<\/p>\n
    \t\t\tSELECT Name AS ProductName, Color\n\t\t\tFROM Production.Product\n\t\t\tWHERE FinishedGoodsFlag = 1\n\t\t\t\u00a0 AND Color NOT IN\n\t\t\t\u00a0\u00a0\u00a0 (SELECT DISTINCT Color FROM Production.Product\n\t\t\t\u00a0\u00a0\u00a0\u00a0 WHERE FinishedGoodsFlag = 0); \n\t\t<\/pre>\n
    Basically, what we’re doing is returning all products that are saleable items (FinishedGoodsFlag<\/code> =<\/code> 1<\/code>) and whose color is not one of the colors of the non-saleable products. Unfortunately, when we run this query we receive an empty resultset.<\/p>\n
    If you’re familiar with the data, you might be surprised by these results. After all, the table contains 295 rows of saleable products. Either there are no colors unique to saleable items, or something is wrong.<\/p>\n
    We can investigate the problem by first running the subquery in the example above separate from the outer query:<\/p>\n
    \t\t\tSELECT DISTINCT Color \n\t\t\tFROM Production.Product\n\t\t\tWHERE FinishedGoodsFlag = 0; \n\t\t<\/pre>\n
    The following table shows the subquery’s results, which includes three distinct items:<\/p>\n\n\n\n\n\n\n
    \n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, we have black and silver non-saleable items, along with those items for which no color has been assigned (the NULL<\/code> value). Now let’s modify the subquery to instead retrieve the colors of the orderable products:<\/p>\n
    \t\t\tSELECT DISTINCT Color \n\t\t\tFROM Production.Product\n\t\t\tWHERE FinishedGoodsFlag = 1; \n\t\t<\/pre>\n
    This time around, we again receive the same three values, along with another seven colors, as shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    Blue<\/p>\n<\/td>\n<\/tr>\n
    \n
    Grey<\/p>\n<\/td>\n<\/tr>\n
    \n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver\/Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Yellow<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly something is wrong. Rows that contain those seven additional colors should have been included in our original query results. The problem, it turns out, is the NULL<\/code> value. Let’s recast the logic of the WHERE<\/code> clause into several separate predicates, connected by the AND<\/code> logical operator:<\/p>\n
    \t\t\tSELECT Name AS ProductName, Color\n\t\t\tFROM Production.Product\n\t\t\tWHERE FinishedGoodsFlag = 1\n\t\t\t\u00a0 AND Color <> 'Black'\n\t\t\t\u00a0 AND Color <> 'Silver'\n\t\t\t\u00a0 AND Color <> NULL; \n\t\t<\/pre>\n
    Essentially, our NOT<\/code> IN<\/code> operator indicates that the value in the Color<\/code> column should not be Black<\/code> and<\/i> should not be Silver<\/code> and<\/i> should not be  NULL<\/code>, in addition to being a saleable product. The problem is with the equation Color<\/code> <><\/code> NULL<\/code>. For a row to be returned, all conditions must evaluate to true; however, a NULL<\/code> cannot evaluate to true or false, which means the condition as a whole will never evaluate to true and no rows will ever be returned. If we were to run this statement, we would once again receive an empty resultset.<\/p>\n
    One way around this predicament is to add a second predicate to the WHERE<\/code> clause in our subquery:<\/p>\n
    \t\t\tSELECT Name AS ProductName, Color\n\t\t\tFROM Production.Product\n\t\t\tWHERE FinishedGoodsFlag = 1\n\t\t\t\u00a0 AND Color NOT IN\n\t\t\t\u00a0\u00a0\u00a0 (SELECT DISTINCT Color FROM Production.Product\n\t\t\t\u00a0\u00a0\u00a0\u00a0 WHERE FinishedGoodsFlag = 0 AND Color IS NOT NULL); \n\t\t<\/pre>\n
    Now our subquery returns only the values Black<\/code> and Silver<\/code>, which means our outer query should return all rows except those whose Color<\/code> value is one of the two colors. The following table shows part of the results, although the query actually returns 120 rows:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 58<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Red<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bike Socks, M<\/p>\n<\/td>\n
    		\n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bike Socks, L<\/p>\n<\/td>\n
    		\n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Blue<\/p>\n<\/td>\n
    		\n
    Blue<\/p>\n<\/td>\n<\/tr>\n
    \n
    AWC Logo Cap<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, S<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, M<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, L<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, XL<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 62<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 44<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 48<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you were to run this query, you would see the Color<\/code> values black<\/code> and silver<\/code> are not included. However, what’s odd about this is that rows with NULL<\/code> for a color are not included either, even though NULL<\/code> is no longer being returned by the subquery. This might be fine in some cases, but in others you might want to see those values. Before we start looking for a workaround, however, another consideration is performance. Many SQL Server folks recommend using an EXISTS<\/code> operator rather than an IN<\/code> operator anyway. So let’s rewrite the last statement using NOT<\/code> EXISTS<\/code>:<\/p>\n
    \t\t\tSELECT Name AS ProductName, Color \n\t\t\tFROM Production.Product a\n\t\t\tWHERE a.FinishedGoodsFlag = 1 AND Color IS NOT NULL\n\t\t\t\u00a0 AND NOT EXISTS\n\t\t\t\u00a0\u00a0\u00a0 (SELECT * FROM Production.Product b\n\t\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE FinishedGoodsFlag = 0 AND a.Color = b.Color); \n\t\t<\/pre>\n
    Our WHERE<\/code> clause now specifies that we include only saleable products, that they be a known color, and that the color not exist within the list of non-orderable colors. As a result, the SELECT<\/code> statement returns the same results as the preceding one, 120 rows. And if we want to include the saleable products with a Color<\/code> value of NULL<\/code>, we can simply remove the IS<\/code> NOT<\/code> NULL<\/code> expression:<\/p>\n
    \t\t\tSELECT Name AS ProductName, Color \n\t\t\tFROM Production.Product a\n\t\t\tWHERE a.FinishedGoodsFlag = 1\n\t\t\t\u00a0 AND NOT EXISTS\n\t\t\t\u00a0\u00a0\u00a0 (SELECT * FROM Production.Product b\n\t\t\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE FinishedGoodsFlag = 0 AND a.Color = b.Color); \n\t\t<\/pre>\n
    Our statement now returns 170 rows, which include those rows with a Color<\/code> value of NULL<\/code>. In theory, this statement should perform better than the ones using NOT<\/code> NULL<\/code>, but you might want to test that part out yourself to be sure. Just know that using NOT<\/code> IN<\/code> with NULL<\/code> values can return unexpected results, so be prepared.<\/p>\n<\/div>\n
    \u00a0“What the heck is a ‘modulo’?”<\/h3>\n
    \n
    Remember when you first learned long division? You had the dividend, the divisor, and the remainder. The dividend is the number you start with. The divisor is the number you divide into the dividend. The remainder is what’s left. Modulo is a mathematical operator that returns the remainder.<\/p>\n
    Here’s how it works. In the follow example, @var1<\/code> serves as our dividend, and @var2<\/code> serves as our divisor:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 INT = 32,\n\t\t\t\u00a0 @var2 INT = 8;\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 \/ @var2 AS Division,\n\t\t\t\u00a0 @var1 % @var2 AS Remainder;\n\t\t<\/pre>\n
    The first expression in our SELECT<\/code> clause uses the division operator (\/<\/code>) to divide @var1<\/code> by @var2<\/code>. The second expression uses the modulo operator (%<\/code>) to calculate the remainder that’s produced when you divide these two numbers. The following table shows the results.<\/p>\n\n\n\n\n
    \n
    Division<\/b><\/p>\n<\/td>\n
    		\n
    Remainder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    No surprise here. When you divide 32 by 8, you get 4, with a remainder of 0. But now let’s produce a remainder other than 0:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 INT = 36,\n\t\t\t\u00a0 @var2 INT = 8;\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 \/ @var2 AS Division,\n\t\t\t\u00a0 @var1 % @var2 AS Remainder;\n\t\t<\/pre>\n
    This time around, we simply set @var1<\/code> to equal 36, rather than 32. Now our results show a remainder of 4.<\/p>\n
    That’s all there is to using the modulo operator. And you can use it with other numeric types:<\/p>\n
    \t\t\tDECLARE\n\t\t\t\u00a0 @var1 DECIMAL(10,4) = 55.556,\n\t\t\t\u00a0 @var2 INT = 5;\n\t\t\t\u00a0\n\t\t\tSELECT\n\t\t\t\u00a0 @var1 \/ @var2 AS Division,\n\t\t\t\u00a0 @var1 % @var2 AS Remainder;\n\t\t<\/pre>\n
    In this case, we’re dividing 55.566 by 5 to come up with the following results:<\/p>\n\n\n\n\n
    \n
    Division<\/b><\/p>\n<\/td>\n
    		\n
    Remainder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    11.111200000000000<\/p>\n<\/td>\n
    		\n
    0.5560<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the expression that contains the modulo operator returns a value of 0.5560<\/code>. As for the expression that contains the division operator, the operator’s returned value is based on the argument type with the highest precedence, which in this case is DECIMAL<\/code>.<\/p>\n<\/div>\n
    “Are all WHEN and ELSE clauses in a CASE expression evaluated if the first WHEN clause evaluates to TRUE?”<\/h3>\n
    \n
    According to the SQL Server documentation, a CASE<\/code> expression evaluates each condition sequentially and stops with the first condition that evaluates to true. In other words, the expression will short circuit when a condition is met, saving the database engine from having to perform unnecessary processing. This is a good thing. The less work the engine has to perform, the better.<\/p>\n
    To get a sense of how this works, let’s look at an example. The following SELECT<\/code> statement includes a CASE<\/code> expression that sets up three possible conditions when concatenating the name-related columns in the vSalesPerson<\/code> view:<\/p>\n
    \t\t\tSELECT \n\t\t\t\u00a0 BusinessEntityID AS EmployeeID,\n\t\t\t\u00a0 CASE\n\t\t\t\u00a0\u00a0\u00a0 WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName\n\t\t\t\u00a0\u00a0\u00a0 WHEN LEN(MiddleName) = 1 THEN FirstName + ' ' + MiddleName + '. ' + LastName\n\t\t\t\u00a0\u00a0\u00a0 ELSE FirstName + ' ' + MiddleName + ' ' + LastName\n\t\t\t\u00a0 END AS FullName,\n\t\t\t\u00a0 TerritoryName AS Territory\n\t\t\tFROM Sales.vSalesPerson\n\t\t\tWHERE TerritoryName IN ('central', 'northeast', 'southeast');\n\t\t<\/pre>\n
    The first CASE<\/code> condition checks whether the MiddleName<\/code> value is NULL<\/code>. If it is, the FirstName<\/code> and LastName<\/code> columns are concatenated and the expression stops running. If the column contains a value other than NULL<\/code>, the expression moves on to the next condition, which checks the length of the MiddleName<\/code> value. If it equals 1<\/code>, the three columns are concatenated and a period is added to the middle initial. The expression will then stop at that point. Otherwise, the ELSE<\/code> condition is applied and all three columns are concatenated as is. The database engine repeats the process for each row and returns the following results:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    Tsvi Michael Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The CASE<\/code> expression’s ability to short circuit can help to avoid unnecessary processing when it can be substituted for other statement elements. For example, suppose we define the following SELECT<\/code> statement:<\/p>\n
    \t\t\tSELECT\n\t\t\t\u00a0 Name AS ProductName,\n\t\t\t\u00a0 Color\n\t\t\tFROM Production.Product\n\t\t\tWHERE FinishedGoodsFlag = 1 AND\n\t\t\t\u00a0 (Color = 'black' OR color = 'red');\n\t\t<\/pre>\n
    Nothing flashy here. For each row, the database engine tests for the three specified conditions and determines whether that row is returned. Consequently, the resultset includes any saleable product that is either black or red, which means 127 rows evaluate to true. Part of those results are shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Black, 58<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 58<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Red<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Black<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 62<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 44<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 48<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 52<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 56<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 58<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 60<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 62<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u00a0Suppose we want to reduce some of the work that the database engine has to perform. We can instead use a CASE<\/code> expression in the WHERE<\/code> clause, as shown in the following SELECT<\/code> statement:<\/p>\n
    \t\t\tSELECT\n\t\t\t\u00a0 Name AS ProductName,\n\t\t\t\u00a0 Color\n\t\t\tFROM Production.Product\n\t\t\tWHERE FinishedGoodsFlag = 1 AND\n\t\t\t\u00a0 Color = CASE Color\n\t\t\t\u00a0\u00a0\u00a0 WHEN 'black' THEN Color\n\t\t\t\u00a0\u00a0\u00a0 WHEN 'red' THEN Color\n\t\t\t\u00a0 END;\n\t\t<\/pre>\n
    If the Color<\/code> value equals black<\/code>, the condition evaluates to true and the expression stops running, thus avoiding any unnecessary processing. Yet the statement returns the same results as the preceding example.<\/p>\n
    Under the right circumstances, using a CASE<\/code> expression in this way can be a handy way to reduce your workloads. However, a CASE<\/code> expression might not always short circuit in the way you hope. For example, suppose your T-SQL includes logic is similar to that shown in the following example:<\/p>\n
    \t\t\tDECLARE @var1 INT = 0;\n\t\t\t\u00a0\n\t\t\tSELECT \n\t\t\t\u00a0 CASE \n\t\t\t\u00a0\u00a0\u00a0 WHEN @var1 = 0 THEN 0 \n\t\t\t\u00a0\u00a0\u00a0 ELSE MAX(1\/0) \n\t\t\t\u00a0 END;\n\t\t<\/pre>\n
    All we’re doing is declaring an INT<\/code> variable, setting its value to 0<\/code>, and using a CASE<\/code> expression to retrieve a specific value. Based on our understanding of the CASE<\/code> expression’s ability to short circuit, we would expect the expression’s first condition to evaluate to true and the expression to then stop. However, what we get from our SELECT<\/code> statement is the following error:<\/p>\n
    \t\t\tMsg 8134, Level 16, State 1, Line 3\n\t\t\tDivide by zero error encountered.\n\t\t<\/pre>\n
    The error shows that the ELSE<\/code> condition did in fact run, even though it shouldn’t have. It turns out that under certain circumstances, a CASE<\/code> expression doesn’t behave like it’s supposed to behave. The problem appears to be related to aggregate functions (at least in part), so be aware that in some cases a CASE<\/code> expression might not deliver the benefits you’re hoping for and you might have to come up with a workaround to make the CASE<\/code> expression work the way you want it to.<\/p>\n<\/div>\n
    “What’s the difference between the two ‘not equal to’ operators (<> and !=)?”<\/h3>\n
    \n
    In T-SQL, the two comparisons operators are syntactically equivalent. They each perform a Boolean comparison between two expressions. If those expressions return non-NULL<\/code>, unequal values, the comparison evaluates to true, otherwise, it evaluates to false. However, if either value is  NULL<\/code>, the evaluation returns a NULL<\/code>. Here’s a sample of the two operators in action:<\/p>\n
    \t\t\tDECLARE @var1 INT = 1;\n\t\t\t\u00a0\n\t\t\tSELECT 'not equal' WHERE @var1 <> 2;\n\t\t\t\u00a0\n\t\t\tSELECT 'not equal' WHERE @var1 != 2;\n\t\t<\/pre>\n
    \u00a0The variable is set to a value of  1<\/code>, and in each SELECT<\/code> statement, that variable is compared to the constant 2<\/code>. As a result, each SELECT<\/code> statement returns the value not<\/code> equal<\/code> because each comparison evaluates to true. Even in terms of performance, there doesn’t appear to be much difference between the two.<\/p>\n
    However, there is one difference you should be aware of. The <><\/code> operator conforms to ANSI standards; the !=<\/code> operator does not, despite that fact that a number of relational database systems support both operators. That said, even if there is only the slightest chance that you might port your script to another system, you’re better off sticking with the  <><\/code> operator.<\/p>\n<\/div>\n
    “I’ve come across operators such as += in variable assignment SET statements. What do they mean?”<\/h3>\n
    \n
    The add-equals operator (+=<\/code>) is a compound operator, one of several types of compound operators supported in SQL Server. A compound operator takes the original value and in some way amends it, rather than replacing it. For example, the following T-SQL uses the add-equals operator to update the value of the @var1<\/code> variable:<\/p>\n
    \t\t\tDECLARE @var1 INT = 2\n\t\t\t\u00a0\n\t\t\tSET @var1 += 1;\n\t\t\t\u00a0\n\t\t\tSELECT @var1;\n\t\t<\/pre>\n
    We start by declaring @var1<\/code> and setting its value to 2<\/code>. We then use a SET<\/code> statement to add a value of 1<\/code> to the original value. The SELECT<\/code> statement returns the new value, which is 3<\/code>.<\/p>\n
    The add-equals operator makes it possible to add a value without having to specify the original value. Without the add-equals operator we would have to rewrite our SET<\/code> statement as follows:<\/p>\n
    \t\t\tDECLARE @var1 INT = 2\n\t\t\t\u00a0\n\t\t\tSET @var1 = @var1 + 1;\n\t\t\t\u00a0\n\t\t\tSELECT @var1;\n\t\t<\/pre>\n
    The add-equals operator merely provides the shorthand necessary to avoid having to explicitly specify the original value, yet the SELECT<\/code> statement still returns a value of 3<\/code>. Plus, SQL Server supports other compound operators as well. For example, the following T-SQL subtracts 1<\/code> from the original value:<\/p>\n
    \t\t\tDECLARE @var1 INT = 2\n\t\t\t\u00a0\n\t\t\tSET @var1 -= 1;\n\t\t\t\u00a0\n\t\t\tSELECT @var1;\n\t\t<\/pre>\n
    In this case, we’re using the subtract-equals (-=<\/code>) operator in our SELECT<\/code> statement, rather than the add-equals operator. The subtract-equals operator works the same way as the add-equals operator, except that the second value is subtracted rather than added. As a result, our SELECT<\/code> statement now returns a value of 1<\/code>.<\/p>\n
    Another example of a compound operator is the multiply-equals operator, which is shown in the following example:<\/p>\n
    \t\t\tDECLARE @var1 INT = 2\n\t\t\t\u00a0\n\t\t\tSET @var1 *= 3;\n\t\t\t\u00a0\n\t\t\tSELECT @var1;\n\t\t<\/pre>\n
    In this case, the SET<\/code> statement uses the multiply-equals operator to multiple the variable value 2<\/code> by the constant value 3<\/code>, giving us a total of 6<\/code>. And compound operators are not limited to mathematical expressions. For example, the following T-SQL includes a SET<\/code> statement that uses the add-equals operator to concatenate values:<\/p>\n
    \t\t\tDECLARE @var1 VARCHAR(30) = 'some value'\n\t\t\t\u00a0\n\t\t\tSET @var1 += ' and then some';\n\t\t\t\u00a0\n\t\t\tSELECT @var1;\n\t\t<\/pre>\n
    In this example, the SET<\/code> statement adds the string value and<\/code> then<\/code> some<\/code> (including the opening space) to the variable value some<\/code> value<\/code>, giving us the value some<\/code> value<\/code> and<\/code> then<\/code> some<\/code>. There are other compound operators as well, such as divide-equals (\/=<\/code>) and modulo-equals (%=<\/code>). Check out the MSDN topic “Compound Operators<\/a>” for more information.<\/p>\n<\/div>\n<\/div>\n\n\n\n
    + CONVERT(NVARCHAR(20), CAST(@var1 AS MONEY), 1) AS amount1,   ‘<\/p>\n\n\n\n
    Each expression in the SELECT<\/code> list references one of the variables as its source data and is made up of multiple components. What provides the final format, however, is the CONVERT<\/code> function. The function requires that the source data be either the MONEY<\/code> or SMALLMONEY<\/code> type. When you call the function, you specify a second argument along with the source value. That argument determines the format of the data. In this case, we specify 1<\/code> so the outputted data includes commas every three digits to the left of the decimal point. We also concatenate the value with a dollar sign to give it that final touch, as shown in the following results:<\/p>\n\n\n\n
    \n
    amount1<\/b><\/p>\n<\/td>
    		\n
    amount2<\/b><\/p>\n<\/td>
    		\n
    amount3<\/b><\/p>\n<\/td><\/tr>
    \n
    $555,555,555.56<\/p>\n<\/td>
    		\n
    $555,555,555.56<\/p>\n<\/td>
    		\n
    $555,555,555.56<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
     Overall, this isn’t too painful a process, unless you’re working with currencies that follow a different format, such as the Swiss franc, which takes apostrophes rather than commas. In such cases, you might have to add yet another function, such as REPLACE<\/code>, to substitute the commas with apostrophes.<\/p>\n\n\n\n
    Fortunately, since the release of SQL Server 2012, you’ve been able to instead use the FORMAT<\/code> function to output your numbers to a currency format. The FORMAT<\/code> function returns a value in a specified format, based on a specified culture. To demonstrate how this works, let’s first recast our preceding expressions into ones that use the FORMAT<\/code> function to configure the data as US dollar amounts:<\/p>\n\n\n\n
    <\/pre>\n\n\n\n
    Notice that we must first cast the character data (@var1<\/code>) to the MONEY<\/code> data type, but we can use the DECIMAL<\/code> variable (@var2<\/code>) as is. The second FORMAT<\/code> argument (c<\/code>) specifies that we’re formatting currency, and the third argument specifies the US symbol (en-us<\/code>) for the cultural context. The SELECT<\/code> statement returns the same results as the previous SELECT<\/code> statement, with all values returned as the NVARCHAR<\/code> type.<\/p>\n\n\n\n
    However, with the FORMAT<\/code> function, we can specify other cultures. For example, the following example uses the German symbol (de-de<\/code>) to provide the cultural context:<\/p>\n\n\n\n
    <\/pre>\n\n\n\n
    Now our results are specific to how the euro is represented in Germany, as shown in the following table:<\/p>\n\n\n\n
    \n
    amount1<\/b><\/p>\n<\/td>
    		\n
    amount2<\/b><\/p>\n<\/td>
    		\n
    amount3<\/b><\/p>\n<\/td><\/tr>
    \n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>
    		\n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>
    		\n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
     Certainly, the FORMAT<\/code> function is a handy tool if you want your results to be displayed in a specific currency. But just to reiterate, such formatting is generally handled by the calling application, with the source data retrieved in its raw state.<\/p>\n\n\n\n
    “I see the plus sign (+) used in a variety of ways in T-SQL script, but I can’t always make sense of how SQL Server arrives at the results it does. Is there a “right way” to use plus signs?”<\/h3>\n\n\n\n
    \n
    SQL Server is a tricky devil. As you’ve discovered, plus signs have all sorts of meaning in T-SQL. You can use them to concatenate string values, add numerical values together, or indicate that a numerical value is a positive number, rather than negative. The following SELECT<\/code> statement demonstrates how to both concatenate and add values:<\/p>\n
    <\/pre>\n
    For the FullName<\/code> column, we’ve used the plus sign to concatenate the FirstName<\/code> and  LastName<\/code> columns with a space in between. For the TwoYearSales<\/code> column, we’ve used the plus sign to add the SalesLastYear<\/code> and  SalesYTD<\/code> columns together. In both cases, the database engine knows when to concatenate the values or when to add them together, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    SalesLastYear<\/b><\/p>\n<\/td>\n
    		\n
    SalesYTD<\/b><\/p>\n<\/td>\n
    		\n
    TwoYearSales<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Blythe<\/p>\n<\/td>\n
    		\n
    $1,750,406.48<\/p>\n<\/td>\n
    		\n
    $3,763,178.18<\/p>\n<\/td>\n
    		\n
    $5,513,584.66<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Mitchell<\/p>\n<\/td>\n
    		\n
    $1,439,156.03<\/p>\n<\/td>\n
    		\n
    $4,251,368.55<\/p>\n<\/td>\n
    		\n
    $5,690,524.58<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    $1,997,186.20<\/p>\n<\/td>\n
    		\n
    $3,189,418.37<\/p>\n<\/td>\n
    		\n
    $5,186,604.57<\/p>\n<\/td>\n<\/tr>\n
    \n
    Garrett Vargas<\/p>\n<\/td>\n
    		\n
    $1,620,276.90<\/p>\n<\/td>\n
    		\n
    $1,453,719.47<\/p>\n<\/td>\n
    		\n
    $3,073,996.36<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi Reiter<\/p>\n<\/td>\n
    		\n
    $1,849,640.94<\/p>\n<\/td>\n
    		\n
    $2,315,185.61<\/p>\n<\/td>\n
    		\n
    $4,164,826.55<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    $1,927,059.18<\/p>\n<\/td>\n
    		\n
    $1,352,577.13<\/p>\n<\/td>\n
    		\n
    $3,279,636.31<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu Ito<\/p>\n<\/td>\n
    		\n
    $2,073,506.00<\/p>\n<\/td>\n
    		\n
    $2,458,535.62<\/p>\n<\/td>\n
    		\n
    $4,532,041.62<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jos\u00e9 Saraiva<\/p>\n<\/td>\n
    		\n
    $2,038,234.65<\/p>\n<\/td>\n
    		\n
    $2,604,540.72<\/p>\n<\/td>\n
    		\n
    $4,642,775.37<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Campbell<\/p>\n<\/td>\n
    		\n
    $1,371,635.32<\/p>\n<\/td>\n
    		\n
    $1,573,012.94<\/p>\n<\/td>\n
    		\n
    $2,944,648.25<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete Mensa-Annan<\/p>\n<\/td>\n
    		\n
    $0.00<\/p>\n<\/td>\n
    		\n
    $1,576,562.20<\/p>\n<\/td>\n
    		\n
    $1,576,562.20<\/p>\n<\/td>\n<\/tr>\n
    \n
    Lynn Tsoflias<\/p>\n<\/td>\n
    		\n
    $2,278,548.98<\/p>\n<\/td>\n
    		\n
    $1,421,810.92<\/p>\n<\/td>\n
    		\n
    $3,700,359.90<\/p>\n<\/td>\n<\/tr>\n
    \n
    Rachel Valdez<\/p>\n<\/td>\n
    		\n
    $1,307,949.79<\/p>\n<\/td>\n
    		\n
    $1,827,066.71<\/p>\n<\/td>\n
    		\n
    $3,135,016.50<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jae Pak<\/p>\n<\/td>\n
    		\n
    $1,635,823.40<\/p>\n<\/td>\n
    		\n
    $4,116,871.23<\/p>\n<\/td>\n
    		\n
    $5,752,694.62<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ranjit Varkey Chudukatil<\/p>\n<\/td>\n
    		\n
    $2,396,539.76<\/p>\n<\/td>\n
    		\n
    $3,121,616.32<\/p>\n<\/td>\n
    		\n
    $5,518,156.08<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    There’s no real mystery here. When we use the plus sign with only character data, the database engine concatenates the values. In this case, the plus sign is considered a string concatenation operator. When we use the plus sign with numerical values, the database engine adds the values together. Under these circumstances, the plus sign is considered an addition operator.<\/p>\n
    Not surprisingly, when we mix and match the types of data, our results are less predictable. Take a look at the following example:<\/p>\n
    <\/pre>\n
    As you can see, we’ve declared seven variables, each defined with a different type. We then use the plus sign to combine the variables in different ways, giving us the following results:<\/p>\n\n\n\n\n
    \n
    var1var2<\/b><\/p>\n<\/td>\n
    		\n
    var3var6<\/b><\/p>\n<\/td>\n
    		\n
    var4var5<\/b><\/p>\n<\/td>\n
    		\n
    var7var3<\/b><\/p>\n<\/td>\n
    		\n
    var6var7<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    TestVarCX<\/p>\n<\/td>\n
    		\n
    1299.99<\/p>\n<\/td>\n
    		\n
    1001.0001<\/p>\n<\/td>\n
    		\n
    2015-05-21 00:00:00.000<\/p>\n<\/td>\n
    		\n
    2017-04-19 23:45:36.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As we would expect, when we pair @var1<\/code> with @var2<\/code>, both character types, we end up with a concatenated string. When we pair @var3<\/code> to @var6<\/code>, both numerical types, the values are added together, even though @var3<\/code> and @var6<\/code> are different types.<\/p>\n
    Now we get to our third pairing: @var4<\/code> +<\/code> @var5<\/code>. This time around, we’re adding a DECIMAL<\/code> value to BIT<\/code> value. However, the database engine still treats both variables as numerical values and adds them together, even though SQL Server documentation suggests that you can’t do this with the BIT<\/code> type. (And really, why would you?)<\/p>\n
    The next item in the select list, @var7<\/code> +<\/code> @var3<\/code>, pairs a DATETIME<\/code> value with a SMALLINT<\/code> value. This time, we get a date that is 300 days later than the original date. When your expression includes the plus sign, the database engine defaults to the data type with the highest precedence, in this case,  DATETIME<\/code>. Based on the type, the engine determines whether to use the plus sign to concatenate values or add them together. If the data type with the highest precedence is a numeric data type, the engine attempts to add the values. In this case, the engine adds the SMALLINT<\/code> value to the DATETIME<\/code> value as the specified number of days. (A DATETIME<\/code> value is actually stored as two integers.)<\/p>\n
    Finally, we pair a MONEY<\/code> date type with the DATETIME<\/code> date type (@var6<\/code> +<\/code> @var7<\/code>). Once again, DATETIME<\/code> takes precedence over MONEY<\/code>, so the database engine adds 999.99 days to our original date to come up with a date in 2017.<\/p>\n
    We can verify how data type precedence works with plus signs by using the SQL_VARIANT_PROPERTY<\/code> function to retrieve the data type of the expressions that pair the different variables:<\/p>\n
    <\/pre>\n
    The SELECT<\/code> statement returns the returned data type for each expression, as shown in the following table:<\/p>\n\n\n\n\n
    \n
    var1var2<\/b><\/p>\n<\/td>\n
    		\n
    var3var6<\/b><\/p>\n<\/td>\n
    		\n
    var4var5<\/b><\/p>\n<\/td>\n
    		\n
    var7var3<\/b><\/p>\n<\/td>\n
    		\n
    var6var7<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    nvarchar<\/p>\n<\/td>\n
    		\n
    money<\/p>\n<\/td>\n
    		\n
    decimal<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     In each case, the expression returns the type with the highest precedence and determines how the plus sign will be used.<\/p>\n
    So far in our examples, the database engine has been able to add or concatenate values without a problem because the engine could implicitly convert the data type with the lowest precedence to the type with the highest. But this isn’t always the case:<\/p>\n
    <\/pre>\n
    This time around, we’re using the plus sign to pair a NVARCHAR<\/code> value with a SMALLINT<\/code> value. Because SMALLINT<\/code> has precedence over NVARCHAR<\/code>, the database engine attempts to convert the value TestVar<\/code> to the SMALLINT<\/code> type, which of course is not possible. Consequently, the database engine returns the following error message:<\/p>\n
    <\/pre>\n
    The only way we can pair a numerical value with a string value is to concatenate them. In order to do so, we must explicitly convert the SMALLINT<\/code> value to a character data type:<\/p>\n
    <\/pre>\n
    Now our SELECT<\/code> statement returns the value TestVar300<\/code>.<\/p>\n
    There’s one other use of the plus sign that you should be aware of-as a unary plus operator. SQL Server supports two types of unary operators, plus and minus, which are used to designate whether a numeric value is positive or negative:<\/p>\n
    <\/pre>\n
    All we’ve done here is to designate our two literal values as positive and negative numbers. The SELECT<\/code> statement returns the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    123.45<\/p>\n<\/td>\n
    		\n
    -123.45<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Be aware, however, that you cannot use the positive unary operator to convert a negative number to a positive number. For that, you need to use the ABS<\/code> mathematical function.<\/p>\n<\/div>\n\n\n\n
    “The NULLIF expression makes no sense. I would think you’d use it to somehow ferret out a NULL value or to test a condition for a NULL value. But it appears to merely return a NULL if two values are equal. Is that all it’s doing?”<\/h3>\n\n\n\n
    \n
    That’s part of what it’s doing. When a NULLIF<\/code> expression compares two values, it returns the first value if the two values are not equal. If they are equal, the expression returns a NULL<\/code> value of the same data type as the first specified value. Here’s what a NULLIF<\/code> expression looks like in action:<\/p>\n
    <\/pre>\n
    In this case, the NULLIF<\/code> expression returns valueA<\/code> because the two values are not equal. If they were equal, the returned value would be NULL<\/code>. In addition, the returned value would be NULL<\/code> if the first value were NULL<\/code>.<\/p>\n
    When you know how a NULLIF<\/code> expression works, it doesn’t seem so bad, although its use still has a tendency to cause confusion. Luckily, we can achieve the same results by using a CASE<\/code> statement:<\/p>\n
    <\/pre>\n
    The advantage of the CASE<\/code> statement is that it’s a lot easier to understand. Sure, NULLIF<\/code> provides simpler syntax, but if it creates confusion, what’s the point?<\/p>\n<\/div>\n\n\n\n
    “Can you use expressions in data manipulation language (DML) statements?”<\/h3>\n\n\n\n
    \n
    You can use expressions wherever T-SQL syntax permits them, including DML statements. Take a look at part of the syntax for the UPDATE<\/code> statement’s SET<\/code> clause:<\/p>\n
    <\/pre>\n
     According to SQL Server Books Online, the expression placeholder can be a “variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value.” This pretty much defines the term expression<\/i> as we understand it. Note, however, that any expression you define must conform to the rules associated with the particular T-SQL statement in which the expression is defined. For example, an UPDATE<\/code> statement’s UPDATE<\/code> clause can include the TOP<\/code> clause, as shown in the following syntax:<\/p>\n
    <\/pre>\n
     In this case, expression “specifies the number or percent of rows that will be updated.” In other words, you cannot specify a non-numerical string value as your expression, as you can with other types of expressions. That said, it’s still considered an expression.<\/p>\n
    So, yes, you can use an expression in a DML statement. In fact, they’re used all the time. Let’s look at an example. Suppose we create the following table in our database and insert a row of data:<\/p>\n
    <\/pre>\n
    Now let’s use an UPDATE<\/code> statement to modify that row of data:<\/p>\n
    <\/pre>\n
    Our SET<\/code> clause is updating two columns. Each SET<\/code> definition includes an expression that either adds the @AmtReceived<\/code> value to the target column or subtracts that value. If we run the SELECT<\/code> statement after the table has been updated, we come up with the following results:<\/p>\n\n\n\n\n
    \n
    ProductID<\/b><\/p>\n<\/td>\n
    		\n
    InStock<\/b><\/p>\n<\/td>\n
    		\n
    OnOrder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    67<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you’re writing T-SQL statements, you’re writing expressions, even if you don’t realize it. That’s why it’s important to refer back to the statement’s syntax whenever you’re uncertain how to define a particular element. Expressions are supported throughout most statements. The syntax for the UPDATE<\/code> statement, for example, includes the expression placeholder in 10 different places, which suggests that many of us might not be using some statements to their fullest capacity.<\/p>\n<\/div>\n\n\n\n
     “The CASE expression allows me to leave out the ELSE clause. Is it that important to include it?”<\/h3>\n\n\n\n
    \n
    Yes, but…. The ELSE<\/code> clause specifies the value to return if no other comparison operations (WHEN...THEN<\/code>) evaluate to true. If you omit the ELSE<\/code> clause, the database engine instead returns a NULL<\/code> value. A NULL<\/code> value might be fine, if that’s what you want. But what if it’s not? Take a look at the following example, which uses a CASE<\/code> expression to determine how to return an employee’s full name:<\/p>\n
    <\/pre>\n
    If the MiddleName<\/code> column is NULL<\/code>, that column is not included in the expression. Only the first and last names are concatenated. If the MiddleName<\/code> value is an initial, a period is added to the initial and concatenated with the first and last names. Otherwise, all three names are concatenated, as shown in the following results:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    Tsvi Michael Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now let’s modify the CASE<\/code> expression by removing the ELSE<\/code> clause:<\/p>\n
    <\/pre>\n
    This time a NULL<\/code> is returned if the MiddleName<\/code> column has a value that is more than an initial, as the following table shows:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly, you would not want to delete the ELSE<\/code> clause in this instance. You could, however, add a WHEN...THEN<\/code> condition that replaces the ELSE<\/code> clause, in which case, you’re still returning NULL<\/code> of none of the conditions are met, which might be fine under some circumstances. Consider the following example:<\/p>\n
    <\/pre>\n
    If the SalesQuota<\/code> value is great than 250,000, a true<\/code> is returned. If the value is less than or equal to 250,000, we get a false<\/code>. However, if the column is NULL<\/code>, neither condition can be true, so the CASE<\/code> expression returns a NULL<\/code>, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    HigherQuota<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Stephen Jiang<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Blythe<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Mitchell<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi Reiter<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu Ito<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Campbell<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete Mensa-Annan<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Syed Abbas<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Amy Alberts<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    You can choose to include an ELSE<\/code> clause and then provide a default value, such as N\/A<\/code> (or even NULL<\/code>), whether or not you think it’s necessary. In fact, many developers think you should never omit the ELSE<\/code> clause, even if you believe it is impossible for one of the WHEN...THEN<\/code> conditions not to be met. Someone might make a change to the database that affects the statement or your thinking about possible outcomes might not be correct.<\/p>\n<\/div>\n\n\n\n
    “Can you shed light on the order in which the database engine processes the components in an expression?”<\/h3>\n\n\n\n
    \n
    The way in which an expression’s elements are processed depends on several factors: their listed order, whether any subset of elements is enclosed in parentheses, and what operators are used to connect the various elements. Parentheses group elements together to ensure that those elements are processed as a unit before being incorporated into the rest of the expression. In addition, the expression as a whole adheres to the concept of operator precedence,<\/i> in which the operator types determine the order in which the expression is evaluated. For example, the multiply and division arithmetic operators take precedence over the greater than (><\/code>) and lesser than (<<\/code>) comparison operations, which take precedence over logical operators such as AND<\/code> or ANY<\/code>. (You can find details about operator precedence in the MSDN topic “Operator Precedence<\/a>.”)<\/p>\n
    Let’s start with a few simple arithmetic-based expressions to give you a sense of how this works. The following example declares several INT<\/code> variables, assigns an expression to each one, and returns the values from all three:<\/p>\n
    <\/pre>\n
    All we’re doing in each expression is multiplying and adding and subtracting values. However, because we use parentheses in the second and third expressions, our results come up quite different:<\/p>\n\n\n\n\n
    \n
    num1<\/b><\/p>\n<\/td>\n
    		\n
    num2<\/b><\/p>\n<\/td>\n
    		\n
    num3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The @num1<\/code> expression is fairly straightforward. We multiple 3 by 2, add 5, and subtract 7, which gives us a total of 4.<\/p>\n
    However, in the @num2<\/code> expression, we enclose the 2<\/code> +<\/code> 5<\/code> elements in parentheses, so they’re processed first, giving us a total of 7. The 7 is then multiplied by the first 3, giving us 21. From there, we subtract 7, giving us a total of 14.<\/p>\n
    In the @num3<\/code> expression, the parentheses enclose the 2<\/code> +<\/code> 5<\/code> -<\/code> 7<\/code> elements, which are processed before the other elements. Because this returns 0, 3 is multiplied by 0, giving us a final result of 0.<\/p>\n
    Now let’s look at another example, which again declares three variables and applies different expressions to them:<\/p>\n
    <\/pre>\n
    Here’s what are results look like:<\/p>\n\n\n\n\n
    \n
    SalesDiff1<\/b><\/p>\n<\/td>\n
    		\n
    SalesDiff2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    6500<\/p>\n<\/td>\n
    		\n
    500<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     The first expression includes no parentheses. However, the multiply operator takes precedence over the addition and subtraction operators, so the multiplication is performed first. That means we first multiply the SalesQuota<\/code> value by 2 and the SalesYTD<\/code> value by 2, giving us the expression 6000<\/code> -<\/code> 2500<\/code> +<\/code> 3000<\/code>, which equals 6500.<\/p>\n
    The second expression does use parentheses. The first set of parentheses returns a value of 6000. The second set of parentheses actually contains a third set, and those are the elements evaluated first, before the outer set of elements. After they’re evaluated, that part of the expression enclosed in the second set of parentheses becomes @SalesLastYear<\/code>  +<\/code> 3000<\/code>, giving us a total of 5500. That 5500 is then subtracted from the 6000, which is how we end up with 500.<\/p>\n
    Now let’s look at an expression that incorporates character data as well as numerical data. In the following SELECT<\/code> statement, our WHERE<\/code> clause includes both the OR<\/code> and the AND<\/code> logical operators:<\/p>\n
    <\/pre>\n
    The WHERE<\/code> clause search condition contains three predicates separated by the AND<\/code> operator and the OR<\/code> operator. In its current state, the search condition states that the ListPrice<\/code> value must be greater than 2.5 times the StandardCost<\/code> value and<\/i> the ProductLine<\/code> value must equal r<\/code> or<\/i> the ProductLine<\/code> value must equal t<\/code>. According to the rules of operator precedence, the database engine first evaluates the AND<\/code> logical operator and then the OR<\/code> logical operator. Because of the placement of the operators, the first two predicates must both evaluate to true or<\/i> the third predicate must evaluate to true. In other words, all returned rows must meet the first two conditions or meet the third condition. As a result, the statement returns 59 rows that qualify. The following table shows part of the results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    StandardCost<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n
    		\n
    ProductLine<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Front Wheel<\/p>\n<\/td>\n
    		\n
    96.7964<\/p>\n<\/td>\n
    		\n
    218.01<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Rear Wheel<\/p>\n<\/td>\n
    		\n
    108.7844<\/p>\n<\/td>\n
    		\n
    245.01<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-Panniers, Large<\/p>\n<\/td>\n
    		\n
    51.5625<\/p>\n<\/td>\n
    		\n
    125.00<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bottle Cage<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, M<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, L<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 60<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Touring Frame – Yellow, 62<\/p>\n<\/td>\n
    		\n
    199.8519<\/p>\n<\/td>\n
    		\n
    333.42<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 46<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 50<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 54<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 46<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 50<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 54<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Suppose what we were really after was to return rows that met the first condition and<\/i> either the second or<\/i> third condition. In this case, we would have to modify our search condition slightly:<\/p>\n
    <\/pre>\n
    Notice that the second and third predicates are now enclosed in parentheses. Consequently, the database engine will first evaluate them as a unit. That means for all rows returned, ListPrice<\/code> must be greater than 2.5 times the value of StandardCost<\/code> and<\/i> ProductLine<\/code> must equal r<\/code> or<\/i> t<\/code>. The following table shows all the results now returned by the query.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    StandardCost<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n
    		\n
    ProductLine<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bottle Cage<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, M<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, L<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Tire Tube<\/p>\n<\/td>\n
    		\n
    1.4923<\/p>\n<\/td>\n
    		\n
    3.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Tire Tube<\/p>\n<\/td>\n
    		\n
    1.8663<\/p>\n<\/td>\n
    		\n
    4.99<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Tire<\/p>\n<\/td>\n
    		\n
    8.0373<\/p>\n<\/td>\n
    		\n
    21.49<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    ML Road Tire<\/p>\n<\/td>\n
    		\n
    9.3463<\/p>\n<\/td>\n
    		\n
    24.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Tire<\/p>\n<\/td>\n
    		\n
    12.1924<\/p>\n<\/td>\n
    		\n
    32.60<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Tire<\/p>\n<\/td>\n
    		\n
    10.8423<\/p>\n<\/td>\n
    		\n
    28.99<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Notice that we now have only nine rows and that each row meets both conditions for the ListPrice<\/code> and ProductLine<\/code> columns. By applying the parentheses, we have better controlled how the rules of operator precedence are applied.<\/p>\n<\/div>\n\n\n\n
    “I get confused on how to treat constants in my expressions. Is there a trick in using them?”<\/h3>\n\n\n\n
    \n
    A constant is a literal value that is either part of an expression or is the entire expression. The way in which you treat constants depends on the type of data. As a general rule, you enclose character data in single quotes, but leave numerical data without quotes, although it’s not quite as straightforward as this. Before we go into the oddities, though, let’s look at a few examples of character constants. The following T-SQL declares several character variables and then retrieves their values:<\/p>\n
    <\/pre>\n
    By default, when you’re working with character string constants, you enclose them in singe quotes. If you’re working with Unicode strings, you precede the opening quote with an uppercase N. Anything enclosed in the quotes is considered part of that string, even if the characters are numbers, operators, or other types of symbols, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    some value<\/p>\n<\/td>\n
    		 <\/td>\n\n
    some value + 1<\/p>\n<\/td>\n
    		\n
    12345<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     In SQL Server, we can instead enclose character strings in double quotes by setting the QUOTED_IDENTIFIER<\/code> option to  OFF<\/code>, as shown in the following example:<\/p>\n
    <\/pre>\n
    When the QUOTED_IDENTIFIER<\/code> option is set to OFF<\/code>, we can use double quotes just like single quotes, as is the case with the @var1<\/code> declaration. The advantage of using double quotes is that you can pass in a special character, such as an apostrophe, without having to escape it by adding a second apostrophe, as we had to do for @var3<\/code>. The SELECT<\/code> statement now returns the results shown in the following table:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    Var3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    its value<\/p>\n<\/td>\n
    		\n
    it’s a value<\/p>\n<\/td>\n
    		\n
    it’s a value<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Be aware, however, that the QUOTED_IDENTIFIER<\/code> option has other implications besides being able to use double quotes for string constants. For example, the option affects the way identifiers are handled in T-SQL. In addition, the SQL Server Native Client Provider and ODBC driver set this option to ON<\/code>, which can impact your code if you had set the option to OFF<\/code>. For these reasons you’re usually better off leaving the option set to ON<\/code>.<\/p>\n
    As previously mentioned, you typically don’t enclose numerical constants in quotes, even if they include currency symbols or scientific notation. For example, the following T-SQL declares four types of numerical variables, sets their values, and retrieves those values:<\/p>\n
    <\/pre>\n
    If we had enclosed the constants in quotes, the database engine would have interpreted the values as character data and have tried to convert it to the type of the applicable variable. For the first three variables, an implicit conversion would have worked fine, but it would mean unnecessary work on the part of the database engine. For the last variable, the engine would have returned the following error:<\/p>\n
    <\/pre>\n
    When we specify a numerical constant without quotes, the database engine assumes a numerical type consistent with the value. For example, the value 147<\/code> is assumed to be an INT<\/code>, so no implicit conversion is necessary. The following table shows the results returned by the  SELECT<\/code> statement.<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    147<\/p>\n<\/td>\n
    		\n
    10.5800<\/p>\n<\/td>\n
    		\n
    68.24<\/p>\n<\/td>\n
    		\n
    87.9<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Even with numeric constants, an implicit conversion might be called for. In the following SELECT<\/code> statement, the constant 1000<\/code> is compared the value in the ListPrice<\/code> column, which is configured with the MONEY<\/code> data type:<\/p>\n
    <\/pre>\n
    The 1000<\/code> is treated as an INT<\/code> value, so it must be implicitly converted to the MONEY<\/code> type. But that’s not all what’s going on in the WHERE<\/code> clause. Notice that it also includes a string constant (blue<\/code>). The constant is compared to the Color<\/code> column, which is configured with the NVARCHAR<\/code> data type. This means that the database engine must convert the VARCHAR<\/code> string to the NVARCHAR<\/code> type, which gives us the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 46<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 50<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 54<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 60<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 60<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 46<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 50<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 54<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 60<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 46<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 50<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 54<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Like string constants, date and time values are also enclosed in single quotes, even though the values are stored as integers, as you can see in the following example:<\/p>\n
    <\/pre>\n
    The date and time data types will accept various formats when passing in a constant, as long as the value is enclosed in quotes. The following table shows the results returned by the SELECT<\/code> statement:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2014-08-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    2014-07-28 16:24:27.293<\/p>\n<\/td>\n
    		\n
    2014-07-30<\/p>\n<\/td>\n
    		\n
    14:05:55.0010000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     You should also be aware of how to treat constants when working with some of the other data types. For example, a VARBINARY<\/code> value takes an 0x<\/code> prefix but is not enclosed in quotes. The same goes for a BIT<\/code> value. However, a UNIQUEIDENTIFIER<\/code> value is enclosed in quotes, as shown in the following T-SQL:<\/p>\n
    <\/pre>\n
     Our SELECT<\/code> statement now returns the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    0x14AD<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    CC421A37-E462-4AE0-8451-38F837FC5A1A<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Again, we generally enclose string values in quotes and leave the quotes off for numerical values, but as you’ve seen, some areas can be somewhat gray. If you get confused, check out the MSDN topic “Constants<\/a>.”<\/p>\n<\/div>\n\n\n\n
    “Are there any advantages to using a COALESCE expression rather than a CASE expression when checking for the first non-NULL value in a list of values?”<\/h3>\n\n\n\n
    \n
    A COALESCE<\/code> expression is a syntactic shortcut for a CASE<\/code> expression. That means, when the query optimizer gets ahold of a COALESCE<\/code> expression, it rewrites it as a CASE<\/code> one, so in that sense, they’re one in the same. Let’s look at a couple of examples. In the first one, we use COALESCE<\/code> to return the first column that is not a NULL<\/code> value:<\/p>\n
    <\/pre>\n
    For this particular product, the Size<\/code> column and SizeUnitMeasureCode<\/code> column each contains a NULL<\/code> value, but the WeightUnit<\/code> column contains an actual value, G<\/code>, so the COALESCE<\/code> expression returns that value, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Size<\/b><\/p>\n<\/td>\n
    		\n
    SizeUnit<\/b><\/p>\n<\/td>\n
    		\n
    WeightUnit<\/b><\/p>\n<\/td>\n
    		\n
    FirstNotNull<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Rear Wheel<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    G <\/p>\n<\/td>\n
    		\n
    G <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Now let’s look at what happens when we change the  COALESCE<\/code> expression to a CASE<\/code> expression:<\/p>\n
    <\/pre>\n
    The SELECT<\/code> statement returns the same results as the previous example. That said, the COALESCE<\/code> expression is a lot simpler, although the logic of the CASE<\/code> statement is easier to understand. In some cases, you might find that it’s better to use CASE<\/code> so your intent is clear to other developers.<\/p>\n
    One other consideration with COALESCE<\/code>. Developers sometimes run into problems with COALESCE<\/code> expressions because of data type issues. The value returned by COALESCE<\/code> is based on the data type of the input value with the highest precedence, which might or might not be the returned value. If an implicit conversion is required, and such a conversion is not permitted, COALESCE<\/code> will return an error. See the article “Questions about SQL Server Data Types You were Too Shy to Ask<\/a>” for more details about this particular issue.<\/p>\n
    All that said, figuring out the logic behind COALESCE<\/code> is not too difficult, so choosing between CASE<\/code> and COALESCE<\/code> often comes down to nothing more than personal preference.<\/p>\n<\/div>\n\n\n\n
    “I’m working on a query that uses the NOT IN operator in the WHERE clause. The operator checks values in a list returned by a subquery. When the returned list includes a NULL value, the query returns an empty resultset, even though I know I should be seeing results. Any idea what might be happening?”<\/h3>\n\n\n\n
    \n
    Using NOT<\/code> IN<\/code> can be tricky, especially when NULL<\/code> values are involved. Case in point. The following SELECT<\/code> statement includes a WHERE<\/code> clause that checks for specific colors:<\/p>\n
    <\/pre>\n
    Basically, what we’re doing is returning all products that are saleable items (FinishedGoodsFlag<\/code> =<\/code> 1<\/code>) and whose color is not one of the colors of the non-saleable products. Unfortunately, when we run this query we receive an empty resultset.<\/p>\n
    If you’re familiar with the data, you might be surprised by these results. After all, the table contains 295 rows of saleable products. Either there are no colors unique to saleable items, or something is wrong.<\/p>\n
    We can investigate the problem by first running the subquery in the example above separate from the outer query:<\/p>\n
    <\/pre>\n
    The following table shows the subquery’s results, which includes three distinct items:<\/p>\n\n\n\n\n\n\n
    \n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, we have black and silver non-saleable items, along with those items for which no color has been assigned (the NULL<\/code> value). Now let’s modify the subquery to instead retrieve the colors of the orderable products:<\/p>\n
    <\/pre>\n
    This time around, we again receive the same three values, along with another seven colors, as shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    Blue<\/p>\n<\/td>\n<\/tr>\n
    \n
    Grey<\/p>\n<\/td>\n<\/tr>\n
    \n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver\/Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Yellow<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly something is wrong. Rows that contain those seven additional colors should have been included in our original query results. The problem, it turns out, is the NULL<\/code> value. Let’s recast the logic of the WHERE<\/code> clause into several separate predicates, connected by the AND<\/code> logical operator:<\/p>\n
    <\/pre>\n
    Essentially, our NOT<\/code> IN<\/code> operator indicates that the value in the Color<\/code> column should not be Black<\/code> and<\/i> should not be Silver<\/code> and<\/i> should not be  NULL<\/code>, in addition to being a saleable product. The problem is with the equation Color<\/code> <><\/code> NULL<\/code>. For a row to be returned, all conditions must evaluate to true; however, a NULL<\/code> cannot evaluate to true or false, which means the condition as a whole will never evaluate to true and no rows will ever be returned. If we were to run this statement, we would once again receive an empty resultset.<\/p>\n
    One way around this predicament is to add a second predicate to the WHERE<\/code> clause in our subquery:<\/p>\n
    <\/pre>\n
    Now our subquery returns only the values Black<\/code> and Silver<\/code>, which means our outer query should return all rows except those whose Color<\/code> value is one of the two colors. The following table shows part of the results, although the query actually returns 120 rows:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 58<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Red<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bike Socks, M<\/p>\n<\/td>\n
    		\n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bike Socks, L<\/p>\n<\/td>\n
    		\n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Blue<\/p>\n<\/td>\n
    		\n
    Blue<\/p>\n<\/td>\n<\/tr>\n
    \n
    AWC Logo Cap<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, S<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, M<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, L<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, XL<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 62<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 44<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 48<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you were to run this query, you would see the Color<\/code> values black<\/code> and silver<\/code> are not included. However, what’s odd about this is that rows with NULL<\/code> for a color are not included either, even though NULL<\/code> is no longer being returned by the subquery. This might be fine in some cases, but in others you might want to see those values. Before we start looking for a workaround, however, another consideration is performance. Many SQL Server folks recommend using an EXISTS<\/code> operator rather than an IN<\/code> operator anyway. So let’s rewrite the last statement using NOT<\/code> EXISTS<\/code>:<\/p>\n
    <\/pre>\n
    Our WHERE<\/code> clause now specifies that we include only saleable products, that they be a known color, and that the color not exist within the list of non-orderable colors. As a result, the SELECT<\/code> statement returns the same results as the preceding one, 120 rows. And if we want to include the saleable products with a Color<\/code> value of NULL<\/code>, we can simply remove the IS<\/code> NOT<\/code> NULL<\/code> expression:<\/p>\n
    <\/pre>\n
    Our statement now returns 170 rows, which include those rows with a Color<\/code> value of NULL<\/code>. In theory, this statement should perform better than the ones using NOT<\/code> NULL<\/code>, but you might want to test that part out yourself to be sure. Just know that using NOT<\/code> IN<\/code> with NULL<\/code> values can return unexpected results, so be prepared.<\/p>\n<\/div>\n\n\n\n
     “What the heck is a ‘modulo’?”<\/h3>\n\n\n\n
    \n
    Remember when you first learned long division? You had the dividend, the divisor, and the remainder. The dividend is the number you start with. The divisor is the number you divide into the dividend. The remainder is what’s left. Modulo is a mathematical operator that returns the remainder.<\/p>\n
    Here’s how it works. In the follow example, @var1<\/code> serves as our dividend, and @var2<\/code> serves as our divisor:<\/p>\n
    <\/pre>\n
    The first expression in our SELECT<\/code> clause uses the division operator (\/<\/code>) to divide @var1<\/code> by @var2<\/code>. The second expression uses the modulo operator (%<\/code>) to calculate the remainder that’s produced when you divide these two numbers. The following table shows the results.<\/p>\n\n\n\n\n
    \n
    Division<\/b><\/p>\n<\/td>\n
    		\n
    Remainder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    No surprise here. When you divide 32 by 8, you get 4, with a remainder of 0. But now let’s produce a remainder other than 0:<\/p>\n
    <\/pre>\n
    This time around, we simply set @var1<\/code> to equal 36, rather than 32. Now our results show a remainder of 4.<\/p>\n
    That’s all there is to using the modulo operator. And you can use it with other numeric types:<\/p>\n
    <\/pre>\n
    In this case, we’re dividing 55.566 by 5 to come up with the following results:<\/p>\n\n\n\n\n
    \n
    Division<\/b><\/p>\n<\/td>\n
    		\n
    Remainder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    11.111200000000000<\/p>\n<\/td>\n
    		\n
    0.5560<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the expression that contains the modulo operator returns a value of 0.5560<\/code>. As for the expression that contains the division operator, the operator’s returned value is based on the argument type with the highest precedence, which in this case is DECIMAL<\/code>.<\/p>\n<\/div>\n\n\n\n
    “Are all WHEN and ELSE clauses in a CASE expression evaluated if the first WHEN clause evaluates to TRUE?”<\/h3>\n\n\n\n
    \n
    According to the SQL Server documentation, a CASE<\/code> expression evaluates each condition sequentially and stops with the first condition that evaluates to true. In other words, the expression will short circuit when a condition is met, saving the database engine from having to perform unnecessary processing. This is a good thing. The less work the engine has to perform, the better.<\/p>\n
    To get a sense of how this works, let’s look at an example. The following SELECT<\/code> statement includes a CASE<\/code> expression that sets up three possible conditions when concatenating the name-related columns in the vSalesPerson<\/code> view:<\/p>\n
    <\/pre>\n
    The first CASE<\/code> condition checks whether the MiddleName<\/code> value is NULL<\/code>. If it is, the FirstName<\/code> and LastName<\/code> columns are concatenated and the expression stops running. If the column contains a value other than NULL<\/code>, the expression moves on to the next condition, which checks the length of the MiddleName<\/code> value. If it equals 1<\/code>, the three columns are concatenated and a period is added to the middle initial. The expression will then stop at that point. Otherwise, the ELSE<\/code> condition is applied and all three columns are concatenated as is. The database engine repeats the process for each row and returns the following results:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    Tsvi Michael Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The CASE<\/code> expression’s ability to short circuit can help to avoid unnecessary processing when it can be substituted for other statement elements. For example, suppose we define the following SELECT<\/code> statement:<\/p>\n
    <\/pre>\n
    Nothing flashy here. For each row, the database engine tests for the three specified conditions and determines whether that row is returned. Consequently, the resultset includes any saleable product that is either black or red, which means 127 rows evaluate to true. Part of those results are shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Black, 58<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 58<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Red<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Black<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 62<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 44<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 48<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 52<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 56<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 58<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 60<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 62<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Suppose we want to reduce some of the work that the database engine has to perform. We can instead use a CASE<\/code> expression in the WHERE<\/code> clause, as shown in the following SELECT<\/code> statement:<\/p>\n
    <\/pre>\n
    If the Color<\/code> value equals black<\/code>, the condition evaluates to true and the expression stops running, thus avoiding any unnecessary processing. Yet the statement returns the same results as the preceding example.<\/p>\n
    Under the right circumstances, using a CASE<\/code> expression in this way can be a handy way to reduce your workloads. However, a CASE<\/code> expression might not always short circuit in the way you hope. For example, suppose your T-SQL includes logic is similar to that shown in the following example:<\/p>\n
    <\/pre>\n
    All we’re doing is declaring an INT<\/code> variable, setting its value to 0<\/code>, and using a CASE<\/code> expression to retrieve a specific value. Based on our understanding of the CASE<\/code> expression’s ability to short circuit, we would expect the expression’s first condition to evaluate to true and the expression to then stop. However, what we get from our SELECT<\/code> statement is the following error:<\/p>\n
    <\/pre>\n
    The error shows that the ELSE<\/code> condition did in fact run, even though it shouldn’t have. It turns out that under certain circumstances, a CASE<\/code> expression doesn’t behave like it’s supposed to behave. The problem appears to be related to aggregate functions (at least in part), so be aware that in some cases a CASE<\/code> expression might not deliver the benefits you’re hoping for and you might have to come up with a workaround to make the CASE<\/code> expression work the way you want it to.<\/p>\n<\/div>\n\n\n\n
    “What’s the difference between the two ‘not equal to’ operators (<> and !=)?”<\/h3>\n\n\n\n
    \n
    In T-SQL, the two comparisons operators are syntactically equivalent. They each perform a Boolean comparison between two expressions. If those expressions return non-NULL<\/code>, unequal values, the comparison evaluates to true, otherwise, it evaluates to false. However, if either value is  NULL<\/code>, the evaluation returns a NULL<\/code>. Here’s a sample of the two operators in action:<\/p>\n
    <\/pre>\n
     The variable is set to a value of  1<\/code>, and in each SELECT<\/code> statement, that variable is compared to the constant 2<\/code>. As a result, each SELECT<\/code> statement returns the value not<\/code> equal<\/code> because each comparison evaluates to true. Even in terms of performance, there doesn’t appear to be much difference between the two.<\/p>\n
    However, there is one difference you should be aware of. The <><\/code> operator conforms to ANSI standards; the !=<\/code> operator does not, despite that fact that a number of relational database systems support both operators. That said, even if there is only the slightest chance that you might port your script to another system, you’re better off sticking with the  <><\/code> operator.<\/p>\n<\/div>\n\n\n\n
    “I’ve come across operators such as += in variable assignment SET statements. What do they mean?”<\/h3>\n\n\n\n
    \n
    The add-equals operator (+=<\/code>) is a compound operator, one of several types of compound operators supported in SQL Server. A compound operator takes the original value and in some way amends it, rather than replacing it. For example, the following T-SQL uses the add-equals operator to update the value of the @var1<\/code> variable:<\/p>\n
    <\/pre>\n
    We start by declaring @var1<\/code> and setting its value to 2<\/code>. We then use a SET<\/code> statement to add a value of 1<\/code> to the original value. The SELECT<\/code> statement returns the new value, which is 3<\/code>.<\/p>\n
    The add-equals operator makes it possible to add a value without having to specify the original value. Without the add-equals operator we would have to rewrite our SET<\/code> statement as follows:<\/p>\n
    <\/pre>\n
    The add-equals operator merely provides the shorthand necessary to avoid having to explicitly specify the original value, yet the SELECT<\/code> statement still returns a value of 3<\/code>. Plus, SQL Server supports other compound operators as well. For example, the following T-SQL subtracts 1<\/code> from the original value:<\/p>\n
    <\/pre>\n
    In this case, we’re using the subtract-equals (-=<\/code>) operator in our SELECT<\/code> statement, rather than the add-equals operator. The subtract-equals operator works the same way as the add-equals operator, except that the second value is subtracted rather than added. As a result, our SELECT<\/code> statement now returns a value of 1<\/code>.<\/p>\n
    Another example of a compound operator is the multiply-equals operator, which is shown in the following example:<\/p>\n
    <\/pre>\n
    In this case, the SET<\/code> statement uses the multiply-equals operator to multiple the variable value 2<\/code> by the constant value 3<\/code>, giving us a total of 6<\/code>. And compound operators are not limited to mathematical expressions. For example, the following T-SQL includes a SET<\/code> statement that uses the add-equals operator to concatenate values:<\/p>\n
    <\/pre>\n
    In this example, the SET<\/code> statement adds the string value and<\/code> then<\/code> some<\/code> (including the opening space) to the variable value some<\/code> value<\/code>, giving us the value some<\/code> value<\/code> and<\/code> then<\/code> some<\/code>. There are other compound operators as well, such as divide-equals (\/=<\/code>) and modulo-equals (%=<\/code>). Check out the MSDN topic “Compound Operators<\/a>” for more information.<\/p>\n<\/div>\n\n\n\n
    + CONVERT(NVARCHAR(20), CAST(@var2 AS MONEY), 1) AS amount2,   ‘<\/p>\n\n\n\n
    Each expression in the SELECT<\/code> list references one of the variables as its source data and is made up of multiple components. What provides the final format, however, is the CONVERT<\/code> function. The function requires that the source data be either the MONEY<\/code> or SMALLMONEY<\/code> type. When you call the function, you specify a second argument along with the source value. That argument determines the format of the data. In this case, we specify 1<\/code> so the outputted data includes commas every three digits to the left of the decimal point. We also concatenate the value with a dollar sign to give it that final touch, as shown in the following results:<\/p>\n\n\n\n
    \n
    amount1<\/b><\/p>\n<\/td>
    		\n
    amount2<\/b><\/p>\n<\/td>
    		\n
    amount3<\/b><\/p>\n<\/td><\/tr>
    \n
    $555,555,555.56<\/p>\n<\/td>
    		\n
    $555,555,555.56<\/p>\n<\/td>
    		\n
    $555,555,555.56<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
     Overall, this isn’t too painful a process, unless you’re working with currencies that follow a different format, such as the Swiss franc, which takes apostrophes rather than commas. In such cases, you might have to add yet another function, such as REPLACE<\/code>, to substitute the commas with apostrophes.<\/p>\n\n\n\n
    Fortunately, since the release of SQL Server 2012, you’ve been able to instead use the FORMAT<\/code> function to output your numbers to a currency format. The FORMAT<\/code> function returns a value in a specified format, based on a specified culture. To demonstrate how this works, let’s first recast our preceding expressions into ones that use the FORMAT<\/code> function to configure the data as US dollar amounts:<\/p>\n\n\n\n
    <\/pre>\n\n\n\n
    Notice that we must first cast the character data (@var1<\/code>) to the MONEY<\/code> data type, but we can use the DECIMAL<\/code> variable (@var2<\/code>) as is. The second FORMAT<\/code> argument (c<\/code>) specifies that we’re formatting currency, and the third argument specifies the US symbol (en-us<\/code>) for the cultural context. The SELECT<\/code> statement returns the same results as the previous SELECT<\/code> statement, with all values returned as the NVARCHAR<\/code> type.<\/p>\n\n\n\n
    However, with the FORMAT<\/code> function, we can specify other cultures. For example, the following example uses the German symbol (de-de<\/code>) to provide the cultural context:<\/p>\n\n\n\n
    <\/pre>\n\n\n\n
    Now our results are specific to how the euro is represented in Germany, as shown in the following table:<\/p>\n\n\n\n
    \n
    amount1<\/b><\/p>\n<\/td>
    		\n
    amount2<\/b><\/p>\n<\/td>
    		\n
    amount3<\/b><\/p>\n<\/td><\/tr>
    \n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>
    		\n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>
    		\n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
     Certainly, the FORMAT<\/code> function is a handy tool if you want your results to be displayed in a specific currency. But just to reiterate, such formatting is generally handled by the calling application, with the source data retrieved in its raw state.<\/p>\n\n\n\n
    “I see the plus sign (+) used in a variety of ways in T-SQL script, but I can’t always make sense of how SQL Server arrives at the results it does. Is there a “right way” to use plus signs?”<\/h3>\n\n\n\n
    \n
    SQL Server is a tricky devil. As you’ve discovered, plus signs have all sorts of meaning in T-SQL. You can use them to concatenate string values, add numerical values together, or indicate that a numerical value is a positive number, rather than negative. The following SELECT<\/code> statement demonstrates how to both concatenate and add values:<\/p>\n
    <\/pre>\n
    For the FullName<\/code> column, we’ve used the plus sign to concatenate the FirstName<\/code> and  LastName<\/code> columns with a space in between. For the TwoYearSales<\/code> column, we’ve used the plus sign to add the SalesLastYear<\/code> and  SalesYTD<\/code> columns together. In both cases, the database engine knows when to concatenate the values or when to add them together, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    SalesLastYear<\/b><\/p>\n<\/td>\n
    		\n
    SalesYTD<\/b><\/p>\n<\/td>\n
    		\n
    TwoYearSales<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Blythe<\/p>\n<\/td>\n
    		\n
    $1,750,406.48<\/p>\n<\/td>\n
    		\n
    $3,763,178.18<\/p>\n<\/td>\n
    		\n
    $5,513,584.66<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Mitchell<\/p>\n<\/td>\n
    		\n
    $1,439,156.03<\/p>\n<\/td>\n
    		\n
    $4,251,368.55<\/p>\n<\/td>\n
    		\n
    $5,690,524.58<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    $1,997,186.20<\/p>\n<\/td>\n
    		\n
    $3,189,418.37<\/p>\n<\/td>\n
    		\n
    $5,186,604.57<\/p>\n<\/td>\n<\/tr>\n
    \n
    Garrett Vargas<\/p>\n<\/td>\n
    		\n
    $1,620,276.90<\/p>\n<\/td>\n
    		\n
    $1,453,719.47<\/p>\n<\/td>\n
    		\n
    $3,073,996.36<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi Reiter<\/p>\n<\/td>\n
    		\n
    $1,849,640.94<\/p>\n<\/td>\n
    		\n
    $2,315,185.61<\/p>\n<\/td>\n
    		\n
    $4,164,826.55<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    $1,927,059.18<\/p>\n<\/td>\n
    		\n
    $1,352,577.13<\/p>\n<\/td>\n
    		\n
    $3,279,636.31<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu Ito<\/p>\n<\/td>\n
    		\n
    $2,073,506.00<\/p>\n<\/td>\n
    		\n
    $2,458,535.62<\/p>\n<\/td>\n
    		\n
    $4,532,041.62<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jos\u00e9 Saraiva<\/p>\n<\/td>\n
    		\n
    $2,038,234.65<\/p>\n<\/td>\n
    		\n
    $2,604,540.72<\/p>\n<\/td>\n
    		\n
    $4,642,775.37<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Campbell<\/p>\n<\/td>\n
    		\n
    $1,371,635.32<\/p>\n<\/td>\n
    		\n
    $1,573,012.94<\/p>\n<\/td>\n
    		\n
    $2,944,648.25<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete Mensa-Annan<\/p>\n<\/td>\n
    		\n
    $0.00<\/p>\n<\/td>\n
    		\n
    $1,576,562.20<\/p>\n<\/td>\n
    		\n
    $1,576,562.20<\/p>\n<\/td>\n<\/tr>\n
    \n
    Lynn Tsoflias<\/p>\n<\/td>\n
    		\n
    $2,278,548.98<\/p>\n<\/td>\n
    		\n
    $1,421,810.92<\/p>\n<\/td>\n
    		\n
    $3,700,359.90<\/p>\n<\/td>\n<\/tr>\n
    \n
    Rachel Valdez<\/p>\n<\/td>\n
    		\n
    $1,307,949.79<\/p>\n<\/td>\n
    		\n
    $1,827,066.71<\/p>\n<\/td>\n
    		\n
    $3,135,016.50<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jae Pak<\/p>\n<\/td>\n
    		\n
    $1,635,823.40<\/p>\n<\/td>\n
    		\n
    $4,116,871.23<\/p>\n<\/td>\n
    		\n
    $5,752,694.62<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ranjit Varkey Chudukatil<\/p>\n<\/td>\n
    		\n
    $2,396,539.76<\/p>\n<\/td>\n
    		\n
    $3,121,616.32<\/p>\n<\/td>\n
    		\n
    $5,518,156.08<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    There’s no real mystery here. When we use the plus sign with only character data, the database engine concatenates the values. In this case, the plus sign is considered a string concatenation operator. When we use the plus sign with numerical values, the database engine adds the values together. Under these circumstances, the plus sign is considered an addition operator.<\/p>\n
    Not surprisingly, when we mix and match the types of data, our results are less predictable. Take a look at the following example:<\/p>\n
    <\/pre>\n
    As you can see, we’ve declared seven variables, each defined with a different type. We then use the plus sign to combine the variables in different ways, giving us the following results:<\/p>\n\n\n\n\n
    \n
    var1var2<\/b><\/p>\n<\/td>\n
    		\n
    var3var6<\/b><\/p>\n<\/td>\n
    		\n
    var4var5<\/b><\/p>\n<\/td>\n
    		\n
    var7var3<\/b><\/p>\n<\/td>\n
    		\n
    var6var7<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    TestVarCX<\/p>\n<\/td>\n
    		\n
    1299.99<\/p>\n<\/td>\n
    		\n
    1001.0001<\/p>\n<\/td>\n
    		\n
    2015-05-21 00:00:00.000<\/p>\n<\/td>\n
    		\n
    2017-04-19 23:45:36.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As we would expect, when we pair @var1<\/code> with @var2<\/code>, both character types, we end up with a concatenated string. When we pair @var3<\/code> to @var6<\/code>, both numerical types, the values are added together, even though @var3<\/code> and @var6<\/code> are different types.<\/p>\n
    Now we get to our third pairing: @var4<\/code> +<\/code> @var5<\/code>. This time around, we’re adding a DECIMAL<\/code> value to BIT<\/code> value. However, the database engine still treats both variables as numerical values and adds them together, even though SQL Server documentation suggests that you can’t do this with the BIT<\/code> type. (And really, why would you?)<\/p>\n
    The next item in the select list, @var7<\/code> +<\/code> @var3<\/code>, pairs a DATETIME<\/code> value with a SMALLINT<\/code> value. This time, we get a date that is 300 days later than the original date. When your expression includes the plus sign, the database engine defaults to the data type with the highest precedence, in this case,  DATETIME<\/code>. Based on the type, the engine determines whether to use the plus sign to concatenate values or add them together. If the data type with the highest precedence is a numeric data type, the engine attempts to add the values. In this case, the engine adds the SMALLINT<\/code> value to the DATETIME<\/code> value as the specified number of days. (A DATETIME<\/code> value is actually stored as two integers.)<\/p>\n
    Finally, we pair a MONEY<\/code> date type with the DATETIME<\/code> date type (@var6<\/code> +<\/code> @var7<\/code>). Once again, DATETIME<\/code> takes precedence over MONEY<\/code>, so the database engine adds 999.99 days to our original date to come up with a date in 2017.<\/p>\n
    We can verify how data type precedence works with plus signs by using the SQL_VARIANT_PROPERTY<\/code> function to retrieve the data type of the expressions that pair the different variables:<\/p>\n
    <\/pre>\n
    The SELECT<\/code> statement returns the returned data type for each expression, as shown in the following table:<\/p>\n\n\n\n\n
    \n
    var1var2<\/b><\/p>\n<\/td>\n
    		\n
    var3var6<\/b><\/p>\n<\/td>\n
    		\n
    var4var5<\/b><\/p>\n<\/td>\n
    		\n
    var7var3<\/b><\/p>\n<\/td>\n
    		\n
    var6var7<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    nvarchar<\/p>\n<\/td>\n
    		\n
    money<\/p>\n<\/td>\n
    		\n
    decimal<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     In each case, the expression returns the type with the highest precedence and determines how the plus sign will be used.<\/p>\n
    So far in our examples, the database engine has been able to add or concatenate values without a problem because the engine could implicitly convert the data type with the lowest precedence to the type with the highest. But this isn’t always the case:<\/p>\n
    <\/pre>\n
    This time around, we’re using the plus sign to pair a NVARCHAR<\/code> value with a SMALLINT<\/code> value. Because SMALLINT<\/code> has precedence over NVARCHAR<\/code>, the database engine attempts to convert the value TestVar<\/code> to the SMALLINT<\/code> type, which of course is not possible. Consequently, the database engine returns the following error message:<\/p>\n
    <\/pre>\n
    The only way we can pair a numerical value with a string value is to concatenate them. In order to do so, we must explicitly convert the SMALLINT<\/code> value to a character data type:<\/p>\n
    <\/pre>\n
    Now our SELECT<\/code> statement returns the value TestVar300<\/code>.<\/p>\n
    There’s one other use of the plus sign that you should be aware of-as a unary plus operator. SQL Server supports two types of unary operators, plus and minus, which are used to designate whether a numeric value is positive or negative:<\/p>\n
    <\/pre>\n
    All we’ve done here is to designate our two literal values as positive and negative numbers. The SELECT<\/code> statement returns the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    123.45<\/p>\n<\/td>\n
    		\n
    -123.45<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Be aware, however, that you cannot use the positive unary operator to convert a negative number to a positive number. For that, you need to use the ABS<\/code> mathematical function.<\/p>\n<\/div>\n\n\n\n
    “The NULLIF expression makes no sense. I would think you’d use it to somehow ferret out a NULL value or to test a condition for a NULL value. But it appears to merely return a NULL if two values are equal. Is that all it’s doing?”<\/h3>\n\n\n\n
    \n
    That’s part of what it’s doing. When a NULLIF<\/code> expression compares two values, it returns the first value if the two values are not equal. If they are equal, the expression returns a NULL<\/code> value of the same data type as the first specified value. Here’s what a NULLIF<\/code> expression looks like in action:<\/p>\n
    <\/pre>\n
    In this case, the NULLIF<\/code> expression returns valueA<\/code> because the two values are not equal. If they were equal, the returned value would be NULL<\/code>. In addition, the returned value would be NULL<\/code> if the first value were NULL<\/code>.<\/p>\n
    When you know how a NULLIF<\/code> expression works, it doesn’t seem so bad, although its use still has a tendency to cause confusion. Luckily, we can achieve the same results by using a CASE<\/code> statement:<\/p>\n
    <\/pre>\n
    The advantage of the CASE<\/code> statement is that it’s a lot easier to understand. Sure, NULLIF<\/code> provides simpler syntax, but if it creates confusion, what’s the point?<\/p>\n<\/div>\n\n\n\n
    “Can you use expressions in data manipulation language (DML) statements?”<\/h3>\n\n\n\n
    \n
    You can use expressions wherever T-SQL syntax permits them, including DML statements. Take a look at part of the syntax for the UPDATE<\/code> statement’s SET<\/code> clause:<\/p>\n
    <\/pre>\n
     According to SQL Server Books Online, the expression placeholder can be a “variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value.” This pretty much defines the term expression<\/i> as we understand it. Note, however, that any expression you define must conform to the rules associated with the particular T-SQL statement in which the expression is defined. For example, an UPDATE<\/code> statement’s UPDATE<\/code> clause can include the TOP<\/code> clause, as shown in the following syntax:<\/p>\n
    <\/pre>\n
     In this case, expression “specifies the number or percent of rows that will be updated.” In other words, you cannot specify a non-numerical string value as your expression, as you can with other types of expressions. That said, it’s still considered an expression.<\/p>\n
    So, yes, you can use an expression in a DML statement. In fact, they’re used all the time. Let’s look at an example. Suppose we create the following table in our database and insert a row of data:<\/p>\n
    <\/pre>\n
    Now let’s use an UPDATE<\/code> statement to modify that row of data:<\/p>\n
    <\/pre>\n
    Our SET<\/code> clause is updating two columns. Each SET<\/code> definition includes an expression that either adds the @AmtReceived<\/code> value to the target column or subtracts that value. If we run the SELECT<\/code> statement after the table has been updated, we come up with the following results:<\/p>\n\n\n\n\n
    \n
    ProductID<\/b><\/p>\n<\/td>\n
    		\n
    InStock<\/b><\/p>\n<\/td>\n
    		\n
    OnOrder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    67<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you’re writing T-SQL statements, you’re writing expressions, even if you don’t realize it. That’s why it’s important to refer back to the statement’s syntax whenever you’re uncertain how to define a particular element. Expressions are supported throughout most statements. The syntax for the UPDATE<\/code> statement, for example, includes the expression placeholder in 10 different places, which suggests that many of us might not be using some statements to their fullest capacity.<\/p>\n<\/div>\n\n\n\n
     “The CASE expression allows me to leave out the ELSE clause. Is it that important to include it?”<\/h3>\n\n\n\n
    \n
    Yes, but…. The ELSE<\/code> clause specifies the value to return if no other comparison operations (WHEN...THEN<\/code>) evaluate to true. If you omit the ELSE<\/code> clause, the database engine instead returns a NULL<\/code> value. A NULL<\/code> value might be fine, if that’s what you want. But what if it’s not? Take a look at the following example, which uses a CASE<\/code> expression to determine how to return an employee’s full name:<\/p>\n
    <\/pre>\n
    If the MiddleName<\/code> column is NULL<\/code>, that column is not included in the expression. Only the first and last names are concatenated. If the MiddleName<\/code> value is an initial, a period is added to the initial and concatenated with the first and last names. Otherwise, all three names are concatenated, as shown in the following results:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    Tsvi Michael Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now let’s modify the CASE<\/code> expression by removing the ELSE<\/code> clause:<\/p>\n
    <\/pre>\n
    This time a NULL<\/code> is returned if the MiddleName<\/code> column has a value that is more than an initial, as the following table shows:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly, you would not want to delete the ELSE<\/code> clause in this instance. You could, however, add a WHEN...THEN<\/code> condition that replaces the ELSE<\/code> clause, in which case, you’re still returning NULL<\/code> of none of the conditions are met, which might be fine under some circumstances. Consider the following example:<\/p>\n
    <\/pre>\n
    If the SalesQuota<\/code> value is great than 250,000, a true<\/code> is returned. If the value is less than or equal to 250,000, we get a false<\/code>. However, if the column is NULL<\/code>, neither condition can be true, so the CASE<\/code> expression returns a NULL<\/code>, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    HigherQuota<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Stephen Jiang<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Blythe<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Mitchell<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi Reiter<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu Ito<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Campbell<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete Mensa-Annan<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Syed Abbas<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Amy Alberts<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    You can choose to include an ELSE<\/code> clause and then provide a default value, such as N\/A<\/code> (or even NULL<\/code>), whether or not you think it’s necessary. In fact, many developers think you should never omit the ELSE<\/code> clause, even if you believe it is impossible for one of the WHEN...THEN<\/code> conditions not to be met. Someone might make a change to the database that affects the statement or your thinking about possible outcomes might not be correct.<\/p>\n<\/div>\n\n\n\n
    “Can you shed light on the order in which the database engine processes the components in an expression?”<\/h3>\n\n\n\n
    \n
    The way in which an expression’s elements are processed depends on several factors: their listed order, whether any subset of elements is enclosed in parentheses, and what operators are used to connect the various elements. Parentheses group elements together to ensure that those elements are processed as a unit before being incorporated into the rest of the expression. In addition, the expression as a whole adheres to the concept of operator precedence,<\/i> in which the operator types determine the order in which the expression is evaluated. For example, the multiply and division arithmetic operators take precedence over the greater than (><\/code>) and lesser than (<<\/code>) comparison operations, which take precedence over logical operators such as AND<\/code> or ANY<\/code>. (You can find details about operator precedence in the MSDN topic “Operator Precedence<\/a>.”)<\/p>\n
    Let’s start with a few simple arithmetic-based expressions to give you a sense of how this works. The following example declares several INT<\/code> variables, assigns an expression to each one, and returns the values from all three:<\/p>\n
    <\/pre>\n
    All we’re doing in each expression is multiplying and adding and subtracting values. However, because we use parentheses in the second and third expressions, our results come up quite different:<\/p>\n\n\n\n\n
    \n
    num1<\/b><\/p>\n<\/td>\n
    		\n
    num2<\/b><\/p>\n<\/td>\n
    		\n
    num3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The @num1<\/code> expression is fairly straightforward. We multiple 3 by 2, add 5, and subtract 7, which gives us a total of 4.<\/p>\n
    However, in the @num2<\/code> expression, we enclose the 2<\/code> +<\/code> 5<\/code> elements in parentheses, so they’re processed first, giving us a total of 7. The 7 is then multiplied by the first 3, giving us 21. From there, we subtract 7, giving us a total of 14.<\/p>\n
    In the @num3<\/code> expression, the parentheses enclose the 2<\/code> +<\/code> 5<\/code> -<\/code> 7<\/code> elements, which are processed before the other elements. Because this returns 0, 3 is multiplied by 0, giving us a final result of 0.<\/p>\n
    Now let’s look at another example, which again declares three variables and applies different expressions to them:<\/p>\n
    <\/pre>\n
    Here’s what are results look like:<\/p>\n\n\n\n\n
    \n
    SalesDiff1<\/b><\/p>\n<\/td>\n
    		\n
    SalesDiff2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    6500<\/p>\n<\/td>\n
    		\n
    500<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     The first expression includes no parentheses. However, the multiply operator takes precedence over the addition and subtraction operators, so the multiplication is performed first. That means we first multiply the SalesQuota<\/code> value by 2 and the SalesYTD<\/code> value by 2, giving us the expression 6000<\/code> -<\/code> 2500<\/code> +<\/code> 3000<\/code>, which equals 6500.<\/p>\n
    The second expression does use parentheses. The first set of parentheses returns a value of 6000. The second set of parentheses actually contains a third set, and those are the elements evaluated first, before the outer set of elements. After they’re evaluated, that part of the expression enclosed in the second set of parentheses becomes @SalesLastYear<\/code>  +<\/code> 3000<\/code>, giving us a total of 5500. That 5500 is then subtracted from the 6000, which is how we end up with 500.<\/p>\n
    Now let’s look at an expression that incorporates character data as well as numerical data. In the following SELECT<\/code> statement, our WHERE<\/code> clause includes both the OR<\/code> and the AND<\/code> logical operators:<\/p>\n
    <\/pre>\n
    The WHERE<\/code> clause search condition contains three predicates separated by the AND<\/code> operator and the OR<\/code> operator. In its current state, the search condition states that the ListPrice<\/code> value must be greater than 2.5 times the StandardCost<\/code> value and<\/i> the ProductLine<\/code> value must equal r<\/code> or<\/i> the ProductLine<\/code> value must equal t<\/code>. According to the rules of operator precedence, the database engine first evaluates the AND<\/code> logical operator and then the OR<\/code> logical operator. Because of the placement of the operators, the first two predicates must both evaluate to true or<\/i> the third predicate must evaluate to true. In other words, all returned rows must meet the first two conditions or meet the third condition. As a result, the statement returns 59 rows that qualify. The following table shows part of the results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    StandardCost<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n
    		\n
    ProductLine<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Front Wheel<\/p>\n<\/td>\n
    		\n
    96.7964<\/p>\n<\/td>\n
    		\n
    218.01<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Rear Wheel<\/p>\n<\/td>\n
    		\n
    108.7844<\/p>\n<\/td>\n
    		\n
    245.01<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-Panniers, Large<\/p>\n<\/td>\n
    		\n
    51.5625<\/p>\n<\/td>\n
    		\n
    125.00<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bottle Cage<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, M<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, L<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 60<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Touring Frame – Yellow, 62<\/p>\n<\/td>\n
    		\n
    199.8519<\/p>\n<\/td>\n
    		\n
    333.42<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 46<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 50<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 54<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 46<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 50<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 54<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Suppose what we were really after was to return rows that met the first condition and<\/i> either the second or<\/i> third condition. In this case, we would have to modify our search condition slightly:<\/p>\n
    <\/pre>\n
    Notice that the second and third predicates are now enclosed in parentheses. Consequently, the database engine will first evaluate them as a unit. That means for all rows returned, ListPrice<\/code> must be greater than 2.5 times the value of StandardCost<\/code> and<\/i> ProductLine<\/code> must equal r<\/code> or<\/i> t<\/code>. The following table shows all the results now returned by the query.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    StandardCost<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n
    		\n
    ProductLine<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bottle Cage<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, M<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, L<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Tire Tube<\/p>\n<\/td>\n
    		\n
    1.4923<\/p>\n<\/td>\n
    		\n
    3.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Tire Tube<\/p>\n<\/td>\n
    		\n
    1.8663<\/p>\n<\/td>\n
    		\n
    4.99<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Tire<\/p>\n<\/td>\n
    		\n
    8.0373<\/p>\n<\/td>\n
    		\n
    21.49<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    ML Road Tire<\/p>\n<\/td>\n
    		\n
    9.3463<\/p>\n<\/td>\n
    		\n
    24.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Tire<\/p>\n<\/td>\n
    		\n
    12.1924<\/p>\n<\/td>\n
    		\n
    32.60<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Tire<\/p>\n<\/td>\n
    		\n
    10.8423<\/p>\n<\/td>\n
    		\n
    28.99<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Notice that we now have only nine rows and that each row meets both conditions for the ListPrice<\/code> and ProductLine<\/code> columns. By applying the parentheses, we have better controlled how the rules of operator precedence are applied.<\/p>\n<\/div>\n\n\n\n
    “I get confused on how to treat constants in my expressions. Is there a trick in using them?”<\/h3>\n\n\n\n
    \n
    A constant is a literal value that is either part of an expression or is the entire expression. The way in which you treat constants depends on the type of data. As a general rule, you enclose character data in single quotes, but leave numerical data without quotes, although it’s not quite as straightforward as this. Before we go into the oddities, though, let’s look at a few examples of character constants. The following T-SQL declares several character variables and then retrieves their values:<\/p>\n
    <\/pre>\n
    By default, when you’re working with character string constants, you enclose them in singe quotes. If you’re working with Unicode strings, you precede the opening quote with an uppercase N. Anything enclosed in the quotes is considered part of that string, even if the characters are numbers, operators, or other types of symbols, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    some value<\/p>\n<\/td>\n
    		 <\/td>\n\n
    some value + 1<\/p>\n<\/td>\n
    		\n
    12345<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     In SQL Server, we can instead enclose character strings in double quotes by setting the QUOTED_IDENTIFIER<\/code> option to  OFF<\/code>, as shown in the following example:<\/p>\n
    <\/pre>\n
    When the QUOTED_IDENTIFIER<\/code> option is set to OFF<\/code>, we can use double quotes just like single quotes, as is the case with the @var1<\/code> declaration. The advantage of using double quotes is that you can pass in a special character, such as an apostrophe, without having to escape it by adding a second apostrophe, as we had to do for @var3<\/code>. The SELECT<\/code> statement now returns the results shown in the following table:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    Var3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    its value<\/p>\n<\/td>\n
    		\n
    it’s a value<\/p>\n<\/td>\n
    		\n
    it’s a value<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Be aware, however, that the QUOTED_IDENTIFIER<\/code> option has other implications besides being able to use double quotes for string constants. For example, the option affects the way identifiers are handled in T-SQL. In addition, the SQL Server Native Client Provider and ODBC driver set this option to ON<\/code>, which can impact your code if you had set the option to OFF<\/code>. For these reasons you’re usually better off leaving the option set to ON<\/code>.<\/p>\n
    As previously mentioned, you typically don’t enclose numerical constants in quotes, even if they include currency symbols or scientific notation. For example, the following T-SQL declares four types of numerical variables, sets their values, and retrieves those values:<\/p>\n
    <\/pre>\n
    If we had enclosed the constants in quotes, the database engine would have interpreted the values as character data and have tried to convert it to the type of the applicable variable. For the first three variables, an implicit conversion would have worked fine, but it would mean unnecessary work on the part of the database engine. For the last variable, the engine would have returned the following error:<\/p>\n
    <\/pre>\n
    When we specify a numerical constant without quotes, the database engine assumes a numerical type consistent with the value. For example, the value 147<\/code> is assumed to be an INT<\/code>, so no implicit conversion is necessary. The following table shows the results returned by the  SELECT<\/code> statement.<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    147<\/p>\n<\/td>\n
    		\n
    10.5800<\/p>\n<\/td>\n
    		\n
    68.24<\/p>\n<\/td>\n
    		\n
    87.9<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Even with numeric constants, an implicit conversion might be called for. In the following SELECT<\/code> statement, the constant 1000<\/code> is compared the value in the ListPrice<\/code> column, which is configured with the MONEY<\/code> data type:<\/p>\n
    <\/pre>\n
    The 1000<\/code> is treated as an INT<\/code> value, so it must be implicitly converted to the MONEY<\/code> type. But that’s not all what’s going on in the WHERE<\/code> clause. Notice that it also includes a string constant (blue<\/code>). The constant is compared to the Color<\/code> column, which is configured with the NVARCHAR<\/code> data type. This means that the database engine must convert the VARCHAR<\/code> string to the NVARCHAR<\/code> type, which gives us the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 46<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 50<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 54<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 60<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 60<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 46<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 50<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 54<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 60<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 46<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 50<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 54<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Like string constants, date and time values are also enclosed in single quotes, even though the values are stored as integers, as you can see in the following example:<\/p>\n
    <\/pre>\n
    The date and time data types will accept various formats when passing in a constant, as long as the value is enclosed in quotes. The following table shows the results returned by the SELECT<\/code> statement:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2014-08-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    2014-07-28 16:24:27.293<\/p>\n<\/td>\n
    		\n
    2014-07-30<\/p>\n<\/td>\n
    		\n
    14:05:55.0010000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     You should also be aware of how to treat constants when working with some of the other data types. For example, a VARBINARY<\/code> value takes an 0x<\/code> prefix but is not enclosed in quotes. The same goes for a BIT<\/code> value. However, a UNIQUEIDENTIFIER<\/code> value is enclosed in quotes, as shown in the following T-SQL:<\/p>\n
    <\/pre>\n
     Our SELECT<\/code> statement now returns the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    0x14AD<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    CC421A37-E462-4AE0-8451-38F837FC5A1A<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Again, we generally enclose string values in quotes and leave the quotes off for numerical values, but as you’ve seen, some areas can be somewhat gray. If you get confused, check out the MSDN topic “Constants<\/a>.”<\/p>\n<\/div>\n\n\n\n
    “Are there any advantages to using a COALESCE expression rather than a CASE expression when checking for the first non-NULL value in a list of values?”<\/h3>\n\n\n\n
    \n
    A COALESCE<\/code> expression is a syntactic shortcut for a CASE<\/code> expression. That means, when the query optimizer gets ahold of a COALESCE<\/code> expression, it rewrites it as a CASE<\/code> one, so in that sense, they’re one in the same. Let’s look at a couple of examples. In the first one, we use COALESCE<\/code> to return the first column that is not a NULL<\/code> value:<\/p>\n
    <\/pre>\n
    For this particular product, the Size<\/code> column and SizeUnitMeasureCode<\/code> column each contains a NULL<\/code> value, but the WeightUnit<\/code> column contains an actual value, G<\/code>, so the COALESCE<\/code> expression returns that value, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Size<\/b><\/p>\n<\/td>\n
    		\n
    SizeUnit<\/b><\/p>\n<\/td>\n
    		\n
    WeightUnit<\/b><\/p>\n<\/td>\n
    		\n
    FirstNotNull<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Rear Wheel<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    G <\/p>\n<\/td>\n
    		\n
    G <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Now let’s look at what happens when we change the  COALESCE<\/code> expression to a CASE<\/code> expression:<\/p>\n
    <\/pre>\n
    The SELECT<\/code> statement returns the same results as the previous example. That said, the COALESCE<\/code> expression is a lot simpler, although the logic of the CASE<\/code> statement is easier to understand. In some cases, you might find that it’s better to use CASE<\/code> so your intent is clear to other developers.<\/p>\n
    One other consideration with COALESCE<\/code>. Developers sometimes run into problems with COALESCE<\/code> expressions because of data type issues. The value returned by COALESCE<\/code> is based on the data type of the input value with the highest precedence, which might or might not be the returned value. If an implicit conversion is required, and such a conversion is not permitted, COALESCE<\/code> will return an error. See the article “Questions about SQL Server Data Types You were Too Shy to Ask<\/a>” for more details about this particular issue.<\/p>\n
    All that said, figuring out the logic behind COALESCE<\/code> is not too difficult, so choosing between CASE<\/code> and COALESCE<\/code> often comes down to nothing more than personal preference.<\/p>\n<\/div>\n\n\n\n
    “I’m working on a query that uses the NOT IN operator in the WHERE clause. The operator checks values in a list returned by a subquery. When the returned list includes a NULL value, the query returns an empty resultset, even though I know I should be seeing results. Any idea what might be happening?”<\/h3>\n\n\n\n
    \n
    Using NOT<\/code> IN<\/code> can be tricky, especially when NULL<\/code> values are involved. Case in point. The following SELECT<\/code> statement includes a WHERE<\/code> clause that checks for specific colors:<\/p>\n
    <\/pre>\n
    Basically, what we’re doing is returning all products that are saleable items (FinishedGoodsFlag<\/code> =<\/code> 1<\/code>) and whose color is not one of the colors of the non-saleable products. Unfortunately, when we run this query we receive an empty resultset.<\/p>\n
    If you’re familiar with the data, you might be surprised by these results. After all, the table contains 295 rows of saleable products. Either there are no colors unique to saleable items, or something is wrong.<\/p>\n
    We can investigate the problem by first running the subquery in the example above separate from the outer query:<\/p>\n
    <\/pre>\n
    The following table shows the subquery’s results, which includes three distinct items:<\/p>\n\n\n\n\n\n\n
    \n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, we have black and silver non-saleable items, along with those items for which no color has been assigned (the NULL<\/code> value). Now let’s modify the subquery to instead retrieve the colors of the orderable products:<\/p>\n
    <\/pre>\n
    This time around, we again receive the same three values, along with another seven colors, as shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    Blue<\/p>\n<\/td>\n<\/tr>\n
    \n
    Grey<\/p>\n<\/td>\n<\/tr>\n
    \n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver\/Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Yellow<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly something is wrong. Rows that contain those seven additional colors should have been included in our original query results. The problem, it turns out, is the NULL<\/code> value. Let’s recast the logic of the WHERE<\/code> clause into several separate predicates, connected by the AND<\/code> logical operator:<\/p>\n
    <\/pre>\n
    Essentially, our NOT<\/code> IN<\/code> operator indicates that the value in the Color<\/code> column should not be Black<\/code> and<\/i> should not be Silver<\/code> and<\/i> should not be  NULL<\/code>, in addition to being a saleable product. The problem is with the equation Color<\/code> <><\/code> NULL<\/code>. For a row to be returned, all conditions must evaluate to true; however, a NULL<\/code> cannot evaluate to true or false, which means the condition as a whole will never evaluate to true and no rows will ever be returned. If we were to run this statement, we would once again receive an empty resultset.<\/p>\n
    One way around this predicament is to add a second predicate to the WHERE<\/code> clause in our subquery:<\/p>\n
    <\/pre>\n
    Now our subquery returns only the values Black<\/code> and Silver<\/code>, which means our outer query should return all rows except those whose Color<\/code> value is one of the two colors. The following table shows part of the results, although the query actually returns 120 rows:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 58<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Red<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bike Socks, M<\/p>\n<\/td>\n
    		\n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bike Socks, L<\/p>\n<\/td>\n
    		\n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Blue<\/p>\n<\/td>\n
    		\n
    Blue<\/p>\n<\/td>\n<\/tr>\n
    \n
    AWC Logo Cap<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, S<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, M<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, L<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, XL<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 62<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 44<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 48<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you were to run this query, you would see the Color<\/code> values black<\/code> and silver<\/code> are not included. However, what’s odd about this is that rows with NULL<\/code> for a color are not included either, even though NULL<\/code> is no longer being returned by the subquery. This might be fine in some cases, but in others you might want to see those values. Before we start looking for a workaround, however, another consideration is performance. Many SQL Server folks recommend using an EXISTS<\/code> operator rather than an IN<\/code> operator anyway. So let’s rewrite the last statement using NOT<\/code> EXISTS<\/code>:<\/p>\n
    <\/pre>\n
    Our WHERE<\/code> clause now specifies that we include only saleable products, that they be a known color, and that the color not exist within the list of non-orderable colors. As a result, the SELECT<\/code> statement returns the same results as the preceding one, 120 rows. And if we want to include the saleable products with a Color<\/code> value of NULL<\/code>, we can simply remove the IS<\/code> NOT<\/code> NULL<\/code> expression:<\/p>\n
    <\/pre>\n
    Our statement now returns 170 rows, which include those rows with a Color<\/code> value of NULL<\/code>. In theory, this statement should perform better than the ones using NOT<\/code> NULL<\/code>, but you might want to test that part out yourself to be sure. Just know that using NOT<\/code> IN<\/code> with NULL<\/code> values can return unexpected results, so be prepared.<\/p>\n<\/div>\n\n\n\n
     “What the heck is a ‘modulo’?”<\/h3>\n\n\n\n
    \n
    Remember when you first learned long division? You had the dividend, the divisor, and the remainder. The dividend is the number you start with. The divisor is the number you divide into the dividend. The remainder is what’s left. Modulo is a mathematical operator that returns the remainder.<\/p>\n
    Here’s how it works. In the follow example, @var1<\/code> serves as our dividend, and @var2<\/code> serves as our divisor:<\/p>\n
    <\/pre>\n
    The first expression in our SELECT<\/code> clause uses the division operator (\/<\/code>) to divide @var1<\/code> by @var2<\/code>. The second expression uses the modulo operator (%<\/code>) to calculate the remainder that’s produced when you divide these two numbers. The following table shows the results.<\/p>\n\n\n\n\n
    \n
    Division<\/b><\/p>\n<\/td>\n
    		\n
    Remainder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    No surprise here. When you divide 32 by 8, you get 4, with a remainder of 0. But now let’s produce a remainder other than 0:<\/p>\n
    <\/pre>\n
    This time around, we simply set @var1<\/code> to equal 36, rather than 32. Now our results show a remainder of 4.<\/p>\n
    That’s all there is to using the modulo operator. And you can use it with other numeric types:<\/p>\n
    <\/pre>\n
    In this case, we’re dividing 55.566 by 5 to come up with the following results:<\/p>\n\n\n\n\n
    \n
    Division<\/b><\/p>\n<\/td>\n
    		\n
    Remainder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    11.111200000000000<\/p>\n<\/td>\n
    		\n
    0.5560<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the expression that contains the modulo operator returns a value of 0.5560<\/code>. As for the expression that contains the division operator, the operator’s returned value is based on the argument type with the highest precedence, which in this case is DECIMAL<\/code>.<\/p>\n<\/div>\n\n\n\n
    “Are all WHEN and ELSE clauses in a CASE expression evaluated if the first WHEN clause evaluates to TRUE?”<\/h3>\n\n\n\n
    \n
    According to the SQL Server documentation, a CASE<\/code> expression evaluates each condition sequentially and stops with the first condition that evaluates to true. In other words, the expression will short circuit when a condition is met, saving the database engine from having to perform unnecessary processing. This is a good thing. The less work the engine has to perform, the better.<\/p>\n
    To get a sense of how this works, let’s look at an example. The following SELECT<\/code> statement includes a CASE<\/code> expression that sets up three possible conditions when concatenating the name-related columns in the vSalesPerson<\/code> view:<\/p>\n
    <\/pre>\n
    The first CASE<\/code> condition checks whether the MiddleName<\/code> value is NULL<\/code>. If it is, the FirstName<\/code> and LastName<\/code> columns are concatenated and the expression stops running. If the column contains a value other than NULL<\/code>, the expression moves on to the next condition, which checks the length of the MiddleName<\/code> value. If it equals 1<\/code>, the three columns are concatenated and a period is added to the middle initial. The expression will then stop at that point. Otherwise, the ELSE<\/code> condition is applied and all three columns are concatenated as is. The database engine repeats the process for each row and returns the following results:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    Tsvi Michael Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The CASE<\/code> expression’s ability to short circuit can help to avoid unnecessary processing when it can be substituted for other statement elements. For example, suppose we define the following SELECT<\/code> statement:<\/p>\n
    <\/pre>\n
    Nothing flashy here. For each row, the database engine tests for the three specified conditions and determines whether that row is returned. Consequently, the resultset includes any saleable product that is either black or red, which means 127 rows evaluate to true. Part of those results are shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Black, 58<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 58<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Red<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Black<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 62<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 44<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 48<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 52<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 56<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 58<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 60<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 62<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Suppose we want to reduce some of the work that the database engine has to perform. We can instead use a CASE<\/code> expression in the WHERE<\/code> clause, as shown in the following SELECT<\/code> statement:<\/p>\n
    <\/pre>\n
    If the Color<\/code> value equals black<\/code>, the condition evaluates to true and the expression stops running, thus avoiding any unnecessary processing. Yet the statement returns the same results as the preceding example.<\/p>\n
    Under the right circumstances, using a CASE<\/code> expression in this way can be a handy way to reduce your workloads. However, a CASE<\/code> expression might not always short circuit in the way you hope. For example, suppose your T-SQL includes logic is similar to that shown in the following example:<\/p>\n
    <\/pre>\n
    All we’re doing is declaring an INT<\/code> variable, setting its value to 0<\/code>, and using a CASE<\/code> expression to retrieve a specific value. Based on our understanding of the CASE<\/code> expression’s ability to short circuit, we would expect the expression’s first condition to evaluate to true and the expression to then stop. However, what we get from our SELECT<\/code> statement is the following error:<\/p>\n
    <\/pre>\n
    The error shows that the ELSE<\/code> condition did in fact run, even though it shouldn’t have. It turns out that under certain circumstances, a CASE<\/code> expression doesn’t behave like it’s supposed to behave. The problem appears to be related to aggregate functions (at least in part), so be aware that in some cases a CASE<\/code> expression might not deliver the benefits you’re hoping for and you might have to come up with a workaround to make the CASE<\/code> expression work the way you want it to.<\/p>\n<\/div>\n\n\n\n
    “What’s the difference between the two ‘not equal to’ operators (<> and !=)?”<\/h3>\n\n\n\n
    \n
    In T-SQL, the two comparisons operators are syntactically equivalent. They each perform a Boolean comparison between two expressions. If those expressions return non-NULL<\/code>, unequal values, the comparison evaluates to true, otherwise, it evaluates to false. However, if either value is  NULL<\/code>, the evaluation returns a NULL<\/code>. Here’s a sample of the two operators in action:<\/p>\n
    <\/pre>\n
     The variable is set to a value of  1<\/code>, and in each SELECT<\/code> statement, that variable is compared to the constant 2<\/code>. As a result, each SELECT<\/code> statement returns the value not<\/code> equal<\/code> because each comparison evaluates to true. Even in terms of performance, there doesn’t appear to be much difference between the two.<\/p>\n
    However, there is one difference you should be aware of. The <><\/code> operator conforms to ANSI standards; the !=<\/code> operator does not, despite that fact that a number of relational database systems support both operators. That said, even if there is only the slightest chance that you might port your script to another system, you’re better off sticking with the  <><\/code> operator.<\/p>\n<\/div>\n\n\n\n
    “I’ve come across operators such as += in variable assignment SET statements. What do they mean?”<\/h3>\n\n\n\n
    \n
    The add-equals operator (+=<\/code>) is a compound operator, one of several types of compound operators supported in SQL Server. A compound operator takes the original value and in some way amends it, rather than replacing it. For example, the following T-SQL uses the add-equals operator to update the value of the @var1<\/code> variable:<\/p>\n
    <\/pre>\n
    We start by declaring @var1<\/code> and setting its value to 2<\/code>. We then use a SET<\/code> statement to add a value of 1<\/code> to the original value. The SELECT<\/code> statement returns the new value, which is 3<\/code>.<\/p>\n
    The add-equals operator makes it possible to add a value without having to specify the original value. Without the add-equals operator we would have to rewrite our SET<\/code> statement as follows:<\/p>\n
    <\/pre>\n
    The add-equals operator merely provides the shorthand necessary to avoid having to explicitly specify the original value, yet the SELECT<\/code> statement still returns a value of 3<\/code>. Plus, SQL Server supports other compound operators as well. For example, the following T-SQL subtracts 1<\/code> from the original value:<\/p>\n
    <\/pre>\n
    In this case, we’re using the subtract-equals (-=<\/code>) operator in our SELECT<\/code> statement, rather than the add-equals operator. The subtract-equals operator works the same way as the add-equals operator, except that the second value is subtracted rather than added. As a result, our SELECT<\/code> statement now returns a value of 1<\/code>.<\/p>\n
    Another example of a compound operator is the multiply-equals operator, which is shown in the following example:<\/p>\n
    <\/pre>\n
    In this case, the SET<\/code> statement uses the multiply-equals operator to multiple the variable value 2<\/code> by the constant value 3<\/code>, giving us a total of 6<\/code>. And compound operators are not limited to mathematical expressions. For example, the following T-SQL includes a SET<\/code> statement that uses the add-equals operator to concatenate values:<\/p>\n
    <\/pre>\n
    In this example, the SET<\/code> statement adds the string value and<\/code> then<\/code> some<\/code> (including the opening space) to the variable value some<\/code> value<\/code>, giving us the value some<\/code> value<\/code> and<\/code> then<\/code> some<\/code>. There are other compound operators as well, such as divide-equals (\/=<\/code>) and modulo-equals (%=<\/code>). Check out the MSDN topic “Compound Operators<\/a>” for more information.<\/p>\n<\/div>\n\n\n\n
    + CONVERT(NVARCHAR(20), @var3, 1) AS amount3;  <\/p>\n\n\n\n
    Each expression in the SELECT<\/code> list references one of the variables as its source data and is made up of multiple components. What provides the final format, however, is the CONVERT<\/code> function. The function requires that the source data be either the MONEY<\/code> or SMALLMONEY<\/code> type. When you call the function, you specify a second argument along with the source value. That argument determines the format of the data. In this case, we specify 1<\/code> so the outputted data includes commas every three digits to the left of the decimal point. We also concatenate the value with a dollar sign to give it that final touch, as shown in the following results:<\/p>\n\n\n\n
    \n
    amount1<\/b><\/p>\n<\/td>
    		\n
    amount2<\/b><\/p>\n<\/td>
    		\n
    amount3<\/b><\/p>\n<\/td><\/tr>
    \n
    $555,555,555.56<\/p>\n<\/td>
    		\n
    $555,555,555.56<\/p>\n<\/td>
    		\n
    $555,555,555.56<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
     Overall, this isn’t too painful a process, unless you’re working with currencies that follow a different format, such as the Swiss franc, which takes apostrophes rather than commas. In such cases, you might have to add yet another function, such as REPLACE<\/code>, to substitute the commas with apostrophes.<\/p>\n\n\n\n
    Fortunately, since the release of SQL Server 2012, you’ve been able to instead use the FORMAT<\/code> function to output your numbers to a currency format. The FORMAT<\/code> function returns a value in a specified format, based on a specified culture. To demonstrate how this works, let’s first recast our preceding expressions into ones that use the FORMAT<\/code> function to configure the data as US dollar amounts:<\/p>\n\n\n\n
    <\/pre>\n\n\n\n
    Notice that we must first cast the character data (@var1<\/code>) to the MONEY<\/code> data type, but we can use the DECIMAL<\/code> variable (@var2<\/code>) as is. The second FORMAT<\/code> argument (c<\/code>) specifies that we’re formatting currency, and the third argument specifies the US symbol (en-us<\/code>) for the cultural context. The SELECT<\/code> statement returns the same results as the previous SELECT<\/code> statement, with all values returned as the NVARCHAR<\/code> type.<\/p>\n\n\n\n
    However, with the FORMAT<\/code> function, we can specify other cultures. For example, the following example uses the German symbol (de-de<\/code>) to provide the cultural context:<\/p>\n\n\n\n
    <\/pre>\n\n\n\n
    Now our results are specific to how the euro is represented in Germany, as shown in the following table:<\/p>\n\n\n\n
    \n
    amount1<\/b><\/p>\n<\/td>
    		\n
    amount2<\/b><\/p>\n<\/td>
    		\n
    amount3<\/b><\/p>\n<\/td><\/tr>
    \n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>
    		\n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td>
    		\n
    555.555.555,56 \u00e2\u00ac<\/p>\n<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
     Certainly, the FORMAT<\/code> function is a handy tool if you want your results to be displayed in a specific currency. But just to reiterate, such formatting is generally handled by the calling application, with the source data retrieved in its raw state.<\/p>\n\n\n\n
    “I see the plus sign (+) used in a variety of ways in T-SQL script, but I can’t always make sense of how SQL Server arrives at the results it does. Is there a “right way” to use plus signs?”<\/h3>\n\n\n\n
    \n
    SQL Server is a tricky devil. As you’ve discovered, plus signs have all sorts of meaning in T-SQL. You can use them to concatenate string values, add numerical values together, or indicate that a numerical value is a positive number, rather than negative. The following SELECT<\/code> statement demonstrates how to both concatenate and add values:<\/p>\n
    <\/pre>\n
    For the FullName<\/code> column, we’ve used the plus sign to concatenate the FirstName<\/code> and  LastName<\/code> columns with a space in between. For the TwoYearSales<\/code> column, we’ve used the plus sign to add the SalesLastYear<\/code> and  SalesYTD<\/code> columns together. In both cases, the database engine knows when to concatenate the values or when to add them together, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    SalesLastYear<\/b><\/p>\n<\/td>\n
    		\n
    SalesYTD<\/b><\/p>\n<\/td>\n
    		\n
    TwoYearSales<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Blythe<\/p>\n<\/td>\n
    		\n
    $1,750,406.48<\/p>\n<\/td>\n
    		\n
    $3,763,178.18<\/p>\n<\/td>\n
    		\n
    $5,513,584.66<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Mitchell<\/p>\n<\/td>\n
    		\n
    $1,439,156.03<\/p>\n<\/td>\n
    		\n
    $4,251,368.55<\/p>\n<\/td>\n
    		\n
    $5,690,524.58<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    $1,997,186.20<\/p>\n<\/td>\n
    		\n
    $3,189,418.37<\/p>\n<\/td>\n
    		\n
    $5,186,604.57<\/p>\n<\/td>\n<\/tr>\n
    \n
    Garrett Vargas<\/p>\n<\/td>\n
    		\n
    $1,620,276.90<\/p>\n<\/td>\n
    		\n
    $1,453,719.47<\/p>\n<\/td>\n
    		\n
    $3,073,996.36<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi Reiter<\/p>\n<\/td>\n
    		\n
    $1,849,640.94<\/p>\n<\/td>\n
    		\n
    $2,315,185.61<\/p>\n<\/td>\n
    		\n
    $4,164,826.55<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    $1,927,059.18<\/p>\n<\/td>\n
    		\n
    $1,352,577.13<\/p>\n<\/td>\n
    		\n
    $3,279,636.31<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu Ito<\/p>\n<\/td>\n
    		\n
    $2,073,506.00<\/p>\n<\/td>\n
    		\n
    $2,458,535.62<\/p>\n<\/td>\n
    		\n
    $4,532,041.62<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jos\u00e9 Saraiva<\/p>\n<\/td>\n
    		\n
    $2,038,234.65<\/p>\n<\/td>\n
    		\n
    $2,604,540.72<\/p>\n<\/td>\n
    		\n
    $4,642,775.37<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Campbell<\/p>\n<\/td>\n
    		\n
    $1,371,635.32<\/p>\n<\/td>\n
    		\n
    $1,573,012.94<\/p>\n<\/td>\n
    		\n
    $2,944,648.25<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete Mensa-Annan<\/p>\n<\/td>\n
    		\n
    $0.00<\/p>\n<\/td>\n
    		\n
    $1,576,562.20<\/p>\n<\/td>\n
    		\n
    $1,576,562.20<\/p>\n<\/td>\n<\/tr>\n
    \n
    Lynn Tsoflias<\/p>\n<\/td>\n
    		\n
    $2,278,548.98<\/p>\n<\/td>\n
    		\n
    $1,421,810.92<\/p>\n<\/td>\n
    		\n
    $3,700,359.90<\/p>\n<\/td>\n<\/tr>\n
    \n
    Rachel Valdez<\/p>\n<\/td>\n
    		\n
    $1,307,949.79<\/p>\n<\/td>\n
    		\n
    $1,827,066.71<\/p>\n<\/td>\n
    		\n
    $3,135,016.50<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jae Pak<\/p>\n<\/td>\n
    		\n
    $1,635,823.40<\/p>\n<\/td>\n
    		\n
    $4,116,871.23<\/p>\n<\/td>\n
    		\n
    $5,752,694.62<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ranjit Varkey Chudukatil<\/p>\n<\/td>\n
    		\n
    $2,396,539.76<\/p>\n<\/td>\n
    		\n
    $3,121,616.32<\/p>\n<\/td>\n
    		\n
    $5,518,156.08<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    There’s no real mystery here. When we use the plus sign with only character data, the database engine concatenates the values. In this case, the plus sign is considered a string concatenation operator. When we use the plus sign with numerical values, the database engine adds the values together. Under these circumstances, the plus sign is considered an addition operator.<\/p>\n
    Not surprisingly, when we mix and match the types of data, our results are less predictable. Take a look at the following example:<\/p>\n
    <\/pre>\n
    As you can see, we’ve declared seven variables, each defined with a different type. We then use the plus sign to combine the variables in different ways, giving us the following results:<\/p>\n\n\n\n\n
    \n
    var1var2<\/b><\/p>\n<\/td>\n
    		\n
    var3var6<\/b><\/p>\n<\/td>\n
    		\n
    var4var5<\/b><\/p>\n<\/td>\n
    		\n
    var7var3<\/b><\/p>\n<\/td>\n
    		\n
    var6var7<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    TestVarCX<\/p>\n<\/td>\n
    		\n
    1299.99<\/p>\n<\/td>\n
    		\n
    1001.0001<\/p>\n<\/td>\n
    		\n
    2015-05-21 00:00:00.000<\/p>\n<\/td>\n
    		\n
    2017-04-19 23:45:36.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As we would expect, when we pair @var1<\/code> with @var2<\/code>, both character types, we end up with a concatenated string. When we pair @var3<\/code> to @var6<\/code>, both numerical types, the values are added together, even though @var3<\/code> and @var6<\/code> are different types.<\/p>\n
    Now we get to our third pairing: @var4<\/code> +<\/code> @var5<\/code>. This time around, we’re adding a DECIMAL<\/code> value to BIT<\/code> value. However, the database engine still treats both variables as numerical values and adds them together, even though SQL Server documentation suggests that you can’t do this with the BIT<\/code> type. (And really, why would you?)<\/p>\n
    The next item in the select list, @var7<\/code> +<\/code> @var3<\/code>, pairs a DATETIME<\/code> value with a SMALLINT<\/code> value. This time, we get a date that is 300 days later than the original date. When your expression includes the plus sign, the database engine defaults to the data type with the highest precedence, in this case,  DATETIME<\/code>. Based on the type, the engine determines whether to use the plus sign to concatenate values or add them together. If the data type with the highest precedence is a numeric data type, the engine attempts to add the values. In this case, the engine adds the SMALLINT<\/code> value to the DATETIME<\/code> value as the specified number of days. (A DATETIME<\/code> value is actually stored as two integers.)<\/p>\n
    Finally, we pair a MONEY<\/code> date type with the DATETIME<\/code> date type (@var6<\/code> +<\/code> @var7<\/code>). Once again, DATETIME<\/code> takes precedence over MONEY<\/code>, so the database engine adds 999.99 days to our original date to come up with a date in 2017.<\/p>\n
    We can verify how data type precedence works with plus signs by using the SQL_VARIANT_PROPERTY<\/code> function to retrieve the data type of the expressions that pair the different variables:<\/p>\n
    <\/pre>\n
    The SELECT<\/code> statement returns the returned data type for each expression, as shown in the following table:<\/p>\n\n\n\n\n
    \n
    var1var2<\/b><\/p>\n<\/td>\n
    		\n
    var3var6<\/b><\/p>\n<\/td>\n
    		\n
    var4var5<\/b><\/p>\n<\/td>\n
    		\n
    var7var3<\/b><\/p>\n<\/td>\n
    		\n
    var6var7<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    nvarchar<\/p>\n<\/td>\n
    		\n
    money<\/p>\n<\/td>\n
    		\n
    decimal<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     In each case, the expression returns the type with the highest precedence and determines how the plus sign will be used.<\/p>\n
    So far in our examples, the database engine has been able to add or concatenate values without a problem because the engine could implicitly convert the data type with the lowest precedence to the type with the highest. But this isn’t always the case:<\/p>\n
    <\/pre>\n
    This time around, we’re using the plus sign to pair a NVARCHAR<\/code> value with a SMALLINT<\/code> value. Because SMALLINT<\/code> has precedence over NVARCHAR<\/code>, the database engine attempts to convert the value TestVar<\/code> to the SMALLINT<\/code> type, which of course is not possible. Consequently, the database engine returns the following error message:<\/p>\n
    <\/pre>\n
    The only way we can pair a numerical value with a string value is to concatenate them. In order to do so, we must explicitly convert the SMALLINT<\/code> value to a character data type:<\/p>\n
    <\/pre>\n
    Now our SELECT<\/code> statement returns the value TestVar300<\/code>.<\/p>\n
    There’s one other use of the plus sign that you should be aware of-as a unary plus operator. SQL Server supports two types of unary operators, plus and minus, which are used to designate whether a numeric value is positive or negative:<\/p>\n
    <\/pre>\n
    All we’ve done here is to designate our two literal values as positive and negative numbers. The SELECT<\/code> statement returns the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    123.45<\/p>\n<\/td>\n
    		\n
    -123.45<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Be aware, however, that you cannot use the positive unary operator to convert a negative number to a positive number. For that, you need to use the ABS<\/code> mathematical function.<\/p>\n<\/div>\n\n\n\n
    “The NULLIF expression makes no sense. I would think you’d use it to somehow ferret out a NULL value or to test a condition for a NULL value. But it appears to merely return a NULL if two values are equal. Is that all it’s doing?”<\/h3>\n\n\n\n
    \n
    That’s part of what it’s doing. When a NULLIF<\/code> expression compares two values, it returns the first value if the two values are not equal. If they are equal, the expression returns a NULL<\/code> value of the same data type as the first specified value. Here’s what a NULLIF<\/code> expression looks like in action:<\/p>\n
    <\/pre>\n
    In this case, the NULLIF<\/code> expression returns valueA<\/code> because the two values are not equal. If they were equal, the returned value would be NULL<\/code>. In addition, the returned value would be NULL<\/code> if the first value were NULL<\/code>.<\/p>\n
    When you know how a NULLIF<\/code> expression works, it doesn’t seem so bad, although its use still has a tendency to cause confusion. Luckily, we can achieve the same results by using a CASE<\/code> statement:<\/p>\n
    <\/pre>\n
    The advantage of the CASE<\/code> statement is that it’s a lot easier to understand. Sure, NULLIF<\/code> provides simpler syntax, but if it creates confusion, what’s the point?<\/p>\n<\/div>\n\n\n\n
    “Can you use expressions in data manipulation language (DML) statements?”<\/h3>\n\n\n\n
    \n
    You can use expressions wherever T-SQL syntax permits them, including DML statements. Take a look at part of the syntax for the UPDATE<\/code> statement’s SET<\/code> clause:<\/p>\n
    <\/pre>\n
     According to SQL Server Books Online, the expression placeholder can be a “variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value.” This pretty much defines the term expression<\/i> as we understand it. Note, however, that any expression you define must conform to the rules associated with the particular T-SQL statement in which the expression is defined. For example, an UPDATE<\/code> statement’s UPDATE<\/code> clause can include the TOP<\/code> clause, as shown in the following syntax:<\/p>\n
    <\/pre>\n
     In this case, expression “specifies the number or percent of rows that will be updated.” In other words, you cannot specify a non-numerical string value as your expression, as you can with other types of expressions. That said, it’s still considered an expression.<\/p>\n
    So, yes, you can use an expression in a DML statement. In fact, they’re used all the time. Let’s look at an example. Suppose we create the following table in our database and insert a row of data:<\/p>\n
    <\/pre>\n
    Now let’s use an UPDATE<\/code> statement to modify that row of data:<\/p>\n
    <\/pre>\n
    Our SET<\/code> clause is updating two columns. Each SET<\/code> definition includes an expression that either adds the @AmtReceived<\/code> value to the target column or subtracts that value. If we run the SELECT<\/code> statement after the table has been updated, we come up with the following results:<\/p>\n\n\n\n\n
    \n
    ProductID<\/b><\/p>\n<\/td>\n
    		\n
    InStock<\/b><\/p>\n<\/td>\n
    		\n
    OnOrder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    67<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you’re writing T-SQL statements, you’re writing expressions, even if you don’t realize it. That’s why it’s important to refer back to the statement’s syntax whenever you’re uncertain how to define a particular element. Expressions are supported throughout most statements. The syntax for the UPDATE<\/code> statement, for example, includes the expression placeholder in 10 different places, which suggests that many of us might not be using some statements to their fullest capacity.<\/p>\n<\/div>\n\n\n\n
     “The CASE expression allows me to leave out the ELSE clause. Is it that important to include it?”<\/h3>\n\n\n\n
    \n
    Yes, but…. The ELSE<\/code> clause specifies the value to return if no other comparison operations (WHEN...THEN<\/code>) evaluate to true. If you omit the ELSE<\/code> clause, the database engine instead returns a NULL<\/code> value. A NULL<\/code> value might be fine, if that’s what you want. But what if it’s not? Take a look at the following example, which uses a CASE<\/code> expression to determine how to return an employee’s full name:<\/p>\n
    <\/pre>\n
    If the MiddleName<\/code> column is NULL<\/code>, that column is not included in the expression. Only the first and last names are concatenated. If the MiddleName<\/code> value is an initial, a period is added to the initial and concatenated with the first and last names. Otherwise, all three names are concatenated, as shown in the following results:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    Tsvi Michael Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now let’s modify the CASE<\/code> expression by removing the ELSE<\/code> clause:<\/p>\n
    <\/pre>\n
    This time a NULL<\/code> is returned if the MiddleName<\/code> column has a value that is more than an initial, as the following table shows:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly, you would not want to delete the ELSE<\/code> clause in this instance. You could, however, add a WHEN...THEN<\/code> condition that replaces the ELSE<\/code> clause, in which case, you’re still returning NULL<\/code> of none of the conditions are met, which might be fine under some circumstances. Consider the following example:<\/p>\n
    <\/pre>\n
    If the SalesQuota<\/code> value is great than 250,000, a true<\/code> is returned. If the value is less than or equal to 250,000, we get a false<\/code>. However, if the column is NULL<\/code>, neither condition can be true, so the CASE<\/code> expression returns a NULL<\/code>, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    HigherQuota<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Stephen Jiang<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Blythe<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Mitchell<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tsvi Reiter<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pamela Ansman-Wolfe<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Shu Ito<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Campbell<\/p>\n<\/td>\n
    		\n
    false<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tete Mensa-Annan<\/p>\n<\/td>\n
    		\n
    true<\/p>\n<\/td>\n<\/tr>\n
    \n
    Syed Abbas<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Amy Alberts<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    You can choose to include an ELSE<\/code> clause and then provide a default value, such as N\/A<\/code> (or even NULL<\/code>), whether or not you think it’s necessary. In fact, many developers think you should never omit the ELSE<\/code> clause, even if you believe it is impossible for one of the WHEN...THEN<\/code> conditions not to be met. Someone might make a change to the database that affects the statement or your thinking about possible outcomes might not be correct.<\/p>\n<\/div>\n\n\n\n
    “Can you shed light on the order in which the database engine processes the components in an expression?”<\/h3>\n\n\n\n
    \n
    The way in which an expression’s elements are processed depends on several factors: their listed order, whether any subset of elements is enclosed in parentheses, and what operators are used to connect the various elements. Parentheses group elements together to ensure that those elements are processed as a unit before being incorporated into the rest of the expression. In addition, the expression as a whole adheres to the concept of operator precedence,<\/i> in which the operator types determine the order in which the expression is evaluated. For example, the multiply and division arithmetic operators take precedence over the greater than (><\/code>) and lesser than (<<\/code>) comparison operations, which take precedence over logical operators such as AND<\/code> or ANY<\/code>. (You can find details about operator precedence in the MSDN topic “Operator Precedence<\/a>.”)<\/p>\n
    Let’s start with a few simple arithmetic-based expressions to give you a sense of how this works. The following example declares several INT<\/code> variables, assigns an expression to each one, and returns the values from all three:<\/p>\n
    <\/pre>\n
    All we’re doing in each expression is multiplying and adding and subtracting values. However, because we use parentheses in the second and third expressions, our results come up quite different:<\/p>\n\n\n\n\n
    \n
    num1<\/b><\/p>\n<\/td>\n
    		\n
    num2<\/b><\/p>\n<\/td>\n
    		\n
    num3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The @num1<\/code> expression is fairly straightforward. We multiple 3 by 2, add 5, and subtract 7, which gives us a total of 4.<\/p>\n
    However, in the @num2<\/code> expression, we enclose the 2<\/code> +<\/code> 5<\/code> elements in parentheses, so they’re processed first, giving us a total of 7. The 7 is then multiplied by the first 3, giving us 21. From there, we subtract 7, giving us a total of 14.<\/p>\n
    In the @num3<\/code> expression, the parentheses enclose the 2<\/code> +<\/code> 5<\/code> -<\/code> 7<\/code> elements, which are processed before the other elements. Because this returns 0, 3 is multiplied by 0, giving us a final result of 0.<\/p>\n
    Now let’s look at another example, which again declares three variables and applies different expressions to them:<\/p>\n
    <\/pre>\n
    Here’s what are results look like:<\/p>\n\n\n\n\n
    \n
    SalesDiff1<\/b><\/p>\n<\/td>\n
    		\n
    SalesDiff2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    6500<\/p>\n<\/td>\n
    		\n
    500<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     The first expression includes no parentheses. However, the multiply operator takes precedence over the addition and subtraction operators, so the multiplication is performed first. That means we first multiply the SalesQuota<\/code> value by 2 and the SalesYTD<\/code> value by 2, giving us the expression 6000<\/code> -<\/code> 2500<\/code> +<\/code> 3000<\/code>, which equals 6500.<\/p>\n
    The second expression does use parentheses. The first set of parentheses returns a value of 6000. The second set of parentheses actually contains a third set, and those are the elements evaluated first, before the outer set of elements. After they’re evaluated, that part of the expression enclosed in the second set of parentheses becomes @SalesLastYear<\/code>  +<\/code> 3000<\/code>, giving us a total of 5500. That 5500 is then subtracted from the 6000, which is how we end up with 500.<\/p>\n
    Now let’s look at an expression that incorporates character data as well as numerical data. In the following SELECT<\/code> statement, our WHERE<\/code> clause includes both the OR<\/code> and the AND<\/code> logical operators:<\/p>\n
    <\/pre>\n
    The WHERE<\/code> clause search condition contains three predicates separated by the AND<\/code> operator and the OR<\/code> operator. In its current state, the search condition states that the ListPrice<\/code> value must be greater than 2.5 times the StandardCost<\/code> value and<\/i> the ProductLine<\/code> value must equal r<\/code> or<\/i> the ProductLine<\/code> value must equal t<\/code>. According to the rules of operator precedence, the database engine first evaluates the AND<\/code> logical operator and then the OR<\/code> logical operator. Because of the placement of the operators, the first two predicates must both evaluate to true or<\/i> the third predicate must evaluate to true. In other words, all returned rows must meet the first two conditions or meet the third condition. As a result, the statement returns 59 rows that qualify. The following table shows part of the results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    StandardCost<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n
    		\n
    ProductLine<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Front Wheel<\/p>\n<\/td>\n
    		\n
    96.7964<\/p>\n<\/td>\n
    		\n
    218.01<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Rear Wheel<\/p>\n<\/td>\n
    		\n
    108.7844<\/p>\n<\/td>\n
    		\n
    245.01<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-Panniers, Large<\/p>\n<\/td>\n
    		\n
    51.5625<\/p>\n<\/td>\n
    		\n
    125.00<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bottle Cage<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, M<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, L<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 60<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Touring Frame – Yellow, 62<\/p>\n<\/td>\n
    		\n
    199.8519<\/p>\n<\/td>\n
    		\n
    333.42<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 46<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 50<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Yellow, 54<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 46<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 50<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 54<\/p>\n<\/td>\n
    		\n
    601.7437<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Suppose what we were really after was to return rows that met the first condition and<\/i> either the second or<\/i> third condition. In this case, we would have to modify our search condition slightly:<\/p>\n
    <\/pre>\n
    Notice that the second and third predicates are now enclosed in parentheses. Consequently, the database engine will first evaluate them as a unit. That means for all rows returned, ListPrice<\/code> must be greater than 2.5 times the value of StandardCost<\/code> and<\/i> ProductLine<\/code> must equal r<\/code> or<\/i> t<\/code>. The following table shows all the results now returned by the query.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    StandardCost<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n
    		\n
    ProductLine<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bottle Cage<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, M<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Racing Socks, L<\/p>\n<\/td>\n
    		\n
    3.3623<\/p>\n<\/td>\n
    		\n
    8.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Tire Tube<\/p>\n<\/td>\n
    		\n
    1.4923<\/p>\n<\/td>\n
    		\n
    3.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Tire Tube<\/p>\n<\/td>\n
    		\n
    1.8663<\/p>\n<\/td>\n
    		\n
    4.99<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Tire<\/p>\n<\/td>\n
    		\n
    8.0373<\/p>\n<\/td>\n
    		\n
    21.49<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    ML Road Tire<\/p>\n<\/td>\n
    		\n
    9.3463<\/p>\n<\/td>\n
    		\n
    24.99<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Tire<\/p>\n<\/td>\n
    		\n
    12.1924<\/p>\n<\/td>\n
    		\n
    32.60<\/p>\n<\/td>\n
    		\n
    R<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Tire<\/p>\n<\/td>\n
    		\n
    10.8423<\/p>\n<\/td>\n
    		\n
    28.99<\/p>\n<\/td>\n
    		\n
    T<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Notice that we now have only nine rows and that each row meets both conditions for the ListPrice<\/code> and ProductLine<\/code> columns. By applying the parentheses, we have better controlled how the rules of operator precedence are applied.<\/p>\n<\/div>\n\n\n\n
    “I get confused on how to treat constants in my expressions. Is there a trick in using them?”<\/h3>\n\n\n\n
    \n
    A constant is a literal value that is either part of an expression or is the entire expression. The way in which you treat constants depends on the type of data. As a general rule, you enclose character data in single quotes, but leave numerical data without quotes, although it’s not quite as straightforward as this. Before we go into the oddities, though, let’s look at a few examples of character constants. The following T-SQL declares several character variables and then retrieves their values:<\/p>\n
    <\/pre>\n
    By default, when you’re working with character string constants, you enclose them in singe quotes. If you’re working with Unicode strings, you precede the opening quote with an uppercase N. Anything enclosed in the quotes is considered part of that string, even if the characters are numbers, operators, or other types of symbols, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    some value<\/p>\n<\/td>\n
    		 <\/td>\n\n
    some value + 1<\/p>\n<\/td>\n
    		\n
    12345<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     In SQL Server, we can instead enclose character strings in double quotes by setting the QUOTED_IDENTIFIER<\/code> option to  OFF<\/code>, as shown in the following example:<\/p>\n
    <\/pre>\n
    When the QUOTED_IDENTIFIER<\/code> option is set to OFF<\/code>, we can use double quotes just like single quotes, as is the case with the @var1<\/code> declaration. The advantage of using double quotes is that you can pass in a special character, such as an apostrophe, without having to escape it by adding a second apostrophe, as we had to do for @var3<\/code>. The SELECT<\/code> statement now returns the results shown in the following table:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    Var3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    its value<\/p>\n<\/td>\n
    		\n
    it’s a value<\/p>\n<\/td>\n
    		\n
    it’s a value<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Be aware, however, that the QUOTED_IDENTIFIER<\/code> option has other implications besides being able to use double quotes for string constants. For example, the option affects the way identifiers are handled in T-SQL. In addition, the SQL Server Native Client Provider and ODBC driver set this option to ON<\/code>, which can impact your code if you had set the option to OFF<\/code>. For these reasons you’re usually better off leaving the option set to ON<\/code>.<\/p>\n
    As previously mentioned, you typically don’t enclose numerical constants in quotes, even if they include currency symbols or scientific notation. For example, the following T-SQL declares four types of numerical variables, sets their values, and retrieves those values:<\/p>\n
    <\/pre>\n
    If we had enclosed the constants in quotes, the database engine would have interpreted the values as character data and have tried to convert it to the type of the applicable variable. For the first three variables, an implicit conversion would have worked fine, but it would mean unnecessary work on the part of the database engine. For the last variable, the engine would have returned the following error:<\/p>\n
    <\/pre>\n
    When we specify a numerical constant without quotes, the database engine assumes a numerical type consistent with the value. For example, the value 147<\/code> is assumed to be an INT<\/code>, so no implicit conversion is necessary. The following table shows the results returned by the  SELECT<\/code> statement.<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    147<\/p>\n<\/td>\n
    		\n
    10.5800<\/p>\n<\/td>\n
    		\n
    68.24<\/p>\n<\/td>\n
    		\n
    87.9<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Even with numeric constants, an implicit conversion might be called for. In the following SELECT<\/code> statement, the constant 1000<\/code> is compared the value in the ListPrice<\/code> column, which is configured with the MONEY<\/code> data type:<\/p>\n
    <\/pre>\n
    The 1000<\/code> is treated as an INT<\/code> value, so it must be implicitly converted to the MONEY<\/code> type. But that’s not all what’s going on in the WHERE<\/code> clause. Notice that it also includes a string constant (blue<\/code>). The constant is compared to the Color<\/code> column, which is configured with the NVARCHAR<\/code> data type. This means that the database engine must convert the VARCHAR<\/code> string to the NVARCHAR<\/code> type, which gives us the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    ListPrice<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 46<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 50<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 54<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Touring Frame – Blue, 60<\/p>\n<\/td>\n
    		\n
    1003.91<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 60<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 46<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 50<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 54<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-1000 Blue, 60<\/p>\n<\/td>\n
    		\n
    2384.07<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 46<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 50<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring-2000 Blue, 54<\/p>\n<\/td>\n
    		\n
    1214.85<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Like string constants, date and time values are also enclosed in single quotes, even though the values are stored as integers, as you can see in the following example:<\/p>\n
    <\/pre>\n
    The date and time data types will accept various formats when passing in a constant, as long as the value is enclosed in quotes. The following table shows the results returned by the SELECT<\/code> statement:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n
    		\n
    var4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2014-08-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    2014-07-28 16:24:27.293<\/p>\n<\/td>\n
    		\n
    2014-07-30<\/p>\n<\/td>\n
    		\n
    14:05:55.0010000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     You should also be aware of how to treat constants when working with some of the other data types. For example, a VARBINARY<\/code> value takes an 0x<\/code> prefix but is not enclosed in quotes. The same goes for a BIT<\/code> value. However, a UNIQUEIDENTIFIER<\/code> value is enclosed in quotes, as shown in the following T-SQL:<\/p>\n
    <\/pre>\n
     Our SELECT<\/code> statement now returns the following results:<\/p>\n\n\n\n\n
    \n
    var1<\/b><\/p>\n<\/td>\n
    		\n
    var2<\/b><\/p>\n<\/td>\n
    		\n
    var3<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    0x14AD<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    CC421A37-E462-4AE0-8451-38F837FC5A1A<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Again, we generally enclose string values in quotes and leave the quotes off for numerical values, but as you’ve seen, some areas can be somewhat gray. If you get confused, check out the MSDN topic “Constants<\/a>.”<\/p>\n<\/div>\n\n\n\n
    “Are there any advantages to using a COALESCE expression rather than a CASE expression when checking for the first non-NULL value in a list of values?”<\/h3>\n\n\n\n
    \n
    A COALESCE<\/code> expression is a syntactic shortcut for a CASE<\/code> expression. That means, when the query optimizer gets ahold of a COALESCE<\/code> expression, it rewrites it as a CASE<\/code> one, so in that sense, they’re one in the same. Let’s look at a couple of examples. In the first one, we use COALESCE<\/code> to return the first column that is not a NULL<\/code> value:<\/p>\n
    <\/pre>\n
    For this particular product, the Size<\/code> column and SizeUnitMeasureCode<\/code> column each contains a NULL<\/code> value, but the WeightUnit<\/code> column contains an actual value, G<\/code>, so the COALESCE<\/code> expression returns that value, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Size<\/b><\/p>\n<\/td>\n
    		\n
    SizeUnit<\/b><\/p>\n<\/td>\n
    		\n
    WeightUnit<\/b><\/p>\n<\/td>\n
    		\n
    FirstNotNull<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Rear Wheel<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    G <\/p>\n<\/td>\n
    		\n
    G <\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Now let’s look at what happens when we change the  COALESCE<\/code> expression to a CASE<\/code> expression:<\/p>\n
    <\/pre>\n
    The SELECT<\/code> statement returns the same results as the previous example. That said, the COALESCE<\/code> expression is a lot simpler, although the logic of the CASE<\/code> statement is easier to understand. In some cases, you might find that it’s better to use CASE<\/code> so your intent is clear to other developers.<\/p>\n
    One other consideration with COALESCE<\/code>. Developers sometimes run into problems with COALESCE<\/code> expressions because of data type issues. The value returned by COALESCE<\/code> is based on the data type of the input value with the highest precedence, which might or might not be the returned value. If an implicit conversion is required, and such a conversion is not permitted, COALESCE<\/code> will return an error. See the article “Questions about SQL Server Data Types You were Too Shy to Ask<\/a>” for more details about this particular issue.<\/p>\n
    All that said, figuring out the logic behind COALESCE<\/code> is not too difficult, so choosing between CASE<\/code> and COALESCE<\/code> often comes down to nothing more than personal preference.<\/p>\n<\/div>\n\n\n\n
    “I’m working on a query that uses the NOT IN operator in the WHERE clause. The operator checks values in a list returned by a subquery. When the returned list includes a NULL value, the query returns an empty resultset, even though I know I should be seeing results. Any idea what might be happening?”<\/h3>\n\n\n\n
    \n
    Using NOT<\/code> IN<\/code> can be tricky, especially when NULL<\/code> values are involved. Case in point. The following SELECT<\/code> statement includes a WHERE<\/code> clause that checks for specific colors:<\/p>\n
    <\/pre>\n
    Basically, what we’re doing is returning all products that are saleable items (FinishedGoodsFlag<\/code> =<\/code> 1<\/code>) and whose color is not one of the colors of the non-saleable products. Unfortunately, when we run this query we receive an empty resultset.<\/p>\n
    If you’re familiar with the data, you might be surprised by these results. After all, the table contains 295 rows of saleable products. Either there are no colors unique to saleable items, or something is wrong.<\/p>\n
    We can investigate the problem by first running the subquery in the example above separate from the outer query:<\/p>\n
    <\/pre>\n
    The following table shows the subquery’s results, which includes three distinct items:<\/p>\n\n\n\n\n\n\n
    \n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, we have black and silver non-saleable items, along with those items for which no color has been assigned (the NULL<\/code> value). Now let’s modify the subquery to instead retrieve the colors of the orderable products:<\/p>\n
    <\/pre>\n
    This time around, we again receive the same three values, along with another seven colors, as shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    Blue<\/p>\n<\/td>\n<\/tr>\n
    \n
    Grey<\/p>\n<\/td>\n<\/tr>\n
    \n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver<\/p>\n<\/td>\n<\/tr>\n
    \n
    Silver\/Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Yellow<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly something is wrong. Rows that contain those seven additional colors should have been included in our original query results. The problem, it turns out, is the NULL<\/code> value. Let’s recast the logic of the WHERE<\/code> clause into several separate predicates, connected by the AND<\/code> logical operator:<\/p>\n
    <\/pre>\n
    Essentially, our NOT<\/code> IN<\/code> operator indicates that the value in the Color<\/code> column should not be Black<\/code> and<\/i> should not be Silver<\/code> and<\/i> should not be  NULL<\/code>, in addition to being a saleable product. The problem is with the equation Color<\/code> <><\/code> NULL<\/code>. For a row to be returned, all conditions must evaluate to true; however, a NULL<\/code> cannot evaluate to true or false, which means the condition as a whole will never evaluate to true and no rows will ever be returned. If we were to run this statement, we would once again receive an empty resultset.<\/p>\n
    One way around this predicament is to add a second predicate to the WHERE<\/code> clause in our subquery:<\/p>\n
    <\/pre>\n
    Now our subquery returns only the values Black<\/code> and Silver<\/code>, which means our outer query should return all rows except those whose Color<\/code> value is one of the two colors. The following table shows part of the results, although the query actually returns 120 rows:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 58<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Red<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bike Socks, M<\/p>\n<\/td>\n
    		\n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bike Socks, L<\/p>\n<\/td>\n
    		\n
    White<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Blue<\/p>\n<\/td>\n
    		\n
    Blue<\/p>\n<\/td>\n<\/tr>\n
    \n
    AWC Logo Cap<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, S<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, M<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, L<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    Long-Sleeve Logo Jersey, XL<\/p>\n<\/td>\n
    		\n
    Multi<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 62<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 44<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 48<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you were to run this query, you would see the Color<\/code> values black<\/code> and silver<\/code> are not included. However, what’s odd about this is that rows with NULL<\/code> for a color are not included either, even though NULL<\/code> is no longer being returned by the subquery. This might be fine in some cases, but in others you might want to see those values. Before we start looking for a workaround, however, another consideration is performance. Many SQL Server folks recommend using an EXISTS<\/code> operator rather than an IN<\/code> operator anyway. So let’s rewrite the last statement using NOT<\/code> EXISTS<\/code>:<\/p>\n
    <\/pre>\n
    Our WHERE<\/code> clause now specifies that we include only saleable products, that they be a known color, and that the color not exist within the list of non-orderable colors. As a result, the SELECT<\/code> statement returns the same results as the preceding one, 120 rows. And if we want to include the saleable products with a Color<\/code> value of NULL<\/code>, we can simply remove the IS<\/code> NOT<\/code> NULL<\/code> expression:<\/p>\n
    <\/pre>\n
    Our statement now returns 170 rows, which include those rows with a Color<\/code> value of NULL<\/code>. In theory, this statement should perform better than the ones using NOT<\/code> NULL<\/code>, but you might want to test that part out yourself to be sure. Just know that using NOT<\/code> IN<\/code> with NULL<\/code> values can return unexpected results, so be prepared.<\/p>\n<\/div>\n\n\n\n
     “What the heck is a ‘modulo’?”<\/h3>\n\n\n\n
    \n
    Remember when you first learned long division? You had the dividend, the divisor, and the remainder. The dividend is the number you start with. The divisor is the number you divide into the dividend. The remainder is what’s left. Modulo is a mathematical operator that returns the remainder.<\/p>\n
    Here’s how it works. In the follow example, @var1<\/code> serves as our dividend, and @var2<\/code> serves as our divisor:<\/p>\n
    <\/pre>\n
    The first expression in our SELECT<\/code> clause uses the division operator (\/<\/code>) to divide @var1<\/code> by @var2<\/code>. The second expression uses the modulo operator (%<\/code>) to calculate the remainder that’s produced when you divide these two numbers. The following table shows the results.<\/p>\n\n\n\n\n
    \n
    Division<\/b><\/p>\n<\/td>\n
    		\n
    Remainder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    No surprise here. When you divide 32 by 8, you get 4, with a remainder of 0. But now let’s produce a remainder other than 0:<\/p>\n
    <\/pre>\n
    This time around, we simply set @var1<\/code> to equal 36, rather than 32. Now our results show a remainder of 4.<\/p>\n
    That’s all there is to using the modulo operator. And you can use it with other numeric types:<\/p>\n
    <\/pre>\n
    In this case, we’re dividing 55.566 by 5 to come up with the following results:<\/p>\n\n\n\n\n
    \n
    Division<\/b><\/p>\n<\/td>\n
    		\n
    Remainder<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    11.111200000000000<\/p>\n<\/td>\n
    		\n
    0.5560<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the expression that contains the modulo operator returns a value of 0.5560<\/code>. As for the expression that contains the division operator, the operator’s returned value is based on the argument type with the highest precedence, which in this case is DECIMAL<\/code>.<\/p>\n<\/div>\n\n\n\n
    “Are all WHEN and ELSE clauses in a CASE expression evaluated if the first WHEN clause evaluates to TRUE?”<\/h3>\n\n\n\n
    \n
    According to the SQL Server documentation, a CASE<\/code> expression evaluates each condition sequentially and stops with the first condition that evaluates to true. In other words, the expression will short circuit when a condition is met, saving the database engine from having to perform unnecessary processing. This is a good thing. The less work the engine has to perform, the better.<\/p>\n
    To get a sense of how this works, let’s look at an example. The following SELECT<\/code> statement includes a CASE<\/code> expression that sets up three possible conditions when concatenating the name-related columns in the vSalesPerson<\/code> view:<\/p>\n
    <\/pre>\n
    The first CASE<\/code> condition checks whether the MiddleName<\/code> value is NULL<\/code>. If it is, the FirstName<\/code> and LastName<\/code> columns are concatenated and the expression stops running. If the column contains a value other than NULL<\/code>, the expression moves on to the next condition, which checks the length of the MiddleName<\/code> value. If it equals 1<\/code>, the three columns are concatenated and a period is added to the middle initial. The expression will then stop at that point. Otherwise, the ELSE<\/code> condition is applied and all three columns are concatenated as is. The database engine repeats the process for each row and returns the following results:<\/p>\n\n\n\n\n\n\n
    \n
    EmployeeID<\/b><\/p>\n<\/td>\n
    		\n
    FullName<\/b><\/p>\n<\/td>\n
    		\n
    Territory<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    275<\/p>\n<\/td>\n
    		\n
    Michael G. Blythe<\/p>\n<\/td>\n
    		\n
    Northeast<\/p>\n<\/td>\n<\/tr>\n
    \n
    277<\/p>\n<\/td>\n
    		\n
    Jillian Carson<\/p>\n<\/td>\n
    		\n
    Central<\/p>\n<\/td>\n<\/tr>\n
    \n
    279<\/p>\n<\/td>\n
    		\n
    Tsvi Michael Reiter<\/p>\n<\/td>\n
    		\n
    Southeast<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The CASE<\/code> expression’s ability to short circuit can help to avoid unnecessary processing when it can be substituted for other statement elements. For example, suppose we define the following SELECT<\/code> statement:<\/p>\n
    <\/pre>\n
    Nothing flashy here. For each row, the database engine tests for the three specified conditions and determines whether that row is returned. Consequently, the resultset includes any saleable product that is either black or red, which means 127 rows evaluate to true. Part of those results are shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    ProductName<\/b><\/p>\n<\/td>\n
    		\n
    Color<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Black, 58<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 58<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Red<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sport-100 Helmet, Black<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 62<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 44<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 48<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 52<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    HL Road Frame – Red, 56<\/p>\n<\/td>\n
    		\n
    Red<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 58<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 60<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n
    \n
    LL Road Frame – Black, 62<\/p>\n<\/td>\n
    		\n
    Black<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     Suppose we want to reduce some of the work that the database engine has to perform. We can instead use a CASE<\/code> expression in the WHERE<\/code> clause, as shown in the following SELECT<\/code> statement:<\/p>\n
    <\/pre>\n
    If the Color<\/code> value equals black<\/code>, the condition evaluates to true and the expression stops running, thus avoiding any unnecessary processing. Yet the statement returns the same results as the preceding example.<\/p>\n
    Under the right circumstances, using a CASE<\/code> expression in this way can be a handy way to reduce your workloads. However, a CASE<\/code> expression might not always short circuit in the way you hope. For example, suppose your T-SQL includes logic is similar to that shown in the following example:<\/p>\n
    <\/pre>\n
    All we’re doing is declaring an INT<\/code> variable, setting its value to 0<\/code>, and using a CASE<\/code> expression to retrieve a specific value. Based on our understanding of the CASE<\/code> expression’s ability to short circuit, we would expect the expression’s first condition to evaluate to true and the expression to then stop. However, what we get from our SELECT<\/code> statement is the following error:<\/p>\n
    <\/pre>\n
    The error shows that the ELSE<\/code> condition did in fact run, even though it shouldn’t have. It turns out that under certain circumstances, a CASE<\/code> expression doesn’t behave like it’s supposed to behave. The problem appears to be related to aggregate functions (at least in part), so be aware that in some cases a CASE<\/code> expression might not deliver the benefits you’re hoping for and you might have to come up with a workaround to make the CASE<\/code> expression work the way you want it to.<\/p>\n<\/div>\n\n\n\n
    “What’s the difference between the two ‘not equal to’ operators (<> and !=)?”<\/h3>\n\n\n\n
    \n
    In T-SQL, the two comparisons operators are syntactically equivalent. They each perform a Boolean comparison between two expressions. If those expressions return non-NULL<\/code>, unequal values, the comparison evaluates to true, otherwise, it evaluates to false. However, if either value is  NULL<\/code>, the evaluation returns a NULL<\/code>. Here’s a sample of the two operators in action:<\/p>\n
    <\/pre>\n
     The variable is set to a value of  1<\/code>, and in each SELECT<\/code> statement, that variable is compared to the constant 2<\/code>. As a result, each SELECT<\/code> statement returns the value not<\/code> equal<\/code> because each comparison evaluates to true. Even in terms of performance, there doesn’t appear to be much difference between the two.<\/p>\n
    However, there is one difference you should be aware of. The <><\/code> operator conforms to ANSI standards; the !=<\/code> operator does not, despite that fact that a number of relational database systems support both operators. That said, even if there is only the slightest chance that you might port your script to another system, you’re better off sticking with the  <><\/code> operator.<\/p>\n<\/div>\n\n\n\n
    “I’ve come across operators such as += in variable assignment SET statements. What do they mean?”<\/h3>\n\n\n\n
    \n
    The add-equals operator (+=<\/code>) is a compound operator, one of several types of compound operators supported in SQL Server. A compound operator takes the original value and in some way amends it, rather than replacing it. For example, the following T-SQL uses the add-equals operator to update the value of the @var1<\/code> variable:<\/p>\n
    <\/pre>\n
    We start by declaring @var1<\/code> and setting its value to 2<\/code>. We then use a SET<\/code> statement to add a value of 1<\/code> to the original value. The SELECT<\/code> statement returns the new value, which is 3<\/code>.<\/p>\n
    The add-equals operator makes it possible to add a value without having to specify the original value. Without the add-equals operator we would have to rewrite our SET<\/code> statement as follows:<\/p>\n
    <\/pre>\n
    The add-equals operator merely provides the shorthand necessary to avoid having to explicitly specify the original value, yet the SELECT<\/code> statement still returns a value of 3<\/code>. Plus, SQL Server supports other compound operators as well. For example, the following T-SQL subtracts 1<\/code> from the original value:<\/p>\n
    <\/pre>\n
    In this case, we’re using the subtract-equals (-=<\/code>) operator in our SELECT<\/code> statement, rather than the add-equals operator. The subtract-equals operator works the same way as the add-equals operator, except that the second value is subtracted rather than added. As a result, our SELECT<\/code> statement now returns a value of 1<\/code>.<\/p>\n
    Another example of a compound operator is the multiply-equals operator, which is shown in the following example:<\/p>\n
    <\/pre>\n
    In this case, the SET<\/code> statement uses the multiply-equals operator to multiple the variable value 2<\/code> by the constant value 3<\/code>, giving us a total of 6<\/code>. And compound operators are not limited to mathematical expressions. For example, the following T-SQL includes a SET<\/code> statement that uses the add-equals operator to concatenate values:<\/p>\n
    <\/pre>\n
    In this example, the SET<\/code> statement adds the string value and<\/code> then<\/code> some<\/code> (including the opening space) to the variable value some<\/code> value<\/code>, giving us the value some<\/code> value<\/code> and<\/code> then<\/code> some<\/code>. There are other compound operators as well, such as divide-equals (\/=<\/code>) and modulo-equals (%=<\/code>). Check out the MSDN topic “Compound Operators<\/a>” for more information.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    Nobody seems to ask questions about SQL Expressions in forums, even though expressions can cause all sorts of problems. Even the books on T-SQL skate over them in haste to get to more complicated topics. It is time for frank, straight-forward Q&A, and who better than Robert Sheldon to give the A?…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4183,4252,5771],"coauthors":[6779],"class_list":["post-1854","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-t-sql","tag-t-sql-programming","tag-too-shy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1854","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1854"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1854\/revisions"}],"predecessor-version":[{"id":110603,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1854\/revisions\/110603"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1854"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}