Feeds:
Posts
Comments

Archive for the ‘Solutions’ Category

How to restore SQL SERVER 2012 backup on earlier versions of SQL Server

Posted in Solutions, SQL SERVER, tagged , , , , , on October 23, 2013| 2 Comments »

‘Can you restore SQL Server 2012 backup in earlier versions of SQL Server?’ I came across this question many times from the community and the simple answer is NO. You cannot restore a new version of SQL Server backup in earlier versions. However, there is a way around that can be followed to achieve it. Basically, you can generate the script of all objects along with the data using SQL Server 2012 with any earlier versions compatibility and run those scripts on earlier versions of SQL Server. This is how you can get the complete data with schema from SQL Server 2012 to earlier versions.

Problem :
Before proceeding with the solution, I would like to show you the error, that happens when you restore a higher versions of
SQL Server database backup to an earlier version of SQL Server. Given below is the image.

erromessage33241.1

The media family on device ‘E:\DBBackup\AdventureWorks2012_Backup\AdventureWorks2012_Backup’ is incorrectly formed. SQL Server cannot process this media family.

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

SOLUTION :
Let me proceed with the solution, step by step.

Step 1:
Open SSMS and right click on the database that you want to restore in earlier versions of SQL Server and then select Task and then further click on Generate Script …
Given below is the image.
Restore SQL Server 2012 backup on earlier versions.1.1

Step 2 :
Given below is the informational screen, that will guide you how to generate the scripts along with the steps. Press NEXT button.
Restore SQL Server 2012 backup on earlier versions.1.2

Step 3 :
In this step, you will get two options. Given below are the details.

  1. Script entire database and all database objects
  2. Select specific database objects

Option 1 is preferred because you can generate all database objects altogether from it. However if you upgrade any object using any new features of SQL Server 2012, then you should select Option 2 and exclude that particular object and press NEXT button. Given below is the screen image.

Restore SQL Server 2012 backup on earlier versions.1.3-1

Step 4 :
In this option you need to select the script file location to save all the scripts in a particular file. Rest of the options you can leave as default. Once you are done with the file location, you can see an ADVANCE button. Click on this button. It will open the advance option for the scripting.
Restore SQL Server 2012 backup on earlier versions.1.4

Step 5 :
Once you are inside the Advance option, you need to select further two options. Given below are the details.

  1. Script for SQL Server version
  2. Type of data to script.

In the Option 1 make sure that you have selected the right earlier Version of SQL Server, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.5

In the Option 2, make sure that you have selected Schema and data, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.6-1

Once you configure both options, press OK and press Next in the previous screen, as shown below.

Restore SQL Server 2012 backup on earlier versions.1.7

Step 6
Once you press NEXT button, it will take you to the summary screen where you can see all your configuration altogether.
Again press NEXT button to proceed, as show in the image below.

Restore SQL Server 2012 backup on earlier versions.1.8

Step 7
In this step, SQL Server will automatically script all your selected object along with the data. And will give you the status report, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.9

Restore SQL Server 2012 backup on earlier versions.1.10

Step 8 :
Now you need to browse the file you saved in particular location in the Step 4 and Execute it in selected earlier version of SQL Server.

Let me know if you came across this problem and resolved it in a different manner.

Read Full Post »

SQL SERVER – How to implement conditional where clause

Posted in Solutions, SQL SERVER, tagged , , , on October 8, 2013| 2 Comments »

Few days ago, I received a query from one of my blog readers inquiring how to implement a conditional where clause in a SQL Server query / stored procedure. His requirement was quiet simple. He said that if he passes any filter value to the query / procedure, it should consider that filter otherwise query should ignore that filter and return all records.

Given below is the script.
SAMPLE :

USE AdventureWorks2012
GO
--DROP PROCEDURE Usp_sample
--GO
CREATE PROCEDURE Usp_sample
@City nvarchar(30),
@CountryRegionName nvarchar(50)
AS

SELECT *
FROM
HumanResources.vEmployee
WHERE (1=(CASE WHEN @City IS NULL THEN 1 ELSE 0 END) Or [City]=@City)
AND (1=(CASE WHEN @CountryRegionName IS NULL THEN 1 ELSE 0 END)
Or [CountryRegionName]=@CountryRegionName)
GO

EXAMPLE 1 : (WITHOUT any filter)
In this example, I did not pass any filter to the stored procedure, so the stored procedure ignored all filters and it returned all records as expected.

USE AdventureWorks2012
GO
EXEC Usp_sample @City=NULL,@CountryRegionName=NULL
GO

EXAMPLE 2 : (With ONE filter)
In this example, I passed only one filter to the stored procedure and it returned records on the basis of only one filter and ignored the second filter.

USE AdventureWorks2012
GO
EXEC Usp_sample @City='Renton',@CountryRegionName=NULL
GO

Read Full Post »

SQL SERVER 2012 – How to check Sequence has reached its minimum or maximum value

Posted in Solutions, SQL SERVER, tagged , , , , , , on October 5, 2013| Leave a Comment »

Recently I came across an issue related to the sequence object (shipped in SQL Server 2012) because usually we generate the next ID from the sequence object without checking whether it has reached its limit and it cannot generate a new ID. Due to this it generates an error as well. Given below are the error details.

Msg 11728, Level 16, State 1, Line 1
The sequence object ‘Seq_Sample1’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. Sequence object maintain its minimum and maximum value by default.

So what is the solution ? But before proceeding with the solution let me create a sample.

SAMPLE :

--This script is compatible with SQL Server 2012 and above.
--Create a sequence object
USE [AdventureWorks2012]
GO
--DROP SEQUENCE [dbo].[Seq_Sample1]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample1]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
GO
--Generate sequence number up to 4 ID
--because it has a maximum limit of 4.
SELECT Next value for [dbo].[Seq_Sample1]
GO 4

SOLUTION 1 : (Not recommended)
In this solution, you need to check the minimum and maximum value of the sequence with the current value and if it matches either of them, it means that sequence has been reached to its minimum or value and you cannot generate a new ID. It is not recommended because it is a lengthy procedure. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @minimum_value As sql_variant
DECLARE @maximum_value As sql_variant
DECLARE @current_value As sql_variant

SELECT
@minimum_value=[minimum_value]
,@maximum_value=[maximum_value]
,@current_value=[current_value]
FROM
sys.sequences AS seq
WHERE name ='Seq_Sample1'

SELECT IIF(@minimum_value=@current_value
OR @maximum_value=@current_value
,'You cannot generate a new ID'
,'You can generate a new ID') AS [Result]
--OUTPUT

sequence limit.1.1

SOLUTION 2 : Using Is_exhausted property (Recommended)
In this solution, we will use a sequence property namely “is_exhausted” and can quickly check whether it reached its limit or not.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT IIF((SELECT is_exhausted FROM sys.sequences AS seq
WHERE name ='Seq_Sample1')=1
,'You cannot generate a new ID'
,'You can generate a new ID')
AS [Result]
--OUTPUT

sequence limit.1.1

CONCLUSION :
As you can see, both solutions generate the same output but solution 2 is recommended because we used a property namely “is_exhausted” and reduced the size of the script.

Read Full Post »

SQL SERVER – How do I get only the numbers before the decimal?

Posted in Solutions, SQL SERVER, tagged , , , , , on October 3, 2013| 1 Comment »

I came across this query today, when I was generating some reports and I do not want to show the decimals in the report. I did some research and came up with multiple solutions. Before proceeding with the solution, let me create a sample to demonstrate the solution.

Sample :

USE Tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[Col_ID] INT,
[Col_Decimal] decimal(18,4)
)
GO
INSERT INTO tbl_sample VALUES (1,12345.9876)
INSERT INTO tbl_sample VALUES (2,-12345.9876)
INSERT INTO tbl_sample VALUES (3,123.45)
INSERT INTO tbl_sample VALUES (4,12.00)
GO

Given below are the solutions.

