Identity Reseed question

May 28, 2010 at 4:02 PM

What is the recommended approach in TST to reseeding identity columns after inserting test data into a table with an identity column?

I've tried doing a DBCC CHECKIDENT('TableName', RESEED, 0); in the SQLTest_TEARDOWN_SuiteName stored procedure but since that runs before the ROLLBACK, the test data is still in the table and the reseed is pointless.

Ideally, the database should be left in as close a state as possible after the test as it was before the test. Identity are one place that this isn't true.

 

Have you considered adding the ability to allow for a SQLTest_RESEED_SuiteName stored procedure that handles reseeding identity columns after the ROLLBACK?

Coordinator
Jun 2, 2010 at 9:00 PM

I am not sure that you need to reseed the identity column. I don’t know what your scenario is but I assume you need to validate data from a table that has an identity column as the primary key. The quick start database has a few examples where validation is done on a table that has an identity column. Those examples (like SQLTest_QFn_GetEmployeeAllReports from database TSTQuickStart) may or may not be useful in your case. To setup the quick start database just run “TST /QuickStart”.

Another thing you could consider using is ROW_NUMBER() (see http://msdn.microsoft.com/en-us/library/ms186734.aspx) to generate a sequence of integers and use that instead of the actual identity column when inserting data in the table #ActualResult. 

Please let me know if you need more details or if you wish to give me more info about your scenario.

Jun 2, 2010 at 9:54 PM

I'm not trying to test the value of the IDENTITY column.

 

My goal is for the database state to be the same after the test as it was before the test. If the test inserted a record in a table with an IDENTITY column, that IDENTITY value has been used. Run enough tests that do that on an IDENTITY column with a small enough data type and you can run out of IDENTITY values.

 

I've seen this happen on a system that used a SMALLINT IDENTITY column. After the tests had been run a number of times, the seed value hit the maximum for a SMALLINT and everything that tried to insert into that table failed.

 

But, if you RESEED the IDENTITY column after the transaction the test is in has been rolled back then the problem goes away. The problem is that the TST infrastructure does not lend itself to doing this.

 

My suggestion was to allow for another type of stored procedure in the TST framework for doing the RESEED. This is just one option. No doubt there is other ways to RESEED the IDENTITY columns so they values are the same after the test as they were before the test.

Aug 3, 2010 at 4:22 PM

I really could use a solution to this problem. I just hit it again on a project I'm working on. After fixing a problem, I reran my set of tests to make sure things were working when I hit an "Arithmetic overflow error converting IDENTITY to data type smallint." error on my test that had just been working.

The core issue is that the schema I need to run tests against has IDENTITY columns and some of them are SMALLINT columns. After running enough tests, the SMALLINT identity column got maxed out. This was happening in a stored procedure called by a trigger that was invoked by a INSERT in one of my TST tests.

For any system with IDENTITY columns, some way to reseed them after a test is run is needed. Otherwise there is a ticking clocking counting down to an unexpected failure. Granted, with an INT or BIGINT column, a huge number of tests need to be run before the error hits. But it can and eventually will happen after running enough tests. With a SMALLINT column, after 32767 inserts have been attempted on any table with a SMALLINT IDENTITY column, tests will start failing.

Would you please considered adding something long the lines of a SQLTest_RESEED_SuiteName or SQLTest_RESEED_TestName type of stored procedure that can be used to reseeding identity columns. It would need to be called after TST does the ROLLBACK as the final part of the test execution process.

Without some sort of solution for resetting the seed value for IDENTITY columns back to what they were before the test ran, all I can do is manually reset the seed value and wait for the problem to surface again.

Coordinator
Aug 5, 2010 at 4:27 AM

Yes, will do. I am going to add this into the next version. It will take me a couple of weeks though until I deploy the fix. I may want to get your feedback after I have all the details worked out so stay tuned.

Coordinator
Aug 11, 2010 at 3:30 AM
Edited Aug 11, 2010 at 3:32 AM

JohnMayo,

I looked at two alternatives:

1. Have pre-transaction setups and post-transaction teardowns. I know you only need the teardown but I like symmetry. Those new setup/teardowns would work for suites. They would also have to work for individual tests as well since I assume you may have tests that are not grouped in suites. There would be rules about what happens when such setups or teardowns fail. The general case would look like this:     

  • The pre-transaction setup for the suite
  • The pre-transaction setup for the test
  • Begin transaction
  • The setup for the suite
  • The test
  • The teardown for the suite
  • Begin transaction
  • The post-transaction setup for the test
  • The post-transaction setup for the suite 

2. A test session setup and a test session teardown. The setup /teardown would be scoped for the test session. The session setup would be run at the beginning of a test session. After that all the tests in the session are run (each in its own transaction). At the end the session teardown would be run.  

I prefer the second alternative. The issue with the first one is that it would add a lot more rules to communicate to the user and I’d rather keep things simpler. It would add some overhead. It seems that the benefits we get do not justify complicating things so much.

If I go with the second solution in the session teardown you could have something like:

    DECLARE @MaxEntryId     tinyint

    SELECT @MaxEntryId = MAX(EntryId) FROM T1

    DBCC CHECKIDENT (T1, RESEED, @MaxEntryId) WITH NO_INFOMSGS

    SELECT @MaxEntryId = MAX(EntryId) FROM T2

    DBCC CHECKIDENT (T2, RESEED, @MaxEntryId) WITH NO_INFOMSGS

    ...

    SELECT @MaxEntryId = MAX(EntryId) FROM TN

    DBCC CHECKIDENT (TN, RESEED, @MaxEntryId) WITH NO_INFOMSGS

Let me know if this is fine with you.

 

Aug 11, 2010 at 12:50 PM
Edited Aug 11, 2010 at 2:49 PM
I completely agree with the symmetry aspect and think that is a great idea. Your idea of a single setup/teardown for the entire test session is also a great idea. It is *much* simpler than what I had suggested and serves the same purpose. In the session teardown, I could reset any IDENTITY values that need resetting. For that matter, with the session setup I could record all of the starting IDENTITY values if I needed to for some reason. The session setup/teardown solves my problem and keeps things simple and very usable.
Coordinator
Aug 15, 2010 at 11:58 PM

Done in the new release (V 1.6).

Note that the setup and teardown stored procedure must declare one int parameter. When TST calls them it will set the parameter with the value of the test session ID. The doc explains how one could use this parameter. See the section “Custom processing of the test results” in the new documentation.

Example:  

CREATEPROCEDURE SQLTest_SESSION_SETUP 
   @TestSessionId int
AS
BEGIN
   EXEC TST.Assert.LogInfo 'SQLTest_SESSION_SETUP'
   ...
END
GO

CREATEPROCEDURE SQLTEST_SESSION_TEARDOWN 
   @TestSessionId int
AS
BEGIN
   EXEC TST.Assert.LogInfo 'This is SQLTEST_SESSION_TEARDOWN'
   ...
END
GO