Improving performance of adding a column with a single value

Question

By experimentation and surprisingly, I have found out that LEFT JOINING a point-table is much faster on large tables then a simple assigning of a single value to a column. By a point-table I mean a table 1x1 (1 row and 1 column).

Approach 1. By a simple assigning value, I mean this (slower):

SELECT A.*, 'Value' as NewColumn,
FROM Table1 A

Approach 2. By left-joining a point-table, I mean this (faster):

WITH B AS (SELECT 'Value' as 'NewColumn')
SELECT * Table1 A
LEFT JOIN B
ON A.ID <> B.NewColumn

Now the core of my question. Can someone advise me how to get rid of the whole ON clause:

ON A.ID <> B.NewColumn?

Checking the joining condition seems unnecessary waste of time because the key of table A must not equal the key of table B. It would throw out the rows from results if t1.ID had the same value as 'Value'. Removing that condition or maybe changing <> to = sign, seems further space to facilitate the join's performance.

Update February 23, 2015
Bounty question addressed to performance experts. Which of the approaches mentioned in my question and answers is the fastest.
Approach 1 Simple assigning value,
Approach 2 Left joining a point-table,
Approach 3 Cross joining a point-table (thanks to answer of Gordon Linoff)
Approach 4 Any other approach which may be suggested during the bounty period.
As I have measured empirically time of query execution in seconds of 3 approaches - the second approach with LEFT JOIN is the fastest. Then CROSS JOIN method, and then at last simple assigning value. Surprising as it is. Performance expert with a Solomon's sword is needed to confirm it or deny it.

Answer 1

I'm surprised this is faster for a simple expression, but you seem to want a cross join:

WITH B AS (SELECT 'Value' as NewColumn)
SELECT *
FROM Table1 A CROSS JOIN
     B;

I use this construct to put "parameters" in queries (values that can easily be changed). However, I don't see why it would be faster. If the expression is more complicated (such as a subquery or very complicated calculation), then this method only evaluates it once. In the original query, it would normally be evaluated only once, but there might be cases where it is evaluated for each row.

Answer 2

You can also try with CROSS APPLY:

SELECT A.*, B.*,
FROM Table1 A
CROSS APPLY(SELECT 'Value' as 'NewColumn') B

Answer 3

Can you try to insert into a temp table instead of outputting to screen:

SELECT A.*, 'Value' as NewColumn
INTO #Table1Assign
FROM Table1 A

and

WITH B AS (SELECT 'Value' as 'NewColumn')
SELECT * Table1 A
INTO #Table1Join
LEFT JOIN B
ON A.ID <> B.NewColumn

That takes the actual transmission and rendering of the data to SSMS out of the equation, which could be caused by network slowdown or processing on the client.

When I run this with a 1M row table, I consistently get better performance with the simple assigning method, even if I switch to CROSS JOIN for the join method.

Answer 4

I doubts that second approach will be faster,with three select and left join. First of all you should test same query with various sample data repeatedly.

What is the real scenario like ?

Inner join will be definitely faster than left join .

How about this ?

Declare @t table(id int,c2 varchar(10))
INSERT INTO @T
select 1,'A' union all
select 2,'A' union all
select 3,'B' union all
select 4,'B' 

Declare @t1 table(nEWcOL varchar(10))
INSERT INTO @T1 Values('Value')

-- #Approach1
--SELECT * FROM @T outer apply
 --@t1

--Create index on both join column
 --#Approach2
SELECT * FROM @T A inner join
 @t1 b on a.c2<>b.nEWcOL

--#Approach3
Declare @value varchar(20)
Select @value= nEWcOL from @t1

select *,@value value from @t

Answer 5

Too much text for a comment, so added this as an answer although I'm actually more adding to the question (**)

Somehow I think this is going to be one of those 'it depends' situations. I think it depends a lot on the amount of rows involved and even more on what happens afterwards with the data. Is it simply returned, is it used in a GROUP BY or DISTINCT later on, do we further JOIN or calculate with it etc..

Anyway, I think this IS an interesting question in that I've had to find out the hard way that having a dozen of 'parameters' in a single-row temp-table was faster than having them assigned upfront to 12 variables. Many, many moons ago the code I was given looked like an absurd construction to me so I rewrote it to use @variables instead. This was in a +1000-lines stored procedure which needed some extra performance squeezed out of it. After quite a bit of refactoring it turned out to run remarkably slower than before the change?!?!!

I've never really understood why and at the time simply reverted to the old version again. My best guess is some weird kind of combination of parameter-sniffing vs (auto-created?) statistics on the temp-table in question; if anyone could bring some light to your question it probably will lead to an answer of mine too =)

(**: I realize SO is not a forum so I apologise upfront, simply wanted to chime in that the observed behaviour of the OP isn't entirely anecdotal)

Answer 6

Select * doesn't use indexes properly on SQL, you should always specify your columns.

Other than that I would use

DECLARE @Value VARCHAR(30) = 'Value'
SELECT t.Id, t.C2, @Value NewColumn
FROM Table1 t