Hello All,

I am about to code a realy "cool" in my opinion system that should become quite popular soon. So Im wanting to code an efficiant system, their will be multiple LARGE tables.

To ensure that the system is efficiant, what is the recommended maximum entities for a database to hold. I will be able to code a system that switches for example
- songlist1
- songlist2
Rather than one big 'songlist'.

Thanks in Advance.

To ensure that the system is efficiant, what is the recommended maximum entities for a database to hold.

There isn't a recommended max that would be up to the DBA/DB Designer to decide

in a text only data schema > 10,000,000 rows would be nothing out of the ordinary, but in a blob/binary data schema 10,000 could be far too many.

Provided you normalise the table structures and set indexes on the appropriate columns to speed up queries and/ searches, splitting up primary tables and the added complexities should not be needed.

I'm not sure whether you're asking how many rows can live in a table, or how many tables can live in a database? Either could be an entity.

Sorry for not being clear. Im reffering to rows in a table.

Thank you for your input Chis Hirst, that ammunt is fine by me.

Thanks.

In that case, the maximum rows that can be in a table without killing the system is almost limitless. Databases are meant to store amazing volumes of info. Think of the IRS and social security numbers, they have at least 300,000,000 records. They also have better servers, but the point is databases love flexing their muscle on huge amounts of data. It comes natural to them, like dogs chasing cats.

How is your table set up? This can have a huge impact on how well the system can perform against it, and if you want to explain that part in more detail, I have a feeling you'll get a much more accurate number than "a whole lot." The main thing is if you're table is really really tall ( vertically, rows ), it shouldn't be too far ( horizontally, columns and their widths ).

The database is confusing, i am aware that a database can have multiple rows. That's the whole point of a database, but I'm wanting to make queries work better. Show results faster and update faster, i presume if i split the database every 4,000 rows it will allow faster database searching.
I'm just presuming.

A "normalised" structure with indexes would be quicker than having the primary data spread over several tables.

What you're talking about is called partitioning ( breaking your data into parts ) and it's the horizontal type ( each table has an entire record, and an identical structure, but a row could wind up in any of several tables ) instead of vertical ( where you break the fields in a record up, and have part of the same record in each table, all of them with a different schema ).

SQL Server can do this for you automatically, and it's the recommended way to go if you have more than a few hundred million rows in a table. I get the sense you can see the ways this can be beneficial; if each table has less records, that's less work to find one, or add a new one. Now let's look at some drawbacks.

First, you need to come up with a partition scheme; if you have ten tables instead of one, and somebody wants to add a record, which table does it go into? You have to write a specialized function to answer that, and it gets run before every row is added. Same with finding a row. This overhead can be more than dealing with a clustered index with a ~90 % fill factor.

Plus, maintenance, and changes to fields and indexes can force you to do the same thing to a long list of tables, instead of just one. And if you ever need to query statistics across the board, thing of how awful writing the SQL will be!

I've been in charge of about 235 million rows in a SQL Server table; most queries executed in less than five seconds. It was a dedicated server, and a pretty good one, but it was also a well designed table. Unless you're expecting your system to scale beyond that, or you have thousand-byte char or image fields, I think you'll be better off in the long run with a single table.