Lesson 1. Database Basics
Before you learn about the T-SQL language, it is important that you understand the basics of server databases and the objects they contain. This lesson explains:
What a database is
What a table is
What database diagrams, views, stored procedures, user-defined functions, and triggers are
What Is a Database?
T-SQL is the language you use when working with Microsoft SQL Server. Microsoft SQL Server is considered a client-server database. It is used along with an application that presents the data. An example of such a database is Microsoft Access. A SQL Server database is a collection of objects. This collection of objects includes all of the tables, views, stored procedures, functions, and other objects necessary to build a database system. The tables, which are explained in the next section, in a database usually relate to each other.
What Is a Table?
Tables are generally the first thing you add to a SQL Server database. Tables contain the data in your SQL Server database. Each table contains information about a subject. For example, one table might contain information about customers, whereas another table might contain information about orders. Each table in a database must have a unique name.
A table is made up of rows and columns. The columns are called fields, and each field has a unique name. Each row contains an individual occurrence of the subject modeled by the table. Each field contains a specific piece of information about the item that is being described by the row. A row is most often referred to as a record. For example, within the Customer table, the fields included may be the CustomerID, CompanyName, ContactFirstName, ContactLastName, and so on, and each record in the Customer table contains information about a specific customer. Lesson 4, “Working with SQL Server Tables,” covers the details of creating and working with tables.
What Is a Database Diagram?
A database diagram graphically shows the structure of the database (see Figure 1.1), showing how one table is related to another within the database. Using a database diagram, you can modify the tables, columns, relationships, keys, indexes, and constraints that make up the database. For more about database diagrams, see Lesson 5, “Working with Table Relationships,” where you find out that database diagrams are very powerful!
What Is a View?
A view is a virtual table. Its contents are based on a query. Like a table, a view is composed of rows and columns. An example is a view that retrieves data from a customer table and from an order table. It retrieves information from each table for the customers residing in the state of Alaska.
Except in the case of a special type of view called an indexed view, views exist only in memory (their data is not stored on disk). The data in a view comes from one or more tables in the database (see Figure 1.2). It can also come from other views, and even from data in other databases. Whenever you reference a view, SQL Server dynamically retrieves the rows and columns contained in it. There are no restrictions on querying and only a few restrictions on modifying data via views. For more information about views, see Lesson 12, “Working with SQL Server Views.”
FIGURE 1.2 The View Designer included in SQL Server Management Studio helps you when designing a view.
What Is a Stored Procedure?
A stored procedure is a piece of programming code that can accept input parameters and can return one or more output parameters to the calling procedure or batch (see Figure 1.3). Stored procedures generally perform operations on the database, including the process of calling other stored procedures. An example is a stored procedure that updates the city of all customers residing in Westlake Village, changing the city to Venice Beach.
Stored procedures can also return status information to the calling procedure to indicate whether they succeeded or failed. Lesson 13, “Using T-SQL to Design SQL Server Stored Procedures,” Lesson 14, “Stored Procedure Techniques Every Developer Should Know,” and Lesson 15, “Power Stored Procedure Techniques,” cover stored procedures in quite a bit of detail.
What Is a User-Defined Function?
User-defined functions are procedures that accept parameters, perform some sort of action, and return the result of that action as a value (see Figure 1.4). The return value can be either a single value or a set of values. An example is a user-defined function that combines the contents of the first name, middle initial and last name fields, returning them as a single string.
FIGURE 1.4 User-defined functions are procedures that accept parameters, perform some sort of action, and return the result of that action as a value.
User-defined functions have many benefits. Like stored procedures, they allow modular programming, meaning that you can call them from anywhere in your program. If you need to modify the function, you can do so in only one place. User-defined functions generally execute quickly. For more about user-defined functions, see Lesson 17, “Building and Working with User-Defined Functions.”
What Is a Trigger?
A trigger is a special type of stored procedure that executes when data changes in a database table. There are three events that cause a trigger to execute. They include Insert, Update, and Delete statements that occur when data is modified in a table or view. Triggers have several uses. An example is a trigger that executes when the user tries to delete a customer. The trigger checks to see if the customer is active. If the customer is active, the trigger prohibits the user from deleting that customer. Lesson 18, “Creating and Working with Triggers” covers the intricacies of triggers.
Summary
A database is similar to a house. Without the proper foundation, it will fall apart. This lesson introduced the various system objects, providing you with a sense of what each object does and why it is important. You learned a little about tables, database diagrams, views, stored procedures, functions, and triggers, and the role that each plays in a SQL Server databases. Don’t be concerned if you are still unsure as to how you use each of these objects. We will spend quite a bit of time delving into each one in detail throughout the remaining lessons.
Q&A
Q. Describe the difference between a trigger and a standard stored procedure.
A. Triggers execute automatically in response to changes to data; you must explicitly invoke a stored procedure.
Q. What is a database diagram used for?
A. You use a database diagram to define and represent relationships between the tables in a database.
Workshop
Quiz
1. Stored procedures can have both input and output parameters (true/false).
2. You can usually update the results of a view (true/false).
3. How do we refer to a column in a table?
4. What is another name for a row in a table?
Quiz Answers
1. True.
2. True.
3. We refer to a column in a table as a field.
4. Another name for a row in a table is a record.
Activities
Each lesson in this book will provide you with an opportunity to practice the techniques that you have learned in the lesson. The section containing these exercises is referred to as “Activities.” Because you don’t have enough information yet to build tables or any of the other objects you learned about in the lesson, this lesson does not contain any activities.