December 30, 2008 at 12:16 pm
Hi Guys,
Does anybody know,how SQL SERVER handles Identity fields.I mean How,the Identity fields have been implemented in SQL SERVER.
December 30, 2008 at 1:06 pm
Basically, an identity column is used when an automatically incremented value is required. If used in a table where rows are regularly deleted, gaps in the sequential numbers will occur. If you want to avoid that situation, don't use Identity.
Do you have a more specific question? You can find all the information you need about this in Books OnLine.
Greg
December 31, 2008 at 10:31 am
No,My question was that,when you create an identity field in a table,How SQL SERVER handles it?Where does it keep the latest value for that field?Does SQL SERVER only use sysColumns for covering that?How can I populate autoval by myself?
December 31, 2008 at 10:51 am
Aspet Golestanian Namagerdi (12/31/2008)
No,My question was that,when you create an identity field in a table,How SQL SERVER handles it?Where does it keep the latest value for that field?Does SQL SERVER only use sysColumns for covering that?How can I populate autoval by myself?
It tracks that info in some (unspecified) system table we no longer get to see, so we dont' have direct access to modifying that (which is a good thing).
You can however:
- manually insert values of your own by temporarily using SET IDENTITY_INSERT MyTable ON/OFF.
- change the assingment pattern using the appropriate parameters in the IDENTITY definition.
- change the starting point by using DBCC CHECKIDENT('MyTable', RESEED,myValue).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 12:35 pm
we want to take out the Identity property in a large table for a project,which we do not have any problem with that.The problem is that,in case that we need to rollback(take back the Identity property),we need to do it as fast as possible.So I can not rely on creating new table ,transfer the data to it....
So, I need a way to work directly with system tables(I guess it is SysColumns)
December 31, 2008 at 12:37 pm
Aspet Golestanian Namagerdi (12/31/2008)
we want to take out the Identity property in a large table for a project,which we do not have any problem with that.The problem is that,in case that we need to rollback(take back the Identity property),we need to do it as fast as possible.So I can not rely on creating new table ,transfer the data to it....So, I need a way to work directly with system tables(I guess it is SysColumns)
those tables don't really exist as tables anymore in 2005, and all direct updates to them is disallowed. I am sorry to say, but you will find you CAN'T work directly through the system tables.
Where they really are, and WHAT they really are is hidden somewhere in the DBEngine.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 31, 2008 at 1:57 pm
Aspet Golestanian Namagerdi (12/31/2008)
we want to take out the Identity property in a large table for a project,which we do not have any problem with that.The problem is that,in case that we need to rollback(take back the Identity property),we need to do it as fast as possible.So I can not rely on creating new table ,transfer the data to it....So, I need a way to work directly with system tables(I guess it is SysColumns)
As far as I know you can’t modify an identity column to become none identity column, so it is interesting for me to know why it isn’t a problem for you to cancel the identity, but it is a problem to recreate the identity. Can you pleas explain it?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply