Join Adam Wilbert for an in-depth discussion in this video Understanding the built-in SQL Server data types, part of Database Foundations: Creating and Manipulating Data.
- When we begin building databases one of the first questions we'll need to answer is; what kind of data will need to be stored? A data type is simply a declaration of what kinds of information a database will allow and how much disk space or resources you want to allocate to the storage of that information. They're most commonly used when defining columns in a table but they'll also come into play when creating local variables or expressions in your SQL scripts. Some data types will only allow you to store whole numbers and others will allow you to store special international text characters.
Data types also come in a variety of precisions as well. For instance, some time based data types are accurate to the second while others will keep perfect time down to a 100 nanoseconds. While it might seem that being more accurate is always better keep in mind that higher precision comes at a cost of longer processing speeds and higher storage requirements. Choosing which one to use comes down to your business needs and the rules that you establish for your data. Microsoft SQL Server features many built-in data types that can be grouped into the following categories; exact numbers, approximate numbers, dates and times, strings of text characters, unicode character strings, binary data such as images and other files, and spatial data.
We'll look at how you can get the most out of each of these types throughout this chapter. When performing calculations or combining values together it's not uncommon to pair two values of different data types. For instance, if you were to multiply a money value and an integer together. SQL Server uses something called Data Type Precedence to control what happens to those values and the data type that gets assigned to the result. When you combine two pieces of data that are of a different data type SQL Server will convert the type with the lower precedence into the type with the higher precedence.
Finally, lets talk about Collation. Because SQL Server supports many different kinds of data types special rules have been created to control sorting order. For instance, do capital letters come before lowercase? What about accented characters? Do numbers sort before letters or after? Collation is the term that defines the various rules for how all of these data types would land if you threw them all into a big pile and had the server put them in order. The default collation type follows Latin character rules, is case insensitive, but accent mark sensitive.
These settings can be changed when the server is setup or specified for each individual database.
Note: This course will also prepare certification candidates for the Microsoft Technology Associate Exam 98-364, Database Administration Fundamentals.
- Storing dates, times, and text
- Converting data types
- Creating tables
- Writing T-SQL commands
- Selecting records with queries
- Combining and sorting data
- Creating views
- Creating stored procedures and functions
- Inserting and updating data in a table
- Deleting records and tables