1

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.

5

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.

  • 1
    +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
5

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.

2

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

  • 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

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.