asked
Filestream's primary ID
I have created a filestream as Microsoft has described to do by making the "GUID" the primary key, as shown here. CREATE TABLE [dbo].[RCWFileStream]( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()), [Name] [nvarchar](100) NOT NULL, [Data] [varbinary](max) FILESTREAM NOT NULL, [recordID] [bigint] NOT NULL, //Foreign key [description] [varchar](max) NULL, [filesize] [float] NULL, [timestep] [datetime2](7) NULL CONSTRAINT [DF_RCWFileStream_timestep] DEFAULT (sysdatetime()), 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] FILESTREAM_ON [FileStreamData] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [FileStreamData] As I understand it, in general this creates a Non-Clustered structure on a Clustered Table. I have changed it to this: CREATE TABLE [dbo].[RCWFileStream]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [guid] [uniqueidentifier] ROWGUIDCOL UNIQUE NOT NULL DEFAULT (newsequentialid()), [Name] [nvarchar](100) NOT NULL, [Data] [varbinary](max) FILESTREAM NULL, [recordID] [bigint] NOT NULL, [description] [varchar](max) NULL, [filesize] [float] NULL, [timestep] [datetime2](7) NULL CONSTRAINT [DF_RCWFileStream_timestep] DEFAULT (sysdatetime()), 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] FILESTREAM_ON [FileStreamData] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [FileStreamData] A Clustered structure on a clustered table. Both work in testing, there is a list of records(recordID) you select a record and can pull up the associated files. Its internal, between 2-10 concurrent users, not hi volume. The second one feels right but Microsoft gives no information on using integers as primary keys in filestreams. Why is that? Is the second one better or worse? Have I left something out?
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
Yeah, there's nothing special about using a GUID as the identifier for FILESTREAM. It's just something that Microsoft chose to do in the documentation. You can make it pretty much anything that can work as a primary key, including INT.
Correct me if I am wrong but a FILESTREAM is the process of utilizing a part of the SQL Server as a file server. Because a GUID is random this allows for even distribution of the work load over the tables indexes reducing LOCK contention. Returning a row from a table takes mille-seconds while returning a gigabyte file from a FILESTREAM takes, well, a whole lot longer. So the question is does introducing INT as a primary key break this?
No, not really. The return speed is going to about the same regardless.
17 People are following this question.
