PDA

View Full Version : BUG: Loss of sequence numbering in identification fields



sandrobrz
21-Jan-2015, 07:44 AM
Testing the DataFlex, I created two tables in sql server, which has a bigint field as id and seed 1-1.

In the Password field in sql left as not allow null and data dictionary not activated the option of required.

When inserting a new record with no password, I get the message CK through the sql.
8596

By correcting to save the record, a number of id is skipped.

8597

Martin Moleman
21-Jan-2015, 10:43 AM
Could you make a Create Table script in SQL management Studio and post here? Also the <table>.int file.

Larry R Pint
21-Jan-2015, 10:51 AM
IIRC that's the way the autoincrementing identity fields in SQL work. If a save fails, the assigned ID is not reused. I'm guessing this is to help with multiple simultaneous saves from multiple sources.

I just needed a unique ID for each record, so I didn't care if they were actually sequential or not and didn't worry about it.

Is there some reason you need them to be sequential with no missing numbers? It is more "audit-able" with no missing numbers. (i.e. a missing number would indicate something deleted after the fact and be suspicious.)

Stephen W. Meeley
21-Jan-2015, 11:03 AM
When I read your report my reaction was the same as Larry's - it sounds logical (even if not optimal) that it would work that way. The assignment of the ID needs to happen right before the save, which uses that value, and the save fails for any reason, the value get's discarded.

This conversation seems to confirm that (you have to read between the lines a bit because they discuss things like reseeding and changing the increment value)...

http://stackoverflow.com/questions/2828936/does-sql-server-guarantee-sequential-inserting-of-an-identity-column

sandrobrz
21-Jan-2015, 01:38 PM
It's just that, whether the record was excluded because when the failure in the numbering the impression is that it has been deleted.

sandrobrz
21-Jan-2015, 01:40 PM
So it's not a bug but a feature of SQL Server from as seed identification field is guaranteed.


Thank you all.

sandrobrz
21-Jan-2015, 01:47 PM
files int.

DRIVER_NAME MSSQLDRV
SERVER_NAME SERVER=SQL2014;UID=SA;PWD=s1s2s3s@;DATABASE=elojaf acil
DATABASE_NAME elf_usuario
SCHEMA_NAME dbo


RECNUM_TABLE NO
PRIMARY_INDEX 1
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE NO


FIELD_NUMBER 1
FIELD_INDEX 1


FIELD_NUMBER 2
FIELD_INDEX 2
FIELD_RELATED_FILE 1
FIELD_RELATED_FIELD 1


INDEX_NUMBER 1
INDEX_NAME PK_elf_usuario


INDEX_NUMBER 2
INDEX_NAME elf_usuario002


DRIVER_NAME MSSQLDRV
SERVER_NAME SERVER=SQL2014;UID=SA;PWD=s1s2s3s@;DATABASE=elojaf acil
DATABASE_NAME elf_cliente
SCHEMA_NAME dbo


RECNUM_TABLE NO
PRIMARY_INDEX 1
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE NO


FIELD_NUMBER 1
FIELD_INDEX 1


FIELD_NUMBER 2
FIELD_INDEX 2


INDEX_NUMBER 1
INDEX_NAME PK_elf_cliente


INDEX_NUMBER 2
INDEX_NAME elf_cliente002




Script files from sql.

USE [elojafacil]
GO


/****** Object: Table [dbo].[elf_cliente] Script Date: 21/01/2015 16:44:13 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


SET ANSI_PADDING ON
GO


CREATE TABLE [dbo].[elf_cliente](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[login] [varchar](30) NOT NULL,
[senha] [nchar](30) NOT NULL,
[ativo] [smallint] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_elf_cliente] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


GO


SET ANSI_PADDING OFF
GO






USE [elojafacil]
GO


/****** Object: Table [dbo].[elf_usuario] Script Date: 21/01/2015 16:47:15 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[elf_usuario](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[elf_cliente] [bigint] NOT NULL,
[login] [nchar](30) NOT NULL,
[senha] [nchar](30) NOT NULL,
[ativo] [tinyint] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_elf_usuario] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


GO


ALTER TABLE [dbo].[elf_usuario] WITH CHECK ADD CONSTRAINT [FK_elf_usuario_elf_cliente] FOREIGN KEY([elf_cliente])
REFERENCES [dbo].[elf_cliente] ([id])
GO


ALTER TABLE [dbo].[elf_usuario] CHECK CONSTRAINT [FK_elf_usuario_elf_cliente]
GO

chuckatkinson
21-Jan-2015, 02:23 PM
Just a note - you should not post anything like UID and PWD data in a forum posting. You might want to edit your post and remove this ... Just saying.