[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Identity Column on VARCHAR

Posted on 2008-11-17
3
Medium Priority
?
1,309 Views
Last Modified: 2012-05-05
The primary key in a current table is set to VARCHAR(100). The columns start with S and then trail with several numbers. The new requirement is make all new records a number only and auto increment. However, I can't figure out how to make this auto increment with a data type of VARCHAR. Any thoughts?

SAMPLE TABLE:

CREATE TABLE Question (
        MyID      VARCHAR(100)      PRIMARY KEY      
      , MyCol      VARCHAR(30)            NOT NULL
)

SAMPLE OF EXISTING DATA:

MyID      MyCol
S00001      Example1
S00002      Example2
S00003      Example3
S00004      Example4
S00005      Example5

SAMPLE OF DESIRED DATA:

MyID      MyCol
S00005      Example5
S00004      Example4
S00003      Example3
S00002      Example2
S00001      Example1
5      Example10
4      Example9
3      Example8
2      Example7
1      Example6
0
Comment
Question by:computerstreber
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22977521
in short: you cannot.
especially, you cannot mix identity values with non-identity values.

long answer:
you could create the functionality with a trigger...

or (better):

create 1 additional column that is identity (for all rows, hence).
create 1 additional computed column that will return the current value of MyID, if not null, otherwise the identity column value.

0
 
LVL 15

Expert Comment

by:MohammedU
ID: 22977720
Keep in mind that you will end up with performance issue if the table has hight inserts....
Better to convert the col datatype if there are no non interger values ....
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22978187
Here is the code for angelIII functionality to recreate the tables.
CREATE TABLE dbo.Question
	(
	MyID  AS  (CONVERT([varchar](100),ID,0)) PERSISTED NOT NULL,
	MyCol varchar(50) NULL,
	ID int NOT NULL IDENTITY (1, 1)
	)  ON [PRIMARY]
GO
 
 
 
ALTER TABLE dbo.Question ADD CONSTRAINT
	PK_Question PRIMARY KEY CLUSTERED 
	(
	MyID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
GO

Open in new window

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question