SQL Server Management Studio ZEROFILL

sql sql-server-2008 ssms zerofill
Austin picture Austin · Feb 27, 2013 · Viewed 9.1k times · Source

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

Answer

sgeddes picture sgeddes · Feb 27, 2013

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

Related questions

SQL Server 2008: how do I grant privileges to a username?

I need to be able to establish an ODBC connection through SQL Server authentication. In SSMS how do I grant permission for a user to be able to have ALL RIGHTS on a specific database? is there a way to …

Read More
How do I grant myself admin access to a local SQL Server instance?

I installed SQL Server 2008 R2 to my local machine. But, I can't create a new database because of rights (or lack of). "CREATE DATABASE PERMISSION DENIED" So, I tried to assign the admin privileges to my current login "User does …

Read More
Saving results with headers in Sql Server Management Studio

I am using SQL Server Management Studio. I wish to save the results of a query to an excel file. I choose "save as" and then save to CSV file which I can open in excel. All good except I …

Read More