Choosing Between Char, Varchar and NVarChar Data Types

What is the best way to store a string data type into an SQL Server database? I’d probably decide not to influence your decision about choosing which one was the best among the three, but I’d demonstrate an example with which we could use to differentiate each data type from the rest.

In SQL Server, we can interchangeably use CHAR and VARCHAR or NVARCHAR to store a string value like ‘GLEN’ for example. Each of which will allocate the right space for it and the effect of how the data is stored is respective to each own.

For example, the CHAR data type can hold the string ‘GLEN’ and so does VARCHAR but these won’t tell you about the kind of strategy being used to save the word ‘GLEN’ into a field. So to give us a bird’s eye view of how the SQL Server stores data on each data type, here are the basic differences between the three:

CHAR
Data is padded with blanks to fill the entire field size. For instance, if one creates a CHAR(20) field to store a string with 4 characters only,  the system will pad it with additional 16 blanks to fill in the unused storage space being allocated. This data type requires 1 byte of storage and can store up to 8000 characters.

VARCHAR
Unlike the CHAR data type, if one creates the field of the same length, e.g. VARCHAR(20), the system will store the actual value of the string ‘GLEN’ and will not pad it with extra blanks. Using this data type will actually save us an extra space in the long run. Like CHAR, this requires 1 byte of storage and can store up to 8000 characters.

NVARCHAR
Like the VARCHAR data type, this will also store the actual value of the string and does not pad the unused allocated characters with blanks. The advantage of using NVARCHAR over the two counterparts is that it supports both Unicode and Non-Unicode Data Types. As opposed to VARCHAR, this requires 2 bytes of storage and can only store up to 4000 characters.

Difference between Unicode and Non-Unicode Data Types

An example of Non-Unicode data types is the US English characters. Data types that use 1 byte to encode each character can only represent 256 different characters. The client tools like ADO.Net, uses the collation of Non-Unicode data as a hint to choose the code page of the data, like 1252 for Latin1 (ANSI) as an example.

With Unicode, however, the client does not need to interpret the data according to a code page, because the data itself is already multi-byte and the client will just interpret it according to the UCS-2 encoding which is the actual Unicode style preferred by SQL Server.

SQL Server Example

In this example, we have created a table with three fields of different data types: CHAR, VARCHAR and NVARCHAR. We then filled it in with similar values, and for this case, it’s my name, GLEN.

To easily find out how much storage space our strings have taken up, I have used the function DATALENGTH(). The result shows the spaces the three data types have used up respectively.

CHAR = 20 bytes

VARCHAR = 4 bytes

NVARCHAR = 8 bytes

Notice that NVARCHAR uses 2 bytes of storage for each characters. It can be said that using NVARCHAR is not a wise choice because the additional byte isn’t necessary if the system we’re creating is exclusively using the US English language only. However, if the program you wish to create requires support for at least one foreign language, like the Korean Hangul or the Japanese Kanji for instance, then using NVARCHAR data type is the way to go.

Configuring PhpMyAdmin to Connect to MariaDB

Assuming that you have a webserver, a running PhpMyAdmin connected to a locally installed MySQL and a newly installed MariaDB in your local server, we can easily set MariaDB as the default database server for PhpMyAdmin. Aside from that assumption, what we're also guessing here is that you have the necessary rights to access the virtual directory or the webroot of your local webserver where your copy of PhpMyAdmin folder is installed.

Anyway, here’s how I did it.

1. In the PHPMYADMIN folder, copy the file config.sample.inc.php to a new file config.inc.php.
2. Open the file config.inc.php in any text editor. Look for the following lines:

/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;

3. Notice that the default value for the “host” field is localhost.  By right, as I’ve said in the beginning, PhpMyAdmin referring to localhost as its server for database will connect to MySQL. And by some standards, the port rightfully assigned to MySQL Server is port 3306. For MariaDB, I set it to use the port 3308, so what we’re gonna do is attach that port number to the string needed by the host field so PhpMyAdmin should connect to MariaDB instead.

/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost:3308';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;

4. Leave the tcp as the standard connection type.

Here’s what PhpMyAdmin connected to MariaDB should somehow look like in your end. Now, the real question is, why the need to use PhpMyAdmin when one can be better off with some desktop SQL Managers like HeidiSQL?

The whole point of doing this is not to replace your current SQL Manager with PhpMyAdmin but to augment it. What I mean by that is, it's almost not possible to be at your own desk all the time. Sometimes, you’ll have to work on another computer in your company. What PhpMyAdmin can do is to let you access your database in the browser in any computer in your local network and do your thing right there especially in times of urgency. Otherwise, you’ll just have to walk back to your desk and use HeidiSQL that is installed in your office PC.

That’s all for this post. Have a nice day everyone!

Boolean Field in Visual Foxpro and MS SQL Server

In Visual Foxpro 9 (VFP) and it’s lower versions, there exists a logical field with which a programmer can use to have some kind of a digital flip-type switch that we regularly use in queries or in a control, like a checkbox, for example. If one can imagine how a light switch works, the idea behind is the same with that of a logical field. The apparent difference emerging from that point being, is that the usual ON and OFF status that we regularly see on a common household light switch is omitted, the logical field in VFP uses a boolean value of either .T. or .F. instead, with “.T.” representing the value for TRUE and “.F.” for FALSE.

