Customer Table, Part 2
By Richard Rost

25 days ago

Lesson 5: Date, Currency, Number Field Data Types

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this lesson, we continue working with the customer table in SQL Server by focusing on numeric, currency, and date data types. We discuss how to store values like family size, credit limits, and customer-since dates, and why it is important to choose the right data type, particularly when migrating data from Microsoft Access. We review best practices for handling fields such as integers, floating point numbers, decimals for currency, and datetime2 for date and time values. We also discuss SQL Server concepts like schemas, columns, keys, constraints, triggers, indexes, and statistics, with comparisons to how similar features work in Access.

Navigation

Prev: Lesson 04
Next: Lesson 06

Update

I've decided for this course to switch to datetime2(3). I'll explain why in Lesson 7, but in a nutshell, even though Access normally only shows times to the nearest second, internally, functions like Now() return millisecond precision, and that's stored in the table. You don't see it, but it's there. When you go to import this data into SQL Server, you'll get an error saying that the field can't accommodate the data. So, again, to keep things Access-compatible, datetime2(3) will be more appropriate.

Keywords

SQL Server for Access, customer table, SQL Server, numeric data types, currency data types, date data types, int, float, decimal 19 4, datetime2, bit, schema, primary key, foreign key, constraints, indexes, statistics, data integrity, referential integrit

Comments for Customer Table, Part 2

Age	Subject	From
26 days	Update on Date Fields	Richard Rost
23 days	Bit Fields	Kevin Robertson
24 days	Why Cant I Save the Table	Gary Becker

Subscribe to Customer Table, Part 2
Get notifications when this page is updated

Intro

Transcript

In lesson five, we are continuing to work with our customer table and SQL Server. We are going to look at numeric, currency, and date data types. You will learn how to store things like family size, credit limits, and customer-since dates using the right field types, and why choices like decimal versus float or datetime2 versus date actually matter, especially when you start working with real data, particularly if you are bringing that data over from Microsoft Access.

Moving right along here, let's start talking about number types. We got all the text stuff done in the last lesson. Let's move on to family size. Family, why is it not typing? Come here. There it is. Family size.

You have to be careful sometimes. You click on it and it just selects it like that. You cannot type, unlike Microsoft Access where sometimes when you just click on it and it highlights the whole field, you can start typing. SQL Server is a little trickier.

So what are we going to put in here? Family size does not require a decimal portion, so it is going to just be an int. That again will be a long integer. Allow nulls - that is up to you.

Now floating point numbers, let's do a discount rate. This is going to be a float. We are going to use float as the equivalent of a double in Microsoft Access. Remember, in Access I told you to only worry about two things: long integer and double. Well, here it is going to be int and float. Don't worry about any of the other number types until we get to it, with the exception of currency values.

So credit limit, just like in the other class, credit limit. This is going to be - now this is the weird one - it's decimal. And then you get two numbers here. I'm going to go with 19,4 and I'm going to explain what that means in just a second.

If you haven't watched this video, go watch this. It's an Access video. It's my decimal versus double. And I talk about why a lot of people pick decimal from the list of options and they can't figure out why their numbers get truncated. You want to use double and not decimal. The reason why boils down to this thing right here: precision and scale. I zoom in a little bit so you can see it.

A decimal value in Access has a precision and scale, same with SQL Server. What does this mean? The precision is the first number. That's the total number of digits allowed left and right of the decimal point. Scale, the number on the right here, is how many digits are allowed to the right of the decimal point.

So 19,4 means 19 total digits with four digits after the decimal point. Why did I pick these numbers? This is the closest thing to how Access stores currency values. So if you are going to be upgrading your database from Access to SQL Server, this is the number that's going to be most compatible with what you've got right now.

A decimal 19,4 can store all of these numbers. You get four digits after the decimal place regardless, which is roughly what Access currency values do. And if you want, you can have a number all the way up to that size right there. If you are dealing with currency values that are that large, you can afford to pay me more. That's what I say in my other video where I make the currency to text, where it writes out like a check, like 1,400. I said, if you are dealing with billions, you can write me a check and I'll update the code for you. This is why we are using decimal.

Now, don't use float for money. You might be thinking float allows decimals. No, don't use it because you run into problems like this. If you use float for money, you run into the Superman 3 Office Space problem, where the pennies do not get rounded off properly. Without getting into super math nerdiness, which I know some of you like, we will do that maybe in a future class.

Float stores numbers as approximate binary fractions, so decimal values cannot be represented perfectly. That means you get results like that sometimes. Now imagine doing thousands or tens of thousands of transactions with different rounding steps and all of a sudden you get drifting pennies that go in the little fund that Michael Bolton steals. You do not want to use it for money values. It's okay to use float for measurements: rates, percentages, heights, coordinates, that kind of stuff. Use decimal for money.

But Richard, you say, there is a money data type in here. Why not use money for money? SQL Server does have a money type and it works fine in many cases, but I still recommend decimal because decimal is a standard exact numeric type. It's the most common choice for currency values in serious database design. You control the decimals. You can choose how many decimal places you want. With money, you are kind of locked into its behavior and formatting assumptions, and money can behave unexpectedly in calculations and in rounding in certain cases.

Decimal is a lot more predictable and consistent. Most importantly, and this is for you guys, that's why this is a class for SQL Server for Access users, this translates perfectly well to a Microsoft Access currency value. When you bring your data over, it's going to fit nicely into that. So forget about money; don't use money. Just like we do not use ntext, I'll invalidate it that way. Yeah, yeah, yeah, this gate. Go away.

What's next? Next up we have customer-since. How long has this guy been a customer? We are going to use datetime2(0). You are just going to have to make yourself a cheat sheet to remember the stuff that we are going to use, like all this stuff here. It took me a while to learn this stuff too, especially when you are used to coming from Access and it's all easy stuff like just date.

SQL Server does have a date data type, but it has no time portion. So it's literally just the date. That is all you need. But if you are bringing data over from Access and you have times in there, use datetime2. What happened to just datetime? Well, it's a legacy data type. SQL Server has a whole bunch of old legacy ones that they don't use anymore, like we talked about ntext before. Datetime has quirks: less precision, less flexible accuracy. Microsoft recommends using datetime2. That's the new modern one.

Again, cheat sheet. I'll put a cheat sheet together for everybody. I'll throw it on a mouse pad too.

We are going to use datetime2 for all of our date and time values. What's the number in parentheses? That's the precision. You can do fractions of a second, not that you really ever need it with Microsoft Access, but that's like three decimals after it. Now you are storing milliseconds. Seven is super high precision; I believe that's the max. Zero means no fractional seconds. Just like Access, just date and time to the second.

So datetime2(0) is what we are going to be using for today. In fact, I have a whole separate video that I did on how to track milliseconds in Access, which is a pain in the butt. SQL Server actually makes that a lot easier if you are doing things like scientific measurements where you are tracking milliseconds. Now you can do them.

What's next? We have isActive. That's our Boolean value. In Microsoft Access, it's called a yes/no value. Here, it's a bit. We are using a big boy, big girl language. Bit, byte. Anybody knows a bit is one or zero. One bit. A byte is eight bits. We all know that one.

Does anybody know what four bits is, without Googling it? Four bits. It's half a byte. Anybody? It's called a nibble. Believe it or not, look it up: nibble. For back when memory was so expensive that we actually had to track half a byte.

If you want to set your default value for your bit, come right down here. Default value or binding. We will set that to one.

One important difference with Microsoft Access: Access stores true values internally as negative one and false as zero. It comes from old-school binary logic where true is represented as all bits turned on. In Access, false is zero, true is negative one. The reason is in binary math, negative one means all bits turned on, which makes Boolean logic work more efficiently for the database engine and the VBA code. Even though Access shows negative one and SQL Server shows one, they both mean the same thing, logically, just using different internal number systems.

