Tag Archive | Tables

Design tables

May include but is not limited to: table width, sizing data types, IN_ROW_DATA (BLOBs), overflow data, sparse columns, computed columns, persisted computed columns

In the heart of every relational database system, we find tables. Lots of them. Everybody knows how they look – rows and columns, and in the intersection the data value itself and a nice header with the column titles.

Our first issue is table width. You can calculate it easily by the built-in dynamic management view sys.dm_db_index_physical_stats. The hard decision is how wide your table should be. A wide table is not necessarily the devil itself, and a very short table can be a bad solution, too. Of course it’s not the best idea to store ordered products in the customer table rendered as columns like Order1, Order2… I think the keyword here is clever design. Normalize your database system, ensure referential integrity, and you won’t have to worry about table width. You’d need to store that data anyway, don’t you?

Read More…

Advertisements

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.