SQL Server Data Types you Must Know

Why data types are important

  1. The data is stored in the database in a consistent and known format.
  2. Knowing the data type allows you to know which calculations and formulations you can use on the column.
  3. Data types affect storage. Some values take up more space when stored in one data type versus another.  Take our age tables above for example.
  4. Data types affect performance. The less time the database has to infer values or convert them the better.  “Is December, 32, 2015 a date?”

Commonly used SQL Server Data Types

There are over thirty different data types you can choose from when defining columns.  Many of these are set up for very specific jobs such as storing images, and others more suitable to general use.

Here is the data types you’ll most frequently encounter in your everyday use of SQL.  These are:

  • INT
  • VARCHAR, NVARCHAR
  • DATETIME
  • DECIMAL, FLOAT
  • BIT

INT – Integer Data Type

The integer data type is used to store whole numbers.  Examples include -23, 0, 5, and 10045.  Whole numbers don’t include decimal places.  Since SQL server uses a number of computer words to represent an integer there are maximum and minimum values which it can represent.  An INT datatype can store a value from -2,147,483,648 to 2,147,483,647.

Practical uses of the INT data type include using it to count values, store a person’s age, or use as an ID key to a table.

But INT wouldn’t be so good to keep track of a terabyte hard drive address space, as the INT data type only goes to 2 billion and we would need to track into the trillions.  For this you could use BIGINT.

The INT data type can be used in calculations.  Since DaysToManufacture is defined as INT we can easily calculate hours by multiplying it by 24:

SELECT Name,
       DaysToManufacture,
       DaysToManufacture * 24 as HoursToManufacture
FROM   Production.Product

Here you can see the results

Use of INT to perform calculations.

There are many operations and functions you can use with integers which we’ll cover once we dig into functions.

VARCHAR and NVARCHAR – Text Values

Both VARCHAR and NVARCHAR are used to store variable length text values.  “VARCHAR” stands for variable length character.

The number of characters to store in a VARCHAR or NVARCHAR are defined within the column.   For instance as you can see in the following column definition from the object explorer, the product name is defined to hold fifty characters.

VARCHAR definition shown in SQL Server Management Studio

What makes VARCHAR popular is that values less than fifty characters take less space.  Only enough space to hold the value is allocated.  This differs from the CHAR data type which always allocates the specified length, regardless of the length of the actual data stored.

The VARCHAR datatype can typically store a maximum of 8,000 characters.  The NVARCHAR datatype is used to store Unicode text.  Since UNICODE characters occupy twice the space, NVARCHAR columns can store a maximum of 4,000 characters.

The advantage NVARCHAR has over VARCHAR is it can store Unicode characters.  This makes it handy to store extended character sets like those used for languages such as Kanji.

If your database was designed prior to SQL 2008 you’ll most likely encounter VARCHAR; however, more modern databases or those global in nature tend to use NVARCHAR.

DATETIME – Date and Time

The DATETIME data type is used to store the date and time.  An example of a DATATIME value is

1968-10-23 1:45:37.123

This is the value for October 23rd, 1968 at 1:45 AM.  Actually the time is more precise than that.  The time is really 45 minutes, 37.123 seconds.

In many cases you just need to store the date.  In these cases, the time component is zeroed out.  Thus, November 5th, 1972 is

1972-11-05 00:00:00.000

A DATETIME can store dates from January 1, 1753, through December 31, 9999.  This makes the DATETIME good for recording dates in today’s world, but not so much in William Shakespeare’s.

As you get more familiar with the various SQL built-in functions you’ll be able to manipulate the data.  To give you a glimpse, we’ll use the YEAR function to count employees hired each year.  When given a DATETIME value, the YEAR function return the year.

The query we’ll use is

SELECT   YEAR(HireDate),
         Count(*)
FROM     HumanResources.Employee
GROUP BY YEAR(HireDate)
ORDER BY YEAR(HireDate)

And here are the results

Use YEAR on DATETIME data type

The benefit is the DATETIME type ensures the values are valid dates.  Once this is assured, we’re able to use a slew of functions to calculate the number of days between dates, the month of a date and so on.

We’ll explore these various functions in detail in another blog article.

DECIMAL and FLOAT – Decimal Points

As you may have guessed DECIMAL and FLOAT datatypes are used to work with decimal values such as 10.3.

I lumped DECIMAL and FLOAT into the same category, since they both can handle values with decimal points; however, they both do so differently:

If you need precise values, such as when working with financial or accounting data, then use DECIMAL.  The reason is the DECIMAL datatype allows you to define the number of decimal points to maintain.

DECIMAL

DECIMAL data types are defined by precision and scale.  The precision determine the number of total digits to store; whereas, scale determine the number of digits to the right of the decimal point.

A DECIMAL datatype is specified as DECIMAL(precision,scale).

A DECIMAL datatype can be no more than 38 digits.  The precision and scale must adhere to the following relation

0 <= scale <= precision <= 38 digits

In the Production.Product table, the weight column’s datatype is defined as DECIMAL(8,2).  The first digit is the precision, the second the scale.

Weight is defined to have eight total digits, two of them to the right of the decimal place.  We’ll the following sample query to illustrate how this data type.

SELECT   DISTINCT Weight
FROM     Production.Product
WHERE    Weight BETWEEN 29.00 and 189.00
ORDER BY Weight DESC

