Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Implement data types and NULLs

  1. Home
  3. Exam 70-761: Querying Data with Transact-SQL
  5. Implement data types and NULLs

Exam 70-761: Querying Data with Transact-SQL retired on 31 January, 2021.

Data types (Transact-SQL)

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are based on the system-supplied data types. For more information about alias data types, see CREATE TYPE (Transact-SQL). User-defined types obtain their characteristics from the methods and operators of a class that you create by using one of the programming languages support by the .NET Framework.

When two expressions that have different data types, collations, precision, scale, or length are combined by an operator, the characteristics of result are determined by the following –

  • The data type of the result is determined by applying the rules of data type precedence to the data types of the input expressions. For more information, see Data Type Precedence (Transact-SQL).
  • The collation of the result is determined by the rules of collation precedence when the result data type is char, varchar, text, nchar, nvarchar, or ntext. For more information, see Collation Precedence (Transact-SQL).
  • The precision, scale, and length of the result depend on the precision, scale, and length of the input expressions. For more information, see Precision, Scale, and Length (Transact-SQL).
  • SQL Server provides data type synonyms for ISO compatibility. For more information, see Data Type Synonyms (Transact-SQL).

Data type categories

Data types in SQL Server are organized into the following categories –

  • Exact numerics
  • Unicode character strings
  • Approximate numerics
  • Binary strings
  • Date and time
  • Other data types
  • Character strings

In SQL Server, based on their storage characteristics, some data types are designated as belonging to the following groups –

  • Large value data types: varchar(max), and nvarchar(max)
  • Large object data types: text, ntext, image, varbinary(max), and xml

CAST and CONVERT (Transact-SQL)

Example: Change the type of input data

Cast

SELECT 9.5 AS Original,
CAST(9.5 AS INT) AS [int],
CAST(9.5 AS DECIMAL(6, 4)) AS [decimal];

Convert

SELECT 9.5 AS Original,
CONVERT(INT, 9.5) AS [int],
CONVERT(DECIMAL(6, 4), 9.5) AS [decimal];

The set of results is the following one.

OriginalINTDecimal
9.599.5000

Syntax

— CAST Syntax:
CAST ( expression AS data_type [ ( length ) ] )

— CONVERT Syntax:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Join SQL Server tables where columns include NULL values

While creating database tables we are faced with the decision of whether to allow NULL values or to not allow NULL values in the columns.  By default SQL Server sets the column value to allow NULL values when creating new tables, unless other options are set.  But dealing with NULL values especially when joining tables can become a challenge.

In most cases the columns in the tables probably allow NULL values unless we consciously changed them while creating a table or changed the default setting for the database by using the SET ANSI_NULL_DEFAULT OFF and SET ANSI_NULL_DFLT_ON OFF option.  These settings change the behavior of how a table is created if we do not specify NULL or NOT NULL when creating a table.

Illustration – When these two options are OFF the default nullable value is set to no, meaning no null values are acceptable.

The screen shot below shows that the column does not allow NULLs

Illustration – When these two options are ON the default nullable value is set to yes, meaning null values are acceptable

The screen shot below shows that the this column does allow NULLs.

ISNULL (Transact-SQL)

Replaces NULL with the specified replacement value.

Syntax

ISNULL ( check_expression , replacement_value )

Where,

check_expression
Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expression.

Return Types
Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

Note, the value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

COALESCE (Transact-SQL)

Coalesece (Transact-SQL) evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn’t null.

Syntax

COALESCE ( expression [ ,…n ] )

Return Types
Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

If all arguments are NULL, COALESCE returns NULL. At least one of the null values must be a typed NULL.

Comparing COALESCE and CASE
The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,…n) is rewritten by the query optimizer as the following CASE expression:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
…
ELSE expressionN

Menu