|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-07 : 12:15:14
|
| [code]Maximum Capacity Specifications Topic last updated -- January 2004The first table specifies maximum capacities that are the same for all editions of Microsoft® SQL Server™ 2000. The second and third tables specify capacities that vary by edition of SQL Server 2000 and the operating system.This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements. The table does not include Microsoft SQL Server 2000 Windows® CE Edition. Maximum sizes/numbers Object SQL Server 7.0 SQL Server 2000 Batch size 65,536 * Network Packet Size1 65,536 * Network Packet Size1 Bytes per sort string column 8,000 8,000 Bytes per text, ntext, or image column 2 GB-2 2 GB-2 Bytes per GROUP BY, ORDER BY 8,060 8,060 Bytes per index 900 9002 Bytes per foreign key 900 900 Bytes per primary key 900 900 Bytes per row 8,060 8,060 Bytes in source text of a stored procedure Lesser of batch size or 250 MB Lesser of batch size or 250 MB Clustered indexes per table 1 1 Columns in GROUP BY, ORDER BY Limited only by number of bytes per GROUP BY, ORDER BY Limited only by number of bytes per GROUP BY, ORDER BY Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement 10 Columns per index 16 16 Columns per foreign key 16 16 Columns per primary key 16 16 Columns per base table 1,024 1,024 Columns per SELECT statement 4,096 4,096 Columns per INSERT statement 1,024 1,024 Connections per client Maximum value of configured connections Maximum value of configured connections Database size 1,048,516 TB3 1,048,516 TB3 Databases per instance of SQL Server 32,767 32,767 Filegroups per database 256 256 Files per database 32,767 32,767 File size (data) 32 TB 32 TB File size (log) 4 TB 32 TB Foreign key table references per table 253 253 Identifier length (in characters) 128 128 Instances per computer N/A 16 Length of a string containing SQL statements (batch size) 65,536 * Network packet size1 65,536 * Network packet size1 Locks per connection Max. locks per server Max. locks per server Locks per instance of SQL Server 2,147,483,647 (static)40% of SQL Server memory (dynamic) 2,147,483,647 (static)40% of SQL Server memory (dynamic) Nested stored procedure levels 32 32 Nested subqueries 32 32 Nested trigger levels 32 32 Nonclustered indexes per table 249 249 Objects concurrently open in an instance of SQL Server4 2,147,483,647 (or available memory) 2,147,483,647 (or available memory) Objects in a database 2,147,483,6474 2,147,483,6474 Parameters per stored procedure 1,024 2,100 REFERENCES per table 253 253 Rows per table Limited by available storage Limited by available storage Tables per database Limited by number of objects in a database4 Limited by number of objects in a database4 Tables per SELECT statement 256 256 Triggers per table Limited by number of objects in a database4 Limited by number of objects in a database4 UNIQUE indexes or constraints per table 249 nonclustered and 1 clustered 249 nonclustered and 1 clustered 1 Network Packet Size is the size of the tabular data scheme (TDS) packets used to communicate between applications and the relational database engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.2 The maximum number of bytes in any key cannot exceed 900 in SQL Server 2000. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. For more information, see Maximum Size of Index Keys.3 The data portion of a database cannot exceed 2 GB in size when using the SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine (MSDE) 1.0. The total size of the database, including log files, can exceed 2 GB provided the sum of the sizes of the data files remains 2 GB or lower.4 Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.Maximum Numbers of Processors Supported by the Editions of SQL Server 2000This table shows the maximum number of processors that the database engine in each SQL Server 2000 edition can use on symmetric multiprocessing (SMP) computers. You can install an edition of SQL Server on a computer that has more processors than the database engine will use, but the database engine will not use more processors than is indicated in this table. For example, you can install SQL Server Standard Edition on an eight-processor computer running Windows 2000 Advanced Server, but the database engine will not use more than four of the processors.Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine (MSDE 2000) SQL Server CE Enterprise Evaluation Edition Microsoft Windows 2000 DataCenter 32 4 2 32 2 N/A 32 Windows 2000 Advanced Server 8 4 2 8 2 N/A 8 Windows 2000 Server 4 4 2 4 2 N/A 4 Windows 2000 Professional N/A N/A 2 2 2 N/A 2 Microsoft Windows NT® 4.0 Server, Enterprise Edition 8 8 2 8 2 N/A 8 Windows NT 4.0 Server 4 4 2 4 2 N/A 4 Windows NT 4.0 Workstation N/A N/A 2 2 2 N/A 2 Microsoft Windows 98 N/A N/A 1 Use Desktop Engine 1 N/A N/A Microsoft Windows CE N/A N/A N/A N/A N/A 1 N/A Maximum Amount of Physical Memory Supported by the Editions of SQL Server 2000This table shows the maximum amount of physical memory, or RAM, that the database engine in each SQL Server 2000 edition can support.Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine (MSDE 2000) SQL Server CE Enterprise Evaluation Edition Windows 2000 DataCenter 64 GB 2 GB 2 GB 64 GB 2 GB N/A 64 GB Windows 2000 Advanced Server 8 GB 2 GB 2 GB 8 GB 2 GB N/A 8 GB Windows 2000 Server 4 GB 2 GB 2 GB 4 GB 2 GB N/A 4 GB Windows 2000 Professional N/A N/A 2 GB 2 GB 2 GB N/A 2 GB Windows NT 4.0 Server, Enterprise Edition 3 GB 2 GB 2 GB 3 GB 2 GB N/A 3 GB Windows NT 4.0 Server 2 GB 2 GB 2 GB 2 GB 2 GB N/A 2 GB Windows NT 4.0 Workstation N/A N/A 2 GB 2 GB 2 GB N/A 2 GB ©1988-2004 Microsoft Corporation. All Rights Reserved.Specifications and Limits New Information - SQL Server 2000 SP3.This topic contains information about supported relational database management systems, object specifications, and source column data types in OLE DB. Supported Relational Database ProductsMicrosoft® SQL Server™ 2000 Analysis Services supports the following relational database products as data sources: SQL Server versions 6.5 and laterMicrosoft Access 97 and laterOracle versions 7.3 and 8.0 Note Text columns in Oracle tables should be variable length column type when using the OLE DB Provider for Oracle. Use the OLE DB for Provider for ODBC if Oracle tables contain text columns of fixed length column type.SpecificationsThe following specifications apply to Analysis Services.Item Specification Dimensions in a database 65,535 maximum, regardless of the number of cubes or whether dimensions are shared or private Levels in a database 65,535 maximum Cubes in a virtual cube 255 maximum Measures in a cube 1,024 maximum Measures in a virtual cube 2,048 maximum Dimensions in a cube 128 maximum Levels in a cube 256 maximum Levels in a dimension 64 maximum Members in a dimension 2^31-1 = 2,147,483,647 maximum Members (or member groups) per parent 64,000 maximum For more information about using member groups, see Member Groups. Calculated members (server defined) in a cube 65,535 maximum Calculated members in a parent measure in session context 31,743 maximum Calculated members in a parent measure in query context 31,743 maximum Calculated members in a parent dimension member in session context 759 maximum Calculated members in a parent dimension member in query context 759 maximum Aggregations per partition 65,535 maximum Cells returned by a query 2^31-1 = 2,147,483,647 cells maximum Although cubes can be larger than this limit, a query that requests more than 2^31-1 cells from a cube will fail. Record size for source database table 64 kilobytes (KB) maximum Length of object name (except dimension name) 50 characters maximum when using Analysis Manager 24 characters maximum when using PivotTable® Service Length of dimension name 24 characters maximum Length of aggregation prefix 50 characters maximum Maximum number of distinct states in a data mining model attribute column 255, after which the column becomes MODEL_EXISTENCE_ONLY For more information, see Data Mining Columns Note The term character in this documentation refers to a UNICODE character.Source Column Data TypesThe data type of the source column for a measure must be numeric except when the Count aggregate function is used. The data type of the source column for a dimension level must be string or numeric (except currency).OLE DB Data TypesColumns of the following OLE DB data types may be used as measures or dimension levels. Columns types that are marked with an asterisk are supported data types for data mining model columns. DBTYPE_BOOL*DBTYPE_I1DBTYPE_I2DBTYPE_I4*DBTYPE_UI1DBTYPE_UI2DBTYPE_UI4DBTYPE_I8DBTYPE_UI8DBTYPE_R4DBTYPE_R8* (Note: Data mining model column inputs of this type will be cast to a 4 byte floating point number)DBTYPE_DECIMALDBTYPE_NUMERICDBTYPE_VARNUMERICDBTYPE_CYDBTYPE_DATE*DBTYPE_DBDATEDBTYPE_DBTIMEDBTYPE_DBTIMESTAMP Columns of the following OLE DB data types may be used as measures only if the Count aggregate function is used. These data types may be used as dimension levels. DBTYPE_BYTESDBTYPE_HCHAPTER*DBTYPE_STRDBTYPE_WSTR* External LimitationsLimitations imposed by other technologies, such as the RDBMS being used, may limit some features of Analysis Services. For example, when merging two partitions containing a large number (> 100) of aggregations, you may receive an error message indicating that the maximum number of ODBC Access 97 File Sharing lock counts has been exceeded. This number is controlled by the Access 97 MaxLocksPerFile registry entry, not by any configuration parameter in Analysis Services.Other such external limitations may apply as well.©1988-2004 Microsoft Corporation. All Rights Reserved.[/code]Books Online is so kewl. The maximum allowed rows will vary of course by how many bytes you average per row. I suppose theoretically, if you had a table with 1 byte, you could hold 32,000,000,000,000 * 32,767 by taking the 1 byte table and splitting it and partitioning it over all available files. Wouldn't that be a fun experiment. Now, of course, you would have to take into account your system tables, statistics, etc. which would definitely shrink the number. I'm sure you get the idea though.It's QUITE large....theoretically of course.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
Maximum No of Rows