Solution 1 : (Without any function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, [Col_Decimal] - ([Col_Decimal]%1) As [Col_Number]
FROM tbl_sample
GO
--OUTPUT

get the number before decimals.1.1

Solution 2 : (Using ROUND function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, ROUND([Col_Decimal],0,1) As [Col_Number]
FROM tbl_sample
GO
--OUPUT

get the number before decimals.1.2

Solution 3 : (Using CAST function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, CAST([Col_Decimal] AS INT) As [Col_Number]
FROM tbl_sample
GO
--OUTPUT

get the number before decimals.1.3

Read Full Post »

SQL SERVER 2012 – How to check date is End of Month date or not?

Posted in Solutions, SQL SERVER, tagged , , , , , on September 30, 2013| 3 Comments »

Today is month end (30 September 2013) and I was checking my general checklist for month end closing procedure. I saw one of my scripts that I had written earlier regarding how to check whether a date is month end date or not. So, I thought of re-writing this script even much shorter & efficient than my earlier script using EOMONTH .

Let me create a sample table to demonstrate.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-03-31')
INSERT INTO tbl_Sample VALUES (3,'2013-10-10')
INSERT INTO tbl_Sample VALUES (4,'2013-12-31')
GO

SOLUTION 1 :
Given below is the script that we usually use in SQL Server 2005/2008 to check whether the date is End of month date or not.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT [ID],[Date],
(CASE WHEN
CONVERT(varchar(11)
,DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date])+1, -1),120)=[Date]
THEN 'Month End' ELSE 'Not a Month End' END) As [Result]
FROM tbl_Sample
GO

isdate is a mont end date.1.1

SOLUTION 2 :
Given below is the script, that I developed using new functions (EOMONTH & IIF) shipped in SQL Server 2012 to check whether the date is End of month date or not. Due to these functions, we can reduce the script size and improve the performance.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT [ID],[Date],
IIF(EOMONTH([Date])=[Date],'Month End','Not a Month End')
As [Result]
FROM tbl_Sample
GO
--OUTPUT

isdate is a mont end date.1.2

CONCLUSION :
As you can see, both solutions have the same result set but Solution 2 is recommended because it is using only one function to check whether the date is month end date or not, instead of a lot of date functions.

Read Full Post »

SQL SERVER 2014 – List of all Table & its Index sizes in MBs along with row count

Posted in Solutions, SQL SERVER, tagged , , , , on September 28, 2013| 2 Comments »

How to get list of all tables’ and its index sizes along with row count looks very simple ? We do have the solution for it. Either you can use sp_spaceused or you can use one of my own solutions.

But things became strange when I tried both solutions on SQL Server 2014 having memory optimized table and both solutions return zero for memory optimized tables. Given below is the script and its output in SQL Server 2014.

USE hkNorthwind
GO
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';
GO

table and index size of memory optimized table.1.1

As you can see in the above image, all memory optimized tables showing zero result. Oopsssssss. So what is the solution ?
I started my research and developed a solution . Given below is the script.

SOLUTION :

USE hkNorthwind
GO
DECLARE @PageSize float
SELECT @PageSize=v.low/1024.0 FROM MASTER.dbo.spt_values v WHERE v.number=1 and v.type='E'

