Data Types

Thursday Feb 25th 1999 by Christopher Shaw

The first may seem really simple and even in some cases too simple. Know your data types. Knowing your data types is just as important as knowing your data.

The first may seem really simple and even in some cases too simple.

Know your data
. Let me explain. Numeric data types have to be numeric. You can not store the
letter "A" in an int. field. This is just the way it works. A character data
type does not mean you are limited to the letters A-Z. It may store numbers as well, and
in many cases it has to. One of the biggest mistakes that I have made on a database is
storing a zip code in an Int. field. When I designed the database that I was working on, I
thought my zip code is 90210, and that is in the range that can stored in an Int. field
data type. The problem lies where there are zip codes that have a preceding zero. Before I
knew it I had four digit zip codes in my database. What a mess that was to clean up!
Knowing your data types is just as important as knowing your data.

This theory can work both ways. At one time I stored numeric data in a
character field due to the ease of importing the data, as there were problems in the data.
This became a big problem about six months later. My boss came to me and said, "Hey,
wouldn’t it be great if we could do a search on this location number?" I started
thinking about it. I had mistyped this data. I could not deliver quick results on these
searches because of the data type. To make this search work as fast as it could I would
need it to do a conversion. This meant what I needed to do was create a new table with the
exact definition of the first table but with the change of the data type. Then I would
have to insert into, or select into and create the table on the fly, with the convert
statement in the code. After I was done then I had to rename the table so that all the
pre-existing stored procedures would still refer to the proper place. Then I had to start
running indexes on the second table. To finish it off I would run update statistics and
change all the stored procedures that affected that table. I considered myself lucky. I
had no triggers or constraints on that table.

These steps may not be the steps that others would have taken but these were the steps
that I had to do if I was going to get the job done correctly. The results are the same.
If I had thought about what I was doing ahead of time then I would not have had all this
additional work. I can tell you one thing I won’t, make this mistake again! I guess
that it was a lesson that I had to learn on my own.


Know your data

This is another mistake that I made. My data was purchased from a
data company. This company made the same mistake that I had by not knowing the data. I am
glad that I am not the only one that has made that mistake.

Without getting into exact details, if you want to use a code that will represent
other items use numbers. Real numbers, and no numbers that start with a zero. If they
start with a zero, they have to be stored as character data types. When you have millions
of rows of data it is much quicker to query on numbers then alpha characters. There are
cases where you may need letters as codes, but before you decide, make sure that you know
exactly why you decided the way that you did. It sounds silly but it can save you many
problems. I like using codes in my data storage, especially when I have many records that
will have the same data in a field. For example, in a big address book would you want to
store each county that a person lives in? Or could you use a code? I live in Orange County
and many of the companies that I do business with are here also. Instead of storing the
word Orange, I will store the number 1 in a field for all companies that are in Orange.
Why would I want to store county? The taxes are always changing by what county that you
live in. I would have a table that sits to the side that would contain the County,
County_Code, Tax_Amount, and County code being the unique key.

Know the Syntax.

There are many programs that will help you in creating the syntax
for a query or other tasks. Even the EM will do some of this for you, such as permissions,
create table, and build indexes. Things will be much easier to do in the GUI. I must admit
I started by using the GUI, but I can’t stand to use it now. If you know the syntax
you may be able to do it faster, and easier. The syntax has a pattern to it. Once you
learn a few statements the rest are easier to learn.

I do not like to use the EM. I like to use the keyboard, not the mouse. I have
problems navigating through all the GUI’s that are in the EM. Creating tables is slow
with keyboard to mouse movement, while building indexes is the whole nine yards! Every
thing can be done much quicker if you don’t use the GUI.

I am not saying never use the GUI. I have found that it can help me. For example, I am
new at this and I want to create a table. I am not to sure on how this create table syntax
works. First I would check BOL. If that doesn’t clear up the confusion in my mind
then I would try the GUI . I can create the table in the GUI and name it as Junk. To learn
the syntax for all the data types I simply script out the table. This sounds repetitive.
Why script it out if I already have it? I found that I script it out then use that script
so I can see a good example of how the Create table works. I also get an example to GRANT
and BUILD INDEX. I don’t recommend this over BOL, but in some cases, when I
can’t find exactly what I am looking for, I improvise.

