Difference between SQL server and Access

[spn200286]

I have taken on the project of creating a hotel management system, that will allow different access levels for different levels of users (e.g. managers all reports, receptionists just booking, chefs meals etc).
i have been researching different formats, and was thinking of SQL server, the question is would this be a better option the using access 07(or 97-03 compatability mode)
form what i have seen SQL server does offer better security options as standard, tho i may be mistaken

any advice/guidance given would be appreciated

 

[KenHigg]

Yes, security is more robust in SQLSever but that is just the tip of the iceberg.

Here in post 9 is one of the more signifcant upsides to incorporating SQL server in your solution:

Link

fyi - Sounds like you're about to embark on quite an undertaking. May I suggest you to consider off the shelve solutions first (or at least research those options) -


ken

 

[spn200286]

cheers for that KenHigg

i would like off the shelf, but its my final year uni project, so it has to be reasonably complicated/fun fun fun if i want to pass with a good grade.
untill i can download SQL server (legally form msdn fty) is there much of a difference in interface between the versions?

 

[KenHigg]

I think you can get an express version of SQL Sever and I'm pretty sure that for what you're doing there will not be much difference between that and one of the full scale versions...


ken

 

[spn200286]

I think you can get an express version of SQL Sever and I'm pretty sure that for what you're doing there will not be much difference between that and one of the full scale versions...


ken

well i get for free so no hair off my nose

 

[RoyVidar]

KenHigg,

I'm looking at the illustration in your link (post #9), which is wrong.

As long as you know what you're doing (indices), Jet will return 15 records, not the whole table.

 

[KenHigg]

What part exactly do you feel is wrong?


ken

 

[RoyVidar]

No feelings involved

The upper half of that picture is wrong. Jet will not bring all 10k records over to the client as long as you know what you're doing. With proper indexing, Jet will bring only the 15 records required.

Se for instance the thread "Does a filtered mdb recordset still bring down the whole recordset?" http://www.access-programmers.co.uk/forums/showthread.php?t=127572

 

[KenHigg]

You seem pretty adamant that the illustration is categorically wrong. I maintain that it does represent one of the primary differences in Access vs. SQL Server. I will yield though and remove it if others feel it is doing more harm than good...

You have sparked my interest in index's though...

When you create an index I have always thought that this was basically like having another column added to the table. Say like in the following example:

No index

tblMain
RECORD_NUM      COLOR
001             red
002             green
003             blue
004             red
005             black
006             yellow
007             blue
008             green
009             red
010             yello

With COLOR column indexed

tblMain
RECORD_NUM      COLOR        INDEX
001             red          6
002             green        4
003             blue         2
004             red          7
005             black        1
006             yellow       9
007             blue         3
008             green        5
009             red          8
010             yellow       10

Is this the way you understand a basic index to work?


ken

Edit:

Or would it make more sense for the index to be saved in it's own structure like:

COLOR_INDEX
005
003
007
002
008
001
004
009
006
010

???

 

[RoyVidar]

If I recall correct, an index, in classic sence, was a copy of the actual column(s) that was loaded into memory to spead up searches, sorting and dataretrieval. The index was searched first, if/when a match was found in the index, then the relevant/required/specified columns of that row was read from the disk. This proved much faster than sequential read of the entire table on the disk.

I don't know how Jet physiacally creates and maintains indices (very little of the inner workings of Jet is publicly known), but it works the same way. When you fire off a query, it will examine the indices first. Unless you've done something stupid, say like filtering on an expression or unindexed fields, which may force Jet to transfer the whole table, Jet will transfer only the rows you request.

In the referenced thread there are both practical samples and code to reproduce results that should easily verify that there is a significant difference between pulling the whole table or filtering on a non-indexed field and filtering on an indexed field.

So, the statements

2 All 10K records are sent back over the network to the local workstation
3 MS Access on the local workstation runs the actual query against the 10k records and returns to the user, the 15 they requested

are true only if you don't use indices, filter on non indexed fields, filter on expressions or in other ways create filters that prevents Jet from using indices. And who among us would do things like that?

 

[KenHigg]

So you think jet has to pull the entire index over first?

 

[RoyVidar]

I don't know. Sounds logical, though, but at least it isn't pulling the whole table.

 

[KenHigg]

I do know from experience that indexes are to be used sparingly. And the larger your tables are and the more relationships you’re trying to maintain the more true it becomes.

Interesting. I set up some test tables and indexes with some really simple .mdb's and while you can see the data in the .mdb in a text editor I couldn't see anything resembling indexes...

MS need s a white paper on it -

 

[RoyVidar]

To add a bit of info, David W. Fenton did a very good explanation here http://groups.google.com/group/comp.databases.ms-access/msg/8eb30fbefdd05f96 where he even speaks about the retrieval of indices (suspecting Jet might be smart enough to not load the whole index) - that reply is probably among the better explanations/sumups around!

In the same thread, you can find a couple of replies by MichKa, with more details, for instance this http://groups.google.com/group/comp.databases.ms-access/msg/84d9aa50d6c774be

In another thread, with a bit more detail about how Jet works in the stages before retrieving the selected records http://groups.google.com/group/comp.databases.ms-access/msg/3d9c7f10a0ddb718