Showing posts with label type. Show all posts
Showing posts with label type. Show all posts

Wednesday, March 28, 2012

if exists

i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?Does column b exist int able youy are trying to update?
--
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>

if exists

i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?
Does column b exist int able youy are trying to update?
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>
sql

if exists

i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?what version of sql server is the problem in?
are there perhaps other owners of identically named tables?
for example, is there both a mauro.table and a dbo.table, where the
mauro.table has the b column and the dbo.table does not?
Mauro wrote:
> i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>|||2005., NO, the column does not exists in the whole server, in the 2000
server it works perfectly and in 2005 in another databases it also works
fine.
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:Of%23ruGI7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> what version of sql server is the problem in?
> are there perhaps other owners of identically named tables?
> for example, is there both a mauro.table and a dbo.table, where the
> mauro.table has the b column and the dbo.table does not?
> Mauro wrote:
not

if exists

i have this:
if exist(select * from sysobjects where name= 'table' and type = 'u' )
begin
update table set a=1 where b=2
end
and its giving me an
"INVALID COMUN NAME B" error,because of course it does not exists
but in sql 2000 its works perfectly, i noticed that in 2005 on another
databases iit also works fine,
why is it entering if the "if "is not true, and why in my database and not
the others?
could this be a BUG?Does column b exist int able youy are trying to update?
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"Mauro" <msbrizuela@.gmail.com> wrote in message
news:%23JWNhRH7FHA.1416@.TK2MSFTNGP09.phx.gbl...
>i have this:
> if exist(select * from sysobjects where name= 'table' and type = 'u' )
> begin
> update table set a=1 where b=2
> end
> and its giving me an
> "INVALID COMUN NAME B" error,because of course it does not exists
> but in sql 2000 its works perfectly, i noticed that in 2005 on another
> databases iit also works fine,
> why is it entering if the "if "is not true, and why in my database and not
> the others?
> could this be a BUG?
>
>
>

Wednesday, March 21, 2012

IDENTITY_INSERT

dear jeff johnson ,
thanks for ur response.
I have one table name fixtures in client machine .In that table one column
is identity type .now i am insert one Row from server database to that clien
t
database
now i have to on the identity_insert in client machine from server machine
give some suggestion.
sangeetha-server machine
sankar--client machine
i am running this query from my machine my machine name is sangeeta
EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
"set identity_insert scoremate.dbo.fixtures on "'
insert into openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select * from scoremate.dbo.fixtures')
select compcode,seasonid,matchid,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select compcode,seasonid,matchid=15,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
scoremate.dbo.fixtures where matchid=13')Hi
Everything seems to point to the client machine?
You may be better off connecting to the server where you are doing the
inserts and not using your OPENROWSET as the destination of the insert.
John
"MOHAMED NASEER" wrote:

> dear jeff johnson ,
> thanks for ur response.
> I have one table name fixtures in client machine .In that table one colum
n
> is identity type .now i am insert one Row from server database to that cli
ent
> database
> now i have to on the identity_insert in client machine from server machine
> give some suggestion.
> sangeetha-server machine
> sankar--client machine
> i am running this query from my machine my machine name is sangeeta
> EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
> "set identity_insert scoremate.dbo.fixtures on "'
> insert into openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select * from scoremate.dbo.fixtures')
> select compcode,seasonid,matchid,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select compcode,seasonid,matchid=15,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> scoremate.dbo.fixtures where matchid=13')sql

Monday, March 19, 2012

Identity Seed

have a table set one column as
data type = int
identity to yes
identity seed = 1 and identity increment = 1
after deleting some rows (e.g. 5), the number 5 will never be re used.
when the number has grown to the limit, can I still insert new rows?when you mention a limit, I am assuming you are talking about a check
constraint.
Regardless, identity column values are not necessary inserted in the order
of sequence, however the seed will always be no lower than the last
committed value.
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Music Lover" <music@.my-heart.org> wrote in message
news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||No he means what happens when the INT field gets to 0x7FFFFFFF (2147483647)
"Mark Broadbent" <nospamplease_mark.broadbent@.virgin.net> wrote in message
news:VnpWa.582$k4.11506@.news2.nokia.com...
> when you mention a limit, I am assuming you are talking about a check
> constraint.
> Regardless, identity column values are not necessary inserted in the order
> of sequence, however the seed will always be no lower than the last
> committed value.
>
> --
> BR,
> Mark Broadbent mcdba,mcse+i
> _________________________
> "Music Lover" <music@.my-heart.org> wrote in message
> news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> > have a table set one column as
> > data type = int
> > identity to yes
> > identity seed = 1 and identity increment = 1
> >
> > after deleting some rows (e.g. 5), the number 5 will never be re used.
> >
> > when the number has grown to the limit, can I still insert new rows?|||Little Test...
USE PUBS
CREATE TABLE dbo.tblIdentity ( ID INT IDENTITY(2147483640,1) CONSTRAINT
PK_tblIdentity PRIMARY KEY CLUSTERED, DATA CHAR(1) )
GO
INSERT INTO tblIdentity ( DATA )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'G'
UNION ALL SELECT 'H'
UNION ALL SELECT 'I'
UNION ALL SELECT 'J'
GO
SELECT * FROM tblIdentity
GO
DROP TABLE dbo.tblIdentity
The Insert query generates the following Error:-
Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
So to answer your question.
No you can't insert records when the INT field reaches its limit
(2147483647)
When this happens you will have to reseed the field to -2147483648.
This will give you a few million more records...
When you run out of numbers after going through the negative values it'll be
time to a) archive some data b) change the number to a GUID or c) change the
number to a BIGINT. But 2147million records is enough for most people ;)
DBCC CHECKIDENT ( tblIdentity , RESEED , -2147483648 )
"Music Lover" <music@.my-heart.org> wrote:
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||you can reset the identity seed to start incrementing from
your chosen number...
>--Original Message--
>have a table set one column as
>data type = int
> identity to yes
>identity seed = 1 and identity increment = 1
>after deleting some rows (e.g. 5), the number 5 will
never be re used.
>when the number has grown to the limit, can I still
insert new rows?
>
>.
>|||And bigint can store -9,223,372,036,854,775,808 through
9,223,372,036,854,775,807.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Tony Wilton" <tony@.scruffytiger.co.uk.NOSPAM> wrote in message
news:3f2a3824$0$12152$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you use an INT field yes.
> Maximum values for data types are:-
> TINYINT 255
> SMALLINT 32767
> INT 2147483647
>
> "Music Lover" <music@.my-heart.org> wrote:
> > Thanks for your reply.
> > Want to double confirm
> > I can insert 2147million records withou any problem?
>|||thanks
how?
"jano" <janobermudes@.microsoft.com> wrote in message
news:09a801c35812$d7540810$a401280a@.phx.gbl...
> you can reset the identity seed to start incrementing from
> your chosen number...
>
> >--Original Message--
> >have a table set one column as
> >data type = int
> > identity to yes
> >identity seed = 1 and identity increment = 1
> >
> >after deleting some rows (e.g. 5), the number 5 will
> never be re used.
> >
> >when the number has grown to the limit, can I still
> insert new rows?
> >
> >
> >
> >.
> >

