Feeds:
Posts
Comments

Archive for the ‘SQL Server 2012’ Category

TRY_CONVERT is one of the new conversion functions shipped with SQL Server. This function converts expression from one data type to another data type. The beauty of this function is that if it fails to convert, it returns NULL value as a results and due to this functionality, it has an edge over CONVERT function. I started using this function wherever conversion required since SQL Server 2012, however, I received an error message this morning when I was trying to use TRY_CONVERT function in SQL Server 2014 as shown below.

Let me explain this error in detail :

Message Number: 195

Severity : 15

Error Message: ‘TRY_Convert’ is not a recognized built-in function name.

Error Generation:

Let me create a sample using TRY_Convert  function to demonstrate this error.

USE SampleDB
GO
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
SELECT @@VERSION
GO

Msg 195, Level 15, State 10, Line 5
‘TRY_CONVERT’ is not a recognized built-in function name.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64)

Try_Convert .1.2

Ooopps…… I am unable to use TRY_CONVERT even though I am using SQL Server 2014 as shown above.

Resolution:

The resolution is very simple, actually, what you are trying to do is to use TRY_CONVERT function in one of the databases having compatibility less than 110 even though you are using SQL Server 2014. Lets fix this issue step by step.

Step 1: Check compatibility

USE SampleDB;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'SampleDB';
GO
--OUTPUT

compatibility_level
——————-
100

(1 row(s) affected)

Step 2: Change compatibility
As you can see above the compatibility of the database is below 110, lets change it to enjoy the core functionality of SQL Server 2014.
Note : Test the compatibility change of your database on your test/development database first, before proceeding to production database.

USE master
GO
ALTER DATABASE SampleDB
SET COMPATIBILITY_LEVEL = 120;
GO

Step 3: Use TRY_CONVERT

USE SampleDB
GO
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
--OUTPUT

Try_Convert .1.1

Conclusion :

Remember, whenever you use NEW SQL Server functions that are compatible with specific versions / compatibility level, you MUST check the version/ compatibility before implementing those functions.

Read Full Post »

Paging became quite simpler & easy to script and manage by using OFFSET & FETCH NEXT keywords in SQL Server 2012 & above. I have written quite a detailed article earlier about it and implemented it in my most of the solutions wherever required. However, when you implement/use paging in your script, you face a big challenge, that is, to find the total number of records in that particular result-set.

Given below are the three methods which you can use to get the total row count from OFFSET / FETCH NEXT.
Before proceeding with the solutions, let me create a sample.

Sample :

