Feeds:
Posts
Comments

Archive for the ‘Database Features’ Category

Adding multiple files in a single filestream filegroup was a very nice & helpful feature shipped with SQL Server 2012. Now, you can easily organize your data under multiple files in the same filestream filegroup. Also, it is not necessary that you place all of them in the same location. You can place them in multiple locations to divide the load as well.

Let me create a sample database to demonstrate. Given below is the script.
Note : Before executing the below script, kindly create a Data folder in C: drive.

USE [master]
GO
CREATE DATABASE [Sample_DB]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'Sample_DB',
FILENAME = N'C:\Data\Sample_DB.mdf',
SIZE = 500MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB
),
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT
(
NAME = N'Filestream1',
FILENAME = N'C:\Data\FileStream1',
MAXSIZE = UNLIMITED
)
LOG ON
(NAME = N'Sample_DB_log'
,FILENAME = N'C:\Data\Sample_DB_log.ldf',
SIZE = 500MB,
MAXSIZE = 1GB,
FILEGROWTH = 10MB
)
GO

Let me add an additional file to the same filegroup. Given below is the script.

ALTER DATABASE [Sample_DB]
ADD FILE
(
NAME = N'Filestream2',
FILENAME = N'C:\Data\Filestream2',
MAXSIZE = 100MB
)TO FILEGROUP [FS]
GO

Lets check if the file has been created or not. Given below is the script.

Select * from sys.master_files where database_id =db_id('Sample_DB')
GO

multiplefilestream1.1

Let me know if you have done this in real world and what was the solution.

Read Full Post »

Sequence object is one of the new additions in SQL Server 2012. It has lot of functionality plus identity column functionality as well. However, there are certain limitations and one of the limitations is, you can only create sequence object in number datatype. Given below is the screen image.

varcharsequence1.1

So, how can we create a sequence object with varchar datatype ?
Lets do it step by step.

Step 1 :
In this step, we need to create a sample table and a sequence to demonstrate it.

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

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

Step 2 :
In this step, we need to create a default value for the [ID] column of the above table, and the default value of the [ID] column should be SEQUENCE and add a custom script to make it varchar.

Given below is the script.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE DEFAULT VALUE OF SEQUENCE
USE tempdb
GO
ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_Sample_Seq
DEFAULT FORMAT((NEXT VALUE FOR dbo.Sample_Seq),'CUS0000#') FOR [ID];
GO

Step 3 :
Lets insert few records into the table.

-- This script is compatibile with SQL Server 2012 and above.
-- Insert records into the table
USE tempdb
GO
INSERT INTO dbo.tbl_sample ([Name])
VALUES ('Imran'),('Bob'),('Sandra')
GO

Step 4 :
Once the data has been inserted, you can browse the table and view the [ID] column data that it is either a number or varchar only.

--Browse Table
USE tempdb
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

varcharsequence1.2

Conclusion :
As you can see, the [ID] column has varchar data instead of number. Remember, whenever you need to do generate sequence in varchar format, you must format the sequence object.

Read Full Post »

In Part 1 of Semantic Search Implementation that I published yesterday, I wrote about how to install and configure semantic search in SQL Server 2012. Today in Part 2, we will learn how to implement semantic search and will demonstrate how beneficial it is.

Prerequisite :

Given below are the two articles that you should read to have clear understanding about Semantic Search Implementation.

  1. Install & Configure Semantic Search
  2. File Table

Lets Implement it step by step.

Step 1: Create database

USE master
GO
CREATE DATABASE [SampleDB]
ON PRIMARY
( NAME = N'SampleDB',
FILENAME = N'C:\DATA\SampleDB.mdf'
),
FILEGROUP SampleFileGroup CONTAINS FILESTREAM
(NAME = SampleFileTableFS,
FILENAME='C:\Data\SampleFileTable'
)
LOG ON
( NAME = N'SampleDB_log',
FILENAME = N'C:\DATA\SampleDB_log.ldf'
)
WITH FILESTREAM
( NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'SampleFileTableDB'
)
GO

Step 2: Create FileTable

USE SampleDB
GO
CREATE TABLE CVBank AS FileTable
WITH
(
FileTable_Directory = 'CVFileTable_Dir'
);
GO

Step 3: Populate data into FileTable
In this step, we will insert few Cvs in the file table.