If you are used to writing code where you are checking for negative one, don't check for zero or not zero because it's the same in both systems in your VBA code.

Let's save it, Control S or hit the save button. And last time I talked about - we can close this now. Last time I talked about this guy here, this dbo.CustomerT. Why is it dbo?

The dbo part sometimes confuses people. In SQL Server tables, not only does the table have a name, they also belong to something called a schema, which you can think of as a folder or a category inside the database. The default schema is dbo, which stands for database owner, and it's basically the default place where SQL Server puts your tables until you tell it otherwise. So dbo.CustomerT just basically means the CustomerT table inside the dbo schema. For this course, you can treat dbo as the standard default and do not worry about it too much until we get into more advanced database organization and security. Don't freak out - all your tables will say dbo in front of them; that's just normal.

What have we got in here? We have columns. Unlike Access, you see your list of columns right here underneath the table in the little Christmas tree hierarchy over here. It's kind of cool. You can see what their data types are. That's very handy. Remember when I used to yell at people in Access because the columns are called the field, and in Excel, they are called columns and not in Access? Well, welcome to SQL Server where they are officially called columns again. So don't worry. You are going to hear me say fields and columns interchangeably. It's just - yeah. It's kind of like my grandma when she was yelling at me. She would mix between English and Italian, and I didn't know what she was saying half the time. I will do the same thing.

Next down here, we have keys. In there, you can see we have PK_CustomerT right there. Now this shows the keys for the table. Right now we have just a primary key; that's the PK. Later, you'll see foreign keys when you relate tables. In Access, when you set a primary key, it feels like you are setting a property in a field or a table, and you see the key icon, but you really do not see a key object as a separate thing; they are just kind of baked into table design.

In SQL Server, keys are more explicit. A primary key is a real database object. It's technically a constraint, so SQL Server lists it in its own section under the table. You are not storing the key in the table as data; you are declaring a rule that SQL Server enforces. The same thing is going to apply to foreign keys as well.

In Access, relationships live in the relationships window, and you check that Enforce Referential Integrity box. This guy right here. I really love having a full library of videos that I can go back to the point of this stuff. Enforced Referential Integrity. You know how to get this? If you are not an Access user, you will not, but if you set up a relationship, you double-click on the join, and right there you can enforce referential integrity. That basically means that you cannot do things like have child records without a parent.

In SQL Server, that relationship is created as a foreign key constraint. It shows up here under keys. In Access, keys feel like table properties, but in SQL Server, they are actual database objects that SQL Server tracks and enforces, so they show up in their own folder.

Next, we have constraints. Constraints are rules that are enforced by SQL Server, such as "this field cannot be null," default values, unique rules, and so on. SQL Server uses constraints to enforce data integrity.

In Microsoft Access, you enforce rules using things like the required field properties, validation rules, referential integrity, and relationships. But a lot of Access databases also rely heavily on the front end to keep data clean. I do that all the time myself: input masks, after update events, before update events, those kinds of things. In other words, the rules often live in forms, VBA code, and code that only runs if the user actually enters data through Access the way you expect them to.

In SQL Server, constraints are much more formal. A constraint is a rule that's built into the database itself and enforced on the server, which means the rule applies no matter what program touches the data, whether it's Access through a linked table, an import, a script, or a web app.

SQL Server constraints include things like NOT NULL (which is basically a required field), default values (which we set earlier), primary key, unique rules, foreign key relationships, and check constraints for validating data (e.g., "family size cannot be higher than 20"). We are not going to get too much into these right now. We will deal with these in a future lesson. The big takeaway is that in Access, rules can be front-end dependent, but in SQL Server, constraints enforce your rules at the data level so they cannot be bypassed.

I have run into this myself where you have a table, you have a form, and you make the users pretty much always use that form. Then you build another form later on, a year or two later, and you forget that those rules were enforced at the form level. With SQL Server, you enforce them at the table level and do not have to worry about it.

Next up is triggers. These are special code that runs automatically when data changes in the table - insert, update, delete, that kind of stuff. It's a very powerful feature. We will get into them in the developer level - not needed yet. They are essentially the equivalent of Access data macros. I have done a little bit with data macros in my Access classes, but I really do not use them that much because I usually enforce things like this in the front end. They are attached directly to a table and they automatically fire when data changes, like when a record is inserted, updated, or deleted. For SQL Server, that's what makes them so powerful.

In Access, they are not extremely reliable for some reason, and they are very, very simple in Access. In SQL Server, we are going to use them a lot, and what makes them so powerful is that the logic runs no matter where the data came from, whether the user edits through an Access form, a linked table, an import, etc., like I just mentioned with constraints. We are going to have a lot of fun when we get to triggers later on.

Indexes - big, big, big time with indexes. We are going to spend some time with indexes. Indexes are your performance boosters. Your primary key automatically creates an index. You can add more indexes later to speed up searches, sorting, and joins.

I spent a lot of time in my Access classes talking about indexes. Go watch this video for some really good stuff. It is the difference between your database moving like a shopping cart with a busted wheel versus a Lamborghini on a test track. It's the difference between limping along in a shuttlecraft at impulse power or the Enterprise going warp 9.5. Trust me, my own website, when I first set it up, when I first migrated everything to SQL Server, I did not set up indexing. I'm like, why is this crawling? I've got my own dedicated server that should be running nice and fast. And then I realized I forgot to index any of the foreign keys. As soon as I did that, bam, it started running like warp speed the next day. So, big, big, big deal with indexing. We are going to spend a lot of time on this.

Sometimes you can even index fields you didn't think you had to index, like datetime fields. Oh yeah, if you are sorting orders based on the date, index that field.

Last up down here, we have statistics. We are not going to touch this much until we get into the advanced classes. That's basically SQL Server's internal notes about the data distribution in the table, used to build smart query plans and keep things fast. Great feature, totally hands-off for beginners. We will get to that later.

Before I let you go, if you are enjoying this series and want to keep going, I have a quick announcement. Level one of my SQL Server for Access Users course, which is what you are watching right now, is going to be completely free. It's shaping up to be about two hours of training before we wrap it up. I want to make sure you are fully up and running, connected to SQL Server, and working with your Access database. So that's going to all be covered in the free material.

After that, we will continue into the beginner series. Level two is just going to be a dollar, like all my other courses. Level three is currently priced at $5.99. Get it? Like my website. I just added both of these to the order page on my website. If you want to lock those in now while the pricing is in place, do it today.

If you want to get these classes at a discount moving forward, consider joining my Learning Connection. Members get 50 percent off all new course releases. My plan is to roll out at least one new lesson in this series every week or two as they are completed. It's a great way to follow the training as it grows without paying full price each time. If you are already a Learning Connection member and want to add these SQL Server classes to your rotation, just contact me through customer service and I'll make sure you are included. I'm going to put a little tool on the website for that soon, but it's not ready yet, so just contact me.

I also have the TechHelp memberships available: Silver, Gold, and Platinum, which give you access to my extended cut TechHelp videos, which are great for my Access videos. Eventually, I'll start doing some SQL Server TechHelp videos, but not yet - we are still working on the regular course. As a Silver member, you get one beginner class free every month, and you can use that to get a beginner class from this course free as well. When we get up to the expert series, you can upgrade to Gold and you'll get a free expert class as well. So, lots of different membership options available for whatever meets your needs.

Today we learned how to choose the right data types for things like numbers, money, and dates, so your tables store information accurately and behave the way you expect.

Post a comment down below. Let me know how you like today's video and how you plan to use this in your own database.

That's going to do it for lesson five. Hope you learned something. Live long and prosper, my friends. I'll see you in lesson six.

Quiz

Q1. Which SQL Server data type should you use to store a whole number value like family size?
A. float
B. decimal
C. int
D. money

Q2. Why is float not recommended for storing money values in SQL Server?
A. It uses too much storage space
B. It does not allow decimal points at all
C. It stores approximate values which can lead to rounding errors
D. It is only available in legacy versions of SQL Server

Q3. What does the decimal data type's precision and scale represent in SQL Server?
A. Precision is the number of decimal places, scale is the number of whole digits
B. Precision is the total number of digits, scale is the number of digits to the right of the decimal point
C. Both are the same and represent the total digits allowed
D. Scale is the storage space required, precision is the performance impact

Q4. Which decimal specification is most compatible with Microsoft Access currency fields?
A. decimal(10,2)
B. decimal(19,4)
C. float(10,2)
D. money

Q5. What is the main advantage of using the decimal type over the money type for currency fields in SQL Server?
A. Decimal uses less storage space than money
B. Decimal is a standard exact numeric type and provides more control over precision and formatting
C. Money is deprecated in SQL Server
D. Money cannot store values greater than 1,000

Q6. Which SQL Server data type is recommended to store both date and time values, especially if importing from Microsoft Access?
A. date
B. datetime
C. datetime2
D. smalldatetime

Q7. What does datetime2(0) represent in SQL Server?
A. Date only, no time part
B. Date and time with milliseconds
C. Date and time up to the nearest second, no fractional seconds
D. Time only, no date

Q8. What is true about Boolean (yes/no) values in SQL Server versus Microsoft Access?
A. Both store true as 1 internally
B. Access uses 0 for true and 1 for false
C. SQL Server uses bit type, Access uses yes/no; SQL Server true is 1, Access true is -1
D. Both store values as string 'true' or 'false'

Q9. In SQL Server, what is a schema (e.g., dbo)?
A. A separate database
B. A set of permissions for users
C. A logical container or category for tables within a database
D. An external data source

Q10. What is the main difference between how primary keys are handled in Microsoft Access and SQL Server?
A. No difference; both are table properties only
B. In Access, primary keys are a visual property; in SQL Server, keys are explicit database objects (constraints)
C. Access cannot create primary keys
D. In SQL Server, you cannot have a primary key

Q11. In SQL Server, what is a constraint?
A. A menu option for deleting data
B. A backup type for tables
C. A rule enforced at the database level to maintain data integrity
D. Only a naming convention for primary keys

Q12. What is a trigger in SQL Server?
A. A command for renaming columns
B. Special code that runs automatically when data is inserted, updated, or deleted
C. An index on a primary key
D. An alternate name for constraints

Q13. Indexes in SQL Server are important because:
A. They are the only way to backup tables
B. They improve database performance on searches, sorts, and joins
C. They limit the size of tables
D. They enforce data validation rules

Q14. What happens if you forget to index important fields, such as foreign keys, in SQL Server?
A. The server will refuse to save your tables
B. You will receive an error every time you use the table
C. Your database queries will run much slower
D. The data will be deleted automatically

Q15. How are rules (such as required fields) generally enforced in Microsoft Access compared to SQL Server?
A. In Access, with formal constraints only; in SQL Server, with front-end code
B. In Access, often enforced at the form/code level; in SQL Server, enforced by constraints at the data level
C. Both only rely on front-end code
D. Both rely on validation properties at the user interface only

Answers: 1-C; 2-C; 3-B; 4-B; 5-B; 6-C; 7-C; 8-C; 9-C; 10-B; 11-C; 12-B; 13-B; 14-C; 15-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from the SQL Server Learning Zone is lesson five, where we continue to build our customer table and explore which data types to use in SQL Server for things like numeric, currency, and date fields. This lesson is especially important if you are working with real data or planning to migrate information from Microsoft Access, as making the right choices about data types can make a huge difference down the line.

We finished covering text fields in the previous lesson, so now we need to consider number fields. For example, a field like FamilySize, which just needs to store whole numbers, is best set as an int. This matches up with the long integer in Access. In SQL Server, you decide whether or not to allow nulls based on your table design.

If you need to store floating point numbers, such as a DiscountRate, you'll want to use a float. In Access, you may be used to working mostly with long integers and doubles; in SQL Server, those generally correspond to int and float. Don't worry about the other types for now, with the exception of currency data.

When you need to store values like a CreditLimit, things get a bit more complex. For currency, the right choice is decimal rather than float. Specifically, I recommend using decimal with a precision and scale like 19,4. What does that mean? The precision (19) is the total number of digits stored, both before and after the decimal point. The scale (4) is how many digits come after the decimal. So a decimal(19,4) field mimics how Access handles currency and will give you enough precision to store very large monetary values with four decimal places.

Why not just use float for money values? Floats cannot represent decimal values exactly, since they are stored as approximate binary fractions. This can lead to rounding and calculation errors, an issue illustrated by the famous "Superman 3 / Office Space" problem where fractions of a cent go missing. Float is fine for things like measurements or percentages, but for money, always use decimal.

SQL Server also has a money data type, but despite its name, I still suggest using decimal. Decimal is the more standardized option and provides precise control over decimal places. With money, you're stuck with whatever formatting and behavior SQL Server decides, which may not always work the way you want, especially in calculations. Most importantly for our purposes, a decimal(19,4) field translates perfectly when bringing Access currency fields into SQL Server.

For date information like when a customer first joined, SQL Server offers several data types. If you only need the date with no time, you can use the date type. If your data includes times, or if you need better compatibility with Access, it's better to use datetime2. The number you put in parentheses (like datetime2(0)) specifies how many decimal digits to store for the seconds. If you do not need fractional seconds, just use (0). Datetime2 is recommended over the older datetime type, as it provides higher precision and is future proof. Again, if this is all new, make a cheat sheet to help keep track of which types to use.

For Boolean values like IsActive, SQL Server uses the bit data type. Access calls this yes/no, but here, bit is the standard. In SQL Server, bit fields store a value of 1 or 0. One thing to be aware of is that Access represents true as negative one and false as zero, based on old binary logic; SQL Server uses one for true and zero for false. Logically, they are equivalent, but you should be mindful of the difference, especially if you are writing code that checks for true or false values directly.

Now let's touch on some of the other features visible in SQL Server table design. The schema prefix (dbo, standing for database owner) simply means this table lives in the default schema. Unless you have a specific reason to do otherwise, you can safely ignore it for now.

Fields in SQL Server are referred to as columns, just like in Excel. You will see me use fields and columns interchangeably, so do not get confused by the terminology - it all means the same thing.

Each table can have one or more keys. The primary key, shown as PK_CustomerT, is an explicit database object called a constraint in SQL Server. In Access, keys feel like table properties; in SQL Server, they are more formal and enforced directly by the database engine. Foreign keys, which manage relationships between tables, are set up the same way, and you'll see them listed under each table.

SQL Server constraints go a lot further than what you get by default in Access. Not only do you have NOT NULL (like a required property), default values, primary key, and unique constraints, but you can also add check constraints to validate data - such as ensuring a family size stays within a reasonable range. The key point is that these constraints are enforced directly on the data, so they work no matter where data comes from - an application, an import, a script, or anything else. In Access, rules are often enforced only through front-end forms or VBA code, which can be bypassed if you are not careful.

Triggers are another powerful feature in SQL Server. Triggers automatically execute code when data in a table changes, such as after insert, update, or delete operations. While Access does support table-level macros with similar functionality, SQL Server's triggers are more robust and reliable, making them ideal for advanced business logic. We will look at triggers in more detail in future lessons.