Monday, March 12, 2012

Identity Not for Replication

Hi,
I am beginner in replication, I use a merge type replication
I have a Publisher on a Sql2000 Server
I have a Distributor on an other Sql2000 Server
And I have subscriber with laptop
I use a pull subscription
My question is I f my database have 1000 tables and I use only 60 tables in
my replication
Do I have to use Not for replication on all Identity column of the 60 tables
or all the 100 tables?
Thank in advance!
You only need to use it on the ones which are truly bi-directional, or where
the inserts will occur on both sides between syncs. For example if all of
your inserts occur on your publisher you will not need it. To be on the safe
side I would put it everywhere.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GC" <GC@.discussions.microsoft.com> wrote in message
news:78448474-E3B8-4449-8561-63B13AA33025@.microsoft.com...
> Hi,
> I am beginner in replication, I use a merge type replication
> I have a Publisher on a Sql2000 Server
> I have a Distributor on an other Sql2000 Server
> And I have subscriber with laptop
> I use a pull subscription
> My question is I f my database have 1000 tables and I use only 60 tables
> in
> my replication
> Do I have to use Not for replication on all Identity column of the 60
> tables
> or all the 100 tables?
> Thank in advance!
>
>
>

Friday, March 9, 2012

IDENTITY Data Type Gaps

Hi:
We recently upgraded an Access XP database to SQL Server, but continue to us
e an Access MDB as the front end (more mods need to be done to migrate the f
ront end to an Access Project). In the original Access database, several ta
bles use an Access AutoNumb
er data type as a primary key. At one point in time, the Access database wa
s converted to a replicated database. Following the conversion, the AutoNum
ber fields no longer create new values by adding 1 to the previous value; th
ey now use a random number
generator. (We converted the database back to a non-replicated format sever
al months prior to converting to SQL Server.)
When we converted to SQL, the AutoNumber fields were converted to IDENTITY d
ata type. With a new record, SQL adds to the highest value for a new Identi
ty field value. We've been having a number of "ODBC Time Out", and "Failed
insert on linked table" err
ors when adding records, (the "link fail" errors occur even when the Access
form being used to add the new record is based on a single table).
One of the things we've discovered is that some of the random numbers genera
ted by Access during its "replicated" phase are fairly close to the maximum
values allowed for the Access AutoNumber data type. (FYI, the Access AutoNu
mber is a "long integer" da
ta type; more or less equivalent to a SQL Server "int" data type.) One of t
he tables with frequent problems only has about 25,000 records, but some of
its records have primary key values that hover near the limit of the "long i
nt" data type. This has re
sulted in huge gaps in the numeric values in the new Identity field values.
I've tried everything I could find to see if I could force SQL Server to st
art new Identity field values in some of the lower value gaps, i.e., I tried
seeding the Identity field
at 27,000, and adding 1 for new records. However, it doesn't work.
Is there any way to force SQL Identity values on a table that already has re
cords in it? (It would be OK if we were creating a new table, but we've got
thousands of related records to these problematic tables so it'd be a bit t
ricky to create new Identit
y field values...although we will probably do this at some point in time.)
Any help would be really appreciated. If what I wrote makes no sense, I'm n
ot surprised. I've been working for 16 hours and I'm burnt.
THANKS!
PaulI don't think you're going to have any joy with this app until you get
rid of the identity property on the columns. However, I think you can
do this without messing up existing data.
Since you're working with linked tables with a Jet front-end, this
gives you some flexibility in coding a workaround. One solution would
be to remove the Identity property from the PK columns and write code
to generatenew unique int PK values yourself. One way you could do
this is to base new records on a local Jet table that has the
identical structure as the SQL Server table. All of this data could
get fed to a parameterized stored procedure that created a new record
and generated the new ID, which would be returned to the application
as an output parameter. Code in the stored procedure would create the
pk value based on querying the table to see if a particular value in a
particular range has been used. Wrap all this in an explicit
transaction that only commits once a valid PK in the numeric range
you're looking to fill is created. The new record could then be
dynamically displayed on a form based on the output parameter value in
a WHERE clause in the form's recordsource.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 29 Feb 2004 16:51:06 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>Hi:
>We recently upgraded an Access XP database to SQL Server, but continue to use an Ac
cess MDB as the front end (more mods need to be done to migrate the front end to an
Access Project). In the original Access database, several tables use an Access Auto
Num
ber data type as a primary key. At one point in time, the Access database w
as converted to a replicated database. Following the conversion, the AutoNu
mber fields no longer create new values by adding 1 to the previous value; t
hey now use a random number
generator. (We converted the database back to a non-replicated format several months prior
to converting to SQL Server.)
>When we converted to SQL, the AutoNumber fields were converted to IDENTITY data typ
e. With a new record, SQL adds to the highest value for a new Identity field value.
We've been having a number of "ODBC Time Out", and "Failed insert on linked table"
er
rors when adding records, (the "link fail" errors occur even when the Access form being use
d to add the new record is based on a single table).
>One of the things we've discovered is that some of the random numbers generated by
Access during its "replicated" phase are fairly close to the maximum values allowed
for the Access AutoNumber data type. (FYI, the Access AutoNumber is a "long integer
" d
ata type; more or less equivalent to a SQL Server "int" data type.) One of
the tables with frequent problems only has about 25,000 records, but some of
its records have primary key values that hover near the limit of the "long
int" data type. This has r
esulted in huge gaps in the numeric values in the new Identity field values.
I've tried everything I could find to see if I could force SQL Server to s
tart new Identity field values in some of the lower value gaps, i.e., I trie
d seeding the Identity fiel
d at 27,000, and adding 1 for new records. However, it doesn't work.
>Is there any way to force SQL Identity values on a table that already has records i
n it? (It would be OK if we were creating a new table, but we've got thousands of r
elated records to these problematic tables so it'd be a bit tricky to create new Ide
nti
ty field values...although we will probably do this at some point in time.)
>Any help would be really appreciated. If what I wrote makes no sense, I'm
not surprised. I've been working for 16 hours and I'm burnt.
>THANKS!
>Paul|||"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:68D7662C-5B5A-4F27-87FA-C2ED1DC43BFE@.microsoft.com...
> Hi:
> We recently upgraded an Access XP database to SQL Server, but continue to
use an Access MDB as the front end (more mods need to be done to migrate the
front end to an Access Project). In the original Access database, several
tables use an Access AutoNumber data type as a primary key. At one point in
time, the Access database was converted to a replicated database. Following
the conversion, the AutoNumber fields no longer create new values by adding
1 to the previous value; they now use a random number generator. (We
converted the database back to a non-replicated format several months prior
to converting to SQL Server.)
> When we converted to SQL, the AutoNumber fields were converted to IDENTITY
data type. With a new record, SQL adds to the highest value for a new
Identity field value. We've been having a number of "ODBC Time Out", and
"Failed insert on linked table" errors when adding records, (the "link fail"
errors occur even when the Access form being used to add the new record is
based on a single table).
> One of the things we've discovered is that some of the random numbers
generated by Access during its "replicated" phase are fairly close to the
maximum values allowed for the Access AutoNumber data type. (FYI, the
Access AutoNumber is a "long integer" data type; more or less equivalent to
a SQL Server "int" data type.) One of the tables with frequent problems
only has about 25,000 records, but some of its records have primary key
values that hover near the limit of the "long int" data type. This has
resulted in huge gaps in the numeric values in the new Identity field
values. I've tried everything I could find to see if I could force SQL
Server to start new Identity field values in some of the lower value gaps,
i.e., I tried seeding the Identity field at 27,000, and adding 1 for new
records. However, it doesn't work.
> Is there any way to force SQL Identity values on a table that already has
records in it? (It would be OK if we were creating a new table, but we've
got thousands of related records to these problematic tables so it'd be a
bit tricky to create new Identity field values...although we will probably
do this at some point in time.)
> Any help would be really appreciated. If what I wrote makes no sense, I'm
not surprised. I've been working for 16 hours and I'm burnt.
> THANKS!
> Paul
See if DBCC CHECKIDENT will help you. You can find the syntax using a
search engine.
Glenn|||Glenn:
I'll give this a shot. Thanks for your input! I've been developing Access
databases and customizing Office solutions for about 5 or 6 years, but when
our company bought out a competitor, I got thrown into the deep end. I'm co
ming up to speed on SQL Ser
ver, but am running into problems partly due to the situation, partly due to
inexperience w/ SQL. Little by little, we're gaining ground, but there's m
uch to be done.
Thanks again!
Paul|||Mary:
Your idea is an approach that I didn't think of. I'm going to look into thi
s closely. I think it's a great idea and could help us get thru this diffic
ult period. Thanks very much for the input! I'm learning SQL the hard way.
I've been doing Access da
tabases and Office customizations for several years, but when our company to
ok over a competing group, I had to dive into the deep end of the SQL Server
pool without my Donald Duck life preserver.
Thanks much again!
Paul|||Well, the newsgroups are a good place to get ideas and info -- good
luck -- the learning curve is indeed a bit steep when you are
attempting to build something more than the most simplistic app.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 7 Mar 2004 16:56:04 -0800, "Paul"
<anonymous@.discussions.microsoft.com> wrote:

>Your idea is an approach that I didn't think of. I'm going to look into this close
ly. I think it's a great idea and could help us get thru this difficult period. Th
anks very much for the input! I'm learning SQL the hard way. I've been doing Acces
s d
atabases and Office customizations for several years, but when our company t
ook over a competing group, I had to dive into the deep end of the SQL Serve
r pool without my Donald Duck life preserver.

Wednesday, March 7, 2012

Identity column.

Kudos to y'all! On most of my tables, I'm using a column of data type numeric and properties set as Identity with no replication. Now I know that it can handle only a length of 9 and obviously not nullable. Now by length of 9, does it mean in bytes or literal digits. If it is, then that would mean that it would reach only up to 999,999,999. What happens then if it reaches that digit?Kudos to y'all! On most of my tables, I'm using a column of data type numeric and properties set as Identity with no replication. Now I know that it can handle only a length of 9 and obviously not nullable. Now by length of 9, does it mean in bytes or literal digits. If it is, then that would mean that it would reach only up to 999,999,999. What happens then if it reaches that digit?

Numeric datatype ...
decimal and numeric
Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

--microsoft (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_3grn.asp)

You can see its larger than what you think.
You can use uniqueidentifier (A globally unique identifier (GUID)) in this case where you can be sure that the above problem will never arise for time being.
But I think after the year 9999 ,those data can have duplicate values.
uniqueidentifier (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_6dyq.asp)|||if you use INTEGER, you can store up to 2 billion (which is a bit bigger than 999,999,999)

if that's not big enough, use BIGINT|||if you use INTEGER, you can store up to 2 billion (which is a bit bigger than 999,999,999)

if that's not big enough, use BIGINT

Thanks for the tip r937. I tried your suggestion but it turns out that with INT data type and set as identity column, the length is only 4 whilst the BIGINT data type gives me only 8. So that would mean...

NUMERIC-> 999999999
INT-> 9999
BIGINT-> 99999999

...or am I lookin' at it the wrong way?|||yes, you are looking at it the wrong way

INT stores numbers from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647)

BIGINT stores numbers from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807)

look it up in Books OnLine

:)|||:cool: This is how the math is done on computers:

Int type is 4 bytes, which is 4*8=32 bits, which means 2^31~= :shocked: 2Gigs(10^9):shocked: unique numbers
(I think a bit is take off for +/-)

BigInt type is 8 bytes, which is 8*8=64 bits, which means 2^63 ~=:shocked: 8*10^18:shocked: unique numbers.

