I have a table on an existing SQL Server 2014 database as follows:
CREATE TABLE [dbo].[Files](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Content] [varbinary](max) NULL,
[Created] [datetime2](7) NOT NULL,
[Flag] [nvarchar](100) NULL,
[Key] [uniqueidentifier] NOT NULL,
[MimeType] [nvarchar](400) NOT NULL,
[Name] [nvarchar](400) NULL,
[Pack] [uniqueidentifier] NOT NULL,
[Slug] [nvarchar](400) NULL,
[Updated] [datetime2](7) NOT NULL,
CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
What is TEXTIMAGE_ON and why not just [PRIMARY]?
How can I change this table to use FileStream? So it would have:
[Key] uniqueidentifier rowguidcol not null
constraint DF_File_Key default newid()
constraint UQ_File_Key unique ([Key]),
And
) filestream_on ???
UPDATE
I have the following TSQL:
exec sp_configure filestream_access_level, 2
reconfigure
alter table dbo.Files
set (filestream_on = 'default')
alter table dbo.Files
alter column [Key] add rowguidcol;
alter table dbo.Files
alter column Content filestream;
alter table dbo.Files
add constraint DF__File__Content default (0x),
constraint DF__File__Key default newid(),
constraint UQ__File__Key unique ([Key]);
go
But when I run it I get the error: Incorrect syntax for definition of the 'TABLE' constraint.
I am using "default" because I want to use the default filegroup.
What am I missing?
1条答案
按热度按时间rvpgvaaj1#
From msdn :
SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" } )
Applies to: SQL Server 2008 through SQL Server 2016. Specifies where FILESTREAM data is stored. ALTER TABLE with the SET FILESTREAM_ON clause will succeed only if the table has no FILESTREAM columns. The FILESTREAM columns can be added by using a second ALTER TABLE statement.
The
TEXTIMAGE_ON
is used by default on tables with big columns (nvarchar(max), varbinary(max)) etc as mentioned here :TEXTIMAGE_ON is not allowed if there are no large value columns in the table. TEXTIMAGE_ON cannot be specified if <partition_scheme> is specified. If "default" is specified, or if TEXTIMAGE_ON is not specified at all, the large value columns are stored in the default filegroup. The storage of any large value column data specified in CREATE TABLE cannot be subsequently altered.