• Ratheesh.K.Nair (7/8/2014)


    Hi Experts,

    Below are the original design and modifications recommended by us. Do i need to add anything or did i miss anything?

    --Original Table design

    CREATE TABLE ASC

    (

    [ASCID] UNIQUEIDENTIFIER NOT NULL,

    [MuthalID] UNIQUEIDENTIFIER NOT NULL,

    [ConsumableTypeID] UNIQUEIDENTIFIER NOT NULL,

    [RemainingYield] INT,

    [ReOrderDate] DATETIME,

    [Recommendation] NVARCHAR (50),

    [CreatedDate] DATETIME NOT NULL,

    [CreatedBy] UNIQUEIDENTIFIER NULL,

    [ModifiedDate] DATETIME NOT NULL,

    [ModifiedBy] UNIQUEIDENTIFIER NULL

    )

    --Recommendataion from our side.

    1)Create a Clustered Index on ASCID.

    2)Non Clustered Index on MuthalID.

    3)Non Clustered Index on MuthalId,ConsumableTypeId

    4)Change ASCID data type to INT (with Identity) to reduce the size of clustered index

    TIA

    Since these are recommendations I assume this must be something that is not yet built? #1 is a horrible recommendation UNLESS they also implement #4. A clustered index on a guid is just plain awful. Since this is the design phase I would HIGHLY recommend you change the name of the table. ASC is a reserved word and you will constantly be fighting with it. Also, a table name should give you an idea of what it contains. This seems like an abbreviation, I would recommend using an actual name instead.

    What about MuthalID and ConsumableTypeID. Both of these are also guids. From this structure I would assume those are foreign keys to another table. Do those tables also have these guids as clustered primary keys? How about CreatedBy? This also seems like another guid as a primary key. Can users be deleted from the system? Are they actual deletes or just soft deletes? Do you have the ability to permanently delete after a soft delete? If you use a guid here you either have to make it a foreign key (which will prevent permanent deletes) or use no foreign key and then you have no clue who performed the action once that user is deleted. All you would be left with is a meaningless guid.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/