Indexing the PO

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

    Comments posted to this topic are about the item Indexing the PO

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71320

    Nice question, thanks Steve
    a slight twist - If the OrderKey column is of type INT, the ALTER TABLE script will fail with error "cannot convert varchar to int"

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    โ€œlibera tute vulgaris exโ€

  • Shayn Thomas

    SSCertifiable

    Points: 5392

    got it wrong but learned something in doing so
    cheers steve

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    Stewart "Arturius" Campbell - Wednesday, August 29, 2018 11:01 PM

    Nice question, thanks Steve
    a slight twist - If the OrderKey column is of type INT, the ALTER TABLE script will fail with error "cannot convert varchar to int"

    I still had the "old" table in my playground database from a prior QOTD.
    For a brief moment, I thought that was the catch that would make "The index is not created because of another error" the correct answer.

  • webrunner

    One Orange Chip

    Points: 29866

    Thanks, I learned something.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Jeff Moden

    SSC Guru

    Points: 994284

    Heh... everytime I'm reminded of this, I end up asking myself... who would index a non-persisted column?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Jeff Moden - Thursday, August 30, 2018 11:39 AM

    Heh... everytime I'm reminded of this, I end up asking myself... who would index a non-persisted column?

    Well, if it is a covering index, then the data doesn't need to be stored in the main table, just the index.  If there is an index on the computed column it would seem valid that the data in the computed column is persisted in the index.

  • Jeff Moden

    SSC Guru

    Points: 994284

    Lynn Pettis - Thursday, August 30, 2018 12:12 PM

    Jeff Moden - Thursday, August 30, 2018 11:39 AM

    Heh... everytime I'm reminded of this, I end up asking myself... who would index a non-persisted column?

    Well, if it is a covering index, then the data doesn't need to be stored in the main table, just the index.  If there is an index on the computed column it would seem valid that the data in the computed column is persisted in the index.

    Hmmmm.... good point and very interesting, Lynn.  That could make for a pretty decent disk space savings on longer tables not to mention a reduction in the actual width of the CI.  Seems like it would also result in a memory savings to not persist the column.  Got some testing to do.  Thanks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

    I saw something interesting like this with one of Brent's demos.

    Parameter from app is nvarchar()

    Table is

    create table Sometable (
    somepk int
    , someval varchar
    )

    Can't change the schema, but add

    alter table sometable add somevalnvarchar nvarchar()

    then index the new column. Can be used by the optimizer, not persisted,

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply