Customer Table, Part 1
By Richard Rost

36 days ago

Lesson 4: Create CustomerT, Text Fields, Identity Key

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

We will start creating our first real customer table in SQL Server. I will show you how to set up an identity field for automatic numbering, define a primary key, and pick the appropriate data types for character fields like NVARCHAR. We will also cover the basics of naming conventions, when to allow null values, and compare some important differences between Microsoft Access and SQL Server. By the end of this lesson, you will have the foundation for building user tables in your database.

Navigation

Prev: Lesson 03
Next: Lesson 05

Keywords

SQL Server for Access, customer table, SQL Server, identity field, primary key, NVARCHAR, character data types, full text search, SQL Server Express, file tables, external tables, graph tables, naming conventions, field types, allow nulls, column properti

Comments for Customer Table, Part 1

Age	Subject	From
36 days	Primary Keys Nvarchar	James Nardone
36 days	Long Names FYI	Monica Jones
38 days	nvarchar	Alex Hedley
38 days	Access to SQL Server	William Dowler
38 days	SQL Tables	Juan Rivera

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

Intro

Transcript

In lesson four, we're going to start building our first real customer table in SQL Server. I'll show you how to set up an identity field, define your primary key, and choose the right character data types like NVARCHAR.

This is where we start laying the foundation for the rest of the database, so getting these core design pieces right now will make everything else a lot easier later on.

Before we get started with today's material, I just have a couple of quick notes from the first couple of lessons. I previously mentioned that I wasn't sure if full text indexing worked with Express. I looked it up and SQL Server Express does support full text search, specifically Express with Advanced Services, and in modern versions it's generally available. We'll talk about that in a later class.

On the other hand, I mentioned that database snapshots are kind of nifty, but Express does not support database snapshots, so we're not going to cover that right now.

Also, my good friend Sammy says that you can browse for a database, and I forgot to mention this before. I had to type in the name. When you go to connect to your database instance, if you go to File Connect, there's a Browse tab right here. If you're looking for a local one that's on this machine, just drop this down, and there it is. You can also browse your network. I don't have any on this network. This is an isolated network. If you've got two or three machines and your SQL Server is on a different one, just click the network one. As long as it's discoverable, we'll talk about that later, then it should show up, but I'm going to hit Cancel.

Also, I want to thank you. I've gotten tons of questions in my website forums, on YouTube, and by email. I'm actually going to take some time and add a lesson at the very end of each level and go over all the questions that have come in. That's one of the nice things about you watching this as I'm making the videos, because I can address questions as they come up.

Unlike some of my other classes, like my Microsoft Access course, that's 10 or 15 years old, some of the videos. That's why I do the Quick Queries, because I can't go back and add stuff to those videos. If you've got questions, post them down below, email them to me, send them in, and we'll try to get to them in class.

Today, we're going to start by building a table. We're going to build a customer table. For those of you who have watched a lot of my videos in the past, you'll be familiar with this guy. It's my TechHelp free template. I build almost all my TechHelp videos off of this simple database. In here, we've got a customer table. We're going to recreate this in SQL Server, most of it. We're not going to do every single field. We're going to do all the different field types, though. I can explain some of the differences between the way Access does things and the way that SQL Server does things.

When you open up the tables tree, there's a bunch of stuff in here. You've got system tables, file tables, external tables, and graph tables.

System tables are the tables that SQL Server uses internally to manage itself. Think of them like the Access system objects. Normally, don't touch these.

File tables are a special feature that lets SQL Server store files like documents, PDFs, images, in a way that also behaves like a table. Cool feature, but not necessary for beginners. We're going to talk about this later.

If you're coming from my Access course, one of the best habits you can bring with you is that you do not store files inside your database, unless you have a really good reason. In Access, people love using attachments or OLE objects to shove PDFs, Word documents, images, whatever, right into the ACCDB file. That is a great way to end up with a bloated, slow database that's a nightmare to back up and maintain.

With SQL Server, you do have more options, including storing file contents in the database using special fields, or using hybrid features like file tables that manage files on disk through SQL Server. Those features, in my opinion, add complexity and are usually overkill for typical business apps. For most projects, the best approach is the same one I teach in Access: save the files in a normal folder or cloud storage, wherever you want to put them, and store the file path or the URL in your table, along with any metadata that you might need. It's simpler, faster, and a whole lot easier to manage in the long term.

If you do have serious security, auditing, or compliance requirements, SQL Server can store the actual file inside the database using a binary field, or with more advanced features like file tables. It's a lot more complicated and it changes how you handle backups and database size, and we're not doing that in the beginner course, but I will cover those options later when we get to the developer lessons.

It looks like every time I drag my mouse off the screen, it pops up and it just stays on the last thing I hovered over. I'm actually using a remote desktop connection here because my SQL Server is on a different machine, and so that's why sometimes I move my mouse off the screen and it sits there. Ignore that. Pay no attention to the man behind the curtain.

What else have we got here? External tables. These are tables that don't physically store data inside your SQL Server database. They're basically links to outside data sources. Think linked table from Access, but enterprise style. Advanced feature, ignore for now.

Graph tables are special tables used for graph relationships, nodes and edges. More like a social network style data modeling. It's very niche, not needed here. But again, I like to explain stuff when you're looking at it so you're not going, what's this? What's that? I like to just tell you what the stuff is. We'll get it over with now.

For today, we only care about regular user tables like customers, orders, those kinds of things. Where are those? Well, there isn't a folder for them. All you do is right-click on tables and go to New, then Table. Don't worry about all the menu options. We'll go over them eventually.

Just like Access, we're going to type in our column name, our field name. Fields and columns, I used to yell at everybody, column columns, no, they're fields. Now they're back to columns again.

We'll start off with the same naming convention: CustomerID, no space there. If you've taken my Access course, you're already familiar with autonumbers. In Access, an autonumber is really just a long integer field behind the scenes that gets auto incremented.

In SQL Server, we accomplish the same thing by using an int field. So type in INT. If you drop this down, there's tons and tons of data types in here, a lot more than Access. We're only going to use some of them. Trust me, it's just like in Access. There are a lot of fields that I never even use.

For now, just follow along with what I'm going over here. We're going to use an int. In fact, a lot of these field types exist for backward compatibility with older versions of SQL Server, so we're not going to even use some of them. I'm going to focus on the handful that you actually need right now. It's kind of like in Access when you've got all those different number sizes. You've got byte, integer, single, double. I tell you to focus on just two: long integer for whole numbers and double for decimal values. That's it. So just like Access, in here, we're only going to use a very small set of data types.

You'll also notice there's "Allow Nulls" over here. This is pretty much the same as the required value in Access. Obviously, for the autonumber here for the CustomerID, we want to turn that off. We do not want null values for our primary key. Later on, certain fields might be required, like first name or last name. You might require those. For things like address, city, state, you can allow nulls.

Remember what I say in Access: no data is better than bad data. Don't force your users to have to type something in like a phone number. Later on, you can always generate a query that says, "Show me all the customers missing phone numbers." It's better than trying to find everybody with a phone number is 5551212 because that's what your employee typed in because he had no choice.

Now that we've defined CustomerID as an int, as an integer type field, the next step is to make it behave like an autonumber. In SQL Server, the autonumber equivalent is called an identity field. You can find that down here in the column properties. I'm going to grab this divider and slide it up a little bit so we can see more of these. There's a whole bunch of stuff down here again. Just like the field properties in Access, don't let it overwhelm you. There's a bunch of stuff in here we're never going to touch.

What you're looking for now is called Identity Specification right there. Just remember "identity," open that up, and we're going to set "Is Identity" to Yes. This tells SQL Server to automatically generate a new value for this field for each record, which is exactly what an autonumber does in Access. The identity increment, this thing here, that's how much it counts up each time. Just leave it at one, and the identity seed is the starting value, usually one as well.

People often ask, "Can I start the autonumbers at 5,000?" No need to worry about it. Autonumbers aren't for you; they're for internal use in the system. But here, yeah, if you want to increment it, you can, and they make it much easier than having to insert a thousand blank records and then deleting them.

This is important: the identity specification does not make this the primary key. It only makes the field auto increment, which is basically autonumber behavior. Primary key is a separate setting. You can find that right up here on the toolbar - there's "Set Primary Key" - or you can right-click on the field and go to "Set Primary Key," either one. It's like the little key symbol next to the field in Access. But unlike Access, SQL Server is not going to stop and say, "Hey, looks like you didn't set a primary key. Would you like to do that now?" SQL Server doesn't do that. It assumes you know what you're doing.

So remember, don't forget to set your primary key.

