Introduction to the Columns of a Table
Introduction to the Columns of a Table
Columns Fundamentals
Introduction
As mentioned already, a table is a list of values. Because the values are presented in a vertical arangement, the vertical arangement is also called a column. A table can contain many columns. In reality, the columns provide a way to better organize the table. Here is an example:
| Name | Age | Gender | Relationship |
| Judie | 18 | Female | Sister |
| Ernest | 24 | Male | Cousin |
| Bill | 52 | Unknown | Uncle |
| David | 36 | Male | Brother |
| Hermine | 12 | Unknown | Niece |
Because a column is a characteristic of a table, it is also called an attribute.
Practical Learning: Introducing Tables
Visually Creating a Column
You can create a column when designing a table or using code. To visually create a column, while the table is displaying in Design View, specify the characteristics of the column using the section on the right side of the right-pointing row header:
Creating a Column Using Code
We saw that the primary formula to create a table was:
CREATE TABLE table-name
After specifying the name of the table, you must list the columns of the table. The list of columns starts with an opening parenthesis "(" and ends with a closing parenthesis ")". Each column must be separated from the next with a comma. You can include all columns on the same line if possible as follows:
CREATE TABLE [SchemaName.]table-name(Column1, Column2, Column3)
Alternatively, to make your statement easier to read, you should create each column on its own line as follows:
CREATE TABLE [SchemaName.]table-name( Column1, Column2, Column3);
You can also indent the columns definitions:
CREATE TABLE [SchemaName.]Country
(
Column1,
Column2,
Column3
);
There are two primary pieces of information you must specify for each column: its name and its type. Therefore, the syntax of creating a column is:
ColumnName data-type options
We also saw that you could use sample code to create a table. This allows you to have more control over the various columns you want the table to have. To do this, open an empty Query Editor and display the Templates Explorer. Expand the Table node. Under Table, drag Create Table, Add Column, or Drop Column, and drop it in the Query Editor. If you use dropped Add Column or Drop Column, you can delete the undesired sections of the code and isolate only the part that handles table creation. Here is an example:
--========================================================================== -- Add column template -- -- This template creates a table, then it adds a new column to the table. --========================================================================== USE <database, sysname, AdventureWorks> GO CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> ( column1 int, column2 char(10) ) GO
Like everything in a database, a column must be identified; that is, a column must have a name. If you are visually creating a table, to specify the name of a column, use a cell under Column Name.
The name of a column:
After respecting these rules, you can add your own rules. In our lessons, here are the rules we will use to name our columns:
You should avoid using the following reserved words of Transact-SQL when naming a column:
There are some other words you should avoid. If you really want to use one of those words, include the name in double-quotes, such as "Address", or between square brackets: [ and ]. An example would be [Address]. In reality, you can include any column name in double-quotes or in [].
Practical Learning: Starting a Column
Introduction to the Type of Value of a Column
The Data Type of a Column
The most important role of a column is to hold some values. For this reason, a column is also called a field. There are various types of values, including names, numbers, etc. Therefore, when creating a column, you must specify the type of values it will hold. If you are visually creating a column, to specify its type:
If you are programmatically creating the column, after specifying its name, enter its type: CREATE TABLE table-name
(
column-name type
);
GO
As we saw already in our introduction to variablesy, SQL and Transact-SQL provide various data types. |
 |
Characters
If you want a column to hold characters or symbols for its values, specify its data type as CHAR, NCHAR, VARCHAR, or NVARCHAR. Here is an example:
CREATE TABLE LibraryMembers
(
Gender char
);
GO
Practical Learning: Setting the Data Type of a Column
Natural Numbers Types
As we saw in out introduction to variables, Transact-SQL supports various types of natural numbers, including:
CREATE TABLE MaritalsStatus
(
StatusCode tinyint
);
GOCREATE SCHEMA Administration GO CREATE TABLE Administration.StudentsGradeScale ( LetterGrade char, MinRange decimal, MaxRange DECIMAL, MinPercent numeric, MaxPercent NUMERIC, Descriptor nchar ); GO
 |
The binary data type is used for a column that would hold natural numbers. The value of a binary type can be stored as a normal integer. Use the binary data type if all values of the column would have the exact same length (or quantity). If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary type also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals. |
Decimal Numbers
As we saw in our study of variables, Transact-SQL supports various types of decimal numbers. We saw that the available types are numeric or decimal (either decimal or numeric would produce the same effect in Microsoft SQL Server). If you simply provide the data type like that, it would be treated like a natural number. If you want the number to be treated as a fraction, specify a scale and a precision.
The numeric() and the decimal() types are used if the values of a column require a certain level of precision. If you don't care about precision, Transact-SQL provides the float and the real data types. Here are examples of using them:
-- This table holds information to evaluate
-- the commissions earned by employees of a furniture store
CREATE TABLE EmployeesCommissions
(
TransactionMinimum real,
TransactionMaximum REAL,
CommissionBase Real,
CommissionRate real
);
GO
As seen with the numeric and the decimal types, if you want a float column or a real column to hold fractional values, you should add parentheses to the type; then provide a scale and a precition in those parentheses.
Money
We saw that Transact-SQL supports the money data type. This type can be applied to a column that will hold currency values.
Transact-SQL also provides the smallmoney data type for columns that use currency values.
We saw that Transact-SQL supports Boolean values through a data type named BIT. You can apply it to a column. Here is an example:
CREATE TABLE TruckDriver
(
IsOrganDonor bit
);
GO
We saw that Transact-SQL provides the sql_variant data type as an alternative to any of the other data types.
Transact-SQL supports coordinates of a geometrical shape through a data type named geometry. To create a column for such a type, you can select or apply it:
Geographical Location-Based Columns
Transact-SQL supports geographical locations through a data type named GEOGRAPHY. To create a column that stores the geographic location of an item, select or apply this data type:
User-Defined Types
In Transact-SQL, you can create a user-defined data type (UDT) as a freindly name for the built-in data types.
To visually create a UDT, in the Object Explorer, expand a database, expand its Programmability node, and expand the Types item. Under Types, right-click User-Defined Data Types and click New User-Defined Data Type...
This would open the New User-Defined Data Type dialog box. The first piece of information you must provide is the schema that will own the new type. Normally, a default schema is provided and you can just accept it. Otherwise, if you had previously created a schema and you want to use it, click the button on the right side of the Schema text box, select it and click OK.
The two most important pieces of information you must provide are a name for the new type as alias and the Transact-SQL type on which it will be based. The name must follow the rules of names in Transact-SQL. In the Data Type combo box, select the data type of your choice. Of course, you must know what type you want to use. Here is an example:
After entering and selecting the desired information, click OK.
To create a UDT with code, the basic formula to use is:
CREATE TYPE AliasName FROM BaseType
To get assistance from template code, open a Query Editor. From the Templates Explorer, expand the User-Defined Data Type node. Drag Create User-Defined Data Type and drop it in the Query Editor. Skeleton code will be generated for you:
-- ================================ -- Create User-defined Data Type -- ================================ USE <database_name,sysname,AdventureWorks> GO -- Create the data type CREATE TYPE <schema_name,sysname,dbo>.<type_name,sysname,Phone> FROM <base_type,,nvarchar> (<precision,int,25>) <allow_null,,NULL> -- Create table using the data type CREATE TABLE <table_name,sysname,test_data_type> ( ID int NOT NULL, Phone <schema_name,sysname,dbo>.<type_name,sysname,Phone> NULL ) GO
You start with the CREATE TYPE expression, followed by the desired name for the new type. After the FROM keyword, type an existing Transact-SQL data type. Here is an example:
CREATE TYPE NaturalNumber FROM int; GO
In the same way, you can create as many aliases of known data types as you want. You must also be aware of rules that govern each data type. Here are examples:
CREATE TYPE NaturalNumber FROM int; GO CREATE TYPE Boolean FROM bit; GO
Columns Review
Introduction
To see the structure of a table in the SQL Server Management Studio, in the Object Explorer, you can expand it:
To view the columns of a table using SQL code, in a Query Editor, type sp_columns followed by the name of the table the columns belong to. Then execute the code. Here is an example:
The bottom section displays the list of columns in the COLUMN_NAME column and other characteristics on the right columns.
The Properties of a Column
A column on a table controls the kind of data that is appropriate for that particular column. The characteristics that identify or describe such a table are defined as its properties. Two primary properties are particularly important and they are required for each column: the name and the data type. Besides these, some other properties can be used to further control the behavior of a field.
Besides the name and data type, you can control the columns of a table using the Columns property sheet in the lower section of the table in Design View. These properties sometimes depend on the data type of the column. Therefore, to specify the properties of a column, you must first select it in the upper section of the table. This selection can be done by just clicking either the name or the data type. Then you can click the first field in the lower section, select the desired property and type the necessary value:
As an alternative to using the Design View of a table, to see the characteristics of a column, in the Object Explorer, expand the database and the Tables node. Expand the Columns node:
Any of these actions would display the Column Properties dialog box:
Another way to see the properites of a column is by calling the COLUMNPROPERTY() function. Its syntax is:
COLUMNPROPERTY(id, column, property) RETURNS int/bit;
The COLUMNPROPERTY() function takes three argument and all are required. The function produces a value that depends on the type of information you want. It can produce an integer or a Boolean (bit) value.
The first argument is the integral identifier of the table that holds the column. The second argument is the name of the column. The third section specifies the type of information you want. For example, to know the maximum number of characters that a character-based column allows, you can pass the third section as PRECISION (case-insensitive).
Description
Common and enabled for all fields, the description is used for a sentence that describes the column. You can type anything on that field.
Because different human languages use different mechanisms in their alphabetic characters, this can affect the way some operations are performed on data, you can ask the database to apply a certain language mechanism to the field by changing the Collation property. Otherwise, you should accept the default specified by the table.
To specify the collation of a column when creating in, type COLLATE, followed by the desired collation code. Here is an example:
CREATE TABLE Customers(
FullName nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
);
Columns Maintenance
Introduction
Column maintenance consists of reviewing or changing any of its aspects. This includes reviewing the structure of columns of a table, renaming a column, deleting a column, changing the data type or the nullity of a column, etc. When making a change on a column, you are also said to alter the table. One of the operations involved with modifying a column consists of changing its data type. To support this operation, the SQL starts with the following formula: ALTER TABLE table-name When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table. |
 |
Adding a New Column
After a table has already been created, you can still add a new column to it.
To visually add a new column in SQL Server Management Studio, in the Object Explorer:
In both cases, in the empty bottom field, enter the necessary information. In SQL, the basic formula to add a new column to a table is:
ALTER TABLE table-name ADD ColumnName Properties
ColumnName is required. In fact, on the right side of the ADD word, define the column by its name and using all the options we reviewed for columns. Here is an example:
ALTER TABLE StaffMembers
ADD Gender nchar;
GO
When this code executes, a new column named Gender of type nchar will be added to a table named StaffMembers in the current database.
You can also use sample code to add a new column to a table. First display an empty Query Editor and display the Templates Explorer. Expand the Table node. Under Table, drag Add Column and drop it in the Query Editor. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:
--==========================================================================
-- Add column template
--
-- This template creates a table, then it adds a new column to the table.
--==========================================================================
USE <database, sysname, AdventureWorks>
GO
-- Add a new column to the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
ADD <new_column_name, sysname, column3>
<new_column_data-type,, datetime>
<new_column_nullability,, NULL>
GO
Inserting a New Column
To visually insert a new column between two existing ones, right-click the column that will succeed it and click Insert Column:
This would create a new empty field. Type the desired name and specify the other options.
Renaming a Column
If you find out that the name of a column is not appropriate, you can change it.
To visually rename a column, in the Object Explorer:
In Microsoft SQL Server Managemen Studio, to change the name of a column, first open an empty Query Editor. In a Query Editor, use the following formula of sp_rename:
sp_rename 'table-name.ColumnName', 'NewColumnName', N'COLUMN'
Then execute the statement. sp_rename and 'COLUMN' are required. table-name is the name of the table that the column belongs to. ColumnName is the current name of the column. NewColumnName is the desired name you want to give to the column. Here is an example:
sp_rename N'StaffMembers.FullName', N'EmployeeName', N'COLUMN'; GO
When this code executes, the interpreter will look for a column named FullName in the StaffMembers table of the current or selected database. If it finds that column in the table, then it renames it EmployeeName.
Changing the Data Type of a Column
To visually change the data type of a column, open the table in Design View, locate the column under Column Name, and change its type under Data Type.
The formula to programmatically change the data type of a column is:
ALTER TABLE table-name ALTER COLUMN ColumnName Newdata-type
Here is an example:
CREATE SCHEMA HumanResources;
GO
CREATE TABLE HumanResources.Genders
(
Gender char
);
GO
ALTER TABLE HumanResources.Genders
ALTER COLUMN Gender tinyint;
GO
Deleting a Column
If you have an undesired column that you don't want anymore in a table, you can remove it:
ALTER TABLE table-name DROP COLUMN ColumnNameOn the right side of the ALTER TABLE expression, type the name of the table. On the right side of the DROP COLUMN expression, enter the name of the undesired column. Here is an example:
ALTER TABLE StaffMembers DROP COLUMN CurrentResidence; GOWhen this code executes, the interpreter will look for a column named CurrentResidence in a table StaffMembers of the current or selected database. If it finds that column, it will remove it from the table.
Microsoft SQL Server can also generate sample code you can use to delete a column from a table. Before doing this, first display an empty Query Editor and display the Templates Explorer. Expand the Table node. In the Table section, drag Drop Column and drop it in the Query Editor. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:
--============================================
-- Drop column template
--
-- This template creates a table, then it
-- drops one of the columns of the table.
--============================================
USE <database, sysname, AdventureWorks>
GO
-- Drop a column from the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
DROP COLUMN <new_column_name, sysname, column3>
GO
Practical Learning: Ending the Lesson
|
|
|||
| Previous | Copyright © 2000-2025, FunctionX | Last Update: Monday 07 April 2025, 21:30 | Next |
|
|
|||