SELECT
OBJECT_SCHEMA_NAME(tbl.object_id) as [Schema Name]
, OBJECT_NAME(tbl.object_id) as [Table Name]
, CASE
WHEN (tbl.is_memory_optimized=0) THEN
ROUND(TRY_CONVERT(float,ISNULL((SELECT @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE i.object_id = tbl.object_id),0.0))/1024,3)
ELSE
Round(TRY_CONVERT(float,isnull((SELECT (tms.[memory_used_by_table_kb])
FROM [sys].[dm_db_xtp_table_memory_stats] tms
WHERE tms.object_id = tbl.object_id), 0.0))/1024,3)
END
as [DataSpaceUsed (In Mbs)]
, (CASE
WHEN (tbl.is_memory_optimized=0) THEN
ROUND(TRY_CONVERT(float,ISNULL((
(SELECT SUM (used_page_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = tbl.object_id)
+ ( CASE (SELECT count(*) FROM sys.internal_tables WHERE parent_id = tbl.object_id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222))
WHEN 0 THEN 0
ELSE (
SELECT sum(p.used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = tbl.object_id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) AND p.object_id = it.object_id)
END )
- (SELECT SUM (CASE WHEN(index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END)
FROM sys.dm_db_partition_stats WHERE object_id = tbl.object_id)
) * @PageSize, 0.0) )/1024,3)
ELSE
ROUND(TRY_CONVERT(float,isnull((SELECT (tms.[memory_used_by_indexes_kb])
FROM [sys].[dm_db_xtp_table_memory_stats] tms
WHERE tms.object_id = tbl.object_id), 0.0)) /1024,3)
END)
as [IndexSpaceUsed (In Mbs)]

,(CASE
WHEN (tbl.is_memory_optimized=0) THEN
OBJECTPROPERTYEX(OBJECT_ID, N'Cardinality')
ELSE
(
SELECT ISNULL([rows],0)  as [Total Records]
FROM sys.hash_indexes as Ind
CROSS APPLY sys.dm_db_stats_properties(Ind.object_id,Ind.index_id)
WHERE Ind.index_id =2 AND Ind.object_id=tbl.object_id
)
END) [Total No of Rows]

FROM
sys.tables as tbl
--OUPUT

table and index size of memory optimized table.1.2

As you can see in the above script (solution), SQL Server 2014 handles disk based table and memory optimized table space, index space and row count separately. So you need to calculate it separately.

Let me know if you came across the same situation and its solution.

Read Full Post »

SQL SERVER 2012 – How to convert varchar to currency

Posted in Solutions, SQL SERVER, tagged , , , , on September 27, 2013| Leave a Comment »

I had come across this problem (how to convert varchar to currency) a couple of years ago and fixed this problem. Recently I had a chance to revisit my solutions and I discovered that we can re-script this solution much more efficiently than I did earlier, using FORMAT & TRY_CONVERT (new functions shipped in SQL Server 2012).

Note : It is recommended to do this formatting from the front end of the application.

Let me create a sample to demonstrate the solution :

SAMPLE:

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
CREATE TABLE [dbo].[tbl_sample]
(
[Col_ID] [int] NULL,
[Col_varchar] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (1, N'1000.00')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (2, N'-10.21')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (3, N'1234.5678')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (4, N'123.45')
GO

SOLUTION:
In this solution, first of all, we need to convert the data into money data type then format it into any currency using culture.
Given below is the solution.

SELECT
[Col_ID]
,[Col_varchar]
,FORMAT(TRY_CONVERT(money,[Col_varchar]),'C','en-US')
AS [Varchar_to_currency]
FROM [tbl_sample]
GO

varchar_to_currency.1.1

Read Full Post »

SQL Server 2012- How to enforce uniqueness for sequence values

Posted in Solutions, SQL SERVER, tagged , , , , , on September 23, 2013| 3 Comments »

Sequence object is one of the nice features shipped with SQL Server 2012. It gives much more flexibility than identity column. Due to its flexibility, I usually recommend to use it instead of identity. But recently I came across with a so called issue related to sequence object. The issue is that sequence object does not enforce uniqueness by itself.

Let me create a sample to demonstrate the issue step by step.
Step 1:
Create a sample table. Given below is the script.

USE [AdventureWorks2012]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample Table
CREATE TABLE [dbo].[tbl_sample]
(
[ID] [int] NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Step 2 :
Create a sequence object with CYCLE property.

--Create a sample Sequence object
--DROP SEQUENCE [dbo].[Seq_Sample]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2
CYCLE
GO
--

Step 3 :
Insert few records in the above sample table using sequence object.

USE [AdventureWorks2012]
GO
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-1')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-2')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-3')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-4')
GO

Step 4 :
Browse the table. Given below is the script.

USE [AdventureWorks2012]
GO
SELECT * FROM [tbl_sample]
GO
--OUTPUT

sequence_eniqueness.1.1

As you can see in the above result set, there are duplicate IDs in the ID column. Ooopsss it does not enforce uniqueness by default. So what is the solution ?

SOLUTION :
Let me explain the solution step by step.

Step 1 :
Create a sample table. Given below is the script.

USE [AdventureWorks2012]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample Table
CREATE TABLE [dbo].[tbl_sample]
(
[ID] [int] NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Step 2 :
Create a unique index on column ID of sample table.

USE [AdventureWorks2012]
GO
CREATE UNIQUE INDEX AK_tbl_sample_ID
ON dbo.tbl_sample (ID)
GO

Step 3 :
Create a sequence object with NO CYCLE property.

USE [AdventureWorks2012]
GO
--Create a sample Sequence object
--DROP SEQUENCE [dbo].[Seq_Sample]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
NO CYCLE
GO
--

Step 4 :
Insert few records in the above sample table using sequence object to check whether the uniqueness is enforced or not.

USE [AdventureWorks2012]
GO
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-1')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-2')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-3')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-4')
GO
--OUTPUT

