|
|
Error Message
Server: Msg 173, Level 15, State 1, Line 2
The definition of column <Column Name> must
include a data type.
Causes
The basic syntax for creating a table using the CREATE TABLE statement is as follows:
|
CREATE TABLE <Table Name> (
<Column Name 1> <Data Type> [ NULL | NOT NULL ] ,
<Column Name 2> <Data Type> [ NULL | NOT NULL ]
)
From this basic syntax of CREATE TABLE statement, all elements are required except for the NULL / NOT NULL constraint of a column. The table name, column name and data type are needed to be specified for the table to be created. Assigning a data type to each column is one of the first steps to take in designing a table. Data types define the data value allowed for each column. Data types can be assigned to a column by one of the following:
- Using SQL Server system data types.
- Creating alias data types that are based on system data types.
- Creating user-defined types from types created in the Microsoft .NET Framework common language runtime.
There is no default data type assigned if the data type of a column is not specified. This error message will be encountered if the data type is not included in the column definition as can be seen in the following CREATE TABLE statement:
CREATE TABLE [dbo].[User] (
[UserID] NOT NULL,
[FirstName] NOT NULL,
[LastName] NOT NULL
)
Msg 173, Level 15, State 1, Line 2
The definition for column 'UserID' must include a data type.
Similarly, when declaring a table variable, the data type also needs to be specified, otherwise, this error message will be encountered as well.
DECLARE @User TABLE (
[UserID] NOT NULL,
[FirstName] NOT NULL,
[LastName] NOT NULL
)
Msg 173, Level 15, State 1, Line 2
The definition for column 'UserID' must include a data type.
Solution / Work Around:
To avoid this error from happening, always make sure that a data type is associated with a column when creating a table. Assigning a data type to an object defines four attributes of the object:
- The kind of data contained by the object
- The length or size of the store value
- In the case of numeric data types, the precision of the number
- In the case of numeric data types, the scale of the number
SQL Server data types can be organized into the following categories:
- Exact numeric (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint)
- Approximate numeric (float, real)
- Date and time (date, datetime2, datetime, datetimeoffset, smalldatetime, time)
- Character strings (char, varchar, text)
- Unicode character strings (nchar, nvarchar, ntext)
- Binary strings (binary, varbinary, image)
- Other data types (cursor, hierarchyid, sql_variant, table, timestamp, uniqueidentifier, xml)
Using the example earlier, here’s how the CREATE TABLE statement will look like which will avoid this error message from happening:
CREATE TABLE [dbo].[User] (
[UserID] INT NOT NULL,
[FirstName] VARCHAR(50) NOT NULL,
[LastName] VARCHAR(50) NOT NULL
)
|