question about retrieving identity value

hi,
according to the SQL Server Books Online, there're 3 system functions return
last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
...
BEGIN TRAN
...
INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
...
COMMIT TRAN
...
I think using IDENT_CURRENT may be a problem in multiuser environment,
because it returns the last identity value generated in any session. But he
said it's ok, since these statements are put in a transaction and SQL Server
will lock the table. Is it true?Hi
He is wrong. SQL Server may not lock the whole table, so it is possible that
2 processes can insert into the same table, at the same time.
SCOPE_IDENTITY is the correct one to use.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:72F8BDEC-FA0F-4880-B1E7-FFBDB8102955@.microsoft.com...
> hi,
> according to the SQL Server Books Online, there're 3 system functions
> return
> last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
> SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
> ...
> BEGIN TRAN
> ...
> INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
> SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
> ...
> COMMIT TRAN
> ...
> I think using IDENT_CURRENT may be a problem in multiuser environment,
> because it returns the last identity value generated in any session. But
> he
> said it's ok, since these statements are put in a transaction and SQL
> Server
> will lock the table. Is it true?

Question about OUTPUT clause

In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY.

Question: will this sql 2005 approach also provide the newly added identity value?

insert into TestTable -- ID column of testtable is an Identity field

output inserted.id, inserted.col1 into @.insertedRecords

values('row 20')

select ID from @.insertedRecords

TIA,

Barkingdog

.

Sure can. Did your attempts not work? Here is a script to demo:

create table testTable
(
testTableId int identity primary key,
value varchar(10) unique
)
go

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
values('row 20')

select scope_identity()
select * from @.insertedRows

go

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
select 'row 21'
union all
select 'row 22'

select * from @.insertedRows

|||

I haven't encoutnered any problems but I was just wondering if my original thought was correct.

Thanks.

Barkingdog

sql

Question about Identity generation and issues..

Has anyone run into this issue before?
I'm creating test scenarios doing Deletes/Updates/Inserts and after the test
scenario is completed need to remove any and all changes to the Database
that were made. The connection is made and many statements are run and all
are encapsulated in a single transaction. When all transactions are
completed a rollback is performed.
The problem arises here:
Table A has an identity column defined as the primary key on it.
A process (run in a .Net transaction) executes and performs an insert into
Table A to generate an Identity but before the transaction is completed the
process runs other statements using that generated value in other tables for
reference purposes. During the time these queries run, another instance of
the same process performs the same insert allowing the table to auto
generate its identity value. A certain percentage of the time, I'll get an
error whereby a duplicate primary key violation occurs.
Has anyone encountered anything like this and is there a way around it?
I've tried setting differing Isolation levels, minimizing the transaction to
only the section that is actually changing data, unique transaction
names...?
Thanks
DAre you using a column with IDENTITY property or are you generating the
sequencial value?. If you are generating it, can we see the code used?
AMB
"news" wrote:

>
> Has anyone run into this issue before?
>
> I'm creating test scenarios doing Deletes/Updates/Inserts and after the te
st
> scenario is completed need to remove any and all changes to the Database
> that were made. The connection is made and many statements are run and al
l
> are encapsulated in a single transaction. When all transactions are
> completed a rollback is performed.
>
> The problem arises here:
>
> Table A has an identity column defined as the primary key on it.
>
> A process (run in a .Net transaction) executes and performs an insert into
> Table A to generate an Identity but before the transaction is completed th
e
> process runs other statements using that generated value in other tables f
or
> reference purposes. During the time these queries run, another instance o
f
> the same process performs the same insert allowing the table to auto
> generate its identity value. A certain percentage of the time, I'll get a
n
> error whereby a duplicate primary key violation occurs.
>
> Has anyone encountered anything like this and is there a way around it?
> I've tried setting differing Isolation levels, minimizing the transaction
to
> only the section that is actually changing data, unique transaction
> names...?
>
> Thanks
>
> D
>
>|||The column has been defined as an INT IDENTITY(1,1) NOT NULL
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E07EDB1D-6F14-4F87-9D77-13E58115A473@.microsoft.com...
> Are you using a column with IDENTITY property or are you generating the
> sequencial value?. If you are generating it, can we see the code used?
>
> AMB
> "news" wrote:
>
test
all
into
the
for
of
an
transaction to|||Is there a way to reproduce the problem in our computer?
AMB
"news" wrote:

