Relational Concepts  «Prev  Next»

Lesson 2 Introduction to SQL Server tables
Objective Define a SQL Server table.

SQL Server Tables

Tables are the fundamental building blocks of a SQL Server database. You can think of a table as a spreadsheet containing rows and columns. Columns are sometimes referred to as fields; rows are sometimes referred to as records.
A column defines a specific attribute; a row supplies the data for that attribute.
For example, a table devoted to employees would have columns defining the specific attributes of an employee, such as employee number, name, and address. However, this does not necessarily mean that the company has employees. The company might employ only contractors, whose data is stored in another table. A table, then, will always have at least one column, but will not always have rows. The diagram below illustrates the relationship between rows and columns in a table.

Relationship of rows and columns in a table

Table elements

When creating a table, there are a series of elements that can be customized, depending on the attributes of the table you are creating. These elements include:
  1. Column definitions, up to 1024 columns
  2. Indexes, both clustered and non-clustered
  3. The file group where the table is stored
  4. Attributes such as primary key, foreign key, and check constraints
  5. Default values
You will lean how and why to define these elements as you proceed through this course. In the next lesson, you will learn about data integrity.
To create, modify, or delete a table, you must have the appropriate permissions:

Action Required Permission
Create System administrator (SA), or those who have create table permissions in a specific database
Modify Table owner, DBO, SA, or members of the sysadmin, db-owner, and db-ddladmin roles
Delete Table owner, DBO, SA, or members of the sysadmin, db-owner, and db-ddladmin roles

Schema versus Model

Think of the word schema as meaning a complete description of a database in a formal language supported by the database management system. A schema will include a formal description of all tables included in the database, their relationships and constraints, and other information needed for creating or reconstructing the database. A schema should be a complete description of the structure of the database, but will say nothing about the actual data contained in it. I introduce the term here because, in the context of SQL Server Modeling, it can often be used more or less synonymously with the word model. Schema is more appropriately used in the database context, whereas model is used in the modeling context. But the two terms are closely linked in the context of the SQL Server Modeling framework.
In the next lesson, we will dive into the elements of a table.