To organize the information that a column holds, a table needs a series of details about each column. Two aspects are particularly important: a name and the type of data that a column should/must/can hold.
To be able to recognize the categories of information that a column holds, the column should have a name. In Microsoft SQL Server, the name of a column displays in the top, the header part, of the column. The name of a column allows the database as a file to identify the column. The name of a column also will help you, the database developer, to identify that column. There are rules and suggestions you must or should follow when naming the columns of a table.
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:
After deciding on the name of a column, the database needs to know what kind of information the column would hold. Since there are various kinds of information a database can deal with, we saw in Lesson 5 the types of data that Microsoft SQL Server supported. Therefore, you must specify the data type that is necessary for a particular column.
A database deals with various types of data, appropriate or not for certain fields. This means that you should take care of jobs behind the scenes as much as you can. One way you can do this is by controlling the amount of information that can be stored in a particular field. As various columns can hold different types of data, so can the same data type control its own mechanism of internal data entry. The length of data means different things to different fields. Columns that carry the same data type can have different lengths.
Bit Fields: We saw already that a bit column type is meant for one of two answers. The user is supposed to simply let the database know that the answer is yes or no, true or false, on or off, 1 or 0. Therefore, the only length of this field is 1.
Integers: The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.
Decimal and Floating-Point Numbers: The Length specifies how many bytes the field can store.
Strings: The Length of a character or string column specifies the maximum number of characters that the field can hold.
In some circumstances, you will need to change or specify the length as it applies to a particular field. For example, since you should use the varchar data type for a string field whose content will change from one record to another, not all varchar columns need to have the same length. Although a First Name and a Book Title columns should use the varchar type, both columns would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both fields would use the same data type but different lengths. On the other hand, for columns of datetime2 and money data types, you should accept the default length suggested by the database.
There are two ways you can change the length of a string-based column:
If a column was already created, to find out its length, you can call the COL_LENGTH() function. Its syntax is:
COL_LENGTH ( 'table' , 'column' ) RETURNS smallint;
This function takes two required arguments. The first argument is the name of the table that owns the column. The second argument is the name of the column. The result is as follows:
Here is an example:
SELECT COL_LENGTH(N'Employees', N'HourlySalary') AS [Size of Salary]; GO
We saw that the primary formula to create a table was:
CREATE TABLE TableName
After specifying the name of the table, you must list the columns of the table. The list of columns starts with an opening parenthesis "(". The list ends with a closing parenthesis ")". Each column must be separated from the next with a comma, except for the last column. You can include all columns on the same line if possible as follows:
CREATE TABLE [SchemaName.]Country(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.]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 DataType Options
The name of a column should follow the same rules and suggestions we reviewed for the columns.
After typing the name of the column, type the desired or appropriate data type for the column. For this example, use one of the (appropriate) data types we reviewed.
Remember that some of the data types need to have a length. In the case of text-based columns, when using SQL to create your columns, because it is less visual than the table design of the SQL Server Management Studio, you cannot rely on the default length of strings suggested by SQL. As it happens, the SQL Server Management Studio specifies different default values for text-based columns. Therefore, when using SQL to create your columns, you should (strongly) specify your own default length for text-based columns.
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 window and display the Templates Explorer. Expand the Table node. Under Table, you can drag Create Table, Add Column, or Drop Column, and drop it in the query window. 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
In Lesson 5, we saw that, and how, you can create user-defined data types for existing Transact-SQL data types. In Lesson 5, we stored our types in the master database. If you are working on a database, you can create and store your new types in it. As mentioned in Lesson 5, to visually create a UDT, in the Object Explorer, expand your database, expand its Programmability node, and expand its Types node. Under Types, right-click User-Defined Data Types and click New User-Defined Data Type...
In the New User-Defined Data Type dialog box, fill in the necessary information and click OK
Remember that you can also programmatically create the data type(s). Here are examples:
USE Exercise; GO CREATE TYPE NaturalNumber FROM int; GO CREATE TYPE ShortString FROM nvarchar(20); GO CREATE TYPE ItemCode FROM nchar(10); GO CREATE TYPE LongString FROM nvarchar(80); GO CREATE TYPE Salary FROM decimal(8, 2); GO CREATE TYPE Boolean FROM bit; GO
After creating the UDT(s), you can use it(them) for your column(s). To do this visually, after displaying the table in design view, click the column name, click the arrow of the Data Type combo box to display a mix of Transact-SQL types and your own defined types:
We will write many expressions that include the names of columns. In such expressions, you will need to indicate the particular column you are referring to. There are various ways you can do this. To refer to, or to indicate, a table:
You can create an alias name of a table to use in an expression that involves a column.
To visually create an alias for a table, in the Properties window for the table, click Alias and type the desired name:
To create an alias of a table using code, type a letter or a word that will represent the table to which the column belongs. The letter or the word is followed by a period, and followed by the name of the column. An example would be empl.LastName. At the end of the statement, you must type the name of the table, followed by space, and followed by the letter or the word. An example would be Employee empl.
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.
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 window, execute sp_columns followed by the name of the table the columns belong to. Here is an example:
This action displays the list of columns in the COLUMN_NAME column and other characteristics on the right columns.
A column on a table controls what kind of data is appropriate for that particular column. The characteristics that identify or describe such a table are defined as its properties. As we have seen previously, three primary properties are particularly important and required for each column: the name, the data type, and the length. Besides these, some other properties can be used to further control the behavior of a particular field.
Besides the name, data type and length of a column, 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, the data type, or the length of the column. Then you can either press F6 or 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 bay calling the COLUMNPROPERTY() function. Its syntax is:
COLUMNPROPERTY(id, column, property) RETURNS int/bit;
This function takes three arguments and all are required. All three arguments are passed as strings. The function returns a value that depends on the type of information you want. It can return an integer or a Boolean (bit) value.
The first argument is the integral identifier of the table that holds the column. To get it, you can call the OBJECT_ID() function and pass it the name of the table. The second argument is the name of the column. The third argument specifies the type of information you want. For example, to know the maximum number of characters that a string-based column allows, you can pass the third argument as PRECISION (case-insensitive). Here is an example:
SELECT COLUMNPROPERTY(OBJECT_ID(N'Employees'), N'LastName', N'precision')AS [Maximum Number of Characters]; GO
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 languages use different mechanisms in their alphabetic characters, this can affect the way some sort algorithms or queries 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 );
When making a change on a column, you are also said to alter the table. To support this operation, SQL starts with the following formula:
ALTER TABLE TableName
When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table.
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 TableName 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 Address nvarchar(100) NULL GO
When this code executes, a new column name Address, of type nvarchar, with a limit of 100 characters, and that allows empty values, 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 window and display the Templates Explorer. Expand the Table node. Under Table, drag Add Column and drop it in the query window. 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_datatype,, datetime> <new_column_nullability,, NULL> GO
To visually insert a new column between two existing one, 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.
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 SQL, to change the name of a column, first open an empty query window. In a query window, execute sp_rename using the following formula:
sp_rename 'TableName.ColumnName', 'NewColumnName', N'COLUMN'
The sp_rename factor and the 'COLUMN' string are required. The TableName factor is the name of the table that the column belongs to. The ColumnName is the current name of the column. The 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 is executed, 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.
If you have an undesired column that you don't want anymore in a table, you can remove it. To visually delete a column, in the Object Explorer, expand the database, the Tables, and the Columns nodes. Right-click the undesired column and click Delete. The Delete Object dialog box would display. If you still want to delete the column, click OK. To change your mind, click Cancel.
To delete a column using code, first open or access an empty query window, and use the following formula:
ALTER TABLE TableName DROP COLUMN ColumnName
On 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; GO
When this code is executed, 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 window and display the Templates Explorer. Expand the Table node. In the Table section, drag Drop Column and drop it in the query window. 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