USE SampleDB
GO
INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT'Muhammad Imran -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Muhammad Imran -CV.doc', SINGLE_BLOB
) AS FileData
GO

INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT 'Salman Tahir -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Salman Tahir -CV.doc', SINGLE_BLOB
) AS FileData
GO

INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT 'Syed Kamran Ali -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Syed Kamran Ali -CV.doc', SINGLE_BLOB
) AS FileData
GO

Step 4: Create full text search on FileTable

Right click on the file table point to full-Text index then select Define Full-Text Index… Further details to create Full Text Index is available here
semantic_search_part2.1.1

Now, we are prepared to use semantic search. Lets create few examples.

Example 1: Search Sharepoint Developer CVs
As I mentioned above that, I populated some CVs in the file table, now it is time to search Cvs with certain criteria using semantic search.
In this example, I will search candidates having knowledge of sharepoint.

USE SampleDB
GO
SELECT
D.name
,KEYP_TBL.score
,KEYP_TBL.Keyphrase
FROM dbo.CVBank D
INNER JOIN SEMANTICKEYPHRASETABLE
(
dbo.CVBank,(name,file_stream)
) AS KEYP_TBL
ON D.path_locator = KEYP_TBL.document_key
WHERE KEYP_TBL.keyphrase like '%sharepoint%'
ORDER BY KEYP_TBL.score DESC
--OUTPUT

semantic_search_part2.1.2

Example 2: Search SQL Developer CVs
In this example, I will search candidates having knowledge of SQL.

USE SampleDB
GO
SELECT
D.name
,KEYP_TBL.score
,KEYP_TBL.Keyphrase
FROM dbo.CVBank D
INNER JOIN SEMANTICKEYPHRASETABLE
(
dbo.CVBank,(name,file_stream)
) AS KEYP_TBL
ON D.path_locator = KEYP_TBL.document_key
WHERE KEYP_TBL.keyphrase like '%sql%'
ORDER BY KEYP_TBL.score DESC
--OUTPUT

semantic_search_part2.1.3
Conclusion :
Semantic Search is very handy if you need to develop some solution on the basis of unstructured document. Shall post another nice feature of semantic search in my upcoming post.

Read Full Post »

In the earlier versions of SQL Server, it was very difficult to view the unstructured documents placed in the SQL Server. But in SQL Server 2012, with the solution shipped namely Semantic Search not only can you easily look inside in the unstructured documents but also you can create phrase table to find key phrases, similar documents, related documents as well. This feature gave boost to another newly introduced feature namely File Table in SQL Server 2012. You must install and configure Semantic Search before you use it.
Let’s install / configure it Step by Step.

Step 1:
In order to install Semantic Search, first of all you need to check whether FULL TEXT SEARCH feature is installed in your SQL Server or not. Because Semantic search feature is based on Full text search feature.
Given below is the script to check this.

USE master
GO
SELECT SERVERPROPERTY('IsFullTextInstalled') AS [Result]
GO
--OUTPUT

Result
1

(1 row(s) affected)

Step 2:
If result of step 1 is 1 then you should not do anything in Step 2. But if the result is 0, you must run the SQL Server setup again, go to Feature Selection option, select Full-Text and Semantic Extractions for search and install this feature (as shown in the picture below).

Install Semantic 1.2

Step 3:
Once you install the Full-Text and Semantic Extractions for search feature, you need to check whether Semantic Language Statistics Database (One of the core dependency of Semantic searches) is installed or not. Given below is the script to check.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Install Semantic 1.3

Ooooppss……… as you can see, the output says blank; it means semantic language statistics database is not yet installed. So you need to install it.

Step 4:

To install semantic language statistics database, you need to go to SQL Server installation media and browse the given below folder and run SemanticLanguageDatabase.msi

• For x86 : …\x86\Setup\SemanticLanguageDatabase.msi
• For x64 : …\x64\Setup\SemanticLanguageDatabase.msi

Given below is the screen image.

Install Semantic 1.4

Step 5:

Once you execute the above SemanticLanguageDatabase.msi and install it properly, it will give you two database files (semanticsDB, semanticsdb_log) in a location (C:\Program Files\Microsoft Semantic Language Database), in case you install it in the default location. Now you have database files, just attach these files in your SQL Server.

Install Semantic 1.5

Step 6:
Once you attach these database files in your SQL Server, then you have to register semantic language database. Given below is the script.

