How to create a file stream table on a file stream enabled database?

Problem: How to create a file stream table on a file stream enabled database?

Solution: Create a Table for Storing FILESTREAM Data Starting SQL Server 2008

When the database has a FILESTREAM filegroup, you can create or modify tables to store FILESTREAM data.  Files are stored in a VARBINARY(max) datatype can store the files up to 2GB of data.

There are two required columns that needs to be in the table.

·         To specify that a column contains FILESTREAM data, you create a varbinary(max) column and add the FILESTREAM attribute.

·         There should be unique row identifier and unique constraint on the table.

To know more about how to create a file stream on a database. Please go through this article. Create File Stream on a Database

Example:

In this Example, I would like to show we can create file stream table on a file stream enabled database.

IF NOT EXISTS (

                                SELECT 1

                                FROM information_schema.tables

                                WHERE table_name = N'DocumentFilestream'

                                                AND table_schema = N'dbo'

                                                AND table_type = 'Base table'

                                )

BEGIN

                CREATE TABLE [dbo].[DocumentFilestream]

(

                                [DocumentID] [int] NOT NULL

CONSTRAINT [PK_DocumentFilestream_DocumentID] PRIMARY KEY CLUSTERED,

                                [ROWID] [uniqueidentifier] ROWGUIDCOL NOT NULL

CONSTRAINT DF_DocumentFilestream_ROWID DEFAULT(NEWID())

CONSTRAINT Ux_Row_ROWID UNIQUE, -- needed for filestream

                                [Content] [varbinary](max) FILESTREAM NULL,

                                [FileType] [varchar](10) NULL,

                                [FileSize] [int] NULL

                                ) ON [PRIMARY] – you can keep this on a different file group as well.

                PRINT N'[dbo].[Document] Table Created'

END