A SQL Azure tip a day (4) – Create a table

When you design your database tables there are some important things to think of. Some of them are important in a normal database also, but they are much more important in SQL Azure, like:

– Normalize to remove redundant data
– Use the smallest datatype possible
– Create as few indexes as possible, but not too few
– Avoid redundant indexes
– Every table must have clustered index

This is important in a normal database but it is even more important in SQL Azure because you have limited space for every database, your connections may be terminated due to heavy use of resources, you pay for what you use and the data that you transfer. You can use the SQL Azure management portal do design your tables or write the T-SQL statement yourself. The syntax to create a table in SQL azure is the same as in SQL server 2005/2008, but not all options are supported in SQL Azure. 

Below you’ll find a sample of how to create a table named Table1 CREATE TABLE [dbo].[Table1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Column1] [nvarchar](50) NOT NULL, [Column2] [nvarchar](15) NOT NULL, [Column3] [tinyint] NULL, CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH ( — PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, — FILLFACTOR=80, — ALLOW_ROW_LOCKS = ON, — ALLOW_PAGE_LOCKS = ON, — DATA_COMPRESSION=PAGE ) )–ON PRIMARY GO Wait a minute! Some important options of the clustered index can’t be controlled, like FILLFACTOR, ALLOW_xxx_LOCKS and DATA_COMPRESSION. What does that mean? Well, if you execute the following code, you can see that FILLFACTOR=0, ALLOW_xxx_LOCKS are 1 SELECT name, fill_factor, allow_row_locks, allow_page_locks FROM sys.indexes

That means that every page of the index will be filled to 100%, leaving no room for new records. Well, thats fine for indexes that are always increasing, but if not, you’ll get extra IO operations caused by page splits and you’ll get fragmented indexes, causing performance issues. To remove the fragmentation you need to rebuild your indexes regulary to increase the performance, but that is a subject for later blog posts. In the SQL azure platform, you shouldn’t need to care about the infrastructure, like files or file groups and it isn’t possible to place tables or indexes in different file groups.

If you want to know more about SQL Azure, feel free to contact any of our SQL server consultants and we will help you.