The results follow:

Using DECIMAL data type to display results

FLOAT

Where DECIMAL datatypes are great for exact numbers, FLOATs are really good for long numeric values.  Though a DECIMAL value can have 38 digits total, in many engineering and scientific application this is inadequate.  For scientific applications where extreme numeric values are encountered, FLOAT rises to the top!

FLOATS have a range from – 1.79E+308 to 1.79E+308.  That means the largest value can be 179 followed by 306 zeros (large indeed!).

Because of the way float data is stored in the computer (see IEEE 754 floating point specification) the number stored is an extremely close approximation.  For many application this is good enough.

Because of the approximate behavior, avoid using <> and = operators in the WHERE clause.  Many a DBA has been burned by the statement.

WHERE mass = 2.5

Their expectation are dashed when mass is supposed to equal 2.5, but really, in the computer it is stored as 2.499999999999999; therefore, not equal to 2.500000000000000!

That is the nature of floating points and computers.  You and I see 2.499999999999999 and think for practical purposes it is 2.5, but to the computer, were off just a bit.  J

BIT – Boolean or Yes/No values

There’s times when you just need to store whether something “is” or “is not.”  For instance, whether an employee is active.  It is in these cases that the BIT datatype comes to its own.  This data type be one of three states: 1, 0, or NULL.

The value of 1 signifies TRUE and 0 FALSE.

In this query we’re listing all salaried position job titles

SELECT DISTINCT JobTitle
FROM   HumanResources.Employee
WHERE  SalariedFlag = 1

Here are the results

Using the BIT data type in Searches

We could have also use ‘True’ instead of 1.  Here is the same example using ‘True’

SELECT DISTINCT JobTitle
FROM   HumanResources.Employee
WHERE  SalariedFlag = 'True'

And the opposite using ‘False’

SELECT DISTINCT JobTitle
FROM   HumanResources.Employee
WHERE  SalariedFlag = 'False'

I tend to stick with 1 and 0, since it is easier to type, but if you’re going for readability, then ‘True’ and ‘False’ are good options.

Read more:
https://www.essentialsql.com/commonly-used-sql-server-data-types/

Data Modelling Examples

Exercise 1

An art researcher has asked you to design a database to record details of ARTISTS and the MUSEUM in which their PAINTINGS are displayed. For each painting, the researcher wants to know the size of the canvas, year painted, title, and style. The nationality, date of birth and death of each artist must be recorded. For each museum, record details of its location and speciality, if it has one.

Picture2

Exercise 2

The president of a BOOK wholesaler has told you that she wants information about the PUBLISHERS, AUTHORS and books that she carries. She knows that publishers publish many books and books can be written by one or more authors (obviously authors can also write more than one book). In order to track accurate information about the publisher, the wholesaler wants to know the publishers’ name, their phone number and their address. They also want to know who to call when they need to contact the publisher. The wholesaler also wants to know the book titles, the ISBN number, the date it was published, the genre, and the author of the book. She also wants some basic information about the authors.

Picture3

 

ER Modelling Example

SCENARIO:

This problem is concerned with modelling of a database that contains information on researchers, academic institutions, and collaborations among researchers in a graduate school.

A researcher can be employed as either a professor or a lab assistant. There are
three kinds of professors: assistant, associate and full professors.

The following should be stored:
• For each researcher, his or her name, date of birth, and current position (if any).
• For each institution, its name, country, and inauguration year.
• For each institution, the names of its schools (e.g. School of Law, School of Business). A school belongs to exactly
one institution.
• An employment history, including information on all employment (start and end dates, position, and what
school).
• Information about co-authorships: i.e., which researchers have co-authored a research paper. The titles of
common research papers should also be stored.
• For each researcher, information on his or her highest degree (BSc, MSc or PhD), including who was the main
supervisor, and at what school. The supervisor must be a professor.
• For each professor, information on what research projects (title, start and end dates) he or she is involved in,
and the total amount of grant money for which he or she was the main applicant.

QUESTION:

Draw a logical ERD in crow’s foot notation for the dataset described above. Make sure to indicate all relevant entity sets, attributes, primary keys, foreign keys, relationships and cardinalities. Following good database design principle, the ERD should not contain redundant entity sets, relationships or attributes. Use relationships whenever appropriate and include verb phrases when it is helpful. If you need to make any assumptions, state them clearly in your answer.

HINTS:

The core components of the ERD could be divided as followed:
1) Researcher – Authorship – Paper
2) Professor – Assignment – Project
3) Researcher – Employment/Position – School/Institution
4) Researcher –Qualification/Degree – School/Institution and Professor/Supervisor
5) Institution – School
6) Researcher is-a LabAssistant and Professor (or Researcher – ResearcherType) with an
attribute to differentiate among the different rank of professorship (the suggested solution uses type attribute in Employment).

The alternative thinking as long as they are reasonable:
1) It is acceptable to have a currentPosition or position attribute for the Researcher
2) It is acceptable for School to be a strong entity set, which could affect the PK of School,
Employment/Position and Qualification/Degree
3) It is acceptable if students do not rename the FK in the entity set if there is no conflict 4) It is ok if they opt for a ResearchType pattern instead of a specialization or superset/subset. The solution is not as good because LabAssistant and Professor do not share many of the relationships.