FileTable in SQL Server

File table is special type of table to store files and folder in SQL Server 2012 onwards, these files and folder are easily accessible through windows explorer it as good as windows file system. We can add, delete file to windows directory by copy and paste then it will shows those files in table FileTable

We can manage files from windows directory as well as using SQL server management studio i.e. delete/truncate/update/select etc.
File tables have specific set of columns while creation of filetable we no need to specify list of columns.
To create filetable database should have file stream file & file group

File Tables provide following features

1. Provides hierarchy of files and folders
2. Every rows represent file or folder
3. We can perform DML operation on selected columns i.e. non computed columns
4. Every rows hold followings
            (i) File stream column for stream data and file_id as GUID
            (ii) Path and parent path locator as an hierarchy
            (iii) Ten file attribute i.e. file creation/access/modification etc.
            (iv) Type column which support full text and semantic search

5. We cannot change existing table to file table
6. We must need to specify parent directory(i.e. Dineshbabuverma) at database level
7. Must have valid file stream file and file group
8. We cannot create table level constraints while creation of table but we can add constraints after creation of table
9. We cannot create table filetable in database tempdb.

Let us perform steps to create table filetable consider following scenarios
Table Name : MyFileTable
Database Name : MyFileTableDB
Directory Name : Dineshbabuverma
File Group Name : FIleTableFG
FileTable File Name : FileTableFile


(i) Create Database with given filegroup and filename : MyFileTableDB

CREATE DATABASE [MyFileTableDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'MyFileTableDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyFileTableDB.mdf' , SIZE = 15360KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FIleTableFG] CONTAINS FILESTREAM DEFAULT
( NAME = N'FIleTableFile', FILENAME = N'C:\FIleTableFile' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'MyFileTableDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyFileTableDB_logFile.ldf' , SIZE = 38208KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

If we have existing database we can file group and file as type filestream using below commands

ALTER DATABASE MyFileTableDB
ADD FILEGROUP MyFileTableFG contains filestream;
GO

ALTER DATABASE MyFileTableDB
ADD FILE
( NAME = 'MyFileTableFile', FILENAME = 'C:\MyFileTableFile'
)
TO FILEGROUP MyFileTableFG;
GO



(ii) Enable Filestream
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO


(iii) Set Filestream Details as below
ALTER DATABASE MyFileTableDB
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'Dineshbabuverma')


Same we can set Using UI as per below screen, marked in color yellow



(iv) Enable Filestream for Transact SQL Access: first go to SQL Server configuration manager, Right click on SQL Server Instance Properties Filestream tab and enable filestream access as below


select DB_NAME(database_id ) DatabaseName ,* FROM sys.database_filestream_options
where directory_name is not null;


Output of query:


Now we can create file Table and explore the file table directory according to below figure.
CREATE TABLE MyFIleTable AS FILETABLE

output of the select command
select * FROM MyFIleTable






Once we click on explore filetable directory it open windows file directory DineshBabuverma of table Myfiletable


Now let us just copy and paste some files as below


Now execute again select statement, we can see our files in table MyFileTable
select * FROM MyFIleTable



/*Below delete command will delete file IMG_20150829_161804067.jpg from windows directory, first execute then check in filedirectory*/
DELETE FROM MyFIleTable
WHERE stream_id ='869D2F26-2A61-E511-BE9B-4487FCD82C05'

select * FROM MyFIleTable



Now if we check files in windowsfiledirectory DineshBabuverma, we will see only two files

We can insert file into FileTable using below command

INSERT INTO [dbo].MyFIleTable
([name],[file_stream])
SELECT
'DP1.png', * FROM OPENROWSET(BULK N'D:\DP1.png', SINGLE_BLOB) AS FileData
;


For inserted file we can see additional one row in above result set, marked in color yellow.

Note: Please try above scenarios in development/test environment.

Data Compression in SQL Server

SQL Server support three types of Data compression which help to save storage space by reducing the size of the database, data compression has overhead on CPU, it is because for usage purpose CPU has to decode the compressed data. Data compression is not applicable on system tables. Enterprise and developer edition support Database compression.

Types of Data compression:

1. Row Level Compression
2. Page Level Compression
3. Unicode Compression

Data Compression can be configured on following configurations.
a. Clustered Table
b. Heap Table(A table without cluster index)
c. Non Clustered indexes
d. Indexed Views
e. On Individual partition of partitioned table or index

Row Level Compression
(i) Row level compression work by storing fixed length Char data types albeit they were stored as Variable length Varchar data types
(ii) It won’t stores NULL and 0 values
(iii) Reducing Meta data required for storing of data.

We can use following SQL commands for Row level compression on table Employee
ALTER TABLE Employee REBUILD WITH (DATA_COMPRESSION=ROW)

For partitioned table use below command
ALTER TABLE Employee REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)

For index, row level compression use below command assuming index idx_Employee_Id on table Employee
ALTER INDEX idx_Employee_Id ON Employee REBUILD WITH (DATA_COMPRESSION=ROW)


Page Level Compression
Page level compression, compress data by storing repeating or common value at one place and making reference to that value in table, when page compression applied then row level compression automatically applied.

Page level compression uses following techniques

(i) Row level compression is applied to store maximum number of rows on page
(ii) Column prefix compression is applied to store repeating values in header and reference is mapped with repeating/common values.
(iii) Dictionary compression scans for repeating values and store scanned info in page header


We can use following SQL commands for Page level compression on table Employee.
ALTER TABLE Employee REBUILD WITH (DATA_COMPRESSION=PAGE)

For partitioned table use below command.
ALTER TABLE Employee REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

For index, page level compression use below command assuming index idx_Employee_Id on table Employee.
ALTER INDEX idx_Employee_Id ON Employee REBUILD WITH (DATA_COMPRESSION=PAGE)


Unicode Compression
Unicode compression enables database engine to compress Unicode values(NChar, NVarchar) stored in row and page compressed objects, we can use Unicode compression with fixed length NChar and NVarchar data types.
Unicode compression get automatically used accordingly when we use row/page compression.


If we want to implement data compression using wizard we can use following steps
1. Right click on table Employee then click storage and then manage compression as below



2. It will populate below window



3. From the below screen select row/page compression.


4. Select compression type as row then click next, it will populate below window



5. Click finish then it will the command in new window or we can use RUN immediately from above screen, here we have selected create script, then click finish.



USE [Capital]
ALTER TABLE [dbo].[Employee] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)

it will generate above script as per our selection, we can execute script for row level compression.

Queries to Delete Duplicate Records in SQL Server

Queries to Delete Duplicate records

/*Start Create Table with Duplicate records */
CREATE TABLE DeleteDuplicate
(
ID INT,
NAME VARCHAR(88)
)

INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4

/*End Create Table with Duplicate records */


/*Method#01 Start DELETE Duplicate Rows using while loops*/
WHILE ( 1 = 1 )
BEGIN
SET ROWCOUNT 1

DELETE c
FROM DeleteDuplicate c
INNER JOIN (SELECT id,
NAME
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1) CC
ON C.id = CC.id
AND C.NAME = CC.NAME

IF @@ROWCOUNT = 0
BEGIN
SET ROWCOUNT 0
BREAK;
END
END
/*Method#01 End DELETE Duplicate Rows using while loops*/


/*Method#02 Start DELETE Duplicate Rows using Go statement*/
INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO

DECLARE @DupRowCount INT = 0;
SELECT @DupRowCount =@DupRowCount +( Count(*) -1)
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1
select @DupRowCount

SET Rowcount 1
DELETE c
FROM DeleteDuplicate c
INNER JOIN (SELECT id,
NAME
FROM DeleteDuplicate
GROUP BY id,
NAME
HAVING Count(*) > 1) CC
ON C.id = CC.id
AND C.NAME = CC.NAME
GO 20
/* above we need to specify value of duplicate records. which got from variable @DupRowCount*/
SET RowCount 0
GO
/*Method#02 End DELETE Duplicate Rows using Go statement*/



/*Method#03 Start DELETE Duplicate Rows using CTE*/

INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO


;WITH DELETECTE(Id,Name,DeleteCount )
as
(select id,name ,
r = row_number() Over(Partition by id,name order by id,name)
from DeleteDuplicate )
DELETE FROM DELETECTE
WHERE DeleteCount<>1
GO
/*Method#03 End DELETE Duplicate Rows using CTE*/

/*Method#04 Start DELETE Duplicate Rows using Physical Address(physloc)[Undocumented vartual column or pseudo  column]*/


INSERT INTO DeleteDuplicate
(id, NAME)
VALUES (1, 'SHAYAM'),
(1,'SHAYAM'),
(2, 'Lohia'),
(2, 'Lohia'),
(2,'Lohia')
GO 4
GO


delete from DeleteDuplicate
where %%physloc%% not in
(select min(%%physloc%%) from DeleteDuplicate
group by ID,Name
)

/*Method#04 End DELETE Duplicate Rows using Physical Address(physloc)[Undocumented vartual column or pseudo  column]*/



DROP TABLE DeleteDuplicate


Note: Please try above SQL Script in Testing environment only.

WHO EXECUTED WHICH QUERY?

WHO EXECUTED WHAT?

SELECT db.[transaction name],
sl.NAME 'LoginName',
dp.NAME LOGINROLE,
[current lsn],
[operation],
[transaction id],
[description],
spid,
[begin time],
[transaction sid],
tex.text Most_Recent_SQL_Executed_in_That_Session
FROM Fn_dblog (NULL, NULL) db
LEFT JOIN sys.syslogins sl
ON db.[transaction sid] = sl.sid
LEFT JOIN sys.database_principals dp
ON db.[transaction sid] = dp.sid
LEFT JOIN sys.dm_exec_connections dec
ON db.spid = dec.session_id
LEFT JOIN sys.dm_exec_sessions des
ON db.spid = des.session_id
CROSS apply sys.Dm_exec_sql_text(dec.most_recent_sql_handle) tex
WHERE db.[transaction name] LIKE '%DROPOBJ%'


Using the above query we can find out who executed which query(INSERT/UPDATE/DELETE/DROP etc.), based on the transaction log entry.
Note: we can only get Last executed query in particular session based on column most_recent_sql_handle of system view sys.dm_exec_connections.

Please do not get confuse if you are getting INSERT query but in log you have searched for UPDATE, this is because you have executed INSERT query after UPDATE query in that session,