{"id":1837,"date":"2014-07-14T00:00:00","date_gmt":"2014-07-14T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/questions-about-sql-server-data-types-you-were-too-shy-to-ask\/"},"modified":"2021-09-29T16:21:35","modified_gmt":"2021-09-29T16:21:35","slug":"questions-about-sql-server-data-types-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-sql-server-data-types-you-were-too-shy-to-ask\/","title":{"rendered":"Questions about SQL Server Data Types You were Too Shy to Ask"},"content":{"rendered":"
\n

The Questions<\/h2>\n
    \n
  1. “Under what circumstances is character data truncated without triggering an error?”<\/a>.<\/li>\n
  2. “SQL Server’s implicit conversions sometimes lead to unexpected query results, such as a value being returned as a data type different from what I expect. How do implicit conversions work?”<\/a>.<\/li>\n
  3. “I’ve created a query that uses the ISNUMERIC function to verify whether a value is numeric and, if so, convert it to an integer. In some cases, however, the database engine generates an error because it’s trying to convert a value that’s not really a numeric. What’s going on?”<\/a><\/li>\n
  4. “I’m told I should use the BIT data type where possible to save space. Is it true that it saves space or is this just syntactic sugar?”<\/a><\/li>\n
  5. “When I use the DATALENGTH and LEN functions to calculate the size of a string, I often receive different results, even though I’d expect them to be the same. What’s the difference between the two functions?”<\/a>.<\/li>\n
  6. “I’m troubleshooting a stored procedure that uses a COALESCE expression to return the first non-NULL value from a set of columns. However the procedure often generates a conversion error, even if the expression returns is the first column listed. Any idea why this might be occurring?”<\/a>.<\/li>\n
  7. “I’m developing a query that contains a BETWEEN operator in the WHERE clause. The compared data includes a range of consecutive values that contain both letters and numbers, as in XYZ60 through XYZ700. Even when I specify the entire range, values are unexpectedly omitted from my query results. Any idea why this is occurring?”<\/a><\/li>\n
  8. “I’ve seen some T-SQL code that contains ‘national character varying(max)’ listed as that data type, rather than one of the normal types, yet everything seems to work fine. Is this something new?”<\/a>.<\/li>\n
  9. “When is it best to use the CHAR date type rather than VARCHAR?”<\/a>.<\/li>\n
  10. “I’m building a query that joins a FLOAT column to a REAL column, but when I run the statement, it returns no results, even though I can see the matching values in the two columns. Why is this happening?”<\/a>.<\/li>\n
  11. “I have a table that includes a VARCHAR column and a stored procedure that accesses data from the table, based on values in that column. The procedure includes an NVARCHAR parameter used to qualify the WHERE clause, which references the VARCHAR column. Does it matter that the data types are different between the parameter and the column?”<\/a>.<\/li>\n
  12. “My query needs to calculate past and future dates based on an existing date as well as calculate the time difference between two dates. I’ve tried using arithmetic operators to perform the calculations, but the results are never right. How do I go about working with dates?”<\/a>.<\/li>\n
  13. “I’m told never to use SQL_VARIANT data type inappropriately. What is an appropriate use for this data type?”<\/a><\/li>\n<\/ol>\n

    “Under what circumstances is character data truncated without triggering an error?”<\/h3>\n
    \n

    Never, one might hope. But SQL Server is a sneaky devil, preventing truncation some of the time, but not all the time. Sure, if you try to insert too large a value into a column, the database engine baulks, just like it would for the following table: \t \t<\/p>\n

    \tIF OBJECT_ID('OurStuff', 'U') IS NOT NULL\n\tDROP TABLE OurStuff;\n\tGO\n\t\n\tCREATE TABLE OurStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffName VARCHAR(10) NOT NULL\n\t);\n\tGO\n\t\n<\/pre>\n
    The StuffID<\/code> column, the primary key, is  configured with the INT<\/code> data type, and the  \tStuffName<\/code>  column is configured as VARCHAR(10)<\/code>. All very basic. And just as basic is the following  \tINSERT<\/code> statement:<\/p>\n
    \tINSERT INTO OurStuff(StuffName)\n\tVALUES('Really Big Thing');\n<\/pre>\n
     \tWhat we’re trying to do is here is insert a value into the  \tStuffName<\/code>  column that exceeds the data type’s specified length. Not surprisingly, SQL Server kicks out an error:<\/p>\n
    \tMsg 8152, Level 16, State 14, Line 1\n\tString or binary data would be truncated.\n\tThe statement has been terminated.\n<\/pre>\n
     Everything just as we’d expect. The value being inserted is too big, so the database engine  jumps to the rescue, preventing any values from being truncating and, consequently, ensuring integrity of the data. \t \t<\/p>\n
    Unfortunately, SQL Server is not quite so protective with its variables and parameters.  Consider the following T-SQL script, which declares the @stuff<\/code> variable and sets its value to a  string that exceeds the data type’s specified length:<\/p>\n
    \tDECLARE @stuff VARCHAR(10) = 'Really Big Thing';\n\tPRINT @stuff;\n<\/pre>\n
     \tThe database engine doesn’t blink. It simply inserts the string’s first 10 characters (Really<\/code> \tBig<\/code>)  and goes about its business, pretending that all is well, when in fact our variable must limp along with a truncated  value. The PRINT<\/code>  statement merely confirms our suspicions and returns the really wrong Really<\/code>  \tBig<\/code>.<\/p>\n
    It gets worse. Imagine you’re using an ISNULL<\/code> function to compare two variables,  as in the following example:<\/p>\n
    \tDECLARE\n\t  @stuff1 VARCHAR(5) = NULL,\n\t  @stuff2 VARCHAR(10) = 'Big Thing';\n\tPRINT ISNULL(@stuff1, @stuff2);\n\t<\/pre>\n
     \tThe first variable is defined as VARCHAR(5)<\/code> and assigned a value of  \tNULL<\/code>.  The second is defined as VARCHAR(10)<\/code> and assigned the string value  \tBig<\/code> \tThing<\/code>.  So far so good. Now we get to ISNULL<\/code>. The thing with this  function is that the first expression (in this case, @stuff1<\/code>) determines the outputted data  type, even if that expression returns a NULL<\/code>. That means the  \t@stuff2<\/code>  value is confined by @stuff1<\/code> data type constraints. As a result, our  \tPRINT<\/code>  statement, rather than returning Big<\/code>  \tThing<\/code>,  gives us Big<\/code> \tT<\/code>,  which sounds more like a mob boss than a credible data item.<\/p>\n
    Parameters too can fall victim to truncation without warning. Suppose we create the following  stored procedure:<\/p>\n
    \tIF OBJECT_ID('AddStuff', 'P') IS NOT NULL\n\tDROP PROCEDURE AddStuff;\n\tGO\n\t\n\tCREATE PROCEDURE AddStuff \n\t@stuff VARCHAR(10)\n\tAS\n\tINSERT INTO OurStuff(StuffName) VALUES(@stuff);\n\tGO\n\t<\/pre>\n
    A simple example, of course, but it demonstrates an important concept. Notice that the  \t@stuff<\/code>  parameter is defined as VARCHAR(10)<\/code>. Now suppose we pass in a value longer than 10 characters:<\/p>\n
    \tEXEC AddStuff 'Really Big Thing';\n<\/pre>\n
     \tThe database engine runs the procedure without a hitch and returns a message saying that one  row is affected (the row inserted into the OurStuff<\/code> table). We can verify this by  running a simple SELECT<\/code>  statement:<\/p>\n
    \tSELECT * FROM OurStuff;<\/pre>\n
     \tThe results, shown in the following table, indicate that the value has indeed been truncated,  although not once did SQL Server raise an exception.<\/p>\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffName<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    Really Big<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tSo be aware of what can happen when working with variables and parameters. You’ll likely want  to include in your T-SQL code the logic necessary to catch values that might be faulty. Obviously, you can’t rely on the  database engine to do it for you.<\/p>\n<\/p><\/div>\n
    “SQL  Server’s implicit conversions sometimes lead to unexpected query results, such as a value being returned as a data type  different from what I expect. How do implicit conversions work?”<\/h3>\n
    \n
    SQL Server is great at surreptitiously converting values from one type to another:  \tCHAR<\/code>  to VARCHAR<\/code>, \tFLOAT<\/code>  to NCHAR<\/code>, \tBIGINT<\/code>  to MONEY<\/code>.  You get the picture. As long as the values are compatible with the target type, the database engine does all the work,  and you get to kick back and reap the rewards.<\/p>\n
    Sort of.<\/p>\n
    Imagine the following scenario, in which we add a  \tCHAR<\/code>  variable to an INT<\/code>  variable:<\/p>\n
    \tDECLARE\n\t  @stuff1 CHAR(2) = '22',\n\t  @stuff2 INT = 33;\n\tSELECT @stuff1 + @stuff2 AS AllStuff,\n\t  SQL_VARIANT_PROPERTY(@stuff1 + @stuff2, 'basetype') AS BaseType;\n\t<\/pre>\n
     \tWe declare the variables, assign values, and add them together. We also pull the base type of  the returned value, just so we know what’s going on. The following table shows us the results of our  \tSELECT<\/code>  statement:<\/p>\n\n\n\n\n
    \n
    AllStuff<\/b><\/p>\n<\/td>\n
    		\n
    BaseType<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    55<\/p>\n<\/td>\n
    		\n
    int<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tAll looks fine on the surface. We add our INT<\/code> value to our  \tCHAR<\/code>  value and come up with the total (55), returned as an INT<\/code> value. The database engine has  implicitly converted the CHAR<\/code> value to an  \tINT<\/code> value. The  \tINT<\/code>  data type is used because INT<\/code> takes precedence over  \tCHAR<\/code>. SQL Server is quite explicit when it  comes to data type precedence. INT<\/code> wins out over  \tCHAR<\/code>  and VARCHAR<\/code>  every time. If a DATETIME<\/code>  value is tossed into the mix, it will beat out INT<\/code> and just about all other types.  (You can find details about precedence rules in the MSDN topic “Data  Type Precedence<\/a>.”)<\/p>\n
    Returning to our example, SQL Server converts the  \tCHAR<\/code>  value to INT<\/code>  and then proceeds to add the two integers together, giving us a result of 55<\/code>. But what if we didn’t want to add the  values together but concatenate them instead? In that case, we’d have to explicitly convert the  \tINT<\/code>  value:<\/p>\n
    \tDECLARE\n\t  @stuff1 CHAR(2) = '22',\n\t  @stuff2 INT = 33;\n\tSELECT @stuff1 + CAST(@stuff2 AS CHAR(2)) AS AllStuff,\n\t  SQL_VARIANT_PROPERTY(@stuff1 + CAST(@stuff2 AS CHAR(2)), \n\t    'basetype') AS BaseType;\n\t\n<\/pre>\n
    Now our results look quite different. Not only are the two values concatenated, but the value  is also returned as a CHAR<\/code>  type:<\/p>\n\n\n\n\n
    \n
    AllStuff<\/b><\/p>\n<\/td>\n
    		\n
    BaseType<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2233<\/p>\n<\/td>\n
    		\n
    char<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tYou should also be aware of how SQL Server handles implicit conversions for numerical data.  For example, suppose this time around, we create a DECIMAL<\/code> variable and an  \tINT<\/code>  variable, assign a value to the DECIMAL<\/code> variable, and then set the  \tINT<\/code>  variable to equal the DECIMAL<\/code> variable:<\/p>\n
    \tDECLARE\n\t  @stuff1 DECIMAL(8,4) = 1234.9999,\n\t  @stuff2 INT;\n\tSET @stuff2 = @stuff1;\n\tSELECT @stuff1 AS Stuff1, @stuff2 AS Stuff2;\n\t<\/pre>\n
    The SELECT<\/code> statement now returns these results:<\/p>\n\n\n\n\n
    \n
    Stuff1<\/b><\/p>\n<\/td>\n
    		\n
    Stuff2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1234.9999<\/p>\n<\/td>\n
    		\n
    1234<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNotice what the database engine has done. Rather than rounding the value up, as might be  expected, it simply truncates the value, giving us our integer, without too great a concern for the original value. And  the problem isn’t limited to DECIMAL<\/code>–INT<\/code>  conversions. Check out what happens when we go from INT<\/code> to  \tREAL<\/code>  and back again:<\/p>\n
    \tDECLARE \n\t  @stuff1 INT = 1080000000,\n\t  @stuff2 INT = 1080000016,\n\t  @stuff3 REAL;\n\tSET @stuff3 = @stuff2;\n\tSELECT \n\t  @stuff3 AS Stuff3,\n\t  CAST(@stuff3 AS INT) AS Stuff3int,\n\t  CASE\n\t    WHEN @stuff3 = @stuff1\n\t    THEN 'yes'\n\t    ELSE 'no'\n\t  END AS IsEqual;\n\t<\/pre>\n
     \tWe declare the INT<\/code> variables and set their values,  and then declare a REAL<\/code>  variable and set its value to equal the second INT<\/code> variable. In our  \tSELECT<\/code>  statement, we then retrieve the REAL<\/code> variable as it is stored, convert it  to an INT<\/code>, and then check whether the  first and third variables are equal. The following table shows our results:<\/p>\n\n\n\n\n
    \n
    Stuff3<\/b><\/p>\n<\/td>\n
    		\n
    Stuff3in<\/b><\/p>\n<\/td>\n
    		\n
    IsEqual<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1.08E+09<\/p>\n<\/td>\n
    		\n
    1080000000<\/p>\n<\/td>\n
    		\n
    yes<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNotice that the Stuff3<\/code> value appears as scientific  notation because the @stuff3<\/code> variable had insufficient precision to hold the original integer. In addition,  when the variable is converted to an integer, it returns a value different from what it was assigned. Instead, it now  equals the value stored in @stuff1<\/code>.  \t \t<\/p>\n
    In many cases, you shouldn’t leave it up to the database engine to do your conversions, no  matter how tempting that might be. In fact, some developers insist that all conversions should be explicit. At the very  least, be sure that whenever the integrity of the data could be brought into question, error on the side of converting  the data explicitly.<\/p>\n<\/p><\/div>\n
    “I’ve  created a query that uses the ISNUMERIC<\/code> function to verify whether a value is numeric and, if so, convert it to an integer. In some cases, however, the database  engine generates an error because it’s trying to convert a value that’s not really a numeric. What’s going on?”<\/h3>\n
    \n
    The ISNUMERIC<\/code> function can be a slippery one.  On the surface, its purpose appears only to determine whether the submitted value is indeed a valid numeric type. But  looks can be deceiving. Take the following T-SQL script. It creates a table with a  \tVARCHAR<\/code>  column and then adds a mix of values to that column:<\/p>\n
    \tIF OBJECT_ID('OurStuff', 'U') IS NOT NULL\n\tDROP TABLE OurStuff;\n\tGO\n\t\n\tCREATE TABLE OurStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffName VARCHAR(10) NOT NULL\n\t);\n\tGO\n\t\n\tINSERT INTO OurStuff(StuffName)\n\tVALUES('abc'), ('def123'), ('456'), ('$789'), ('1d1'), (','), ('.'), ('$.,');\n\t\n\t<\/pre>\n
    Nothing extraordinary, just your run-of-the-mill alphanumeric hodgepodge. But now let’s look  at what happens when we apply the ISNUMERIC<\/code> function to the  \tStuffName<\/code>  column:<\/p>\n
    \tSELECT StuffID, StuffName, ISNUMERIC(StuffName) AS IsNumber\n\tFROM OurStuff;\n<\/pre>\n
    All we’re doing is pulling the table’s two columns, as well as returning a third column to  test each value’s numeric status. The ISNUMERIC<\/code> function returns a  \t1<\/code> if  the tested value appears to be a numeric data type, otherwise returns a 0<\/code>. Here’s what we get:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffName<\/b><\/p>\n<\/td>\n
    		\n
    IsNumber<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    abc<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    def123<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    456<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    $789<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    1d1<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    ,<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    .<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    8<\/p>\n<\/td>\n
    		\n
    $.,<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tIt should come as no surprise that the first two values are considered non-numeric and the  third value is. However, the function also considers $789<\/code> to be numeric, despite the dollar sign  (or more precisely, because of the dollar sign), and 1d1<\/code> to be numeric because of its  resemblance to scientific notation, whether or not that is the intent.<\/p>\n
    The sky might not fall because of these interpretations, but they could cause problems if you  want to perform other operations based on the output of the ISNUMERIC<\/code> function. For example,  suppose we want to explicitly convert each value to an INT<\/code> based on whether we think that value is  numeric:<\/p>\n
    \tSELECT StuffID, StuffName,\n\t  CASE \n\t    WHEN ISNUMERIC(StuffName) = 1  \n\t    THEN CAST(StuffName AS INT)\n\t    ELSE NULL \n\t  END AS CaseResult\n\tFROM OurStuff\n\tWHERE StuffID BETWEEN 1 AND 3;\n<\/pre>\n
    In this case, we’re retrieving only the first three rows from our table. If the  \tISNUMERIC<\/code>  function returns a 1<\/code>,  we convert the value to the INT<\/code> type; otherwise, we return a  \tNULL<\/code>  value. As to be expected, the first two rows each return a NULL<\/code> and the third row returns the  value 456<\/code> as an  \tINT<\/code>,  as shown in the following results:<\/p>\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffName<\/b><\/p>\n<\/td>\n
    		\n
    CaseResult<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    abc<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    def123<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    456<\/p>\n<\/td>\n
    		\n
    456<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNow let’s retrieve the fourth row from the table:<\/p>\n
    \tSELECT StuffID, StuffName,\n\t  CASE \n\t    WHEN ISNUMERIC(StuffName) = 1  \n\t    THEN CAST(StuffName AS INT)\n\t    ELSE NULL \n\t  END AS CaseResult\n\tFROM OurStuff\n\tWHERE StuffID = 4;\n\t<\/pre>\n
    This time around, we receive the following error message, indicating that we cannot convert  that value to an INT<\/code>:<\/p>\n
    \tMsg 245, Level 16, State 1, Line 1\n\tConversion failed when converting the varchar value '$789' to data type int.\n<\/pre>\n
    This shouldn’t come as too big of a surprise. The  \tINT<\/code>  data type has never been fond of dollar signs.  The same goes for scientific  notation. If we were to try to run the query against the last row in our table, we would receive a similar error.<\/p>\n
    And it’s not just dollar signs and possible scientific notation that can elicit problems. The \tISNUMERIC<\/code> function recognizes an  assortment of characters as being numeric (in addition to actual numbers), such as plus signs and minus signs and  periods and tabs and commas. Take a look at the following example, in which we assign a tab to the  \t@tab<\/code>  variable:<\/p>\n
    \tDECLARE @tab VARCHAR(10) = '      ';\n\tPRINT ISNUMERIC(@tab);\n<\/pre>\n
    As odd as this might seem, the ISNUMERIC<\/code> function returns a  \t1<\/code>,  as it will in the next example, in which we pass in a plus sign:<\/p>\n
    \tDECLARE @plus NVARCHAR(10) = '+';\n\tPRINT ISNUMERIC(@plus);\n\t<\/pre>\n
    If you’re working with a version of SQL Server prior to 2012, you’ll have to come up with  some sort of workaround to check for those circumstances in which the apparent numeric value would generate an error.  You might, for example, create a function or add a CASE<\/code> statement to setup complex  test conditions.  \t \t<\/p>\n
    For those of you working with SQL Server 2012 or later, you have a better alternative-the \tTRY_CONVERT<\/code>  \t function:<\/p>\n
    \tSELECT StuffID, StuffName,\n\t  CASE \n\t    WHEN ISNUMERIC(StuffName) = 1  \n\t    THEN TRY_CONVERT(INT, StuffName)\n\t   ELSE NULL \n\t  END AS CaseResult\n\tFROM OurStuff;\n\t\n<\/pre>\n
    The function first tests whether the requested conversion will work. If so, it converts the  data to the target data type. If not, it returns a NULL<\/code>:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffName<\/b><\/p>\n<\/td>\n
    		\n
    CaseResult<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    abc<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    def123<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    456<\/p>\n<\/td>\n
    		\n
    456<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    $789<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    1d1<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    ,<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    .<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    $.,<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the only number that can be converted is  \t456<\/code>.  The rest are there only to complicate the lives of overworked DBAs with nothing better to do than to search for wayward  numbers.<\/p>\n<\/p><\/div>\n
    “I’m told I should use the \t \tBIT<\/code> data type where possible to save  space. Is it true that it saves space or is this just syntactic sugar?”<\/h3>\n
    \n
    Yes, the BIT<\/code> data type can save space under certain  circumstances, and it undoubtedly has some advantages over an approach such as using  \tCHAR(1)<\/code>, which requires a  \tCHECK<\/code>  constraint to limit the values in the same way BIT<\/code> already does. With the  \tBIT<\/code>  data type, you can insert only the values 1<\/code> and  \t0<\/code>,  as well as NULL<\/code>  values, assuming the object is nullable.  \t \t<\/p>\n
    That said, a BIT<\/code> data type still uses a byte of  data, just like CHAR(1)<\/code>  or TINYINT<\/code>.  Some might have assumed that the BIT<\/code> type translates into only one bit of  data, given the type’s tie-in to the 1<\/code> and  \t0<\/code>  value limits. But that’s hardly the case. BIT<\/code> is actually in integer data type and  consequently requires more legroom. Case in point:<\/p>\n
    \tDECLARE @stuffed BIT = 0;\n\tSELECT\n\t  @stuffed AS Stuffed,\n\t  DATALENGTH(@stuffed) AS BitLength;\n\t<\/pre>\n
    As you can see in the following results, the value does indeed use one byte:<\/p>\n\n\n\n\n
    \n
    Stuffed<\/b><\/p>\n<\/td>\n
    		\n
    BitLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tHowever, if you create a table with multiple BIT<\/code> columns, the database engine is clever  enough to store the column data in a single byte, up to eight columns. If your table contains between 9 and 16  \tBIT<\/code>  columns, SQL Server stores the data as two bytes. Between 17 and 24 BIT<\/code> columns? Three bytes, and so  on. You get the picture.<\/p>\n
    That said, you should still be wary about implementing  \tBIT<\/code>.  It can have unexpected results when used in an expression, and it’s not necessarily well suited to masquerading as a bit  flag or bit mask. For an interesting take on the whole issue, check out Joe Celko’s excellent article “BIT  of a Problem<\/a>.”<\/p>\n<\/p><\/div>\n
    “When  I use the  \tDATALENGTH<\/code> and  \tLEN<\/code> functions to calculate the size of a  string, I often receive different results, even though I’d expect them to be the same. What’s the difference between the  two functions?”<\/h3>\n
    \n
    The DATALENGTH<\/code> function returns the number of  bytes used by an expression, and the LEN<\/code> function returns the number of  characters contained in an expression. The LEN<\/code> function is also limited to string  expressions; whereas, the DATALENGTH<\/code> function supports  expressions of any type.  \t \t<\/p>\n
    To better understand how they both work, let’s take a look at them in action. The following  T-SQL script creates a table with four columns, each configured with a character data type, and then inserts data into  those columns:<\/p>\n
    \tIF OBJECT_ID('TestLength', 'U') IS NOT NULL\n\tDROP TABLE TestLength;\n\tGO\n\t\n\tCREATE TABLE TestLength\n\t(\n\t  char1 CHAR(10),\n\t  nchar2 NCHAR(10),\n\t  varchar3 VARCHAR(10),\n\t  nvarchar4 NVARCHAR(10)\n\t);\n\tGO\n\t\n\tINSERT INTO TestLength\n\tVALUES('stuff', 'stuff', 'stuff', 'stuff');\n\tINSERT INTO TestLength\n\tVALUES('stuff   ', 'stuff   ', 'stuff   ', 'stuff   ');\n\t<\/pre>\n
    Notice that, in the final INSERT<\/code> statement, I’ve added  several spaces after each value. You’ll see why in a moment. But first, let’s retrieve data from each of the columns,  applying both the LEN<\/code>  and DATALENGTH<\/code>  functions:<\/p>\n
    \tSELECT \n\t  LEN(char1) AS len1,\n\t  DATALENGTH(char1) AS datalength1,\n\t  LEN(nchar2) AS len2,\n\t  DATALENGTH(nchar2) AS datalength2,\n\t  LEN(varchar3) AS len3,\n\t  DATALENGTH(varchar3) AS datalength3,\n\t  LEN(nvarchar4) AS len4,\n\t  DATALENGTH(nvarchar4) AS datalength4\n\tFROM TestLength;\n\t\n<\/pre>\n
    The following table shows the statement’s output:<\/p>\n\n\n\n\n\n
    \n
    len1<\/b><\/p>\n<\/td>\n
    		\n
    datalength1<\/b><\/p>\n<\/td>\n
    		\n
    len2<\/b><\/p>\n<\/td>\n
    		\n
    datalength2<\/b><\/p>\n<\/td>\n
    		\n
    len3<\/b><\/p>\n<\/td>\n
    		\n
    datalength3<\/b><\/p>\n<\/td>\n
    		\n
    len4<\/b><\/p>\n<\/td>\n
    		\n
    datalength4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    10<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n
    		\n
    20<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n
    		\n
    10<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    10<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n
    		\n
    20<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n
    		\n
    8<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n
    		\n
    16<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tThe len1<\/code> and  \tdatalength1<\/code>  columns in the resultset refer to the char1<\/code> column in the  \tTestLength<\/code>  table. The column is configured as CHAR(10)<\/code>. The  \tLEN<\/code>  function provides of the actual number of characters being used (5<\/code>), not including the trailing  spaces. The DATALENGTH<\/code>  function indicates that 10 bytes are being used, despite the actual number of characters. This is because the column is  configured at a fixed width of 10<\/code>. As a result, the  \tDATALENGTH<\/code>  function can provide us with a more accurate view of our storage requirements.<\/p>\n
    The len2<\/code> and  \tdatalength2<\/code>  columns in the resultset refer to the nchar2<\/code> column and work much the  same way, except that it is a Unicode column configured as NCHAR(10)<\/code>. The  \tLEN<\/code>  function still shows that five characters are being used (not including spaces), but the  \tDATALENGTH<\/code>  function indicates that the value actually requires 20 bytes, just like we’d expect.<\/p>\n
    When we get to the len3<\/code> and  \tdatalength3<\/code>  columns, the results get even more interesting. This data is based on the varchar3<\/code> column, which is configured as \tVARCHAR(10)<\/code>. Even so, the  \tLEN<\/code>  function still indicates that only five characters are being used. However, because we’re working with a  \tVARCHAR<\/code>  column, the DATALENGTH<\/code>  function indicates that five bytes are being used by the value in the first row and eight bytes by the value in the  second row (to accommodate the trailing spaces). The len4<\/code> and  \tdatalength4<\/code>  columns work in the same way, only the number of bytes is doubled in each case to account for the Unicode type.<\/p>\n
    As mentioned earlier, although the LEN<\/code> function is limited to string data, you  can use the DATALENGTH<\/code>  function on an expression of any data type. For example, the following T-SQL declares an  \tINT<\/code>  variable, assigns a value, and then checks the number of bytes the variable uses:<\/p>\n
    \tDECLARE @int INT = 1234567;\n\tSELECT @int AS IntNew, DATALENGTH(@int) AS IntLength;\n\t<\/pre>\n
     \tThe SELECT<\/code> statement returns the results shown  in the following table, which indicate that the value uses four bytes, the expected length for the  \tINT<\/code>  data type:<\/p>\n\n\n\n\n
    \n
    IntNew<\/p>\n<\/td>\n
    		\n
    IntLength<\/p>\n<\/td>\n<\/tr>\n
    \n
    1234567<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tEven if our integer were made up of only two digits, the results would be the same. Same goes  for a DATETIME<\/code> value, in terms of  consistency:<\/p>\n
    \tDECLARE @now DATETIME = GETDATE();\n\tSELECT @now AS DateNow, DATALENGTH(@now) AS DateLength;\n<\/pre>\n
     \tIn this case, the DATALENGTH<\/code> function returns eight  bytes:<\/p>\n\n\n\n\n
    \n
    DateNow<\/b><\/p>\n<\/td>\n
    		\n
    DateLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2014-07-05 21:32:23.507<\/p>\n<\/td>\n
    		\n
    8<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    We would expect eight bytes because DATETIME<\/code> values are stored as two integers,  one for the date and one for the time.<\/p>\n<\/p><\/div>\n
    “I’m  troubleshooting a stored procedure that uses a  \tCOALESCE<\/code>  expression to return the first non-NULL  value from a set of columns. However the procedure often generates a conversion error, even if the expression returns is  the first column listed. Any idea why this might be occurring?”<\/h3>\n
    \n
    A COALESCE<\/code> expression can be a bit tricky to  get right at first. It is essentially shorthand for a type of CASE<\/code> expression that evaluates a series of  expressions, often columns or variables, and returns the first one that does not evaluate to  \tNULL<\/code>. Let’s look how it works. The  following T-SQL declares several variables and assigns values to them, based on their respective types:<\/p>\n
    \tDECLARE\n\t  @stuffNull VARCHAR(5) = NULL,\n\t  @stuffID INT = 1001,\n\t  @stuffName VARCHAR(10) = 'Big Thing';\n\tSELECT \n\t  COALESCE(@StuffNull, @StuffID, @StuffName) AS NotNullStuff,\n\t  SQL_VARIANT_PROPERTY(COALESCE(@StuffNull, @StuffID, @StuffName), 'basetype') AS BaseType;\n\t<\/pre>\n
    The COALESCE<\/code> expression takes the three  variables as an argument. Because the @StuffNull<\/code> variable contains a  \tNULL<\/code>  value, it will be skipped and we’ll move to the next variable, @StuffID<\/code>. This one contains a value, so the \tSELECT<\/code> statement returns that value  in its results:<\/p>\n\n\n\n\n
    \n
    NotNullStuff<\/b><\/p>\n<\/td>\n
    		\n
    BaseType<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1001<\/p>\n<\/td>\n
    		\n
    int<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tEverything seems in order here. The @StuffID<\/code> variable does not contain a  \tNULL<\/code>, so that value is returned,  and it is indeed an integer, just as we would expect. But what if we change the order of our variables:<\/p>\n
    \tDECLARE\n\t  @stuffNull VARCHAR(5) = NULL,\n\t  @stuffID INT = 1001,\n\t  @stuffName VARCHAR(10) = 'Big Thing';\n\tSELECT \n\t  COALESCE(@StuffNull, @StuffName, @StuffID) AS NotNullStuff,\n\t  SQL_VARIANT_PROPERTY(COALESCE(@StuffNull, @StuffID, @StuffName), 'basetype') AS BaseType;\n\t<\/pre>\n
     \tThe @StuffName<\/code> variable does not contain a \tNULL<\/code>  value, so that variable’s value should be returned. However, the statement instead generates the following error:<\/p>\n
    \tMsg 245, Level 16, State 1, Line 5\n\tConversion failed when converting the varchar value 'Big Thing' to data type int.\n<\/pre>\n
     \tThe problem is that a COALESCE<\/code> expression uses the data  type with the highest precedence. It does not matter what order the arguments are presented. Because  \tINT<\/code>  has a higher precedence than VARCHAR<\/code>, the returned type is an \tINT<\/code>, which means the database  engine is trying to convert the Big<\/code>  \tThing<\/code>  string to an integer. It just doesn’t work.<\/p>\n
    Data type precedence also applies to a type’s length:<\/p>\n
    \tDECLARE\n\t  @stuffNull VARCHAR(5) = NULL,\n\t  @stuffName1 VARCHAR(10) = 'Big Thing',\n\t  @stuffName2 VARCHAR(15) = 'Next Big Thing';\n\tSELECT \n\t  COALESCE(@StuffNull, @StuffName1, @StuffName2) AS NotNullStuff,\n\t  SQL_VARIANT_PROPERTY(COALESCE(@StuffNull, @StuffName1, @StuffName2), 'basetype') AS BaseType,\n\t  SQL_VARIANT_PROPERTY(COALESCE(@StuffNull, @StuffName1, @StuffName2), 'maxlength') AS BaseLength;\n<\/pre>\n
     \tBecause @StuffName1<\/code> is the first variable to  contain a non-null value, that value is returned. However, because @StuffName2<\/code> has a greater length (15<\/code>  as opposed to 10<\/code>),  that is the data type of the returned value, as the following table shows:<\/p>\n\n\n\n\n
    \n
    NotNullStuff<\/b><\/p>\n<\/td>\n
    		\n
    BaseType<\/b><\/p>\n<\/td>\n
    		\n
    BaseLength<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Big Thing<\/p>\n<\/td>\n
    		\n
    varchar<\/p>\n<\/td>\n
    		\n
    15<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tAlthough both variables are VARCHAR<\/code>, the  \t15<\/code>  length has precedence over the 10<\/code> length, just like  \tINT<\/code>  has precedence over a VARCHAR<\/code>.<\/p>\n<\/p><\/div>\n
    “I’m developing a query that  contains a  \tBETWEEN<\/code> operator in the  \t \tWHERE<\/code> clause. The compared data includes  a range of consecutive values that contain both letters and numbers, as in  \tXYZ60<\/code> through  \tXYZ700<\/code>. Even when I specify the entire range, values are  unexpectedly omitted from my query results. Any idea why this is occurring?”<\/h3>\n
    \n
    When you use the BETWEEN<\/code> operator for non-numerical  data, you have to keep in mind how SQL Server sorts and searches for data. The example data you provide is considered  character data (non-numerical), so the returned range of values follow the rules of the database’s configured collation,  which specifies how character data is sorted and compared, based on language and usage norms.  \t \t<\/p>\n
    The best way to understand this is to see it in action. The following T-SQL creates the \tOurStuff<\/code> table, which includes a \tVARCHAR<\/code>  column and DATEIME<\/code>  column, and inserts a number of rows into those columns:<\/p>\n
    \tIF OBJECT_ID('OurStuff', 'U') IS NOT NULL\n\tDROP TABLE OurStuff;\n\tGO\n\t\n\tCREATE TABLE OurStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffProdID VARCHAR(10) NOT NULL,\n\t  StuffDate DATETIME NOT NULL\n\t);\n\tGO\n\t\n\tINSERT INTO OurStuff(StuffProdID, StuffDate) VALUES\n\t  ('ab38', '2014-07-06 12:46:48.240'),\n\t  ('ab39', '2014-07-08 10:46:48.240'),\n\t  ('ab48', '2014-07-09 08:46:48.240'),\n\t  ('ab49', '2014-07-10 06:46:48.240'),\n\t  ('ab58', '2014-07-11 04:46:48.240'),\n\t  ('ab59', '2014-07-12 02:46:48.240'),\n\t  ('ab398', '2014-07-13 22:46:48.240'),\n\t  ('ab399', '2014-07-14 20:46:48.240'),\n\t  ('ab400', '2014-07-15 18:46:48.240'),\n\t  ('cd38', '2014-07-16 16:46:48.240'),\n\t  ('cd39', '2014-07-17 14:46:48.240'),\n\t  ('cd48', '2014-07-18 12:46:48.240'),\n\t  ('cd49', '2014-07-19 10:46:48.240'),\n\t  ('cd58', '2014-07-20 11:46:48.240'),\n\t  ('cd59', '2014-07-21 12:46:48.240'),\n\t  ('cd398', '2014-07-22 13:46:48.240'),\n\t  ('cd399', '2014-07-23 14:46:48.240'),\n\t  ('cd400', '2014-07-24 15:46:48.240');\n<\/pre>\n
     \tNow suppose we issue a simple query that retrieves the rows of data based on  \tStuffID<\/code>  values 6<\/code>  through 16<\/code>:<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffID BETWEEN 6 AND 16;\n\t<\/pre>\n
    Not surprisingly, the statement returns the results shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffProdID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    ab59<\/p>\n<\/td>\n
    		\n
    2014-07-12 02:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    ab398<\/p>\n<\/td>\n
    		\n
    2014-07-13 22:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    8<\/p>\n<\/td>\n
    		\n
    ab399<\/p>\n<\/td>\n
    		\n
    2014-07-14 20:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    9<\/p>\n<\/td>\n
    		\n
    ab400<\/p>\n<\/td>\n
    		\n
    2014-07-15 18:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    10<\/p>\n<\/td>\n
    		\n
    cd38<\/p>\n<\/td>\n
    		\n
    2014-07-16 16:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    11<\/p>\n<\/td>\n
    		\n
    cd39<\/p>\n<\/td>\n
    		\n
    2014-07-17 14:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    12<\/p>\n<\/td>\n
    		\n
    cd48<\/p>\n<\/td>\n
    		\n
    2014-07-18 12:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    13<\/p>\n<\/td>\n
    		\n
    cd49<\/p>\n<\/td>\n
    		\n
    2014-07-19 10:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    14<\/p>\n<\/td>\n
    		\n
    cd58<\/p>\n<\/td>\n
    		\n
    2014-07-20 11:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    15<\/p>\n<\/td>\n
    		\n
    cd59<\/p>\n<\/td>\n
    		\n
    2014-07-21 12:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    16<\/p>\n<\/td>\n
    		\n
    cd398<\/p>\n<\/td>\n
    		\n
    2014-07-22 13:46:48.240<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \the BETWEEN<\/code> operator does exactly what we  expect. It includes rows 6<\/code>  through 16<\/code>  and omits the rest. However, now suppose we modify the BETWEEN<\/code> clause to retrieve data  based on the StuffProdID<\/code> column:<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffProdID BETWEEN 'a' AND 'c';\n\t<\/pre>\n
    Now our results include only the first nine rows:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffProdID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    ab38<\/p>\n<\/td>\n
    		\n
    2014-07-06 12:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    ab39<\/p>\n<\/td>\n
    		\n
    2014-07-08 10:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    ab48<\/p>\n<\/td>\n
    		\n
    2014-07-09 08:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    ab49<\/p>\n<\/td>\n
    		\n
    2014-07-10 06:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    ab58<\/p>\n<\/td>\n
    		\n
    2014-07-11 04:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    ab59<\/p>\n<\/td>\n
    		\n
    2014-07-12 02:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    ab398<\/p>\n<\/td>\n
    		\n
    2014-07-13 22:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    8<\/p>\n<\/td>\n
    		\n
    ab399<\/p>\n<\/td>\n
    		\n
    2014-07-14 20:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    9<\/p>\n<\/td>\n
    		\n
    ab400<\/p>\n<\/td>\n
    		\n
    2014-07-15 18:46:48.240<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tAlthough we’ve specified that the letter c<\/code> as the end of the range, no rows whose \tStuffProdID<\/code> value starts with that  letter are included. They are not part of the acceptable range of values, based on the sort order defined by the  collation. In other words, all values that start with c<\/code> include other characters, so they’re  considered out of range.  \t \t<\/p>\n
    To help clarify this, let’s sort our table based on the  \tStuffProdID<\/code>  column:<\/p>\n
    \tSELECT * FROM OurStuff\n\tORDER BY StuffProdID;\n\t<\/pre>\n
    The data in the StuffProdID<\/code> column is sorted based  on the rules of the database collation:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffProdID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    ab38<\/p>\n<\/td>\n
    		\n
    2014-07-06 12:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    ab39<\/p>\n<\/td>\n
    		\n
    2014-07-08 10:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    ab398<\/p>\n<\/td>\n
    		\n
    2014-07-13 22:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    8<\/p>\n<\/td>\n
    		\n
    ab399<\/p>\n<\/td>\n
    		\n
    2014-07-14 20:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    9<\/p>\n<\/td>\n
    		\n
    ab400<\/p>\n<\/td>\n
    		\n
    2014-07-15 18:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    ab48<\/p>\n<\/td>\n
    		\n
    2014-07-09 08:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    ab49<\/p>\n<\/td>\n
    		\n
    2014-07-10 06:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    ab58<\/p>\n<\/td>\n
    		\n
    2014-07-11 04:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    ab59<\/p>\n<\/td>\n
    		\n
    2014-07-12 02:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    10<\/p>\n<\/td>\n
    		\n
    cd38<\/p>\n<\/td>\n
    		\n
    2014-07-16 16:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    11<\/p>\n<\/td>\n
    		\n
    cd39<\/p>\n<\/td>\n
    		\n
    2014-07-17 14:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    16<\/p>\n<\/td>\n
    		\n
    cd398<\/p>\n<\/td>\n
    		\n
    2014-07-22 13:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    17<\/p>\n<\/td>\n
    		\n
    cd399<\/p>\n<\/td>\n
    		\n
    2014-07-23 14:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    18<\/p>\n<\/td>\n
    		\n
    cd400<\/p>\n<\/td>\n
    		\n
    2014-07-24 15:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    12<\/p>\n<\/td>\n
    		\n
    cd48<\/p>\n<\/td>\n
    		\n
    2014-07-18 12:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    13<\/p>\n<\/td>\n
    		\n
    cd49<\/p>\n<\/td>\n
    		\n
    2014-07-19 10:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    14<\/p>\n<\/td>\n
    		\n
    cd58<\/p>\n<\/td>\n
    		\n
    2014-07-20 11:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    15<\/p>\n<\/td>\n
    		\n
    cd59<\/p>\n<\/td>\n
    		\n
    2014-07-21 12:46:48.240<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tThe data is sorted first by the first character, then by the second character, within the  first-character grouping, next by the third character, within the second-character grouping, and so on. If we reconsider  our BETWEEN<\/code>  clause (BETWEEN<\/code> \t'a'<\/code> \tAND<\/code> \t'c'<\/code>),  we’ll see that the range includes all the values that start with a, but none that start with  \tc<\/code>.  If we had a c<\/code>-only  value, that would be included, but in this case, all of our values that start with  \tc<\/code>  include other characters as well and consequently are outside the range specified in the  \tBETWEEN<\/code>  clause. Now let’s specify a more inclusive range:<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffProdID BETWEEN 'ab3' AND 'cd4';\n\t<\/pre>\n
    Because our range includes all rows up to cd4<\/code>, our resultset includes several more  rows then it did when we specified only c<\/code> as the upper end of the range:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffProdID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    ab38<\/p>\n<\/td>\n
    		\n
    2014-07-06 12:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    ab39<\/p>\n<\/td>\n
    		\n
    2014-07-08 10:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    ab48<\/p>\n<\/td>\n
    		\n
    2014-07-09 08:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    ab49<\/p>\n<\/td>\n
    		\n
    2014-07-10 06:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    ab58<\/p>\n<\/td>\n
    		\n
    2014-07-11 04:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    ab59<\/p>\n<\/td>\n
    		\n
    2014-07-12 02:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    ab398<\/p>\n<\/td>\n
    		\n
    2014-07-13 22:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    8<\/p>\n<\/td>\n
    		\n
    ab399<\/p>\n<\/td>\n
    		\n
    2014-07-14 20:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    9<\/p>\n<\/td>\n
    		\n
    ab400<\/p>\n<\/td>\n
    		\n
    2014-07-15 18:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    10<\/p>\n<\/td>\n
    		\n
    cd38<\/p>\n<\/td>\n
    		\n
    2014-07-16 16:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    11<\/p>\n<\/td>\n
    		\n
    cd39<\/p>\n<\/td>\n
    		\n
    2014-07-17 14:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    16<\/p>\n<\/td>\n
    		\n
    cd398<\/p>\n<\/td>\n
    		\n
    2014-07-22 13:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    17<\/p>\n<\/td>\n
    		\n
    cd399<\/p>\n<\/td>\n
    		\n
    2014-07-23 14:46:48.240<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNote, however, we’re still missing any rows that start with  \tcd4<\/code>  or cd5<\/code>  because they fall outside the specified range. If you refer back to the fully sorted resultset, you’ll see that the  values cd400<\/code>  through cd59<\/code>  are at the end of the result set, after cd4<\/code>.<\/p>\n
    The following example demonstrates this concept further:<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffProdID BETWEEN 'ab38' AND 'ab400';\n\t<\/pre>\n
    Now our results include only five rows, those that fall within the specified range. As you  can see in the following results, ab400<\/code> is now included because we specified  the entire value, not just the first part of it:<\/p>\n\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffProdID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    ab38<\/p>\n<\/td>\n
    		\n
    2014-07-06 12:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    ab39<\/p>\n<\/td>\n
    		\n
    2014-07-08 10:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    ab398<\/p>\n<\/td>\n
    		\n
    2014-07-13 22:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    8<\/p>\n<\/td>\n
    		\n
    ab399<\/p>\n<\/td>\n
    		\n
    2014-07-14 20:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    9<\/p>\n<\/td>\n
    		\n
    ab400<\/p>\n<\/td>\n
    		\n
    2014-07-15 18:46:48.240<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tIf you’re working with DATETIME<\/code> values, you also need to  be aware of how the data is sorted and compared. The following example specifies a date range three days apart:<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffDate BETWEEN '20140710' AND '20140713';\n\t<\/pre>\n
    Because the range specified in a BETWEEN<\/code> clause is inclusive, we might  expect four rows to be returned, but instead we get only three:  \t \t<\/p>\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffProdID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    ab49<\/p>\n<\/td>\n
    		\n
    2014-07-10 06:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    ab58<\/p>\n<\/td>\n
    		\n
    2014-07-11 04:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    ab59<\/p>\n<\/td>\n
    		\n
    2014-07-12 02:46:48.240<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tWhen working with DATETIME<\/code> values, keep in mind that  the value is always made up of the date and<\/i> time, down to a thousandth of a  second. The time part is factored into the range calculations, so you must take that part into account when you’re  specifying your range. If you don’t specify a specific time, as is the case in our  \tSELECT<\/code> statement, SQL Server  assumes the time is midnight-all zeroes.  \t \t<\/p>\n
    One approach to dealing with this issue is to specify the entire  \tDATETIME<\/code>  values:<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffDate BETWEEN '2014-07-10 06:46:48.240' \n\t  AND '2014-07-13 22:46:48.240';\n<\/pre>\n
     \tThis way, our results will include all rows whose  \tDATETIME<\/code>  values fall within this very specific range:<\/p>\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffProdID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    ab49<\/p>\n<\/td>\n
    		\n
    2014-07-10 06:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    ab58<\/p>\n<\/td>\n
    		\n
    2014-07-11 04:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    ab59<\/p>\n<\/td>\n
    		\n
    2014-07-12 02:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    ab398<\/p>\n<\/td>\n
    		\n
    2014-07-13 22:46:48.240<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tThe trouble with this approach, however, is that it can get fairly cumbersome. Instead of  using the BETWEEN<\/code> operator, you might  consider the greater than or equal to operator (>=<\/code>)  along with the lesser than operator (<<\/code>):<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffDate >= '20140710' \n\t  AND StuffDate < '20140714';\n\t<\/pre>\n
    For the beginning of the range, we specify that the date be greater than or equal to the  specified date, which puts us right at midnight. We then use the lesser than operator to specify one day past our  intended date. Because it will also be set to midnight, it will pick up anything before midnight on the previous day.  The following table shows the results now returned by the SELECT<\/code> statement:<\/p>\n\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffProdID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    4<\/p>\n<\/td>\n
    		\n
    ab49<\/p>\n<\/td>\n
    		\n
    2014-07-10 06:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    5<\/p>\n<\/td>\n
    		\n
    ab58<\/p>\n<\/td>\n
    		\n
    2014-07-11 04:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    6<\/p>\n<\/td>\n
    		\n
    ab59<\/p>\n<\/td>\n
    		\n
    2014-07-12 02:46:48.240<\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    ab398<\/p>\n<\/td>\n
    		\n
    2014-07-13 22:46:48.240<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    “I’ve seen some T-SQL code  that contains ‘national<\/code>  \tcharacter<\/code>  \tvarying(max)<\/code>‘ listed as that data type, rather than one of the \tnormal<\/i> types, yet everything seems to work fine. Is this something new?”<\/h3>\n
    \n
    There’s nothing new about national character data types such as  \tNATIONAL<\/code> \tCHARACTER<\/code> \tVARYING<\/code>.  SQL Server includes them as synonyms to appease the ISO gods in order to conform to the international standards set by  that organization. In fact, many of the SQL Server data types include ISO synonyms. (The TechNet topic “Data  Type Synonyms<\/a>” lists all the ISO synonyms.)<\/p>\n
    You can use the synonyms in place of the names of the base data types in your data definition  language (DDL) statements. However, once you create the object, the synonyms are no longer used. For example, the  following T-SQL script creates a table that includes columns configured with the  \tNVARCHAR<\/code>  and NATIONAL<\/code> \tCHARACTER<\/code> \tVARYING<\/code>  data types:<\/p>\n
    \tIF OBJECT_ID('OurStuff', 'U') IS NOT NULL\n\tDROP TABLE OurStuff;\n\tGO\n\t\n\tCREATE TABLE OurStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffName1 NVARCHAR(MAX) NOT NULL,\n\t  Stuffname2 NATIONAL CHARACTER VARYING(MAX) NOT NULL\n\t);\n\tGO\n\t\n\tINSERT INTO OurStuff(StuffName1, Stuffname2)\n\tVALUES('Really Big Thing', 'Amazingly Big Thing');\n<\/pre>\n
     We can specify the national character type just like we do any other type. Plus, we can  insert and manipulate data just like any other type. But now let’s look at the column metadata:<\/p>\n
    \tSELECT\n\t  c.name AS ColumnName,\n\t  t.name AS TypeName\n\tFROM sys.columns c INNER JOIN sys.types t \n\t  ON c.user_type_id = t.user_type_id\n\tWHERE object_id = OBJECT_ID('OurStuff')\n\tORDER BY c.column_id;\n\t<\/pre>\n
     \tAll we’re doing is pulling the column names and their data types, after we created our table.  The following results tell all:<\/p>\n\n\n\n\n\n\n
    \n
    ColumnName<\/b><\/p>\n<\/td>\n
    		\n
    TypeName<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    StuffID<\/p>\n<\/td>\n
    		\n
    int<\/p>\n<\/td>\n<\/tr>\n
    \n
    StuffName1<\/p>\n<\/td>\n
    		\n
    nvarchar<\/p>\n<\/td>\n<\/tr>\n
    \n
    Stuffname2<\/p>\n<\/td>\n
    		\n
    nvarchar<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tAs you can see, both character columns have been created with the  \tNVARCHAR<\/code>  data type. The NATIONAL<\/code> \tCHARACTER<\/code> \tVARYING<\/code>  alias has disappeared altogether.<\/p>\n<\/p><\/div>\n
    “When is it best to use the \t \tCHAR<\/code> date type rather than  \tVARCHAR<\/code>?”<\/h3>\n
    \n
    The key to knowing when to use one over the other is to first understand the differences  between them. The CHAR<\/code>  and NCHAR<\/code>  data types store data at a fixed length. If an inserted value is less then that length, it is padded with trailing  spaces to ensure that every value is of equal length. The VARCHAR<\/code> and  \tNVARCHAR<\/code>  data types are variable length, which means the size depends on the inserted value, plus an additional couple of bytes  for overhead.<\/p>\n
    The following example helps illustrate the differences between the data types:<\/p>\n
    \tDECLARE\n\t  @stuff1 CHAR(7) = 'thing',\n\t  @stuff2 NCHAR(7) = 'thing',\n\t  @stuff3 VARCHAR(10) = 'thing',\n\t  @stuff4 NVARCHAR(10) = 'thing';\n\tSELECT \n\t  DATALENGTH(@stuff1) AS char1,\n\t  DATALENGTH(@stuff2) AS nchar2,\n\t  DATALENGTH(@stuff3) AS varchar3,\n\t  DATALENGTH(@stuff4) AS nvarchar4;\n\t\n<\/pre>\n
    All we’re doing here is using the DATALENGTH<\/code> function to determine the size  of each variable, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    char1<\/b><\/p>\n<\/td>\n
    		\n
    nchar2<\/b><\/p>\n<\/td>\n
    		\n
    varchar3<\/b><\/p>\n<\/td>\n
    		\n
    nvarchar4<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    7<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n
    		\n
    10<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tAlthough the value is only five characters long, the length of the  \tCHAR<\/code>  value is seven bytes, because that was how the variable was defined. The  \tNCHAR<\/code> value works the same way,  except that it’s doubled to account for the Unicode characters, so the DATALENGTH<\/code> function returns  \t14<\/code>.  On the other hand, the VARCHAR<\/code> value requires only the bytes needed for the value (not including the overhead  bytes). The NVARCHAR<\/code>  value merely doubles that.<\/p>\n
    The choice, then, on whether to use CHAR<\/code> or  \tVARCHAR<\/code>  depends on how consistent the size of the values are and how small the values are. Microsoft recommends that you use \tCHAR<\/code>  only when value lengths are consistent, and use VARCHAR<\/code> when the lengths vary a  lot. Microsoft is noncommittal about the gray areas in between.  \t \t<\/p>\n
    Small columns (fewer than five or six characters) often work well as  \tCHAR<\/code>  columns when you take into account the overhead added to a VARCHAR<\/code> column. Even so, some  developers would suggest that you use CHAR<\/code> only if all values are exactly the  same length. If values are shorter than the designated length, they get padded with spaces, which can be annoying to deal  with and could potentially waste a lot of space if your values are mostly one-to-two characters but you’ve created a \tCHAR(5)<\/code>  column to accommodate a relatively few five-digit values.  \t \t<\/p>\n
    That said, if all your values are a comparable length, you don’t incur the storage penalty  for overhead that comes with VARCHAR<\/code>, although storage has grown so  cheap, some of the concerns about the extra space have been mitigated. You might see some performance gains if you go  with CHAR<\/code>,  but the significance of those gains can vary and are perhaps not worth the trade-off of having to deal with the trailing  spaces. Even so, CHAR<\/code> is a useful option when your  data is consistent and small.<\/p>\n<\/p><\/div>\n
    “I’m  building a query that joins a  \tFLOAT<\/code>  column to a  \tREAL<\/code>  column, but when I run the statement, it returns no results, even though I can see the matching values in the two  columns. Why is this happening?”<\/h3>\n
    \n
    Welcome to the shifty world of approximate-number data types, in which floating-point data is  merely an approximation and the numbers you see might not be the real numbers at all. Let me demonstrate. The following  T-SQL declares a FLOAT<\/code>  variable, sets its value, and retrieves the value as its represented in SQL Server Management Studio (SSMS) as well as  it actually exists in the database:<\/p>\n
    \tDECLARE @StuffAvg FLOAT = 33.33;\n\tSELECT @StuffAvg AS StuffAvg, \n\t  STR(@StuffAvg, 30, 15) AS RealStuff;\n\t<\/pre>\n
    The STR<\/code> function converts the numerical data to  character data and, in the process, gives us a glimpse of what’s really out there:<\/p>\n\n\n\n\n
    \n
    StuffAvg<\/b><\/p>\n<\/td>\n
    		\n
    RealStuff<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    33.33<\/p>\n<\/td>\n
    		\n
    33.329999999999998<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tSo here’s what’s happened. We assigned the value  \t33.33<\/code>  to the variable, but the database engine actually stored it as 33.329999999999998<\/code>, thus the approximation  component. When SSMS retrieves that value, however, it sees fit to once again make it look nice and neat (a fact that  displeases more than a few in the SQL Server user community).<\/p>\n
    On the surface, all this might not seem too big a deal-or perhaps it does. In any case, let’s  take a closer look. The following T-SQL creates a table that contains a FLOAT<\/code> column and a  \tREAL<\/code>  column and then populates those columns with a few choice values:<\/p>\n
    \tIF OBJECT_ID('OurStuff', 'U') IS NOT NULL\n\tDROP TABLE OurStuff;\n\tGO\n\t\n\tCREATE TABLE OurStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffAvg1 FLOAT NOT NULL,\n\t  StuffAvg2 REAL NOT NULL\n\t);\n\tGO\n\t\n\tINSERT INTO OurStuff(StuffAvg1, StuffAvg2)\n\tVALUES(33.33, 33.33), (66.66, 66.66), (99.99, 99.99);\n<\/pre>\n
     \tNow let’s do what we did with our variable and retrieve the SSMS-enhanced values and the  actual values:<\/p>\n
    \tSELECT\n\t  StuffID,\n\t  StuffAvg1, \n\t  STR(StuffAvg1, 30, 15) AS RealStuff1,\n\t  StuffAvg2, \n\t  STR(StuffAvg2, 30, 15) AS RealStuff2\n\tFROM OurStuff;\n\t<\/pre>\n
     \tOnce again, our trusty STR<\/code> function comes to the rescue  and shows us what’s what:<\/p>\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffAvg1<\/b><\/p>\n<\/td>\n
    		\n
    RealStuff1<\/b><\/p>\n<\/td>\n
    		\n
    StuffAvg2<\/b><\/p>\n<\/td>\n
    		\n
    RealStuff2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    33.33<\/p>\n<\/td>\n
    		\n
    33.329999999999998<\/p>\n<\/td>\n
    		\n
    33.33<\/p>\n<\/td>\n
    		\n
    33.330001831054688<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    66.66<\/p>\n<\/td>\n
    		\n
    66.659999999999997<\/p>\n<\/td>\n
    		\n
    66.66<\/p>\n<\/td>\n
    		\n
    66.660003662109375<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    99.99<\/p>\n<\/td>\n
    		\n
    99.989999999999995<\/p>\n<\/td>\n
    		\n
    99.99<\/p>\n<\/td>\n
    		\n
    99.989997863769531<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tAs you can see, not only do the FLOAT<\/code> and  \tREAL<\/code>  data types like to approximate their values, they also like to do it differently. Yet according to the SSMS perspective,  each pair of values is exactly the same. What makes matters worse is that our references to the values also get somewhat  fuzzy. Check it out:<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffAvg1 = 33.33;\n\t<\/pre>\n
    When we specify 33.33<\/code> in our  \tWHERE<\/code>  clause, the applicable row is returned:<\/p>\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffAvg1<\/b><\/p>\n<\/td>\n
    		\n
    StuffAvg2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    33.33<\/p>\n<\/td>\n
    		\n
    33.33<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNow let’s specify 33.329999999999998<\/code> in our  \tWHERE<\/code>  clause:<\/p>\n
    \tSELECT * FROM OurStuff\n\tWHERE StuffAvg1 = 33.329999999999998; \n\t<\/pre>\n
     \tThe statement returns the exact results as the previous one. And it works the same way for  our REAL<\/code> column. Either value will  return the same row.<\/p>\n
    By all appearances, then, it would seem that we could specify either value or compare values  and everything would work out fine. Guess again. This time around, we’ll compare the values as they appear in SSMS,  without the benefit of STR<\/code>:<\/p>\n
    \tDECLARE @StuffAvg1 FLOAT = 33.33;\n\tDECLARE @StuffAvg2 REAL = 33.33;\n\tPRINT \n\t  CASE \n\t    WHEN @StuffAvg1 = @StuffAvg2 \n\t    THEN 'equal' \n\t    ELSE 'not equal'\n\t  END;\n\t<\/pre>\n
    It turns out that the statement returns a value of  \tnot<\/code> \tequal<\/code>,  suggesting that, despite appearances, not all instances of FLOAT<\/code> and  \tREAL<\/code>  are anything close to equal. So what happens if we try to join a FLOAT<\/code> column to a  \tREAL<\/code>  column? To find out, let’s create a couple tables and add some data:<\/p>\n
    \tIF OBJECT_ID('OurStuff', 'U') IS NOT NULL\n\tDROP TABLE OurStuff;\n\tGO\n\t\n\tIF OBJECT_ID('OurOtherStuff', 'U') IS NOT NULL\n\tDROP TABLE OurOtherStuff;\n\tGO\n\t\n\tCREATE TABLE OurStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffAvg1 FLOAT NOT NULL\n\t);\n\tGO\n\t\n\tCREATE TABLE OurOtherStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffAvg2 REAL NOT NULL\n\t);\n\tGO\n\t\n\tINSERT INTO OurStuff(StuffAvg1)\n\tVALUES(33.33), (66.66), (99.99);\n\t\n\tINSERT INTO OurOtherStuff(StuffAvg2)\n\tVALUES(33.33), (66.66), (99.99);\n\t<\/pre>\n
    Now let’s retrieve the data in the OurStuff<\/code> table:<\/p>\n
    SELECT * FROM OurStuff;\n\t<\/pre>\n
    Not surprisingly, our results look like this:<\/p>\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffAvg1<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    33.33<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    66.66<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    99.99<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNext, let’s retrieve the data in the OurOtherStuff<\/code> table:<\/p>\n
    \tSELECT * FROM OurOtherStuff;<\/pre>\n
     \tOnce again, no surprises:<\/p>\n\n\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffAvg2<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    1<\/p>\n<\/td>\n
    		\n
    33.33<\/p>\n<\/td>\n<\/tr>\n
    \n
    2<\/p>\n<\/td>\n
    		\n
    66.66<\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    99.99<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tSo with everything in place, let’s join these critters:<\/p>\n
    \t\tSELECT a.StuffID, a.StuffAvg1, b.StuffAvg2\n\tFROM OurStuff a INNER JOIN OurOtherStuff b\n\t  ON a.StuffAvg1 = b.StuffAvg2;\n\t<\/pre>\n
    As it turns out, the statement returns no rows, which is why Microsoft generally recommends  that you avoid  using FLOAT<\/code> or REAL<\/code> in equality constructions in places such as joins or WHERE<\/code> conditions. To make this work, you  would need to do some rounding or converting or creating calculated columns or something as equally clever. Just don’t  rely on FLOAT<\/code>  and REAL<\/code>  for any sort of precision. That’s not what they were designed to do. They offer a great deal of flexibility, but at a  price.<\/p>\n<\/p><\/div>\n
    “I have a table that  includes a  \tVARCHAR<\/code> column and a stored procedure that accesses data from the  table, based on values in that column. The procedure includes an  \tNVARCHAR<\/code>  parameter used to qualify the  \tWHERE<\/code>  clause, which references the  \tVARCHAR<\/code>  column. Does it matter that the data types are different between the parameter and the column?”<\/h3>\n
    \n
    Yes, it matters, but let’s make sure we’re talking about the same thing. The following T-SQL  script creates a table, inserts data into that table, creates a procedure that retrieves data from that table, and then  executes the procedure, passing in the parameter value Really<\/code>  \tBig<\/code> \tThing<\/code>:<\/p>\n
    \tIF OBJECT_ID('OurStuff', 'U') IS NOT NULL\n\tDROP TABLE OurStuff;\n\tGO\n\t\n\tCREATE TABLE OurStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffName VARCHAR(25) NOT NULL,\n\t  StuffDate DATETIME NOT NULL DEFAULT GETDATE()\n\t);\n\tGO\n\t\n\tINSERT INTO OurStuff(StuffName)\n\tVALUES('Thing'), ('Big Thing'), ('Really Big Thing'), ('Amazingly Big Thing');\n\t\n\tIF OBJECT_ID('FindStuff', 'P') IS NOT NULL\n\tDROP PROCEDURE FindStuff;\n\tGO\n\t\n\tCREATE PROCEDURE FindStuff \n\t@stuff NVARCHAR(25)\n\tAS\n\tSELECT StuffID, StuffDate FROM OurStuff\n\tWHERE StuffName = @stuff;\n\tGO\n\t\n\tEXEC FindStuff 'Really Big Thing';\n\t<\/pre>\n
    When you run the procedure, it returns the StuffID<\/code> and  \tStuffDate<\/code>  values, as the following results show:<\/p>\n\n\n\n\n
    \n
    StuffID<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    3<\/p>\n<\/td>\n
    		\n
    2014-07-06 17:16:29.457<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tou might have noticed that the table’s StuffName<\/code> column is configured as  \tVARCHAR<\/code>  and the procedure’s @stuff<\/code>  parameter is configured as NVARCHAR<\/code>. The database engine implicitly converts the value without no apparent problem.  In the background, however, we might find a different story. Although the database engine can perform an implicit  conversion without a hiccup, the operation can result in a performance penalty, in part because it is preventing index  seeks. As a result, when you start adding millions-or billions-of rows to the equation, a minor data type mismatch can  have a significant impact. For more information about all this, check out my Simple Talk article “Data  Conversion in SQL Server<\/a>.”<\/p>\n<\/p><\/div>\n
    “My query needs to calculate  past and future dates based on an existing date as well as calculate the time difference between two dates. I’ve tried  using arithmetic operators to perform the calculations, but the results are never right. How do I go about working with  dates?”<\/h3>\n
    \n
    The best two tools for achieving what you’re trying to do are the  \tDATEADD<\/code>  and DATEDIFF<\/code>  functions. DATEADD<\/code>  subtracts or adds a specified interval of time to a date value, which can be any date or time data type.  \tDATEDIFF<\/code>  returns the interval of time between two specified date values. Again, the values can be any of the date or time date  types.<\/p>\n
    Let’s first look at how the DATEADD<\/code> function works. The following T-SQL  declares a DATETIME<\/code>  variable, uses the GETDATE<\/code>  function to assign the current date and time values to the function, and then retrieves the dates 10 years forward and  10 years back:<\/p>\n
    \tDECLARE @StuffDate DATETIME = GETDATE();\n\tSELECT \n\t  @StuffDate AS StuffDate,\n\t  DATEADD(yy, 10, @StuffDate) AS Plus10,\n\t  DATEADD(yy, -10, @StuffDate) AS Less10;\n\t<\/pre>\n
    The DATEADD<\/code> function takes three arguments. The  first argument is the date part, such as day, week, month, or year. In this case, we’ve specified  \tyy<\/code>  for year. The second argument is the number of years forward or backward we want to go. Finally, we specify our base  date. The SELECT<\/code>  statement returns the following results:<\/p>\n\n\n\n\n
    \n
    StuffDate<\/b><\/p>\n<\/td>\n
    		\n
    Plus10<\/b><\/p>\n<\/td>\n
    		\n
    Less10<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2014-07-07 19:03:50.183<\/p>\n<\/td>\n
    		\n
    2024-07-07 19:03:50.183<\/p>\n<\/td>\n
    		\n
    2004-07-07 19:03:50.183<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tIf we want to extract a specific date or time element, we can use the  \tDATEPART<\/code>  function, as in the following example:<\/p>\n
    \tDECLARE @StuffDate DATETIME = GETDATE();\n\tSELECT \n\t  DATEPART(yy, @StuffDate) AS StuffDate,\n\t  DATEPART(yy, DATEADD(yy, 10, @StuffDate)) AS Plus10,\n\t  DATEPART(yy, DATEADD(yy, -10, @StuffDate)) AS Less10;\n\t<\/pre>\n
    All we’ve done is specify that only the year should be extracted from the date, as shown in  the following results:<\/p>\n\n\n\n\n
    \n
    StuffDate<\/b><\/p>\n<\/td>\n
    		\n
    Plus10<\/b><\/p>\n<\/td>\n
    		\n
    Less10<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2014<\/p>\n<\/td>\n
    		\n
    2024<\/p>\n<\/td>\n
    		\n
    2004<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tNow let’s use the DATEDIFF<\/code> function to calculate the  number of months between two dates:<\/p>\n
    \tDECLARE @StuffDate1 DATETIME = GETDATE();\n\tDECLARE @StuffDate2 DATETIME = DATEADD(yy, 10, @StuffDate1);\n\tSELECT \n\t  DATEPART(yy, @StuffDate1) AS StuffDate1,\n\t  DATEPART(yy, @StuffDate2) AS StuffDate2,\n\t  DATEDIFF(mm, @StuffDate1, @StuffDate2) AS MonthDiff;\n\t<\/pre>\n
    The DATEDIFF<\/code> function also requires three  arguments. As with DATEADD<\/code>, the first argument is the  date part. This time around we’re using mm<\/code> for month. The next two arguments are  the dates we want to compare, in this case, our two DATETIME<\/code> variables. As the following  results show, there are 120 months between the two dates:<\/p>\n\n\n\n\n
    \n
    StuffDate1<\/b><\/p>\n<\/td>\n
    		\n
    StuffDate2<\/b><\/p>\n<\/td>\n
    		\n
    MonthDiff<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    2014<\/p>\n<\/td>\n
    		\n
    2024<\/p>\n<\/td>\n
    		\n
    120<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     \tThe functions available in SQL Server for working with date and time values are definitely  ones worth getting to know. Date and time values are treated differently from other types of data and require special  consideration. You can find more about these functions in the MSDN topic “Date  and Time Data Types and Functions<\/a>.”<\/p>\n<\/p><\/div>\n
    “I’m told never to use \t \tSQL_VARIANT<\/code> data type inappropriately. What is an appropriate use for  this data type?”<\/h3>\n
    \n
    SQL_VARIANT is included in SQL Server mainly for system use. Although it is used within system procedures  \tand views, It has several important  restrictions. It can only be used with great caution, and there is usually  \ta better way. It cannot be used in a relational table.  However, there do exist some appropriate specialised  \tuses.<\/p>\n
    An appropriate use is  one in which you have no other reasonable option or a reasonable option requires a workaround that causes more trouble  than using SQL_VARIANT<\/code>.  In fact, under the right circumstances, the data type could prove a valuable tool. It supports most T-SQL base types all  within a single structure, such as a column or parameter. For example, if you define a column with  \tSQL_VARIANT<\/code>, you can insert  character data and numerical data and data\/time data. Here’s proof. The following T-SQL script creates a table that  includes a SQL_VARIANT<\/code>  column and then inserts into it an assortment of values:<\/p>\n
    \tIF OBJECT_ID('OurStuff', 'U') IS NOT NULL\n\tDROP TABLE OurStuff;\n\tGO\n\t\n\tCREATE TABLE OurStuff\n\t(\n\t  StuffID INT NOT NULL IDENTITY PRIMARY KEY,\n\t  StuffName SQL_VARIANT NOT NULL\n\t);\n\tGO\n\t\n\tINSERT INTO OurStuff(StuffName) VALUES('Same Old Thing');\n\tINSERT INTO OurStuff(StuffName) VALUES(479);\n\tINSERT INTO OurStuff(StuffName) VALUES(GETDATE());\n\tINSERT INTO OurStuff(StuffName) VALUES(1e9);\n\tINSERT INTO OurStuff(StuffName) VALUES(453.66903438);\n\t<\/pre>\n
    Sure, we can insert the same data into a VARCHAR<\/code> column, but the values would all  come out VARCHAR<\/code>  data. The SQL_VARIANT<\/code>  data type, on the other hand, preserves the original type, which we can verify by using the  \tSQL_VARIANT_PROPERTY<\/code>  function:<\/p>\n
    \tSELECT\n\t  StuffName,\n\t  SQL_VARIANT_PROPERTY(StuffName, 'basetype') AS BaseType,\n\t  SQL_VARIANT_PROPERTY(StuffName, 'maxlength') AS BaseLength,\n\t  SQL_VARIANT_PROPERTY(StuffName, 'precision') AS BasePrecision\n\tFROM OurStuff;\n\t<\/pre>\n
     \tAnd here are the results:<\/p>\n\n\n\n\n\n\n\n\n
    \n
    StuffName<\/b><\/p>\n<\/td>\n
    		\n
    BaseType<\/b><\/p>\n<\/td>\n
    		\n
    BaseLength<\/b><\/p>\n<\/td>\n
    		\n
    BasePrecision<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Same Old Thing<\/p>\n<\/td>\n
    		\n
    varchar<\/p>\n<\/td>\n
    		\n
    8000<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    479<\/p>\n<\/td>\n
    		\n
    int<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n
    		\n
    10<\/p>\n<\/td>\n<\/tr>\n
    \n
    2014-07-08 09:06:34.440<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n
    		\n
    8<\/p>\n<\/td>\n
    		\n
    23<\/p>\n<\/td>\n<\/tr>\n
    \n
    1000000000<\/p>\n<\/td>\n
    		\n
    float<\/p>\n<\/td>\n
    		\n
    8<\/p>\n<\/td>\n
    		\n
    53<\/p>\n<\/td>\n<\/tr>\n
    \n
    453.66903438<\/p>\n<\/td>\n
    		\n
    numeric<\/p>\n<\/td>\n
    		\n
    9<\/p>\n<\/td>\n
    		\n
    11<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Notice that each type has been preserved. SQL_VARIANT<\/code> was even able to pick up on the  scientific notation. So you can see why, in certain circumstances, such a data type could be useful. For example, you  might consider SQL_VARIANT<\/code>  when defining a parameter in a stored procedure or function. This approach helps to maintain some of the precision you might otherwise lose when passing  in numeric or date\/time values through a VARCHAR<\/code> parameter. Another example of how \tSQL_VARIANT<\/code> might be useful is if  you’re supporting an application that allows users to define their own tables and fields.  \tSQL_VARIANT<\/code> can help you avoid  having to set up extra structures to support each data type.<\/p>\n
    You’ll have to decide on a case-by-case basis whether to use  \tSQL_VARIANT<\/code>,  but know that the data type comes with a number of limitations. For instance, SQL Server converts  \tSQL_VARIANT<\/code>  values to NVARCHAR(4000)<\/code>  to support older versions of the OLE DB and ODBC providers. Same thing with many other external tools that don’t  recognize SQL_VARIANT<\/code>.  In addition, using linked servers in conjunction with SQL_VARIANT<\/code> can lead to potential memory  leaks. Plus, you can’t use the data type in statement elements such as LIKE<\/code> expressions or computed columns. So  before you implement the SQL_VARIANT<\/code> data type, do your homework. A good place to start with the TechNet topic “Using  sql_variant Data<\/a>.<\/p>\n<\/p><\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
     Although SQL  Data Types seem to cause a lot of grief for database developers and can be tricky in their use, we seem to be expected to know all about them, and so it is embarrassing to ask questions about them in forums. Rob Sheldon continues in his mission to answer all those questions that we hesitate to ask.…<\/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":[4168,4150,4151,4252,5771],"coauthors":[],"class_list":["post-1837","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-sql","tag-sql-server","tag-t-sql-programming","tag-too-shy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1837","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=1837"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1837\/revisions"}],"predecessor-version":[{"id":41052,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1837\/revisions\/41052"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1837"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1837"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1837"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1837"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}