Error: A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property.

The Error

Msg 5505, Level 16, State 1, Line 31
A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property.

I got this error when I tried to create a table with FILESTREAM column and an integer column with primary key, after enabling FILESTREAM at the Server Instance.

CREATE TABLE MTB_Images_FileStream
(
    ImageID INT PRIMARY KEY,
    ImageStream VARBINARY(MAX) FILESTREAM
)

The Problem

As the error description says, while creating a table with a FILESTREAM column, the table should have a non-null unique column with the ROWGUIDCOL property. This is a mandatory requirement for creating a FILESTREAM table.

The Solution

The solution for this issue is to have a unique, non null, ROWGUIDCOL column instead of the INT PRIMARY KEY column or add it as an additional column. For my situation, I’ve replaced the primary key column with a uniqueidentifier not null ROWGUIDCOL unique default newid() column.

CREATE TABLE MTB_Images_FileStream
(
    ImageID uniqueidentifier not null ROWGUIDCOL unique default newid(),
    ImageStream VARBINARY(MAX) FILESTREAM
)

This solved the issue for me.

Reference

• More about creating a table for storing images and files using FILESTREAM.