Feeds:
Posts
Comments

Archive for the ‘Database Features’ Category

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.

creating sub directory in filetable

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.

creating sub directory in filetable 2

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.

Advertisements

Read Full Post »

Filetable is one of the best features shipped with SQL Server 2012. It makes our life easier when you need to manage your files and folders via SQL Server when it actually resides in the file system of windows. However, you can easily access them using windows application and SQL Server as well.

Pre-requisites :

Problem :
Sometimes, you execute a select statement against a filetable using SQL Server Management Studio (SSMS) and it becomes inaccessible and even after spending so much time, the query does not return the result set. However, you can still access it via windows file system. In normal scenario, filetable returns result set in less than few seconds (depending upon the size of filetable). Now what to do ?

Solution :
First of all, I cancelled the query that kept on running for hours. It seemed to me that this filetable had been locked because of some transaction. Just to test this scenario, I executed the same query with no lock table hint and it returned the result set as shown below.

USE SampleDB
GO
SELECT * FROM [dbo].[CVBank] WITH (NOLOCK)
GO
--OUTPUT

indentify filetable lock.1.1

Wow, so the file table was indeed locked. So half of the problem is solved. Now, in order to fix this issue, I need to identify which file(s) is/are being used and due to which, the filetable has been apparently locked.
Given below is the script that will give you the file(s) name being used and due to which, the filetable has been locked. I found this script on MSDN.

USE SampleDB
GO
SELECT opened_file_name
    FROM sys.dm_filestream_non_transacted_handles
    WHERE fcb_id IN
        ( SELECT request_owner_id FROM sys.dm_tran_locks );
GO
--OUTPUT

indentify filetable lock.1.2

As mentioned above, I identified the lock held by the filetables and in order to avoid this issue, you can take necessary action to close this file.

Let me know if you come across this situation in real world.

Read Full Post »

In my earlier article, I wrote about how to generate a serial number by using the sequence object for a result set . However, sometimes you need to generate a negative serial number for any result set and you can easily achieve it by adjusting few properties in the sequence object.

Let me explain it step by step to demonstrate the solution.

Step 1 :
First of all, you need to create a sequence object along with some specific negative parameters as shown below.

USE AdventureWorks2012
GO
--DROP SEQUENCE dbo.Seq_Sample
--GO
CREATE SEQUENCE dbo.Seq_Sample
   AS int
    START WITH -1
    INCREMENT BY -1
    MINVALUE -3000
    MAXVALUE -1
    CYCLE ;
GO
--OUTPUT

Step 2 :
In this step, you are ready to use the above sequence object to generate a negative serial number as shown below.

USE AdventureWorks2012
GO
SELECT
NEXT VALUE FOR Seq_Sample AS [Sno], Name
FROM sys.all_objects ;
GO
--OUTPUT

Negative serial number - Sequence.1.1

Step 3 :
In this step, you need to reset the sequence number in order to start the sequence number from -1 each time. Given below is the script.

USE AdventureWorks2012
GO
ALTER SEQUENCE dbo.Seq_Sample
RESTART WITH -1 ;
GO
--OUTPUT

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

Read Full Post »

Sequence object is one of the handy objects when we need to maintain one serial number across the tables. I have written few articles on Sequence object earlier in my post.

PROBLEM :
Today, I will share an issue that I came across while dropping sequence object. I created few tables and made a single sequence object as a default serial number in one of the columns in these tables. But once I drop this sequence object it gave me error due to dependency. It seems that I need to go one by one and search each dependency of this sequence object and drop all of them before dropping Sequence Object.

Let me create an example to demonstrate the solution.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE TABLE
USE tempdb
GO
CREATE TABLE dbo.tbl_sample
( [ID] VARCHAR(8) ,
[Product Name] varchar(50)
)
GO

--CREATE SEQUENCE
USE tempdb
GO
CREATE SEQUENCE dbo.Sample_Seq AS
INT START WITH 1
INCREMENT BY 1 ;
GO

-- CREATE DEFAULT VALUE OF SEQUENCE
USE tempdb
GO
ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_Sample_Seq
DEFAULT (NEXT VALUE FOR dbo.Sample_Seq) FOR [ID];
GO
--TRY TO DROP SEQUENCE OBJECT
DROP SEQUENCE Sample_Seq
GO
--OUTPUT

Msg 3729, Level 16, State 1, Line 1
Cannot DROP SEQUENCE ‘Sample_Seq’ because it is being referenced by object ‘Const_Sample_Seq’.

SOLUTION :
As mentioned above, I created a default constraint to a table using sequence object. The problem started when I tried to drop the sequence. Due to dependency, if one sequence object has been used in multiple tables as a default value, then you need to search each dependency of that sequence object and as usual drop all dependent objects before dropping the sequence object.
You can do it, one finding and dropping one object by one object, simple but hectic solution.
Alternative, you can use give below solution. This solution will prepare script to drop all dependent objects of the any sequence object.

-- This script is compatibile with SQL Server 2012 and above.
USE tempdb
GO
SELECT
'ALTER TABLE '
+ object_name(parent_object_id)
+ ' DROP CONSTRAINT '
+ referencing_entity_name AS [Text]
FROM sys.dm_sql_referencing_entities ('dbo.Sample_Seq', 'OBJECT') re
INNER JOIN sys.default_constraints dc
	ON re.referencing_id=dc.object_id
GO
--OUTPUT

drop contraint.1.1

Just copy the above result set and execute it as mentioned below and it will drop all your dependent objects related to sequence object. Given below is the script.

-- This script is compatibile with SQL Server 2012 and above.
USE tempdb
GO
ALTER TABLE tbl_sample DROP CONSTRAINT Const_Sample_Seq
GO
--OUTPUT

Once you drop the constraint, you can easily drop the sequence, you will not get the above error.

-- This script is compatibile with SQL Server 2012 and above.
DROP SEQUENCE Sample_Seq
--OUTPUT

Command(s) completed successfully.

Let me know if you came across such issues and its solutions.

Read Full Post »

In my earlier articles, I wrote how to insert files inside the file table and this is more than enough if you maintain few files inside one filetable. But once a filetable is crowded you should maintain the files inside the folders to manage it properly. But how to create a folder inside a filetable ?

Note : I strongly recommend that you should read 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)

There are two methods to create a folder inside file table. Given below are the details.

  • Using SQL Server Management Studio (SSMS)
  • Using T-SQL

Let me explain each method in detail.

  • Using SQL Server Management Studio (SSMS)

This is the easiest way to create a folder inside filetable. In order to follow this method we need to achieve it step by step.

Step 1 :
First of all you should select the respective filetable in which you need to create a folder and right click on it and select “Explore filetable directory” as shown in the image below.

create_folder_inside_filetable.1.1

Step 2 :
Once you select “Explore filetable directory”, it will open that particular filetable directory folder. You can create folder here, just like in windows, as shown below.

create_folder_inside_filetable.1.2

create_folder_inside_filetable.1.3

  • Using T-SQL

This method we usually use when we need to create a folder dynamically using T-SQL. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE SampleDB
GO
INSERT INTO dbo.CVBank
(name,is_directory) VALUES ('New folder', 1);

Read Full Post »

Generating Serial number is something that is very usual for a SQL Server developer, either it is for report or any result set. I would not be wrong if I said that it is something that we need to use every now and then. I usually use ROW_NUMBER to generate the serial number in most of the places unless otherwise any compatibility issues. However, I wanted to generate it in new ways and recently, I found a new method to generate serial number while playing with Sequence Object.
Given below are scripts to generate a serial number using ROW_NUMBER & SEQUENCE Object.

  • Using ROW_NUMBER
--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT 
ROW_NUMBER() OVER(ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--OUTPUT

serial number using sequence.1.1

  • Using SEQUENCE Object 
--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--DROP SEQUENCE dbo.Seq_Sample
--GO
--Create Sequence Object
CREATE SEQUENCE dbo.Seq_Sample
 AS int     START WITH 1
INCREMENT BY 1 ;
GO
--Generate serial number using Sequence object
SELECT
  NEXT VALUE FOR dbo.Seq_Sample OVER (ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--Reset Sequence object to 1, So it will start from 1
--in the next result set.
ALTER SEQUENCE [dbo].Seq_Sample RESTART WITH 1
GO
--OUTPUT

serial number using sequence.1.1

Read Full Post »

I deployed some Sequence objects on production database and after couple of days, I was facing some problem with Sequence objects. It was little difficult to debug all sequence objects at once, so I thought of checking the sequence objects modified dates to find if I modified any Sequence object after deployment.

Given below are the two methods to find the modified dates of Sequence Object.

METHOD 1 : Using sys.sequence
Given below is the script to find the modified date of all sequence object using sys.sequences

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.sequences
GO
--OUTPUT

Sequence modified dates

METHOD 2 : Using sys.objects
Given below is the script to find the modified date of all sequence object using sys.objects

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.objects
WHERE [type] ='SO'
GO
--OUTPUT

Sequence modified dates

Read Full Post »

Older Posts »