How to change an identity column value in SQL

April 3, 2012 at 10:14 AMMadestro

It's not very obvious to the non-db-admin type but you can't just override an identity value in a table, even if you set identity_insert ON.

What you need to do instead is:

  • Turn identity_insert ON
  • Delete the record you need to change
  • Insert it again with the desired identity value
  • Turn identity_insert OFF

Here is the code snippet:

SET IDENTITY_INSERT YOUR_TABLE ON
DELETE FROM YOUR_TABLE WHERE YOUR_IDENTITY_FIELD = THE_ID_YOU_NEED_TO_CHANGE
INSERT INTO YOUR_TABLE (YOUR_IDENTITY_FIELD, ...[other fields]) VALUES (THE_NEW_ID,  ...[other fields])
SET IDENTITY_INSERT YOUR_TABLE OFF

Posted in: Databases

Tags:

Expanding the list of databases in SQL Management Studio 2008 takes a long time or loads indefinitely

January 13, 2012 at 1:38 PMMadestro

I was trying to connect to my hosted database with SQL Management Studio 2008 when I ran into an issue. I was able to connect fine using my database credentials but when I attempted to expand the Databases node, Object Explorer just hung trying to load the list of databases. I could run queries and everything, but the database list node would not load.

The issue turned out to be the amount of data being loaded by Object Explorer. Because my provider hosts a lot of databases, Object Explorer was taking an unusually long time loading all the information for every database in the list.

The solution to this issue is:

  • Login to any database server
  • Select the "Databases" node in Object Explorer
  • In the top menu, select "View -> Object Explorer Details"
  • When the "Object Explorer Details" tab opens, right click on any of the headers (.e.g Name, Owner, etc) to get a list of columns to display
  • Uncheck all the columns you don't need. I personally keep only the Name and Owner columns checked
  • Restart SQL Management Studio

Log in to your server again and the databases should be displayed normally.

Here is the link to the original post I found in order to troubleshoot this issue: http://stackoverflow.com/questions/2013923/expanding-list-of-databases-in-sql-server-2008-management-studio-takes-longer-th

Posted in: Databases

Tags: