Understand Data Types IN SQL
In SQL, typically each
table column has an associated data type. Text, Integer, VarChar, Date, and
more, are typically available types for developers to choose from.
- MySQL Data Types
- Oracle Data Types
- SQL Server Data Types
When developing, make
sure you choose the proper data type for the column. Dates should be DATE
variables, numbers should be a numeric type, etc. This becomes especially
important when we deal with a later topic: indexing; but I'll demonstrate an
example of poor knowledge of data types below:
SELECT employeeID, employeeName
FROM employee WHERE employeeID
= 5142875;
Looks fine based on
what we currently know, correct? However, what if employeeID is actually a
string. Now we've got a problem, because the DBMS might not find a match
(because string data types and integers are different types).
Therefore, if you're
using indexing, you'll probably be perplexed as to why your query is taking
forever, when it should be a simple index scan. This is the reason that
developers need to pay special attention to data types and their applications.
Non-key attributes which are IDs are often string types, as opposed to integers,
because of the increased flexibility that is granted. However, this is also a
trouble area for junior developers, who assume that ID fields will be integers.
