How to Add an Identity Column to SQL Server Database Design

An identity column is a special type of column in SQL Server that automatically generates sequential values for each row in a table. This can be useful for creating unique identifiers, such as primary keys.

To create an identity column, you need to specify the IDENTITY property in the column definition, along with the starting value (seed) and the increment value. For example, the following statement creates a table called Customer with an identity column called id:

CREATE TABLE [Customer](
  id int NOT NULL IDENTITY(1,1),
  name varchar(255),
  email varchar(255),
  modified datetime DEFAULT (getDate()),
  CONSTRAINT customer_pkey PRIMARY KEY(id)
)
GO

In Luna Modeler database modeling tool, you can define the table structure visually, and add the IDENTITY(1,1) definition to the After Script section. See the screenshot below:

The IDENTITY(1,1) property means that the id column will start from 1 and increase by 1 for each new row.

You can also use negative values for the increment to create a descending sequence. For example, IDENTITY(100,-1) will start from 100 and decrease by 1 for each new row.

Datatype Alias

If you want to work with identity columns more comfortably, you can create a Datatype alias in Luna Modeler.

You add this object type to the diagram by clicking the Other button on the main toolbar or from the diagram context menu.

For the newly created object set the name, e.g. autoincrement and write int IDENTITY(1,1) to the definition field.

You can then use your autoincrement datatype alias in a similar way to the classic datatype.

Benefits of using datatype aliases in Luna Modeler

• You can easily recognize the identity column in the diagram because the name of the data type is not int, but a name you define, in this case autoincrement.
• You can easily navigate to a table/column using the Find function.

#