| employee | 1 | sample_index | 1 | Name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | Now, the structure of modified indexes in a table will be displayed as shown below − It is the SQL server equivalent of SHOW INDEX statement in MySQL. ![]() You can verify the indices of the table using the EXEC sp_helpindex statement as shown. įollowing query adds an index on the columns Name and Salary −ĪLTER TABLE Employee ADD INDEX sample_index (Name, Salary) You can add index to an existing column of a table using the ADD INDEX statement along with the ALTER statement −ĪLTER TABLE table_name ADD INDEX index_name (key_part.). To verify the above query if you describe the table using the EXEC sp_help command you can observe the created column in the field list as shown below − You can add the new column at the start using the FIRST statement along with alter, following is the syntax to do so −ĪLTER TABLE table_name ADD COLUMN (column_name column_definition.) FIRSTįollowing query adds a column with name ID at start −ĪLTER TABLE Employee ADD COLUMN ID INT FIRST | Address | varchar(50) | YES | | NULL | | | Location | varchar(255) | YES | | NULL | | | Field | Type | Null | Key | Default | Extra | ![]() Now, the structure of the newly altered table will be displayed as shown below − To verify the above query if you describe the table using the 'EXEC sp_help' command you can observe the created column in the field list as shown below − If you want to add a column before or after an existing column you need to use AFTER followed by the name of the existing column (after the column definition).ĪLTER TABLE table_name ADD COLUMN (column_name column_definition.) AFTER existing_columnįollowing query adds a column named address after the Location.ĪLTER TABLE Employee ADD COLUMN Address VARCHAR(50) AFTER Location | ID | NAME | AGE | ADDRESS | SALARY | SEX | Now, the CUSTOMERS table will be displayed as − To verify whether the CUSTOMERS table is altered by adding a new column SEX, use the SELECT statement to retrieve the records of the table − | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |įollowing is the example to ADD a New Column to an existing table −Įxecuting the query above will produce the following output − If you need to add a new column to a table you should use the ADD COLUMN option to ALTER TABLE statement as shown below −ĪLTER TABLE table_name ADD column_name datatype Ĭonsider the CUSTOMERS table having the following records − Where, the alter_option depends on the type of operation to be performed on a table. ![]() Syntaxįollowing is the basic syntax of an ALTER TABLE command −ĪLTER TABLE table_name This is also where the ALTER TABLE command differs from UPDATE command while ALTER TABLE interacts with the structure of a table to modify it, UPDATE only interacts with the data present in the table without disturbing its structure. Since this command modifies the structure of a table, it is a part of Data Definition Language. ![]() You can also use the ALTER TABLE command to add and drop various constraints on an existing table. The SQL ALTER TABLE command is used to modify a table's structure by adding, deleting columns in an existing table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |