SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using Alphanumeric column as Identity column		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gigabyte
Starting Member

India
30 Posts
Posted - 05/29/2012 :  14:08:51  Show Profile  Reply with Quote
How could we convert\use alphanumeric column as identity column??

Thanks in advance.

GIGABYTE+

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 05/29/2012 :  14:22:46  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
We can't.
Could set the value using a trigger but an identity has to be numeric.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Gigabyte
Starting Member

India
30 Posts
Posted - 05/29/2012 :  14:28:43  Show Profile  Reply with Quote
I know we should not have alphabets in identity column.

But how would you deal with situation where you have a column which has alphanumeric data and you need to make that as identity column.

So, I mean using that column, how can create\add a new column which auto increment.

Thanks in advance.

SATISH GEDDAMURI
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 05/29/2012 :  14:31:05  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
It's not possible with an identity. You can use a trigger - but what do you mean by aut increment for an alphanumeric value?.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 05/29/2012 :  14:31:57  Show Profile  Reply with Quote
quote:
Originally posted by Gigabyte

I know we should not have alphabets in identity column.

But how would you deal with situation where you have a column which has alphanumeric data and you need to make that as identity column.

So, I mean using that column, how can create\add a new column which auto increment.

Thanks in advance.

SATISH GEDDAMURI

do you mean creating a column with alphanumeric sequence?

something like

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Gigabyte
Starting Member

India
30 Posts
Posted - 05/29/2012 :  14:46:35  Show Profile  Reply with Quote
Lets say, we have a table with doesn't have identity column like below:

id-- data
1agh -- A
2rtu -- B
3poo -- C
4oik -- D
5uuu -- E
6jkkh-- F
7dfghh -- G
8tghgh -- H
9fhh -- I
10sdfg -- J
11fg -- K
12bgh -- L

Using ID column as reference how would you create a column which is an identity column?

Thanks in advance.


SATISH GEDDAMURI
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 05/29/2012 :  15:08:35  Show Profile  Reply with Quote
quote:
Originally posted by Gigabyte

Lets say, we have a table with doesn't have identity column like below:

id-- data
1agh -- A
2rtu -- B
3poo -- C
4oik -- D
5uuu -- E
6jkkh-- F
7dfghh -- G
8tghgh -- H
9fhh -- I
10sdfg -- J
11fg -- K
12bgh -- L

Using ID column as reference how would you create a column which is an identity column?

Thanks in advance.


SATISH GEDDAMURI

see the last posted link

you'll need to create a computed column based on an integer identity column for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 05/30/2012 :  00:02:57  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Better to split the I'd column into two to separate the numerical prefix. This could then become an identity if you wish - but you probably don't want gaps to support the letter equivalent so populate it using a trigger. Now you can generate the letter when the table is accessed using a computed column or view.
You could use the trigger to populate the letter as well as the numerical value if you wish. Do you not want to allow for more than 26 rows?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000