-1

I do a unit test Add/Insert a data record into a table with column name Id which is an identity column.

In the unit test I manually insert the id. So I could set the Identiy_Insert to ON before the Insert statement.

Are there any drawbacks keeping this feature turned ON for the production server?

Or should I change my unit testing approach? Inserting a value for the Name field and retrieve

and ASSERT it should be prove enough that the data record got inserted for the integrations

sake.

Pascal
  • 12,265
  • 25
  • 103
  • 195
  • 2
    The `IDENTITY_INSERT ON` setting is an **exceptional** setting which you should turn on **ONLY when absolutely needed** (and turn off again as quickly as possible). – marc_s Feb 25 '12 at 21:27
  • Hard to follow what the actual question is here. – Tim Abell Jan 07 '15 at 18:53

2 Answers2

2

its not a feature you can keep ON. It is valid per connection.

give it a try, open a connection on SSMS and set it to ON to one table. Then open another connection and you will see that if you try to insert the IDENTITY on the same table, it will fail.

EBarr
  • 11,826
  • 7
  • 63
  • 85
Diego
  • 34,802
  • 21
  • 91
  • 134
  • 1
    The identity insert (manually insert an ID) will only be used for integration testing purpose. Later on the production server there will be no manually ID inserting. - although it would be possible because its turned ON - – Pascal Feb 25 '12 at 20:18
  • 1
    but what I meant is that you cant turn it ON indefinitely. It is a setting that is valied per session. That's why I told you to test on SSMS. Open 2 connections to the same DB, turn identity insert ON to a talbe in one of them and try to insert the Identity using the other connection. You'll see that it will fail – Diego Feb 25 '12 at 20:23
  • and for what is that test? In my integration tests there will be no second connection opened and then access the same table! So what is the point. What are you trying to say to me? – Pascal Feb 25 '12 at 20:34
  • 1
    I am trying to tell you that IDENTITY INSERT is not something you can keep ON 100% of the time. If you set it ON, it will be ON only to the connection that you used to set it ON. It will be OFF to every subsequesnt connection. If you do what I said you will see that. – Diego Feb 25 '12 at 20:39
  • I know that I have to set it ON do an insert and set it OFF again because only table table can have it set ON see this old post from me: http://stackoverflow.com/questions/9404001/unit-testing-with-identity-columns. Anyway I just realized I am on the wrong track. My code in the Add method did an explicit insert of the testplanId of the Testplan table. That was my error therefore I got the Identity_Insert not set etc... error message :P – Pascal Feb 25 '12 at 20:58
  • 1
    hahah OMG you still did not understand what I wanted to say. But anyway, glad you solved your problem – Diego Feb 25 '12 at 21:27
-1

From MSDN:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

In general, I would not recommend using IDENTITY_INSERT in production to avoid concurrency issues. As for the unit tests, I'd suggest creating a mocked data access layer instead of relying on a real database.

Yuriy Guts
  • 2,180
  • 1
  • 14
  • 18
  • The manual insert comes only from within my integration tests. In production there user inserted data records do not insert the ID EXPLICITLY) – Pascal Feb 25 '12 at 20:21
  • I find that a mocked database layer becomes cumbersome and really does NOT add more value than a good set of integration tests that manage their own data setup / usage. Mocking in most cases wastes time, adds complexity to the solution as well as additional coding time without getting a true sense of how the data works in the field. – tsells Feb 26 '12 at 01:09