18 - Building Your SQL Server

• How to plan for installation - Get prepared to install SQL Server. Learn what hardware is required and plan how you will use the space on your disk drives.

• How to install SQL Server - Follow step-by-step procedures to install SQL Server on your server hardware. Learn about the various installation options available for character sets, sort orders, and security models.

• How to use SQL Enterprise Manager - Find out how the SQL Enterprise Manager can help you customize your installation. Learn how to create server groups and register your server for administration with SQL Enterprise Manager.

• How to set up user access and create databases - Learn how to create login IDs and usernames, and how access to database objects is managed. Find out how to allocate storage by creating database devices and how to create databases.

In the preceding chapter, you learned about SQL Server, relational database management systems (RDBMS), the role of the database administrator (DBA), designing databases, and the tools provided to manage SQL Server. This background information forms an important foundation as you install SQL Server, create databases, and begin to use SQL Server as an important component for building client-server applications.

You are now ready to make specific plans for your server. The first two sections in this chapter provide some guidance on selecting hardware appropriate for use as a server and how SQL Server allocates and uses disk storage. Then you learn about the installation options you must decide upon for your installation to be a success. This discussion is followed by procedures for installing SQL Server and using the SQL Enterprise Manager to create login IDs and usernames. Finally, you learn how to define devices, databases, and various database objects.

You should spend some time designing your first database, following the guidelines from Chapter 17, before you actually create it on the server. Even database designers with years of experience don't always foresee all eventualities, and changes will undoubtedly need to be made on occasion. The design process is still an important part of database administration, however, and it should be approached with a professional attitude.

Sizing Your Server

• The nature of the application

• Expansion of the scope of the project

• Unforeseen circumstances

Evaluating the Nature of Applications

Allowing for Expansion and Unforeseen Circumstances

• A table being added or deleted

• A column being added to or deleted from a table

• A column size (or even a column datatype) changing

• Discovering the need for another index

• A revision to the estimated volume of data

• A change in the scope of the project

Planning Disk Space Usage

Devices

Devices used for databases and transaction logs are generally referred to as database devices. Devices used to store backups are referred to as dump devices. Database devices are used to store the data, indexes, and transaction logs.

Database Devices

These devices are used to store the data and indexes. When a device is created, you are asked to supply a logical name and a physical name. The logical name is the name by which the device will be identified within SQL Server. The physical name is the operating system file specification where the device will be stored.

Every time activity occurs in the database, an entry is made in the transaction log. By using a transaction log, SQL Server ensures that either an entire transaction is applied to the database, or none of it is applied. The database is never left in an undefined intermediate state due to a partial transaction being applied and then terminated due to some system failure.

Databases and their transaction logs should be stored on separate devices. This increases performance and provides for easier administration. Also, if the log device is on a separate drive than the data device, you lessen the risk of losing data. If the data device fails, you can restore the database from the most recent backup (via the LOAD command), and update it with the current transaction log to bring it back to its state at the time that the device failed. If the log device fails, you can immediately DUMP the database to create a fresh backup and reestablish the transaction log.

Dump Devices

Dump devices are used to store backups of databases and transaction logs. The importance of dump devices cannot be overstated. It is critical that the DBA define and execute regularly scheduled backups of all databases for which he or she is responsible. If the dump device is created on a tape drive, the physical name must be the Windows NT name for the tape drive.

Two default diskette dump devices are created at installation: DISKETTEDUMPA and DISKETTEDUMPB. These provide backward compatibility to earlier versions of SQL Server. With databases becoming larger and larger and tape drives becoming more prominent, it is difficult to justify backing up databases on diskettes.

Segments

A segment can best be thought of as a piece of a database device on which tables and/or indexes can be explicitly placed. Segments must be built on devices and must be used specifically by one database. This allows you to place a database on more than one database device (up to a maximum of 32 segments). The ALTER DATABASE command allows you to expand a database from one device to a segment on another device. The segment on the subsequent device can then be identified when creating tables and/or indexes. If an index already exists, it must be dropped and re-created on the new segment for the entire index to be placed on the new segment.

Even though it is possible to identify multiple segments on one device, it is not recommended because objects placed on the segments would then compete for space. This would negate the performance gain sought by the use of segments.

Some uses for segments include separating a table from its index by placing segments on two separate devices and putting a frequently accessed table on more than one device to spread the work around.

Databases

SQL Server uses the following three units of data storage:

• Page (2K, or 2,048 bytes)

• Extent (8 pages)

• Allocation unit (32 Extents)

The smallest amount of space that can be allocated to a database is 1 allocation unit (256 contiguous 2K pages, or 0.5M). However, when you create devices and databases with the Enterprise Manager (explained later in this chapter), you will be asked to specify the number of megabytes. You will not be able to enter fractional numbers.

Even though the smallest fragment of space must be at least one allocation unit, SQL Server uses the paging technique to store data. It puts as many data rows from a table (or index rows from an index) onto a page as it can. If an additional row is too large to fit on the remainder of the page, it must be stored on another page.

Each page requires 32 bytes of overhead, which means that only 2,016 bytes are available in each page. Also, one page out of every allocation unit is reserved for use by SQL Server. That page contains information about how data is stored on the other 255 pages. Some overhead is required for various elements to be stored; this overhead varies depending on the type of elements.

Given certain assumptions, a good start for estimating the space required for a SQL Server database can be accomplished by following these steps:

1. Establish the data model. The data model is the end product of an analysis of the application's data requirements. This includes defining the tables to be used, columns in each table, relationships among the tables, and any indexes that should be built.

2. Determine the size (in bytes) of a row in each table.

3. Multiply each row size by the estimated number of rows for that table.

4. Determine the size (in bytes) of a row in each index.

5. Multiply each size by the estimated number of rows for that index.

6. Sum the products.

This gives you a starting point on which to base further calculations and against which to measure future changes. There are formulae in Appendix B of the Administrator's Companion that can be used to specify exactly the number of data rows and index rows that can be stored on a page. For these formulae, you need to know the number and data types of the columns in the table, as well as the estimated number of records. Because the data model can change many times, especially in the early stages of development, these calculations may need to be updated frequently.

Installing SQL Server

The machine that you select for your server should be listed on the Hardware Compatibility List (HCL) for Windows NT Server, which is available in the Windows NT Server box and also updated regularly on CompuServe and Microsoft's World Wide Web server on the Internet. You should have already installed Windows NT Server. As a rule of thumb, the server should not be a Primary Domain Controller (PDC) or Backup Domain Controller (BDC), especially on an active network with many users. These types of servers already have a significant load placed on them validating logons. There are exceptions of course. In small networks with only ten or twenty users, and perhaps only one server, SQL Server can be installed on the PDC as long as it is sufficiently powerful and has enough RAM. See "Sizing Your Server" earlier in the chapter for more details.

Before you launch the setup program, review the next four sections for background information on the choices you will make during setup. Some of these selections cannot be changed after setup is complete without a significant effort.

The choices you make for sort order and character set are very important for one simple reason - if you choose incorrectly, you must reinstall SQL Server or rebuild the master database to change them. If you have already created databases and entered data, you must rebuild the master database, re-create all other databases, and then reload and rebuild them to reflect the new settings.

Sort Order

There is no right or wrong sort order. For different applications and environments, each of the possible selections can be a sensible choice. Ideally, the sort order is selected to reflect the needs of the applications that will be used and the expectations of users running the system. For example, most users do not expect upper- and lowercase to have an impact on the results returned by a query. Therefore, a case-insensitive sort order is usually appropriate. There are applications, however, that must distinguish between upper- and lowercase characters when used in ID fields, for example. In these situations, you may need to use a case-sensitive order and develop application program logic to insulate users from this distinction, while still providing the expected results from queries.

If you are installing a single SQL Server to be used in an isolated environment, you only need to make a selection that provides appropriate results for your applications. If you are adding a SQL Server to an existing computing environment that already contains other database servers, it is important that you consult with the DBAs for those systems. If there is any chance that you may need to interchange data, you should make every effort to use the same sort order and character set to avoid data conversion problems when transferring data.

The default sort order changed from version 4.2 to version 6.0. The version 4.2 default was binary sort order, which is generally the fastest. This order does not generate results that match the order you would find in a typical dictionary (dictionary order), which can lead to some confusion. The default for version 6.0 is dictionary order, case-insensitive, which is the most intuitive order for users.

Character Set

All character sets use the same characters for the first 128 characters. The differences appear in the second 128 characters, which are used primarily for foreign language support. Many of these characters include diacritical marks. If you are using SQL Server only in English-speaking environments, or if you have only one SQL Server to set up, the character set you select is probably unimportant.

The same character set must be used on both clients and servers for data to be properly displayed. Windows NT clients include an International application icon in the Control Panel that allows you to set the character set for that computer.

Some applications, especially older MS-DOS applications, use extended characters for graphics. These applications are generally written expecting Code Page 437 to be used as the character set. Change the character set used on a computer running Windows NT Workstation and then run the application to see if it displays characters properly. If not, you will need to use Code Page 437 to support the application.

If you must exchange data with other SQL Servers, it is critical that you use the same character set. Data entered with another character set will be incorrectly displayed, which will confuse users and might lead to serious errors.

The default character set changed from version 4.2 to version 6.0. The version 4.2 default was Code Page 850. The default for version 6.0 is ISO 8859-1, which is used as a default by Sybase SQL Servers running on UNIX and VMS. The new default reflects the growing need for Microsoft SQL Servers to interoperate with other database servers in enterprise computing environments.

Choosing a Security Model

Standard

The standard security model implements an entirely separate user account database specifically for SQL Server. This is the default security model. SQL Server users and groups are created using the SQL Enterprise Manager or system stored procedures. These accounts can then be assigned as users of particular databases and granted specific permissions within those databases.

Integrated

The integrated security model uses the accounts and groups defined in a Windows NT domain for SQL Server as well. Users and groups are created using the User Manager for Domains tool provided with Windows NT Server. They can then be assigned the capability to use SQL Server using the SQL Security Manager and given specific database access and permissions using the SQL Enterprise Manager or system procedures.

Mixed

The mixed security model allows the DBA to use accounts of both types - integrated and mixed. Windows NT accounts and groups can be assigned to a SQL Server login ID using the SQL Security Manager. Each user may be assigned to a separate login ID, or a collection of users or a group can be assigned to a single login ID. In addition, standard model login IDs with no relationship to a Windows NT account can be created and managed.

Selecting Protocols for Client-Server Computing

The network libraries are referred to collectively as Net-Libraries. When implemented, they take the form of dynamic link libraries (DLLs), which are installed on the computers that use them. For example, SSMSRPCN.DLL is the name of the DLL that contains the Microsoft Multi-Protocol RPC Net-Library. DLLs are a widely used mechanism for implementing capabilities for both operating systems and applications. They are an ideal mechanism for implementing Net-Libraries.

It is possible, and even common, to configure a SQL Server to support more than one Net-Library. Servers generally have plenty of processing power to handle multiple network libraries and transport protocols. This makes sense because it allows a single server the capability to support clients with different requirements. Most clients, however, are configured to use one transport protocol and a single Net-Library to conserve the use of client resources.

See "Understanding Network Protocols," (Ch. 4)

The connections between a server and various clients can be characterized as either a trusted connection or a nontrusted connection. Some network protocols support authenticated connections between clients and servers. In other words, mechanisms are built into the networking protocols to ensure that a client connection is authentic - the client is who he claims to be. Other network protocols do not have authentication mechanisms and must rely on higher level protocols to provide authentication services. For example, clients using the multiprotocol and named pipes Net-Libraries can establish trusted connections. A Novell NetWare client using the Sockets Net-Library would establish a non-trusted connection.

The issue of trusted and nontrusted connections is related to the security model you select. Nontrusted connections must use SQL Server's login validation since the connection itself is nontrusted. Therefore, clients using nontrusted connections must use standard security or the standard mode of mixed security. If you want to use integrated security, or the integrated mode of mixed security, you must use a network protocol that supports trusted connections.

Each of the supported Net-Libraries has its own characteristics that make it an appropriate selection for particular environments. Some client configurations limit the possible Net-Library selections you can make. The characteristics of the various Net-Libraries, and suggestions for when they are appropriate, are outlined in the following sections.

Named Pipes

The Named Pipes Net-Library has been available since the very first version of SQL Server was offered by Microsoft. It is still available and is the default IPC protocol installed by the SQL Server setup program. Named pipes support is available for all Windows platforms and MS-DOS. A pair of test utilities, MAKEPIPE and READPIPE, are available to test the function of a named pipes connection. Named pipes can be used over all Microsoft transport stacks including NetBEUI, NWLink IPX/SPX, and TCP/IP. Named pipes cannot be installed and used over native Novell transports. The use of named pipes creates a trusted connection that can be used with any security model.

TCP/IP Sockets

Support for sockets clients extends the reach of SQL Server support to a wide range of TCP/IP transport protocols available from various vendors. The popularity and use of TCP/IP stacks have grown dramatically over the past few years. A standard was created for a Windows-based sockets interface, which has allowed software companies to create utilities and applications that exist at the upper layers of the OSI seven-layer model. These applications can use the sockets interface to request network transport services. Microsoft has implemented its upper-layer SQL Server client support to operate in this environment. TCP/IP sockets clients create nontrusted connections to SQL Servers. Support is available for all Windows platforms, but not for MS-DOS.

NWLink IPX/SPX

To support SQL Server on networks with clients using native Novell IPX/SPX stacks, Microsoft added the NWLink IPX/SPX Net-Library. This Net-Library uses an SPX interface to request transport services from IPX stacks in a manner analogous to the use of a sockets interface for TCP/IP. IPX/SPX Net-Library clients create nontrusted connections to SQL Server. Support is a available for all Windows platform and MS-DOS.

Banyan VINES

This Net-Library supports SQL Server use in environments using Banyan VINES as a network operating system (NOS). Use of this Net-Library creates a nontrusted connection to SQL Server. Support is available for all Windows platforms and MS-DOS.

Multiprotocol RPC

The Multiprotocol RPC Net-Library is the newest IPC protocol and is also the one that Microsoft recommends for most situations. RPC stands for Remote Procedure Call, and this general purpose IPC mechanism is used widely in Windows NT environments. RPCs are used, for example, to allow Windows NT administrative utilities such as Server Manager to connect to remote Windows NT servers.

This Net-Library is a little different from the others. It actually runs on "top" of named pipes, IPX/SPX, or TCP/IP sockets - hence the name multiprotocol. Use of the Multiprotocol RPC Net-Library allows the creation of trusted connections over TCP/IP and IPX/SPX (as well as named pipes). This Net-Library is available for all Windows platforms, but not for MS-DOS.

An important new feature available only with this Net-Library is the capability to encrypt information before it is transmitted over the network and then decrypt it at the other end of the connection. Although this introduces additional processing overhead and therefore slows down response time somewhat, this is a critical need if you have stringent security requirements. In today's network environment, you should generally assume that a perpetrator can capture all network traffic for analysis and reconstruction unless you have gone to extraordinary lengths to physically secure your premises.

Running the SQL Server Setup Program

It is a good idea to set up a domain account for use by the SQL Executive service. If you are using a master domain model, the account should usually be a member of the master domain. The account must be a member of the Administrators local group on the proposed server and should have Log on As a Service rights. It is also a good idea to set the Never Expire option for the account.

If the computer you will use as your server has a CD-ROM drive, you can install your server directly from the CD. If it does not, you can copy the contents of the CD onto a shared network directory using a computer that does have a CD-ROM drive, or simply share the CD.

See "Using Service Accounts," (Ch. 4)

To install SQL Server, follow these steps:

1. Insert the SQL Server CD into the CD-ROM drive on the server or connect to the shared location of the installation files. Change to the directory that matches the type of computer you have - Intel, MIPs, or Alpha AXP. Run SETUP.EXE.

2. Select the option to install SQL Server and Utilities. Follow the instructions provided, and complete the dialog boxes by selecting options for sort order, character set, and security model as discussed earlier. On-line help is available as you go through the process either by clicking Help buttons on dialog boxes or by pressing the F1 function key.

3. The setup process should generally take less than 30 minutes.

Using the SQL Enterprise Manager to Build Your Server

For these operations you should use an account with system administrator (SA) privileges. You can use the SA account itself, but it is better to create a group called SQLAdmins (or something similar) and assign SA privileges to the group. Then you can control who is an administrator by assigning or revoking membership in the group. For more information, see "What Is a Group?" later in the chapter.

Starting the SQL Enterprise Manager

1. Open the Program Manager. Find the SQL Server program group.

2. Double-click the SQL Enterprise Manager icon.

3. The first time you run SQL Enterprise Manager, the Register Server dialog box appears. You need to create at least one server group and register your server as a member of a server group. See the next section for details on how to complete this process.

An "Unable to Connect to Server" message appears when trying to register your server. Make sure that the computer you are using is running at least one network protocol in common with the SQL Server. If you are using TCP/IP, try to PING the SQL Server to check basic connectivity.

Creating Server Groups and Registering Servers

To create a server group and register your server, follow these steps:

1. Start the SQL Enterprise Manager.

2. Open the Register Server dialog box by choosing Server, Register Server from menu or by clicking the Register Server button on the toolbar. The Register Server dialog box appears, as shown in figure 18.1.

   Fig. 18.1 - Enter a server's name and an administrator's ID and password in the Register Server dialog box so that the SQL Enterprise Manager can be used to manage the server.

3. Enter the name of the SQL Server you want to register in the Server box. Alternatively, you can click the List Servers button. When the Select Server dialog box appears, select the server you want to register (see fig. 18.2).

   Fig. 18.2 - The Select Server dialog box is used to choose a server from the list of available servers. If your server does not appear in the list, enter its name manually.

4. In the Login Information box, select the type of connection you want to establish when you use SQL Enterprise Manager to connect to this server. If the server uses the standard security model, select Use Standard Security. This establishes a nontrusted connection to SQL Server and relies on SQL Server security to verify privileges. You should also select this option if you want to use the standard mode of mixed security. If you are using the integrated or mixed security model and want to use a trusted connection to SQL Server, click Use Trusted Connection. See "Choosing a Security Model " earlier in the chapter for more details.

5. If you chose standard security, enter the login ID and password. If you are using a trusted connection, your Windows NT user account will be used to validate your access and you need not enter an ID and password here.

6. There is a check box at the bottom of the Register Server dialog box for Display Server Status in Server Manager. If this box is selected (the default), SQL Enterprise Manager displays the status of SQL Server using a stoplight icon in the Server Manager window. This indicates whether SQL Server is started, stopped, or paused and provides a convenient way to view the status of your SQL Servers at a glance.

7. It is now time to select a server group. You may use the default group that is already created (SQL 6.0), or you can create your own group(s) to reflect any organizational plan you want. If you don't want to create a new server group, skip ahead to step 9. To create a server group, click the Servers button. The Manage Server Groups dialog box appears, shown in figure 18.3.

   Fig. 18.3 - The Manage Server Groups dialog box is used to create server groups. These can be used by administrators to organize the collection of servers for which they have administrative responsibilities.

8. Enter the name of the new server group you want to create in the Name text box. You can create a hierarchy of groups, much like a subdirectory hierarchy on a hard disk. Select either the Top Level Group or Sub Group Of option button. If you chose Sub Group Of, you must select a parent group that will contain this server group in the server group hierarchy displayed. You need not select a parent group if you are creating a top level group. Click Add, and your new server group will be displayed in the hierarchy. Then click Close to return to the Register Server dialog box.

9. Click the Register button. Your SQL Server is now registered as a member of the server group you selected and can be managed from this computer using SQL Enterprise Manager. Repeat steps 3 through 8 for each server you want to manage. When you are finished registering servers, click Close to close the Register Server dialog box.

When changing a server registration the Register button cannot be seen. If you are changing a server registration, the Register button becomes the Modify button. Clicking Modify will change your original server registration.

A Quick Tour of the SQL Enterprise Manager

Fig. 18.4 - The SQL Enterprise Manager with the Server Manager window open.

When you use SQL Enterprise Manager, you can either use the menus or toolbar buttons. ToolTips are available for the toolbar buttons. If you position the mouse cursor over a toolbar button and pause momentarily, a small box appears near the mouse cursor identifying the toolbar button's function.

Two main windows can be opened in SQL Enterprise Manager: the Server Manager window and the Replication Topology window. The Server Manager window is displayed by default whenever SQL Enterprise Manager is started. The Replication Topology window can be opened by clicking the Replication Topology toolbar button, or by choosing Server, Replication Configuration from the menu. As you execute various tasks, a variety of dialog boxes appear.

By making selections and entering information in the various dialog boxes, you can perform many of the same actions that used to require entering commands in Transact-SQL or using system stored procedures. Of course, you can still use commands to complete these tasks. Using the isql or ISQL/w utilities, you can enter individual commands or run scripts containing sequences of commands that have been saved in a file. You may even prefer this option if you must perform an operation repeatedly on many different servers.

A drop-down list box at the top of the Server Manager window allows you to select the server on which you want to focus. Alternatively, you can simply double-click a server to connect to the server and display information about the various objects defined on the server. At the right of the Server drop-down list box is a button which that displays a legend describing the various icons and symbols used in the Server Manager window. The Server Legend window is shown in figure 18.5.

Fig. 18.5 - The Server Legend window provides a convenient reference for all the icons and symbols used in the SQL Enterprise Manager's various displays.

In addition to making selections from menus or using toolbar buttons, you can also use the right mouse button to display context-sensitive menus for many objects in the Server Manager window and the Replication Topology window. By positioning the mouse pointer over an object and clicking the right mouse button, you can initiate many tasks such as creating or deleting an object or editing its structure. The SQL Enterprise Manager will be used extensively throughout the rest of the SQL Server chapters to illustrate how to perform various management functions.

See "isql and ISQL/w," (Ch. 17)

See "Using SQL Scripts and Stored Procedures for Maintenance Tasks," (Ch. 19)

Creating Login IDs and Usernames

The way in which users gain access to SQL Server is affected by the security model you select when installing SQL Server. If you select integrated security, the domain accounts already defined for use by Windows NT Server are used for SQL Server access as well. A trusted connection is made between the client workstation and the server. The SQL Security Manager allows you to manage accounts using integrated security. Standard security implies a completely separate set of user accounts, and the connections between client workstations and the server are nontrusted connections. Mixed security allows you to use either integrated or standard security. See "Choosing a Security Model" earlier in the chapter for more information on this topic.

Understanding Users, Usernames, and Login IDs

The procedures for creating each of these entities are given as they are defined, but you may want to read this entire section before actually creating new login IDs or usernames.

SQL Server uses the term login rather than Windows NT's logon. Do not let this slight difference confuse you. SQL Server uses the term login for historical reasons. The meaning is the same.

What Is a Login ID?

Anyone who wants to access information in a SQL Server database must have a login ID. When a user (an actual person) connects to SQL Server using an application program or administrative utility, a login process occurs. If a nontrusted connection is made, the user is prompted to enter a login ID and corresponding password. If a trusted connection is made, the login is implicit using the account that was mapped to the user's Windows NT account with the SQL Security Manager, or the default login ID if no mapping was created. If a default login ID has not been established, the user is denied access to the server. In other words, even in situations in which the user is not prompted to enter an ID and password, a specific login ID is being used, and a security context for the user is established or denied.

A valid login ID and password allows a user to connect to a server, but does not provide access to any of the databases defined on that server. To use one or more databases, the login ID must be associated with a username, and the username must be assigned permissions for specific databases and database objects.

Often, the username is identical to the login ID. This is another potential source of confusion, so it is important to be accurate. Even if the two match, a login ID of Sam, for example, is a different entity from the username Sam.

The term login ID is often shortened to login.

To create a new login ID, follow this procedure:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Click the Manage Logins toolbar button or choose Manage, Logins from the menu. The Manage Logins dialog box appears (see fig. 18.6).

   Fig. 18.6 - The Manage Logins dialog box is used to create login names, assign passwords, and grant database access.

4. Click the drop-down button to the right of the text box and select New Login from the list. The text box will clear, and you can enter a new login ID name.

5. Enter a password for the login ID. Users who will be connected to SQL Server with the default login will need to know this password.

6. Select a default language for this login in the Default Language drop-down list box.

7. In the Database Access box, you can make multiple selections as appropriate. You need not make any assignments now if you just want to create the login ID. All other selections can be made later.

   Select the Permit box for each database you want the default login to be permitted to use. Click the Default box for the one database that will become the current database when this login is used. The username this login will use within a database is listed in the User column. The default username is the same as the login ID. You can change this if you want by typing another name in the box. Alternatively, you can select the Alias box for a database and select a username that has already been defined to be used as an alias for this login ID. A login may be assigned only one username (or alias) per database. Finally, you can select one group in the Group box per database. This group is in addition to the public group that contains all users.

   
   It is a good idea to give all logins a default database other than master. This discourages users from creating objects in the master database, which is generally a bad practice.
   

8. Click Add. The confirm password dialog box appears. Carefully reenter the password to confirm it and click OK.

9. Repeat steps 4 through 8 if you want to add another login. Click Close to close the Manage Logins dialog box.

What Is a Username?

A username is used to assign a login ID access rights to a database and objects within the database. A particular login ID can be assigned to only one username for a given database, but more than one login ID can be assigned to the same username. For example, if the login ID Sam is assigned to the username Sam for the PUBS database, the Sam login ID cannot also be assigned to the Fred username in PUBS. Another login ID, Sue for example, could be assigned to the Sam username in PUBS. The username Sam would then be an alias for the login ID Sue in the PUBS database.

To create a new username for a database, follow this procedure:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage and open the Databases folder. The databases on the server are listed. Select the database for which you want to create a username.

4. Choose Manage, Users from the menu. The Manage Users dialog box appears, as shown in figure 18.7.

   Fig. 18.7 - The Manage Users dialog box is used to assign SQL Server usernames to login IDs for access to a database.

5. Enter a new username in the User Name drop-down combo box. If a name already appears in the text box, click the drop-down button at the far right of the box and select New User from the list. A username can be up to 30 characters. The first character must be a letter, or either of the symbols # or _. The rest of the characters can be letters, numbers, or most symbols. If you leave the name blank, a name that matches the login ID (see next step) is created for use in the database.

6. Select a login ID from the Login drop-down list box.

7. If you want, you can add this username to one group in addition to the public group, which automatically contains all usernames.

8. Click Add. The Add button becomes a Modify button, indicating that the new username has been added to the database.

9. If this username is to be used as an alias for more than one login, you may select logins in the Available Logins box and click Add->. This adds these logins to the Aliased Logins box. When any aliased login uses this database, it will use the username just created as an alias. Click Modify.

10. Click Close.

What Is an Alias?

Whenever two or more login IDs share a username, that username is called an alias. Any username can be used as an alias. An alias can be an appropriate way to provide several users with the same access rights. Aliases are commonly used to allow more than one user (each with a login ID but sharing a single username) to act as a database owner (DBO). If you intend to alias a number of users to a single username, it is best to create a username that indicates a type of person or a role (like Engineer or Sales), rather than a proper name (like Sam), to avoid confusion.

To assign a username as an alias for more than one login, follow this procedure:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage and open the Databases folder. The databases on the server are listed. Select the database for which you want to create an alias.

4. Choose Manage, Users from the menu. The Manage Users dialog box appears (see fig. 18.8).

   Fig. 18.8 - The Manage Users dialog box can also be used to assign a username as an alias for more than one login to use within a particular database.

5. Select a username in the User Name drop-down combo box.

6. The primary login ID associated with this username appears in the Login drop-down list box.

7. Select logins in the Available Logins box and click Add->. This adds these logins to the Aliased Logins box. When any aliased login uses this database, it will use the username just created as an alias. Click Modify.

8. Click Close.

What Is a Group?

A group is a collection of usernames created to simplify the assignment of access rights. When a username is created in a database, it is automatically added to the built-in group public and cannot be deleted. You may also create other groups. Each username may be added to one additional group besides public. You can remove a username from that additional group and add it to a different group if you later want to change the username's membership.

If you have a database with many usernames defined, it is a good practice to use groups to manage permissions. In such a database, it may be easier to create the groups first and then add usernames to the appropriate group as they are created.

To add a group to a database, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage and open the Databases folder. The databases on the server are listed. Select the database for which you want to create a group.

4. Choose Manage, Groups from the menu. The Manage Groups dialog box appears (see fig. 18.9).

   Fig. 18.9 - The Manage Groups dialog box is used to create a group of users for the purpose of granting database access.

5. Enter a new group name in the Group drop-down combo box. If a name already appears in the text box, click the drop-down button at the far right of the box and select New Group from the list. A group name can be up to 30 characters. The first character must be a letter or either of the symbols # or _. The rest of the characters may be letters, numbers, or most symbols.

6. If you want, you can add usernames to membership in the group. Select usernames from the Users list box in the Database Users box. Click the Add-> button in the center of the dialog box to add them to the group. They will appear in the Users in Group list box.

7. When you are done adding users, click Add in the upper right corner of the dialog box.

Understanding Special Users and Login IDs

System Administrator (SA)

When you install SQL Server, the sa login ID is created automatically. This login allows full access to all databases, database objects, and commands. A number of operations can only be initiated by the SA. Anyone who knows the password for the sa login can act as SA for the server. In addition, with integrated security, you can use the SQL Security Manager to map one or more Windows NT accounts to the sa login. When these users attach to SQL Server using a trusted connection, they will be acting in the role of SA.

The person acting in the role of system administrator (SA) frequently uses the sa login ID. A lowercase sa refers to the login ID. An uppercase SA is an abbreviation for the role of system administrator.

SAs are not subjected to any security checks after they are connected. They can perform any operation and manage all objects in all databases. An SA can temporarily take on the role of another username by using the following command:

SETUSER ['ID']

Default Login ID

When a user connects to SQL Server using a trusted connection, an implicit login process occurs. If the SA has used the SQL Security Manager to map the user's Windows NT account to a SQL Server login ID, that login is used. If no mapping has been created, SQL Server uses the default login ID (if it has been created). You can create a default login ID during SQL Server installation or by using the SQL Enterprise Manager.

To create a default login ID with SQL Enterprise Manager, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name. Select the server you want to manage.

3. Choose Server, Configurations from menu. The Server Configuration tabbed dialog box appears, as shown in figure 18.10. Click the Security Options tab.

   Fig. 18.10 - The Security Options tab of the Server Configuration dialog box is used to select the security model used by this server and configure other security related settings.

4. Your current settings are displayed. If the Default Login text box is empty, enter a login to use as the default. Click OK to close the dialog box and save your new setting. This does not create the login ID. If the login ID was previously created, you are finished. If you need to create a new login to match the entry you just made, continue with step 5.

5. Click the Manage Logins toolbar button or choose Manage, Logins from the menu. The Manage Logins dialog box appears (see fig. 18.11).

   Fig. 18.11 - The Manage Logins dialog box can be used to assign database access to the default login ID.

6. Enter the same name you used in step 4 into the Login Name drop-down combo box. If a name is already listed in the text box, click the drop-down button to the right of the text box and select New Login from the list.

7. Enter a password for the login ID. Users who will be connected to SQL Server with the default login will need to know this password.

8. Select a default language for this login in the Default Language drop-down list box.

9. In the Database Access box, you can make multiple selections as appropriate. Select the Permit box for each database you want the default login to be permitted to use. Click the Default box for the one database that will become the current database when this login is used. The username this login will use within a database is listed in the User column. The default username is the same as the login ID. You can change this if you want by typing another name in the box. Alternatively, you can select the Alias box for a database and select a username that has already been defined to be used as an alias for this login ID. A login can be assigned only one username (or alias) per database. Finally, you can select one group in the Group box per database. This group is in addition to the public group, which contains all users.

10. Click Add. The confirm password dialog box appears. Carefully reenter the password to confirm it and click OK.

11. Click Close to close the Manage Logins dialog box.

Visitor Login IDs

If you have a number of users that need only occasional access to SQL Server, you may want to create a visitor login ID that can be shared among these casual users. This is done as a convenience for the SA. Rather than create individual logins for all these users, you create a single login (named visitor, for example) and provide the login ID and password to users who fit the description of a visitor. Typically such a login would be assigned very limited access to databases and database objects. This is usually done by the use of guest usernames, discussed later in the chapter.

