SQL Server Performance

Updating computed columns

Discussion in 'T-SQL Performance Tuning for Developers' started by starwars8818, Jul 6, 2004.

  1. starwars8818 New Member

    Hi Guys,<br /><br />Can anybody tell me how I can update computed columns (preferably on a system table)<br /><br /><br />Starwars8818<br /><i></i>Think, Think, Think again. Blessed you are for being born a human. [<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />)]
  2. satya Moderator

    Why it is required on a system table?
    In general its not recommended to update system tables directly.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. derrickleggett New Member

    (preferably on a system table)<br /><br />DON'T!!!! <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Think, Think, Think again. Blessed you are for thinking before do. Where's the little green guy icon?<br /><br />Otherwise, you would use ALTER TABLE.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  4. starwars8818 New Member

    Hi guys,

    Lemme put it straight forward. I wanted to update sysstats in sysobjects to hide the objects in some user database. converting (masking) the second bit from right in the sysstats column of sysobjects should hide the object. Ofcourse you can hide system objects from EM, but I want to hide user objects from T-SQL. Guys help me around..........

    Starwars8818
    Think, Think, Think again. Blessed you are for being born a human.
  5. satya Moderator

    The big question why to hide the objects, there is more harm than any use by performing update on system tables. And MS will not support if you have contract with them.

    I follow and refer Kalen Delaney's reference:
    The jury's still out about whether directly accessing SQL Server system tables is a good thing. The SQL Server 7.0 documentation states explicitly that you shouldn't access system tables, but the SQL Server 2000 documentation loosens up that advice a bit: Reference of documented columns in system tables is permissible. However, many of the columns in system tables are not documented. Applications should not be written to query or update undocumented columns directly.

    What about updating the system tables? The documentation stresses that "system tables should not be altered directly by any user." I completely agree with this advice. With older versions of SQL Server, I sometimes had to directly update a system table because they offered no other way to do what I needed. But in SQL Server 7.0 and 2000, Microsoft supplies enough system procedures and other tools to do any system-table modification I've required.


    To highlight what Derrick mentioned, to update the system tables SP_CONFIGURE must be used and further information :
    Execute permissions on sp_configure with no parameters, or with only the first parameter, default to all users. Execute permissions for sp_configure with both parameters, used to change a configuration option, default to the sysadmin and serveradmin fixed server roles. RECONFIGURE permissions default to the sysadmin fixed server role and serveradmin fixed server role, and are not transferable.


    HTH
    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. derrickleggett New Member

    If you want this level of viewing rights, then you need to control it at the user level. Set up a user and specifically deny them access to anything they don't have access to. Screwing around with sysobjects is not the way to go about doing this. Make sense?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  7. vbkenya New Member

    OK....If you insist on hiding your tables through this perilous route, here is what you have to do:

    1. Enable modofication of system objects through the properties dialog box of the SQL server instance -General tab
    2. Open the sysobjects table
    3. Change the value in the xtype field for the table to "S" from "U"
    4. Close the sysobjects table
    5. Refresh the view

    This is a little bit safer coz' you don't need to touch the sysstat field at all.



    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  8. starwars8818 New Member

    Hi guys,

    In simple words, I don't want to do this in production environment. Only in Test server which developers will also be using (NOT IMPORTANT SERVER). I want to keep some data regarding appraisals temporarily in that table before I move it on. The name may be tempting to some users to dig and find out what it contains. In the first place they should not be able to see the table at all.

    I want to avoid permissions regarding database and server roles. Please eliminate this option out totally.

    FORGET EVERYTHING !! JUST TELL ME ONE THING:-

    How do I update computed columns??

  9. satya Moderator

    Ok, you're clear now.
    You may follow Nathan's steps or here is what I did previously at my test environment.

    Open ISQL/w and USE the pubs database.


    In the Query text box, type
    SELECT * FROM sysobjects
    WHERE type = 'U'

    Change the sysstat value of publishers from 83 to 81. (The sysstat value of 83 represents the bit setting 1010011. The second bit from the right is the display bit, which you must turn off: 1010001. This bit setting corresponds to sysstat value 81.)


    Allow updates to the system tables by entering the following statements in ISQL/w:
    sp_configure 'allow updates', 1
    RECONFIGURE with override
    go

    Update sysobjects by entering the following statements in ISQL/w:
    SET sysstat= (sysstat^2)
    WHERE name = 'publishers'
    sp_configure 'allow updates', 0
    RECONFIGURE with override
    go

    This command tells the system to flip the current setting of the second bit in sysstat. The first time you run this command, it turns off the bit; rerunning the command turns on the bit.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. starwars8818 New Member

    Hi satya. On running

    SET sysstat = (sysstat^2)
    WHERE name = 'publishers'
    sp_configure 'allow updates', 0
    RECONFIGURE with override
    go


    I get

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '='.


    I also added UPDATE SYSOBJECTS to the first line. Error given is

    Server: Msg 170, Level 15, State 1, Line 4
    Line 4: Incorrect syntax near 'sp_configure'.


    Starwars8818
  11. satya Moderator

    As you're over the first error by adding UPdate sysobjects, just add GO before SP_CONFIGURE in order to complete that statement.
    (execute both statements seperately)

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page