Feeds:
Posts
Comments

Archive for the ‘Features’ Category

HASH index (NONCLUSTERED) is a new type of index introduced in SQL Server 2014. It is only supported by memory optimized table. Recently, I was working on a memory optimized table and I came across in a situation where I had to find all the hash indexes available in the database. As usual I got it from a system view (sys.indexes). It is nothing new but the story did not end here because I found a shorter way to get the list of non-clustered hash indexes.
Given below is script that can give you the list of all hash indexes available in any particular database.

USE hkNorthwind
GO
SELECT
object_schema_name([object_id]) As [Schema Name]
, object_name([object_id]) As [Table Name]
, [name] As [Index Name]
, [type_desc] As [Index Description]
FROM
sys.hash_indexes
--OUTPUT

list of hash indexes.1.1

Read Full Post »

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 »

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 »

Ability to return result sets from triggers is one of the features that is marked as a deprecated feature in SQL Server 2012.
After reading this, few questions flashed my mind, that I would like to share with you.

  • How do we return the result sets from triggers?
  • Why do we need this feature mostly?
  • How can we stop using this feature from earlier versions?
  • How can we get the same functionality without using this feature?

I added first two questions for the beginners to understand this concept.

Lets proceed step by step from the first question.

  • How do we return the result sets from triggers ?

Step 1 :
Lets create a table to demonstrate the use of return the result sets from trigger.

USE tempdb;
GO
CREATE TABLE tbl_Employee(
EmployeeID int identity (1,1),
EmployeeName nvarchar(100) NULL
)
GO

Step 2 :
Create a trigger on this table (tbl_Employee).

CREATE TRIGGER trg_Insert ON tbl_Employee
FOR INSERT
AS
SELECT INSERTED.EmployeeID, INSERTED.EmployeeName FROM inserted;
GO

Step 3 :
Now, lets insert one record and check if it is working properly.

INSERT INTO tbl_Employee VALUES('Imran')
GO
--OUTPUT

triggerresultset1.1

Woww, it is working, we got the result sets from the trigger 😉

  • Why do we need this feature mostly?

Mostly, we use select statement inside the trigger/ stored procedure to return the result set and the purpose of the result set is to debug (to check what values it returns) the trigger / stored procedure.
(Note : It is a common practice to debug any trigger/stored procedure via result set, but it may break your application and also it is not recommended).

  • How can we stop using this feature from earlier versions?

Step 4 :
We are enabling an advance feature ‘disallow results from triggers’ to 1 to enable this feature in the earlier version of SQL Server and it is also recommended by SQL Server to make it 1.

sp_configure 'disallow results from triggers',1
GO
RECONFIGURE

Step 5 :
Insert one more record in the table after enabling the feature and check that this feature has enabled properly or not.

INSERT INTO tbl_Employee VALUES('Bob')
GO
--OUTPUT

Ooopps…… I am unable to execute it, because I enabled the return result set feature from trigger at instance level not on database level
and it is working fine.
Msg 524, Level 16, State 1, Procedure trg_Insert, Line 4
A trigger returned a resultset and the server option ‘disallow_results_from_triggers’ is true.

  • How can we get the same functionality without using this feature?

Step 6 :
You can easily use the same functionality without using this feature. Let me alter the trigger and make a sample.

ALTER TRIGGER trg_Insert ON tbl_Employee
FOR INSERT
AS

Declare @EmployeeID int
Declare @EmployeeName nvarchar(100)

SELECT @EmployeeID=INSERTED.EmployeeID
, @EmployeeName=INSERTED.EmployeeName FROM inserted;

Print 'EmployeeID ' + Convert (varchar(10),@EmployeeID)
Print 'Employee Name ' + @EmployeeName
GO

Step 7 :
Insert one more record in the table and check the functionality.

INSERT INTO tbl_Employee VALUES('Sandra')
GO
--OUTPUT

EmployeeID 3
Employee Name Sandra

Conclusion :
DO NOT  use SELECT statement to return the result set from a stored procedure / trigger. Always use print command instead, to debug it.

Reference : MSDN

Read Full Post »

DATABASEPROPERTY (metadata function) is being discontinued since the emerge of SQL Server 2012 and it is replaced by another metadata function namely DATABASEPROPERTYEX. Both functions take parameters (database name, property name) and return property value on the basis of input parameters. But the major difference between both functions is the return type. DATABASEPROPERTY returns only int datatype while DATABASEPROPERTYEX returns the sql_variant datatype. In simple words, you cannot expect any other data type than int from DATABASEPROPERTY.
I did some research and found some additional difference that I would like to share.

Given below are the scripts, that is written for DATABASEPROPERTYEX & DATABASEPROPERTY to compare both meta data functions.

--Code for DATABASEPROPERTYEX
SELECT DATABASEPROPERTYEX('AdventureWorks2012','Collation')
AS [Collation],
DATABASEPROPERTYEX('AdventureWorks2012','ComparisonStyle')
AS [ComparisonStyle],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiNullDefault')
AS [IsAnsiNullDefault],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiNullsEnabled')
AS [IsAnsiNullsEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiPaddingEnabled')
AS [IsAnsiPaddingEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiWarningsEnabled')
AS [IsAnsiWarningsEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsArithmeticAbortEnabled')
AS [IsArithmeticAbortEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoClose')
AS [IsAutoClose],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoCreateStatistics')
AS [IsAutoCreateStatistics],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoShrink')
AS [IsAutoShrink],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoUpdateStatistics')
AS [IsAutoUpdateStatistics],
DATABASEPROPERTYEX('AdventureWorks2012','IsCloseCursorsOnCommitEnabled')
AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsFulltextEnabled')
AS [IsFulltextEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsInStandBy')
AS [IsInStandBy],
DATABASEPROPERTYEX('AdventureWorks2012','IsLocalCursorsDefault')
AS [IsLocalCursorsDefault],
DATABASEPROPERTYEX('AdventureWorks2012','IsMergePublished')
AS [IsMergePublished],
DATABASEPROPERTYEX('AdventureWorks2012','IsNullConcat')
AS [IsNullConcat],
DATABASEPROPERTYEX('AdventureWorks2012','IsNumericRoundAbortEnabled')
AS [IsNumericRoundAbortEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsParameterizationForced')
AS [IsParameterizationForced],
DATABASEPROPERTYEX('AdventureWorks2012','IsQuotedIdentifiersEnabled')
AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsPublished')
AS [IsPublished],
DATABASEPROPERTYEX('AdventureWorks2012','IsRecursiveTriggersEnabled')
AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsSubscribed')
AS [IsSubscribed],
DATABASEPROPERTYEX('AdventureWorks2012','IsSyncWithBackup')
AS [IsSyncWithBackup],
DATABASEPROPERTYEX('AdventureWorks2012','IsTornPageDetectionEnabled')
AS [IsTornPageDetectionEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','LCID')
AS [LCID],
DATABASEPROPERTYEX('AdventureWorks2012','Recovery')
AS [Recovery],
DATABASEPROPERTYEX('AdventureWorks2012','SQLSortOrder')
AS [SQLSortOrder],
DATABASEPROPERTYEX('AdventureWorks2012','Status')
AS [Status],
DATABASEPROPERTYEX('AdventureWorks2012','Updateability')
AS [Updateability],
DATABASEPROPERTYEX('AdventureWorks2012','UserAccess')
AS [UserAccess],
DATABASEPROPERTYEX('AdventureWorks2012','Version')
AS [Version]
--Code for DATABASEPROPERTY
SELECT DATABASEPROPERTY('AdventureWorks2012','Collation')
AS [Collation],
DATABASEPROPERTY('AdventureWorks2012','ComparisonStyle')
AS [ComparisonStyle],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiNullDefault')
AS [IsAnsiNullDefault],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiNullsEnabled')
AS [IsAnsiNullsEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiPaddingEnabled')
AS [IsAnsiPaddingEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiWarningsEnabled')
AS [IsAnsiWarningsEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsArithmeticAbortEnabled')
AS [IsArithmeticAbortEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAutoClose')
AS [IsAutoClose],
DATABASEPROPERTY('AdventureWorks2012','IsAutoCreateStatistics')
AS [IsAutoCreateStatistics],
DATABASEPROPERTY('AdventureWorks2012','IsAutoShrink')
AS [IsAutoShrink],
DATABASEPROPERTY('AdventureWorks2012','IsAutoUpdateStatistics')
AS [[IsAutoUpdateStatistics]],
DATABASEPROPERTY('AdventureWorks2012','IsCloseCursorsOnCommitEnabled')
AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsFulltextEnabled')
AS [IsFulltextEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsInStandBy')
AS [IsInStandBy],
DATABASEPROPERTY('AdventureWorks2012','IsLocalCursorsDefault')
AS [IsLocalCursorsDefault],
DATABASEPROPERTY('AdventureWorks2012','IsMergePublished')
AS [IsMergePublished],
DATABASEPROPERTY('AdventureWorks2012','IsNullConcat')
AS [IsNullConcat],
DATABASEPROPERTY('AdventureWorks2012','IsNumericRoundAbortEnabled')
AS [IsNumericRoundAbortEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsParameterizationForced')
AS [IsParameterizationForced],
DATABASEPROPERTY('AdventureWorks2012','IsQuotedIdentifiersEnabled')
AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsPublished')
AS [IsPublished],
DATABASEPROPERTY('AdventureWorks2012','IsRecursiveTriggersEnabled')
AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsSubscribed')
AS [IsSubscribed],
DATABASEPROPERTY('AdventureWorks2012','IsSyncWithBackup')
AS [IsSyncWithBackup],
DATABASEPROPERTY('AdventureWorks2012','IsTornPageDetectionEnabled')
AS [IsTornPageDetectionEnabled],
DATABASEPROPERTY('AdventureWorks2012','LCID')
AS [LCID],
DATABASEPROPERTY('AdventureWorks2012','Recovery')
AS [Recovery],
DATABASEPROPERTY('AdventureWorks2012','SQLSortOrder')
AS [SQLSortOrder],
DATABASEPROPERTY('AdventureWorks2012','Status')
AS [Status],
DATABASEPROPERTY('AdventureWorks2012','Updateability')
AS [Updateability],
DATABASEPROPERTY('AdventureWorks2012','UserAccess')
AS [UserAccess],
DATABASEPROPERTY('AdventureWorks2012','Version')
AS [Version]

Given below is the output of both meta data functions and you can see that DATABASEPROPERTY will reveal the correct result whenever the return type of property value is int but on the other hand DATABASEPROPERTYEX will give you the result of any property value because its return type is sql_variant.
DatabaseProperty

Conclusion:
DATABASEPROPERTYEX is one of the handy metadata functions when we need to retrieve the property value on the basis of property and database name.
It also provides an additional info than DATABASEPROPERTY. Remember, DATABASEPROPERTY is discontinued in SQL Server 2012, so don’t use it in further developments. Also if you upgrade SQL Server earlier version to SQL Server 2012, change DATABASEPROPERTY to DATABASEPROPERTYEX in the script.

MSDN : Reference

Read Full Post »

Whenever you upgrade previous version of SQL Server to SQL Server 2012 you need to take care of few issues. In this article, I will discuss one of the important issues that is Create trigger with append clause. This clause will not stop you to upgrade it to SQL Server 2012 but it will break your scripts. But how ?
Let me explain it Step by Step with the simple examples.
Step 1 :
First create given below table and triggers on any database prior to SQL Server 2012.

CREATE TABLE dbo.Student
([Student_ID] INT,
[Student_Name] VARCHAR(100)
)
GO
CREATE TRIGGER dbo.FirstTrigger
ON dbo.Student
FOR INSERT
AS
BEGIN
PRINT 'First Trigger'
END
GO
CREATE TRIGGER dbo.SecondTrigger
ON dbo.Student
FOR INSERT
WITH APPEND
AS
BEGIN
PRINT 'Second Trigger'
END
GO

Step 2 :
Upgrade the database on SQL Server 2012. Once you upgrade it will not alert you.
Step 3 :
Immediately after upgrade, run the given below query to view if any with append clause is available in the trigger.

Select A.object_id,A.definition from sys.sql_modules A
Inner Join sys.triggers B On A.object_id =B.object_id
where definition like '%with append%'

withappend1.1-1

Step 4 :
In the above example, we have one trigger with append clause, so alter the trigger and remove the with append clause because it is no more required for the triggers in latest versions of SQL Server.

Now, you are ready to use this trigger in the SQL Server 2012.

In case, if you don’t follow step 3 and 4, lets see what may happen.

insert into student values (1,'Imran')
--OUTPUT

First Trigger
Msg 195, Level 15, State 1, Procedure SecondTrigger, Line 5
‘APPEND’ is not a recognized option.

Conclusion :
Remember, whenever you upgrade to SQL Server 2012, check WITH APPEND clause  in the triggers to avoid this error.

Read Full Post »

In my last post, I had written about the new feature SEQUENCE in SQL SERVER 2012. But in this post, I would like to write the difference between SEQUENCE and IDENTITY in SQL Server 2012 with examples. I hope this example will make these two concepts more clear.
Given below are the differences along with the sample source code:

S.No

Identity

Sequence

1

Dependant on table.

Independent from table.

2

Identity is a property in a table.

Example :

CREATE TABLE Table
test_Identity

(

[ID] int Identity (1,1),

[Product Name] varchar(50)

)

 

 

Sequence is an object.

Example :

CREATE SEQUENCE [dbo].[Sequence_ID]

AS [int]

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 1000

NO CYCLE

NO CACHE

3

If you need a new ID from an identity column you need to
insert and then get new ID.

Example :

Insert into [test_Identity] Values (‘SQL Server’)

GO

SELECT @@IDENTITY AS ‘Identity’

–OR

Select SCOPE_IDENTITY() AS ‘Identity’

In the sequence, you do not need to insert new ID, you can view the new ID directly.

Example :

SELECT NEXT VALUE
FOR dbo.[Sequence_ID]

 

 

4

You cannot perform a cycle in identity column. Meaning, you cannot restart the counter after a
particular interval.

 

In the sequence, you can simply add one property to make it a cycle.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CYCLE;

5

You cannot cache Identity column property.

Sequence can be easily cached by just setting cache property of
sequence. It also improves the performance.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

CACHE 3;

6

You cannot remove the identity column from the table directly.

The sequence is not table dependent so you can easily remove it

Example :

Create table dbo.[test_Sequence]

(

[ID] int,

[Product Name] varchar(50)

)

GO

–First Insert With Sequence object

INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (NEXT VALUE FOR [Ticket] , ‘MICROSOFT SQL SERVER 2008’)

GO

–Second Insert without Sequence

INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (2 , ‘MICROSOFT SQL SERVER 2012’)

7

You cannot define the maximum value in identity column it is
based on the data type limit.

Here you can set up its maximum value.

 

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

MAXVALUE 2000;

8

You can reseed it but cannot change the step size.

Example :

DBCC CHECKIDENT (test_Identity, RESEED, 4)

 

You can reseed as well as change the step size.

Example :

ALTER SEQUENCE [dbo].[Sequence_ID]

RESTART WITH 7

INCREMENT BY 2;

9

You cannot generate range from identity.

You can generate a range of sequence
values from a sequence object
with the help of sp_sequence_get_range.

Read Full Post »