:angel: have fun|||In short, the length of the datatype is the number of bytes it requires. Character data is 1 byte per character (2 for unicode), so the length of the datatype equals the length of the string. Numeric datatypes are stored in binary, as Spere explains above.|||Thanks guys for the explanation. So the length doesn't necessary mean the number of digits/characters but the number of bytes. Interesting. I'll look it up for some more on BOL. Thanks again guys!

Friday, February 24, 2012

Identity column increases abruptly, abnormally.

Hi
I am facing a big problem. please help.
We r having SQL Server 2000 Enterprise.
I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
delete.
These triggers are inserting the audit for these actions into another Table2
.
Table2 has an identity column which increment by 1 automatically.
Problem:
Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
As identity column increases automatically, I am helpless.
Is there any patch/service pack for this.
Please Suggest.
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UKHi,
Some thing new
Can you verify the application/code which is used inside tge trigger and
confirm whether you have too many rollbacks happening.
This is the only thing I can think off now.
This sort of issue might also happen if the server does not goes off through
a graceful shutdown.
Thanks
Hari
SQL Server MVP
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another
> Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Also check whether there is replication involved with range based identity.
May be one of the subscribers has an identity range starting from 500 +...
"Sameer" wrote:

> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another Tabl
e2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Thanks Hari for ur prompt reply.
ya there r some possibilities of rollbacks.
i really appreciate ur guess.
Can u please explain what happens to a identity column & its values when
rollback comes into picture?
Please help .. I may be able to solve the problem by ur valuable feedback.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hari Prasad" wrote:

> Hi,
> Some thing new
> Can you verify the application/code which is used inside tge trigger and
> confirm whether you have too many rollbacks happening.
> This is the only thing I can think off now.
> This sort of issue might also happen if the server does not goes off throu
gh
> a graceful shutdown.
> Thanks
> Hari
> SQL Server MVP
> "Sameer" <Sameer@.discussions.microsoft.com> wrote in message
> news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
>
>|||On Fri, 25 Aug 2006 03:19:01 -0700, Sameer wrote:

>Hi
>I am facing a big problem. please help.
>We r having SQL Server 2000 Enterprise.
>I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
>delete.
>These triggers are inserting the audit for these actions into another Table
2.
>Table2 has an identity column which increment by 1 automatically.
>Problem:
>Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
>As identity column increases automatically, I am helpless.
>Is there any patch/service pack for this.
Hi Sameer,
It might help if you posted some code.
Hugo Kornelis, SQL Server MVP|||On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:

>Thanks Hari for ur prompt reply.
>ya there r some possibilities of rollbacks.
>i really appreciate ur guess.
>Can u please explain what happens to a identity column & its values when
>rollback comes into picture?
Hi Sameer,
Since the identity value generation is performed outside of the
transaction context, the values generated for the inserts that were
rolled back are not re-used. See the repro below.
CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
OtherCol int NOT NULL CHECK (OtherCol > 0));
-- First row is inserted
INSERT INTO Demo (OtherCol) VALUES (1);
-- Second row explicitly rolled back
BEGIN TRAN;
INSERT INTO Demo (OtherCol) VALUES (2);
ROLLBACK TRAN;
-- Third row implicitly rolled back because constraint is violated
INSERT INTO Demo (OtherCol) VALUES (-3);
-- Fourth row is inserted
INSERT INTO Demo (OtherCol) VALUES (4);
-- Check results
SELECT * FROM Demo;
DROP TABLE Demo;
Hugo Kornelis, SQL Server MVP|||How can i solve this identity increase problem ... as identity is not in
context of transaction.
Please suggest a solution to rollback identity with transaction.
i'll be grateful to u.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hugo Kornelis" wrote:

> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>
> Hi Sameer,
> Since the identity value generation is performed outside of the
> transaction context, the values generated for the inserts that were
> rolled back are not re-used. See the repro below.
> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
> OtherCol int NOT NULL CHECK (OtherCol > 0));
> -- First row is inserted
> INSERT INTO Demo (OtherCol) VALUES (1);
> -- Second row explicitly rolled back
> BEGIN TRAN;
> INSERT INTO Demo (OtherCol) VALUES (2);
> ROLLBACK TRAN;
> -- Third row implicitly rolled back because constraint is violated
> INSERT INTO Demo (OtherCol) VALUES (-3);
> -- Fourth row is inserted
> INSERT INTO Demo (OtherCol) VALUES (4);
> -- Check results
> SELECT * FROM Demo;
> DROP TABLE Demo;
>
> --
> Hugo Kornelis, SQL Server MVP
>|||Basically, you can't keep an identify column from skipping values in the
face of rollbacks unless the transactions are serialized. You need to assign
your own number and then wait for the transaction that uses that number to
commit before assigning the next one. This will have lousy performance but
it's the only way that you can roll back a transaction without missing a
number. For example if you set the sequence number to max sequence number +
1, you need to wait until that insert commits before you can set the next
one because the next transaction needs the new number before it assigns its
number. If you assign the number outside of the transaction with an
identity column, rollbacks will lose the number because it is used and not
written to the table.
Bottom line, you can't guarantee sequential numbers without sequential
transactions so your choices are to make the transactions sequential or to
live with skipped numbers.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:D229D6C8-38C3-4CC5-A972-368670CB88EA@.microsoft.com...[vbcol=seagreen]
> How can i solve this identity increase problem ... as identity is not in
> context of transaction.
> Please suggest a solution to rollback identity with transaction.
> i'll be grateful to u.
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens Business Services
> Bracknell
> UK
>
> "Hugo Kornelis" wrote:
>|||Lines: 24
Organization: Posted via Supernews, http://www.supernews.com
X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:10634
On Sat, 26 Aug 2006 10:21:02 -0700, Sameer wrote:

>How can i solve this identity increase problem ... as identity is not in
>context of transaction.
>Please suggest a solution to rollback identity with transaction.
>i'll be grateful to u.
Hi Sameer,
IDENTITY should never be used if the actuall values matter. It is
intended to be used as a surrogate key, for internal use by the database
logic but not exposed to the user.
If you care aboout gaps in your IDENTITY sequence, then you shouldn't
use IDENTITY at all. Find the current maximum value in the table, add
one and use that as your new value. Use locks to ensure that two
connections executing the same coode at the same time won't get the same
maximum value, then try to insert new rows with the same new value. Your
concurrency will suffer, but you're rid of gaps - until you manuallly
delete a row, of course.
Hugo Kornelis, SQL Server MVP

Identity column increases abruptly, abnormally.

Hi
I am facing a big problem. please help.
We r having SQL Server 2000 Enterprise.
I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
delete.
These triggers are inserting the audit for these actions into another Table2.
Table2 has an identity column which increment by 1 automatically.
Problem:
Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
As identity column increases automatically, I am helpless.
Is there any patch/service pack for this.
Please Suggest.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UKHi,
Some thing new :)
Can you verify the application/code which is used inside tge trigger and
confirm whether you have too many rollbacks happening.
This is the only thing I can think off now.
This sort of issue might also happen if the server does not goes off through
a graceful shutdown.
Thanks
Hari
SQL Server MVP
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another
> Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Also check whether there is replication involved with range based identity.
May be one of the subscribers has an identity range starting from 500 +...
"Sameer" wrote:
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Thanks Hari for ur prompt reply.
ya there r some possibilities of rollbacks.
i really appreciate ur guess.
Can u please explain what happens to a identity column & its values when
rollback comes into picture?
Please help .. I may be able to solve the problem by ur valuable feedback.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hari Prasad" wrote:
> Hi,
> Some thing new :)
> Can you verify the application/code which is used inside tge trigger and
> confirm whether you have too many rollbacks happening.
> This is the only thing I can think off now.
> This sort of issue might also happen if the server does not goes off through
> a graceful shutdown.
> Thanks
> Hari
> SQL Server MVP
> "Sameer" <Sameer@.discussions.microsoft.com> wrote in message
> news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> > Hi
> >
> > I am facing a big problem. please help.
> > We r having SQL Server 2000 Enterprise.
> >
> > I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> > delete.
> > These triggers are inserting the audit for these actions into another
> > Table2.
> > Table2 has an identity column which increment by 1 automatically.
> >
> > Problem:
> > Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> > As identity column increases automatically, I am helpless.
> >
> > Is there any patch/service pack for this.
> >
> > Please Suggest.
> >
> >
> > --
> > Regards
> > Sameer Gupta
> > C# Designer & Developer
> > Siemens
> > Bracknell
> > UK
>
>|||On Fri, 25 Aug 2006 03:19:01 -0700, Sameer wrote:
>Hi
>I am facing a big problem. please help.
>We r having SQL Server 2000 Enterprise.
>I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
>delete.
>These triggers are inserting the audit for these actions into another Table2.
>Table2 has an identity column which increment by 1 automatically.
>Problem:
>Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
>As identity column increases automatically, I am helpless.
>Is there any patch/service pack for this.
Hi Sameer,
It might help if you posted some code.
--
Hugo Kornelis, SQL Server MVP|||On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>Thanks Hari for ur prompt reply.
>ya there r some possibilities of rollbacks.
>i really appreciate ur guess.
>Can u please explain what happens to a identity column & its values when
>rollback comes into picture?
Hi Sameer,
Since the identity value generation is performed outside of the
transaction context, the values generated for the inserts that were
rolled back are not re-used. See the repro below.
CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
OtherCol int NOT NULL CHECK (OtherCol > 0));
-- First row is inserted
INSERT INTO Demo (OtherCol) VALUES (1);
-- Second row explicitly rolled back
BEGIN TRAN;
INSERT INTO Demo (OtherCol) VALUES (2);
ROLLBACK TRAN;
-- Third row implicitly rolled back because constraint is violated
INSERT INTO Demo (OtherCol) VALUES (-3);
-- Fourth row is inserted
INSERT INTO Demo (OtherCol) VALUES (4);
-- Check results
SELECT * FROM Demo;
DROP TABLE Demo;
Hugo Kornelis, SQL Server MVP|||How can i solve this identity increase problem ... as identity is not in
context of transaction.
Please suggest a solution to rollback identity with transaction.
i'll be grateful to u.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hugo Kornelis" wrote:
> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
> >Thanks Hari for ur prompt reply.
> >
> >ya there r some possibilities of rollbacks.
> >i really appreciate ur guess.
> >Can u please explain what happens to a identity column & its values when
> >rollback comes into picture?
> Hi Sameer,
> Since the identity value generation is performed outside of the
> transaction context, the values generated for the inserts that were
> rolled back are not re-used. See the repro below.
> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
> OtherCol int NOT NULL CHECK (OtherCol > 0));
> -- First row is inserted
> INSERT INTO Demo (OtherCol) VALUES (1);
> -- Second row explicitly rolled back
> BEGIN TRAN;
> INSERT INTO Demo (OtherCol) VALUES (2);
> ROLLBACK TRAN;
> -- Third row implicitly rolled back because constraint is violated
> INSERT INTO Demo (OtherCol) VALUES (-3);
> -- Fourth row is inserted
> INSERT INTO Demo (OtherCol) VALUES (4);
> -- Check results
> SELECT * FROM Demo;
> DROP TABLE Demo;
>
> --
> Hugo Kornelis, SQL Server MVP
>|||Basically, you can't keep an identify column from skipping values in the
face of rollbacks unless the transactions are serialized. You need to assign
your own number and then wait for the transaction that uses that number to
commit before assigning the next one. This will have lousy performance but
it's the only way that you can roll back a transaction without missing a
number. For example if you set the sequence number to max sequence number +
1, you need to wait until that insert commits before you can set the next
one because the next transaction needs the new number before it assigns its
number. If you assign the number outside of the transaction with an
identity column, rollbacks will lose the number because it is used and not
written to the table.
Bottom line, you can't guarantee sequential numbers without sequential
transactions so your choices are to make the transactions sequential or to
live with skipped numbers.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:D229D6C8-38C3-4CC5-A972-368670CB88EA@.microsoft.com...
> How can i solve this identity increase problem ... as identity is not in
> context of transaction.
> Please suggest a solution to rollback identity with transaction.
> i'll be grateful to u.
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens Business Services
> Bracknell
> UK
>
> "Hugo Kornelis" wrote:
>> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>> >Thanks Hari for ur prompt reply.
>> >
>> >ya there r some possibilities of rollbacks.
>> >i really appreciate ur guess.
>> >Can u please explain what happens to a identity column & its values when
>> >rollback comes into picture?
>> Hi Sameer,
>> Since the identity value generation is performed outside of the
>> transaction context, the values generated for the inserts that were
>> rolled back are not re-used. See the repro below.
>> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
>> OtherCol int NOT NULL CHECK (OtherCol > 0));
>> -- First row is inserted
>> INSERT INTO Demo (OtherCol) VALUES (1);
>> -- Second row explicitly rolled back
>> BEGIN TRAN;
>> INSERT INTO Demo (OtherCol) VALUES (2);
>> ROLLBACK TRAN;
>> -- Third row implicitly rolled back because constraint is violated
>> INSERT INTO Demo (OtherCol) VALUES (-3);
>> -- Fourth row is inserted
>> INSERT INTO Demo (OtherCol) VALUES (4);
>> -- Check results
>> SELECT * FROM Demo;
>> DROP TABLE Demo;
>>
>> --
>> Hugo Kornelis, SQL Server MVP|||On Sat, 26 Aug 2006 10:21:02 -0700, Sameer wrote:
>How can i solve this identity increase problem ... as identity is not in
>context of transaction.
>Please suggest a solution to rollback identity with transaction.
>i'll be grateful to u.
Hi Sameer,
IDENTITY should never be used if the actuall values matter. It is
intended to be used as a surrogate key, for internal use by the database
logic but not exposed to the user.
If you care aboout gaps in your IDENTITY sequence, then you shouldn't
use IDENTITY at all. Find the current maximum value in the table, add
one and use that as your new value. Use locks to ensure that two
connections executing the same coode at the same time won't get the same
maximum value, then try to insert new rows with the same new value. Your
concurrency will suffer, but you're rid of gaps - until you manuallly
delete a row, of course.
--
Hugo Kornelis, SQL Server MVP|||Thanks Mr. Hugo, Mr. Hari & Mr. Roger for ur kind suggestions.
I am fully able diagnose what's happening. I just come out of a potential
turn around for the issue.
Thanks very much. I am grateful for ur prompt replies.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UK

