{"id":77,"date":"2014-01-02T23:01:33","date_gmt":"2014-01-02T15:01:33","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=77"},"modified":"2014-01-02T23:25:41","modified_gmt":"2014-01-02T15:25:41","slug":"sql-server-2012-new-feature-sequence-numbers","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html","title":{"rendered":"SQL Server 2012 New Feature – Sequence Numbers"},"content":{"rendered":"

Coba sedikit explorasi dengan sequence number di SQL Server 2012. Para developer mungkin tidak asing identity column pada saat kita akan membuat sebuah field pada sebuah column di sebuah table. dimana pada column tersebut kita bisa setup sebuah nilai identity column dan nilai increment nya.<\/p>\n

Namun itu hanya berlaku pada 1 buah tabel, nah sekarang bagaimana caranya agar nilai yang berurut tersebut bisa kita sebar di beberapa tabel namun ? disini lah fungsi dari fitur Sequence Number dapat berperan.<\/p>\n

Type data dari Sequence Number dapat diterapkan pada semua jenis data integer, namun jika sebuah sequence tidak di deklarasikan tipe datanya maka secara default dia akan memiliki type data BigInt.<\/p>\n

Sebuah sequence Number memiliki ruang urutan mulai dari -2,147,483,648 sampai dengan 2,147,483,647. Jadi jika kita akan membuat Sequence Number yang memiliki pertambahan nilai 1 mulai dari -2,147,483,648 sampai dengan 2,147,483,647 dapat dibuat dengan cara<\/p>\n

\n
CREATE<\/span> SEQUENCE<\/span> Schema<\/span>.SequenceName\r\n    AS<\/span> int<\/span>\r\n    INCREMENT BY<\/span> 1 ;<\/pre>\n
 <\/p>\n<\/div>\n
jika kita akan membuat Sequence Number yang memiliki pertambahan nilai 1 mulai dari 1 sampai dengan 2,147,483,647 dapat dibuat dengan cara<\/p>\n
\n
CREATE<\/span> SEQUENCE<\/span> Schema<\/span>.SequenceName\r\n    AS<\/span> int<\/span>\r\n    START<\/span> WITH<\/span> 1\r\n    INCREMENT BY<\/span> 1 ;<\/pre>\n
 <\/p>\n<\/div>\n
untuk lebih memperjelas penjelasan diatas, mari kita sama2 melakukan eksperimen dibawah ini.<\/p>\n
Pertama yang kita lakukan adalah menentukan schema mana yang akan diterapkan Sequence Number. Misal saya akan gunakan database AdventureWorks2012 dengan schema TestSN<\/strong>. karena scehma TestSN tidak ada di database tersebut, maka saya bikin baru<\/p>\n
\n
Use<\/span> AdventureWorks2012 ;\r\nGo<\/span> \r\n\r\nCREATE<\/span> SCHEMA<\/span> TestSN ;\r\n  GO<\/pre>\n
 <\/p>\n<\/div>\n
setelah itu kita membuat Sequence Numbernya didalam Schema tersebut yang saya beri nama UrutTmbh1<\/strong><\/p>\n
\n
\n
Create<\/span> SEQUENCE<\/span> TestSN.UrutTmbh1 AS<\/span> INT<\/span>\r\n    START<\/span> WITH<\/span> 10000 \r\n    INCREMENT BY<\/span> 1;\r\nGo<\/pre>\n
 <\/p>\n<\/div>\n
anda dapat mencek nya hasil pembuatan sequence tersebut di Database AdventureWork2012 seperti gambar berikut<\/p>\n<\/div>\n
\"image\"<\/a><\/p>\n
Saya akan coba terapkan Sequence ini pada 2 buah tabel. Saya coba buat 2 tabel contoh dulu<\/p>\n
\n
CREATE<\/span> TABLE<\/span> Employees\r\n(\r\n    EmployeeId INT<\/span> NOT<\/span> NULL<\/span> PRIMARY<\/span> KEY<\/span>,\r\n    Name NVARCHAR(255) NULL<\/span>\r\n);\r\n\r\nCREATE<\/span> TABLE<\/span> Contractors\r\n(\r\n    ContractorId INT<\/span> NOT<\/span> NULL<\/span> PRIMARY<\/span> KEY<\/span>,\r\n    Name NVARCHAR(255) NULL<\/span>\r\n);<\/pre>\n
 <\/p>\n<\/div>\n
Setelah tabel diatas tercipta, mari kita masukkan 1 buah data masing-masing pada tabel tersebut dengan cara berikut :<\/p>\n
\n
INSERT INTO<\/span> Employees (EmployeeId, Name)\r\n   VALUES<\/span> (NEXT<\/span> VALUE<\/span> FOR<\/span> TestSN.UrutTmbh1, 'Jane'<\/span>);\r\n\r\nINSERT INTO<\/span> Contractors (ContractorId, Name)\r\n   VALUES<\/span> (NEXT<\/span> VALUE<\/span> FOR<\/span> TestSN.UrutTmbh1, 'John'<\/span>);\r\nGo<\/pre>\n
 <\/p>\n<\/div>\n
Tampak bahwa saya untuk memasukkan nilai EmployeeId pada tabel Employees dengan menggunakan klausa NEXT VALUE FOR. Bgitu juga dengan field Contractor Id pada table Contractor.<\/p>\n
Jika kita coba lihat hasilnya adalah seperti berikut :<\/p>\n
\"image\"<\/a><\/p>\n
Tampak terbentuk sebuah urutan angka (Sequence number) pada 2 field yang berbeda dan berbeda tabel juga.<\/p>\n
Demikian semoga dapat dipahami dan bermanfaat.<\/p>\n","protected":false},"excerpt":{"rendered":"
Coba sedikit explorasi dengan sequence number di SQL Server 2012. Para developer mungkin tidak asing identity column pada saat kita akan membuat sebuah field pada sebuah column di sebuah table. dimana pada column tersebut kita bisa setup sebuah nilai identity column dan nilai increment nya. Namun itu hanya berlaku pada \u2026<\/p>\n
 Continue reading<\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":109,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[26],"class_list":["post-77","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-sequence-numbers"],"yoast_head":"\nSQL Server 2012 New Feature - Sequence Numbers - Suherman Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2012 New Feature - Sequence Numbers - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"Coba sedikit explorasi dengan sequence number di SQL Server 2012. Para developer mungkin tidak asing identity column pada saat kita akan membuat sebuah field pada sebuah column di sebuah table. dimana pada column tersebut kita bisa setup sebuah nilai identity column dan nilai increment nya. Namun itu hanya berlaku pada \u2026 Continue reading\" \/>\n<meta property=\"og:url\" content=\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-02T15:01:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-02T15:25:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png\" \/>\n\t<meta property=\"og:image:width\" content=\"247\" \/>\n\t<meta property=\"og:image:height\" content=\"234\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#article\",\"isPartOf\":{\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"SQL Server 2012 New Feature – Sequence Numbers\",\"datePublished\":\"2014-01-02T15:01:33+00:00\",\"dateModified\":\"2014-01-02T15:25:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html\"},\"wordCount\":327,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png\",\"keywords\":[\"Sequence Numbers\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html\",\"url\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html\",\"name\":\"SQL Server 2012 New Feature - Sequence Numbers - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#primaryimage\"},\"image\":{\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png\",\"datePublished\":\"2014-01-02T15:01:33+00:00\",\"dateModified\":\"2014-01-02T15:25:41+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#primaryimage\",\"url\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png\",\"contentUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png\",\"width\":247,\"height\":234},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2012 New Feature – Sequence Numbers\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server 2012 New Feature - Sequence Numbers - Suherman Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2012 New Feature - Sequence Numbers - Suherman Blog","og_description":"Coba sedikit explorasi dengan sequence number di SQL Server 2012. Para developer mungkin tidak asing identity column pada saat kita akan membuat sebuah field pada sebuah column di sebuah table. dimana pada column tersebut kita bisa setup sebuah nilai identity column dan nilai increment nya. Namun itu hanya berlaku pada \u2026 Continue reading","og_url":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2014-01-02T15:01:33+00:00","article_modified_time":"2014-01-02T15:25:41+00:00","og_image":[{"width":247,"height":234,"url":"https:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png","type":"image\/png"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#article","isPartOf":{"@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"SQL Server 2012 New Feature – Sequence Numbers","datePublished":"2014-01-02T15:01:33+00:00","dateModified":"2014-01-02T15:25:41+00:00","mainEntityOfPage":{"@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html"},"wordCount":327,"commentCount":0,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png","keywords":["Sequence Numbers"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#respond"]}]},{"@type":"WebPage","@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html","url":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html","name":"SQL Server 2012 New Feature - Sequence Numbers - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#primaryimage"},"image":{"@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png","datePublished":"2014-01-02T15:01:33+00:00","dateModified":"2014-01-02T15:25:41+00:00","breadcrumb":{"@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#primaryimage","url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png","contentUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/sq.png","width":247,"height":234},{"@type":"BreadcrumbList","@id":"https:\/\/suherman.asia\/w2\/sql-server-2012-new-feature-sequence-numbers.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"SQL Server 2012 New Feature – Sequence Numbers"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/77","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=77"}],"version-history":[{"count":2,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/77\/revisions"}],"predecessor-version":[{"id":110,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/77\/revisions\/110"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media\/109"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=77"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=77"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=77"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}