March 9, 2014 at 1:22 am
Hi
We have identity functionality for integer , Similarly i want for charters .i.e.
EX:
ID Name Sal
CD101 A 1000
CD102 B 2000
CD103 C 3000
CD104 D 4000
CD105 E 5000
I want output like above, I wont provide Value for ID column. It it should take automatically while inserting data into table.(like identity column)?
Is it possible in SQL Server? If Possible , can any one explain ?
Thanks in-advance
March 9, 2014 at 4:51 am
If we're talking SQL Server 2012, you can use a SEQUENCE. There are a number of examples on the documentation page.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2014 at 5:51 am
Grant Fritchey (3/9/2014)
If we're talking SQL Server 2012, you can use a SEQUENCE. There are a nuber of examples on the documentation page.
i think sequence for only integer datatypes not for characters. Am I right?
March 9, 2014 at 6:02 am
PRR.DB (3/9/2014)
Grant Fritchey (3/9/2014)
If we're talking SQL Server 2012, you can use a SEQUENCE. There are a nuber of examples on the documentation page.i think sequence for only integer datatypes not for characters. Am I right?
A sequence is just a way of incrementing numbers. It's not associated with a table or a column. It's just a function running out in SQL Server. If you want to make it a default value for the column, you can. You just call NEXT value FOR and you're off.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2014 at 7:42 am
PRR.DB (3/9/2014)
HiWe have identity functionality for integer , Similarly i want for charters .i.e.
EX:
ID Name Sal
CD101 A 1000
CD102 B 2000
CD103 C 3000
CD104 D 4000
CD105 E 5000
I want output like above, I wont provide Value for ID column. It it should take automatically while inserting data into table.(like identity column)?
Is it possible in SQL Server? If Possible , can any one explain ?
Thanks in-advance
Don't this to yourself. You are trying to open a can of worms that will haunt you for the rest of eternity. Not only is it incredibly difficult to do with concurrency, it provides no benefit. In fact, it is possible to cause lots of issues. Consider what happens when you start getting into the 3 character realm. You suddenly have a LOT of combinations you want to exclude.
Why does your primary key have to be a some alphanumeric mashup? Is there really anything wrong just a number?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 10, 2014 at 9:36 am
I agree with Sean. What will happen when you get to one thousand?
You could have an identity column and a computed column to show your alphanumeric ID if the characters are not intended to count and are made just to describe some property.
March 10, 2014 at 9:46 am
Let's pretend that after 999 we increment the last character.
So when we have ABC999 the next value will be ABD001.
Now consider what the next value will be when the current value is ASR999 or SEW999. Do you see how horrible this can get?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 10, 2014 at 10:50 pm
Sean Lange (3/10/2014)
Let's pretend that after 999 we increment the last character.So when we have ABC999 the next value will be ABD001.
Now consider what the next value will be when the current value is ASR999 or SEW999. Do you see how horrible this can get?
Hi Sean,
String is fixed in ID column.
EX: E101,E102,E103.......E999,E1000,E1001,.......E99999999
always E is fixed
March 11, 2014 at 7:25 am
PRR.DB (3/10/2014)
Sean Lange (3/10/2014)
Let's pretend that after 999 we increment the last character.So when we have ABC999 the next value will be ABD001.
Now consider what the next value will be when the current value is ASR999 or SEW999. Do you see how horrible this can get?
Hi Sean,
String is fixed in ID column.
EX: E101,E102,E103.......E999,E1000,E1001,.......E99999999
always E is fixed
That doesn't match what you posted originally. But if all you want is to stick an E on the front just generate a computed column, or even better, add this on when you pull the data out. If the value is always E then what is the point of having it in your primary key?
Alter table YourTable
add NewColumn as 'E' + cast(MyIdentityColumn as varchar(7))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2014 at 7:38 am
While I'm honestly not crazy about this approach in general, it's not an end of the world scenario and, SEQUENCE can absolutely do what you're asking for. Personally, I'd do this as an alternate key since it's clearly supposed to be human readable. Just have the real PK on the table be an integer. It doesn't have to be exposed to the users and they can still refer to this one as long as you put a unique constraint on it, it'll be fine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply