Click here to Skip to main content
13,195,379 members (66,669 online)
Rate this:
Please Sign up or sign in to vote.
Hi folks,

This is a 'best practices' question. I am replacing an application that used the Unidata (Pick like) database. The application itself is a messaging process, which is not a problem, neither is the setup of the table. My question is of an index nature. In Unidata, there is a small table for 'pending' messages, and a very large table for 'sent' messages.

My question is: could I combine the two tables, using a Boolean field that would be equal to 1 when a message was pending (not yet sent), then changed 0 when sent. Could I create an index on this field (column), but only when the field is greater than 0? And would this be an efficient way to do this? Or is it better to continue to have two tables to do this? My main concern is to make sure the 'pending' messages can be searched efficiently.

Thank you for your assistance,
Rich Algeni
Posted 5-Mar-12 11:25am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1


My experience is with SQL Server 2005 for what it's worth. I don't know that combining the two tables is a good idea. Indexing is a bit of an expensive operation in terms of adding and removing records. Once the index is complete, the searching and retrieving of records is quite fast. I'm not aware of any to index only if a value meets a certain criteria.

For speed purposes, I would personally keep the two tables separate. Of course, the only way to be sure is to create a test case. I'm sure you could export your data to your DB of choice and run a test to see how much effort it takes to do the two different setups. With a bit of logging and probably a day's effort or less, you'll have a better feel for the right choice.

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Thanks for the info Hogan! I'll do some testing to see what's best. I wasn't sure if I had completely run off the tracks!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.171019.1 | Last Updated 6 Mar 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100