EXEC sp_fulltext_semantic_register_language_statistics_db
@dbname = N'semanticsdb'
GO

Step 7:
Again, you need to check whether the semantic language statistics database is installed or not. Given below is the script.

SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Install Semantic 1.6

Now, you can see that the semantic language statistics database is installed.

In my next article, I will discuss how to implement semantic search on file tables and make your life easier.

Reference : MSDN

Read Full Post »

Today, I was working on contained database and came across with an issue (will post this issue in my upcoming post), where I had to find a list of uncontained objects (As per MSDN, uncontained objects are basically those objects that cross the database boundaries in a contained database.)

Also, as per MSDN, given below are the objects that usually cross the boundaries of the database:

  • Unknown containment behavior (dynamic SQL or deferred name resolution)
  • DBCC command
  • System stored procedure
  • System scalar function
  • System table valued function
  • System built-in function

But it is quiet difficult, if you search for any of the above objects in the database one by one. SQL Server resolves this issue by releasing a dynamic management view (sys.dm_db_uncontained_entities) that will give you all uncontained objects of any database, in one shot.

Let me create an uncontained objects in a contained database.

--Create a stored procdure by using sys.allobjects
USE AdventureWorks2012
GO
CREATE PROC test_Procedure
AS
SELECT * FROM sys.all_objects
GO

Once you created the above stored procedure, just execute the given below script it will give you the name of above procedure along with other uncontained objects.

 Use AdventureWorks2012
GO
SELECT O.name
,O.type_desc
,UCE.class_desc
,UCE.statement_type
,UCE.feature_name
,UCE.feature_type_name
FROM sys.dm_db_uncontained_entities AS UCE
LEFT JOIN sys.objects AS O
ON UCE.major_id = O.object_id
--OUTPUT

dm_db_uncontained_entities1.1-1

Note : If the above query return Nil(or emplty) it means that you donot have any uncontained objects.

Read Full Post »

File table is one of my favorite features introduced in SQL Server 2012. When I was working with file table, I came across a question how to find all system objects (primary key, default vaule, indexes etc) related to any file table.

We can achieve this by two different methods.

 Method 1:

In this method, we did self-join in the sys.objects to find all system objects related to any file table. This is general script that we normally use to find any child object related to parent object.

--This script will work in SQL Server 2012 and above.
Declare @FileTableName as varchar(50)='dbo.Databank'
-- Enter File table Name Here
Select B.[name] as [File Table Name]
,A.[name] as [Related objects]
from sys.objects A
Inner Join sys.objects B
On A.[parent_object_id] =B.[object_id]
Where B.[type] ='U'
And B.object_id=object_id(@FileTableName)
GO
--OUTPUT

filetablesystemobjects1.1

 Method 2 (Shortcut):

In this method, we used a new system view namely sys.filetable_system_defined_objects (This system view returns all the system objects related to any file table) introduced in SQL Server 2012.
Given below is the script that will give you all system objects related to file table without any self-join.

--This script will work in SQL Server 2012 and above.
Declare @FileTableName as varchar(50)='dbo.Databank'
Select object_name(parent_object_id) as [File Table Name]
,object_name(object_id) as [Related objects]
from sys.filetable_system_defined_objects
Where parent_object_id =object_id(@FileTableName)
GO
--OUTPUT

filetablesystemobjects1.2

Aware of any other shortcut ?

Read Full Post »

Few days ago, I was working on columnstore Index and I came across a problem finding the size of all columnstore Indexes on disk in a particular database.
I have given below the script which can help you find the size of a single columnstore index on disk or all columnstore indexes in a particular database by using
sys.column_store_segments, sys.column_store_dictionaries.

Use AdventureWorks2012
Go
Declare @TableName as nvarchar(Max)
--If you need columnstore index size for one table
--Change the @TableName parameter from NULL to table name
Set @TableName =NULL --'SalesOrderDetail_Sample'

