{"id":86527,"date":"2020-03-01T02:58:18","date_gmt":"2020-03-01T02:58:18","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=86527"},"modified":"2020-03-01T02:58:18","modified_gmt":"2020-03-01T02:58:18","slug":"identity-column-vs-etl-process","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/identity-column-vs-etl-process\/","title":{"rendered":"Identity column vs ETL Process"},"content":{"rendered":"

Creating ETL<\/strong> process can pose so many challenges we will only fully discover them when really building the ETL<\/strong>.<\/p>\n

Before you complain about modelling, the example here has no relation to data warehouse modelling. The ETL<\/strong> in question was a migration from a data source to another. The need for executing multiple times was mainly for a testing purpose.<\/p>\n

Let’s get back a bit and understand what I’m talking about.<\/p>\n

Imagine you are building an ETL<\/strong>. Importing a lot of data to a set of tables and implementing many rules inside the ETL<\/strong>. Your ETL<\/strong> will be executed many times, but the tables need to be always empty when the process starts. As a result your ETL<\/strong> needs a cleaning task, in the beginning, clearing all the tables.<\/p>\n

Some tables have an identity<\/em> field. Since you are clearing the tables and ensuring everything will start again from the same point, you need to reset the identity<\/em> as well.<\/p>\n

Probably your first thought is to use Truncate Table<\/strong> statement. The problem is Truncate Table<\/strong> is not supported in tables referenced by a foreign key.<\/p>\n

Check the example below: The Truncate Table<\/strong> is blocked by the foreign key.<\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0class<\/span>
\n\u00a0\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0id<\/span>\u00a0\u00a0\u00a0\u00a0INT<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>\u00a01<\/span>)<\/span>\u00a0PRIMARY<\/span>\u00a0KEY<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0title<\/span>\u00a0VARCHAR<\/span>(<\/span>50<\/span>)<\/span>
\n\u00a0\u00a0)<\/span>
\ngo<\/span> <\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0students<\/span>
\n\u00a0\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0id<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INT<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>\u00a01<\/span>)<\/span>\u00a0PRIMARY<\/span>\u00a0KEY<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0NAME<\/span>\u00a0\u00a0\u00a0\u00a0VARCHAR<\/span>(<\/span>50<\/span>)<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0classid<\/span>\u00a0INT<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0FOREIGN<\/span>\u00a0KEY<\/span>\u00a0(<\/span>classid<\/span>)<\/span>\u00a0REFERENCES<\/span>\u00a0class<\/span>(<\/span>id<\/span>)<\/span>
\n\u00a0\u00a0)<\/span>
\ngo<\/span> <\/p>\n

TRUNCATE<\/span>\u00a0TABLE<\/span>\u00a0class<\/span>\u00a0 <\/span><\/div>\n

\"\"<\/p>\n

You need to use\u00a0Delete<\/strong> or drop and re-create the constraints. Choosing the simplest solution, let’s talk about using the Delete<\/strong>.<\/p>\n

Let’s consider the scenario where your ETL<\/strong> will need to run many times and need to start with a cleaning task. This scenario, sometimes, is used for staging servers in the middle of a bigger ETL<\/strong> process. I used for data migration and had to make the execution many times during development, always returning to the start point before executing again.<\/p>\n

That’s why you need a cleaning process, deleting all the records on the tables, but that’s not enough. The tables have identity fields and you also need to reset the identity seed before executing again the ETL<\/strong>.<\/p>\n

Reseting the Identity Seed: Regular behaviour<\/p>\n<\/h2>\n

Reset an identity seed seems simple:<\/p>\n

DBCC<\/span>\u00a0checkident<\/span>(<\/span>‘testTable’<\/span>,<\/span>\u00a0reseed<\/span>,<\/span>\u00a01<\/span>)<\/span>\u00a0 <\/span><\/div>\n

\nThe problem is the combination of Delete<\/strong> and the DBCC CheckIdent<\/strong>. This combination can lead to very strange results. Let’s see what can happen below:<\/p>\n

The first execution will be in an empty table, never used before.<\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0testtable<\/span>
\n\u00a0\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0id<\/span>\u00a0\u00a0\u00a0\u00a0INT<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>\u00a01<\/span>)<\/span>\u00a0PRIMARY<\/span>\u00a0KEY<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0price<\/span>\u00a0NUMERIC<\/span>(<\/span>15<\/span>,<\/span>\u00a02<\/span>)<\/span>
\n\u00a0\u00a0)<\/span>
\ngo<\/span> <\/p>\n

DELETE<\/span>\u00a0testtable<\/span> <\/p>\n

