how to get maxium value of a table when there are no records

Question

Post reply

how to get maxium value of a table when there are no records

venkatesh.kuppili

Mr or Mrs. 500

Points: 579
More actions
January 20, 2009 at 3:19 pm

#133711

i have table a with two columns..
Testtable
vai1 int pk
vai2 varchar(50)
i want to get the max value of primary key column
supose i already inserted 3 records and deleted 3 records
i want to get the value as "4" when there are no records
declare @max-2 int
select @max-2=(vai1) from iw.prizes_seeds
select @max-2
Please help me

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

SELECT IDENT_CURRENT('yourtablename')+1

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

venkatesh.kuppili Mr or Mrs. 500 Points: 579 More actions · Answer 2

Thank you very much..

It is wokring fine now..

Thank you!

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Probably should have clarified. That returns the last used identity value from your table and adds 1 to it. That may not be what you actually want. The differences in the multiple identity functions in the system from BOL:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.
SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Garadin (1/20/2009)
SELECT IDENT_CURRENT('yourtablename')+1

I thought about this, but the OP's post didn't indicate that the PK (int column) was defined as an identity column. With out that info, how would youo determine the max value when all records have been deleted??

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Lynn Pettis (1/20/2009)
Garadin (1/20/2009)
SELECT IDENT_CURRENT('yourtablename')+1
I thought about this, but the OP's post didn't indicate that the PK (int column) was defined as an identity column. With out that info, how would youo determine the max value when all records have been deleted??

That's the only thing that made sense to me as to why someone would be worried about incrementing an empty table. I guess I did just assume it was an identity column tho :hehe:.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Garadin (1/20/2009)
Lynn Pettis (1/20/2009)
Garadin (1/20/2009)
SELECT IDENT_CURRENT('yourtablename')+1
I thought about this, but the OP's post didn't indicate that the PK (int column) was defined as an identity column. With out that info, how would youo determine the max value when all records have been deleted??
That's the only thing that made sense to me as to why someone would be worried about incrementing an empty table. I guess I did just assume it was an identity column tho :hehe:.

But if it is an IDENTITY column, why are you concerned about the max value? It will automatically increment the next time a row is inserted.

The question doesn't make much sense.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 7

And if you want to get really confused, then, without an IDENTITY setting on the column, when the table is empty, the MAX value should be NULL. I'll bet that's not helpful to whatever it is that the OP is doing.

"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