how many tables can an MS SQL database hold?

Question

I've ran into this cryptic statement for SQL Server: Files Per Database 32,767. What does that mean exactly? Is there a maximum number of tables for a given version of SQL Server. We try to support SQL Server post 2005 32-bit and 64-bit.

So if anyone has a handy dandy table they use to figure out how many tables they can have per DB for Microsoft SQL Servers I'd heartily appreciate seeing it.

I see this http://msdn.microsoft.com/en-us/library/ms143432%28v=SQL.90%29.aspx still don't know what files per database means though... — Peter Turner, Apr 15 '10 at 14:22
If you have 32,767 tables in your database, you're doing it wrong. — davr, Apr 15 '10 at 15:44
Then by all means, point me in the right direction! I'm just evaluating edge cases in my software. — Peter Turner, Apr 15 '10 at 17:58

score 6 · Accepted Answer · edited Apr 13 '17 at 12:14

6

The number of tables is limited by the global size of the database and namespace only, as far as I know. So as long as you have the storage it should not matter. In any case your design is probably broken if you have a large amount of tables. Tables should only be used for data definition, not carry information about the data inside (which is the typical usecase when you generate large amounts of tables).

Files per database limitation comes into play when you are in a situation with multiple files/file group for the database. See the previous discussion at SQL Server 2005 / 2008 - multiple files / filegroups - how many? Why? for more information about files and file groups in sql server.

edited Apr 13 '17 at 12:14

Community

1

answered Apr 15 '10 at 14:33

pehrs

8,749
29
46

2

+1, "If you're asking about the maximum limit, you're probably doing it wrong" - TDWTF – Chris S Apr 15 '10 at 17:56
@pehrs This is my question over years. I have a table that store Id/Name so i concatenate id's with comma and store in one column. I wrote a function that get comma separated string and return table with one column and When i want to search something in this string use the IN operator from function result. is this a bad way? Another important issue might be this: If i would to use additional table to hold extra information i should be add more than 40 tables to current about 200 tables in my db. Is your opinion on adding tables yet? please describe if you could. – QMaster Oct 29 '14 at 20:47

GregD · Answer 2 · 2010-04-15T17:46:09.617

6

From that same spec sheet:

Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

edited Apr 15 '10 at 17:46

answered Apr 15 '10 at 14:37

GregD

8,713
1
23
35

score 3 · Answer 3 · answered Apr 15 '10 at 16:00

3

It's not tables, it's file system datafiles. Log files, data files, temp files, etc. Each DB can have more that one file.

answered Apr 15 '10 at 16:00

Dayton Brown

1,549
2
13
23

So you mean 32,767 is the max number of files, but the usual number of files is like 4-5? – Peter Turner Apr 15 '10 at 18:02
Yup. It depends of course on complexity, etc. But most DB's I've seen only have 2 to 10 or so. You have at least 2 per database. – Dayton Brown Apr 16 '10 at 20:25

how many tables can an MS SQL database hold?

3 Answers3