Creating Tables, Rows, and Columns
Relational databases are defined in fairly simple terms1:
- Database: An extensible collection of related data typically organized as a set of tables. For example, an accounting database would contain information about the customers, inventory, orders, items, and other details of the accounting operation. Where and how the data is stored, protected, fetched, and updated is irrelevant, as it is managed entirely by the database management system (DBMS). The key word here is "extensible." Due to its fundamental design, a relational database is easily expanded to encompass more data entities to store.
- Tables: An extensible collection of rows containing related data. For example, the Customers table would contain a collection of rows pertaining to (just) customers—not the things they order, or sell, or where they bank—just about the individual customer.
- Rows: An extensible collection of column headings and typed columns to contain data details collected in a single table. Each row pertains to a single entity as a row in the Customers table would refer to a single customer. There is never an implied row order in a relational data table—this means, unless specifically requested, rows are returned in a nondeterministic order. This is especially true of SQL Server 2005 queries—with parallel processing, even rows stored with a clustered index can appear in any order.
- Columns: A named storage place for base-typed, user-defined typed, or (in the case of SQL Server) sql_variant "morphing" typed data. Columns are always returned in the order in which they are defined unless otherwise requested.
1 See C. J. Date, An Introduction to Database Systems (Volume 1, 4th Edition) (Addison-Wesley, 1986), p. 117.
IMHO - No, an ADO.NET DataTable or TableAdapter object is not synonymous with a database table.
How SQL Server Stores Relational Databases
SQL Server has expanded the number and type of objects managed and contained in the database to include collections of other objects such as logins, roles, users, stored procedures, views, triggers, functions, user-defined types, reports, and other objects; and in SQL Server 2005, assemblies, functions, aggregates, and CLR-based user-defined types (UDTs). In SQL Server, the definition of a column is expanded to include the ability to define columns whose datatype morphs to the datatype of the data stored on a row-by-row basis (sql_variant) or is defined by a CLR-based user-defined type.
SQL Server databases can contain billions of tables; tables have zero to virtually any number of rows, and rows contain 1 to 1,024 columns2 but are (generally) limited in size to 8K3 (not counting BLOB and variable-length columns)4. But, no, I don't expect your database to have more than a few dozen to a few hundred tables. If you have more than a thousand tables, you have a very complex database. I guess SQL Server supports a virtually unlimited number of tables so Microsoft could say that SQL Server supports as many tables as Oracle or one of its other competitors. It's like saying your car can contain a billion marbles—just how many marbles does one car need to carry?
2 In SQL Server 2005.
3 SQL Server 2005 supports row-overflow storage, which enables variable-length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable-length columns pushed out of row. Because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8KB" topic in SQL Server 2005 BOL.
4 See "Maximum Capacity Specifications for SQL Server 2005" in BOL.
IMHO - If you find yourself working with a table that contains several hundred columns, there's usually something wrong with the design. Consider that the maximum size for a row (the sum of all data consumed by its columns) is about 8,000 bytes. Sure, some column data is stored on separate pages (like BLOBs and varchar(max) columns) and don't contribute (very much) to the total. Just make sure that your database is properly normalized before coming back to us when your rows are too large.
Your data is ultimately stored in named and typed "columns." The term "column" is synonymous with a "field" in an Index Sequential (ISAM) database like JET or a flat-file database. Okay, let's go over those objects in a bit more detail.
