StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h1>How to store large blobs in sql server</h1> <p>Storing large chunks of binary data in SQL Server is not a great approach. It makes your database very bulky to backup and performance is generally not great. Storing <strong>files</strong> is usually done on the <strong>file</strong> system. Sql Server 2008 has out of the box support for <code>FILESTREAM</code>. Microsoft documents the cases to use <strong>FileStream</strong> as follows</p> <ul> <li>Objects that are being stored are, on average, larger than 1 MB.</li> <li>Fast read access is important.</li> <li>You are developing applications that use a middle tier for application logic. </li> </ul> <p>In your case I think all points are valid. </p> <h2>Enable on Server</h2> <p>To enable <code>FILESTREAM</code> support on the server use the following statement.</p> <pre><code>EXEC sp_configure filestream_access_level, 2 RECONFIGURE </code></pre> <h2>Configure the Database</h2> <p>To get a filestream filegroup linked to your database create </p> <pre><code>ALTER DATABASE ImageDB ADD FILEGROUP ImageGroup CONTAINS FILESTREAM ALTER DATABASE ImageDB ADD FILE ( NAME = 'ImageStream', FILENAME = 'C:\Data\Images\ImageStream.ndf') TO FILEGROUP TodaysPhotoShoot </code></pre> <h2>Creating the table</h2> <p>The next step is getting your data in the database with filestream storage:</p> <pre><code>CREATE TABLE Images ( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL PRIMARY KEY, [CreationDate] DATETIME NOT NULL, [ImageFile] VARBINARY(MAX) FILESTREAM NULL ) </code></pre> <p>For <code>Filestream</code> to work you not only need the <code>FILESTREAM</code> property on a field in the table, but also a field which has the <code>ROWGUIDCOL</code> property.</p> <h2>Inserting Data with TSQL</h2> <p>Now to insert data in this table you can use TSQL:</p> <pre><code>using(var conn = new SqlConnection(connString)) using(var cmd = new SqlCommand("INSERT INTO Images VALUES (@id, @date, cast(@image as varbinary(max))", conn)) { cmd.Parameters.AddRange(new { new SqlParameter("id", SqlDbType.UniqueIdentifier).Value = uId, new SqlParameter("date", SqlDbType.DateTime).Value = creationDate, new SqlParameter("image", SqlDbType.varbinary).Value = imageFile, }); conn.Open cmd.ExecuteScalar(); } </code></pre> <h2>Inserting data using <code>SqlFileStream</code></h2> <p>There also exists an approach to get the file data on disk using Win32 directly. This offers you streaming access <code>SqlFileStream</code> inherits from <code>IO.Stream</code>. </p> <p>Inserting data using win32 can be done with for example the code below:</p> <pre><code> public void InsertImage(string connString, Guid uId, DateTime creationDate, byte[] fileContent) { using (var conn = new SqlConnection(connString)) using (var cmd = new SqlCommand(@"INSERT INTO Images VALUES (@id, @date, cast(@image as varbinary(max)) output INSERTED.Image.PathName()" , conn)) { conn.Open(); using (var transaction = conn.BeginTransaction()) { cmd.Transaction = transaction; cmd.Parameters.AddRange( new[] { new SqlParameter("id", SqlDbType.UniqueIdentifier).Value = uId, new SqlParameter("date", SqlDbType.DateTime).Value = creationDate, new SqlParameter("image", SqlDbType.VarBinary).Value = null } ); var path = (string)cmd.ExecuteScalar(); cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"; var context = (byte[])cmd.ExecuteScalar(); using (var stream = new SqlFileStream(path, context, FileAccess.ReadWrite)) { stream.Write(fileContent, 0, fileContent.Length); } transaction.Commit(); } } </code></pre> <hr> <h1>How to model a Photo storage database</h1> <p>With the filestream approach to store the images the table is very narrow which is good for performance since many records can be stored per 8K data page. I would use the following model:</p> <pre><code> CREATE TABLE Images ( Id uniqueidentifier ROWGUIDCOL NOT NULL PRIMARY KEY, ImageSet INTEGER NOT NULL REFERENCES ImageSets, ImageFile VARBINARY(MAX) FILESTREAM NULL ) CREATE TABLE ImageSets ( ImageSet INTEGER NOT NULL PRIMARY KEY, SetName nvarchar(500) NOT NULL, Author INTEGER NOT NULL REFERENCES Users(USerId) ) CREATE TABLE Users ( UserId integer not null primary key, UserName nvarchar(500), AddressId integer not null REFERENCES Addresses ) CREATE TABLE Organsations ( OrganisationId integer not null primary key OrganisationName nvarchar(500), AddressId integer not null REFERENCES Addresses ) CREATE TABLE Addresses ( AddressId integer not null primary key, Type nvarchar(10), Street nvarchar(500), ZipCode nvarchar(50), City nvarchar(500), ) CREATE TABLE OrganisationMembers ( OrganisationId integer not null REFERENCES Organisations, UserId integer not null REFERENCES Users, PRIMARY KEY (UserId, OrganisationId) ) CREATE NONCLUSTERED INDEX ixOrganisationMembers on OrganisationMembers(OrganisationId) </code></pre> <p>This translates to the following Entity RelationShip Diagram:</p> <p><img src="https://i.stack.imgur.com/nBmyt.png" alt="Entity RelationShip Diagram"></p> <ul> <li>Performance wise, the narrow images table is very good as it contains only a few bytes of data per record. </li> <li>We can assume that an image is always member of an Image Set, The Set information could be hidden if there is only 1 image in it.</li> <li>I assume you want to track which users are member of which organisations, so I added a table to link them (Assuming a user can be member of multiple organisations).</li> <li>The primary key on the OrganisationMembers table has UserId as first field since there normally a lot more users than Organisations and you probably will want to show which organisations a user is member off more often than the inverse.</li> <li>The index on OrganisationId in OrganisationMembers is there to cater for queries where the list of members for a specific Organisation needs to be shown. </li> </ul> <p>References:</p> <ul> <li><a href="http://technet.microsoft.com/en-us/library/cc645923.aspx" rel="nofollow noreferrer">Enable and configure Filestream</a></li> <li><a href="http://technet.microsoft.com/en-us/library/cc645585.aspx" rel="nofollow noreferrer">Create a filestream enabled Database</a></li> <li><a href="http://technet.microsoft.com/en-us/library/cc645583.aspx" rel="nofollow noreferrer">Create a table for storing filestream data</a></li> <li><a href="http://technet.microsoft.com/en-us/library/cc645962.aspx" rel="nofollow noreferrer">Managing Filestream with Transact SQL</a></li> <li><a href="http://technet.microsoft.com/en-us/library/cc645940.aspx" rel="nofollow noreferrer">Create Client applications with Filestreams</a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload