SQL Server Performance

Xtype = 'L'

Discussion in 'General DBA Questions' started by chetanjain04, May 8, 2007.

  1. chetanjain04 Member

    I may have gone through the BOL thousands of times looking for various xtypes. But this time had a closer look at xtype = 'L'

    Object type. Can be one of these object types:
    C = CHECK constraint
    D = Default or DEFAULT constraint
    F = FOREIGN KEY constraint
    L = Log
    FN = Scalar function
    IF = Inlined table-function
    P = Stored procedure
    PK = PRIMARY KEY constraint (type is K)
    RF = Replication filter stored procedure
    S = System table
    TF = Table function
    TR = Trigger
    U = User table
    UQ = UNIQUE constraint (type is K)
    V = View
    X = Extended stored procedure

    What is xtype = 'L'. Just don't have any idea what is this? I hv never seen this. Does anybody here know this?
  2. satya Moderator

    In sysobjects table it is valid as per your reference and other xtype is valid to syscolumns & systypes system table. Based on the user defined data type the simplest level, a record is the physical storage associated with a table or index row. This is applicable to the columns that are for LOB data types (text, ntext, image etc.)

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. Adriaan New Member

    A log shipping pair perhaps?
  4. satya Moderator

    XTYPE varies on 3 system tables, obviously for sysobject its for Log and others it is for type of UDF used.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. Adriaan New Member

    Satya, could you explain in practical terms?
  6. satya Moderator

    Data types such as 'bigint','decimal','float','int','money','numeric ','real','smallint','smallmoney','tinyint' in addition to LOB data types I have referred above

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  7. Adriaan New Member

    Satya,

    Looking at the xtype column, it classifies various types of objects - tables, views, indexes, constraints, functions, procedures, triggers, and logs.

    Different system tables have an xtype column, and the 'L' value on the column does not seem to be valid on all system tables. But I can't quite follow how you go from the LOB data types, to the "log" object type.

    Educate me!
  8. satya Moderator

    My referene is for systypes and sycolumns, not specific to sysobjects. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />I believe we wait until Chetan comes back and say where and what he wants to clarify.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  9. Adriaan New Member

    Ah - now I get it ... I think ...

    There is just one list of values for xtype in BOL, and this is on the entry for sysobjects, but actually the 'L' value can only occur in systypes and syscolumns.
  10. satya Moderator

    If you search on XTYPE then you would get reference on other 2 system tables too. SYSOBJECTS reference for L is correct as of Log.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  11. Adriaan New Member

    You can find the references, yes, but there's no list of possible values there - only under sysobjects.
  12. satya Moderator

    True, I was referring until Kalen Delaney book and other available resources at my end. I believe this is a good quote to pickup to post under MVP newsgroups for further calrification.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  13. MohammedU New Member

    I believe it is left over from SQL Server 6.5...
    Becuase in sql server 6.5 and before database log file was stored in the database not as .ldf file it was called syslog or something like that...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  14. chetanjain04 Member

    Hello All,

    actually we would be doing an entire export of database and then would be importing it into a new db. hence, i wanted to ensure we are not missing any objects. Log is just something which aroused a question in my mind. I thank all for posting your ideas on this one..

    Thanks,

    Chetan
  15. satya Moderator

    Hmm, you come with a simple question for a major discussion here [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]. Anyways I have shooted the other XTYPE=L based to SQL Dev.team.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>

Share This Page