Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
I see this 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
6  
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

3 Answers 3

up vote 4 down vote accepted

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 http://serverfault.com/questions/29097/sql-server-2005-2008-multiple-files-filegroups-how-many-why for more information about files and file groups in sql server.

share|improve this answer
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 at 20:47

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.

share|improve this answer

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.