Step 5 :
Browse the table again. Given below is the script.

USE [AdventureWorks2012]
GO
SELECT * FROM [tbl_sample]
GO
--OUTPUT

sequence_eniqueness.1.2

Conclusion :
Whenever you need to enforce uniqueness in the table using sequence object, make sure you have a UNIQUE INDEX on that particular column of the table. Also make sure that SEQUENCE object has NO CYCLE property.

Read Full Post »

SQL SERVER 2012- Shortest way to calculate median

Posted in Solutions, SQL SERVER, tagged , , , , , on September 21, 2013| Leave a Comment »

Calculating median has never been an issue, as we have lots of solutions to calculate medians using CTE, user defined functions etc. But what encourages me to write this post is, I found a shortcut to calculate the median using PERCENTILE_CONT (A new analytic function shipped in SQL Server 2012).

As per the definition : To find the Median, place the numbers you are given in value order and find the middle number. Click here for Reference

Given below is the sample that I will use to demonstrate what is median.

<br />SELECT ProductID,StandardCost FROM<br />[Production].[ProductCostHistory]<br />WHERE ProductID IN (707,708,711,712,713)<br />ORDER BY ProductID<br />GO<br />--OUTPUT<br />

median.1.1

Given below is the script to calculate the median in a shortest way.

<br />Use AdventureWorks2012<br />GO<br />SELECT<br />DISTINCT [ProductID]<br />,PERCENTILE_CONT(0.5)<br />WITHIN GROUP (ORDER BY [StandardCost])<br />OVER (PARTITION BY [ProductID]) AS Median<br />FROM [Production].[ProductCostHistory]<br />WHERE ProductID IN (707,708,711,712,713)<br />ORDER BY [ProductID]<br />GO<br />--OUTPUT<br />

median.1.2

Note : Whenever you calculate median using PERCENTILE_CONT make sure it is PERCENTILE_CONT(0.5).

Read Full Post »

SQL SERVER 2012- How to change datatype of Sequence Object

Posted in Solutions, SQL SERVER, tagged , , , , , , , on September 20, 2013| Leave a Comment »

‘How to change the datatype of type Sequence object’ became critical for me when I created a sequence object with a wrong datatype and deployed it on client side and later I tried to change the datatype of the sequence object but I could not 😦

Let me demonstrate the issue and its resolution step by step.

Step 1 :
In this step, we will create a sequence object using smallint data type.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [SMALLINT]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
NO CACHE
GO

Step 2 :
In this step, we will use 3 sequence IDs. This step is just for demonstration purpose only.

Use AdventureWorks2012
GO
SELECT NEXT VALUE FOR [Seq_Sample]
GO 3

Step 3 :
Let’s try to alter its datatype. This step is just to demonstrate the error only.

Use AdventureWorks2012
GO
ALTER SEQUENCE [dbo].[Seq_Sample] AS INT
GO
--OUTPUT

Msg 11711, Level 15, State 1, Line 1
Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.

Ooopsss cannot change it. So, what is the solution ?

Step 4:
In this step, we need to get the current value (last value) of the above sequence object. Never skip this step because once you drop the above sequence object, it would be very difficult to get the current value if it is implemented for multiple tables.
Given below is the script.

Use AdventureWorks2012
GO
SELECT current_value FROM SYS.SEQUENCES
WHERE name='Seq_Sample'
--OUTPUT

current_value
————-
3

(1 row(s) affected)

Step 5:
Once you have the current value of the above sequence, it is time to drop the sequence object.

Use AdventureWorks2012
GO
DROP SEQUENCE [dbo].[Seq_Sample]
GO

Step 6:
Now, it is time to re-create the sequence object.  Make sure that you change the datatype of the sequence object. Also you must change the START value of the new sequence object’s to the current value + 1 (we got the current value in step 3).
Given below is the script.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [INT]
START WITH 4
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
NO CACHE
GO

Step 7:
Finally, check the next value of the sequence.
Given below is the script.

Use AdventureWorks2012
GO
Select NEXT VALUE FOR [Seq_Sample] AS [Result]
GO
--OUTPUT

Result
———–
4

(1 row(s) affected)

Read Full Post »

« Newer Posts - Older Posts »