Setting up a pseudo-boolean for conversion to SQL Server

[JMongi]

Since SQL doesn't use boolean fields (as I understand it anyway), what is the best way to set up a field in Access if your BE will end up on SQL server express?

I know in SQL Server itself (from Googling) there is a Bit type that is used. Is there a best practice to make this transition as seamless as possible?

For the real-life issue it would be a for a field indicating whether something was concrete filled or not.

 

[Minty]

I just use an integer field and use -1 for true and 0 for false.
You can use a bit field but it gets confusing as Access doesn't like Null values in Bit fields that is the real issue;
http://allenbrowne.com/NoYesNo.html

As long as you set the field to not accept null values and always have a default value you should't have an issue.

 

[JMongi]

I was going to use an integer field as well but didn't know if there was a best practice kind of situation when you are developing with an eye towards migrating to sql server in the future.

 

[Minty]

If you are really anal about storage space you can use Smallint in SQL that only accepts values from -32,768 to 32,767 and takes up 2 bytes.

int, bigint, smallint, and tinyint (Transact-SQL) - SQL Server

Transact-SQL reference for int, bigint, smallint, and tinyint data types. These data types are used to represent integer data.

docs.microsoft.com

 

[theDBguy]

Hi. How exactly are you planning to migrate the Access table into SQL Server? If I am not mistaken, wouldn't the SSMA tool automatically convert a Yes/No field from Access into a BIT field in SQL Server? Just thinking out loud...

 

[JMongi]

Didn't know that...LOL! But, based on Allen Browne's article, I don't think I need to mess with them.

 

[Minty]

Hi. How exactly are you planning to migrate the Access table into SQL Server? If I am not mistaken, wouldn't the SSMA tool automatically convert a Yes/No field from Access into a BIT field in SQL Server? Just thinking out loud...

@theDBguy It does indeed, but it allows nulls and that breaks it in the front end.

 

[Isaac]

I was going to use an integer field as well but didn't know if there was a best practice kind of situation when you are developing with an eye towards migrating to sql server in the future.

there is - don't use creative and weird data types, stick to the basics.