SQL Server Why TEXTIMAGE_ON and make table as FileStream

xoshrz7s  于 2023-04-10  发布在  其他
关注(0)|答案(1)|浏览(140)

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?

rvpgvaaj

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.

相关问题