November 20, 2011 at 11:14 pm
i have two identity column col1 and col2 in my table
i have written an SP in which on new row addition i want the latest ID so that i could refer that to another table
my query when i write the query
"SELECT @Lastest_id = SCOPE_IDENTITY()"
which value i will get Col1 or Col2
actaully i want Col2 value, should i need to change the query to get the desired result
November 21, 2011 at 12:00 am
Hmm , Indeterminate i would say.
It does sound as if your design is wrong, why have you two identity columns ?
November 21, 2011 at 2:02 am
actually first identity column is regular id and second identity column is primary Key and will be use for reference
you mean table design is problem, i should keep only one identity column,but i want to identity column each have its purpose
okey suppose if i remove one identity column but for my knowledge could you let me know which Column it will take
November 21, 2011 at 7:56 am
Hi
Please will you clear me what this sentace means
"first identity column is regular id and second identity column is primary Key "
Look brother you can have only one identity column in table and column that have identity and column that have primary key is different things...
you can have idendity column and Primary key on single column also.
Look when your saying u have placed Identity Key to some column it only generates automatic value and one with Primary key you need to pass the value to it.. as primary key column wont have auto generate value...
And as per your question the column that have key word IDENTITY to its name will be returne when you call Scope_Identity.
note you cant have two identity column in single table.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
November 21, 2011 at 8:00 am
BEGIN TRAN
CREATE TABLE dbo.tid
(
id1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
id2 INT IDENTITY(10,10) NOT NULL,
sometext VARCHAR(10) default(newid())
)
ROLLBACK
Msg 2744, Level 16, State 2, Line 3
Multiple identity columns specified for table 'tid'. Only one identity column per table is allowed.
November 21, 2011 at 10:18 pm
Okey got it ,thanx for your explanation
actaully i have created table in design view
in that i have created two identity column
but fail to note that when i made Col1 as Identity Col2 identity get removed
only when i created table using sql syntax that time it gave me error
November 22, 2011 at 4:19 am
What version are you using?
Might be interesting to post feedback to ms about it.
November 22, 2011 at 6:04 am
i am using SQL Server 2005 Developer Edition
November 22, 2011 at 6:11 am
Still <does not> fails the same way in Denali.
I'll ask around if it's worth posting as a bug reporting upgrade.
November 22, 2011 at 6:42 am
I still have SQL 2005 dev. Let me test real quick.
November 22, 2011 at 6:48 am
Okay, the behavior does work as described. When entering multiple columns in the designer, it will move the IDENTITY property to the most recent column chosen as identity.
However, I do not believe this is a bug or an error. The fact of the matter is, only one IDENTITY column is allowed per table. Since the designer isn't saving the table while you're designing it, it decides you've changed your mind about which column you want the IDENTITY property assigned to.
If you create the table with column 1 as the IDENTITY, don't choose IDENTITY on another column, save the table, then modify it trying to enter a second IDENTITY column, SSMS will correctly error out.
My opinion, this is by design, not a bug, not a silent error, and isn't worth it to report to Microsoft because they will probably say "We're not going to fix it."
November 22, 2011 at 6:56 am
Nice catch, works for me.
Tx Brandie.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply