Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Gigabyte
Starting Member
30 Posts |
 Posted - 2012-05-29 : 14:08:51
|
| How could we convert\use alphanumeric column as identity column??Thanks in advance.GIGABYTE+ |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-29 : 14:22:46
|
| 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. |
 |
|
|
Gigabyte
Starting Member
30 Posts |
Posted - 2012-05-29 : 14:28:43
|
| 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 |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-29 : 14:31:05
|
| 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-29 : 14:31:57
|
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 likehttp://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Gigabyte
Starting Member
30 Posts |
Posted - 2012-05-29 : 14:46:35
|
| 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 -- LUsing ID column as reference how would you create a column which is an identity column?Thanks in advance. SATISH GEDDAMURI |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-29 : 15:08:35
|
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 -- LUsing ID column as reference how would you create a column which is an identity column?Thanks in advance. SATISH GEDDAMURI
see the last posted linkyou'll need to create a computed column based on an integer identity column for that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-30 : 00:02:57
|
| 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. |
|
|
|
|
|
|
Using Alphanumeric column as Identity column