What are the effects of concatenation on MS SQL Server
Data used
SELECT sales_date,
sales_qty,
sales_price,
emp_short
FROM sales;
go
sales_qty,
sales_price,
emp_short
FROM sales;
go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-02-06 13:01:01.000 6.00 11.75 000190 2006-03-06 14:01:01.000 5.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-04-06 15:01:01.000 4.00 11.75 000030 2006-05-06 16:01:01.000 3.00 11.75 000010 2004-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000010 2006-06-06 17:01:01.000 2.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000310 2006-02-06 13:01:01.000 6.00 11.75 000110 2005-03-06 14:01:01.000 5.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000110 2006-10-06 10:01:01.000 3.00 11.75 000110 2007-06-06 17:01:01.000 2.00 11.75 000010 2007-04-06 00:00:00.000 4.00 11.75 000010 2007-05-01 00:00:00.000 5.00 11.75 000030 2007-07-06 00:00:00.000 1.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000030 2004-03-06 14:01:01.000 5.00 11.75 000110 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2006-06-06 17:01:01.000 2.00 11.75 000030 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-03-06 14:01:01.000 5.00 11.75 000310 2006-04-06 15:01:01.000 4.00 11.75 000010 2006-06-06 17:01:01.000 2.00 11.75 000110 2006-07-06 19:01:01.000 1.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-12-06 11:01:01.000 8.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000030 2006-07-06 19:01:01.000 1.00 11.75 000010 2007-11-06 10:01:01.000 9.00 11.75 000030 2006-07-06 00:00:00.000 1.00 11.75 000190 (39 row(s) affected)
Execution Plan for the plain query
Concatenation
SELECT sales_date,
sales_qty,
sales_price,
'by: ' + emp_short
FROM sales;
go
sales_qty,
sales_price,
'by: ' + emp_short
FROM sales;
go
sales_date sales_qty sales_price ----------------------- --------------------------------------- --------------------------------------- -------------- 2005-10-06 10:01:01.000 10.00 11.75 by: 000310 2006-10-06 10:01:01.000 10.00 11.75 by: 000310 2005-11-06 10:01:01.000 9.00 11.75 by: 000110 2006-10-06 10:01:01.000 10.00 11.75 by: 000310 2005-12-06 11:01:01.000 8.00 11.75 by: 000010 2006-02-06 13:01:01.000 6.00 11.75 by: 000190 2006-03-06 14:01:01.000 5.00 11.75 by: 000110 2006-10-06 10:01:01.000 10.00 11.75 by: 000190 2006-10-06 10:01:01.000 10.00 11.75 by: 000190 2006-04-06 15:01:01.000 4.00 11.75 by: 000030 2006-05-06 16:01:01.000 3.00 11.75 by: 000010 2004-10-06 10:01:01.000 10.00 11.75 by: 000310 2005-11-06 10:01:01.000 9.00 11.75 by: 000010 2006-06-06 17:01:01.000 2.00 11.75 by: 000190 2006-01-06 12:01:01.000 7.00 11.75 by: 000310 2006-02-06 13:01:01.000 6.00 11.75 by: 000110 2005-03-06 14:01:01.000 5.00 11.75 by: 000190 2006-10-06 10:01:01.000 10.00 11.75 by: 000110 2006-10-06 10:01:01.000 3.00 11.75 by: 000110 2007-06-06 17:01:01.000 2.00 11.75 by: 000010 2007-04-06 00:00:00.000 4.00 11.75 by: 000010 2007-05-01 00:00:00.000 5.00 11.75 by: 000030 2007-07-06 00:00:00.000 1.00 11.75 by: 000190 2006-10-06 10:01:01.000 10.00 11.75 by: 000030 2004-03-06 14:01:01.000 5.00 11.75 by: 000110 2005-04-06 15:01:01.000 4.00 11.75 by: 000010 2005-05-06 16:01:01.000 3.00 11.75 by: 000190 2006-06-06 17:01:01.000 2.00 11.75 by: 000030 2006-07-06 19:01:01.000 1.00 11.75 by: 000310 2006-03-06 14:01:01.000 5.00 11.75 by: 000310 2006-04-06 15:01:01.000 4.00 11.75 by: 000010 2006-06-06 17:01:01.000 2.00 11.75 by: 000110 2006-07-06 19:01:01.000 1.00 11.75 by: 000190 2005-10-06 10:01:01.000 10.00 11.75 by: 000030 2005-12-06 11:01:01.000 8.00 11.75 by: 000190 2006-01-06 12:01:01.000 7.00 11.75 by: 000030 2006-07-06 19:01:01.000 1.00 11.75 by: 000010 2007-11-06 10:01:01.000 9.00 11.75 by: 000030 2006-07-06 00:00:00.000 1.00 11.75 by: 000190 (39 row(s) affected)
Execution Plan for the query with concatenation
Conclusion
- The concatenation adds a new table operation: the COMPUTE SCALAR.
- If you can avoid the concatenation, avoid it.
- This was a very small table, so the overhead is minimal, but on much larger tables, it is a significant operation.
- Leave these operations to the report writer, instead. It's lot more efficient at doing this, it's already processing and formatting each row.