> The column has been defined as an INT IDENTITY(1,1) NOT NULL
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:E07EDB1D-6F14-4F87-9D77-13E58115A473@.microsoft.com...
> test
> all
> into
> the
> for
> of
> an
> transaction to
>
>|||>> The problem arises here: Table A has an identity column defined as
the primary key on it. <<
That is a problem! You have hired someone who does not know better
than use IDENTITY as a key in a schema. The right thing to do is
re-design that table with a proper relational key. This is a much
better idea than any of the kludges you are going to be given.|||Instead claiming that the sky is falling like senor Joe and give up identity
columns, there are a couple of more practical ideas. The most important
solution is to reproduce the problem. What version of SQL Server? How many
records in the database? How hard is the table being hit? What do the
inserts look like?
For example, one solution would be a simple retry mechanism (which btw, Joe,
would be needed with any sort of sequencing mechanism).
Thomas
"news" <DavidP> wrote in message
news:ugrawntOFHA.2144@.TK2MSFTNGP09.phx.gbl...
>
> Has anyone run into this issue before?
>
> I'm creating test scenarios doing Deletes/Updates/Inserts and after the
> test
> scenario is completed need to remove any and all changes to the Database
> that were made. The connection is made and many statements are run and
> all
> are encapsulated in a single transaction. When all transactions are
> completed a rollback is performed.
>
> The problem arises here:
>
> Table A has an identity column defined as the primary key on it.
>
> A process (run in a .Net transaction) executes and performs an insert into
> Table A to generate an Identity but before the transaction is completed
> the
> process runs other statements using that generated value in other tables
> for
> reference purposes. During the time these queries run, another instance
> of
> the same process performs the same insert allowing the table to auto
> generate its identity value. A certain percentage of the time, I'll get
> an
> error whereby a duplicate primary key violation occurs.
>
> Has anyone encountered anything like this and is there a way around it?
> I've tried setting differing Isolation levels, minimizing the transaction
> to
> only the section that is actually changing data, unique transaction
> names...?
>
> Thanks
>
> D
>|||In a schema where you have several branches to a snowflake describing say a
insurance distributor model, I needed a way to create a unique identifier
for each instance without having a three part key to carry to each relating
table.
The server is SQL Server 2000 with latest service packs and patches et al.
The table is being hit pretty hard as this data is being loaded to load up
the insurance distributor plans and such.
Currently the table has only in the neighborhood of 100039484 records
Inserts are simple inserts where there is one value set being put in i.e.:
INSERT INTO InsuranceDistributorPlan
(DistributorID, PlanPeriodId, PlanTypeID, Name, Source)
-- VALUES (-90000, -2004, 1, 'Davids test category', 4);
SELECT -90000, -2004, 1, 'Jeffs test category', 4
This creates and unique identifier for me to then relate other data items
to.
This type of query is being run from a .Net application (explicit
transactions don't appear to affect the issue).
Identities are reset when a rollback occurs so why would this issue arrise?
"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:%23X7fSKyOFHA.1932@.tk2msftngp13.phx.gbl...
> Instead claiming that the sky is falling like senor Joe and give up
identity
> columns, there are a couple of more practical ideas. The most important
> solution is to reproduce the problem. What version of SQL Server? How many
> records in the database? How hard is the table being hit? What do the
> inserts look like?
> For example, one solution would be a simple retry mechanism (which btw,
Joe,
> would be needed with any sort of sequencing mechanism).
>
> Thomas
>
> "news" <DavidP> wrote in message
> news:ugrawntOFHA.2144@.TK2MSFTNGP09.phx.gbl...
into
transaction
>|||I presume that SQL has been patched to service pack 3a? What
indexes are on the table? Script them using the QA and post
them to them to group if you can. (Change the column names
and/or index names if you like)
There is a knowledge base article on an identity problem,
however it is old and has presumably been fixed in one of
the service packs. (http://tinyurl.com/46yme)
Thomas
"news" <DavidP> wrote in message
news:%23P$Hwh5OFHA.3512@.TK2MSFTNGP15.phx.gbl...
> In a schema where you have several branches to a snowflake
> describing say a
> insurance distributor model, I needed a way to create a
> unique identifier
> for each instance without having a three part key to carry
> to each relating
> table.
> The server is SQL Server 2000 with latest service packs
> and patches et al.
> The table is being hit pretty hard as this data is being
> loaded to load up
> the insurance distributor plans and such.
> Currently the table has only in the neighborhood of
> 100039484 records
> Inserts are simple inserts where there is one value set
> being put in i.e.:
> INSERT INTO InsuranceDistributorPlan
> (DistributorID, PlanPeriodId, PlanTypeID, Name, Source)
> -- VALUES (-90000, -2004, 1, 'Davids test category',
> 4);
> SELECT -90000, -2004, 1, 'Jeffs test category', 4
> This creates and unique identifier for me to then relate
> other data items
> to.
> This type of query is being run from a .Net application
> (explicit
> transactions don't appear to affect the issue).
> Identities are reset when a rollback occurs so why would
> this issue arrise?
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:%23X7fSKyOFHA.1932@.tk2msftngp13.phx.gbl...
> identity
> Joe,
> into
> transaction
>|||On Thu, 7 Apr 2005 10:36:42 -0700, news wrote:
(snip)
>Identities are reset when a rollback occurs
(snip)
Hi news,
While I must admit that I don't really understand the proble you
describe, I do know that this statement is incorrect. A rollback will
not reset the identity.
CREATE TABLE Test (Ident int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Descr varchar(60) NOT NULL)
go
INSERT Test (Descr)
VALUES ('First row')
go
BEGIN TRANSACTION
INSERT Test (Descr)
VALUES ('Second row - will disappear after rollback')
SELECT * FROM Test
ROLLBACK TRANSACTION
go
INSERT Test (Descr)
VALUES ('Third row, to prove that IDENTITY value 2 is not reused')
SELECT * FROM Test
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||sorry, that was a typo..
My statement was meant to say that
"Identities aren't reset"
Thanks,
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:am2b51t2qil494ud2uc4nsmrd7glqe0d4m@.
4ax.com...
> On Thu, 7 Apr 2005 10:36:42 -0700, news wrote:
> (snip)
> (snip)
> Hi news,
> While I must admit that I don't really understand the proble you
> describe, I do know that this statement is incorrect. A rollback will
> not reset the identity.
> CREATE TABLE Test (Ident int IDENTITY(1,1) NOT NULL PRIMARY KEY,
> Descr varchar(60) NOT NULL)
> go
> INSERT Test (Descr)
> VALUES ('First row')
> go
> BEGIN TRANSACTION
> INSERT Test (Descr)
> VALUES ('Second row - will disappear after rollback')
> SELECT * FROM Test
> ROLLBACK TRANSACTION
> go
> INSERT Test (Descr)
> VALUES ('Third row, to prove that IDENTITY value 2 is not reused')
> SELECT * FROM Test
> go
> DROP TABLE Test
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Question about IDENTITY columns.

We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
This table is present in 2 databases Db1 and Db2. We want to keep them
both in sync daily. The mapping of Name -> ID should be the same in
both the tables. We allow only Db1 tables to be updated. So we would
like to have a program that daily truncates [Db2].[TAB1] and creates
and executes a DTS package to transfer the table from DB1 to DB2.
My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
will get copied to DB2. If we delete the row with ID 3, will the next
syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
the table as 1,2,4,5?
Thanks
YashIf you create the field in table 2 as an identity field you will have
1,2,3,4 rather than 1,2,4,5 when trucating and re-populating. You could use
a table trigger to keep both tables in sync.
groutme in SO Cal
groutme_alternate@.sbcglobal.net
<yashgt@.yahoo.com> wrote in message
news:1109006625.544445.159200@.c13g2000cwb.googlegroups.com...
> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
It will copy 1, 2, 4,5. But you have to set the property "Enable Identity
insert" in the options tab of the "Transform Data Task" properties, in order
to allow explicit values to be inserted into the identity column.
AMB
"yashgt@.yahoo.com" wrote:

> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||>> If we delete the row with ID 3, will the next syncing execution cause the
There is an option in DTS which allow identity inserts under the
transformation section. When this is off the values are inserted without
generting new ones. Otherwise the new values will be generated in the
sequence based on the seed and increment set on the identity column for the
table.
Anith|||If the data in DB2 gets replaced every day then what's the point of giving i
t
an IDENTITY column? Make it a regular numeric column so that you have full
control over what values go in there.
David Portas
SQL Server MVP
--

Question about IDENTITY

Just a quick question.

I have a table with an IDENTITY field. I inserted a record using the is 1024 so as to distinguish the record. There are only about 40 records in the table, including id 1024.

What I need is for the IDENTITY to go back to 39 and start it's count from there to fill the gap up to 1024. How is this done?

Delete the record with id 1024 then edit the table and set the "Identity Seed" to 39. You could probably write an alter table statement to handle this or go into enterprise manager and design the table.

Why can't the ID's continue after 1024? Other than looks it shouldn't matter as long as the ID's are unique.

-- LZ

|||

True. It doesn't matter, the IDs are unique, and 1024 serves it purpose...
For the most part, I am just trying to get a better handle on IDENTITY and the various ways to control it.

I only have access to the database through code (and a form that I hid on the server that allows me to insert SQL statements), and I am just trying to make sure I have as much control over the data as I would if I had direct access to it.

Thanks for your input...

Question about getting the latest identity field in a specific table

Dear All,

Suppose in the program a record is added to a table whose
primary key is a identity field. If I really want to get the lastest
value for that field after the insertion, is it the best way to use
IDENT_CURRENT() to obtain this value?

Thanks for your kind attention

Yours faithfully,
BennyI would rather use SCOPE_IDENTITY() or @.@.IDENTITY depending on the
requirements. SQL Server Books Online states that, IDENT_CURRENT is similar
to the 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three
functions return last-generated identity values. However, the scope and
session on which 'last' is defined in each of these functions differ.

- IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
- @.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.

--
- Anith
( Please reply to newsgroups only )|||Anith Sen (anith@.bizdatasolutions.com) writes:
> - IDENT_CURRENT returns the last identity value generated for a specific
> table in any session and any scope.

One important thing to clarify here is that IDENT_CURRENT() can be affected
by insertions by other processes, where as scope_identity and @.@.identity
cannot.

Thus, ident_current() is rarely the function you should call in application
code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

question about DBCC checkident

Hi all,
In our application we use a special table(only 2 columns, one of which
is identity) to generate unique keys to use in our client application.One of
my recent requests was to create a procedure that would reserve a set of
keys in the table and return it to client.
The procedure I wrote:
1. Inserts a new row into the table to get the current identity
2. execute dbcc checkident with reseed parameter and the
blocksize+current identity.
3. Another insert into the table to ensure the identity is reset
properly. (I added this step only because in testing I found that this makes
identity setup work correctly).
I have included the code for the procedure at the end of the message.
Now this procedure works fine for a single user. However in multiuser
scenario with more than 100 users running this procedure concurrently,
application server has started crashing.
While trying to simulate this problem, I created a batch process that runs
125 concurrent processes running this procedure. I found something strange
in this. One of things I observed is that the sessions that successfully
run, show the following dbcc output:
"C:\CBORD\split tables>osql -E -S APK -d cbord -n -i"test_blockinsert.sql"
Checking identity information: current identity value '153757', current
column value '153906'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
But some sessions do not report this messages and I think this sessions are
failing the DBCC CheckIdent call siliently. There are no error messages in
sql server error log.
Has anyone seen or experienced this before. Is running DBCC checkident for
such a high number of concurrent users very bad?
Thanx, Amol.
ALTER procedure getnextkey_range(@.as_tablename varchar(128),@.ai_blockSize
integer,@.al_startkey integer output)
as
begin
declare @.ls_revision varchar(40);
declare @.ls_msgprefix varchar(100);
declare @.ls_sql varchar(1024);
declare @.li_range_end integer;
set @.ls_revision='$Revision: 1.7 $';
set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey with
(tablockx) (dummyvalue) values (1)';
execute(@.ls_sql);
set @.al_startkey=@.@.identity;
set @.li_range_end = @.al_startkey + @.ai_blockSize - 1; -- -1 to account for
the previous insert;
set @.ls_sql = 'dbcc checkident (''' + rtrim(ltrim(@.as_tablename)) +
'_nextkey'',reseed,' + cast(@.li_range_end as varchar(8)) + ')';
execute (@.ls_sql)
set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey
(dummyvalue) values (' + cast(@.li_range_end as varchar(8)) + ')';
execute(@.ls_sql);
endDon't do it like that. You can create a simple table and sp that will allow
you to get the next ID for a specific table very easily without using
Identities. Have a look at this example:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Amol" <apk@.nospam.cbord.com> wrote in message
news:OxXVhERHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> In our application we use a special table(only 2 columns, one of which
> is identity) to generate unique keys to use in our client application.One
> of my recent requests was to create a procedure that would reserve a set
> of keys in the table and return it to client.
> The procedure I wrote:
> 1. Inserts a new row into the table to get the current identity
> 2. execute dbcc checkident with reseed parameter and the
> blocksize+current identity.
> 3. Another insert into the table to ensure the identity is reset
> properly. (I added this step only because in testing I found that this
> makes identity setup work correctly).
> I have included the code for the procedure at the end of the message.
> Now this procedure works fine for a single user. However in multiuser
> scenario with more than 100 users running this procedure concurrently,
> application server has started crashing.
> While trying to simulate this problem, I created a batch process that runs
> 125 concurrent processes running this procedure. I found something strange
> in this. One of things I observed is that the sessions that successfully
> run, show the following dbcc output:
> "C:\CBORD\split tables>osql -E -S APK -d cbord -n -i"test_blockinsert.sql"
> Checking identity information: current identity value '153757', current
> column value '153906'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
>
> But some sessions do not report this messages and I think this sessions
> are failing the DBCC CheckIdent call siliently. There are no error
> messages in sql server error log.
> Has anyone seen or experienced this before. Is running DBCC checkident for
> such a high number of concurrent users very bad?
> Thanx, Amol.
>
>
> ALTER procedure getnextkey_range(@.as_tablename varchar(128),@.ai_blockSize
> integer,@.al_startkey integer output)
> as
> begin
> declare @.ls_revision varchar(40);
> declare @.ls_msgprefix varchar(100);
> declare @.ls_sql varchar(1024);
> declare @.li_range_end integer;
> set @.ls_revision='$Revision: 1.7 $';
> set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey with
> (tablockx) (dummyvalue) values (1)';
> execute(@.ls_sql);
> set @.al_startkey=@.@.identity;
> set @.li_range_end = @.al_startkey + @.ai_blockSize - 1; -- -1 to account
> for the previous insert;
> set @.ls_sql = 'dbcc checkident (''' + rtrim(ltrim(@.as_tablename)) +
> '_nextkey'',reseed,' + cast(@.li_range_end as varchar(8)) + ')';
> execute (@.ls_sql)
> set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey
> (dummyvalue) values (' + cast(@.li_range_end as varchar(8)) + ')';
> execute(@.ls_sql);
> end
>
>

Question about data type sqlserver ?

I have two problem :

+ The first, This is table store all items in bookshop system

tblItems:
IDItem Identity(Auto number)
Namebook nvarchar2
Price nvarchar2
Chapters nvarchar2
Weight nvarchar2 (weight of book)
....
I design data type for Chapter,or Price,Weight is nvarchar2 ? <--I wrong ? (I want to refer to principle of design the database)

+ The second ,When i design Price is the int datatype ! The default value is 0 ( I don't want to have this value ,i want to it is a empty field )

I really sorry because i ask too much ! Because i am a new programming !

Any Help or Advice would like appreciately ! Thanks u !

In my opinion you should use these datatypes:
IDItem Identity(Auto number)
Namebook nvarchar2
Price decimal
Chapters integer
Weight decimal orinteger depending upon the unit of measure
I don't understand what you are saying about Price and the integerdatatype and having a default value of 0. Unless you do somethingspecial SQL Server will insert a NULL into that field if you haven'tspecified a value.

|||

Thank tomorton very much !
I really don't understand what happen to me !

But I design again table ,everything is good !

Question about converting bigint field to int field

We made a poor decision a long time ago when designing our database
structure. We used bigint data types as the identity keys for many of
our base tables. For many reasons I would like to change these fields
to int at the largest. The largest data in these fields is around
200,000. I know that int can easily store this.

What should I be worried about when changing these fields from bigint
to int? If anything. Your help is appreciated. I did several
searches without much luck.I think you've covered the one biggy - make sure your existing data will
fit!

Others...

a) Make sure anything you join with are the same type, basically make
sure you change it everywhere including your foreign keys table.
b) Remember to do the stored procedures, udfs, triggers that may use them
as parameter.
c) You'll need to drop any constraints on your column definied with the
identity property, see example problem...

drop table t

create table t (
mycol bigint identity primary key,
t char(1) )

insert t ( t) values( 'a' )

alter table t alter column mycol int not null

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<mamorgan1@.gmail.com> wrote in message
news:1137768775.133857.115810@.g44g2000cwa.googlegr oups.com...
> We made a poor decision a long time ago when designing our database
> structure. We used bigint data types as the identity keys for many of
> our base tables. For many reasons I would like to change these fields
> to int at the largest. The largest data in these fields is around
> 200,000. I know that int can easily store this.
> What should I be worried about when changing these fields from bigint
> to int? If anything. Your help is appreciated. I did several
> searches without much luck.|||Just curious... What problems are there with having bigint as an
identity column?|||There aren't any problems - it works just fine.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"pb648174" <google@.webpaul.net> wrote in message
news:1138052526.544803.80680@.z14g2000cwz.googlegro ups.com...
> Just curious... What problems are there with having bigint as an
> identity column?|||I think its because
Bigint takes 8 bytes storage and Int takes 4 bytes.
SQL Server will not automatically promote other integer data types
(tinyint, smallint, and int) to bigint.

Regards
Amish Shah|||> What problems are there with having bigint as an identity column?

extra storage space causing slower performance of everything

Question about BCP

I have a data file that I am trying to BCP IN into a table. The table has an IDENTITY column as PK.

If the BCP process fails in between (there would be about 500,000 records in the data file) and I restart the process, would the records be overwritten into the table or deleted and re-isnerted if the record already exists? I noticed that it does not create duplicates. So either its over writing the existing records or ignoring them and inserting the new records. I did not find any documenttion regarding this in BOL.

Thanks.

I did a test, found that BCP always tries to append data from file to table. You can use-bbatch_size to specifies the number of rows per batch of data copied. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is copied in one batch.

And there is a -E switch, which decides whether using IDENTITY values from file, or generate a new unique IDENTITY. So when you use BCP with -E option to import data from file that may bring duplicate key values, an error will be raised saying vilation primary key constraint. You can take a look at this SQL SDK article:

http://msdn.microsoft.com/library/en-us/coprompt/cp_bcp_61et.asp?frame=true

|||

I do use the -E option. I have a stored proc that I run from multiple Query Analyzer windows parallely to transfer the data faster.

Here's how the command builds up:

SET @.str = 'bcp ' + @.db + '.dbo.' + @.table + ' in "' + @.Fileloc + '" -f"C:\mount\backup21\DocPhrase.fmt" -S' + @.server + ' -T -E '

When I run multiple processes, occassionally one of them fails with locking issue. So I re-run the stored proc. I noticed it doesnt complain about existing records. And there are no duplicates too if I run the bcp in multiple times. So I was wondering whether BCP ignores if the record already exists in the table or overwrites it.

|||

ndinakar:

When I run multiple processes, occassionally one of them fails with locking issue. So I re-run the stored proc. I noticed it doesnt complain about existing records. And there are no duplicates too if I run the bcp in multiple times. So I was wondering whether BCP ignores if the record already exists in the table or overwrites it.

Really strange, in my testing BCP always tried to append rows, not ignore, nor overwritel; and if run same bcp multiple times with -E option, the duplicates vilation error will be raised. I use Profiler to trace SQL server, and found actually BCP calls 'insert bulk' command, not update.

Are you sure your identify column is primary key of the table? How about add a hint to the bcp command as:

-h CHECK_CONSTRAINT

Or you canget the Profiler trace to see what happens why the BCP runs to your SQL server.

|||

Yes you are right. It does throw an error if I try to BCP in the same file again if the BCP in was successful the first time.

I believe what was happening in mi situation was, since the entire bcp in is treated as one transaction, if for some locking reason it fails none of the records are inserted. That is why when I re run the process, it does not complain and nicely inserts the records. The records were never in in the first place.

|||It should be as you say, something related to sql transaction in BCP. And there is a-bbatch_size option that can be used to specifies the number of rows per batch of data copied. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch.