Quick Start Guide to SQL Server 7 — Part 1

There comes a time in every programmers life when your voice drops a pitch, hairs sprout in the most unexpected of places and you realise the need to move onto a bigger and better database system.

Most of us start off dabbling with Access databases. They’re fun, cheap and easy to maintain. But there comes a time I call it The Change when they’re just not enough.

You need something more powerful, more expensive, more professional looking. And often, that something is SQL Server.

This four-part series provides quick, no-nonsense, step-by-step instructions for getting started with all editions of SQL Server 7.0. It doesn’t delve into its history, it doesn’t stop to evaluate whether or not this is actually the database system for you, it doesn’t babble.

This guide is for those graduating from Access, needing to start using SQL Server and fast.

It covers all the SQL Server essentials, alongside chunks of relevant Visual Basic and ADO code. From table creation right through to stored procedures, it’s all here.

Remember, this guide is designed for printing. Either print each page direct from your browser, or get the friendlier, more compact version by clicking our print link to the right. Unfortunately this guide isn’t currently available as one big download.

If you’re looking for further help or assistance with problems, try checking out the Books Online application, under SQL Server’s entry on the Start menu.

Enough chit-chat, it’s time to get serious. The topic is SQL Server, we’re wanting just the facts, ma’am… and your time starts… now!

Databases:

• Launching your Server Taskpad
• Creating a Database
• Viewing your Database in Enterprise Manager
• Moving your Access database to SQL Server

Tables:

• Viewing your Tables
• Creating a Table
• Altering your Table
• Entering data into your Table
• Viewing your Table Properties

Data Types:

• List of all SQL Server data types

Security:

• Creating a Login
• Granting access to a user

Indexes:

• Creating an Index
• Running the Index wizard
• Managing your Indexes
• Running the Index Tuning Wizard

Relationships:

• Creating a relationship

VB Code:

• Skeletal ADO data access code

Views:

• Creating a View
• Editing your Views
• Granting user access to a View

Deleting, Renaming:

• Deleting an Object
• Renaming an Object

Defaults:

• Creating a Default
• Binding a Default
• Editing a Default

User Defined Types (UDTs):

• Creating a UDT
• Implementing a UDT

Rules:

• Creating a Rule
• Binding a Rule
• Editing a Rule

Transactions:

• How they Work, with VB Code

Triggers:

• Managing Triggers
• Sample Trigger Code

Stored Procedures:

• Creating a Stored Procedure
• Editing a Stored Procedure

Stored Procedure Examples, with VB code:

• SELECT Stored Procedure
• UPDATE Stored Procedure
• DELETE Stored Procedure
• INSERT Stored Procedure
• Output Parameters
• More Output Parameters

Databases are a holding place for information.

Within SQL Server, a database may hold numerous tables, views or stored procedures, among other items.

We’ll cover all of these later, just remember that SQL Server has features that Access doesn’t have (such as stored procedures), and at the same time, Access sports certain qualities not found in SQL Server (such as Forms).

Another core difference is that Access is essentially just an .MDB file, whereas SQL Server is ‘live’ and running on the server.

In this section, we’ll learn how to create an empty database the holding point for tables and so on plus port any existing Access databases across to SQL Server via something called the Upsizing Wizard.

Launch your Server Taskpad:

• Start Enterprise Manager, the front-end for SQL Server
• Programs/Microsoft SQL Server 7.0/Enterprise Manager
• Expand the SQL Server Group item to view all connected systems running SQL Server
• Dip into the computer you want by double-clicking on its icon

To create a Database:

• Launch your Server Taskpad (as before)
• Click the ‘Set Up your Database Solution’ icon
• Click the ‘Create a Database’ icon to run the Wizard
• On the first introductory screen, click Next
• On the next screen, give your database a name and location, then click Next
• It’s best to give your database a name without spaces, to avoid confusion and errors later
• The database file location will be the place your actual tables, data within them, and so on, are stored
• Every action in your database is recorded in a transaction log. So, if you have a hardware failure or major database corruption, you can restore data from that transaction log. For greatest peace of mind, change the transaction location to an external drive one different from the location of the main database files

• This screen simply asks you to set the initial starting size of your database. Change if required, then click Next
• With Access, database sizes grow as more information is put into them. With SQL Server, you set a certain amount of disk space to one side, ready to hold future data
• When you need to store more information, previously you had to manually ‘expand’ the database. However SQL Server 7 can now automatically do that expanding for you, as we’ll see on the next screen
• On this screen, select your database growth options. Click Next when finished.
• The default options dictate that when the current database is ‘full’, it allocates another block of hard disk space for the database to use.
• It’s recommended you stick with the defaults, unless you have specific requirements

• The next two screens ask you for the initial size and growth rates of your transaction logs. Click Next when finished

• Once again, it’s recommended you accept the defaults unless you have specific requirements

• On the final Wizard screen, review the description of your new database, move back and forth to implement any changes, then click Finish when complete
• After the database is complete, you may be prompted to create a maintenance plan for the database

• The maintenance wizard allows you to schedule database backups and so on. You may follow this through if you wish
• We will not be covering maintenance in this quick start guide

To view your database in Enterprise Manager:

• Launch your Server Taskpad (as above)
• Double-click on your server name, to the left of your screen
• Double-click on the ‘Databases’ folder
• You should see a list of databases on the screen, including your own. Many of these are samples that ship with SQL Server.
• Double-click on your database name
• If you’re new to this, don’t worry about some of the mysterious items listed under your database such as Rules and Stored Procedures. We’ll cover the most important of these later

It’s also worth noting that Microsoft Access 2000 ships with an Upsizing Wizard that can automatically port your .MDB database over to SQL Server.

To move your Access database to SQL Server:

• Launch Microsoft Access 2000
• Click ‘Tools’, ‘Database Utilities’, ‘Upsizing Wizard’
• If you’re using Access 97, you probably won’t have the Upsizing Wizard we’re looking for. To download it, visit www.microsoft.com/accessdev/prodinfo/AUT97dat.htm
• For all other versions, it’s advisable to upgrade your database to Access 97/2000 and then use the Upsizing Wizard from there
• Unlike Access, SQL Server doesn’t use ‘forms’. Only your tables, queries and relationships will be ported across
• Follow the on-screen prompts

The most important objects within your database are tables.

These are a lot like Excel spreadsheets, each storing rows of information, such as a customer order or shipping address.

Typically, you would utilise normalisation rules when creating the tables, meaning you should ensure each table has no repeating columns, utilises primary and foreign keys where appropriate, and so on.

To view the Tables:

• View your Database in Enterprise Manager (as before)
• Click the ‘Tables’ image
• Any tables displayed beginning with ‘sys’, as well as ‘dtproperties’ are SQL Server’s own. It uses these tables to store information about your database

To create a Table:

• View the Tables (as before)
• Click ‘Action’ in the toolbar, select ‘New Table’
• Enter a Table Name when prompted
• You may wish to use a table naming convention here. Many opt for tblTableName, whilst others prefer TableName_t. It’s your call
• Define your fields, row by row
• Column Name Enter the name of this field. You may wish to use naming conventions here, depending on the data type
• Datatype Select the required data type (full explanatory list on the next page)
• Length This isn’t always editable. It specifies the length of your data type (eg, char data type, 15 characters in length, or varchar data type, maximum 15 characters in length)
• Precision Applies to numeric data only. This isn’t always editable. Specifies the maximum number of digits in the number (eg, the number 539.154 has a scale of six). Almost a length value for numeric data types
• Scale Applies to numeric data only. This isn’t always changeable. Specifies the maximum number of digits to store to the right of the decimal point (eg, 12.528 has a scale of three)
• Allow Nulls Check if Null values (ie, nothing) are allowable in this field. Otherwise, uncheck
• Default Value A default value to be placed into this field when a new row is inserted
• Identity If this will be an identity column (ie, you want SQL Server to place a unique number into the field), check this box
• Identity Seed If this is an identity column, enter the starting point for the unique numbers. By default, this is set at one
• Indentity Increment If this is an identity column, with each new row, the unique ID number will be incremented. Typically, it is upped by one each time. If you wish to change that figure (ie, increase numbers by ten each time), enter the incrementing value here
• IsRowGuid Check if you want to automatically insert a Globally Unique Identifier (GUID) into this field. These are guaranteed unique IDs and no two in the world should ever be the same. An example of a GUID is {B31C08E5-2A56-11D4-B09E-CFB9E165D54C}. To choose this option, you must have a Data Type of Unique Identifier
• When finished, press CTRL-F4 to close the table
• If prompted to save, click Yes/No

To alter your Table design:

• View the Tables (as before)
• Right click on your table
• Select ‘Design Table’
• Make changes
• Press CTRL-F4 to exit
• If prompted to save, click Yes/No

To enter data into your Table:

• View your Tables (as before)
• Right click on your table
• Select ‘Open Table’, ‘Return All Rows’
• Start entering data, row by row
• Note that this table viewer is a little buggy. For example, if your table specifies that a GUID should be inserted into a field, it will appear as ‘<Null>’ or as a bunch of zeros until you hit the Run button (exclamation mark icon on the toolbar)

To view Table Properties:

• View the Tables (as before)
• Single click on your table
• Press ALT-Enter

Each field in a table needs to be of a specific data type. The following list describes each of them, starting with the most common.