How To Find Next Key Value - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How To Find Next Key Value

Author

Topic

sree432
Starting Member

7 Posts

Posted - 2004-11-25 : 04:39:38

I Want To Know What Is The Next Primary Key Value
I Need To Use For New Insert Statement ????
==================================================
Following Are The Info/Conditions
1. Table Got Primary Key
2. Primary Key Is Varchar
3. And Primary Key Is Not IDENTITY Field
4. Donot Want To Use Max Function To Know Next Primary Key

What The Way To Find It ????
This is My Interview Question ??
It seems it can be known using SQL Server.

Can Any body able to give me answer ???

Early thanks

Sreedhar Reddy

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-25 : 05:04:03

how do they generate the primary key? is it an integer value but saved as varchar?

you can use

select top 1 primarykey from tablename order by primarykey desc

--------------------
keeping it simple...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-25 : 14:35:58

Well, given the Info/conditions

select cast(newid() as varchar(36)) as new_key

But really, You have to define "Next Primary Key Value".
If the key is 'ASdrkiD', then what is the "next" PK value

rockmoose

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-26 : 11:34:02

[code]declare @t table (q varchar(8) primary key)
insert @t
select '1' union
select '2' union
select '3' union
select '4' union
select '5' union
select '6' union
select '7' union
select '8' union
select '9' union
select '10' union
select '11'
select top 1 cast(cast(q as int)+1 as varchar(8)) Next_PK_Value
from @t
order by cast(q as int) desc[/code]

CLages
Posting Yak Master

116 Posts

Posted - 2004-11-27 : 08:44:32

WHAT EXTACTY DOES "Next_PK_Value" in the sentence ?
Because i execute without it and nothing changes.

in other word both return the same result.

tks
Carlos Lages

select top 1 cast(cast(q as int)+1 as varchar(8))
select top 1 cast(cast(q as int)+1 as varchar(8)) Next_PK_Value

Carlos Lages

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-27 : 10:18:47

quote:
Originally posted by CLages

WHAT EXTACTY DOES "Next_PK_Value" in the sentence ?
Because i execute without it and nothing changes.

in other word both return the same result.

tks
Carlos Lages

select top 1 cast(cast(q as int)+1 as varchar(8))
select top 1 cast(cast(q as int)+1 as varchar(8)) Next_PK_Value

It is just an alias for the column name.
The query is the same.

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-27 : 12:34:45

This is not an answerable question. You need more info about the PK. i.e. how is the next value to be calculated.
Pretty obvious that the question isn't well thought out as 3. is not needed as an identity is already not possible.
There are a lot of ways of generating a value that does not already exist - but if you have an artificial key then a varchar is not a good choice.

I suspect this was the sort of thing they were looking for rather than an answer. i.e. that you realise the question doesn't have a correct answer.

quote:
Originally posted by sree432

I Want To Know What Is The Next Primary Key Value
I Need To Use For New Insert Statement ????
==================================================
Following Are The Info/Conditions
1. Table Got Primary Key
2. Primary Key Is Varchar
3. And Primary Key Is Not IDENTITY Field
4. Donot Want To Use Max Function To Know Next Primary Key

What The Way To Find It ????
This is My Interview Question ??
It seems it can be known using SQL Server.

Can Any body able to give me answer ???

Early thanks

Sreedhar Reddy

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-27 : 17:20:41

not a good interview question at all. horrible.

I guess they are looking for something like this:

select top 1 (convert(int, PK) +1 ) as NextPK from table order by PK DESC

but i like the new_id() method !!! i bet if you tell them that, they will be very confused and not even understand why that is a perfectly good answer, based on the conditions and what they are asking! of course, they are probably in the camp that every table should have a system-generated PK that is meaningless. i love it .. what is the "next" value ... that cracks me up.

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-27 : 18:42:20

If they don't want to use the MAX function, You could give them MIN(..) - 1 instead

This is really silly.
I don't think You want the job anyway !?!?

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources