Search code examples
sqlsql-server-2008ssmszerofill

SQL Server Management Studio ZEROFILL

I am trying to add a auto_increment primary key with ZEROFILL with a max size of six.

So it outputs:

000001
000002 etc...

However I am really struggling to achieve this and cant find the answer. How do I set up this column type in SQL Server Management Studio?

Thanks

Solution

  • You cannot do this with an integer field in SQL Server (nor would I recommend it with a Varchar).

    Let SQL Server store the field as an Identity, and then (assuming this is for display), format the data when you select it like such:

    SELECT RIGHT('000000' + CONVERT(VARCHAR(6),ID), 6) FROM Table