Moving to MS SQL from Microsoft Access...

start of a learning curve

[SuzyUK]

I'm a looking for some help to get my head around something before I go off on a wrong track.

I use Access, I like it, I know it.. but obviously needs must sometimes and after reading around I realise that I may have to move to a more "web able" database sometime?

I have a Windows host which does offer an SQL database too, which after reading their documentation I presume is MS SQL?

I have (or will have in a few days) access to a standalone Windows 2003 server to "practice/play" on so this isn't going to be done yesterday..

Where does Transact SQL come into this?
can I still design (the theory) in Access and transport the same logic/SQL queries over to MS SQL?
Do relationships/joins work the same?
Are "Queries" now "stored procedures"?
Should I forget everything I "know" already ;)?

All recommendations for thought processes gratefully received and I need to get a "bible" so recommendations of any of these would be handy too..

Thanks
Suzy

[topr8]

i changed over about 6 months ago and was really excited by it, i think you'll find sql-server much more powerful.

you can buy a developer edition of sql-server which i did to install locally here to test things with, by shopping around i found it very cheap, alternatively you get a 120 trial with the download from the ms site, you need one or the other to get the enterprise manager and query analyser which you can use to connect to your live database (i'm sure other tools are available but i've found those to be excellent)

... the data types are slightly different, so i'd design in a local version of sql-server rather than access, once you've jumped you won't want to go back.

transact-sql is the ms version of sql, eg. standard sql with a couple of exceptions and a few add-ons.

relationships work the same way.
queries are now views - stored procedures are a big jump ahead, and for me took a while to get my head around as it involved a new syntax.

>>Should I forget everything I "know" already?

absolutely not, the two are very similiar.

a book i found helpful was: "transact-sql cookbook"
i also use "sql in 10 minutes" as a reference when i forget the basics (it's an age thing!)

good luck and enjoy!

[IanTurner]

Stored Procedures are one of the most important advantages of SQL Server over Access. You can query data in your ASP code directly using SQL statements, but if you write the recordset query inside a stored procedure performance is vastly improved.

[duckhunter]

Join syntax is a little different. You should move towards ANSI standards.

Here's an example:
Select t1.field1, t2.field1, t3.field2
from table1 t1 with (nolock)
join table2 t2 with (nolock)
on t1.id = t2.id
join table3 t3 with (nolock)
on t2.code = t3.code

* Note the with (nolock) hints. This will prevent record or table locking on long running queries. It does allow for a "dirty read". If the underlying records are updated during the select, you will not see them so only use these hints when you can afford the "dirty" read and you will see improvements in speed and scalability.

Also, make sure your tables have indexes that cooperate with your where clauses. If you are always searching a table as: "where field1 = 2" then make sure field1 is indexed. If you are always searching as "where field1 = 2 and field2 = 4" then create an index for Field1/Field2.

Make sure your Primary and Foreign keys are defined too. This will help with data integrity and speed as well. If Table1.ID is the Primary Key, make sure there is a Foreign Key defined for Table2.ID.

Small learning curve but once you get the knack, you'll wonder what took you so long. Good luck!

[woop01]

In addition to what the others said above, don't forget to reindex your databases every now and then. I didn't know about that for about six months after I switched and it had a huge impact on performance when I finally did it.

[SuzyUK]

Thanks everyone..

I've discovered MSDE which seems to be the "middle step" and although it doesn't have Enterprise Manager I'm looking for some utilites to make it easier for me to work with.. I haven't even managed to set up a database yet LOL, but I have the Desktop Engine server running..

Web Host even with support for SQL server doesn't have a management console? so some form of 3rd party application is still needed.. I guess it's going be a big curve..

I'm a little lost at the minute but will be trying to get something working and this thread is bookmarked for all the helpful suggestions when I do.. I keep hoping the lightbulb will go on soon ;)

Suzy

[woop01]

I like to access my MS-SQL Servers with Microsoft Access projects even though it's not the most efficient thing in the world. If you're used to using Access, it's a great way to make the switch.

[Dreamquick]

Suzy for a beginner with MSSQL I'd suggest trying to get ahold of the regular client tools (enterprise manager + query analyser) if you possibly can because they are an invaluable addition to your toolbox from day one.

In a perfect world SQL just works, but when it doesn't or when you need to configure something outside of scripting then those tools are your best friends.

- Tony

[duckhunter]

Dreamquick is right. It would prove very difficult to "manage" a server without Enterprise Mgr.

You can transfer databases/tables from you Host's online server to your local box with a few clicks and visa versa. Create tables with a GUI that is very similar to Access. Manage indexes, permissions, primary and foreign keys, etc., etc, etc.

You can do it with Query Analyzer only but even after working with SQL for almost 10 yrs, I wouldn't want to. Lots to configure and the Enterprise Manager lets you do it quickly without writing scripts.