Help SQL SERVER 6.5/VB 5.0
Quote:
> 1. converting an Access 2.0 database to SQL Server 6.5?
Supposedly there is an 'upsizing wizard', but I've never seen it.
Quote:
> 2. We currently use a number of "direct seeks on indexes" in the
>existing app. using Access 2.0. (Example - Table in excess of
>200,000 records) Are we going to have a performace
>degredation using sql and being unable to use the direct
>seek?
No, your performance should actually be _better_, assuming you index
your tables correctly in SQL Server. (BE WARNED: SQL Server is NOT for
beginners; it's probably a good idea to send someone on a SQL Server
course before you even CONSIDER using it!)
Quote:
> 3. if you are using a sql statement to search for example on a
>customer number = "XXXXX" and the number is the primary
>key, does the index come into play in the sql?
It depends. If your customer number is the first column in the primary
key index, yes, it will use the index, but only if your query is
likely to return less than a certain percentage of the rows in the
table. If you have a composite key and customer number is not the
first column, or you don't have your WHERE clause in the correct
order, or your query will return a large percentage of the records,
the index will not be used. (There are a whole load of other rules as
well, but for simple equality selects as above, the index will almost
always be used, assuming it's constructed correctly).
For example:
Your primary key (i.e. clustered) index is:
supplier_id, customer_id
If you only use customer_id in your WHERE clause, the index will not
be used, as SQL Server only stores statistics for the first column in
an index.
If you use both supplier_id and customer_id in your WHERE, in that
order, there is a good chance the index will be used.
If you SET SHOWPLAN ON while running queries in ISQL /w, you will be
able to see whether the index is being used or not.
Performance tuning of this type on any big RDBMS is, as you can see,
very much a black art! Get your cat and your cauldron ready. :-)
Quote:
> 4. any suggestions on the approach we should use in the conversion
>from VB 3.0 to 5.0?
Nope!