Let's put some more fields in here. Let's do first name: FirstName, no space. This is going to be NVARCHAR(50). Allow Nulls - that's up to you. I'll turn it off. I'll require first name and last name.

Let's talk about naming conventions first. If you've taken my Microsoft Access course, it absolutely carries over to SQL Server. Do not use spaces or weird characters in your table and field names. If you put a space in there - and SQL Server will let you do it - every time you use it in a query or whatever you've got to wrap it in square brackets and it gets old fast. Don't do it. Don't use dashes. Don't use underscores. Can you? Yes. Should you? No. Follow along with me. If you want to get fancy and do stuff on your own afterwards in your own database, great. But don't post questions in the forums with field names that look weird. Stick with my naming conventions. If you don't like them, if you want to use Hungarian notation or do something fancy, that's great. Do it on your own time. Follow my naming conventions, please.

It's not just a pain in queries, but also in views and joins and stored procedures and functions and triggers, basically anywhere you reference your fields. Do yourself a favor, stick with clean names: FirstName, LastName, CustomerID, Email.

While we're at it, let's try to avoid fields that are reserved words - Date, Name, User, those kinds of things, Year.

One more in here for now - Email. Notice the data type that it's suggesting by default for all of these is NCHAR(10). I'm going to explain what these are in just a moment. For now, type in NVARCHAR. For this one, we're going to do 255. This one we will allow nulls for.

What exactly is this nonsense here: SQL Server is recommending NCHAR(10). I made you type in NCHAR(50) or NVARCHAR(50), NVARCHAR(255). Let's talk about these.

You've got four basic types of data for text: CHAR and NCHAR, VARCHAR and NVARCHAR. As far as pronunciation goes, I don't want to hear about it. Everybody makes fun of me for pronouncing "array" as "array" or however. Leave me alone. This is "character," so I might call it "char," but sometimes I call it "car." You'll get it. Don't pick on the instructor, or you'll get the dunce cap and you'll go sit in the corner.

Now, what do these all mean? Well, there's fixed length and variable length, and then there's simple or Unicode.

Fixed length and variable length is easy. Fixed length means it's always this many characters. The database will always reserve that many characters. If you say CHAR(10), the database will always reserve 10 characters for that field, regardless of whether you use them or not. Old versions of Microsoft Access did that, and if you reserve 255 characters for a last name, you get a lot of wasted space. Access nowadays behaves more like VARCHAR - variable length. If you reserve 50 characters, it can be up to 50 characters, but if you only put six characters in that field, that's all the space that's actually physically used. So that's fixed versus variable.

Now, what's the other thing? Unicode.

Unicode basically supports all of those international and higher ASCII characters - things with accent signs and umlauts, the little A with the tilde over it, that kind of stuff. The simple ones are only the low ASCII characters, just the regular ABCDEFG, the stuff on the keyboard essentially.

What's the difference as far as internally goes? The simple set is half the size of the Unicode set because Unicode characters take two bytes per character. The simple ones only take one.

Now, if hard drives were expensive, like they used to be in the 1990s, this would be an issue. It's not an issue for us anymore today. So we are almost always going to use NVARCHAR.

You'll also see some older types like TEXT and NTEXT. Again, those are around for backward compatibility with older versions of SQL Server. Don't use them. We're always going to use this.

So for text fields in SQL Server, we're going to stick with NVARCHAR. It supports Unicode, which means it supports international characters like accents and umlauts, so names like Mueller and Garcia work perfectly. Since Microsoft Access already stores text as Unicode and since I've got a ton of international students, we're going to stick with NVARCHAR. Everybody stays compatible and it just works.

If you're building a database with tons of data and you really want to save space, you can switch, but I don't recommend it. Hard drive space is cheap nowadays, and unless you've got the Library of Alexandria that you're loading in, I wouldn't worry about it.

Because I know some of you are going to ask, NVARCHAR can go up to 4,000 characters. That's more than big enough for most fields. If you need more, there's NVARCHAR(MAX) - you type in an actual MAX in there. That's roughly the equivalent of a long text field in Access, formerly known as a memo field. This can be up to two gigabytes in size, which is pretty big. So basically one field in SQL Server can be the size of an entire Access database file. The whole ACCDB file is at a two gigabyte limit.

That brings us to notes. This will be NVARCHAR(MAX). That's a big field, and we do want to allow nulls for that.

