Table Design

Question

Table Design

Thiyagarajan Natarajan

SSC Enthusiast

Points: 106
More actions
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

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

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

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 1

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.

Thiyagarajan Natarajan SSC Enthusiast Points: 106 More actions · Answer 2

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:

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 4

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

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/