Problem

Table contains two or three columns that should be output as a single column over two or three rows.  Such as [Telephone1], [Telephone2], … columns in a table.

Use Table Values constructor in CROSS APPLY clause

Use the table values constructor, VALUES(), to create a derived table using columns from the other table in the FROM clause.  Literal values can also be inserted. It is nicely flexible.   Best of all it is compact:

SELECT TOP 100 Source
	,TelNo
FROM dbo.ContactBase
CROSS APPLY (
	VALUES (
		[Telephone1]	,'Tel1'
		)
		,(
		Telephone2	,'Tel2'
		)
		,(
		Telephone3	,'Tel3'
		)
	) DT (TelNo, Source)
WHERE TElNo IS NOT NULL
ORDER BY createdon DESC

The UNION method requires three scans of the table to return the same results:

-- Equivalent query using UNION
SELECT TOP 100 Source
	,TelNo
FROM (
	SELECT TOP 100 'Tel1' AS Source
		,Telephone1 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone1 IS NOT NULL
	ORDER BY CreatedOn DESC

	UNION ALL

	SELECT TOP 100 'Tel2'
		,Telephone2 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone2 IS NOT NULL
	ORDER BY CreatedOn DESC

	UNION ALL

	SELECT TOP 100 'Tel3'
		,Telephone3 AS TelNo
		,CreatedOn
	FROM dbo.ContactBase
	WHERE Telephone3 IS NOT NULL
	ORDER BY CreatedOn DESC
	) DT
ORDER BY CreatedOn DESC

Efficiency

Does UNION ALL in the FROM clause avoid multiple table scans (suggested by this  Stack Exchange article)?    The following code still caused three scans:

SELECT TOP 100 Source
	,TelNo
FROM (
	SELECT TOP 100 Telephone1, 'Tel1', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc UNION ALL
	SELECT TOP 100 Telephone2, 'Tel2', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc UNION ALL
	SELECT TOP 100 Telephone3, 'Tel3', CreatedOn FROM dbo.ContactBase ORDER BY CreatedOn Desc 

	) DT (TelNo, Source, CreatedOn)
WHERE TelNo IS NOT NULL
ORDER BY CreatedOn DESC

It doesn’t seem that the TOP 100 clause is making a difference.  These are the two approaches simplified to return the entire table:

SELECT Source
	,TelNo
FROM dbo.ContactBase
CROSS APPLY (
	VALUES (
		[Telephone1]	,'Tel1'
		)
		,(
		Telephone2		,'Tel2'
		)
		,(
		Telephone3		,'Tel3'
		)
	) V(TelNo, Source)

print 'methond 2'

SELECT Source
	,TelNo
FROM (
	SELECT Telephone1, 'Tel1', CreatedOn FROM dbo.ContactBase UNION ALL
	SELECT Telephone2, 'Tel2', CreatedOn FROM dbo.ContactBase UNION ALL
	SELECT Telephone3, 'Tel3', CreatedOn FROM dbo.ContactBase   

	) DT (TelNo, Source, CreatedOn)

The query plans are still very distinct (even if the timings are not so very different):

Limitations

Table Value constructor introduced version 2008.

References

The article that suggested this is-there-a-better-option-than-union-all-for-multiple-selects-from-the-same-row

Similar approach for aggregating across multiple columns: use-values-clause-to-get-the-maximum-value-from-some-columns