problem with identity column value

Example:
create table test
(
id bigint identity(1000,1) NOT NULL,
x int
)
set IDENTITY_INSERT test on;
insert into test (id,x) values (10,1);
set IDENTITY_INSERT test off;
-- identity value is OK = 1000, but:
set IDENTITY_INSERT test on;
insert into test (id,x) values (2000,1);
set IDENTITY_INSERT test off;
-- identity value isn't OK = 2000, but I need identity value = 1000
-- I know solution, but it is to slow:
DECLARE @.id_seq bigint;
SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
set IDENTITY_INSERT test on;
insert into test (id, x) values (2000, 1);
set IDENTITY_INSERT test off;
DBCC CHECKIDENT ('test', RESEED, @.id_seq);
-- do you know some faster and better solution? Thankssilber wrote:
> Example:
> create table test
> (
> id bigint identity(1000,1) NOT NULL,
> x int
> )
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (10,1);
> set IDENTITY_INSERT test off;
> -- identity value is OK = 1000, but:
> set IDENTITY_INSERT test on;
> insert into test (id,x) values (2000,1);
> set IDENTITY_INSERT test off;
> -- identity value isn't OK = 2000, but I need identity value = 1000
> -- I know solution, but it is to slow:
> DECLARE @.id_seq bigint;
> SET @.id_seq = (select IDENT_CURRENT ( 'test' ));
> set IDENTITY_INSERT test on;
> insert into test (id, x) values (2000, 1);
> set IDENTITY_INSERT test off;
> DBCC CHECKIDENT ('test', RESEED, @.id_seq);
> -- do you know some faster and better solution? Thanks
I don't quite understand the problem. Apparently you want to reset the
current IDENTITY value after inserting some data with IDENTITY_INSERT
on. The only reason I can imagine performance would be in issue here is
if you had to issue DBCC CHECKIDENT on a frequent basis during
user-transactions. But if you had to do that on a frequent basis then
I'd say you would be better off not having the column as an IDENTITY at
all.
Alternatively, you could use only negatives for the IDENTITY_INSERT
values and use positives for the incrementing IDENTITY value. That way
the increment won't be affected.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Problem With Distinct clause

Hi

I have the following tables:

Suppliers:
SupplierID int not null primary key auto_increment
SubscriptionTypeID int not null
SubscriptionExpDate Date

Products:
ID int not null primary key auto_increment
SupplierID int not null as foreign key
ProductCode varchar(30) not null
productName varchar(255) not null
ImageID int not null as foreign key

Images:
ImageID int not null primary key auto_increment
Image blob not null

Whats is the correct SQL syntax to retrieve the disticnt Image and ProductName where ProductName is LIKE some user defined string and SubscriptionExpDate >= Date() order by SubscriptionTypeID DESC.

The above query will retrieve multiple records for the same Image if there are multiple suppliers for that product. How do I retrieve the distinct Image? The only other way i can think of solving this is to redesign the table and include the productName in the Images table. However all images are unique but product names supplied by the supplier for a particular image/product can be different. hence to increase the posibility of retrieving a match I have included it in the products Table!

Any help will be appreciated.what does "the distinct image" mean?

if a single product is supplied by multiple suppliers, and you have a query where you join product to suppliers, then why aren't you asking about returning "the distinct product" too?

that is where your difficulty lies ;)|||Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name. Hence I want to retrieve distinct name as well. But that is not possible becuase the product name for a given product is different for each supplier yet it is for the same product. i dont think there is any other way round this than the way I metioned. By including product name in the images table aswell!

You may be wondering what is the purpose of such a query. well it is to be implemented on a web page. The above query should display all items which match the search string but onyl display unique images and any one name. At present I get duplicate images and names per row.

If you have any other suggestions I would be glad to hear them.|||here's the important part of what you just said --Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name.so my question is, which one? the item with the shortest name? the supplier with the latest registration date?

to pick one from among many, you need a way to say which one

answers that are not allowed include "any one," "the first one," and "you pick one"

:)