I'm trying to optimize the following statement:

+'/00000' +CAST(@number+1 AS VARCHAR)

The statement produces a value like VI1/2011/000002 if the @number parameter is 1.

I would like to optimize this in terms of removing redundant cast statements and providing an efficient way to concatenate the strings and integers.

  • Sorry for late comment: where does (@number come from? Is this code in a scalar udf that is called row by row? – gbn Aug 7 '11 at 15:14

No shortcut at all, there is no elegant string concatention in SQL Server

You are mixing varchar and nvarchar though: datatype precedence means the entire expression will be nvarchar.

  • 1
    atleast a simple optimization? i was looking for something more from stackoverflowers ! – Deeptechtons Jan 25 '11 at 11:49
  • 1
    "here is no elegant string concatention in SQL Server": it really is this simple, sorry – gbn Jan 25 '11 at 12:26
  • 1
    i go with you Gbn certain things are better done on presentation layer – Deeptechtons Jan 27 '11 at 6:47
  • @Deeptechtons - Indeed. As the saying goes, "The only winning move is not to play". The best solution is not to do formatting in the database. – Thomas Mar 3 '11 at 3:40
  • This is no longer true in SQL Server 2012. – Nick Chammas Nov 16 '11 at 16:59

If you find yourself frequently performing this conversion as data gets selected out, try using a persisted calculated field instead. It gets added to the table, and SQL Server automatically calculates it whenever the data is inserted or updated. You pay the calculation penalty once - just once - and then it's less CPU whenever the data is selected back out. You can even put an index on it if you find yourself filtering queries with that field.

  • but isn't this operation too much for meager performance improvements – Deeptechtons Jan 27 '11 at 6:48
  • I'm not sure what you mean - can you reword your question? Are you saying adding a persisted computed field is too much work? – Brent Ozar Jan 28 '11 at 22:29

SQL Server 2012 introduces a new function that seems perfectly suited to your needs: CONCAT().

Here's some example code:

DECLARE @number INT             = 1;
DECLARE @now    DATETIME2(3)    = GETDATE();

        , DATEPART(month, @now)
        , '/'
        , DATEPART(year, @now)
        , '/00000'
        , (@number + 1)
    )   AS result

That said, I do recommend as others have to do presentation work in your presentation layer and not in the database. In those cases where it is just easier to throw it into your existing SQL query, however, I believe CONCAT() will serve you well.

That covers the programmability aspect. I don't know how well this would perform, though I would bet certainly no worse than your existing solution.

For more examples, take a look at:

  • seems promising to me, but yes since this is task for UI layer the next project that used same technique did it in the UI layer – Deeptechtons Nov 17 '11 at 4:46

Can you not get this as a parameter and pass it into the SQL proc from the application layer such as .net or java or php... ?

It would be much simpler not to do this at all on the SQL server!

  • Actually that's not a bad idea, hand off complex conversions to a CLR component, that can be jittered to be much faster. – jcolebrand Jan 25 '11 at 18:25
  • well that means i need to get the previous inserted code [VI---] back to presentation layer persist it and then do operation on it then insert it back again. which means: select max(thecode) from blahblahblah then do right with 6 chars and then add 0 to make it int and then add 1 to increment it huhh sounds simple but let me try that – Deeptechtons Jan 27 '11 at 6:44

As gbn suggests (this answer started as a comment to his post, but grew ttoo long to be practical in that area) the only obvious simple optimisation it to make that VARCHAR an NVARCHAR and save one type conversion per invocation. The way you have it there will be an explicit conversion to VARCHAR followed by an implicit conversion to NVARCHAR. Or make the NVARCHARs VARCHARs if you want a VARCHAR out at the end anyway.

You could perhaps change the CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR) to instead CONVERT() to a string dat format that includes mm/yyyy and use SUBSTRING to pull that part out. That way you have:

  • 1x conversion date->string
  • 1x sub-string operation

instead of

  • 2x operations pulling integer parts out of a date
  • 2x type conversions int->string
  • some extra string concatenation (though as there is other concatenation going on anyway this will make little difference as it'll do it all at once)

per invocation.

But really this is not going to save you much, if anything. Even if it did save anything in terms of CPU load or wall-clock time I expect it would very very small even over a large result set and/or in a long loop. And the format would not be quite the same: your current format includes m/yy (no leading zero for months lower than 10) and this would yield mm/yy.


Agree with gbn on mixing nvarchar with varchar, an alternative way of making the sql easier to read (doesn't make it any faster/slower to run), could be:

/* opt 1 */
select 'VI'+ SUBSTRING(CONVERT(varchar, getdate(), 103), 4, 7) 
        + '/00000' + CONVERT(varchar, @number+1)


/* opt 2 */
+'/00000' +CAST(@number+1 AS VARCHAR)

IMPORTANT NOTE: When month is < 10 different result produced:

opt 1 produces: VI1/2011/000002
opt 2 produces: VI01/2011/000002

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.