Overview
The following is a general overview of tables within a SQL Server database; it is by no means a thorough discussion of this subject. This is intended to be a primer for future discussions and covers only the basics, in no particular order.
Table is a database object containing a column definition (or a collection of) in which data is stored row-by-row. You can think of this very similar to a spreadsheet; with a little extra power.
A table is generally designed to handle most relational data in an organized manner; however, SQL Server has evolved to allow handling of data that is not organized.
A table must include the following (at a minimum):
- A name for the table
- A column to store the data
A table may include many other attributes/properties, such as:
- A description of the table
- A schema the table belongs to
- An identity column
- A filegroup the table belongs to
- Check Constraints, to ensure data integrity
- Multiple columns
- Relationships to other tables and/or objects within the database
- Indexes
CREATE TABLE syntax
The following is the bare minimum T-SQL syntax for creating a table:
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition>
| <column_set_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ FILESTREAM_ON { partition_scheme_name | filegroup
| "default" } ]
[ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]
See the "References" section at the end of this blog for the complete syntax. If you don't fully understand the text/symbols used in this syntax please reference my February 2008 posting Understanding MSDN Syntax Conventions.
Here is a CREATE TABLE command, at it's simplest, issued in T-SQL:
CREATE TABLE myTable
(myColumn TINYINT);
This is a very simple T-SQL command that will create a table called "myTable". The table will contain 1 column called "myColumn", this column's data type is "INT" (which is short form for Integer); thus only allowing whole numbers to be entered. The numbers are limited to a range of numbers between 0 and 255.
Columns
A column definition will include the following (at a minimum):
- The name of the column
- A data type, and any attributes required to define the data type
- If it allows NULL values
- If it is Identity Specific
A column definition may include other attributes/properties, such as:
- A default value (or binding)
- A description
- A computed column specification
Example
As mentioned earlier a table will include, at minimum, a column definition. In most cases you will find multiple column definitions within a table. An example customer table for a small business might include a column indicating the CustomerID, CustomerName, BusinessName, Address, PhoneNumber. The table might be called "Customers". To create this example table the T-SQL command might look like this:
CREATE TABLE Customers
(
CustomerID INT PRIMARY KEY NOT NULL,
CustomerName VARCHAR(100) NOT NULL,
BusinessName VARCHAR(100) NULL,
[Address] VARCHAR(500) NULL,
PhoneNumber VARCHAR(10) NULL
)
Notice that the column named "Address" is enclosed in square brackets "[]", this is because "Address" is a reserved keyword in Transact-SQL and can only be used as an identifier object if it is called using one of the "Delimited Identifiers". There are other acceptable delimited identifiers that may be used, you may have to set the SQL Server configuration for quoted_identifiers, or follow other methods to use these. Refer to Books Online (BOL) Delimited Identifiers (see resources at end of posting) for further information.
Also, you may have noticed that this table construction isn't the best there is. Logically the "Address" column currently would contain a complete address in the field (such as: 123 Anywhere St, Anyplace, State, 12345). If we wanted to search this field for all customers that are in the state of California or Hawaii then we would have to search using a LIKE operator (this will be discussed further later); this can take quite some time in SQL depending on how large the table is. Also, if we want to further list our customers by city alphabetically and group them by state then we introduce a complex T-SQL query to obtain this seemingly simple information.
To make this better we would perform what is known as "Normalization" on our tables. Normalization will be discussed in further detail in a future blog. Right now, the grasp of what a table is, what is does, and how the data is stored is the important information to know.
Conclusion
As you can see a table is very much a corner stone to any database, without a table there is no where to store your data in your database. A table can be very simple to setup; while also can be very complex to maintain a great amount of integrity and Business Intelligence rules. This is just a primer discussion, if you want to delve deeper into tables you will want to start your exploration with BOL's Understanding Tables section and follow the appropriate links to further your knowledge. You will find that tables can be very powerful if you setup the structure properly and utilize the appropriate techniques to ensure data integrity. I've already discussed some attributes of tables in earlier postings, such as my postings about Indexes. There is a wealth of knowledge out there on this subject and may seem like you already know most of it, but you will find that there are many aspects of tables that you may not have known about. I encourage you to take a few moments and review the BOL sections mentioned here, at a minimum. You may also want to look into Temporary Tables and for you SQL 2005+ users look into the new data type Table.
References
CREATE TABLE (Transact-SQL): http://technet.microsoft.com/en-us/library/ms174979.aspx
Understanding MSDN Syntax Conventions: http://sqln.blogspot.com/2008/02/understanding-msdn-syntax-conventions.html
TechNet - Tables: http://technet.microsoft.com/en-us/library/ms189104.aspx
Understanding Tables: http://technet.microsoft.com/en-us/library/ms189905.aspx
Creating and Modifying Table Basics: http://msdn.microsoft.com/en-us/library/ms177399.aspx
table (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms175010.aspx
Syntax