Column Types

See page 134 of 'Beginning SQL Server Express 2008)

Text

varchar(50)

For short text fields

varchar(MAX)

(Will eventually supercede text type)

vNarchar(50)

Unicode so double storage space but not limited characterset

nVarchar(MAX)

Unicode so double storage space but not limited characterset

text

Don't Use – Use varchar instead as this will be superceeded by it

Numeric

binary

Used for flags etc (see bit in 'data' for a general boolean data type)

tinyint

8bit unsigned 0 – 255

smallint

16bit (-32,768 – 32,767)

int

32bit (-2,147,483,648 – 2,147,483,647)

bigint

64bit

float

real

DateTime

smalldatetime

Don't use as only runs to 2079

datetime

Standard date and time

datetime2

Same as datetime but allows fractions of a second to be stored.with greate precision

date

To store only the date

time

Stores time based on 24 hour clock

Data

bit

True or false values

binary

Used for flags etc

varbinary

For holding data such as images.  For file sizes > approx 1-2MB it's more efficient to use an external filestream for the actual data storage (which also removes them from the database backup).

You either specify varbinary(n) and give a specific number up to 8000bytes (more is not possible), or you define the column to be varbinary(max) in which case it can hold up to 2 GB of data.

image

Due to besuperceeded by binary – don't use

Add new column

Beginning SQL server 2008 express see page 155

Example of how to add a column

if not exists (select * from syscolumns
  where id=object_id('
') and name='
')
    alter table
 add

if not exists (select * from syscolumns
  where id=object_id('Employees') and name='MaidenName')
    alter table Employees add MaidenName varchar(64) NULL
go

Add leaving new column set to null for all rows

 SqlClient::SqlConnection ^SqlConnection1 = gcnew SqlClient::SqlConnection();
 SqlConnection1->ConnectionString = DataSourceString + DatabaseConnectionString;
 try
 {
  SqlConnection1->Open();
  //Add 'IsRecording' column
  SqlClient::SqlCommand ^SqlCommand1 = gcnew SqlClient::SqlCommand();
  SqlCommand1->Connection = SqlConnection1;
  SqlCommand1->CommandType = CommandType::Text;
  SqlCommand1->CommandText = "if not exists (SELECT * from syscolumns WHERE id=object_id('tblLocations') and name='IsRecording') ALTER TABLE tblLocations ADD IsRecording bit NULL";
  affected = SqlCommand1->ExecuteNonQuery(); //affected = number of rows affected