SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 negative count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wided
Posting Yak Master

200 Posts

Posted - 11/04/2013 :  04:32:46  Show Profile  Reply with Quote
in which case the incremental counter of a table becomes negative
example
1
2
3
......
32
-10

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 11/04/2013 :  05:53:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Is it an identity column?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/04/2013 :  06:47:57  Show Profile  Reply with Quote
Three possibilities
1. you can set identity increment as negatve number
illustration below

declare @t table
(
id int identity(3,-1),
val varchar(5)
)

insert @t
values('test1'),
('test2'),
('test3'),
('test4'),
('test5')

SELECT * FROM @T

output
----------------------------
id	val
---------------------------
3	test1
2	test2
1	test3
0	test4
-1	test5



2. Another case is when you start from a negative number
illustration:

declare @t table
(
id int identity(-3,1),
val varchar(5)
)

insert @t
values('test1'),
('test2'),
('test3'),
('test4'),
('test5')

SELECT * FROM @T

output
------------------------
id	val
------------------------
-3	test1
-2	test2
-1	test3
0	test4
1	test5


3. Finally you can insert a negative value explicitly by setting identity_insert to on


create table #t
(
id int identity(1,1),
val varchar(5)
)

insert #t
values('test1'),
('test2'),
('test3'),
('test4')
set identity_insert  #t on


insert  #t (id,val)
values(-1,'test5')
set identity_insert  #t off

SELECT * FROM  #t

drop table #t


output
----------------------------------
id	val
----------------------------------
1	test1
2	test2
3	test3
4	test4
-1	test5





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wided
Posting Yak Master

200 Posts

Posted - 11/04/2013 :  08:02:16  Show Profile  Reply with Quote
Thanks visakh16 to this information

this is not the case

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/04/2013 :  08:03:24  Show Profile  Reply with Quote
quote:
Originally posted by wided

Thanks visakh16 to this information

this is not the case




then explain us whats your case.
We cant play guess game anymore!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 11/04/2013 :  08:39:03  Show Profile  Reply with Quote
IF the column is NOT an identity column, then somebody - a client program, an import job, or another developer perhaps - inserted the negative values into it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000