So it’s kind of like when your dad asks you if you have left the garage lights on overnight, you may probably hear, “Is it TRUE that you have left the lights on last night?”

Anyway, Creating a logical field in a VFP cursor is as simple as this.

Create Cursor curTest (field1 l)

Select curTest

MODIFY STRUCTURE

But in MS SQL Server, with versions like 2008 or 2012, there is no Boolean or Logical field type. This is probably the reason why I see many software developers turning their trust to characters and integer types just to work around this inadequacy. 

For instance, a programmer can use several of the INT (integers) type field just to represent a value of 0 or 1. Because 0 and 1 can both respectively represent the ON and OFF nature of a basic switch. The idea makes sense until you add another value, like “2” then it loses the foremost characteristic of being a “flip-type” switch. 

The Workarounds in SQL Server

I’ve seen programmers use the int type, which has a range of -2^31 (-2,147,483,648) to  2^31-1 (2,147,483,647) and can take up storage of 4 Bytes in the table. Others use the smallint type, of which range is shorter than the int type but takes up 2 Bytes of storage space. There is also the tinyint that has a range of only 0 to 255 and takes up one Byte of storage space.

One thing to consider when you are feeling a bit determined to use these INT types for a simple switch is that each column of int created in a table will take up the storage space that I mentioned above. For example, if you have two columns of a tinyint field, each column will respectively take up one Byte of storage in a table.

At the other end of the spectrum, there are also those who use a character field to represent a switch in a similar fashion. These programmers, like I do in the early stages of my programming career, have liked the idea of using CHAR(1) type field to mimic the purpose of a logical field. The reason being is that it’s a straightforward solution and it’s easy to read. All that one needs to do is update the char field with a string having a value of “T” or an “F” character. 

The downside of using a char(1) field, however, is that it can take up other characters as well like “A” for instance, aside from the “T” or “F” characters. Also, each char(1) type column’s value in a table will take up 1 Byte of storage. Therefore, if you have two of this column in your table, the values will take up 2 Bytes and so on.

What I Think Is the Right Way to Do It

The best thing to do, in my humble opinion, is to use a bit type field in MS SQL Server. This type accepts integer data type like a bit value of 1 or 0, and it can have a NULL value as well.  

In this example, I’ve created a temporary table with a bit type field. I then inserted a 1 value.

If (Select name from sys.tables where name like '%#testTable%') is not null

      Begin

            drop table #testTable;

            create table #testTable (field1 bit);

     

            insert into #testTable (field1) values (1);

           

            select field1 from #testTable;     

      End

The advantage of the bit type is that it can automatically convert the string values TRUE and FALSE to bit values.  The “TRUE” becomes 1 and “FALSE” becomes 0.

If (Select name from sys.tables where name like '%#testTable%') is not null

      Begin

            drop table #testTable;

            create table #testTable (field1 bit);

     

            insert into #testTable (field1) values ('TRUE');

           

            select field1 from #testTable;     

      End

Sending Updates from Visual Foxpro

Luckily, MS SQL Server will convert the boolean value sent from Visual Foxpro by way of SQL-Pass Through or (SPT) connection.  

For this example, I have a table that I use for keeping LEAVE PAYS. It has a bit type column named isLeapYear. Let’s see what’s in it.

Notice that all other values in the isLeapYear column has zero values except for what’s in the fourth row, which has a bit value of 1.

Now, let’s try and change that into 0 (zero) by sending an UPDATE from Visual Foxpro with this command.

=SQLExec(oHandle.nHandle,'Update MyDatabase.dbo.MyTAble Set isLeapYear = .F.')

Now, we can see that all of the values in the isLeapYear column became zeros. 

Showing the Results with a Checkbox Control in a Grid

Let’s change the value of the fifth column, in the first row to 1. Then we’ll pull the records from MS SQL Server to a Visual Foxpro Grid that has a checkbox column.

=SQLExec(oHandle.nHandle,'Update MyDatabase.dbo.MyTAble Set isLeapYear = .F. where lpID = 9')

 So, this is how it should look like now. 

After we pulled the records from SQL Server, this is what a grid with a checkbox control would look like. Notice that the checkbox is automatically ticked because the underlying value in that column is .T. while the rest have .F. values. I will discuss how I created this checkbox in a grid  on a new post once I get a free time again.

Final Thoughts

Visual Foxpro is somehow lucky to have found its Logical values automatically converted by MS SQL Server to bit values. 

But another point worth considering for using a bit type field is that the SQL Server Database Engine optimizes storage of bit columns.  For example, if a table has less than 8 bit columns, all of the columns are stored as 1 byte. Having more than 8 bit columns but less than 16 will be stored as 2 bytes and so on.

As opposed to using an int type that always stores 1 byte or more per column, this is a great way to conserve server storage space if one is too concern about data normalization and storage conservation. 

Have a nice day!

CinchPro Payroll System

Right now, I'm working on a payroll system I named CinchPro Payroll System. This will be used as a separate payroll system or in conjunction with Tangosoft Biometric Attendance Software. The software fits Papua New Guinea's payroll procedures including tax deductions.

CinchPro comes from the word cinch, which means "easy" and pro as professional. The program aims to simplify payroll processing by removing much of the hassle and let the system work things out for you.