Skip to content

Latest commit

 

History

History
71 lines (49 loc) · 3.53 KB

modify-columns-database-engine.md

File metadata and controls

71 lines (49 loc) · 3.53 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Modify columns
This article shows you how to modify columns using SQL Server Management Studio and Transact-SQL.
WilliamDAssafMSFT
wiassaf
01/03/2025
sql
table-view-index
how-to
modifying data types
column data types [SQL Server]
data types [SQL Server], columns
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

Modify columns

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-pdw-fabricsqldb]

You can modify the data type of a column in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql].

Warning

Modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type. In addition, code and applications that depend on the modified column can fail. These include queries, views, stored procedures, user-defined functions, and client applications. These failures will cascade. For example, a stored procedure that calls a user-defined function that depends on the modified column can fail. Carefully consider any changes you want to make to a column before making it.

Permissions

Requires ALTER permission on the table.

Use SQL Server Management Studio (SSMS)

Modify the data type of a column using SSMS

  1. In Object Explorer, right-click the table with columns for which you want to change the scale and select Design.

  2. Select the column for which you want to modify the data type.

  3. In the Column Properties tab, select the grid cell for the Data Type property and choose a new data type from the dropdown list.

  4. On the File menu, select Save table name.

Note

When you modify the data type of a column, Table Designer applies the default length of the data type you selected, even if you have already specified another. Always set the data type length for to the desired value after specifying the data type.

Warning

If you attempt to modify the data type of a column that relates to other tables, Table Designer asks you to confirm that the change should be made to the columns in the other tables as well.

Use Transact-SQL

Modify the data type of a column using Transact-SQL

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    CREATE TABLE dbo.doc_exy (column_a INT );  
GO  
INSERT INTO dbo.doc_exy (column_a) VALUES (10);  
GO  
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);  
GO

For more information on using ALTER TABLE to modify columns, see ALTER TABLE column_definition.

Next step

[!div class="nextstepaction"] ALTER TABLE (Transact-SQL)