Post reply

Table Design

  • September 15, 2011 at 3:48 am

    #244884

    Hi SQL Experts,

    I like to get some advise from you. My tables have to accept very huge records like millions level.The number of columns are also huge like 125 to 150 in each table. The data quality is also very bad so we won't be able to create any primary/foriegn keys on that tables. So I can't have Normilization

    also.

    In this case, how can design my tables. I like to have Indexs too. But Indexes also occupy more spaces. so I just created only one Non-Clustered Index on each table.

    Please let me know your thoughts/advice.

    Thanks

    Thiyagarajan N

  • September 15, 2011 at 5:16 am

    #1381828

    What is stopping you not to have a normalization, any business rule/your supervisor/anything? A table with non cluster index is called heap which will have many draw backs like fragmentation ther by more IO utilization etc.

  • September 15, 2011 at 5:22 am

    #1381835

    Thanks for the prompt attention.

    Yes, there are lot of business need and the supervisor (rule), for not to have the Normalization... I know it is very very bad design. But no choice for me...I can't even spilit the tables by 2 or 3....:angry:

  • September 15, 2011 at 5:50 am

    #1381858

    I'm at a bit of a loss here. Common and best practices are to split up tables, apply unique constraints, primary key constraints, foreign key constraints and build clustered indexes. Now, you're saying you can do NONE of those, but, hey, I'd like things to run faster. Sorry. All of those common, best practices are there because they are what we do to solve the issues you're describing. You're asking me to make the car run faster, but no, sorry, I can't have an engine in it.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 15, 2011 at 6:06 am

    #1381874

    Millions of rows with hundreds of columns sure can give a problem without your indexes well chosen.

    First thing is: always create a primary key with a clustered index. Second I would create non-clustered indexes on the columns you normally use for the where clause and joins. Use include for (measure) columns you normally add in the select. If a query runs slow, look at the execution plan if it suggests to add an index.

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • September 15, 2011 at 7:02 am

    #1381907

    You need to develop a strategy to cleanse the data so that you can normalize it, create Clustered Indexes, Primary and Foreign Key Constraints as was suggested.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

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