Quick Start Guide to SQL Server 7 -- Part 2

Welcome to the second part of our quick start SQL Server 7 guide.

Get ready to fire up that printer for yet another healthy dollop of no-nonsense, step-by-step, print-and-collect info. It's the only SQL Server guide of its kind on the Web.

If you missed part one, check it out here.

So what are you waiting for? Let's dive straight into the content!

Once you have information in a database, you'll probably want to access it from your application.

But to gain access, you'll first require a username and password login. Then you'll need to grant that login special rights to your tables and so on.

We'll be covering all that in this section.

To create a Login:

• View your Database in Enterprise Manager (as before)
• Click 'Set Up your Database Solution'
• Select 'Create a Login'
• Hit Next at the Welcome screen
• Select the SQL Server authentication mode, then click Next
• If you're running the Desktop edition of SQL Server on Windows 95/98, you will only be able to select SQL Server authentication. This means SQL Server handles all the usernames, passwords and other security issues
• If you're running Windows NT, you will also have the option of using Windows NT authentication. This means you can grant certain NT users direct access to databases, based on their Windows logon, without requiring a separate username and password for SQL Server
• These instructions give advice on creating a login for SQL Server authentication mode
• Enter a login name, then type in the password twice. Click Next when finished
• If you're creating an account for a special user, such as a developer or such like, select the appropriate security roles here. Otherwise, simply ignore. Click Next when finished
• Check the databases you wish to grant this new user access to, then click Next
• Allowing a user access to a database means just that they get access to the database, not the individual objects within it, such as the tables. You need to do this separately (as below)
• Hit Finish to create the login

To grant Table access to a user:

• View your Table Properties (as before)
• Click the 'Permissions' button
• Tick the boxes dependant on which permissions you want to grant the user
• To allow the user to view information in the table, check 'Select'
• To allow new records to be added, check 'Insert'
• To allow the deletion of records, check 'Delete'
• To allow the update of records, check 'Update'
• When granting permissions for stored procedures, which we'll come across later, you'd typically check the 'Exec' (execution) box
• Click OK on the Permissions form, then OK on Table Properties

You will typically assign a username and password combination for either an entire application (ie, one generic login with appropriate permissions), or on an individual user basis.

Most tables in your database will typically hold at least one index.

A table index is rather like the index of a book. It allows for the fast retrieval of specific information.

Adding an index to a field makes SQL Server go and organise all the information within that field, sorting and making it quicker to access that rows' data in future.

But unfortunately indexes in SQL Server aren't quite as simple as Access. Here, we have two different types of index:

• Clustered This type of index sorts the entire table based on one field. Imagine it as a phone book; it organises the Surname field in ascending order and can therefore find rows quicker based on that field. Of course, if it were just to search through a random list of data, it would be much slower. Note though, you can only have one clustered index per table
• Non-Clustered This is a strange index. Let's say you add this index to the Surname field. This index stores all of the Surnames in ascending order somewhere else, along with a pointer to the original row. So when you search for a particular Surname, it looks it up in the separate list of surnames, finds the pointer to the actual row, then speedily retrieves it. You can have more than one non-clustered index per table

You can use both clustered and non-clustered indexes within the same table. And even though SQL Server does a lot of behind-the-scenes work to implement these, you should note that this doesn't affect the way you retrieve records via Visual Basic.

It's also worth pointing out that you can involve more than one field within an index if you wish. For example, you may want to index both the Surname and Forename fields together meaning searches on both of those fields will be lightning fast.

But adding too many indexes also slows down data addition, as after every new record insert, SQL Server needs to reorganise the index. So use them in moderation.

It's also worth noting that, just as in Access, indexes can be unique - or not. If an index is unique, it does not allow duplicates (sometimes called duplicate keys).

To create an Index:

• Open your table to alter the design (as before)
• Click the 'Table and Index Properties' button on the toolbar
• Select the 'Indexes/Keys' tab
• Click 'New'
• Select the Columns (fields) you want to add to your Index
• You can select more than one if you wish
• Enter an Index name
• Alternatively, stick with the default
• Check 'Unique' if you want this column to contain only unique values
• It will automatically select the 'Index' option button. This creates a unique index, whilst the 'Constraint' option simply stops duplicates. Stick with the default.
• If you would like this to be a clustered index, check the 'Create as CLUSTERED' box
• To add another index, click 'New'
• Click Close when finished

To run the Index Wizard:

• Launch your Server Taskpad (as before)
• Click 'Tools' on the toolbar, then 'Wizards'
• Expand the 'Database' entry
• Select 'Create Index Wizard' and click OK
• Follow the on-screen prompts

To manage your Indexes:

• View your Tables (as before)
• Select the one you want to manage
• Right-click, select 'All Tasks', 'Manage Indexes'
• Use the 'New', 'Edit' and 'Delete' commands to alter as required
• Click Close when finished

If you're working on a large project and are unsure where you should add indexes, the Index Tuning Wizard can take frequently used SQL statements and decide for you. We won't be covering this feature in-depth here - for more information, check out Books Online.

To run the Index Tuning Wizard:

• Launch your Server Taskpad (as before)
• Click 'Tools' on the toolbar, then 'Wizards'
• Expand the 'Management' entry
• Select 'Index Tuning Wizard' and click OK
• Follow the on-screen prompts

Relationships in a database allow you to define how information in one table links to information in others.

This is very easy using Microsoft Access, and not that much more difficult with SQL Server, despite a few changes in terminology.

Let's quickly define the two main types of relationship:

• One-to-One In this type of relationship, you say that an entry in one table can only have one entry in another table. For example, an entry in the Employees table may only be allowed one entry in the CompanyCars table
• One-to-Many In this type of relationship, you say that an entry in one table can have many different entries in another. For example, an entry in the GeneralOrder table may have many related entries in the IndividualOrderItems table.

You typically make these relationships work by giving each record a unique value of some sort. For example, say you run a vetinary hospital. You'd probably have a Customer table, containing your customer name and addresses, along with a unique CustomerID number.

You might also have a Pets table, containing the name and breeds of each individual pet the hospital deals with. You'd probably also throw a CustomerID field into that Pets table, so you can tell which customer owns which pet.

Adding relationships to your table ensures data integrity. If we didn't add any relationships to the above scenario, something might go wrong and a particular Pet record might be given an invalid CustomerID. Relationships protect that from happening. They could also stop someone accidentally deleting a Customer that has related Pet records.

In this case, you might consider adding a one-to-many relationship between the main Customer table and the Pets table.

In other words, the relationship is between the CustomerID field in the Customer table (the 'one' part of the relationship) and the CustomerID field in the Pets table (the 'many' part of the relationship).

Then if you tried to add a pet with a CustomerID that didn't exist in the Customer table, the record would be rejected. And these same rules could also stop customers from being deleted when they still have entries in the Pets table.

In brief once more; relationships help maintain data integrity.

For more information about relationships, check out the coverage in part 5 of our VB Database Tutorial here.

To create a relationship:

• Ensure both the tables you wish to join have the necessary keys and are of the same base data type
• A 'primary key' ensures there are no duplicates in a particular field
• If you're creating a one-to-many relationship, the main table field(s) needs to be a primary key (eg, CustomerID)
• If you're creating a one-to-one relationship, the field(s) from both tables involved need to be primary keys
• To add a primary key, edit your table design, highlight the field(s) that you wish to change to primary key status, then click the key image on the toolbar
• View your database in Enterprise Manager (as before)
• Right-click on 'Diagrams' and select 'New Database Diagram...'
• A Database Diagram in SQL Server is like the Relationships screen in Access
• Cancel the Wizard
• Click the 'Add Table' button on the toolbar
• Add the desired tables, then hit Close
• You may find the tables have hidden themselves under each other. To view properly, simply drag them apart
• Highlight the field(s) you want to be involved in the relationship from the main table
• By clicking the small box to the left of the field
• Drag and drop the adjacent box on the field you want to create the relationship with, in the secondary table
• In the box that appears, check the relationship parameters, then click OK
• You will typically have one field from the primary key table, another from the foreign key table
• It is recommended you accept the default relationship options
• Hit ALT-F4 to close the Database Diagram
• Give the diagram a name, then click OK
• If you are given warnings stating existing data violates the relationships you attempted to put in place, sort it out
• If prompted that the specified tables will be saved to your database, click Yes to continue

Naturally, you can use the DAO and ADO controls to access your SQL Server data. But most users prefer to get at it via pure code after all, not only does it reduce the need to use a form and bound controls, it's also a lot more flexible.

