SQL Server Performance

OBJECT_ID

Discussion in 'General DBA Questions' started by JusticeLeague, Jul 12, 2005.

  1. JusticeLeague New Member

    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 1653101161
    When 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!
  2. FrankKalis Moderator

    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)
  3. Madhivanan Moderator

    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
  4. JusticeLeague New Member

    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?
  5. Madhivanan Moderator

    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 so
    Alter a table and see if there is any information in tempdb


    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

    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)
  7. saulius New Member

    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.
  8. FrankKalis Moderator

    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.
  9. Luis Martin Moderator

    May be something change from 2005 to 2011.[:p]
  10. FrankKalis Moderator

    Yes, I've seen that this thread is quite old. [:)]
  11. Madhivanan Moderator

    [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
  12. FrankKalis Moderator

    [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... [;)]
  13. Madhivanan Moderator

    <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&nbsp;add identity property to existing column via SSMS</P><P>[/quote]&nbsp;</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>

Share This Page