Maximum number of Tables - is 4^15 big a number?

[ramraj]

Hello people,
I might sound a little bit crazy, but is there any possibility that you
can incorporate 4^15 (1,073,741,824) tables into a SQL Database?

I mean, is it possible at all? There might be a question of where
anyone would want so many tables, but i'm a bioinformatics guy and I'm
trying to deal with genomic sequences and was coming up with a new
algorithm, where the only limit is the number of tables I can put into
a Database.

So, can you please advise if its possible to put in so many tables into
a SQL database? Or is the Bekerley DB better?

[Erland Sommarskog]

(ra****@gmail.com) writes:

Hello people,
I might sound a little bit crazy, but is there any possibility that you
can incorporate 4^15 (1,073,741,824) tables into a SQL Database?

I mean, is it possible at all? There might be a question of where
anyone would want so many tables, but i'm a bioinformatics guy and I'm
trying to deal with genomic sequences and was coming up with a new
algorithm, where the only limit is the number of tables I can put into
a Database.

So, can you please advise if its possible to put in so many tables into
a SQL database? Or is the Bekerley DB better?

The maximum number of objects in an SQL Server database is 2^31-1,
or 2.147.483.647 and thus 4^15. "Objects" in this context are tables,
stored procedures, views etc. Note that this answer applies specifically
to MS SQL Server. If you with "SQL Database" means about any RDBMS, beware
that the answer is likely to be different for each product.

I don't really know why you think you need this many tables. But if the
tables all are to have the set of columns, then you only need one table,
but with more columns to keep the different portions of the table apart.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gert-Jan Strik]

ra****@gmail.com wrote:

>
Hello people,
I might sound a little bit crazy, but is there any possibility that you
can incorporate 4^15 (1,073,741,824) tables into a SQL Database?

I mean, is it possible at all? There might be a question of where
anyone would want so many tables, but i'm a bioinformatics guy and I'm
trying to deal with genomic sequences and was coming up with a new
algorithm, where the only limit is the number of tables I can put into
a Database.

So, can you please advise if its possible to put in so many tables into
a SQL database? Or is the Bekerley DB better?

In addition to Erland's answer: there might be some practical
limitations.

The smallest possible table, without indexes and only one row will
reserve 16 kilobytes. If you were to create 4^15 tables (without
indexes, primary key and unique constraint), and populate each table
with only 1 row, then you would need 16 TB. If all these tables have a
primary key constraint, that would be 24 TB. Also, if only a few bytes
per 8KB-page are used, then the Buffer Pool cannot be managed
efficiently, with the risk of poor performance.

Gert-Jan

P.S. Yes, it does sound crazy. If at all possible, then don't do it...

[Serge Rielau]

.... and if access to the tables in not heavily skewed there will also be
no opportunity for the SQL Compiler to cache query plans.
Death by compile....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html