你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站,请访问 https://docs.azure.cn。
.alter column command
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer
Alters the data type of an existing table column.
Warning
When altering the data type of a column, any pre-existing data in that column will return a null value in future queries.
After using .alter column
, that data cannot be recovered, even by using another command to alter the column type back to a previous value.
If you need to preserve pre-existing data, see our recommended procedure for changing the type of a column without losing data.
Permissions
You must have at least Table Admin permissions to run this command.
Syntax
.alter
column
[DatabaseName .
] TableName .
ColumnName type
=
ColumnNewType
Learn more about syntax conventions.
Parameters
Name | Type | Required | Description |
---|---|---|---|
DatabaseName | string |
The name of the database that contains the table. | |
TableName | string |
✔️ | The name of the table that contains the column to alter. |
ColumnName | string |
✔️ | The name of the column to alter. |
ColumnNewType | string |
✔️ | The new data type for the column. |
Example
.alter column ['Table'].['ColumnX'] type=string
Changing column type without data loss
To change column type while retaining the historical data, create a new, properly typed table.
For each table T1
you'd like to change a column type in, execute the following steps:
Create a table
T1_prime
with the correct schema (the right column types and the same column order).Ingest the data into
T1_prime
fromT1
, applying the required data transformations. In the example below, Col1 is being converted to the string data type..set-or-append T1_prime <| T1 | extend Col1=tostring(Col1)
Swap the tables using .rename tables command, which allows swapping table names.
.rename tables T_prime=T1, T1=T_prime
When the command completes, the new data from existing ingestion pipelines flows to
T1
that is now typed correctly.Drop the table
T1_prime
T1_prime
will include only a copy of the historical data (before the schema change) and can be safely dropped after confirming the schema and data inT1
were correctly updated..drop table T1_prime
Example:
Change column "Col1" data type in table T1
from guid to string.
// Create table T1
.create table T1 (Col1:guid, Id:int)
// Ingest sample data into T1
.ingest inline into table T1 <|
b642dec0-1040-4eac-84df-a75cfeba7aa4,1
c224488c-ad42-4e6c-bc55-ae10858af58d,2
99784a64-91ad-4897-ae0e-9d44bed8eda0,3
d8857a93-2728-4bcb-be1d-1a2cd35386a7,4
b1ddcfcc-388c-46a2-91d4-5e70aead098c,5
// Create table T1_prime with the correct schema and same column order
.create table T1_prime (Col1:string, Id:int)
// Append data to the new table
.set-or-append T1_prime <| T1 | extend Col1=tostring(Col1)
// Rename tables
.rename tables T1_prime = T1, T1 = T1_prime
// Drop table T1_prime, which now has the old schema and data
.drop table T1_prime