This chapter covers
- Why data types are important
- The consequences of using the wrong standard data type
- Reasons for using advanced data types
- The benefits of working with XML and JSON data
In this chapter, we will explore data types and why it is important to choose the correct data types for our table’s columns. We will start by exploring some simple data types that are commonly used but that many people use incorrectly. We will see the effect that this can have on cost and performance.
We will then explore some of SQL Server’s advanced data types, which are significantly underused. We will investigate the use cases that make them so useful and look at the effects of avoiding them. It is worth noting, however, that while this chapter explores HIERARCHYID, XML, and JSON, other specialized data types exist, such as GEOGRAPHY and GEOMETRY for geospatial data. I strongly encourage you to explore all of SQL Server’s advanced data types.
MagicChoc has decided that it needs a new application, which will support human resources. The script in listing 3.1 creates the HumanResources database and then creates the first table: dbo.employees. You will notice that this table has been created using the data type NVARCHAR(MAX) for every column. This doesn’t look right, does it? But why? All of the data that we want to store can be inserted into this expansive data type. So does it really matter? We will explore this example throughout the chapter.