How to create and drop an MS SQL column

Today, we will learn how to add and drop or rename the created table column.

Description of MSSQL column creation.

• There is a way to create UI through SQL Server Management Studio.
• There is a way to create a database column using Transact-SQL.
• You can also create commands from the CMCD window.
• The database column corresponds to a column in the table.
• It is recommended to back up the column before dropping it.
• You can also drop the database column after renaming it before removing it.

How to create an MSSQL column.

How to create a table through SQL Server Management Studio (SSMS)

1-1. Object explorer - Database- Tables - Column -right mouse - Select New Column.

1-2. TABLE and column input screen.

• Enter a “newAddColumn”.
• View and select the Data Type capture screen to enter.
• Select and enter Allow Nulls.
• Click Save when you are done typing.

1-3. Refresh the Object explorer.

• You can verify that the column has been created.

Create a column using Transact-SQL

2-1. In the SQL Run window, type the source, and click Execute.

use [sampleDB]
go
ALTER TABLE dbo.userInfoTsql ADD newAddColumnTsql VARCHAR (25);

2-2. Check for errors and check the post-refresh column created by Object explorer.

How to change and drop the MSSQL column.

Delete column through SQL Server Management Studio (SSMS)

3-1. Object explorer - Database - Tables - Column - Right mouse - Select Delete.

3-2. In the Delete Object window, click OK.

3-3. Object explorer - Check for columns after refresh.

Delete a column created using Transact-SQL.

4-1. In the SQL Run window, type the source, and click Execute.

use [sampleDB]
go
ALTER TABLE dbo.userInfoTsql DROP COLUMN newAddColumnTsql;

4-2. Object explorer - Refresh again. Check that the column has been dropped.

Changes to columns created using Transact-SQL.

5-1. In the SQL Run window, type the source, and click Execute.

• You can also change the name of the column, but you can also change the size of the data.

use [sampleDB]
go
 
EXEC SP_RENAME 'dbo.userInfoTsql.hp_number', 'cellNumber', 'column';
 
-- DataSize  : VARCHAR(100) -> VARCHAR(200)
ALTER TABLE dbo.userInfoTsql ALTER COLUMN TsqlAddColumnTest VARCHAR(200);
 
-- Datatype : VARCHAR(200) -> NCHAR(500)
ALTER TABLE [table name] ALTER COLUMN [column name] [Type of data to be changed];

ALTER TABLE dbo.userInfoTsql ALTER COLUMN TsqlAddColumnTest NCHAR(500);
 

5-2. Object explorer - Refresh again. Check that the column has been changed.

Let us wrap it up.

1. There are many functions to add and change columns, including defaults, not null, constraints, and so on.
2. I will do the added functions when I explain the pk.