Ok so i signed up with my host, on a nt server for cfm because i started learning it other day and d*mn its easy but the problem is i only know some mysql from sitepoints article on php/mysql. First does SQL server work the same way as MYSQL? Like is there a difference and Accessa came on my computer this is the same thing that is on my server? If it is do i just make something in it on my pc and upload it?
For basic DDL, SQL Server and MySQL work similarly.
SQL Server doesn't have all the extraneous column type synonyms that MySQL has but you can create those on your own if you have to have the same column types.
SQL Server Supports sub-selects meaning your SQL queries can be optimized to run faster.
SQL Server supports Stored Procedures. Meaning your SQL queries can be stored in a compiled form within the database allowing you to manipulate your data before it ever gets to your Script. This provides for faster and more efficient queries. SQL Server has its own programming language to support this called Transact-SQL.
SQL Server supports triggers. Triggers happen when you insert, update or delete a row from a table. These are special stored procedures. By using Triggers you can validate your data before changing the database, return errors if necessary, call other procedures and create entire database applications. I.E. if a new customer is added you can create a shopping cart entry for them, create a profile, pre-approve their credit card number, and a lot of other things all from one form entry.
There are other minor changes i.e. Auto_Increment fields are called Identity Columns in SQL Server. I would suggest getting a book on SQL Server if you plan on using it. Make sure it covers Transact-SQL because you'll find that learning it will enable true Client-Server Applications on the web.
PHP also has SQL Server functions that enable it to connect and communicate with the database. These are similar to the MySQL functions.
If you do use Access you would create the database on your PC and upload the MDB file to the server to use it. You would then connect through a DSN to access the database from CFM. With Access you can also store your queries in the database and have them compiled for faster performance. Just know that with Access 97 your limited to approximately 20-25 concurrent connections. With Access 2000 you might be able to stretch that to 40 concurrent connections. The trick around this is to keep your connections short and close them when done. While this will cause a little more overhead on the server, you will be able to handle up to 100-150 concurrent users on your site. Anything more you have to upgrade. Access 2000 comes with a "SQL Server Upsizing" tool that will convert your database to SQL Server for you which is really handy when the time comes to upgrade.