SQL Identity with leading padded variable number of zeros

Question

Hi, I need to zero pad my ID column for putting in drop down combo boxes, example

001 item text

101 item text 2

but if the numbers only went to 80, then we should have

01 item text

80 item text

and so on, since I need to write this and allow tens / thousands / millions of items to go in, I don't know how many 0's i need up front.

I have the following

An SQL Function

-- Create a function to add 0's to the left of a string.
--Usage: 
ALTER FUNCTION [dbo].[LEFTPADNUMERIC]

               (@SourceString VARCHAR(MAX), 

                @FinalLength  INT) 

RETURNS VARCHAR(MAX) 

AS 

  BEGIN 

    RETURN 

      (SELECT Replicate('0',@FinalLength - Len(@SourceString)) + @SourceString) 

  END

And it get’s used like this:

SELECT T.Id, dbo.LEFTPADNUMERIC(Cast(T.Id AS VARCHAR(32)), (Select LEN(CAST(MAX(Id) as varchar(32))) From Task) ) + SPACE(1) + TT.TaskName
FROM Task T
JOIN TaskTranslation TT ON TT.Id = T.Id AND TT.Language = blah blah blah on and on

do you know a better, more efficient way?

Is it going to be calculating max every time, or is sql clever enough to remember that?

Thanks in advance.

Answer 1

In this case, it would be much easier to add a computed, persisted column to your SQL table that has this padded content:

ALTER TABLE dbo.YourTable
  ADD PaddedID AS RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED

Being a persisted column, the data is actually stored (not calculated on each access), so it will be fast. Now just select this PaddedID column for your drop down box and you're good to go!