Create and alter tables
This objective may include but is not limited to: computed and persisted columns, schemas, scripts to deploy changes to multiple environments, for example, dev, test, production; manage permissions (GRANT, DENY, REVOKE)
Tables are the basis of SQL Server data. A table is built from columns and rows, each value is found in the intersection of the two. SQL Server offers the fully blown DDL, Data Definition Language dedicated to build tables.
Creating a table is pretty straightforward:
CREATE TABLE database.schema.name
( columnName colunmType constraints NULL/NOT NULL, secondColumnName secondColumnType secondConstraints…);
Now a column name must conform to some rules, e.g. it must be unique, it cannot contain reserved keywords of SQL Server, and some special characters, but anybody with some kind of programming background knows these rules immediately.
There’s the possibility to create computed columns, which have some restrictions, but I think these are pretty easy to understand, for example, you cannot insert or update values into/of them. To create a computed column, use the following syntax:
ComputedColumn AS (column1 * column2),
Note that you don’t have to specify a type for these columns. Computed columns are cool, but they are virtual. This means that every time you reference on them, they get calculated, every one of them. You can imagine that this could be a drawback real soon. To override this default behavior, just use the PERSISTED keyword in the CREATE/ALTER table clause to persist your computed columns in the database.
Suppose you have a table, but want to change it in some way, for example, add a column. In this case, you should use the ALTER TABLE clause, which works exactly the same as CREATE TABLE.
Some words on schemas. Schemas provide a way to group your related tables, stored procedures, and various database objects. Also, they lets you control access on the items contained in your schema in one place, so get used to work with them. To create a schema, you use the intuitively named CREATE SCHEMA clause, along with a schema name.