| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Reference Manual: Commands |
![[-]](/dwicons/i_colpse.gif)
| Chapter 1 Commands |
|
| alter table |
|
| Usage |
If stored procedures using select * reference a table that has been altered, no new columns appear in the result set, even if you use the with recompile option. You must drop the procedure and re-create it to include these new columns. Otherwise, the wrong results can be caused by the insert...select statement of insert into table1 select * from table2 in the procedure when the tables have been altered and new columns have been added to the tables.
When the table owner uses alter table, Adaptive Server disables access rules during the execution of the command and enables them upon completion of the command. The access rules are disabled to avoid filtering of the table data during alter table.
Warning!
Do not alter the system tables.
You cannot add a column of datatype bit to an existing table.
The maximum number of columns in a table is:
1024 for fixed-length columns in both all-pages-locked (APL) and data-only-locked (DOL) tables.
254 for variable-length columns in both APL and DOL tables.
1024 for variable-length columns in both APL and DOL tables.
alter table raises an error if the number of variable-length columns in an APL table exceeds 254.
The maximum length for in-row Java columns is determined by the maximum size of a variable-length column for the table's schema, locking style, and page size.
When converting a table to a different locking scheme, the data in the source table cannot violate the limits of the target table. For example, if you attempt to convert a DOL with more than 254 variable-length columns to an APL table, alter table fails because an APL table is restricted to having no more than 254 columns.
Columns with fixed-length data (for example char, binary, and so on) have the maximum sizes shown in the following table:
Locking scheme | Page size | Maximum row length | Maximum column length |
* This size includes six bytes for the row overhead and two bytes for the row length field | |||
APL tables | 2K (2048 bytes) | 1962 | 1960 bytes |
4K (4096 bytes) | 4010 | 4008 bytes | |
8K (8192 bytes) | 8106 | 8104 bytes | |
16K (16384 bytes) | 16298 | 16296 bytes | |
DOL tables | 2K (2048 bytes) | 1964 | 1958 bytes |
4K (4096 bytes) | 4012 | 4006 bytes | |
8K (8192 bytes) | 8108 | 8102 bytes | |
16K (16384 bytes) | 16300 | 16294 bytes - if table does not include any variable length columns | |
16K (16384 bytes) | 16300 (subject to a max start offset of varlen = 8191) | 8191-6-2 = 8183 bytes - if table includes at least on variable length column.* |
The maximum number of bytes of variable length data per row depends on the locking scheme for the table. The following describes the maximum size columns for an APL table:
Page size | Maximum row length | Maximum column length |
2K (2048 bytes) | 1960 | 1960 |
4K (4096 bytes) | 4008 | 4008 |
8K (8192 bytes) | 8104 | 8157 |
16K (16384 bytes) | 16296 | 16227 |
The following describes the maximum size columns for a DOL table:
Page size | Maximum row length | Maximum column length |
2K (2048 bytes) | 1960 | 1958 |
4K (4096 bytes) | 4008 | 4006 |
8K (8192 bytes) | 8157 | 8102 |
16K (16384 bytes) | 16294 | 16294 |
You cannot partition a system table or a table that is already partitioned.
You cannot issue the alter table command with a partition or unpartition clause within a user-defined transaction.
The maximum value for max_rows_per_page is 256 bytes for APL tables. max_rows_per_page parameter is not used for DOL tables.
You cannot partition a system table or a table that is already partitioned.
You cannot issue the alter table command with a partition or unpartition clause within a user-defined transaction.
You cannot use alter table to add a declarative or check constraint and then insert data into the table in the same batch or procedure. Either separate the alter and insert statements into two different batches or procedures, or use execute to perform the actions separately.
You cannot use the following variable in alter table statements that include defaults:
declare @a int select @a = 2 alter table t2 add c3 int default @a
Doing so results in error message 154, which says, "Variable is not allowed in default."
For information about a table and its columns, use sp_help.
To rename a table, execute the system procedure sp_rename (do not rename the system tables).
For information about integrity constraints (unique, primary key, references, and check) or the default clause, see create table in this chapter.
Use the asc and desc keywords after index column names to specify the sort order for the index. Creating indexes so that columns are in the same order specified in the order by clause of queries eliminates the sorting step during query processing. For more information, see Chapter 8, "Indexing for Performance" in the Performance and Tuning Guide.
When you create a cross-database constraint, Adaptive Server stores the following information in the sysreferences system table of each database:
Information stored in sysreferences | Columns with information about the referenced table | Columns with information about the referencing table |
Key column IDs | refkey1 through refkey16 | fokey1 through fokey16 |
Table ID | reftabid | tableid |
Database ID | pmrydbid | frgndbid |
Database name | pmrydbname | frgndbname |
When you drop a referencing table or its database, Adaptive Server removes the foreign key information from the referenced database.
Because the referencing table depends on information from the referenced table, Adaptive Server does not allow you to:
Drop the referenced table,
Drop the external database that contains the referenced table, or
Rename either database with sp_renamedb.
You must first remove the cross-database constraint with alter table.
Each time you add or remove a cross-database constraint, or drop a table that contains a cross-database constraint, dump both of the affected databases.
Warning!
Loading earlier dumps of these databases could cause database corruption.
The sysreferences system table stores the name and the ID number of the external database. Adaptive Server cannot guarantee referential integrity if you use load database to change the database name or to load it onto a different server.
Warning!
Before dumping a database in order to load it with a different name or move it to another Adaptive Server, use alter table to drop all external referential integrity constraints.
You can create column defaults in two ways: by declaring the default as a column constraint in the create table or alter table statement or by creating the default using the create default statement and binding it to a column using sp_bindefault.
You cannot replace a user-defined default bound to the column with sp_bindefault. Unbind the default with sp_unbindefault first.
If you declare a default column value with create table or alter table, you cannot bind a default to that column with sp_bindefault. Drop the default by altering it to NULL, then bind the user-defined default. Changing the default to NULL unbinds the default and deletes it from the sysobjects table.
Adaptive Server issues error message 154, "Variable is not allowed in default," if you use a variable as the argument to a default that is part of an alter table statement. For example:
declare @a int select @a = 2 alter table t2 add c3 int
default @a
The space management properties fillfactor, max_rows_per_page, and reservepagegap in the alter table statement apply to indexes that are created for primary key or unique constraints. The space management properties affect the data pages of the table if the constraint creates a clustered index on an allpages-locked table.
Use sp_chgattribute to change max_rows_per_page or reservepagegap for a table or an index, to change the exp_row_size value for a table, or to store fillfactor values.
Space management properties for indexes are applied:
When indexes are re-created as a result of an alter table command that changes the locking scheme for a table from allpages locking to data-only locking or vice versa. See "Changing locking schemes" for more information.
When indexes are automatically rebuilt as part of a reorg rebuild command.
To see the space management properties currently in effect for a table, use sp_help. To see the space management properties currently in effect for an index, use sp_helpindex.
The space management properties fillfactor, max_rows_per_page, and reservepagegap help manage space usage for tables and indexes in the following ways:
fillfactor leaves extra space on pages when indexes are created, but the fillfactor is not maintained over time. It applies to all locking schemes.
max_rows_per_page limits the number of rows on a data or index page. Its main use is to improve concurrency in allpages-locked tables.
reservepagegap specifies the ratio of empty pages to full pages to apply for commands that perform extent allocation. It applies to all locking schemes.
Space management properties can be stored for tables and indexes so that they are applied during alter table and reorg rebuild commands.
The following table shows the valid combinations of space management properties and locking schemes. If an alter table command changes the table so that the combination is not compatible, the values stored in the stored in system tables remain there, but are not applied during operations on the table. If the locking scheme for a table changes so that the properties become valid, then they are used.
Parameter | allpages | datapages | datarows |
max_rows_per_page | Yes | No | No |
reservepagegap | Yes | Yes | Yes |
fillfactor | Yes | Yes | Yes |
exp_row_size | No | Yes | Yes |
The following table shows the default values and the effects of using the default values for the space management properties.
Parameter | Default | Effect of using the default |
max_rows_per_page | 0 | Fits as many rows as possible on the page, up to a maximum of 255 |
reservepagegap | 0 | Leaves no gaps |
fillfactor | 0 | Fully packs leaf pages |
If a table has max_rows_per_page set, and the table is converted from allpages locking to data-only locking, the value is converted to an exp_row_size value before the alter table...lock command copies the table to its new location. The exp_row_size is enforced during the copy. The following table shows how the values are converted.
If max_rows_per_page is set to | Set exp_row_size to |
0 | Percentage value set by default exp_row_size percent |
255 | 1, that is, fully packed pages |
1-254 | The smaller of:
|
Commands that use large amounts of space allocate new space by allocating an extent rather than allocating single pages. The reservepagegap keyword causes these commands to leave empty pages so that future page allocations take place close to the page that is being split or to the page from which a row is being forwarded.
The reservepagegap value for a table is stored in sysindexes, and is applied when the locking scheme for a table is changed from allpages locking to data-only locking or vice versa. To change the stored value, use the system procedure sp_chgattribute before running alter table.
reservepagegap specified with the clustered keyword on an allpages-locked table overwrites any value previously specified with create table or alter table.
Partitioning a table with the partition clause of the alter table command creates additional page chains, making multiple last pages available at any given time for concurrent insert operations. This improves insert performance by reducing page contention and, if the segment containing the table is spread over multiple physical devices, by reducing I/O contention while the server flushes data from cache to disk.
If you are copying data into or out of a partitioned table, the Adaptive Server must be configured for parallel processing.
When you partition a table, Adaptive Server allocates a control page for each partition, including the first partition. The existing page chain becomes part of the first partition. Adaptive Server creates a first page for each subsequent partition. Since each partition has its own control page, partitioned tables require slightly more disk space than unpartitioned tables.
You can partition both empty tables and those that contain data. Partitioning a table does not move data; existing data remains where it was originally stored, in the first partition. For best performance, partition a table before inserting data.
You cannot partition a system table or a table that is already partitioned. You can partition a table that contains text and image columns; however, partitioning has no effect on the way Adaptive Server stores the text and image columns.
After you have partitioned a table, you cannot use the truncate table command or the sp_placeobject system procedure on it.
To change the number of partitions in a table, use the unpartition clause of alter table to concatenate all existing page chains, then use the partition clause of alter table to repartition the table.
If you unpartition a table, recompile the query plans of any dependent procedures. Unpartitioning does not automatically recompile procedures.
When you unpartition a table with the unpartition clause of the alter table command, Adaptive Server deallocates all control pages, including that of the first partition, and concatenates the page chains. The resulting single page chain contains no empty pages, with the possible exception of the first page. Unpartitioning a table does not move data.
When adding an IDENTITY column to a table, make sure the column precision is large enough to accommodate the number of existing rows. If the number of rows exceeds 10 precision - 1, Adaptive Server prints an error message and does not add the column.
When adding an IDENTITY column to a table, Adaptive Server:
Locks the table until all the IDENTITY column values have been generated. If a table contains a large number of rows, this process may be time-consuming.
Assigns each existing row a unique, sequential IDENTITY column value, beginning with the value 1.
Logs each insert operation into the table. Use dump transaction to clear the database's transaction log before adding an IDENTITY column to a table with a large number of rows.
Each time you insert a row into the table, Adaptive Server generates an IDENTITY column value that is one higher than the last value. This value takes precedence over any defaults declared for the column in the alter table statement or bound to it with sp_bindefault.
add, drop, or modify, and lock sub-clauses are useful to change an existing table's schema. A single statement can contain any number of these sub-clauses, in any order, as long as the same column name is not referenced more than once in the statement.
If stored procedures using select * reference a table that has been altered, no new columns appear in the result set, even if you use the with recompile option. You must drop the procedure and re-create it to include these new columns.
You cannot drop all the columns in a table. Also, you cannot drop the last remaining column from a table (for example, if you drop four columns from a five-column table, you cannot then drop the remaining column). To remove a table from the database, use drop table.
Data copy is required:
To drop a column
To add a NOT NULL column
For most alter table ... modify commands
Use showplan to determine if a data copy is required for a particular alter table command.
You can specify a change in the locking scheme for the modified table with other alter table commands (add, drop, or modify) when the other alter table command requires a data copy.
If alter table performs a data copy, select into /bulkcopy/pllsort must be turned on in the database that includes the table whose schema you are changing.
Adaptive Server must be configured for parallel processing when you alter the schema of a partitioned table and the change requires a data copy.
The modified table retains the existing space management properties (max_rows_per_page, fillfactor, and so on) and indexes of the table.
alter table that requires a data copy does not fire any triggers.
You can use alter table to change the schema of remote proxy tables created and maintained by Component Integration Services (CIS). For information about CIS, see the Component Integration Services User's Guide.
You cannot perform a data copy and add a table level or referential integrity constraint in the same statement.
You cannot perform a data copy and create a clustered index in the same statement.
If you add a NOT NULL column, you must also specify a default clause. This rule has one exception: if you add a user-defined type column, and the type has a default bound to it, you do not need to specify a default clause.
You can always add, drop, or modify a column in an all-pages locked tables. However, there are restrictions for adding, dropping, or modifying a column in a data-only locked table, which are described in the following table:
Type of index | All pages locked, partitioned table | All pages locked, unpartitioned table | Data-only locked, partitioned table | Data-only locked, unpartitioned table |
Clustered | Yes | Yes | No | Yes |
Non-clustered | Yes | Yes | Yes | Yes |
If you need to add, drop, or modify a column in a data-only locked table partitioned table with a clustered index, you can:
Drop the clustered index.
Alter the (data-only locked) table.
Re-create the clustered index.
You cannot add a NOT NULL Java object as a column. By default, all Java columns always have a default value of NULL, and are stored as either varbinary strings or as image datatypes.
You cannot modify a partitioned table that contains a Java column if the modification requires a data copy. Instead, first unpartition the table, run the alter table command, then repartition the table.
You cannot drop the key column from an index or a referential integrity constraint. To drop a key column, first drop the index or referential integrity constraint, then drop the key column. See the Transact-SQL User's Guide for more information.
You can drop columns that have defaults or rules bound to them. Any column-specific defaults are also dropped when you drop the column. You cannot drop columns that have check constraints or referential constraints bound to them. Instead, first drop the check constraint or referential constraint, then drop the column. Use sp_helpconstraint to identify any constraints on a table, and use sp_depends to identify any column- level dependencies.
You cannot drop a column from a system table. Also, you cannot drop columns from user tables that are created and used by Sybase-provided tools and stored procedures.
You can generally modify the datatype of an existing column to any other datatype if the table is empty. If the table is not empty, you can modify the datatype to any datatype that is explicitly convertible to the original datatype.
You can:
Add a new IDENTITY column.
Drop an existing IDENTITY column.
Modify the size of an existing IDENTITY.
See the Transact-SQL User's Guide for more information.
Altering the schema of a table increments the schema count, causing existing stored procedures that access this table to be renormalized the next time they are executed. Changes in datatype-dependent stored procedures or views may fail with datatype normalization type errors. You must update these dependent objects so they refer to the modified schema of the table.
You cannot run alter table from inside a transaction.
Altering a table's schema can invalidate backups that you made using bcp. These backups may use a tables schema that is no longer compatible with the table's current schema.
You can add NOT NULL columns with check constraints, however, Adaptive Server does not validate the constraint against existing data.
You cannot change the locking scheme of a table using the alter table . . . add, drop, or modify commands if the table has a clustered index and the operation requires a data copy. Instead you can
Drop the clustered index.
Alter the table's schema.
Re-create the clustered index.
You cannot alter a table's schema if there are any active open cursors on the table.
You can only add text or image columns that accept null values.
To add a text or image column so it contains only non-null values, first add a column that only accepts null values and then update it to the non-null values.
You can only modify a column from text datatype to the following datatypes:
char
varchar
unichar
univarchar
nchar
nvarchar
You can only modify a column from image datatype to a varbinary datatype, and the column can only include non-null data.
You can modify text or image columns to any other datatypes only if the table is empty.
You cannot add a new text or image column and then drop an existing text or image column in the same statement.
You cannot modify a column to either text or image datatype.
alter table supports changing from any locking scheme to any other locking scheme. You can change:
From allpages to datapages or vice versa
From allpages to datarows or vice versa
From datapages to datarows or vice versa
Before you change from allpages locking to a data-only locking scheme, or vice versa, use sp_dboption to set the database option select into/bulkcopy/pllsort to true, then run checkpoint in the database if any of the tables are partitioned and the sorts for the indexes require a parallel sort.
After changing the locking scheme from allpages-locking to data-only locking or vice versa, the use of the dump transaction command to back up the transaction log is prohibited; you must first perform a full database dump.
When you use alter table...lock to change the locking scheme for a table from allpages locking to data-only locking or vice versa, Adaptive Server makes a copy of the table's data pages. There must be enough room on the segment where the table resides for a complete copy of the data pages. There must be space on the segment where the indexes reside to rebuild the indexes.
Clustered indexes for data-only-locked tables have a leaf level above the data pages. If you are altering a table with a clustered index from allpages-locking to a data-only-locking, the resulting clustered index requires more space. The additional space required depends on the size of the index keys.
Use sp_spaceused to determine how much space is currently occupied by the table, and use sp_helpsegment to see the space available to store the table.
When you change the locking scheme for a table from allpages locking to datapages locking or vice versa, the space management properties are applied to the tables, as the data rows are copied, and to the indexes, as they are re-created. When you change from one data-only locking scheme to another, the data pages are not copied, and the space management properties are not applied.
If a table is partitioned, changing the locking scheme performs a partition-to-partition copy of the rows. It does not balance the data on the partitions during the copy.
When you change the locking scheme for a table, the alter table...lock command acquires an exclusive lock on the table until the command completes.
When you use alter table...lock to change from datapages locking to datarows locking, the command does not copy data pages or rebuild indexes. It only updates system tables.
Changing the locking scheme while other users are active on the system may have the following effects on user activity:
Query plans in the procedure cache that access the table will be recompiled the next time they are run.
Active multi-statement procedures that use the table are recompiled before continuing with the next step.
Ad hoc batch transactions that use the table are terminated.
Warning!
Changing the locking scheme for a table while a bulk copy operation is active can cause table corruption. Bulk copy operates by first obtaining information about the table and does not hold a lock between the time it reads the table information and the time it starts sending rows, leaving a small window of time for an alter table...lock command to start.
If Java is enabled in the database, you can add Java-SQL columns to a table. For more information, see Java in Adaptive Server Enterprise.
The declared class (datatype) of the new Java-SQL column must implement either the Serializable or Externalizable interface.
When you add a Java-SQL column to a table, the Java-SQL column cannot be specified:
As a foreign key
In a references clause
As having the UNIQUE property
As the primary key
If in row is specified, then the value stored cannot exceed 16K bytes, depending on the page size of the data server.
If off row is specified, then:
The column cannot be referenced in a check constraint.
The column cannot be referenced in a select that specifies distinct.
The column cannot be specified in a comparison operator, in a predicate, or in a group by clause.
|
|