Creating Your First Table in Microsoft SQL Server

After completing this chapter, you will be able to

• Develop a naming standard.
• Understand schemas.
• Understand the different SQL Server data types.
• Understand column properties.
• Create and alter tables.
• Understand computed columns.
• Add constraints to a table.
• Understand the FileTable feature.
• Create a database diagram.

Just as the database is the primary container of all objects on an instance of Microsoft SQL Server, the table is the primary container of all data on a SQL Server instance. Tables are the foundation of all objects, and without them a database is useless. The power in any application is the data that it accepts and stores. Without a relational database management system (RDBMS) to store and maintain that data, the application would likely not exist.

While this book’s primary focus is SQL Server, it should be noted that databases come in many shapes and forms. For example, the most widely used database is a Microsoft Excel spreadsheet. Many people extract data or request data from an RDBMS and import that data into Excel. Once the data is in Excel, the end user may create a series of spreadsheets and workbooks that together provide a very robust reporting tool containing answers to many organizational questions.

The downside of this approach is that those Excel spreadsheets and workbooks become data silos that are typically stored on users’ machines. If the spreadsheets and workbooks are not secured and backed up regularly, the information stored in them is vulnerable to a failure or catastrophe. In addition, the process to populate those spreadsheets and workbooks is often manual, and only one person understands how it works. Finally, by storing data in Excel, users are not able to realize the RDBMS benefits of multiuser concurrency and data integrity, which are the foundation of most database management systems.

The previously described downsides alone provide sufficient justification for using an RDBMS. Whether you are working with SQL Server or a similar system, most RDBMSs offer a way to centrally maintain and monitor access and availability to the data. Moreover, they provide governance on how the data is structured, organized, and delivered. These three key components are not typically available in something like an Excel spreadsheet. Using a robust RDBMS such as SQL Server provides administrators and developers with the ability to ensure that data is stored in a central location, and they can enforce naming standards and additional control that almost guarantee consistent and credible data across the organization.

In this chapter, you will first learn the importance of implementing and enforcing a naming standard. From there, you will be introduced to the various data types that are supported by SQL Server. Then you will create your first table using Microsoft SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL). Finally, you will use the same methods to add constraints and keys to your tables.

Developing a naming standard

The first step in any database design project is to develop a naming standard that will be used during the design process. While naming standard development is definitely not a requirement, continuing without some standard could yield an unorganized database that may present challenges to developers when accessing the data. Inconsistent naming conventions often inhibit the development process indirectly. For a developer who is writing T-SQL to modify or retrieve data, naming standards provide clear paths to constructing T-SQL statements. For example, assume that you are designing a database that will store human resources data. You are asked to create a structure that houses information about individual employees, such as their name, address, phone number, and department. Assume that you have designed the database shown in Figure 5-1.

Figure 5-1 This simple database schema does not have naming conventions.

The database schema in Figure 5-1 shows four tables. Notice that each table uses a different naming convention. The name of the table that will store address information is plural, and the name of the table that will store department information is prefixed with tbl. There are other inconsistencies, but you should get the picture. If you were a developer new to this database, writing T-SQL against this database could pose a challenge. Since the table names vary, a developer would have to spend a significant amount of time becoming familiar with the database prior to writing queries. You may have also noticed the inconsistencies in the column names, which further complicate working with this database.

Enforcing governance with regard to naming objects within a database makes the database easier to work with. The following are some best practices:

• General standards

  • Do not use spaces within any object or column name.

  • Underscore characters are acceptable, but be aware that they can present some challenges with visualization tools.

  • Use PascalCase, which means capitalizing the first letter of each word that is used to name an object or column.

  • Do not use reserved keywords. Plural table and column names are acceptable, but singular is preferred in this book. This is completely a matter of preference.

• Table naming standards

  • Names should reflect the contents of the table.

  • Names must be unique to the database and the schema.

• Column naming standards

  • Names should be unique to each table.

  • Names should reflect the business use.

  • Select the appropriate data type, as discussed later in this chapter.

Once this governance is put into place, the updated schema for the earlier sample database resembles Figure 5-2.

Figure 5-2 This database schema has naming conventions.

All the tables now have a common naming standard. Every new word begins with an uppercase letter, and the names are spelled completely. The main thing to notice is that each table name reflects the contents of the table.