Next question I always get: Why does everyone pick NVARCHAR(50) for names? 50 seems like the average number whenever I see it in books and stuff. A few reasons. First, it's a sensible limit. First names are almost never 255 characters long, and last names are never 100 characters long. So 50 covers most real world cases. Go with 100 if you're really worried about super long names, especially if you get some Spanish names like Jose Maria De La Concepcion Santiago Ramon. You've seen some pretty long names before, I'm sure. 100. I've never seen over 100 characters.

Limits do prevent garbage. If you leave it open, users will paste nonsense into fields like whole paragraphs or even other stuff.

SQL Server does care more about declared lengths than Access does. In SQL Server, the size you choose can affect indexing and how SQL Server estimates memory and performance in query plans. So oversizing every text field can hurt you in larger systems. That's why I usually say 50 characters for a first name or last name field, 100 to be extra safe. 255 is fine for an email. For notes, use MAX.

So unlike Access, don't use 255 for everything; pick a sensible max for all of your fields.

That gets us through the text types - lots of definitions. We're not just going to run through it fast. We still have to get to the numbers, integers and floats, decimals, dates and times, and how booleans work. We're going to get to all that in the next lesson.

Before we leave, let's go ahead and save what we have so far. I'm going to hit Control-S. We're going to save this and give it a name: CustomerT. If you haven't taken my Access class, I'll explain why in the next lesson. Just go with CustomerT, hit Enter, and that's going to save it in your list of tables here. You're going to see dbo.CustomerT. We'll talk about that in the next lesson.

So we started building our first customer table. We set up the identity field and the primary key. We learned why NVARCHAR is the go to for text fields.

Post a comment down below and let me know how you like today's video or if you have any questions. That's going to do it for lesson four. Hope you learned something. Live long and prosper, my friends. I'll see you in lesson five and we'll continue building this customer table.

Quiz

Q1. What is the SQL Server equivalent to an autonumber field in Microsoft Access?
A. Identity field
B. Sequence field
C. Uniqueidentifier
D. AutoIncrement

Q2. What should you remember to do after setting a field as an identity in SQL Server?
A. Set the field as primary key separately
B. Create a unique index
C. Change the field to VARCHAR
D. Set it as NULL

Q3. Which field type is recommended for storing most text data in SQL Server for maximum compatibility?
A. NVARCHAR
B. CHAR
C. VARCHAR
D. TEXT

Q4. What is the maximum length for an NVARCHAR data type before you must use NVARCHAR(MAX)?
A. 4000 characters
B. 255 characters
C. 1000 characters
D. 65535 characters

Q5. Why is NVARCHAR preferred over VARCHAR in most modern databases?
A. It supports Unicode characters
B. It has a smaller file size
C. It is faster to access
D. It is compatible only with SQL Server Express

Q6. What is the best practice for storing files like PDFs or images related to customers?
A. Store them outside the database and save the file path or URL
B. Always store the full file contents in a binary field
C. Use OLE objects in your SQL Server table
D. Use external tables to store files

Q7. When defining columns or fields in SQL Server, which of the following naming conventions should you follow based on the lesson?
A. Avoid spaces, underscores, and reserved words
B. Use spaces for readability
C. Start field names with numbers
D. Use dashes between words

Q8. What does 'Allow Nulls' setting in SQL Server correspond to in Microsoft Access?
A. Required property
B. Primary key setting
C. Default value
D. Indexed property

Q9. Why is it not recommended to oversize all text fields in SQL Server?
A. It can negatively affect indexing and performance
B. It always wastes disk space
C. SQL Server does not allow large fields
D. It prevents Unicode support

Q10. What are system tables in SQL Server?
A. Tables SQL Server uses internally to manage itself
B. User-created tables
C. Tables linked to external data
D. Tables for storing files

Q11. What is the primary difference between CHAR/NCHAR and VARCHAR/NVARCHAR data types?
A. Fixed length versus variable length
B. Numeric versus text data
C. Unicode versus ASCII support
D. Permanent versus temporary storage

Q12. When should you consider using NVARCHAR(MAX) for a field?
A. When the expected data may exceed 4000 characters
B. When the field will store only numbers
C. For all email fields
D. For indexing purposes

Q13. Which of these is NOT a recommended data type for storing modern text data in SQL Server?
A. NTEXT
B. NVARCHAR
C. NVARCHAR(MAX)
D. VARCHAR

