add bool column within existing table

Question

I am trying to dd a column, to a database with the program Microsoft SQL Server Management Studio.

I already have a database, with a table, in that table i need to add another column.. but it keeps saying it cannot find type bool or boolean.

my code :

ALTER TABLE table_name ADD IsOpen boolean GO

Any ideas ?

side question, any idea how to alter an existing column ? i have a column called "budget" but it needs to be "Budget".

Solution 1

You need to use bit instead of bool datatype

ALTER TABLE table_name ADD IsOpen bit
 GO

Here some info about datatypes

Data type   Access  SQLServer   Oracle  MySQL   PostgreSQL
boolean     Yes/No   Bit         Byte   N/A      Boolean

Answer for Qustion 2

The syntax to rename a column in an existing table in SQL Server (Transact-SQL) is:

Syntax:

sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

For your column :

sp_rename 'table_name.budget', 'Budget', 'COLUMN';

Solution 2

bit (0/1) is used as boolean in SQL

ALTER TABLE table_name ADD IsOpen bit GO

Solution 3

ALTER TABLE [dbo].[TableX] ADD IsOpen bit 

Solution 4

In mssql there is no type called boolean or bool. You have to use bit, which is 0 or 1.

Solution 5

The answers here are good but there's a best practice that I'm surprised hasn't been mentioned. I would recommend making all new boolean/bit columns NOT NULL.

By allowing null values, the value you're storing can be true, false, or null. In many boolean cases, having a third value (null) doesn't make sense and has the potential to cause unforeseen bugs.

This is the SQL script I would recommend for creating bit/boolean columns:

ALTER TABLE table_name ADD IsOpen bit NOT NULL DEFAULT 1
GO

This will not allow null values and will default records to have a 1/true value. If you expect your data to be false/0 by default, then just change the 1 here to a 0.

View more solutions

Comments

[andrelange91]

any idea for the 2nd. question ? the "side question"