{"id":6,"date":"2006-04-26T05:39:27","date_gmt":"2006-04-26T05:39:27","guid":{"rendered":"http:\/\/etlguru.com\/blog\/2006\/04\/26\/simulating-oracle-sequences-in-sybase-sql-server\/"},"modified":"2011-05-27T00:00:21","modified_gmt":"2011-05-27T00:00:21","slug":"simulating-oracle-sequences-in-sybase-sql-server","status":"publish","type":"post","link":"https:\/\/etlguru.com\/?p=6","title":{"rendered":"Simulating Oracle Sequences in Sybase &#038; SQL Server"},"content":{"rendered":"<div class=\"entry\">\n<p>Programmatic control is lost when identity columns are used in Sybase and SQL Server.  I do not recommend using Identity columns to create surrogate keys during ETL process. There are many more reasons for that. Oracle has the sequence feature which is used extensively by Oracle programmers. I have no clue why other vendors are not providing the same. This custom code has been used  extensively by me and thoroughly tested. I ran multiple processes simultaneously to check if there is deadlock and also made sure that the process returns different sequences to different client process.  <\/p>\n<p><strong>Notes: &#8211;<br \/>\n    <\/strong><br \/>\n    1. The table should have \u00e2\u20ac\u02dcROW LEVEL LOCKING\u00e2\u20ac\u2122<br \/>\n    <br \/>\n    2. The sequence generator process is stateless (See more details in Object Oriented Programming)<br \/>\n    <br \/>\n  3. Create one row for each target table in the sequence master table. Do not try to use one sequence for multiple tables. It will work but probably is not a good idea. <\/p>\n<p><strong>Step 1: -Create a table with following structure.<br \/>\n      <br \/>\n  <\/strong><em>CREATE TABLE sequence_master (<br \/>\n      sequence_nm varchar (55)  NOT NULL ,<br \/>\n      sequence_num integer  NOT NULL<br \/>\n      )<br \/>\n      GO<\/em>    <\/p>\n<p><strong>Step 2: -Create a stored procedure that will return the next sequence.<br \/>\n    <\/strong><em><br \/>\n    CREATE  PROCEDURE p_get_next_sequence<br \/>\n      @sequence_name varchar(100)<br \/>\n      AS<br \/>\n      BEGIN<br \/>\n      DECLARE @sequence_num INTEGER<br \/>\n      \u00e2\u20ac\u201d Returns an error if sequence row is entered into the table.<br \/>\n      SET @sequence_num = -1<\/em><\/p>\n<p>    <em>    UPDATE sequence_master<br \/>\n      SET    @sequence_num = sequence_num = sequence_num + 1<br \/>\n    WHERE  Sequence_name = @sequence_name<\/em><\/p>\n<p>    <em>    RETURN @sequence_num<br \/>\n      END<br \/>\n    GO<\/em><\/p>\n<p>&nbsp;<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Programmatic control is lost when identity columns are used in Sybase and SQL Server. I do not recommend using Identity columns to create surrogate keys during ETL process. There are many more reasons for that. Oracle has the sequence feature which is used extensively by Oracle programmers. I have no clue why other vendors are [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,8,11,10,1],"tags":[],"class_list":["post-6","post","type-post","status-publish","format-standard","hentry","category-data-loading","category-informatica","category-sql-server","category-sybase","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts\/6","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6"}],"version-history":[{"count":2,"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts\/6\/revisions"}],"predecessor-version":[{"id":247,"href":"https:\/\/etlguru.com\/index.php?rest_route=\/wp\/v2\/posts\/6\/revisions\/247"}],"wp:attachment":[{"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/etlguru.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}