Indexes make a big difference in performance. Your primary key automatically creates an index, but you can (and often should) create additional indexes on columns you search or sort by frequently, including date fields. When I moved my own website to SQL Server, performance suffered greatly until I realized I needed to add indexes for foreign key columns and other fields with heavy lookups. Indexes are one of the main ways to make your database run smoothly.

Statistics, which you will see at the bottom of the table design view, are SQL Server's way of tracking data distribution, which helps optimize queries behind the scenes. Beginners do not need to worry about statistics much, but they are a useful feature once you dive into advanced tuning.

Before we wrap up, I want to mention that the first level of my SQL Server for Access Users course is completely free, covering everything you need to get up and running. Level two is just a dollar, and level three is priced at $5.99. You can secure those prices early on my website. If you're interested in discounts for upcoming lessons, consider joining my Learning Connection, which gives you half off new course releases. I'll be adding new lessons regularly, so this is a great way to keep up without paying full price each time. Learning Connection members can also reach out to be added to the SQL Server course rotation.

If you want even more resources, the TechHelp memberships (Silver, Gold, and Platinum) give you access to extended cut videos for my Access material, and in the future, I'll be producing similar resources for SQL Server. Silver members can get a free beginner class every month, and as you advance, Gold membership unlocks free expert classes too.

In this lesson, we explored choosing the right data types for numbers, money, and dates - helping you design robust tables that store your data accurately. Feel free to post your comments with feedback or questions and let me know how you plan to use these tips in your own database.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Numeric data types in SQL Server

Choosing data type for family size field

Using int for whole number storage

Storing floating point numbers with float

Float vs decimal for numeric precision

Using decimal(19,4) for currency values

Precision and scale explanation for decimal

Compatibility of decimal(19,4) with Access currency

Problems with float for money values

Importance of using decimal instead of float for currency

SQL Server money data type vs decimal

Reasons to avoid SQL Server money type

Choosing datetime2(0) for date and time fields

Date-only vs datetime2 with time in SQL Server

Legacy datetime type vs datetime2

Setting fraction precision for datetime2

Boolean storage with bit type

Differences in Boolean logic between Access and SQL Server

Default values for bit fields

Understanding the dbo schema in SQL Server

How table schemas work in SQL Server

Viewing columns and data types in SQL Server tables

Primary keys as database objects in SQL Server

Foreign key constraints overview

Constraints for enforcing data integrity

Differences between Access and SQL Server data rules

Purpose and enforcement of constraints

Introduction to triggers in SQL Server

Difference between triggers and Access data macros

Indexes for performance in SQL Server

Importance of indexing foreign keys and date fields

Brief overview of statistics in SQL Server tables

Article

In this lesson, we will explore how to select the right data types for handling numeric, currency, and date information in SQL Server, particularly if you are coming from a Microsoft Access background. Understanding these data types is crucial if you want your database to store values like family size, credit limits, and dates accurately and work reliably with real-world data.

Let's start by looking at numeric data types. Suppose you have a field called family size. This is just a whole number, so the appropriate type to use in SQL Server is int. An int stores long integers, which is perfect for counts like family size or the number of items in stock. Allowing nulls in this field is up to you; it depends on whether you want to require this information for every record.

Now, if you want to store values that require decimals, like a discount rate or a measurement, you'll use float. In Microsoft Access, we talked about doubles, and float is the SQL Server equivalent. Float is ideal for approximate numeric values, such as percentages, rates, heights, or scientific measurements. However, do not use float for monetary values, as its floating-point representation can introduce subtle rounding errors due to the way binary floating-point works. For example, adding up lots of float values can lead to penny mismatches - something you absolutely cannot have in accounting data.

For currency, such as credit limits or prices, you should use the decimal data type. When defining a decimal field in SQL Server, you'll see two numbers to configure: precision and scale. For example, decimal(19,4) allows up to 19 total digits, of which 4 come after the decimal point. This is very close to how Microsoft Access stores currency values, making the transition between Access and SQL Server much smoother. With decimal(19,4), you can handle values with four digits after the decimal, and up to a number large enough for any typical business case. Use decimal instead of float for money to avoid rounding errors. Even though SQL Server includes a money data type, decimal is considered a more robust, standardized, and predictable choice. Decimal lets you control precision, and it translates more reliably to and from Microsoft Access currency values.

Speaking of dates, when you want to store the date a customer joined (for example, a customer-since field), SQL Server offers several date and time types. If you need just the date (with no time), you can use the date type. However, if your data from Access includes times, or if you want more control over the precision, use datetime2. The datetime2 type is a more modern alternative to the legacy datetime type, offering greater accuracy and flexibility. If you specify datetime2(0), you get date and time up to the seconds, which matches what Access typically uses. If you need millisecond precision, you can increase the number in parentheses; datetime2(3) would include milliseconds, and up to datetime2(7) is possible for nanosecond precision. Overall, datetime2 is the recommended choice over datetime or smalldatetime, especially for data migrations and new designs.

When storing Boolean or yes/no values (for example, isActive, to indicate if a customer is active), use the bit data type. A bit value is either zero (false) or one (true). You can set a default value for a bit field, such as one (active by default). Note that Microsoft Access stores true as -1 and false as 0, while SQL Server uses 1 for true and 0 for false. Despite this difference, both represent Boolean logic and behave as expected; just keep it in mind if you are checking Boolean values in VBA code.

You might notice that in SQL Server, table names often have a prefix like dbo. This is for the database schema - think of it as a folder or namespace in the database. By default, SQL Server assigns new tables to the dbo schema, so CustomerT becomes dbo.CustomerT. For most cases, you can just treat dbo as the default and not worry further until you get into more advanced database topics.

SQL Server structures its objects a bit differently than Access. In SQL Server, the columns (which Access refers to as fields) are visible in the database hierarchy, making it easy to see their types and properties. Additionally, keys - such as the primary key - are more explicit. Instead of being a property of a field or table, SQL Server treats keys as distinct database objects (constraints) that enforce rules like uniqueness or referential integrity.

In Access, many rules are enforced on the client side - using form properties, input masks, or event procedures. SQL Server encourages a much more rigorous approach by enforcing rules at the database level through constraints. These include NOT NULL constraints (required fields), default values, unique constraints, primary and foreign keys (for relationships), and check constraints for data validation (for example, restricting family size to a reasonable range). With these server-side rules, your data integrity is protected no matter where data comes from - forms, imports, scripts, or external applications.

Triggers in SQL Server perform actions automatically when data is inserted, updated, or deleted in a table. These are similar to Access Data Macros but significantly more powerful and reliable. Triggers ensure that business logic is enforced right at the data layer, which is especially valuable in multi-user or multi-application environments.

Indexing is critical for performance in any database, and SQL Server is no exception. Every primary key automatically creates an index to speed up lookups and joins, but you often need additional indexes, especially on fields you frequently search, sort, or join on - such as customer names or order dates. Indexing dramatically improves performance, transforming a sluggish database into a responsive one. Even fields like dates can benefit from being indexed if you are frequently sorting or filtering on them.

Lastly, statistics are another feature that SQL Server uses under the hood to optimize query performance. These track data patterns and distributions to help the engine choose the fastest query plan. As a beginner, you typically do not need to manage these directly, but they are vital for advanced optimization.

By choosing the correct data types and understanding how constraints, triggers, and indexes work together, you ensure your SQL Server tables are not only accurate and compatible with Microsoft Access, but also robust and high-performing for any workload.

If you want to keep building your skills, remember that consistency in your data types and structure will save you trouble later. Always think about the nature of your data (integer, decimal, currency, date, yes/no) and select the type that best represents it in your SQL Server tables. This foundation will make your database flexible, efficient, and ready for whatever applications you build on top of it.