Continuing the process of learning SQL, last time I wrote about SQL primary keys. Today I learned to disallow “null” from primary keys to prevent having blank data and to set “Identify Specification” and “Not for Replication”.
Identify Specification
Setting the primary key’s Identify Specification to “no” means manually having to create the primary key for each row. To change it to yes, set the sub-menu “Is Identity” to “yes”. Doing this also sets the column to “read only”.
Not for Replication
One other step you may want to do for primary keys is to change the Not for Replication setting. “No” means ‘replicate’ (i.e. reuse old, deleted values). “Yes” means ‘never reuse old, deleted values’.
SQL Data Types
Each row has to have a data type to know how to store the data held in it:
- int – a number only
- varchar – a “string” of letters or numbers
- bit – one digit
- usually also set “allow nulls” to ‘off’ and set “Default Value or Binding” to a value.
- decimal – digits and number of digits stored to the right of the decimal
- date (SQL 2012 only) – date only
- datetime – date and time
- time – only time
- money – currency and 2 numbers to the right of a decimal
Standard Column Names
These are the column names and data types most used by my instructor, but you can do whatever you want:
- RecordId – int (primary key)
- CreatedOn – datetime
- CreatedBy – varchar
- ModifiedOn – datetime
- ModifiedBy – varchar
- IsActive – bit
- IsDeleted – bit