Table Column Tutorial

Standard Add / Edit |  Quick Add / Edit   Cheat Sheet  |  Delete |  Primary Key |  See Also

Columns are the building blocks that comprise a Table.   Here you will find described everything you need to manage your Table's Columns.

The first step to Managing a Table's Columns is to Add or Edit a Table.   See the Table Tutorials to learn more.

Once you have the Table properties screen open there are a number of methods that can be used to manage the Table's Columns:

• Quick Add / Edit a Column

  Using the Quick Add / Edit method is a very fast and efficient way to control your Columns.   Using this method you can easily specify the Name, Datatype, Size and Identity values for a Column directly in the Column's list.   This is the recommended method for most Column operations.

  Cheat Sheet

  1. If Adding a new Column:

     • Press "Ctrl+A" to add a Column to the bottom of the list.
     • Press "Ctrl+Shift+A" to add a Column to the top of the list.
     • Press "Ctrl+I" to add a Column under the currently selected Column.
     • Press "Insert" to add a Column under the currently selected Column.
     • Press "Ctrl+Shift+I" to add a Column above the currently selected Column.
     • Press "Shift+Insert" to add a Column above the currently selected Column.
     • Press the button to insert a Column under the currently selected Column.
     • Press Shift + to insert a Column above the currently selected Column.
     • 

     If Editing an existing Column:

     • Press the Enter key with the Column you wish to edit selected.
     • With the Column you wish to edit selected Single click the Column.
     • 
  2. Using a prefix from your Naming Convention  type the name of your new Column.   ie. 'strColumnName'

     Acceptable formats for Quick Add / Edits are:

     Standard:
     <prefix><name>	Column defaults are specified with the prefix and supplied by the Naming Convention. For example: 'strColumnName' will give you a VarChar Column named 'strColumnName' with a Length of 50. This could be 'intValue', 'strValue', 'dtmValue', or any other prefix from the Naming Convention
     To specify a Column's length:
     <prefix><name>(length)	length is a posative Integer (1-99999) specifying the number of character the Column can hold. For example: 'strColumnName(25)' will give you a VarChar Column named 'strColumnName' that can hold a maximum of 25 characters. * If you leave off '(length)', the default length for the specified Naming Convention prefix will be used.
     To specify Precision / Scale:
     <prefix><name>(scale,precision)	precision is a posative Integer (1-99) that is the total number of significant digits to the left and right of the decimal. scale is a posative integer (0-99) that is the total number of sifnificant digits to the right of the decimal. For example: 'sngValue(8, 3)' will give you a Float Column named 'sngValue' that can have eight digits and up to 3 of them can be to the right of the decimal. * If you leave off '(scale,precision)', the default precision & scale for the specified Naming Convention prefix will be used. ** A value of zero for scale lets SQL Server decide how many digits to place to the right of the decimal.
     To specify an identity Column:
     <prefix><name>[seed,increment]	seed is a posative Integer (1-9999) specifying the Identity Column auto-numbering start point. increment is a posative Integer (1-9999) specifying the Identity Column auto-number increase. For example: 'intTableID[100,2]' will give you an Identity Column named 'intTableID' that starts auto-numbering at 100 and will increase by 2 with every insert. * An Identity Column must be derived from the BigInt, Decimal, Integer, Numeric, SmallInt, or TinyInt data types.
     To specify a Datatype using a prefix and then remove the prefix:
     <prefix>.<name>	For example: 'int.ID' will give you an Integer Column named 'ID'.

  3. Press Enter key to submit your changes.
     - OR -
     Press Escape key to discard the changes.
     
• Standard Add / Edit a Column

  1. If Adding a new Column:

     • Click the Add button to insert a Column at the end of the list.
     • Press Shift + Click the Add button to insert a Column at the top of the list.
     • 

     If Editing an existing Column:

     • Click the Edit button with the Column you want to edit selected.
       
     • Press "Shift+E" with the Column you want to edit selected.
     • Double Click the Column you want to edit.
       
  2. Choose a Datatype for the Column from the drop down list.
     ** The Datatypes found in this list are taken directly from the Schema's Naming Convention  and User Defined Datatypes.  The Datatype's Physical Type name is on the left of the "-" and it's prefix is on the right.   Notice how the Datatype's default values are automatically filled in when you select the Datatype.   These values could include the Size, Identity or Nullability.  
     
  3. Using the prefix of your selected Datatype, type the name of your Column.
     
  4. If neccessary, set the size for your Column.
     
  5. Toggle the use of the Identity property and set the Seed and Increment if needed.
     ** The Identity option will be disabled if one of your other Column properties are not compatible with the Identity property.
     
  6. Toggle the Nullable property as needed.
     ** The Nullable option will be disabled if one of your other Column properties are not compatible with the Nullable property.
     
  7. Set a Default value for the Column if needed.
     ** The Default option will be disabled if one of your other Column properties are not compatible with the Default property.
     
  8. Create a computed value for the Column if required.   If you do use a Computed Value for the Column, use the  button to validate your syntax.  (Not Required)
     ** The Computed option will be disabled if one of your other Column properties are not compatible with the Computed property.
     
  9. Click the OK button to submit your settings.
     - OR -
     Click Cancel to discard your setting.
• Delete a Column
  Click the Delete button.

  

  
  - OR -
  Press "Ctrl+D" with the Column you want deleted selected.

  ** Deleting a Column from a Table may have potential effects on other objects in the Schema.  If another object depends upon the Column you are deleting, you will be asked what to do before continuing.
• Primary Key

  To Add/Remove a Primary Key Column:

  • Select that Column and press the Spacebar.
  • Click the the Checkbox to the left.
  • 

  ** Adding/Removing a Primary Key Column may have potential effects on the Schema.   If any other Schema object depends upon the Table's Primary Key, you will be asked what to do before continuing.