;With CTE AS (
SELECT i.object_id
,i.name as [Index_Name]
,SUM(c.on_disk_size)/(1024.0*1024)
As [Columstore_Index_size_on_disk_size (In MBs)]
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_segments AS c
ON c.hobt_id = p.hobt_id
WHERE i.type_desc = 'NONCLUSTERED COLUMNSTORE'
AND ((1=(CASE WHEN @TableName is Null THEN 1 ELSE 0 END)
OR i.object_id = object_id(@TableName)))
GROUP BY i.object_id,i.name

UNION ALL

SELECT i.object_id
,i.name as [Index_Name]
,SUM(c.on_disk_size)/(1024.0*1024)
As [Columstore_Index_size_on_disk_size (In MBs)]
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_dictionaries AS c
ON c.hobt_id = p.hobt_id
WHERE i.type_desc = 'NONCLUSTERED COLUMNSTORE'
AND ((1=(CASE WHEN @TableName is Null THEN 1 ELSE 0 END)
OR i.object_id = object_id(@TableName)))
GROUP BY i.object_id,i.name )

Select object_id,object_name(object_id) as [Table_Name]
,[Index_Name]
,SUM([Columstore_Index_size_on_disk_size (In MBs)])
AS [Columstore_Index_size_on_disk_size (In MBs)] from CTE
Group By object_id,[Index_Name]

Columnstoreindexsize1.1

Reference : MSDN

Read Full Post »

Performance plays a vital role in SQL server. The importance of performance increases with the rise in the data growth. In other words, increase in data growth is directly proportional to the increase in performance. In SQL Server, index plays an important role to increase the performance as well. In SQL server 2012, we got a boost in performance (a new type of index came) namely ColumnStore Index. It is one of the nicest database features introduced in SQL Server 2012 built on xVelocity technology which is used to optimize memory. Lets discuss its syntax , purpose, examples & performance.

Syntax:
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name

ON ( column [ ,…n ] )

[ WITH ( [ ,…n ] ) ]

[ ON {

{ partition_scheme_name ( column_name ) }

| filegroup_name

| “default”

}

]

[ ; ]

::=
{
[database_name. [schema_name ] . | schema_name . ]
table_name
{

::=
{
DROP_EXISTING = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}

Purpose :

The purpose of ColumnStore Index is to increase the performance in comparison to the traditional Index. It is a number of times faster than traditional index. The reason behind its performance is the way it handles the index. In Tradition Index, it stores multiple rows in each page and it retrieves in the same way. In ColumnStore Index, it stores each column in separate page and it retrieves in the same way.
In simple words you can say Tradition Index is a Row store while ColumnStore Index is a Column store.

Graphical Representation:
Given below is the graphical representation of traditional index & ColumnStore Index and you can see how each technology keeps the data pages in a different manner.

columnstoreindex1.1

Let’s test the performance of ColumnStore index Step by Step.

Step 1 :
Create a sample table.

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)

Step 2 :
Insert some data into table (PurchaseOrderDetail_Sample), because if table has few records you cannot measure the performance of columnstore index properly.

--This insertion is just to demonstrate,
--It is not recommended on production server.
Insert into dbo.[PurchaseOrderDetail_Sample]
Select * from [Purchasing].[PurchaseOrderDetail]
GO 100

Step 3 :
Next step is to create a Columnstore index in this table.

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

Step 4 :
Now, it is time to create a query using this table and view the performance. Remember this query has a ColumnStore Index.

SET STATISTICS TIME ON
SET STATISTICS IO ON
--This query will use columnstore index and will return the result set.
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]
GROUP BY ProductID
ORDER BY ProductID

go
--This query will ignore columnstore index and will return the result set.
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]
GROUP BY ProductID
ORDER BY ProductID
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

Step 5 :
Lets compare the result of IO & Time Statistics for both, with columnstore index and without columnstore index.

Type

Logical Reads

CPU Time

Elapsed Time

ColumnStore

34

47 ms

342 ms

Without ColumnStore

8345

1482 ms

1473 ms

ColumnStore1.2

Execution Plan comparison :

ColumnStore1.3

Index Scan comparison :
ColumnStore1.4

Conclusion :
I am quite impressed to see the performance of Columnstore index. It is very handy and recommended in a place where you are either facing performance issues with the query or dealing with data warehouse. However, Columnstore index has some limitations as well which I will post in my upcoming post.

Read Full Post »

In my last article, I had discussed how to convert a table from identity to sequence (manually). In this article, I would be sharing a script that I developed for one of my customers, to convert identity to sequence automatically. I presume that identity column belongs to integer data type family (small int, int, big int).
Note: Please test the script first on test database.

Given below is the example that we need to create, to understand the conversion:

