SQL Server 2012 | Sequence Feature


Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012! A Sequence object is an object that provides functionality similar to Identity (Autonumber) column. The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value of the ID column before you insert records.

A sample example is as follows:

Sample 1:
create sequence dbsequence
start with 1 increment by 5
select next value for dbsequence
--output=1

select next value for dbsequence
--output=6

1.1   Should you need to clean up …
drop sequence dbsequence

Sample 2.1:
create sequence idsequence
start with 1 increment by 3

create table Products_ext
(
id int,
Name varchar(50)
)

INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, 'ProductItem1')


INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, 'ProductItem2')


2.2 Check the result  …

select * from Products_ext

/* Output

1              ProductItem1
4              ProductItem2

*/

2.3 Should you need to clean up …

drop table Products_ext
drop sequence idsequence

Comments

Post a Comment

Popular posts from this blog

Baseless merge!

Baseless merge in TFS ? or a Baseless Question? :-) A colleague came over wondering for what someone asked him to do - and that is to merge two branches. I believe, the tool allows you to do that however, I wouldn't recommend it simply because it is a sort of sibling-sibling merge rather than (what it is intended for) parent-child merge. Reference to support my perspective ... How do I merge two branches? To perform a merge, you can use the merge functionality in Source Control Explorer, or you can use the  merge  command-line tool. You can merge based on changeset, label, date, or version. You might find that your branching structure becomes deeper than one level. If this happens, keep in mind that it is only possible to merge one level at a time. For example, consider the following source tree structure: Development  – Container for isolation development branches. Feature A Source Feature B Source Main  – Main integration build bran...
Read more

QuadKey - what it is?

Image
To start with, let me share an excerpt from MSDN as below: Bing Maps Tile System (Reference:   https://msdn.microsoft.com/en-us/library/bb259689.aspx ) Bing Maps provides a world map that users can directly manipulate to pan and zoom. To make this interaction as fast and responsive as possible, we chose to pre-render the map at many different levels of detail, and to cut each map into tiles for quick retrieval and display. This document describes the projection, coordinate systems, and addressing scheme of the map tiles, which collectively are called the Bing Maps Tile System. So if you follow the above references Bing Maps Tile System, you would get an idea that  - how the world map is kind of apportioned into a tile system and depending on what level of detail or zoom you are interested in, you would get a <N> character long quad-key.  For what I am involved with currently, we care to go for the maximum allowable detail to uniquely identify each...
Read more

Binding ENUM to WPF control - ComboBox

I believe, I explored quite a bit on finding the best approach for my requirement of binding Enum to WPF ComboBox and thought to share the gradual steps here for a one-stop place which hopefully will help similar minded technical enthusiast .. The simplest approach: You want to just bind Enum as it appears (verbatim) to the WPF combo .. Let's define a sample enum as follows: namespace {    public enum TestEnum    {       String1,       String2,       String3,       String4    } } All we now need is a way to leverage Enum - GetValues method to provide a list of values that lets you bind directly to the ComboBox via ItemSource. < ObjectDataProvider x : Key ="dataFromEnum" MethodName ="GetValues" ObjectType ="{ x : Type sys : Enum }" > < ObjectDataProvider.MethodParameters > < x : Type TypeName ="local:TestEnum" /> </ ...
Read more