BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Creating SQL Server Tables Part 1

The fifth part of the SQL Server Programming Fundamentals tutorial looks at the creation of tables within a SQL Server database. Tables provide structured storage locations for all of the information stored within a relational database.

Setting Table Properties

Table Properties

There are several properties that can be set for a new table. The key properties that are of interest to this article are as follows:

  • Name. The name of the table. A new name for the table can be entered here to replace the default name. If the name is not changed, a new name will be requested when the table is saved.
  • Description. A simple, human-readable description for the table.
  • Schema. Tables may be grouped into schemas using this option. Schemas are beyond the scope of this article.
  • Regular Data Space Specification. This setting can be expanded and, using the options within, you can set the filegroup for storage of the table's row data. This is the storage location for normal column data, excluding large objects.
  • Text/Image Filegroup. If desired, large object data can be held in a different filegroup to normal row data. The target filegroup is specified using this property.

The only property that requires modification for this first table is the name. Change the name property of the table to "Customers".

Adding Columns

The Customers table will be used to hold various elements of data for each customer row. These are as follows:

  • Customer Number. Each customer will have a unique reference number. This will be used to identify the customer and will appear on correspondence. A simple, non-nullable integer will suffice for this column. It will be named "CustomerNumber" to match the Pascal case standard. Note that the space has been removed from between the words in the column name. This is not strictly necessary but makes the column easier to work with.
  • Contact Name. All customers will require a name. For business customers this will be the primary contact for the business. For homeowners this will be the name of the person who purchased a service or contract from DisasterFix. The name will be held in two columns named "FirstName" and "LastName". As these will be quite variable in size we will use a varchar column for each. The maximum size for each will be 25 characters.
  • Business Name. For business customers, the name of the company will be held. This will be stored as a variable length character string with a maximum size of one hundred bytes. As homeowners will not have a business name, the "BusinessName" column will be nullable.
  • Creation Date. The final column will hold the date of creation of the customer. This will be used for auditing purposes with only the date being required. We will use a non-nullable, smalldatetime column named "CreationDate".

To create the table's columns, you use the grid in the main area of SSMS. The first column in the grid determines the name of the new column. The second column in the grid permits the selection of the data type for the new table column. Where the data type requires a size to be specified, this is appended to the data type name within parentheses (). The last column shows a checkbox for each table column. If checked, the new column will allow null values to be stored. If not checked, a value will always be required.

Add the five column definitions to the table designer grid. The final result should look similar to the diagram below. If you make an error whilst adding the columns, you can modify values after clicking on them. You can also insert and delete items by right-clicking the small square at the left of the grid row and choosing the appropriate option from the context-sensitive menu.

Customers Table

Once your design matches that shown, the table must be saved. To do so, select "Save Customers" from the File menu, click the Save button on the toolbar or press Ctrl-S. The table will be committed to the database and will appear beneath the Tables branch of the Object Explorer. NB: If the table does not appear, right-click the Tables branch and select "Refresh".

The table designer may now be closed by clicking the X at the top-right of the tabbed window. If you need to edit the table again at a later date, the designer can be reopened by right-clicking the table name in the Object Explorer and selecting the "Design" option.

Congratulations, you have now created the first JoBS database table!

Limitations

There are some limitations that you should be aware of when creating database tables. The first of these is that there is a limit of two billion tables permitted per database. However, a database containing such a large number of tables would be unwieldy to say the least!

A single table may contain up to 1,024 columns. The total size of these columns must not exceed 8,060 bytes, excluding large object data. This is a limitation on the size of the actual information held in a row. It is, therefore, possible to create a table containing variable length columns with a maximum size greater than 8,060 bytes. However, if you try to insert a row with combined data exceeding this limit you will receive an error.

5 July 2008