Create table dbo.[Student]
(
[StudentID] int identity(1,1),
[StudentName] varchar(50)
CONSTRAINT PK_Student_StudentID PRIMARY KEY CLUSTERED
([StudentID] ASC)
)
GO
Insert into dbo.Student values ('Imran'),('Bob'),('Sandra')
GO
Select * from dbo.Student

identity_2_sequence1.1

Now, create the given below stored procedure to convert it automatically.

CREATE PROCEDURE Convert_Identity_To_Sequence_Proc
@Scheme_And_TableName nvarchar(Max)
AS

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @OBJECTID AS BIGINT
DECLARE @IDENTITY_COLUMN_NAME AS NVARCHAR(MAX)
DECLARE @DATA_TYPE_NAME AS SYSNAME
DECLARE @CONSTRAINT_NAME AS NVARCHAR(MAX)
DECLARE @Max_identity_ID AS BIGINT
DECLARE @ParmDefinition AS NVARCHAR(MAX)
DECLARE @TABLE_NAME AS NVARCHAR(MAX)

--Pick up object ID of the table
SELECT @OBJECTID=OBJECT_ID(@Scheme_And_TableName)
--Seperate Table name from the schema
SET @TABLE_NAME =SUBSTRING(@Scheme_And_TableName,CHARINDEX('.',@Scheme_And_TableName)+1,LEN(@Scheme_And_TableName))
Print @TABLE_NAME

--Check if the table has an identity table
If (Select Count(*) from sys.identity_columns where object_id =@OBJECTID)=0
BEGIN
RAISERROR('Could not found the identity column in this table',16,1)
RETURN
END
Print @OBJECTID

-- Pick identity column name , contraint name and data type name from the table.
SELECT @IDENTITY_COLUMN_NAME=A.name,@CONSTRAINT_NAME=B.name, @DATA_TYPE_NAME=D.name FROM sys.columns A
INNER JOIN sys.types D ON A.system_type_id =D.system_type_id
LEFT JOIN sys.indexes B ON A.object_id =B.object_id
LEFT JOIN sys.index_columns C ON B.object_id =C.object_id
AND B.index_id =C.index_id
AND A.column_id =C.column_id
WHERE A.is_identity =1 And A.object_id =@OBJECTID

Print @IDENTITY_COLUMN_NAME
Print @CONSTRAINT_NAME
Print @DATA_TYPE_NAME

-- Add a new column in the table that does not have the IDENTITY property with the same data type
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName +' ADD ' +@IDENTITY_COLUMN_NAME + 'New ' + @DATA_TYPE_NAME + ' NULL'
Print @SQL
EXEC (@SQL)

-- Copy values from the old column and update into the new column
SET @SQL ='UPDATE ' + @Scheme_And_TableName +' SET ' + @IDENTITY_COLUMN_NAME + 'New' + ' =' + @IDENTITY_COLUMN_NAME
Print @SQL
EXEC (@SQL)

-- Drop the primary key constraint from the old identity column
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName + ' DROP CONSTRAINT ' + @CONSTRAINT_NAME
Print @SQL
EXEC (@SQL)

-- Drop the old identity column
SET @SQL =' ALTER TABLE ' + @Scheme_And_TableName + ' DROP COLUMN ' + @IDENTITY_COLUMN_NAME
Print @SQL
EXEC (@SQL)

