Now that you've seen the SQL Server features let's look at how exactly data is stored in tables. This is another step in the process of designing a logical model.
What Are the Data Types Used to Store Data?
Attributes contain the entity descriptions that define what data can be stored. That description is known as a data type and can include things such as numbers, date/time, boolean, and text. It’s important to choose the right one when dealing with mathematical operations.
Numeric (or number) fields store only numeric values and in special situations can hold currency, decimals , or integers. In most cases, you can also define the specifics of the number. With decimals, you can define the number values available to the right or left of the decimal point. Integer fields contain only whole numbers (including negative).
What happens if I define an integer field and decimal values are entered?
This can happen, and normally the DBMS will round your value up or down based on rounding conventions. Software applications usually only allow the user to enter the exact type of number required by the table.
Date/time fields can hold a range of formats, including date or time only, or both in specific formats. Date fields should always be used for date or time operations since you are more or less guaranteed to get correct results when performing operations like adding or subtracting dates to determine the length of service for an employee or someone’s age.
Boolean fields are very basic and should be used for yes or no answers. Pretty much any yes or no question would be best served using this field.
Couldn’t I just use a number field and say use a 0 for no and 1 for yes?
Yes, but what the values actually represent can sometimes come into question. Does the 1 mean yes, or does the 2 mean yes? You might end up needing to include another table to define them. From past experience, I’d say you are better off using a boolean value, rather than an integer value, especially if you expect to have someone else working on the database tables.
Text fields can be represented by a few different data types, such as character or char fields, which usually define a limited number of characters that can be entered. More commonly, SQL Server uses the varchar() value where the developer can define the number of characters that can be stored by putting a value between the parenthesis like this: varchar(100) which means 100 characters can be entered in this field. Since you are allocating the amount of memory that will be used for this data type, its best to set your text values larger than what you will use by 20% to ensure you don’t lose out on larger data.
How and When Would You Use Specific Data Types?
Great question! Experienced developers can often select specific data types by looking at what must be stored in a table. If you use a date/time field to save an employee hire date, you can easily calculate years of service by using built-in SQL date functions. Date/time fields expect only this type of data, and you will receive an error if you attempt to enter anything else. A text field will accept almost any type of data, but calculations could be difficult.
Let's look at the more detailed level of data types where you can specify what a field can hold. Luckily, SQL Server has more specific data types that further define the type of data you want to capture. Date records only the date, time records only the time, and datetime records them both. It would make sense to use datetime as a timestamp indicating a record was created or updated. The time data type would be a good option to record how many minutes a process ran. You would use the date data type to capture an employee’s birthday. Many other data types allow you to specify how much or how long the recorded data can be.
Others have similar features like varchar() and binary, which allow the user to specify a value between 1 and 8000, so you can record that many characters depending on the selected value. Images can be saved to most database tables, but it's not recommended since they can be large. It's better to save them to a file server and record the image link in a text field.
Let’s Recap!
Data types are the specific types of data that are captured, such as:
Numeric
Date/time
Boolean
Text
When you use appropriate data types, it allows you to use built-in functions, such as adding numbers or determining days between calendar dates.
Now that you're familiar with data types, let's check your understanding of SQL vs. NoSQL databases and the elements that make up a relational database.