DBCC<\/span>\u00a0checkident<\/span>(<\/span>‘testTable’<\/span>,<\/span>\u00a0reseed<\/span>,<\/span>\u00a01<\/span>)<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0testtable<\/span>
\nVALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>10<\/span>)<\/span> <\/p>\n

SELECT<\/span>\u00a0*<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0testtable<\/span>\u00a0<\/p>\n

<\/span><\/div>\n

\"\"<\/p>\n

The following executions will be in a table already used, having passed by a delete and dbcc checkident<\/strong>. It’s the same code executed again:<\/p>\n

 <\/p>\n

DELETE<\/span>\u00a0testtable<\/span> <\/p>\n

DBCC<\/span>\u00a0checkident<\/span>(<\/span>‘testTable’<\/span>,<\/span>\u00a0reseed<\/span>,<\/span>\u00a01<\/span>)<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0testtable<\/span>
\nVALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>10<\/span>)<\/span> <\/p>\n

SELECT<\/span>\u00a0*<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0testtable<\/span>\u00a0<\/p>\n

<\/span><\/div>\n

\"\"<\/p>\n

As you may notice, even making the dbcc checkident<\/strong> using the same seed value, the initial identity value in the table becomes different, what can cause all sort of problems for your software.<\/p>\n

The root cause of the problem is one statement, DBCC CheckIdent<\/strong>, behaving in different ways according to how the table is at the moment. Our cleaning script needs a consistent result and it’s not achieving this.<\/p>\n

 <\/p>\n

Investigating the root cause<\/h2>\n

\nWe will need to search deeper in SQL Server<\/strong> to discover why the checkident<\/strong> has these differences and where is SQL Server<\/strong> storing the current number for the Identity column of a table.<\/p>\n

The DMV<\/strong> sys.identity_columns<\/em> has a column called LAST_VALUE<\/strong> which contains the last value used for the identity column. Let’s analyse the value this column stores during the execution of the script.<\/p>\n

DROP<\/span>\u00a0TABLE<\/span>\u00a0testtable<\/span>
\ngo<\/span> <\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0testtable<\/span>
\n\u00a0\u00a0(<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0id<\/span>\u00a0\u00a0\u00a0\u00a0INT<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>\u00a01<\/span>)<\/span>\u00a0PRIMARY<\/span>\u00a0KEY<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0price<\/span>\u00a0NUMERIC<\/span>(<\/span>15<\/span>,<\/span>\u00a02<\/span>)<\/span>
\n\u00a0\u00a0)<\/span>
\ngo<\/span> <\/p>\n

DELETE<\/span>\u00a0testtable<\/span> <\/p>\n

SELECT<\/span>\u00a0object_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NAME<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0column_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_value<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>identity_columns<\/span>
\nWHERE<\/span>\u00a0\u00a0Object_name<\/span>(<\/span>object_id<\/span>)<\/span>\u00a0=<\/span>\u00a0‘testTable’<\/span>\u00a0<\/p>\n

<\/span><\/div>\n

\"\"<\/p>\n

\nJust after have been created, the Last_Value<\/strong> column contains NULL<\/strong>, even after the delete statement has been executed.<\/p>\n

 <\/p>\n

DBCC<\/span>\u00a0checkident<\/span>(<\/span>‘testTable’<\/span>,<\/span>\u00a0reseed<\/span>,<\/span>\u00a01<\/span>)<\/span> <\/p>\n

SELECT<\/span>\u00a0object_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NAME<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0column_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_value<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>identity_columns<\/span>
\nWHERE<\/span>\u00a0\u00a0Object_name<\/span>(<\/span>object_id<\/span>)<\/span>\u00a0=<\/span>\u00a0‘testTable’<\/span>\u00a0<\/p>\n

<\/span><\/div>\n

\"\"<\/p>\n

 <\/p>\n

Even after the execution of the checkident<\/strong>, the Last_Value<\/strong> column remains with NULL<\/strong> value.<\/p>\n

Let’s insert a record:<\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0testtable<\/span>
\nVALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>10<\/span>)<\/span>
\ngo<\/span> 2<\/span> <\/p>\n

SELECT<\/span>\u00a0*<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0testtable<\/span>\u00a0<\/p>\n

<\/span><\/div>\n

\"\"<\/p>\n

 <\/p>\n

The Null<\/strong> value on Last_Value<\/strong> results in a first identity as 1, which is the exact value set as a seed for the identity.<\/p>\n

Let’s repeat again:<\/p>\n

DELETE<\/span>\u00a0testtable<\/span> <\/p>\n

SELECT<\/span>\u00a0object_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NAME<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0column_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_value<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>identity_columns<\/span>
\nWHERE<\/span>\u00a0\u00a0Object_name<\/span>(<\/span>object_id<\/span>)<\/span>\u00a0=<\/span>\u00a0‘testTable’<\/span>\u00a0<\/p>\n

