negative count - 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 2008 Forums

Transact-SQL (2008)

negative count

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2013-11-04 : 04:32:46

in which case the incremental counter of a table becomes negative
example
1
2
3
......
32
-10

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-04 : 05:53:23

Is it an identity column?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 06:47:57

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

wided
Posting Yak Master

218 Posts

Posted - 2013-11-04 : 08:02:16

Thanks visakh16 to this information

this is not the case

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 08:03:24

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-04 : 08:39:03

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources