SQL Server Performance Forum – Threads Archive
OBJECT_ID
Does OBJECT_ID change when, say, you alter a table or do something else with the table. From how SQL generate a test script when creating objects (ie a Table) it seemed that object_id is always equal to sysobjects.Id, sysobjects.Id that was set when the object was created. Say for example when you create a table TestTable and query sysobjects you got 1653101161When you do anything with TestTable you will still have 1653101161 as its Id in the sysobjects. Is there any chance that it will change? Thanks in advance!
No, it won’t change unless you drop and recreate it. And yes, it is the the id column from sysobjects. OBJECT_ID is simply a wrapper for convenience that you don’t have to query the system tables directly. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
As Frank says,it wont change until you drop that object
It used in queries to get object id Select object_Id(‘objectName’)
Madhivanan Failing to plan is Planning to fail
Hi! Thanks for the replies. What can you about this: that when you add a field to a table or alter it, sql creates a temp table based on the table you are altering, is this true? Can some explain if it is and if not what sql technically does?
quote:when you add a field to a table or alter it, sql creates a temp table based on the table you are altering, is this true?
I dont think soAlter a table and see if there is any information in tempdb
Madhivanan Failing to plan is Planning to fail
quote:Originally posted by JusticeLeague
What can you about this: that when you add a field to a table or alter it, sql creates a temp table based on the table you are altering, is this true? Can some explain if it is and if not what sql technically does?
This is only true when you use Enterprise Manager to modify the table structure. However, this is no "real" temp table, but rather an intermediate table. Once the data is pushed from original to this intermediate table back again to the modified original table, the table will still have the same OBJECT_ID that it had before modification. It is just an example on how inefficient Enterprise Manager works most of the time. You can easily check this via a small script in Query Analyzer:
create table test (c1 int)
select object_id(‘test’)
alter table test add c2 int
select object_id(‘test’)
drop table test
———–
1856725667 (1 row(s) affected)
———–
1856725667 (1 row(s) affected)
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
I think it is correct but a bit misleading. If you for example disallow nulls in some column that table will change it’s id.
Welcome to the forum!
How did you make the change to the column? Was it done using SSMS? Sometimes these "visual" tools have the nasty habit to drop and recreate a table behind the scenes, when you think you are "only" altering a column.
May be something change from 2005 to 2011.[
]Yes, I’ve seen that this thread is quite old. [
][quote user="FrankKalis"]
Welcome to the forum!
How did you make the change to the column? Was it done using SSMS? Sometimes these "visual" tools have the nasty habit to drop and recreate a table behind the scenes, when you think you are "only" altering a column.
[/quote]
Yes. It happens when we add identity property to existing column via SSMS
[quote user="Madhivanan"]
[quote user="FrankKalis"]
Welcome to the forum!
How did you make the change to the column? Was it done using SSMS? Sometimes these "visual" tools have the nasty habit to drop and recreate a table behind the scenes, when you think you are "only" altering a column.
[/quote]
Yes. It happens when we add identity property to existing column via SSMS
[/quote]
You should have known better… [
]<P mce_keep="true">[quote user="FrankKalis"] <P>[quote user="Madhivanan"]</P><P>[quote user="FrankKalis"] </P><P>Welcome to the forum!</P><P>How did you make the change to the column? Was it done using SSMS? Sometimes these "visual" tools have the nasty habit to drop and recreate a table behind the scenes, when you think you are "only" altering a column. <BR></P><P>[/quote]</P><P>Yes. It happens when we add identity property to existing column via SSMS</P><P>[/quote] </P><P>You should have known better… <IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"> <BR></P><P>[/quote]</P><P>Yes. I am telling to those who did not kow this <IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"></P>
]]>