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?

Continue reading “Design tables”

Identify the appropriate usage of new data types

May include but is not limited to: geography, geometry, hierarchyid, date, time, datetime2, datetimeoffset, varbinary(max), FILESTREAM

SQL Server 2008 comes with a bunch of new types addressing long-standing issues (such as the need for a date type) and some new functionality in the shape of the spatial (geometry and geography) types. I must admit that I’ll focus mostly for the date and time types, and varbinary(max), because I use these daily, and because I don’t understand the mathematical concept behind hierarchyid, and was never the fan of spatial data.

So, what’s new in dates and times? The following table will explain:

Date A type dedicated to store date information- from 0000-00-00 till 9999-12-31. A wide range! A fixed value.
Time A dedicated type for time values, can store up to 7 fractional second digits (the precision is 100 nanoseconds). You supply the number of digits as a parameter.
Datetime2 The former two together – the range of date with the precision of time!
Datetimeoffset Datetime2 plus time zone information – from -14:00 to +14:00.

  Continue reading “Identify the appropriate usage of new data types”

Design data integrity

May include but is not limited to: primary key, foreign key, check constraint, default constraint, NULL/NOT NULL, unique constraint, DML triggers

SQL Server is a relational database engine – and this means that you can – and probably should – care for the relations of your data. This is called normalization, and there are quite a few ways to implement it – mostly by using constraints on how data should be structured.

The easiest type of constraints is unique. It does nothing more than its name implies – ensures that there all values in a given column are unique. You can even insert a NULL value into a column constrained by UNIQUE – if you allow NULLs in that column – but because each values must be unique, you can insert only one NULL.

Primary keys are like unique constraints, in a way that they enforce unique values. But primary keys are less tolerant than unique – there can be only one primary key in a table (but that can be a combination of two or more columns, too), and a primary key column cannot contain NULL values. If you define a primary key constraint on more than one column, you need to make sure that any possible combinations of those columns are unique. Thus it allows repetitive values in a given column, as long as values in other columns won’t make the same combination.

Continue reading “Design data integrity”