How to Add a FILESTREAM Column to an Existing SQL Server Table

We will add a FILESTREAM column to a SQL Server table that was created using the following code:

CREATE TABLE [dbo].[FS_Table]
(
[Id] [int] NOT NULL,
)

A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property.
If you try add a FILESTREAM column to the table without having a nonnull unique column with the ROWGUIDCOL property, you will get the following error:

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

filestream_column_rowguidcol_error_alter_table

First add a nonnull unique column with the ROWGUIDCOL property to the table:

ALTER TABLE [dbo].[FS_Table] ADD [UI] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT (newid())

Now it is possible to add the FILESTREAM column to the table:

ALTER TABLE [dbo].[FS_Table] ADD [FS_Data] [varbinary](max) FILESTREAM NULL

Related articles:
How to Enable FILESTREAM on the SQL Server Instance
How to Enable FILESTREAM on an Existing SQL Server Database
How to Create a FILESTREAM Enabled SQL Server Database
How to Create a SQL Server Table with a FILESTREAM Column

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">