Home Forums SQL Server 2016 SQL Server 2016 - Administration Help me in designing table and its indexes RE: Help me in designing table and its indexes

  • March 3, 2018 at 7:39 am

    #1981842

    SQLAddict01 - Friday, March 2, 2018 3:07 PM

    CREATE TABLE FileMeta(Id [Some Data Type] NOT NULL, ParentId [Some Data Type] NOT NULL, Type CHAR(1) NOT NULL /* F = File or D = Directory */, AccountId [Some Data Type] NOT NULL, Name VARCHAR(255))
    SELECT …FROM FileMetaWHERE AccountId = [Foo]AND Id = [Bar]
    SELECT …FROM FileMetaWHERE AccountId = [Foo]AND Name = ‘[Some Name]’

    trying to understand and design indexes
    how would you choose indexes?

    You're trying to build a "Parent/Child" table, which is frequently referred to as an "Adjacency List".  I see by your comments that it's going to be used for file directory structures in some way.

    I also see that you're setting yourself up to perpetuate the need for RBAR to traverse the directory structure.  That means that you're actually missing a few columns if you want whatever project you're working on to result in a very high performance solution and so we can't yet recommend any indexes because the columns you actually need indexes on are missing.

    Please see the following articles on a very high performance hybrid method not only for making it so queries against the hierarchy run insanely fast, but maintenance of the hierarchy is easy and even more insanely fast.

    Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url]
    Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations[/url]

    Also and just in case you don't know (many don't), be careful where you use the tokens of "foo" and "bar" in any of your discussions or code because you might not know what the source of those names are but someone you speak to may.  They're a derivative the pronunciation guide  (foÍžobär) of the military slang term "FUBAR", which is a NSFW (Not Safe For Work) term especially in the presence of people that are sensitive to swear words.  I won't even use the terms in code and especially not as examples of code (and, yes, I was in the military). 😉

    --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.

    Change is inevitable... Change for the better is not.

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