Microsoft Visual Studio 2015
Defining SQL Server Database Table Columns
The table will have a primary key called Id by default. That is in my view an accetable name, or it could be change to TableNameId, for example CustomerId (note the singular customer as the Id will be the Id of a single customer). By default Id fields are not Auto-number To add auto-number, change the IdentitySpecification property in the Properties window from false to True.
Fields or Columns can be added in a similar way as in access, each field should have a name and a datatype. There are however many more data types in SQL Server than in access, the following are the most commonly used.
- int - for whole numbers up -2 Billion..+2 Billion
- smallint - for whole numbers -32,767..+32,766
- tinyint - 0..255
- bigint - for astronomical numbers roughly 20 digits of accuracy
- float - for numbers with a decimal point
- money - for currency up to 922 trillion
- varchar(50) - variable length text up to a max of 50 chars
- varchar(max) - variable length text with no limit
- text - DO NOT USE it has been depreciated!!!
- date - dates
- time - times to an accuracy of one millionth of a second
- bit - for logical or boolean values, 0 is false, 1 is true
- image - for storing images up to 2 Gbytes
Allowing NULLS means that when a user is entering a new record, they can leave it blank or NULL (empty). NULL is different from a text field containing a space char. Allow NULLs on fields which are not essential, or could be filled in later. In this example if the Wine has no name, the record would be of no value.
Where there is a field with only a few values, and it is likely that more than half the records will have one particular value, setting a default value for that field can speed up data entry.