Q14. What should you do if you have compliance or security requirements related to document storage?
A. Consider storing files in the database with a binary or use file tables
B. Do nothing, always store files externally
C. Use only CHAR fields for security
D. Store all files as plain text

Q15. What is the suggested maximum length for a FirstName field according to common conventions explained in the lesson?
A. 50
B. 255
C. 1000
D. 10

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

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 SQL Server Learning Zone is all about building our very first real customer table in SQL Server. In this lesson, I'm going to guide you through setting up an identity field, defining a primary key, and choosing appropriate character data types like NVARCHAR. This foundational work is crucial because it makes everything that follows in your database project much easier to manage.

Before we jump into the main lesson, I want to clarify a couple of points from previous lessons. I previously wasn't certain whether full text indexing was available in SQL Server Express. After looking into it, I found that SQL Server Express does support full text search, specifically the Express with Advanced Services edition, and in recent versions, full text search is generally available. We'll discuss this topic more thoroughly in a future lesson. However, regarding database snapshots, SQL Server Express does not support this feature, so we won't be addressing snapshots now.

Thanks to a suggestion from a fellow database enthusiast, I want to point out that when connecting to your database instance, you have the option to browse for your databases using the Browse tab after selecting File Connect. This is useful for finding both local and network databases, provided they're discoverable. If your SQL Server is running on another machine within your network, you can easily find it this way. For now, though, you don't need to worry about these settings.

I'm always grateful for the many questions and comments I receive through my website, YouTube, and email. Because of the volume of great questions, I plan to add a lesson at the end of each course level to address the most common ones. It's an advantage to following along with a current course, as I can adapt and address your questions in real time. Unlike my older video series, such as some of my Access classes that are over a decade old, I can't go back and add content to those, which is one of the reasons I created the Quick Queries series. If you have questions, please post them below or contact me and I'll do my best to fit the answers into each class.

Now, let's get started with building our customer table. Many of you will recognize this as the standard structure from my TechHelp free template, which is the basis for most of my TechHelp videos in Access. We're going to recreate a similar customer table in SQL Server, including all the essential field types but not every single field, so that I can explain the key differences between Access and SQL Server.

When you open the tables area in SQL Server, you'll notice system tables, file tables, external tables, and graph tables listed. System tables are used internally by SQL Server, similar to system objects in Access, and generally you shouldn't modify these. File tables allow SQL Server to store files such as documents and images within the database as table rows. While interesting, this feature adds unnecessary complexity for beginners, so we won't be using it now.

If you're familiar with my Access teachings, you know I advise against storing files directly within your database unless absolutely necessary. Access users often use attachments or OLE objects to store files in their databases, but this almost always leads to inflated, slow, and unwieldy databases. While SQL Server can work with files through special field types and file tables, my recommendation remains the same: keep your actual files in normal folders or cloud storage and simply store the file path or URL plus relevant metadata in your table. This approach is far more manageable in the long run.

However, there are cases where security, compliance, or auditing requirements mean you need to store files inside the database. SQL Server can do this using binary fields or advanced features like file tables. This adds complexity to your backups and database size, but for our beginner lessons, we're not covering those methods just yet. I'll revisit these options when we get into developer-focused topics later in the course.

External tables represent data that's actually stored outside your SQL Server database, serving as enterprise-level linked tables. For now, this is an advanced feature that we can safely ignore. Graph tables are for specialized graph data modeling - think social networks with nodes and edges. Again, this is niche and unnecessary for our project, but I like to briefly explain these concepts so you aren't left wondering what all these options are.

For now, our focus will be on user tables like customers and orders. You create these simply by right-clicking on tables and selecting to add a new table. There's no separate folder for user tables.

Just like in Access, you'll start by entering your column or field names. Whether you call them fields or columns doesn't matter for our purposes. I begin with CustomerID, without spaces. Access users know this as an autonumber - really just a long integer that auto-increments. In SQL Server, the equivalent is an int field, so we'll specify INT as the data type. There are many data types listed, but you'll rarely use most of them.

Focus on int for autonumber fields, as with Access where you typically use long integer for whole numbers. For now, stick with the field types I demonstrate - you'll find most types included for legacy support, but you only need a select handful for daily work.

