FILESTREAM in SQL Server 2008

In SQL Server many times our data type is not limited to only string and numbers. We need to store a large amount of data like documents, photos etc. in SQL Server table. SQL Server provides special data types for large volumes of data. They are known as Large object (LOB). Varbinary (MAX) is the data type that allows you to deal with large binary data. LOB type using this SQL Server Data Type is Binary Large Objects (BLOB).
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing BLOB data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. To improve the performance of Database engine, FILESTREAM caches file data using NT.

There are FILESTREAM filegroups which are used to store data. This filegroup contains file system directories instead of files. These directories are known as data containers. These data containers cannot be nested.

To save file in filestream we have to use FileUpload. It uploads the file and then saves it to Filestream file.

Byte [] buffer = new byte [ (int) FileUpload. FileContent. Length];
FileUpload .FileContent. Read (buffer, 0, buffer.Length);

GET_FILESTREAM_TRANSACTION_CONTEXT is also a newly added feature in SQL Server 2008 which returns current transaction context. To use files stored in Filestream field we have to use SqlFileStream. For that we need to include System. Data. SqlTypes.

SQL Server DELETE and TRUNCATE for ROW

I am currently working on a project, which requires to be synced with server database all the time, and there is lot of deletion which keeps happening, there are about 15,000+ records which gets deleted whenever synchronization is happening between server and client. I was performing DELETE statement to delete row from the table which was not required. Everything worked fine, the real problem started arrising when we were looking at the way database would act, we figured out that everytime we did deletion of 15,000+ rows, it will increase log file of database by 6.00MB. Which means within in a month database log file would increase upto 180MB, and 2190MB within in a year, and this is just regarding Deleting row.

I faced challange regarding how to make sure that the log file does not increase. While going through documentation, I found one keyword called TRUNCATE. When we used TRUNCATE keyword instead of DELETE, it worked perfectly fine, and log file was not increased, and we also found a good increase in performance.

Reason is that, when we use DELETE keyword it deletes row and also it keeps log of it in LOG file, but when we use TRUNCATE, it does not keep log of it, so nothing is recorded in LOG file, hence log file size does not increase. Which means that when we DELETE row(s) and we want to recover data back, we can use recover data back through log file, but when we use TRUNCATE keyword, we can not recover data back from log file.

But, here is the catch, if I use TRUNCATE keyword in TRANSACTION block, it allows us to recover data back, but there is no gurante of recovering data.

Sample Code:
BEGIN TRAN
SELECT * FROM MyTable
TRUNCATE TABLE MyTable
SELECT * FROM MyTable
ROLLBACK
SELECT * FROM MyTable