About tables (SQL Server tutorial)
The first objective described on Microsoft’s 70-461 Querying Microsoft SQL Server 2012 exam is creating database objects. Note that the SQL 2014 exams aren’t out as of yet, but the format will likely stay pretty close. So we’ll get started with the basic building blocks of a database.
- Create and alter tables using T-SQL syntax (simple statements)
- Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE
In the next few posts, we’ll cover a lot of details about SQL server tables: how to create, modify and delete tables.
But first, it makes sense to discuss exactly what a table is and what a table isn’t.
Databases have been around almost as long as computers have, and they form one of the most essential tools used by businesses. They’re deeply influenced by two fields of mathematics: set theory and predicate logic. Together, these theories make up the core definition of how databases operate.
Databases work with sets of data. While we’ll discuss in a later chapter operations which are used against single rows (or records) of a database, the database is designed to work most efficiently with sets. What that means is, when you work with the data in a table, you select a set of records to operate on.
In its simplest form, a set is a collection of objects with specific properties. If you were running a business, you would have a set of items in inventory, a set of customers, and a set of vendors. You might also have other sets, such as employees, tools or computers.
A database serves a business function. Let’s say, for example, that your business made a record profit this year. You want to pay bonuses to your employees, but you want to pay larger bonuses to the employees in the marketing department. A database allows you to get the names of all of those employees simply and easily by issuing a command like this:
SELECT * From dbo.Employees Where Department = ‘Marketing’
So, from the set of Employees included in the Employees table, we are selecting the set of employees who work in the marketing department.
The primary object we talk about when looking at a database is the table. A table is made up of columns, which define the type of data, and rows, which are individual records. Figure 1 is an example of what a table might look like. As you can see, it contains 5 records. The first column, CUSTID, contains a number that increases with each record, and the CreateDate column shows the date the record was initially created.
If you’ve ever done any form of data entry (or filled out a form on the web) then you know that a field is a place to store a discrete piece of data such as a name, birthdate or social security number. Inside a database row, a field is represented as a column.
All information stored in SQL Server is stored in tables—including system information. In SQL Server 2012, tables in a database can be organized into larger partitions called schemas, which provide logical grouping for large numbers of tables. If you don’t specify the schema when creating a table, the database server will default to the default context for the user. By dividing your tables into different schemas, it becomes easier to manage permissions and security for groups of tables (for example, you can create separate schemas related to HR, Senior Management and Sales).
Later in this book, we’ll also review other types of tables and related objects, including views and indexed views, temporary tables, derived tables and tables expressions.
In the next sections, we’ll look at how to create, modify and delete tables.