Creating table with ID column primary key with auto increment

Question

Creating table with ID column primary key with auto increment

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

I'm using SQL Server Management and I'm trying to create a table that has the ID field that must be auto increment. However, I can not use the IDENTITY property, but do everything in script , in the same hand.

I would like to know how I can do this, since (for example) this does not work (because it's for MySQL):

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

sql-server

asked by anonymous 13.03.2017 / 20:54

2 answers

1

The build script for SQL Server looks like this:

CREATE TABLE dbo.Persons
    (
    ID int NOT NULL IDENTITY (1, 1),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NULL,
    Age int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Persons ADD CONSTRAINT
    PK_Persons PRIMARY KEY CLUSTERED 
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Persons SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

13.03.2017 / 21:05

How to set up menu and sub menu? What command to list files starting with "a" and ending with "v"?

score 3 · Accepted Answer

A solution to this problem is to create a function that will return the next value of your PK, follow an example below as if it were a Identity that adds the value of 1 in 1

Table

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Function to generate your customized Identity

CREATE FUNCTION dbo.ProximoIdPersons() 
RETURNS int 
AS 
BEGIN 
    DECLARE @lastval int 
    SET @lastval = (select max(ID) from Persons) 
    IF @lastval is null SET @lastval = 0
    return @lastval + 1
END

Insert you would run

INSERT INTO Persons (ID, LastName, FirstName, Age)
VALUES (dbo.ProximoIdPersons(),'Almeida', 'Jeferson', 27)