We're now going to look at a skeletal piece of commented ADO code for accessing your SQL Server records from within Visual Basic. Note that you must have a reference (Project, References) to the Microsoft ActiveX Data Objects Library before running this code.

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objRS As New ADODB.RecordsetDim objErr As ADODB.ErrorobjConn.Open _"Driver=SQL Server;Server=COLOSSI;Database=Blossom; _        User ID=KarlMoore;Password=TEST"' Open the SQL Server connectionobjConn.CursorLocation = adUseClientIf objConn.State = adStateOpen Then' If everything is OK and we have a connectionobjRS.CacheSize = 10objRS.Open "Select * from tblCustomers", objConn, _              adOpenStatic, adLockOptimistic' Open the recordset object, selecting everything' from tblCustomers - passing our objConn connectionIf Not objRS.EOF And Not objRS.BOF Then' If not at the End Of File (EOF) nor' Beginning Of File (BOF) - in other words,' if we actually have records here - then...MsgBox objRS.Fields("CustomerID")' Display one of our fields just to prove' everything went A-OKobjRS.Fields("CustomerID") = 123objRS.UpdateEnd IfobjRS.Close' Close the recordsetElse' If the connection didn't open A-OK, thenFor Each objErr In objConn.ErrorsMsgBox objErr.DescriptionNext' Display all the errors in the connection objectEnd IfSet objRS = NothingobjConn.CloseSet objConn = Nothing' Tidy up the references, etc.End Sub

Most of this is pretty standard stuff. However there are a few lines of code you may wish to fine tune to suit your individual needs, particularly if you're working in a multi-user, real-time environment.

Unfortunately, this is neither an ADO nor general data access tutorial - and some of the following issues are simply raised for completeness. If it's your first encounter, you may find it all a little strange. Thankfully the ADO help file can help out, with its own mini tutorial. To view it, find and run the file 'ADO210.CHM' or 'ADO200.CHM'. Alternatively, just stick with the template.

CursorLocation Property

objConn.CursorLocation = adUseClient

First off, we have the CursorLocation property. The cursor represents where in the recordset you currently are. It's a little like Word's own flashing cursor, which shows you where you are in the current document.

You can set this to either:

• adUseServer This keeps the cursor with the server, a real resource hog if you have plenty of users. However, when used with certain types of cursor (below), it does have the advantage of being able to - spot newly added records, remove fresh deletions from your recordset and so on. But you use it at a price.
• adUseClient This keeps the cursor on the client side, the user's computer. This removes workload from the server but doesn't allow you to view new records that match your criteria, etc. Still, most of the time, such features aren't required

If you're in doubt, go for adUseClient.

Cursor Type

objRS.Open "Select * from tblCustomers", objConn,adOpenDynamic, adLockOptimistic

The third argument in this code asks for the type of cursor you want. You can replace the current adOpenDynamic with any of the following:

• adOpenForwardOnly This cursor allows you to simply scroll forward through the recordset, but there's no going back. This option is very resource friendly
• adOpenKeyset This cursor allows you to move both backwards and forwards through the recordset. It maintains a live link with the database to ensure all fields are kept up-to-date (ie, they reflect the changes of other users), however it doesn't detect any externally added records. The keyset cursor maintains a live connection to the database and is quite a resource hog
• adOpenStatic This option grabs a copy of the records. You can't view any changes, new records or deletions by other users, though you can make edits yourself. This cursor is pretty resource friendly
• adOpenDynamic Using a dynamic cursor, you can view all changes, additions and deletions by other users. It maintains a live conversation and so is pretty resource expensive

Lock Type

objRS.Open "Select * from tblCustomers", objConn,adOpenDynamic, adLockOptimistic

That fourth argument in this code asks for a 'lock type'. The current adLockPessimistic is interchangeable with any of the following:

• adLockReadOnly Read only. The data retrieved cannot be modified. Very resource friendly, but not always practical
• adLockPessimistic A lock is placed on records as soon as you start editing them and released when the Update or Cancel methods are called
• adLockOptimistic This option means that when you run the Update method, the record is locked, updated and then released, all in one swoop. If someone else changed the record before you started editing it, an error will occur
• adLockBatchOptimistic Whereas adLockOptimistic allows you to edit a record 'offline', then update it in one swoop, adLockBatchOptimistic allows you to do this with more than one record. You can edit the records in the recordset, then do a complete database update on all those changed by running the UpdateBatch method. Any errors will be returned to the Errors collections of the connection

Cache Size

objRS.CacheSize = 10

Another interesting recordset property is CacheSize. This determines how many rows of data are buffered on the client machine.

Increasing this from the default of one allows the server to work more efficiently by returning a whole bunch of rows to the client at once, as opposed to one per shot.

However, don't forget - this means the information you store locally may not always the most up-to-date from the server. So in a multi-user environment, this could lead to update conflicts.

Check us out in a fortnight for the third part in our print-and-collect series. We'll be covering; views, defaults, user defined types and rules!