There are some tools that can help in learning the syntax. I use Embarcadero's DBArtisan
I like the way that the product color codes your SQL statements. I will soon be posting a review
on DBArtisan. You can check out a trial version of DBArtisan on their home page.

You Don’t Know Everything

I know it’s hard to believe. As you read this
you most likely are saying that I know this. This guy isn’t telling me anything that
I don’t know. But here is the secret. You don’t have to know everything. Just
know where to find it. Don’t forget all the resources that you have at your
fingertips. BOL, Tech Net, books, www.Swynk.com are
the most important after you looked at the others. Ask people that you know. The list
servers are a great tool. Join in. Ask and answer.

Don’t sit on the List servers and just ask questions. Answer the
questions that you can. I remember one day I was sitting at work and had a problem that I
couldn’t fix. For some reason my users were getting overlapped results on an ASP
page. I was looking all over and I couldn’t seem to find the answer to this anywhere.
I was entering the destructive mode, just before I was going to go nuts and attack my
Servers with the ever-famous rubber mallet. I posted this question and in an hour I had
the problem solved. I could have sat there for many more hours searching and getting more
and more frustrated as I went along. I got my help because some kind soul decided to help
me. The end result is that if I can help someone from reaching that point I will do so.


I don’t know any other way to say this.

It’s never a matter of "If I am going to lose data", but when. Things
are going to crash. It will happen, and to some people like me it’s more often. The
first SQL class that I took the instructor was talking about the job responsibilities of a
DBA. When he spoke of backups, he said , "In most companies if there is a database
crash and there is no recoverable backup that DBA is fired". In my opinion this
should be the case. If nothing else I think that the lack of recoverable backups is cause
for termination. Many people will say what if it’s a bad tape drive? What if
it’s not my fault? Guess what? It is. If the tape drive is bad you should know it. If
it’s not recoverable it is your fault.

As far as I am concerned, and the company that I work for is concerned, number one
priority for me is keeping the database up. The company that I work makes their money by
their data. This is the way we pay the bills. If there is no data there is no job.

When your database crashes there are ways to minimize loss. The way to do this is the
transaction log. The server that I have is up 24/7. I backup the full database once a
night (then I run DBCC). It’s a backup to disk in a directory that I call backup. In
the directory of backup, I have a folder called trans. In this folder I have created six
backup devices named trans1 to trans6. During normal business hours I backup the
transaction log to a trans device. The first transaction log back up is done to trans1,
the second is done to trans2 and so on. I am running Seagate’s backup program to run
a backup to tape of the transaction logs from the disk shortly after the backup is done. I
do one of the main databases after that disk backup is done. This schedule is kept for 21
days. Two of the tapes are then taken off site to ensure that if there was a natural
disaster that I still have a copy. Before a new 21-day cycle is started a full restore is
done on a separate machine to ensure good backups. The DAT is cleaned every day and we
keep a cleaner tape in the drive at all times. After this is done, do the same for your
master database.

This is where it can get scary. You have done all this prep to do your back ups, you
have the cycle done and you may have even been able to do a restore. Then you forget the
master database. Here we go again. I have done this and I am here to share so let the
laughing begin. I used to back up the master once a week. Sounds good right? I thought so.

The company that I use to work for wanted an old copy of a database installed. The copy
was close to six months old. The reason that they wanted this copy installed was there was
a change of data. There was a huge change in the data and they wanted to verify some
numbers. I was an Assistant DBA at this company and I was not doing the backups at all.
They were being handled by the DBA. I had that data and the database but they had no
master. I am sure now that there is an easier way to do this (DISK INIT, DISK REFIT) but
at the time I didn’t know this.

I was there for days trying to restore the data. To sum it all up, backup the Master at
the same time as the database. In my case, the Master is very small and it is worth the

Mobile Site | Full Site