The Database Owner (DBO)

The creator of a database is the database owner (DBO). On a newly installed SQL Server, the sa login is the DBO of the master database. The SA can grant the ability to create databases to specific logins. If a user connects to SQL Server with one of these logins and creates a database, the user will be known in that database with the special username DBO. In other databases, the user will be known by whatever username has been assigned to that user's login.

Only one login can be assigned DBO status in a database. DBO status can be transferred to a different login (using sp_changedbowner), and multiple logins can be aliased to DBO. The DBO has all privileges in a database and can act as an administrator of that database, granting other users permission to access the database, creating objects, and assigning permissions to objects.

Database Object Owners

A DBO can grant permission for users to create objects in the database. When a user then creates an object, that user becomes the object owner. The object owner must grant permission for other users to access or modify the object, including the DBO or SA. However, the DBO or SA can both impersonate any other user in the database, including the database object owner, and thereby grant permissions. No special login or username is associated with an object owner.

The public Group

All usernames added to a database are automatically added to a special group named public. Usernames cannot be dropped from this group, and the group itself cannot be dropped. Access permissions assigned to the public group create a minimum set of permissions shared by all usernames in a database. Usernames can be added to one group in addition to public.

The guest Username

By creating a username of guest in a database, you create a username context that will be used by any login that has not been explicitly assigned a username or alias for that database. Therefore, any user that can connect to SQL Server (with their own login, a visitor login, or the default login) can use the database with the access permissions assigned to guest, even if the login they use has not been assigned a username or alias. When the guest username is created, it automatically inherits the access permissions granted to the public group. These can be changed by an SA or the DBO.

Creating Devices

Backup (Dump) Devices

The terms dump and backup are synonymous in the context of SQL Server discussions. Starting with version 6.5, there is a growing use of backup rather than dump. Beginners occasionally confuse dump and drop - a serious error! To drop an object is to discard or delete it, something you certainly wouldn't want to do just as you intended to back it up.

To create a backup device, perform the following steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage. Click the plus sign next to the server name to open the list of objects and services on the server.

4. Select Backup Devices in the list and click the right mouse button.

5. Select New Backup Device from the resulting pop-up menu. The New Backup Device dialog box appears (see fig. 18.12).

   Fig. 18.12 - The New Backup Device dialog box is used to create a backup device (also known as a dump device) to be used as the target of a backup procedure.

6. Enter the backup device Name. This is the logical name of the device.

7. Enter the physical name for the dump device in the Location box. The name should be of the form drive:\path\filename for a local disk drive, or \\servername\sharename\path\filename for a shared network drive. A tape drive will have a name of the form \\.\TAPEx where x is replaced with an integer starting with 0 for the first tape drive, 1 for the second, and so on.

8. Select the type of backup device you want to create using the option buttons.

9. Click Create.

Database Devices

When you create a device for a database, you should also create a log device on a separate drive. This allows you to recover the database in case the drive containing the database crashes. (Remember, you must have in place and execute a regularly scheduled DUMP for this plan to work.) Also, creating the transaction log on a device separate from that which contains the database improves performance and allows you to back up the transaction log without backing up the database.

To create a database device, perform the following steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage. Click the plus sign next to the server name to open the list of objects and services on the server.

4. Select Database Devices in the list and click the right mouse button.

5. Select New Database Device from the resulting pop-up menu. The New Database Device dialog box appears, as shown in figure 18.13.

   Fig. 18.13 - The New Database Device dialog box is used to define the name, size, and location of a new database device.

6. Enter the database device Name. This is the logical name of the device.

7. Select the drive on which to put the device. (SQL Server allows devices to be placed on local disks only - that is, placed on the computer on which SQL Server is installed, even though it can be managed from a workstation.)

8. If you want to change the default path, overwrite the name in the Location drop-down combo box or click the ellipsis (...) button to browse the directory structure of the server. Enter the size in megabytes. The minimum size for a database device is 1M. The relative size of the proposed device will be displayed in a bar chart that changes dynamically as you enter the number.

9. Select the Default Device check box if you want to add this database device to the pool of default devices used to store newly created databases when a device is not specified.

10. Click OK. The database device will be added.

Creating Databases and Database Objects

To create a database using the SQL Enterprise Manager, follow these steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage. Click the Manage Databases toolbar button; the Manage Databases dialog box appears (see fig. 18.14).

   Fig. 18.14 - The Manage Databases dialog box provides a graphical representation of the databases defined on a server.

4. Click the New Database button. The New Database dialog box appears, as shown in figure 18.15.

   Fig. 18.15 - The New Database dialog box is used to define a new database.

5. A graph appears representing the various devices defined on this server, the size of each device, and the amount of available space. A default device to contain the new database is automatically entered in the Data Device drop-down list box.

6. Enter a name for the new database in the Name text box.

7. If you want, you can change the data device selected by default by using the Data Device drop-down list box. You can also select <new> from the list if you want to define a new device. Use the same procedure you have already learned for creating a new device to create a device during database creation. Refer to the section "Creating Devices" earlier in this chapter.

8. Enter a size for the data device the Size (MB) text box. By default, this is filled in with the largest amount of space available on the selected device. As you enter numerals, the graphic display is updated to reflect the size of the proposed database.

9. Select a device to contain the transaction log for this database from the Log Device drop-down list box. Again, you can select <new> from the list if you want to define a new device. Enter a size for the log device in the Size (MB) text box.

   Always store the transaction log for a database on a separate device from the database itself. This improves performance and allows you to make backups of the log. As a rule of thumb, the size of the log should be 10 to 25 percent of the database size.

10. If you will be loading a backup into this database before it is used, click Create For Load.

11. Click OK to create the database.

After you have created the database, you can begin creating the database objects. The planning that you've done for the database and its objects can now be implemented. Before you begin, however, review your data model for potential problems. This means that your plan must be documented, not just worked out in your head. Any database requiring the power of SQL Server should be documented and reviewed by at least one other person. It is always easier to do this before these database objects are created than after.

Just a few of the issues you should resolve before proceeding with creating database objects are as follows:

• Have all tables been identified and defined?

• Have all columns been identified for each table?

• Has the datatype and/or length of each column been established?

• Have you determined whether each column can contain a null value?

• Has a set of valid values been determined for each column?

• Has a default value been considered for each column?

• Have any key fields been identified for all tables?

• Have all possible indexes been defined to optimize performance?

• Have all the business rules been identified?

• Have you determined how application developers will present the data to the eventual users?

• Have you determined how data integrity will be enforced?

• Have you determined how referential integrity will be enforced?