IDENTITY column

Hi,
Do many people use the IDENTITY type for a column?
The advantage is that every time you add a row, that identity column is
automatically incremented, however, I have always wondered about the
following issues that I could try to test and find out, but my testing will
not necessarily reveil all there is to know that you might know off the top
of your heads.
1) can you manually insert an identity? for example, row 1, 2 and 3 already
exist and I manually INSERT a row with id 5, what will happen when identity
reaches 4 and wish to move to 5? will it skip 5 because it already exists,
overwrite it, or what?
2) What happens if identity reaches maximum? If INDENTITY is reset, while
rows already exist, what will happen to existing rows? See question 1.
3) identity keeps moving up.. if later rows are deleted, gaps are formed.
How can this be prevented? Is there a way to find most efficiently which
ID's are available?
So basically, imagine I have a table like this:
CREATE TABLE MyTable (
Id IDENTITY(1,1) PRIMARY KEY,
OtherData TEXT
)
GO
INSERT INTO MyTable(OtherData) VALUES ("test1")
INSERT INTO MyTable(OtherData) VALUES ("test2")
INSERT INTO MyTable(OtherData) VALUES ("test3")
INSERT INTO MyTable(OtherData) VALUES ("test4")
INSERT INTO MyTable(OtherData) VALUES ("test5")
DELETE FROM MyTable WHERE Id=2 OR Id=4
Now I wish to have a query that gives the available id's back (2 and 4 in
this case).
The problem is, how do you info about rows that do not exist?
Only way I can think of is get MAX(Id) and then loop through all values in
descending order until you find one that doesn't exist, testing each one.
And this can become very slow in case of many records, and can't be the way.
Any suggestions? How are IDENTITY columns used? Just keep letting it go up
until it runs out? I know this would take millions of records maybe and in
real life situations may never happen, but theoretically, it just doesn't
sound right.
rows will get deleted and added and at some point in the future, I will have
reached the ceiling because deleted id's are never reused.
Or is it much better to use a "FindFreeId" type of stored procedure or
whatever, wit or without an IDENTITY column?
LisaI just thought of a way that it might be done:
Find all consecutive rows where the difference between ID values is more
than 1.
Is there a better way?
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23lmHXdnXFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Do many people use the IDENTITY type for a column?
> The advantage is that every time you add a row, that identity column is
> automatically incremented, however, I have always wondered about the
> following issues that I could try to test and find out, but my testing
> will not necessarily reveil all there is to know that you might know off
> the top of your heads.
> 1) can you manually insert an identity? for example, row 1, 2 and 3
> already exist and I manually INSERT a row with id 5, what will happen when
> identity reaches 4 and wish to move to 5? will it skip 5 because it
> already exists, overwrite it, or what?
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
> rows already exist, what will happen to existing rows? See question 1.
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
> How can this be prevented? Is there a way to find most efficiently which
> ID's are available?
> So basically, imagine I have a table like this:
> CREATE TABLE MyTable (
> Id IDENTITY(1,1) PRIMARY KEY,
> OtherData TEXT
> )
> GO
> INSERT INTO MyTable(OtherData) VALUES ("test1")
> INSERT INTO MyTable(OtherData) VALUES ("test2")
> INSERT INTO MyTable(OtherData) VALUES ("test3")
> INSERT INTO MyTable(OtherData) VALUES ("test4")
> INSERT INTO MyTable(OtherData) VALUES ("test5")
> DELETE FROM MyTable WHERE Id=2 OR Id=4
> Now I wish to have a query that gives the available id's back (2 and 4 in
> this case).
> The problem is, how do you info about rows that do not exist?
> Only way I can think of is get MAX(Id) and then loop through all values in
> descending order until you find one that doesn't exist, testing each one.
> And this can become very slow in case of many records, and can't be the
> way.
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will
> have reached the ceiling because deleted id's are never reused.
> Or is it much better to use a "FindFreeId" type of stored procedure or
> whatever, wit or without an IDENTITY column?
> Lisa
>|||>> Do many people use the IDENTITY type for a column? <<
Lots of them, usually newbies who have not had a course in RDBMS so
they would understand what a relatioanl key is and still think in terms
of sequential files and records. Google some of my rants about this
design flaw.
Or talk to an old programmer who grew up with magnetic tape files which
had record numbers instead of relational keys. We had all those
problems with 1950's technology. Dijkstra was right; we keep
re-invetning the same errors over and over in this trade.|||Many opinions:
http://www.dbpd.com/vault/9805xtra.htm
http://www.sqlteam.com/forums/topic...136&whichpage=1
Types" target="_blank">http://www.ssw.com.au/SSW/Standards...ey
Types
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23lmHXdnXFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
> Do many people use the IDENTITY type for a column?
> The advantage is that every time you add a row, that identity column is
> automatically incremented, however, I have always wondered about the
> following issues that I could try to test and find out, but my testing
> will not necessarily reveil all there is to know that you might know off
> the top of your heads.
> 1) can you manually insert an identity? for example, row 1, 2 and 3
> already exist and I manually INSERT a row with id 5, what will happen when
> identity reaches 4 and wish to move to 5? will it skip 5 because it
> already exists, overwrite it, or what?
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
> rows already exist, what will happen to existing rows? See question 1.
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
> How can this be prevented? Is there a way to find most efficiently which
> ID's are available?
> So basically, imagine I have a table like this:
> CREATE TABLE MyTable (
> Id IDENTITY(1,1) PRIMARY KEY,
> OtherData TEXT
> )
> GO
> INSERT INTO MyTable(OtherData) VALUES ("test1")
> INSERT INTO MyTable(OtherData) VALUES ("test2")
> INSERT INTO MyTable(OtherData) VALUES ("test3")
> INSERT INTO MyTable(OtherData) VALUES ("test4")
> INSERT INTO MyTable(OtherData) VALUES ("test5")
> DELETE FROM MyTable WHERE Id=2 OR Id=4
> Now I wish to have a query that gives the available id's back (2 and 4 in
> this case).
> The problem is, how do you info about rows that do not exist?
> Only way I can think of is get MAX(Id) and then loop through all values in
> descending order until you find one that doesn't exist, testing each one.
> And this can become very slow in case of many records, and can't be the
> way.
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will
> have reached the ceiling because deleted id's are never reused.
> Or is it much better to use a "FindFreeId" type of stored procedure or
> whatever, wit or without an IDENTITY column?
> Lisa
>|||This is a bit of a holy issue.

> Do many people use the IDENTITY type for a column?
Yes much to the chagrin of purists.
<snip>

> 1) can you manually insert an identity? for example, row 1, 2 and 3 alread
y
> exist and I manually INSERT a row with id 5, what will happen when identit
y
> reaches 4 and wish to move to 5? will it skip 5 because it already exists,
> overwrite it, or what?
Yes. In SQL Server, you can use the Identity_Insert statement to temporarily
allow the forceful setting of an identity value. If the identity column is t
he
primary key, then even if Identity_Insert is enabled, duplicates will not be
allowed. Thus, this is one of the reasons that if you are making your identi
ty
column the primary key, you should explicitly do so.

> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
rows
> already exist, what will happen to existing rows? See question 1.
It will throw an error. However, this can be easily anticipated and handled
by
adjusting the data type of the column to something larger. It is highly doub
tful
you will reach the cap of a BigInt.

> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
How
> can this be prevented? Is there a way to find most efficiently which ID's
are
> available?
Gaps will form and you shouldn't care about it. Identity columns are not mea
nt
to provide sequence with no gaps. For that, you should use a different, cust
om
and intentional mechanism. Identity columns are simply a cheap and easy way
of
providing a unique identifier for each row.

> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will ha
ve
> reached the ceiling because deleted id's are never reused.
Purists would agree and thus do not recommend their use. People like Mr. Cel
ko
(and officially Codd) argue that the primary key should be an assemblage of
actual data values not some system generated key. However, since it is highl
y
unlikely that you will run out of key values, this reason alone should not b
e a
deterrent. If you thought it was even remotely possible to hit 2 billion row
s
(the maximum for an int) you can use a BigInt.
Thomas|||"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:Op23lUoXFHA.3864@.TK2MSFTNGP10.phx.gbl...
> This is a bit of a holy issue.
>
> Yes much to the chagrin of purists.
> <snip>
>
> Yes. In SQL Server, you can use the Identity_Insert statement to
> temporarily allow the forceful setting of an identity value. If the
> identity column is the primary key, then even if Identity_Insert is
> enabled, duplicates will not be allowed. Thus, this is one of the reasons
> that if you are making your identity column the primary key, you should
> explicitly do so.
Thomas,
Lisa might get the wrong impression from your answer to this part.
The answer to "Will it skip 5 because it already exists, overwrite it, or
what?" is this, I think:
If the value 5 is inserted "manually", and 5 is larger than the
next "automatic" identity value would have been, the identity
seed for this table will be reset as if 5 were an automatically
inserted value (6 will be inserted next, skipping any values
below 5 that were never generated). If 5 is smaller than
would otherwise have been inserted, the sequence of automatically
generated values will not be changed.
These rules apply whether or not the insert of the value 5 succeeds
or fails. It can fail because of a primary key or unique constraint
on the column if 5 is already a value in the column.
The Books Online topic SET IDENTITY_INSERT and the other
articles about the identity property explain this behavior.
SK
>
> It will throw an error. However, this can be easily anticipated and
> handled by adjusting the data type of the column to something larger. It
> is highly doubtful you will reach the cap of a BigInt.
>
> Gaps will form and you shouldn't care about it. Identity columns are not
> meant to provide sequence with no gaps. For that, you should use a
> different, custom and intentional mechanism. Identity columns are simply a
> cheap and easy way of providing a unique identifier for each row.
>
> Purists would agree and thus do not recommend their use. People like Mr.
> Celko (and officially Codd) argue that the primary key should be an
> assemblage of actual data values not some system generated key. However,
> since it is highly unlikely that you will run out of key values, this
> reason alone should not be a deterrent. If you thought it was even
> remotely possible to hit 2 billion rows (the maximum for an int) you can
> use a BigInt.
>
> Thomas
>|||Joe,
Do you know many "newbies" who have been doing this so long they "still
think in terms of sequential files and records?" ;-)
Lisa,
As you read more on this issue you'll see a debate between the "practical"
side and the "purist" side. Both views have valid points that should be
considered when making your design. When to use an Identity? That depends on
the requirements. But it's a NOT moral issue and it's neither always good,
or always bad. There is no perfect right answer. It just depends.
-Paul
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116731472.050155.78780@.o13g2000cwo.googlegroups.com...
> Lots of them, usually newbies who have not had a course in RDBMS so
> they would understand what a relatioanl key is and still think in terms
> of sequential files and records. Google some of my rants about this
> design flaw.
> Or talk to an old programmer who grew up with magnetic tape files which
> had record numbers instead of relational keys. We had all those
> problems with 1950's technology. Dijkstra was right; we keep
> re-invetning the same errors over and over in this trade.
>|||> Lisa might get the wrong impression from your answer to this part.
> The answer to "Will it skip 5 because it already exists, overwrite it, or
> what?" is this, I think:
> If the value 5 is inserted "manually", and 5 is larger than the
> next "automatic" identity value would have been, the identity
> seed for this table will be reset as if 5 were an automatically
> inserted value (6 will be inserted next, skipping any values
> below 5 that were never generated). If 5 is smaller than
> would otherwise have been inserted, the sequence of automatically
> generated values will not be changed.
> These rules apply whether or not the insert of the value 5 succeeds
> or fails. It can fail because of a primary key or unique constraint
> on the column if 5 is already a value in the column.
I think you're right about the interpretation of her question. Although, as
I
mentioned, perfect sequence and the ability to predict new values should not
be
a consideration if using an identity column.
Thomas|||"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:Op23lUoXFHA.3864@.TK2MSFTNGP10.phx.gbl...
> This is a bit of a holy issue.
Only for people who can't walk and chew gum at the same time.

