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.
|