If you answered no to any of the preceding questions, you are not ready to create database objects. It cannot be stressed enough that proper planning is essential in creating and maintaining a SQL Server database along with a client-server application. Don't fool yourself by believing that you can plan as you go. It just won't work.

You can create several supporting objects during the creation of a table object by using the Advanced Features button. However, the following sections demonstrate how you can create these objects independently of one another to introduce you to the various facilities of the SQL Enterprise Manager. This will be useful later when maintaining the database structure.

Tables

Table 18.1 Data Types

Data Types	Bytes Required
binary(n)	n
varbinary(n)	0-255
char(n)	n
varchar(n)	0-255
datetime	8
smalldatetime	4
decimal(p,s)	1-17 (depending on the precision)
numeric(p,s)	1-17 (depending on the precision)
float(n)	8
real	4
int	4
smallint	2
tinyint	1
money	8
smallmoney	4
bit	1 (up to 8 bit columns can be stored in one byte
timestamp	Same as binary(8)
user-defined	Variable datatypes
text	Stored as series of linked 2K pages
image	Stored as series of linked 2K pages

A number of questions should come to mind when designing tables:

• What data do I need in the tables?

• How will these tables be related to one another?

• What type of data will be stored?

• Are there any columns whose data items are optional?

• Are there any columns whose data items must fall within a certain range of values?

• Are there any columns whose data items must be restricted to values based on a value stored in another column or table?

• Are there any columns whose value will be the same in a large percentage of the rows?

The answers to these questions may necessitate the need to create one or more of the following supporting objects:

• Indexes

• Defaults

• Rules

• Views

• Triggers

• Stored procedures

Tables fall into one of two categories: permanent and temporary. Permanent tables exist until they are explicitly dropped. Temporary tables are further subdivided into two types, local and global. Local temporary tables exist until the current session is terminated. Global temporary tables exist until all sessions using the table have been terminated. To create temporary tables, you must use the CREATE TABLE command or the SELECT INTO command. See "CREATE TABLE Statement," in the TRANSACT-SQL Reference Manual.

To create a permanent table, you must know the columns required for the table as well as the datatypes for all the columns. It is also a good idea to know which columns may contain a null value. To create a permanent table, perform the following steps:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage. Click the plus sign next to the server name to open the list of objects and services on the server.

4. Click the plus sign next to the Databases folder to open the list of databases on this server. Highlight the database on which you wish to create the table.

5. Choose Manage, Tables from the resulting pop-up menu. The Manage Tables dialog box appears, as shown in figure 18.16.

   Fig. 18.16 - The Manage Tables dialog box is used to create a table by defining the columns, their datatypes, their size, and any constraints that apply to the columns.

6. You may now begin entering the column names for the table. For each column identified for the table, you can enter the following pieces of information:
   • Column name

   • Datatype

   • Size (if applicable)

   • Nulls

   • Default

7. You may notice two additional columns in the dialog box labeled Key and Identity. These are read-only. You learn to create indexes and identity columns in the next section, and defaults in another section later on.

8. After you have entered the column names and their datatypes, click the Save Table toolbar button. The Specify Table Name dialog box appears so that you may specify the table name (see fig. 18.17).

   Fig. 18.17 - Use this dialog box to enter a name for the new table.

9. Enter the table name and click OK.

Indexes

An index can be identified as a unique index, which means that the column(s) identified in the index must be unique in the table (that is, no two rows in the table can contain the same value). An example would be a social security number (SSN) on a personnel table. Because no two people should possess the same SSN, a unique index would be appropriate. If an attempt was made to add a record whose SSN already existed in the table, an error would result. Column(s) identified in a unique index cannot allow a NULL value.

This is actually the subject of debate among database aficionados. The uniqueness of Social Security Numbers is not guaranteed. Depending on the size of the population and range of years tracked by your database, this may be a poor choice for a unique index. In practice, it works most of the time, but it does highlight the care you must take when selecting unique indexes.

Other columns do not accommodate a unique index, such as a state column. If a personnel department must produce reports based on the employee's state, it would make sense to create an index on the field. However, because the state field cannot be unique for every row on the table, a unique index cannot be built on the state column.

After the table has been created, you can enter any necessary indexes. As stated earlier, an index is used primarily to reduce response time during the execution of queries. However, indexes can also provide a way to enforce data integrity as well. For example, you can create a unique index on the social security number of a personnel file, thereby prohibiting the addition of records with duplicate social security numbers.

To create an index, perform the following tasks:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage. Click the plus sign next to the server name to open the list of objects and services on the server.

4. Click the plus sign next to the Databases folder to open the list of databases on this server. Highlight the database on which you wish to create the index.

5. Choose Manage, Indexes from the menu. The Manage Indexes dialog box appears, as shown in figure 18.18.

   Fig. 18.18 - The Manage Indexes dialog box is used to define and modify indexes for tables in the database.

6. This dialog box allows you to create and maintain all indexes in a database. First, you must select the table on which you want to create an index from the Table drop-down list box in the upper-left hand corner of the dialog box. When a table is selected, its columns are displayed in the Available Columns In Table list box directly below the Table drop-down list box. The list box also displays whether each column may contain a null value and the datatype of the column.

7. If any indexes currently exist for the selected table, they appear in the drop-down list box to the right of the one for the tables. To create a new index, you must enter the name of the index in the drop-down list box. You now can highlight individual columns and make them part of the index you are creating by clicking Add. As you do this, the column disappears from the Available Columns In Table list box and appears in the Columns In Index (Key) list box. To remove a column from the index, highlight it and click Remove.

8. If you want the index to be for a unique key, click the Unique Keys check box in the Index Attributes area. If the Unique Keys check box is checked, you may also indicate whether to ignore duplicate keys by clicking the Ignore Duplicate Keys check box.

9. You can also specify whether the index should be clustered. If so, you may also indicate whether SQL Server should allow duplicate rows. This option can be specified only when a non-unique clustered index exists.

10. The Sorted Data option is valid only when creating a clustered index on an existing table. If you select Sorted Data, the sort step of the index build is not performed (because you indicated to SQL Server that the data is already sorted in index order). If the data exists in a different order from what the index specifies, the new index will not be built.

11. If you are an experienced DBA who wants to exercise more control over how much of each page should be left available for subsequent rows in the table, you can make an entry in the Fill Factor box. This is only relevant at the time of index creation on an existing table and only useful when you can accurately predict how the data in the table will change in the future.

12. Click the Build button. The new index will be created.

See "Indexing," (Ch. 17)

Defaults

Defaults allow you to avoid providing a value if the value of the column will be the same in most rows. The easiest way to define a default is to enter it when you create a table using the method mentioned previously. The rightmost column in the Manage Tables dialog box is used to provide a default for the column in the table. If you want to define a default that can be used by many columns in various tables in the database, you must define the default a different way.

To define a default that can be used throughout the database, perform the following tasks:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage. Click the plus sign next to the server name to open the list of objects and services on the server.

4. Click the plus sign next to the Databases folder to open the list of databases on this server. Highlight the database on which you want to create the default.

5. Choose Manage, Defaults from menu. The Manage Defaults dialog box appear with three tabs: Defaults, Column Bindings, and Datatype Bindings, as shown in figure 18.19.

6. Make sure that the Defaults tab has been selected and enter the name of the default in the Defaults drop-down list box.

7. Enter the description of the default in the Description text box. If you enter a string for the description, it must be enclosed in quotation marks.

8. Enter a value for the default.

9. Click Add. Even though the default has now been added, it is not associated with any columns in any tables in the database. To apply the default to specific columns, you must now "bind" the default to the columns.

10. Select the Column Bindings tab on the Manage Defaults dialog box (see fig. 18.19).

    Fig. 18.19 - The Column Bindings tab on the Manage Defaults dialog box is used to bind a default value to one or more columns in one or more tables.

11. Select the table that contains the column to which you want to associate the default. Select the column in the table.

12. Click Bind.

Rules

The following criteria can be specified in a rule:

• Specific values (the 51 legal state codes for example, AK, NY, and so on)

• A range of values (a number between 1 and 51)

• Formatting (such as a field that requires that the first two characters be letters and the next three characters be numbers)

Using rules can result in more reliable data in the database because some validation can be done at the server level. Rules are created in much the same way as defaults. The only difference is that from the Manage menu, you would select Rules. In addition, the description must contain the condition(s) for the rule instead of a single value for a default. After a rule is created, you must bind it to column(s) in the database in the same manner you learned to bind defaults to columns in a database. Refer to the "Defaults" section earlier in the chapter.

Views

A view is sometimes called a virtual table, meaning that the data in a view (in most cases) can be manipulated and displayed as though it were in a table. The data itself is not stored in the view, but in the underlying tables on which the view is based. A view is stored in the database as instructions for creating the presentation of the data from the actual tables.

The following benefits make views a valuable tool in SQL Server:

• Views can be constructed so as to show only specific rows and/or columns, thus making it easier for the DBA to limit what users can see in the database.

• Views can reference one or more tables, joining separate tables on columns that correspond to each other.

• In most cases, views can be treated as though they were tables with updates applying to the underlying tables.

To define a view, use the CREATE VIEW statement in conjunction with the SELECT statement from the Transact-SQL language. If you are not experienced with using Transact-SQL, you should review "Retrieving Data With Queries" in the Database Developer's Companion. You should also practice executing queries with the ISQL/W utility provided with SQL Server.

To create a view in SQL Server, perform the following tasks:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage. Click the plus sign next to the server name to open the list of objects and services on the server.

4. Click the plus sign next to the Databases folder to open the list of databases on this server. Highlight the database on which you want to create the view.

5. Choose Manage, View. The Manage Views dialog box appears (see fig. 18.20).

   Fig. 18.20 - The Manage Views dialog box is used to enter the definition for a new view or modify a view that has already been created.

6. <New> appears in the Views drop down list box and CREATE VIEW <view name> AS in the lower portion of the dialog box. To finish creating the view, replace <view name> with the name of the view you want to create, enter a SELECT statement that specifies the tables and columns to include in the view below.

7. Click the Execute button (a green triangle pointing to the right). The view will be created. You may now treat the newly created view as if it were a table in the database for most operations.

Triggers

Stored Procedures

Stored procedures can accept up to 255 parameters. This allows standard calculations or processes based on one or more variables to be coded into a stored procedure. Procedures can also call other stored procedures, which allows some code reuse within SQL Server itself. Version 6.0 of SQL Server contains an enhancement to the EXEC statement that allows you to execute a dynamically built SQL statement. This allows you to program an iterative process in which a SQL statement could be executed a variable number of times, depending on the process and the items for which the process was called.

There is also the opportunity to provide limited access to information from tables a user does not have permission to view. Permission to execute a stored procedure can be granted to the user, and the stored procedure may return specific data from tables to which the user does not normally have access.

They can also provide a way to enforce business rules. For example, a mail order house could have a table built for sales records and have a stored procedure be responsible for entering new rows into the table. The stored procedure could check the outstanding unpaid balance for the customer, and if it happens to be over a defined threshold, reject the attempt to add a record to the table and inform the user that the customer's unpaid balance must be reduced before further sales to that customer are allowed.

Stored procedures allow you to do just about anything you can imagine with a SQL Server database. With very few exceptions, Transact-SQL statements can be combined to provide the required functionality. A thorough understanding of Transact-SQL is recommended to take full advantage of stored procedures.

Stored procedures are created in much the same way as views, except that stored procedures are not limited to the SELECT statement.

To create a stored procedure in SQL Server, perform the following tasks:

1. Start SQL Enterprise Manager.

2. In the Server Manager window, open a server group by clicking the plus sign (+) to the left of the group name.

3. Select the server you want to manage. Click the plus sign next to the server name to open the list of objects and services on the server.

4. Click the plus sign next to the Databases folder to open the list of databases on this server. Highlight the database on which you want to create the stored procedure.

5. Choose Manage, Stored Procedures from the menu. The Manage Stored Procedures dialog box appears (see fig. 18.21).

   Fig. 18.21 - The Manage Stored Procedures dialog box is used to enter the definition for a new stored procedure or modify a stored procedure that has already been created.

6. <New> appears in the Procedures drop-down list box and CREATE PROCEDURE <procedure name> AS in the lower portion of the dialog box. To finish creating the stored procedure, replace < procedure name > with the name of the stored procedure you want to create and enter the Transact-SQL statements that comprise the stored procedure in the window below.

7. Click the Execute button (a green triangle pointing to the right). The stored procedure is then created.

From Here...

• For an understanding of the details associated with administering SQL Server, see Chapter 19, "Maintaining Your SQL Server."

• For an overview of data replication and distributed transaction management, see Chapter 20, "SQL Server Advanced Topics."

• For information on the host connectivity component of BackOffice, SNA Server, see Chapter 21, "SNA Server Preparation and Installation."

• For information on the systems management component of BackOffice, Systems Management Server (SMS), see Chapter 25, "Preparing for SMS."

Table of Contents

17 - Understanding SQL Server

19 - Maintaining Your SQL Server