:::: MENU ::::

Posts Categorized / Null Values

  • Jan 07 / 2010
  • 0
dbDigger, Null Values, T-SQL Interview Questions

Using order by with nulls in T-SQL

You have a table with EmpName and DateOfLeaving columns.
The EmpName column is mandatory, but DateOfleaving will have null value for those employees who have not left the company.
The question is to have list off all employees with the employees with DateOfLeaving coming last and all others sorted ascending order of date of leaving, Name sorted in ascending order for each group.
Consider the Example:

Table: Employees

EmpName DateOfLeaving

Abc 10 Oct 1999

Bcd 11 Nov 1998

Ccd null

Dcd 10 Aug 2000

Eed null

The solution should be:

EmpName DateOfLeaving

Bcd 11 Nov 1998

Abc 10 Oct 1999

Dcd 10 Aug 2000

Ccd null

Eed null

Which query will produce this output?

Answer:

  • Select EmpName, DateOfLeaving from Employees order by isnull(DateOfLeaving,’10/10/9999’),EmpName asc
  • Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc

Explanation:
In SQL Server T-SQL, by default, a query takes null as first when the order is ascending (the default). So we just give some value of greater date in order by so that it will come at last.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Apr 10 / 2009
  • 0
dbDigger, Null Values, T-SQL Tips and Tricks, User Defined Functions UDF

Use ISNULL to replace NULL value with alternate

It may be required to change NULL value of a column with a valid alternate value. This scenario is mostly there for report generation purpose. T-SQL provide a very handy function ISNULL() to replace NULL value with alternate value. Following is syntax for using ISNULL()
ISNULL(ColumntoCheckForNULL, AlternateValue)
ISNULL accepts two parameters. First parameter will always be the column for which alternate is required in case of its NULL value. Second parameter is the alternate value that is required to replace the NULL value. Second value may be any other column or other value. Alternate value may be implicitly converted to data type of first parameter. For example you may provide datetime as alternate value for character family data type but you can not provide character family data type as alternate of date time value.
For example if we are required to display City as alternate of NULL value of AddressLine2

 USE AdventureWorks  
 GO  
 SELECT AddressLine1, ISNULL(AddressLine2,city), PostalCode  
 FROM Person.address  

Similarly to display a string of characters as alternate of NULL AddressLine2

 USE AdventureWorks  
 GO  
 SELECT AddressLine1, ISNULL(AddressLine2,'No Address Line 2'), PostalCode  
 FROM Person.address  

This user defined function would be very helpful while working with string concatenations. If one of the strings being concatenated is NULL then end result of concatenation would be NULL. To avoid this situation use ISNULL where NULL value is expected.Just consider following two examples for use and effect of ISNULL in string concatenation.

 
-- String concatenation with NULL value  
 SELECT 'Value1 '+'Value2 '+NULL AS ConcatenatedString  
 GO  
 -- String concatenation by using ISNULL  
 SELECT 'Value1 '+'Value2 '+isnull(NULL, 'NULL-Replaced') AS ConcatenatedString  
 GO  
  • Apr 09 / 2009
  • 0
Case Statement, dbDigger, Null Values, T-SQL Interview Questions

None of the result expressions in a CASE specification can be NULL.

The error message
Msg 8133, Level 16, State 1, Line 2
None of the result expressions in a CASE specification can be NULL.
is a run time error message and may be generated at all versions of SQL Srever. The said error message is generated when one result expression in a CASE expression is NULL. To remove the error message, analyze your case statement and modify it to make sure that no expression is NULL.

  • Nov 29 / 2008
  • 0
dbDigger, Null Values, T-SQL Interview Questions

Comparing nulls

Question:
What is the Output of the following

IF(NULL IN (34, 35, NULL))
PRINT 'TRUE'
IF(34 NOT IN (35, 36, NULL))
PRINT 'FALSE'

Answer:
Command(s) completed successfully.

Explanation:
Since NULL is an unknown entity, the result of comparing two unknowns is always false. This means that the PRINT statements are never executed as the result of the IF statement is always false.
Ref: IF..ELSE – http://msdn.microsoft.com/en-us/library/ms182717.aspx

Note: The Question is taken from SQLServercentral.com.

  • Oct 15 / 2008
  • 0
dbDigger, Null Values, T-SQL Tips and Tricks

Using NULL in UNION clause of T-SQL

Question: What is the output of the following query?

 
SELECT 1  
 UNION  
 SELECT NULL  
 UNION  
 SELECT '1'  

Answer: NULL, 1

Explanation: UNION only takes distinct values and ‘1’ is implicitly converted to 1. So the NULL comes first and then 1 comes.

Ref: Union –

Note: The Question of day is taken from SQLServercentral.com. I want to compile selected QOD.

  • May 21 / 2008
  • 0
dbDigger, Null Values

using NULL in SQL Server T-SQL

Null is a special value or mark, that is to indicate absence of any data value. It is not a data value but an indicator that we are missing data.
Following are interesting considerations about nulls

  • Use NULL to indicate unknown/missing information only. Do not use NULL in place of zeroes, zero-length strings or other “known” blank values.
  • Update your NULL with proper information as soon as possible.
  • According to ANSI-92 SQL Standard, comparisons with NULL always result in Unknown.

e.g.

SET ANSI_NULLS ON
GO

SELECT * FROM Northwind..Employees
WHERE Region IS NULL
GO

This query returns four employee records. But following query will return no record, the reason for this is that Region = NULL’ evaluates to Unknown every time. Since it never evaluates to True for any rows, it returns no records.

SET ANSI_NULLS ON
GO

SELECT * FROM Northwind.Employees
WHERE Region = NULL
GO

It has been widely miss stated that “the result of a comparison is false if one of the operands is NULL.” In fact, the ANSI-92 SQL Standard states that if one of the operands is NULL, the result of the comparison is “UNKNOWN”.
Presumably to make the transition smoother for 3GL and 4GL programmers from other languages moving over to SQL, Microsoft implemented the SET ANSI_NULLS OFF option, which allows you to perform = and <> comparisons with NULL values. To demonstrate this in action, we can perform the following query:

SET ANSI_NULLS OFF
GO

SELECT * FROM [Northwind].[dbo].[Employees]
WHERE [Region] = NULL
GO

This allows you to perform basic equality comparisons to NULL; however, SQL written using this option may not be portable to other platforms, and might confuse others who use ANSI Standard NULL-handling.

Use SET ANSI_NULLS ON, and always use ANSI Standard SQL Syntax for NULLs. Straying from the standard can cause problems including portability issues, incompatibility with existing code and databases and returning incorrect results.

If you perform scalar math operations and string concatenation functions with NULL, the result is always NULL. For instance, this query returns NULL.

Consult us to explore the Databases. Contact us