> Yes much to the chagrin of purists.
> <snip>
>
already
identity
exists,
> Yes. In SQL Server, you can use the Identity_Insert statement to
temporarily
> allow the forceful setting of an identity value. If the identity column is
the
> primary key, then even if Identity_Insert is enabled, duplicates will not
be
> allowed. Thus, this is one of the reasons that if you are making your
identity
> column the primary key, you should explicitly do so.
>
while rows
> It will throw an error. However, this can be easily anticipated and
handled by
> adjusting the data type of the column to something larger. It is highly
doubtful
> you will reach the cap of a BigInt.
>
formed. How
ID's are
> Gaps will form and you shouldn't care about it. Identity columns are not
meant
> to provide sequence with no gaps. For that, you should use a different,
custom
> and intentional mechanism. Identity columns are simply a cheap and easy
way of
> providing a unique identifier for each row.
>
up
in
doesn't
have
> Purists would agree and thus do not recommend their use. People like Mr.
Celko
> (and officially Codd) argue that the primary key should be an assemblage
of
> actual data values not some system generated key. However, since it is
highly
> unlikely that you will run out of key values, this reason alone should not
be a
> deterrent. If you thought it was even remotely possible to hit 2 billion
rows
> (the maximum for an int) you can use a BigInt.
>
> Thomas
>

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
BevoI don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:
> I don't believe a trigger will work for you either Bevo. You are trying to
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> > Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> >
> > The application is trying to run this insert into the table:
> >
> > insert into X (MYID, MYVAR) values (null, 'test').
> >
> > This error is being returned:
> >
> > Cannot insert explicit value for identity column in table 'X' when
> > IDENTITY_INSERT is set to OFF.
> >
> >
> > I cannot modify the application code insert. Is there a way to configure
> the
> > IDENTITY to replace the null with the IDENTITY value and not error on the
> > attempt.
> >
> > I know a trigger can be written to do this, but I want to know if
> something
> > can be configured in the IDENTITY or TABLE.
> >
> > Thanks!!
> > Bevo
>
>|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.
> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.
> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.
> I cannot modify the application code insert.
!!' Even when it won't work? That seems a bizzare and unreasonable.
--
David Portas
SQL Server MVP
--|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
--
David Portas
SQL Server MVP
--|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
--
David Portas
SQL Server MVP
--

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
Bevo
I don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo
|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:

> I don't believe a trigger will work for you either Bevo. You are trying to
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> the
> something
>
>
|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.

> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.

> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.

> I cannot modify the application code insert.
!!? Even when it won't work? That seems a bizzare and unreasonable.
David Portas
SQL Server MVP
|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
David Portas
SQL Server MVP
|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
David Portas
SQL Server MVP
|||Why you ask. Well, the application is currently supported on MySQL and
Oracle, we are attempting to adopt SQL Server support.
There have been many issues which required application code and database
changes to support SQL Server. For example, the rowsize and index size limits
required changes. Reserved words, such as IDENTITY, and the lack of support
for key SQL functions, like CONCAT (posting on this as well). There are many
others...
This particular issue of the INSERT statement would require extensive
application changes, we view that solution as a last resort.
"David Portas" wrote:

> cannot
> So presumably something has changed since the application went into
> production (I'm assuming that it worked at one time)? What was changed and
> why? If you explain what you are actually trying to achieve then maybe
> someone can suggest some alternatives that won't break your application
> code. It appears that the problem isn't in the INSERT statement but
> elsewhere.
> --
> David Portas
> SQL Server MVP
> --
>
>

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
BevoI don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:

> I don't believe a trigger will work for you either Bevo. You are trying t
o
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> the
> something
>
>|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.

> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.

> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.

> I cannot modify the application code insert.
!!' Even when it won't work? That seems a bizzare and unreasonable.
David Portas
SQL Server MVP
--|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
David Portas
SQL Server MVP
--|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
David Portas
SQL Server MVP
--|||Why you ask. Well, the application is currently supported on mysql and
Oracle, we are attempting to adopt SQL Server support.
There have been many issues which required application code and database
changes to support SQL Server. For example, the rowsize and index size limit
s
required changes. Reserved words, such as IDENTITY, and the lack of support
for key SQL functions, like CONCAT (posting on this as well). There are many
others...
This particular issue of the INSERT statement would require extensive
application changes, we view that solution as a last resort.
"David Portas" wrote:

> cannot
> So presumably something has changed since the application went into
> production (I'm assuming that it worked at one time)? What was changed and
> why? If you explain what you are actually trying to achieve then maybe
> someone can suggest some alternatives that won't break your application
> code. It appears that the problem isn't in the INSERT statement but
> elsewhere.
> --
> David Portas
> SQL Server MVP
> --
>
>