Read an Excerpt
Chapter 1: Introduction to SQL Server
In the book's introduction we mentioned that this is an exciting time to begin working with SQL Server. This is true for many reasons, and just one of them is the provision of SQL Server's wizards, which offer invaluable assistance in performing complex administrative tasks. However, before we start to explore and use such wizards we need to begin with an introduction to SQL Server and some of the essential concepts of basic database theory.This chapter covers some of the core concepts of SQL Server, such as databases, tables, indexes, and stored procedures. Our discussion of these topics includes how SQL Server implements these objects as part of the database.
We will also study some basic concepts of relational databases, including database design and normalization. Both of these topics are very important to designing and creating a relational database that performs optimally in SQL Server. Even if you already know about these topics, it is worth glancing over them, just as a refresher.
To complete our introduction to the core concepts of SQL Server, we will look at how SQL Server fits into the Visual Basic architecture. This will include a quick introduction to data access technologies such as OLE DB, ODBC, and ADO, and how they relate to SQL Server.
This chapter will introduce you to the following topics:
- SQL Server databases
- Data files and transaction logs
- Tables, indexes, keys, and defaults
- Relational database design and normalization
- Stored procedures, triggers, and views
- OLE DB, ODBC, and ADO
Believe it or not, SQL Server has been around since version 1, but it only started gaining real popularity with version 4.21. This version ran on Windows NT 3.5 and started integrating the database with many of the features of the Windows NT operating system, such as asynchronous I/O and threading.
Asynchronous inputloutput (I/O) allows an application to send a request to a component and then continue processing other work. It communicates with the component later to see if the work has been completed. Using asynchronous I/0, SQL Server can maximize the work done by individual threads.
A thread is an individual task that runs independently of the rest of the program, and which gets its own CPU time. A thread belongs to the process that created it, and an application can create multiple threads. Suppose you have an application that displays a clock, with hours, minutes, and seconds. You can create a thread to execute the code that displays the time. This way you can display dialogs and wait for user input while the clock keeps on getting updated, because it is running on its own thread and is getting CPU time of its own.
The next major versions after 4.2 were 6.0 and 6.5. These releases of SQL Server introduced many new features that are found in SQL Server today, such as tighter integration with the Windows NT operating system, and support for multiple processors. Support for mail was also introduced, by allowing queries to send mail using the Mail Application Programming Interface (MAPI).
This latest version of SQL Server, 7.0, has gained widespread popularity- as the release of SQL Server that competes head to head with Oracle, Sybase, and DB2. Many analyses have been performed which suggest that SQL Server provides a total lower cost of ownership than Oracle.
SQL Server provides scalabilty, which means you can scale up from a small database running on a laptop computer to large databases running on multiprocessor clusters. Along with this salability is the fact that SQL Server is easily integrated with Microsoft Office 2000 running the new Microsoft Data Engine (MSDE). For example, if you create a database using Access 2000 and the new Microsoft Data Engine, your database is fully compatible with SQL Server.
SQL Server Databases
The purpose of a database is, as you are probably already aware, to store data. A database in SQL Server consists of multiple objects (and is in fact an object itself). These other objects include tables, keys, constraints, indexes, views, stored procedures, triggers, defaults, rules, and user-defined data types. We'll discuss these objects as we come across them throughout this chapter and the rest of the book.
A SQL Server installation consists of many databases, not all of which are user-defined. SQL Server itself uses databases to manage different objects, user-created databases, and SQL Server configuration options. When we install SQL Server in Chapter 2 we'll look at some of the databases that are automatically included.
All databases defined in SQL Server can be managed from one window within the Enterprise Manager, SQL Server's user interface. The Enterprise Manager will also be covered in detail in the next chapter, after we have installed SQL Server. From here you can view each database and its objects, and perform maintenance on the databases. You can also connect to other SQL Servers on your network and view the databases installed on them, as long as you have the necessary authorization.
The user who creates a database is considered the owner of that database. Only that user and the database administrator initially have access to that database. The owner of the database must grant permissions on the database to other users before they can access it. Then other users can create objects in the database, such as tables, stored procedures, and views, if they have been granted the appropriate permissions. We'll be looking at this more closely in Chapter 4 when we set up new users and grant them permissions to various objects.
Any application that accesses SQL Server does so through the use of T-SQL statements. These statements perform such actions as selecting, inserting, updating, and deleting data, and creating and modifying the structure of your database. When a set of SQL statements is run against the database this is known as a query. In Chapter 5 we'll be creating and running queries, and will learn more about the SQL language too.
SQL is an acronym for Structured Query Language and T-SQL is an acronym for Transact Structured Query Language. SQL complies with the American National Standards Institute ;ANSI) SQL standards, while T-SQL is Microsoft's version of SQL based on ANSI SQL. The latest version of ANSI SQL is referred to as SQL-92. We'll be using T-SQL extensively throughout this book and will explain more about how it is used as we come across it.
Database Files
A database in SQL Server is physically implemented as two or more files. The actual data of the database makes up one file called a data file, while the transaction log file for a database makes up the other file. Transaction logs will be covered in the next section, and basically contain a record of every transaction performed against the database. As your database grows, you can allocate other data files that can be used to cater for the expansion of your database. These files can reside on different volumes and are automatically managed by SQL Server.
When creating a data or log file for SQL Server, these files can be created on either FAT or NTFS file systems.
FAT stands for File Allocation Table and is a file system that has been around since DOS. NTFS stands for NT File System and is more robust than FAT but is only available on Windows NT and Windows 2000. NTFS also provides security anal recovery features that are not available in FAT.
SQL Server can work with and manage the database files in both file systems. Primary data files are the files created when you initially create your database. If you have to manually- expand the space your database needs, you create secondary data files. A database can have only one primary data file but can contain many secondary- data files. Primary data files for a database are assigned an . mdf extension, while secondary data files for a database are assigned an . ndf extension. Log files are assigned an extension of .ldf. You can use other extensions when creating databases, but use of these extensions helps to maintain consistency in your SQL Server installation, and enables quick identification of the database files.
Consider the following diagram. Database A contains a primary data file that has been populated to capacity, meaning that there is no more space available in this file. Therefore a second data file was created for this database and it has a file extension of . ndf, indicating that it is a secondary database file. Each database in the diagram has its own transaction log file, which is used to record the transactions that occur in the database. Database B in the diagram only contains a primary data file and a transaction log file...