Problem with IDENTITY column

Hi all

I have a problem with a table with an IDENTITY column. In order to illustrate my problem I have created a small table according to:

CREATE TABLE [dbo].[W_Person] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) NULL ,
[Age] [int] NULL ,
[OwnerID] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[W_Person] WITH NOCHECK ADD
CONSTRAINT [PK_W_Person] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

When I create two new records, the field ID is assigned the next available value.

So far everything works fine.

THE PROBLEM:
If I insert a record with the following SQL statements:

SET IDENTITY_INSERT W_Person ON

INSERT INTO W_Person (ID, Name, Age, OwnerID)
VALUES (8000, 'John Johnson', 30, 2)

SET IDENTITY_INSERT W_Person OFF

and then deletes the same record. As a next step I tries to insert a new record and let SQL Server generate a new id, the new id is assigned the value of 8001 instead of 3.

Please, can anybody help me on this one!

//Peterhi peterand,

the identity-seed is set to 8000 after your insert. so the next value will be 8001...
if you want to re-seed, take a look at bol "DBCC CHECKIDENT"

markus

Problem with Group By

I have the following query and it's result is not according to my expectation.

SELECT PaymentTerms, VendorCode , count(HeaderId) FROM Tbl_FPOHeader
GROUP BY PaymentTerms, VendorCode

Both PaymentTerms, VendorCode are NVarchar(50) and HeaderId is int type.

I want the result to be sorted by PaymentTerms, but the result is Vendorcode.

If i use Order By PaymentTerms it is working fine, but I would like to get by using group by only.

Please help.

Thank you.

HI,

data in entities is just an unordered set of data unless you use an order statement. So you will have to use an order by clause. if not you might or might not get the results (by accident :-) ) in the prefered order.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de