USE AdventureWorks2014
GO
-- Create Sample Table Table
CREATE TABLE [dbo].[SalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

-- Insert bulk data into sample table
-- It may take few minutes depends upon the server performance
INSERT INTO [dbo].[SalesOrderDetail]
SELECT * FROM [SALES].[SalesOrderDetail]
GO 100

-- Verfiy the data
Select * from [dbo].[SalesOrderDetail]
GO

Method 1 : Using COUNT(*) OVER()

USE AdventureWorks2014
GO
DECLARE
  @PageSize INT = 10,
  @PageNum  INT = 1;

SELECT
  [SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [TotalCount]= COUNT(*) OVER()
FROM [dbo].[SalesOrderDetail]
ORDER BY SalesOrderID
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;
GO
--OUTPUT

row count using Offset 1.1

Method 2 : Using Common Table Expression

USE AdventureWorks2014
GO
DECLARE
    @PageSize INT = 10,
    @PageNum  INT = 1;

;WITH Main_CTE AS(
	SELECT [SalesOrderID]
	, [SalesOrderDetailID]
	, [CarrierTrackingNumber]
	, [OrderQty]
	, [ProductID]
	, [SpecialOfferID]
	FROM [dbo].[SalesOrderDetail]
)
, Count_CTE AS (
	SELECT COUNT(*) AS [TotalCount]
	FROM Main_CTE
)
SELECT *
FROM Main_CTE, Count_CTE
ORDER BY Main_CTE.SalesOrderID
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY
GO
--OUTPUT

row count using Offset 1.1

Method 3 : Using Cross Apply

USE AdventureWorks2014
GO
DECLARE @PageSize INT = 10,
        @PageNum  INT = 1;

 SELECT
  [SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [TotalCount]
FROM [dbo].[SalesOrderDetail]

CROSS APPLY (SELECT COUNT(*) TotalCount
FROM [dbo].[SalesOrderDetail] ) [Count]
ORDER BY SalesOrderID
OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY
GO
--OUTPUT

row count using Offset 1.1

All of the above methods give you the same result-sets. Lets view their performance given below.

S.No

Method

CPU Time

Elapsed Time

1

COUNT(*) OVER()

30654 ms

40372 ms

2

Common Table Expression

11762 ms

7665 ms

3

Cross Apply

11794 ms

7373 ms

Conclusion :
On the basis of above results, I would recommend that you either use Common Table Expression or Cross Apply to get the faster results.

Note : The above queries have been tested on ~12 Million records.

Read Full Post »

Change Data Capture (CDC) is one of the frequently used features in SQL Server 2008 and above, it records any (CDC enabled) table’s changes and stores in audit tables. Recently, I upgraded one of my client’s database from SQL Server 2005 database to SQL Server 2012 and one of the key reasons to upgrade is to utilize the new features in the upgraded version. Once I started enabling CDC feature in few tables of the database it gave me given below error.

Message Number: 22939

Severity : 16

Error Message: The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Error Generation:
I presume that CDC has been enabled on this particular database. Let me create a sample table to demonstrate this error.

USE AdventureWorks2012
GO
--Create Sample Table
CREATE TABLE tbl_Sample
(
 [ID] INT NOT NULL,
 [NAME] VARCHAR(50)
)
GO

-- Enable CDC feature on this table with net changes support parameter.
 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'dbo',
 @source_name = N'tbl_sample',
 @role_name = NULL,
 @supports_net_changes = 1
GO
--OUTPUT

Error Message 22939.1

Msg 22939, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 194
The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Ooopps…… I am NOT able to enable CDC on sample table.

Resolution:
The reason behind this error is that you do not have either primary key or unique index on the sample table and you want to enable net changes support in CDC. Before proceeding with the resolution, you should check whether you can create a Primary Key or Unique Index on the table to avoid such error.
Given below is the simple script to add Primary Key OR Unique Index in the sample table.

--Create Primary Key
USE AdventureWorks2012
GO
ALTER TABLE dbo.tbl_Sample ADD PRIMARY KEY (ID)
GO

-- Create Unique Index
USE AdventureWorks2012
GO
ALTER TABLE tbl_Sample
ADD CONSTRAINT UX_Constraint UNIQUE (ID)
GO
--OUTPUT

You can execute any one of the above scripts and can create Primary Key or Unique Index in the sample table.

Once you executed the above script, you can easily enabled the CDC with supports_net_changes as shown below.

 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'dbo',
 @source_name = N'tbl_sample',
 @role_name = NULL,
 @supports_net_changes = 1
 GO
--OUTPUT

Command(s) completed successfully.

Error Message 22939.2

Conclusion :
Remember, whenever you need to enable CDC with supports_net_changes, make sure that the particular table HAS either Primary Key or Unique Index in order to avoid this error.

Read Full Post »

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.

Read Full Post »

Change Data Capture (CDC) has been discussed in detail in my earlier articles. In this article, I will discuss an error message that I came across while disabling the CDC for a table due to insufficient parameters.

error

Let me explain this error in detail :

Message Number: 22960

Severity : 16

Error Message: Change data capture instance ‘%s’ has not been enabled for the source table ‘%s.%s’. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.

Error Generation:

Let me DISABLE CDC feature on a particular table.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = NULL
GO

Msg 22960, Level 16, State 1, Procedure sp_cdc_disable_table_internal, Line 75
Change data capture instance ‘(null)’ has not been enabled for the source table ‘HumanResources.Department’. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.

Ooopps…… I am unable to disable CDC on this table. How to fix it ?

Resolution:
The resolution is very simple because partially it is explained in the error message itself. Let me fix this error step by step.

Step 1 :
First of all, you need to execute sys.sp_cdc_help_change_data_capture (A system stored procedure) in order to find the capture instance name of that particular table name as shown below.

USE AdventureWorks2012
GO
sys.sp_cdc_help_change_data_capture
GO
--OUTPUT

error message 22960.1

Step 2 :
The next step is to get the capture instance name of the particular table from the above result set and pass it in the sys.sp_cdc_disable_table (A system stored procedure) to disable the CDC feature from that table as shown below.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = N'HumanResources_Department'
GO

Conclusion :

Remember, whenever you need to disable CDC feature from any table, find out the capture instance name of the table and then disable it using sys.sp_cdc_disable_table in order to avoid this error.

Read Full Post »

Columnstore index is one of the new features shipped with SQL Server 2012. I have written few articles about this type of index and described how it boosts in the performance. Recently, I worked on a huge table having table partitions residing in different file groups. And it seems that it is already optimized using Table partition. However, I tried to create a columnstore Index to further boost its performance and I did succeed.

Let me demonstrate it step by step.

Step 1 :
First of all, you need to create a partition function as shown below. If you already have partition function please SKIP this step.

USE AdventureWorks2012
GO
CREATE PARTITION FUNCTION [PFByDueDate](datetime) AS RANGE RIGHT
FOR VALUES
(
    N'2005-05-31T00:00:00', N'2006-05-31T00:00:00' ,
	N'2007-05-31T00:00:00', N'2008-05-31T00:00:00' ,
	N'2009-05-31T00:00:00'
)
GO

Step 2 :
Secondly, you need to create a partition scheme on the above partition function as shown below. If you already have partition scheme please SKIP this step.

USE AdventureWorks2012
GO
CREATE PARTITION SCHEME [PSDueDateByMonthRange]
AS PARTITION [PFByDueDate]
ALL TO ([PRIMARY])
GO

Step 3 :
Now, it is time to create a table using above created partition scheme to partition the data accordingly as shown below. If you already created a table using partition scheme please SKIP this step.

USE AdventureWorks2012
GO
CREATE TABLE dbo.[PurchaseOrderDetail_Sample]
(
[PurchaseOrderID] [int] NOT NULL,
[PurchaseOrderDetailID] [int] NOT NULL,
[DueDate] [datetime] NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[LineTotal] numeric(18,2),
[ReceivedQty] [decimal](8, 2) NOT NULL,
[RejectedQty] [decimal](8, 2) NOT NULL,
[StockedQty] Numeric(18,2),
[ModifiedDate] [datetime] NOT NULL
) ON [PSDueDateByMonthRange]([DueDate]);
GO

Step 4 :
Lets insert some data to test the performance.

--This query may take 2 to 10 minutes depends upon the server.
USE AdventureWorks2012
GO
INSERT INTO dbo.[PurchaseOrderDetail_Sample] WITH(TABLOCK)
SELECT * FROM [Purchasing].[PurchaseOrderDetail]
GO 100

Step 5 :
Once you insert the data, lets build the columnstore index. Remember, once you build the columnstore index you cannot modify the data in the table.

USE AdventureWorks2012
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_PurchaseOrderDetail_Sample_ColumnStore]
ON [PurchaseOrderDetail_Sample]
(
UnitPrice,
OrderQty,
ReceivedQty,
ProductID)
GO

Step 6 :
Once you build the columnstore index, lets execute the query and view the result set WITHOUT columnstore index.

--This query will ignore columnstore index
--By using table's option namely IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
--and will return the result set.
USE AdventureWorks2012
GO
SET STATISTICS TIME ON 
SET STATISTICS IO ON
GO
SELECT
  ProductID as [Product ID]
, AVG(UnitPrice) as [Average Unit Price]
, SUM(OrderQty) as [Purchase Order Qty]
, AVG(ReceivedQty) as [Received Qty]
FROM [dbo].[PurchaseOrderDetail_Sample]
WHERE [DueDate] Between '2007-01-01' And '2008-12-31'
GROUP BY ProductID
ORDER BY ProductID
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
GO
--OUTPUT

SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 69 ms.

(265 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘PurchaseOrderDetail_Sample’. Scan count 3, logical reads 4100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 983 ms, elapsed time = 1158 ms.

Step 7 :
Lets execute the query and view the result set WITH the columnstore index.

USE AdventureWorks2012
GO
SET STATISTICS TIME ON 
SET STATISTICS IO ON
GO
SELECT
  ProductID as [Product ID]
, AVG(UnitPrice) as [Average Unit Price]
, SUM(OrderQty) as [Purchase Order Qty]
, AVG(ReceivedQty) as [Received Qty]
FROM [dbo].[PurchaseOrderDetail_Sample]
WHERE [DueDate] Between '2007-01-01' And '2008-12-31'
GROUP BY ProductID
ORDER BY ProductID
GO
--OUTPUT

SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 57 ms.

(271 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘PurchaseOrderDetail_Sample’. Scan count 1, logical reads 242, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 717 ms, elapsed time = 782 ms.

Conclusion :
As you can observe, there is huge difference between both queries (with and without columnstore index) performance. However, you need to test it in your scenario and implement it accordingly. In addition, kindly read the restrictions & limitation about columnstore index as well.

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 »

Older Posts »