SQL Server 2017 Administrator's Guide

Creating tables with indexes

The basic syntax for creating tables is almost the same, but we have to consider some extras:

• Index is a part of table creation
• Clustered indexes are not allowed

In other words, we are creating indexes together with CREATE TABLE or ALTER TABLE statements.

In earlier versions of SQL Server, the ALTER TABLE statement was not allowed in an In-Memory environment. It was introduced in SQL Server 2016.

The syntax for creating a table is as follows:

create table Ratings_InMem(Id int not null identity primary key nonclustered, UserId int not null, MovieId int not null, Rating tinyint not null, index ix_Users (UserId))with(memory_optimized = on, durability = schema_and_data)

As seen in the script example, the base syntax ...