[SOLVED] What is the DDL keyword that creates index in Microsoft SQL Server?

What is the DDL keyword that creates index in Microsoft SQL Server?

I have a create table SQL statement with the following column:

status_id tinyint not null,

Is there any additional keyword to make it indexed column?

Solution

In answer to your question; yes, you can inline INDEX a column. The syntax is just like when you inline a CONSTRAINT:

CREATE TABLE dbo.MyTable (MyID int IDENTITY CONSTRAINT PK_MyTable PRIMARY KEY,
                          MyColumn varchar(30) NOT NULL,
                          MyStatus tinyint NOT NULL INDEX IX_MyTable_MyStatus,
                          MyDate date NOT NULL CONSTRAINT DF_MyTable_MyDate DEFAULT (SYSDATETIME()));

The only problem with this, however, is that the INDEX is only on MyStatus, so it could easily not be helpful.

Imagine a query like the following:

SELECT MyID,
       MyColumn,
       MyDate
FROM dbo.MyTable
WHERE MyStatus = 0;

The INDEX IX_MyTable_MyStatus is not going to be useful here. This is because the index includes none of the other columns returned in the SELECT (other than MyID, owing to it being implicitly included as it's the CLUSTERED index). At best, the index could be used to do a seek followed by a key lookup, but those get expensive quickly. It wouldn't be surprising to see a scan against the table (and that is what happens, see the linked fiddle at the end of this answer).

As such you would be much better off defining the INDEX at the end, and adding the appropriate INCLUDE:

CREATE TABLE dbo.MyTable (MyID int IDENTITY CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED,
                          MyColumn varchar(30) NOT NULL,
                          MyStatus tinyint NOT NULL,
                          MyDate date NOT NULL CONSTRAINT DF_MyTable_MyDate DEFAULT (SYSDATETIME()),
                          INDEX IX_MyTable_MyStatus (MyStatus) INCLUDE (MyColumn,MyDate));

db<>fiddle with query plans