Quick Newbie SQL Tips

I go back and forth with different versions of SQL which at times will require different syntax. I started using primarily SQL Server 2008 R2 so I needed an update on some certain functions. To help me remember and to save you some time, here they are.

Adding Multiple Colums

There has been a few times where I needed to add multiple columns to a table to update a customer. Here is the old way

ALTER TABLE tableName
ADD( column1Name type,
        column2Name type,
        column3Name type)
GO

Now you dont have to worry about the parenthesis when adding multiple columns to the table :

ALTER TABLE tableName
ADD column1Name type,
      column2Name type,
      column3Name type
GO

Adding Foreign Keys Manually

Sometimes you dont want to sit there and fill out a database diagram, or you cant. Well here is a manual solution to add a foreign key constraint on a table manually.

alter table tableName
add constraint tableName_ColumnFK_FK FOREIGN KEY (ColumnFK) REFERENCES otherTable(columnPK)

Where tablename is the name of the table you are adding the foreign key to, ColumnFK is the name of the foreign key column which resides in the table you are adding the foreign key to, otherTable is the name of the table which has the primary key that you are referencing with your foreign key, and columnPK is the name of the column which is the primary key on the table you are referencing. tableName_ColumnFK_FK is the name of the constraint, but I would strongly recommend keeping the naming convention to keep track of all the foreign keys.

Using The Bit Datatype

Using the bit datatype will decrease the space needed for this simple data. When all you need is a simple yes or no, then why not just store it as a bit, rather then at the worst case 32 bits (signed int) ?

Setting The Length

Setting the length will overtime greatly reduce the size of your database. This in conjunction with varchar will help you ten fold. If you create the following :

column1 text

or

column1 char(MAX)

then you are creating a space hog. Make the size either vary with what is in the field, or something close to what is needed. For example, if you need a phone number, set the field to varchar(14) instead of the max.

Now the other question when dealing with varchar(), =Should I use varchar() or nvarchar()?=. The easy way to explain this: Varchar() is used for the variable length for english characters, etc… while nvarchar is used to store more characters, such as the Chinese fonts. Space is not typically an issue, but it does call for double the amount of reads which in turn slow down performance.