I have a table

create table Objects (
    ObjectID bigint not null primary key,
    ObjectRef1 varchar(50) not null,
    ObjectRef2 varchar(50) not null,
    ObjectRef3 varchar(250) not null 

All fields are unique. The table has approximately 100 million rows. All columns have unique indexes, and are used frequently for queries.

What is faster? To normalize each of the varchar fields into seperate tables, or keep them as they are? If normalized, the table will only have the ObjectID column and ID's to the normalized tables, and I would do inner joins to get the values of ObjectRefX.

Should I consider other databases like Hadoop for this amount of data?

  • "this amount"? YOu think you have big data? Hint - I have a table with 5 billion rows at the moment and that is not big data ;) The rest really depends on usage - not a lot said here. Why bigint for the primary key, not int? – TomTom Aug 26 '13 at 8:34
  • bigint because I expect the table to grow to more than 2 billion rows. – Pål Thingbø Aug 26 '13 at 8:42

From your description, it sounds like normalizing your table would be the better choice due to reduced disk activity. I/O contention is the most significant bottleneck in most systems. If you normalize, you would reduce the size of each row, and since SQL Server reads data from disk in pages, smaller rows results in more rows per page and fewer pages to be read from disk.

This changes, however, if you need to use the ObjectRefX columns together (i.e., "SELECT ObjectRef1, ObjectRef2" or "WHERE ObjectRef1 = 'x' AND ObjectRef2 = 'y'"). If that is the case, the overhead of the joins would probably offset any benefits of the normalization.

And to answer your other question, no, 100 million rows is no problem for a well-organized SQL Server database. Many companies have databases well into the terabyte range with billions of rows. The only caveat is that the larger the database gets, the more management is required to keep it running well, but that's true of any large database on any platform.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.