Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Query String Parser Function
FAQ - SQL Server

FAQ - SQL Server 2005
Home > Frequently Asked Questions > SQL Server Tables
SQL Server Tables - Frequently Asked Questions

 6.  How can I determine if a table has a primary key?

Answer:

To determine if a table has a primary key, you will use the OBJECTPROPERTY metadata function.

OBJECTPROPERTY ( id, property )

The OBJECTPROPERTY metadata function, which returns information about objects in the current database, accepts two parameters, namely the id, which is the ID of the object in the current database, and the property, which is the information to be returned for the object specified by id.  To determine if a table has a primary key, you will use the TableHasPrimaryKey property.

IF OBJECTPROPERTY( OBJECT_ID( '[dbo].[Customers]' ), 'TableHasPrimaryKey' ) = 1
    PRINT '[dbo].[Customers] table has a primary key.'

In this script, the OBJECTPROPERY metadata function will return a value of 1 if the table has a primary key.  Otherwise, it will return a value of 0.  If the table name passed to the function is not a valid table in the current database, the function will return a value of NULL.


 7.  How can I change the length of a VARCHAR or CHAR column?

Answer:

To change the length of a VARCHAR or CHAR column, you will use the ALTER TABLE ALTER COLUMN command as follows:

ALTER TABLE Table Name ALTER COLUMN Column Name VARCHAR ( New Length )

To illustrate, let's say you have the following table definition:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]     INT,
    [FirstName]      VARCHAR(20),
    [LastName]       VARCHAR(20)
)

After initial design, you found out that the length of the [FirstName] and [LastName] columns are not long enough.  To increase their length, you will issue the following statements:

ALTER TABLE [dbo].[Customers] ALTER COLUMN [FirstName] VARCHAR(50)
ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName]  VARCHAR(50)

Be careful when decreasing the length of a column especially when there's data in the table already.  If you try to decrease the length of a VARCHAR or CHAR column that has data and the new length is not long enough to store the existing column data, the following error message will be encountered:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

To avoid this error, make sure that the new length to be assigned to the column is long enough to hold the longest string in the column.  Please refer to the following link regarding this error message:

Frequently Asked Questions - SQL Server Error Messages


 8.  How do I change a column from a NULL to a NOT NULL?

Answer:

To change a column to have the NOT NULL constraint, you will use the ALTER TABLE ALTER COLUMN command as follows:

ALTER TABLE Table Name ALTER COLUMN Column Name Data Type NOT NULL)

As can be seen from this command, to add the NOT NULL constraint to a column you still have to specify the data type of the column even if you are not changing the data type.

To illustrate, let's say you have the following table definition:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]     INT,
    [FirstName]      VARCHAR(20),
    [LastName]       VARCHAR(20)
)

Since the nullability of the columns was not specified, it defaults to NULL, meaning the column will accept NULL values.  There has been some changes in the business rules and you are now asked to make sure that all columns will have a value.  To implement this change in the business rule, you will simply execute the following statements

ALTER TABLE [dbo].[Customers] ALTER COLUMN [CustomerID] INT NOT NULL
ALTER TABLE [dbo].[Customers] ALTER COLUMN [FirstName] VARCHAR(20) NOT NULL
ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName] VARCHAR(20) NOT NULL

Just make sure that the column you are changing doesn't have any NULL values otherwise you will encounter the following error:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'LastName', table 'SQLServerHelper.dbo.Customers'; 
column does not allow nulls. UPDATE fails.
The statement has been terminated.

To avoid this error, update your table by setting the columns with NULL values to a certain value.  If the column is a VARCHAR or CHAR data type, you can update it to an empty string.  If the column is a numeric data type, you can set it to 0.

UPDATE [dbo].[Customers]
SET [LastName] = ''
WHERE [LastName] IS NULL

 9.  What's the maximum number of rows can a table have?

Answer:

The maximum number of rows a table can have is limited by the available storage in the server.  For more information on the maximum capacity specifications of both SQL Server 7.0, SQL Server 2000 and SQL Server 2005, please refer to the following link:

SQL Server Maximum Capacity Specifications


 10.  How can I determine if a column is an identity column?

Answer:

To determine if a column is an identity column, you will use the COLUMNPROPERTY metdata function.

COLUMNPROPERTY ( id, column, property )

The COLUMNPROPERTY metadata function, which returns information about a column or stored procedure parameter in the current database, accepts three parameters, namely the id, which is the ID of the table or stored procedure in the current database, the column, which is the name of the column or stored procedure parameter, and the property, which is the information to be returned for the table or stored procedure specified by id.  To determine if a column is an identity column, you will use the IsIdentity property.

IF COLUMNPROPERTY( OBJECT_ID( '[dbo].[Orders]' ), 'OrderID', 'IsIdentity' ) = 1
    PRINT 'Orders.OrderID is an identity column.'

In this script, the COLUMNPROPERTY metadata function will return a value of 1 if the column is an identity column.  Otherwise, it will return a value of 0.  If the table name passed to the function is not a valid table in the current database or if the column does not exist in the table or stored procedure, the function will return a value of NULL.

More Frequently Asked Questions - SQL Server Tables Previous Page  Next Page  
Related Articles :