Shulou(Shulou.com)05/31 Report--

What is the role of FileStream in SQL Server, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

I. FileStream configuration

1. Configure the SQL Server installation instance: Start-> All Programs-> Microsoft SQL Server 2008 R2-> Configuration Tools-> SQL Server Configuration Manager

Right-click the properties, switch to the FILESTREAM tag, and check the following configuration

two。 Open SQL Server and configure it as follows

The above can also be executed by the following script:

Exec sp_configure filesteam_access_level, 2RECONFIGURE

Finally restart SQL Server Service

II. Demonstration of examples

Create FileStream type files / groups

-- Create filestreamgroup ALTER DATABASE [Archive] ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM GO--Create filestream and association with filestreamgroup aboveALTER DATABASE [Archive] ADD FILE (NAME = noded FileStream, FILENAME = nude:\ Company\ Data\ SQL Server\ FileStream') TO FILEGROUP [FileStreamGroup] GO

Create a test table (note: if the table contains FILESTREAM columns, each row must have a unique row ID)

-- Create tableCREATE TABLE Archive.dbo.Attachment ([ID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL PRIMARY KEY, [FileName] NVARCHAR (100) NULL, [CreateUser] NVARCHAR (100) NULL, [CreateDatetime] DATETIME NULL, [Content] VARBINARY (MAX) FILESTREAM NULL) FILESTREAM_ON [FileStreamGroup]

Insert some test data

-- Insert some recordsINSERT INTO Attachment VALUES (NEWID (), 'File Name 1), GETDATE (), NULL), (NEWID (),' File Name 1), GETDATE (), CAST (''AS VARBINARY (MAX)), (NEWID (), 'File Name 1), GETDATE (), CAST (' This is an attachment, which contains all introduction for filestream' AS VARBINARY (MAX)

Insert some data from the front desk

Using (SqlConnection conn = new SqlConnection ("server=10.7.15.172;database=Archive;uid=sa;pwd=1234;Connect Timeout=180")) {conn.Open (); using (SqlCommand cmd = conn.CreateCommand ()) {string id= Guid.NewGuid (). ToString (); cmd.CommandText = "INSERT INTO Attachment VALUES ('" + id + "', 'File Name 2'," + DateTime.Now + ", @ content)"; SqlParameter param = new SqlParameter ("@ content", SqlDbType.VarBinary, 1000000000) Param.Value = File.ReadAllBytes (@ "D:\ Folder\ 131u_ex151207.log"); cmd.Parameters.Add (param); cmd.ExecuteNonQuery ();} conn.Close ();}

Retrieve data

SELECT DATALENGTH (CONTENT) / (1024.0 * 1024.0) AS MB,* FROM ATTACHMENT

Result

File system

The above files are all real files uploaded, but there is no suffix. If you rename and add a suffix, you can read it. If the last file is an excel file, add .xls, you can open this file with Excel software.

Matters needing attention

Please pay attention to the following:

Not all file stores are suitable for using FileStream, if the file objects stored are on average larger than 1MB, consider using FileStream, otherwise storing in varbinary (max) BLOB in the database will usually provide better streaming performance for smaller file objects; FileStream can be used on a failure cluster (Failover Cluster), but the FileStream filegroup must be on a shared disk resource Compatibility of FILESTREAM with other SQL Server features: https://msdn.microsoft.com/zh-cn/library/bb895334(v=sql.105).aspx

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.