change the datatype and size

change the datatype and size 轉換資料型態注意事項

以NVARCHAR轉換成VARCHAR為例

轉換資料型別

When you change the datatype from nvarchar to varchar, and the column contains the Unicode string, then SQL Server returns the error and terminates the statement.

when converting from NVARCHAR to VARCHAR, as this may lead to data loss if the original data contains characters that are not supported in VARCHAR

轉換資料長度大小

decrease the size of the column, the SQL Server will check the data of the table, and if the length of the data is higher than the new length, it returns the warning and terminate the statement

CREATE TABLE [dbo].[Table_2](

[c1] [int] NULL,

[c2] [nvarchar](10) NULL

)

-- find the rows that can't be converted (利用明確轉換 Explicit conversion來檢查資料是否可以正確轉換)

SELECT t.*

FROM [dbo].[Table_2] t

WHERE NULLIF(REPLACE(TRY_CONVERT(varchar(max), t.c2), '?',''), '') IS NULL

-- 使用ALTER TABLE語法變更型別並對資料進行隱含轉換 (隱含轉換 Implicit conversion)

ALTER TABLE [dbo].[Table_2] ALTER COLUMN c2 varchar(20) ;

-- 轉換時目的地的資料長度，建議指定為兩倍，因為當nvarchar(10)裡面的值如果是中文且長度LEN有超過5，轉換成varchar(10)則會失敗出現以下錯誤，因為varchar(10)只能儲存5個中文字元

ALTER TABLE [dbo].[Table_2] ALTER COLUMN c2 varchar(10) ;

Msg 2628, Level 16, State 1, Line 6

String or binary data would be truncated in table 'TESTDB.dbo.Table_2', column 'c2'. Truncated value: ''.

The statement has been terminated.

-- 若要採用明確轉換 Explicit conversion，則可使用INSERT SELECT與rename Table方式完成資料轉換轉換

A. Change the data type of a column

The following example changes a column of a table from INT to DECIMAL.

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

DROP TABLE dbo.doc_exy ;

GO

B. Change the size of a column

The following example increases the size of a varchar column and the precision and scale of a decimal column. Because the columns contain data, the column size can only be increased. Also notice that col_a is defined in a unique index. The size of col_a can still be increased because the data type is a varchar and the index isn't the result of a PRIMARY KEY constraint.

-- Create a two-column table with a unique index on the varchar column.

CREATE TABLE dbo.doc_exy (col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2)) ;

GO

INSERT INTO dbo.doc_exy VALUES ('Test', 99.99) ;

GO

-- Verify the current column size.

SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale

FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy') ;

GO

-- Increase the size of the varchar column.

ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25) ;

GO

-- Increase the scale and precision of the decimal column.

ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4) ;

GO

-- Insert a new row.

INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;

GO

-- Verify the current column size.

SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale

FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy') ;

ALTER TABLE (Transact-SQL)

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16

Data type conversion (Database Engine)

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16