Next, pay attention to the "Allow Nulls" option, which works like the Required property in Access. For the primary key CustomerID, we never allow nulls. Some fields, like first and last name, you may want to require. Others, like addresses or phone numbers, you can leave optional. My usual advice holds: it's better to have no data than unreliable data, and it's easy to find missing information later without having to work around artificial defaults.

Once you've set CustomerID as an int, the next thing is to make it an identity field, which is SQL Server's version of autonumber. The identity specification is located in the column properties; set the "Is Identity" flag to Yes. This tells SQL Server to auto-increment this field's value for every row, just like Autonumber in Access. The increment and seed let you control the counting, and while you can start at 1 or another value, autonumbers are meant for internal reference so most of the time you don't need to modify them.

Remember, setting the identity property does not automatically make the field into the primary key. These are separate steps. You need to set the primary key manually using the toolbar or context menu. SQL Server won't prompt you if you forget, unlike Access. Always remember to define your primary key.

Let's add more fields: FirstName (no spaces, as usual), with an NVARCHAR(50) data type. I recommend requiring both first and last names. It's important to stay consistent in your naming conventions: don't use spaces, dashes, underscores, or special characters in table or field names, even though SQL Server technically allows them. Clean names like FirstName, LastName, CustomerID, and Email will save you from a lot of unnecessary trouble later. If you want to use your own convention, that's fine, but I suggest you stick to these standards for this tutorial.

It's also wise to avoid using reserved words as field names, such as Date, Name, User, and Year.

Let's add Email next, with NVARCHAR(255). This field can allow nulls since not every customer will have an email address.

You may notice the default suggestion for new text fields is NCHAR(10). Let's discuss what this means. There are four main data types for storing text: CHAR, NCHAR, VARCHAR, and NVARCHAR. The difference boils down to fixed versus variable length and Unicode versus non-Unicode support.

Fixed length types, like CHAR(10), always reserve exactly the specified space, regardless of how much you actually use, which wastes storage when field values are short. Variable length types, like VARCHAR(50), only use as much space as needed for each value. Modern Access is more like VARCHAR now.

The second distinction is Unicode capability. NVARCHAR and NCHAR are Unicode, supporting international characters beyond standard ASCII, such as accents and umlauts. Non-Unicode (CHAR, VARCHAR) can only handle simple ASCII characters. Unicode types use twice as much storage per character, but with the low cost of storage today, the tradeoff is worth it for the benefit of international compatibility.

You might see older types like TEXT and NTEXT, but those are present mainly for legacy reasons. We will always use NVARCHAR for our text fields to take advantage of Unicode support, keeping everything compatible and ready for international input. NVARCHAR fields can be up to 4,000 characters per field, which is more than sufficient for most uses. If you need more, NVARCHAR(MAX) is your go-to - this is similar to the Long Text or Memo field in Access, allowing storage of large amounts of text, up to 2 gigabytes in size.

It's common to wonder why people tend to use NVARCHAR(50) for names. This value is conventional because first and last names are rarely longer than 50 characters, making it a sensible and low-risk limit. If you expect unusually long names, you might want to stretch it to 100. Email addresses can go to 255, and for notes or large text, use NVARCHAR(MAX). Setting reasonable size limits for each field helps preserve data quality by discouraging the entry of irrelevant or excessive information.

Unlike Access, where people often use 255 for everything, SQL Server's handling of field sizes can affect performance and indexing, so it's a good idea to pick appropriate maximum sizes for each field.

That covers text field types for now. In upcoming lessons, we'll be taking a thorough look at numeric data types (integers, floats, decimals), date and time handling, and how booleans work in SQL Server.

Before wrapping up for today, let's save what we've done so far. Save your table as CustomerT. If you're unfamiliar with this naming style, I'll explain my reasoning in the next lesson. Once you save it, you'll see dbo.CustomerT appear in your tables list, and we'll discuss what that means later.

So, in this lesson, we've started constructing our customer table, set up the identity field and primary key, and covered why NVARCHAR is the best choice for your text fields in SQL Server.

If you have any comments or questions, please let me know. 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

Creating a customer table in SQL Server
Setting up an identity field for auto-increment
Defining a primary key in a SQL Server table
Choosing appropriate character data types
Understanding NVARCHAR vs CHAR, VARCHAR, and NCHAR
Naming conventions for fields and tables
Configuring Allow Nulls on fields
Primary key vs identity specification differences
Recommended field lengths for names and emails
Best practices for storing file references versus files
Explanation of fixed length vs variable length text types
Unicode and ASCII text storage in SQL Server
When to use NVARCHAR(MAX)
Saving and naming a new table in SQL Server

