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:
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.
Properly utilizing data types is essential to proper database programming, as they directly lead to query efficiency. Efficient queries are essential to creating quality, scalable applications.