Identity key on varchar column - T-SQL

sql sql-server tsql sql-server-2008 calculated-columns
Abey picture Abey · Sep 3, 2010 · Viewed 15.5k times · Source

I want to know if I can create an Identity (auto increment on a Varchar column. and how can I make it a primary key and create foreign key references on other table.

This is the code i have - 

CREATE TABLE Questions(
    QuestionID int IDENTITY PRIMARY KEY, 
    QuestionNo as 'Q'+Cast(QuestionID as Varchar(10),       
    Question Varchar(200)
)

Is there a way I can make QuestionNo as Primary key and reference it in another table (say Answers (AnswerID, QuestionNo, AnswerText)?

Answer

OMG Ponies picture OMG Ponies · Sep 3, 2010

This worked for me on SQL Server 2005:

CREATE TABLE Questions(
  QuestionID int IDENTITY NOT NULL, 
  QuestionNo as 'Q'+Cast(QuestionID as Varchar(10)) PERSISTED PRIMARY KEY, 
  Question Varchar(200)
)

The main part is that a computed column needs the PERSISTED keyword...

Related questions

Create Computed Column using data from another table

I have a SQL Server 2008 R2 database. This database has two tables called Pictures and PictureUse. Picture table has the following columns: Id (int) PictureName (nvarchar(max)) CreateDate (datetime ) PictureUse table has the following columns : Id (int) Pictureid (int) CreateDate (…

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More