Article

In this lesson, we're going to build our first real customer table in SQL Server. This process will introduce you to the essential building blocks of any relational database: identity fields, primary keys, and character data types like NVARCHAR. Laying a solid foundation with these concepts will make the rest of your database much easier to manage and extend.

Let's begin by understanding where your tables live in SQL Server. When you look at your database in SQL Server Management Studio, you'll notice a list of tables with several categories, such as system tables, file tables, external tables, and graph tables. System tables are for internal SQL Server use, similar to system objects in Access. You generally should not touch these.

File tables are advanced features that let you store files, images, or documents through SQL Server, behaving much like a normal table. However, for most beginner and business projects, it's much simpler and more efficient to keep your files in regular folders (or cloud storage) and simply store the file path or URL in your table. Storing large binary files inside your database makes backups and maintenance much more complicated. We'll cover more about file storage in developer-level lessons.

External tables let you connect to outside data sources, similar to linked tables in Access. Graph tables are meant for specialized data relationships, such as modeling social networks. For now, just focus on standard user tables.

To create your customer table, right-click the "Tables" section in your database and select "New Table." This opens the table designer, where you'll define each column (or field, if you're coming from Access). Enter the name of your first column: CustomerID. Do not use spaces or special characters in your field or table names, as that will force you to use annoying [square brackets] in every query and script. Stick to simple, readable names like CustomerID, FirstName, LastName.

For CustomerID, set the data type to INT. This is the SQL Server equivalent of Access's autonumber. Scroll down in the column properties to find "Identity Specification." Open it, and set "Is Identity" to "Yes." Leave the identity increment at 1 and the seed at 1 unless you have a special reason to start elsewhere. This makes SQL Server automatically generate a unique number for every new record, just like autonumber in Access.

However, making a field an identity does not automatically make it the primary key. You need to set this separately. Click on the row for CustomerID and click the "Set Primary Key" button in the toolbar, or right-click and select "Set Primary Key." The primary key uniquely identifies each record in the table and is used in relationships and indexing.

Next, add more fields to your table. For example, add FirstName and set its data type to NVARCHAR(50). Do the same for LastName. You should decide whether to allow nulls for each field. For critical data like first and last name, you might want to make them required (no nulls allowed). For other fields, like address or phone number, allowing nulls can be more user-friendly. Remember: no data is better than bad data. Don't force users to enter something meaningless just to satisfy a system requirement.

Let's discuss text data types in SQL Server. You'll encounter CHAR, NCHAR, VARCHAR, and NVARCHAR. CHAR and NCHAR are fixed-length types: they always reserve the specified number of characters, regardless of how much you actually store. VARCHAR and NVARCHAR are variable-length: they reserve only as much space as needed, up to the limit you set. The "N" versions (NCHAR, NVARCHAR) support Unicode, meaning they can store international characters and special symbols. The difference in storage is that Unicode takes two bytes per character, while non-Unicode types use only one. Hard drive space is no longer at a premium, so it's best to always use NVARCHAR for text fields, as it keeps your data compatible with modern applications and users across the globe.

When declaring the length for your NVARCHAR fields, choose sensible limits. Names generally don't need more than 50 or 100 characters. Emails can be safely stored in NVARCHAR(255). Set your limits according to real-world use, but don't oversize everything; overly large fields can affect indexing and query performance at scale.

If you have a field that needs to store a lot of text, like a notes field, use NVARCHAR(MAX). This allows up to two gigabytes of text, akin to the "long text" or "memo" datatype in Access. For most purposes, NVARCHAR(50), NVARCHAR(100), NVARCHAR(255), and NVARCHAR(MAX) will cover your needs for short, medium, long, and very long text respectively.

After defining several fields, save your table by pressing Control-S. Give it a name, such as CustomerT. If you are following a convention such as using a "T" suffix for tables, keep it consistent.

To sum up, you've created a basic customer table: added an identity field for automatic numbering, set a primary key, used the NVARCHAR data type for text fields to enable full international character support, and learned how to choose sensible field lengths. In the next steps, you'll add other important field types like numbers, decimals, dates, and boolean fields. But for now, you have a solid start to a well-structured customer table in SQL Server.