Autoincrement field on SQl table

RIAS
RIAS used Ask the Experts™
on
Hello,
I have a field 'Ref' set to autonumber but it does not increment with addition of a new record.

CREATE TABLE [dbo].[tABLE1](

	
	[Ref] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Vece] PRIMARY KEY CLUSTERED 
(
		[Ref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Open in new window


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
With the table structure provided, it seems it has only 1 column, add few more columns and insert values for those columns so that Ref value will get auto incremented using Identity property..

Author

Commented:
I have few more columns , changed the query for  example
HuaMin ChenProblem resolver

Commented:
Show us the definition of your current table.

Did you get any successful message after having inserted the records?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior Developer
Commented:
E.g.

CREATE TABLE dbo.tABLE1
    (
        Ref INT IDENTITY(1, 1) NOT NULL ,
        Payload INT NOT NULL ,
        CONSTRAINT PK_Vece
            PRIMARY KEY CLUSTERED ( Ref ASC )
    );
GO

INSERT INTO dbo.tABLE1 ( Payload )
VALUES ( 11 ) ,
       ( 22 ) ,
       ( 33 );

SELECT *
FROM   dbo.tABLE1 TA;
GO

DROP TABLE dbo.tABLE1;
GO

Open in new window

Author

Commented:
mY QUERY ;

CREATE TABLE [dbo].[TABLE1](
	[Registration] [nvarchar](255) NOT NULL,
	[Fault] [nvarchar](255) NULL,	
	[Ref] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED 
(
	[Registration] ASC,
	[Ref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Open in new window

Author

Commented:
Thanks
ste5anSenior Developer

Commented:
Just a comment:

[Fault] being null-able makes no sense from the viewpoint of a relational data model.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial