SQL Server Performance

Need help in ur example

Discussion in 'Getting Started' started by Hishamkmr, Feb 22, 2011.

  1. Hishamkmr New Member

    Dear Mr. Tom O'Neill,

    I gone throught your tutorial at site. It helped me a lot. I am asking you some more help. It will be helpful for me if you can describe what the code doing which I have marked in bold in the below procedure. This is your example at site. Also at the end do we need "GO". Please help me.



    Database Script to Create Tables for Exercises
    ALTER TABLE [dbo].[USERDETAILS] DROP CONSTRAINT FK_USERDETAILS_USERLIST
    GO
    if exists (select * from sysobjects where id = object_id(N'[dbo].[USERDETAILS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[USERDETAILS]
    GO
    if exists (select * from sysobjects where id = object_id(N'[dbo].[USERLIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[USERLIST]
    GO
    CREATE TABLE [dbo].[USERDETAILS] (
    [detail_id] [int] IDENTITY (1, 1) NOT NULL ,
    [usr_id] [int] NOT NULL ,
    [title] [varchar] (50) NULL ,
    [yrs_service] [numeric](18, 0) NULL ,
    [yrs_title] [numeric](18, 0) NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[USERLIST] (
    [usr_id] [int] IDENTITY (1, 1) NOT NULL ,
    [login] [varchar] (20) NOT NULL ,
    [pswd] [varchar] (20) NOT NULL ,
    [f_name] [varchar] (25) NULL ,
    [l_name] [varchar] (35) NOT NULL ,
    [address_1] [varchar] (30) NULL ,
    [address_2] [varchar] (30) NULL ,
    [city] [varchar] (30) NULL ,
    [state] [char] (2) NULL ,
    [zipcode] [char] (10) NULL , [varchar] (50) NOT NULL
    ) [B]ON [PRIMARY][/B]
    GO
  2. FrankKalis Moderator

    Welcome to the forum!
    Though I'm not Tom O'Neill, I'll try to answer your questions...
    object_id(N'[dbo].[USERDETAILS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    This is a check to see whether this object exists or not before attempting to drop it. The check tries to resolve the string 'dbo.USERDETAILS' to the internal object id and then tries to find a match in sysobjects where this id belongs to an object of type "usertable" (=meaning a table that was not shipped with SQL Server usually).
    ON [PRIMARY]
    This is a specification on which filegroup you want the object to be created.
    EDIT: clarification of 'IsUserTable'
  3. Hishamkmr New Member

    Hi Frankkalis,
    Thanks for your reply, but still I am confused. I am a beginner in SQL server.
    if exists (select * from sysobjects where id = object_id(N'[dbo].[USERDETAILS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    in the above statement. "id" is one of the column in sysobjects. I want to know what is "object_id(N'[dbo].[USERDETAILS]') " and "OBJECTPROPERTY(id, N'IsUserTable') = 1" is this a function?. Can you please describe how this work? I know this is a simple question but since I am a beginner I am not getting in to it. Please help me.
    Regards
    Hisham.A
  4. FrankKalis Moderator

    Yes, both OBJECT_ID() and OBJECTPROPERTY() are system functions. They are described in the SQL Server Books Online. Just follow the links and read through it.
  5. Hishamkmr New Member

    Thanks Frank.
  6. ismailadar New Member

    Hi Hishamkmr,

    I am not tom too, but i will try to answer your question as Frankkalis do.
    firstly sysobjects is system table that Contains one row for each object which are created in working database.
    you can check this table in order to understand whether any objects with spesific name is created before.
    shortly all objects have an ID information that arre stored in sysobjects.When you check and object you should use it's ID but remmering ID is too diffucult so there is a buiilt in function that takes object name as a parametre and returns objectID.
    object_id(N'[dbo].[USERDETAILS]') gives you object id of USERDETAILS table.
    OBJECTPROPERTY is also a builtin function thats give spesific information about an object. it takes 2 paarmeters the first one is id of object the other which property of object.
    OBJECTPROPERTY(id, N'IsUserTable') is a builtn in function that dediced whether object is a user defined table or system table.
  7. Hishamkmr New Member

    Thanks Ismail. I have some more doubts. I will post it later.
  8. satya Moderator

    In any case I would recommend you to refer to SQL Server Books Online that are installed along with SQL SErver or review documentation on MSDN for such first hand information.

Share This Page