-- Rename the new column to the old columns name
SET @SQL ='EXEC sp_rename ' + ''''+ @Scheme_And_TableName + '.'+ @IDENTITY_COLUMN_NAME+'New' + '''' + ',' + ''''+ @IDENTITY_COLUMN_NAME + '''' + ',' + '''COLUMN'''
Print @SQL
EXEC (@SQL)

-- Change the new column to NOT NULL
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName + ' ALTER COLUMN ' + @IDENTITY_COLUMN_NAME + ' ' + @DATA_TYPE_NAME + +' NOT NULL'
Print @SQL
EXEC (@SQL)

-- Add the unique primary key constraint again with the same name
IF @CONSTRAINT_NAME IS NOT NULL
BEGIN
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName + ' ADD CONSTRAINT ' + @CONSTRAINT_NAME +' PRIMARY KEY CLUSTERED (' + @IDENTITY_COLUMN_NAME +' ASC)'
Print @SQL
EXEC (@SQL)
END
ELSE
BEGIN
PRINT 'NO CONSTRAINT'
END
-- Get the highest current value from the column
-- to use it for sequence creation.
SET @SQL ='SELECT @Max_ID=MAX(' + @IDENTITY_COLUMN_NAME + ') FROM ' + @Scheme_And_TableName
SET @ParmDefinition = N'@Max_ID ' + @DATA_TYPE_NAME + ' OUTPUT'
Print @SQL
Print @ParmDefinition
EXECUTE sp_executesql @SQL, @ParmDefinition,@Max_ID=@Max_identity_ID OUTPUT;
Print @Max_identity_ID

-- Use the next Identity value as the START WITH VALUE;
SET @SQL ='CREATE SEQUENCE ' + @Scheme_And_TableName + 'Seq' +
' AS ' + @DATA_TYPE_NAME +
' START WITH ' + try_Convert(varchar(max),@Max_identity_ID+1) +
' INCREMENT BY 1 ' ;
Print @SQL
EXEC (@SQL)

-- Add a default value of sequence to the column
SET @SQL ='ALTER TABLE ' + @Scheme_And_TableName + ' ADD CONSTRAINT Def' + @TABLE_NAME + ' DEFAULT (NEXT VALUE FOR ' + @Scheme_And_TableName + 'Seq)
FOR ' + @IDENTITY_COLUMN_NAME;
Print @SQL
EXEC (@SQL)

GO
--Execute the stored procedure and provide schema and table name as a parameter.
EXEC Convert_Identity_To_Sequence_Proc 'dbo.Student'

Insert few records and check whether the table is properly converted from identity to sequence or not.

Insert into Student (StudentName) Values ('Mark')
Insert into Student (StudentName) Values ('Peter')
Select * from dbo.Student
--OUTPUT

identity_2_sequence1.2

Reference : MSDN

Read Full Post »

In my earlier article, I wrote about sequence. In this article, we will learn how to convert an identity column to sequence, step by step. Original script is available here (MSDN)
Given below is the example that we need to create, to understand the steps :

Create table dbo.[Student]
(
[StudentID] int identity(1,1),
[StudentName] varchar(50)
CONSTRAINT PK_Student_StudentID PRIMARY KEY CLUSTERED
([StudentID] ASC)
)
GO
Insert into dbo.Student values ('Imran'),('Bob'),('Sandra')
GO
Select * from dbo.Student

identity_2_sequence1.1

Step :1
First of all, we need to add another column in the table with the same data type as identity column.

ALTER TABLE dbo.Student ADD StudentIDNew int NULL
GO

Step :2
Update the IDs from identity column to the newly added column.

UPDATE dbo.Student
SET [StudentIDNew] = [StudentID]
GO

Step :3
Drop the primary key constraint from the identity column of the table.

ALTER TABLE dbo.Student
DROP CONSTRAINT PK_Student_StudentID;
GO

Step :4
Drop the identity column from the column.

ALTER TABLE dbo.Student
DROP COLUMN [StudentID] ;
GO

Step :5
Rename the new column name to the old identity column name.

EXEC sp_rename 'dbo.Student.StudentIDNew',
'StudentID', 'COLUMN';
GO

Step :6
Change the new identity column to NOT NULL.

ALTER TABLE dbo.Student ALTER COLUMN [StudentID] int NOT NULL ;
GO

Step :7
Add primary key constraint back to the table.

ALTER TABLE dbo.Student
ADD CONSTRAINT PK_Student_StudentID PRIMARY KEY CLUSTERED
([StudentID] ASC) ;
GO

Step :8
Now, we need to create the sequence but before sequence creation, we need to know the last ID of the identity column.

SELECT MAX(StudentID) FROM dbo.Student ;
GO

Step :9
Create the sequence using the maximum ID +1 and the same data type of the identity column.

CREATE SEQUENCE dbo.StudentSeq
AS int
START WITH 4
INCREMENT BY 1 ;
GO

Step :10
Make the sequence as a default value of the column.

ALTER TABLE dbo.Student
ADD CONSTRAINT Const_StudentSeq DEFAULT (NEXT VALUE FOR dbo.StudentSeq)
FOR StudentID;
GO

Step :11
Insert few records and check whether the table is properly converted from identity to sequence or not.

Insert into Student (StudentName) Values ('Mark')
Insert into Student (StudentName) Values ('Peter')
Select * from dbo.Student
--OUTPUT

identity_2_sequence1.2

Shall discuss how to change a table from identity to sequence automatically in my next post.

Read Full Post »

« Newer Posts - Older Posts »