SQL Server Portal

In my earlier article, I have explained that creating a directory / sub directory in any file table is same like creating a directory / sub directory in windows itself. However, sometimes you need to create these directories and sub directories via T-SQL. I came across a case recently where I had to create a series of sub directories inside a directly using T-SQL.

Prerequisite :
I strongly recommend that you read the given below articles to have a clear understanding about FileTables.

1. FileTables – Prerequisites
2. FileTables – Data Definition Language (DDL)
3. FileTables – Data Manipulation Language (DML)

Let me explain the solution step by step.

Step 1 :
First of all you need to create a sequence (A new object shipped with SQL Server 2012) to generate a series of IDs. However you can use any other techniques to create these series.

--This script is compatible with SQL Server 2012 and above.
--DROP SEQUENCE [dbo].[NewID]
--GO
CREATE SEQUENCE [dbo].[NewID]
AS [bigint]
START WITH 100000000
INCREMENT BY 1
CACHE
GO

Step 2 :
In this step, you need to create the given below procedure that can generate a new ID for your sub directory. Remember that filetable maintains directory and sub directory IDs in a hierarchy ID datatype. So you must get the parent folder ID (directory hierarchy ID) in order to create a child folder (sub directory). You can get further detail about hierarchy ID here. This stored procedure is self explanatory.

--This script is compatible with SQL Server 2012 and above.
--DROP PROCEDURE dbo.GetNewPathLocator
--GO
CREATE PROCEDURE dbo.GetNewPathLocator
@MainFolderID HIERARCHYID,
@SubDirectoryPath VARCHAR(MAX) OUTPUT

AS
BEGIN

DECLARE @FirstSeqNum sql_variant,
@LastSeqNum  sql_variant

EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.NewID'
, @range_size = 3
, @range_first_value = @FirstSeqNum OUTPUT
, @range_last_value = @LastSeqNum OUTPUT

SELECT @SubDirectoryPath = CONCAT(COALESCE(@MainFolderID.ToString(),'/'),
CONVERT(VARCHAR(20),@FirstSeqNum) ,'.',
CONVERT(VARCHAR(20),Convert(BIGINT,@FirstSeqNum)+1) ,'.',
CONVERT(VARCHAR(20),@LastSeqNum) ,'/')

END
GO

Step 3 :
Now, it is time to create a sub directory in any directory using T-SQL. I already created a directory inside a filetable as shown below.

Let us create a sub directory inside that directory.

--This script is compatible with SQL Server 2012 and above.
DECLARE @MainFolderPath AS HIERARCHYID
--You must have the given below file table (dbo.DataBank)
--and directory (IT) inside file table in the existing database.
SELECT @MainFolderPath=path_locator FROM dbo.DataBank
WHERE [name]='IT'

DECLARE @SubDirectoryPath varchar(max)
EXEC dbo.GetNewPathLocator
@MainFolderID=@MainFolderPath
, @SubDirectoryPath = @SubDirectoryPath OUTPUT

--SELECT @SubDirectoryPath

INSERT INTO dbo.DataBank (name,path_locator,is_directory,is_archive)
VALUES ('sub directory', @SubDirectoryPath, 1, 0);
GO

Given below is the new sub directory created inside IT folder via T-SQL.

Conclusion :
Remember, filetable keeps directory and sub directory IDs in Hierarchy ID datatype. So you must go through this concept. In addition, the whole process is self explanatory.

Let me know if you came across this situation and how you handled it.

Read Full Post »