Take the 2-minute tour ×
Server Fault is a question and answer site for professional 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

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.