<\/span><\/div>\n

\"\"<\/p>\n

 <\/p>\n

After the delete, the last_value<\/strong> is 2, the last identity used. We need the checkident<\/strong>:<\/p>\n

 <\/p>\n

DBCC<\/span>\u00a0checkident<\/span>(<\/span>‘testTable’<\/span>,<\/span>\u00a0reseed<\/span>,<\/span>\u00a01<\/span>)<\/span> <\/p>\n

SELECT<\/span>\u00a0object_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NAME<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0column_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_value<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>identity_columns<\/span>
\nWHERE<\/span>\u00a0\u00a0Object_name<\/span>(<\/span>object_id<\/span>)<\/span>\u00a0=<\/span>\u00a0‘testTable’<\/span>\u00a0<\/p>\n

<\/span><\/div>\n

\"\"<\/p>\n

Now we can easily see the difference: When the table is empty the last_value<\/strong> is NULL<\/strong> and it continues to be NULL<\/strong> after the checkident<\/strong>. However, when the table already had some records included, the last_value<\/strong> is filled and the checkident<\/strong> will reseed it to the value we are providing. So, it’s a difference between last_value<\/strong> field being\u00a0NULL<\/strong> or the seed value.<\/p>\n

Solving the problem<\/h2>\n

Knowing these details, we can create a query to calculate the next identity value for a table. If the last_value<\/strong> is NULL<\/strong> the next value will be the seed value, if last_value<\/strong> has a value, the next one will be the last_value<\/strong> + the increment<\/p>\n

 <\/p>\n

SELECT<\/span>\u00a0Isnull<\/span>(<\/span>CONVERT<\/span>(<\/span>INT<\/span>,<\/span>\u00a0last_value<\/span>)<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+<\/span>\u00a0CONVERT<\/span>(<\/span>INT<\/span>,<\/span>\u00a0increment_value<\/span>)<\/span>,<\/span>\u00a0CONVERT<\/span>(<\/span>INT<\/span>,<\/span>\u00a0seed_value<\/span>)<\/span>)<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>identity_columns<\/span>
\nWHERE<\/span>\u00a0\u00a0Object_name<\/span>(<\/span>object_id<\/span>)<\/span>\u00a0=<\/span>\u00a0‘testTable’<\/span>\u00a0 <\/span><\/div>\n

 <\/p>\n

The Convert<\/strong>s are needed because on this DMV<\/strong> the columns are of type sql_variant<\/strong>, so we need to convert them to an integer.<\/p>\n

Knowing how to find the correct value we can now fix our cleaning code in order to always have the same result, with the identity starting in 1.<\/p>\n

 <\/p>\n

DECLARE<\/span>\u00a0@lastValue<\/span>\u00a0INT<\/span> <\/p>\n

DELETE<\/span>\u00a0testtable<\/span> <\/p>\n

SELECT<\/span>\u00a0@lastValue<\/span>\u00a0=<\/span>\u00a0last_value<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>identity_columns<\/span>
\nWHERE<\/span>\u00a0\u00a0Object_name<\/span>(<\/span>object_id<\/span>)<\/span>\u00a0=<\/span>\u00a0‘testTable’<\/span> <\/p>\n

IF<\/span>\u00a0(<\/span>\u00a0@lastValues<\/span>\u00a0IS<\/span>\u00a0NULL<\/span>\u00a0)<\/span>
\n\u00a0\u00a0DBCC<\/span>\u00a0checkident<\/span>(<\/span>‘testTable’<\/span>,<\/span>\u00a0reseed<\/span>,<\/span>\u00a01<\/span>)<\/span>
\nELSE<\/span>
\n\u00a0\u00a0DBCC<\/span>\u00a0checkident<\/span>(<\/span>‘testTable’<\/span>,<\/span>\u00a0reseed<\/span>,<\/span>\u00a00<\/span>)<\/span>\u00a0 <\/p>\n

\n<\/span><\/div>\n","protected":false},"excerpt":{"rendered":"

Creating ETL process can pose so many challenges we will only fully discover them when really building the ETL. Before you complain about modelling, the example here has no relation to data warehouse modelling. The ETL in question was a migration from a data source to another. The need for executing multiple times was mainly……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,1],"tags":[4824,4287,4149],"coauthors":[6810],"class_list":["post-86527","post","type-post","status-publish","format-standard","hentry","category-blogs","category-uncategorized","tag-etl","tag-identity","tag-learn-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86527","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=86527"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86527\/revisions"}],"predecessor-version":[{"id":86538,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86527\/revisions\/86538"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=86527"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=86527"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=86527"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=86527"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}