computerstreber
asked on
Identity Column on VARCHAR
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Better to convert the col datatype if there are no non interger values ....