Transition from MSAccess to SQL Server - Advise?

Greetings,

I am looking to begin learning SQL Server 2005 and hoping to find a book that will build off of my Access & JetSQL foundation.

Are there any great books out there that have that approch?

(Sam's Learn ASP.NET in 21 Days, for example, has a section at the end of each chapter that says, "That's not ASP!" which highlights the differences between classic ASP and ASP.NET - so you can see the differences in bold print)

Thank you in advance,


~Melagan
______
"It's never too late to become what you might have been.

 

[gmmastros]

There is a FAQ's for books. faq183-3324

The other FAQ's are good too.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Best thing you can do is stop using the query builder (if you use it now). I learned SQL before I learned access, so I never really picked up this habit, and I think it has helped me tremendously.

Some differences that come immediately to mind:

iif does not exist in SQL Server. CASE statement accomplishes the same thing though.

mid does not exist either. SUBSTRING is the equivalent.

isnull behaves differently

joining multiple tables is not such a clumsy exercise in parentheses

you don't have access to all the other vba functions

the database engine is vastly superior. There are some things I have run into that you simply can't do in access, but SQL Server has no problem with.

There are a million other differences, but I am sure you can figure those out when you run into them (and if not, there's tek-tips!)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[LNBruno]

...and dates!!! For the love of all that is sanity, read the FAQs about dates in SQL Server!



< M!ke >
Acupuncture Development: a jab well done.

 

[SQLSister]

Learn about the datatypes in SQL Server; there are real differnces between what is available in SQL server and what Access uses.

Security is another issue that is very different and that you need to understand thoroughly.

And finally consider learning about performance tuning. This isn't something most Access developers ever have to consider due to the size of the databases. But it is a critical issue for SQL Server and it is better to learn from the start what are the better ways to write the SQL so that performance won't become an issue. (And skip any chapters in any books you read that deal with cursors. You are better off not learning about them until you are expert enough to know that you almost never need them.)



Questions about posting. See faq183-874

 

[Melagan]

I thank you all so much for the great feedback! My next question becomes this: Most of the recommended books teach SQL Server 2000, including the books listed at

http://www.sql-server-performance.com/self_taught_dba.asp

I know the "current" version is '2005', and I heard there is a planned release of a '2008' version. This begs the question - is it worth spending all of that time, money, and effort, to learn SQL Server 2000, when it is nearly two versions old?

Or... is it like Office 2000 compared to Office 2007, with the core programs not being too terribly different, just some added bells and whistles?

~Melagan
______
"It's never too late to become what you might have been.

 

[Crowley16]

2005 is completely different to 2000, well almost...

however do keep in mind that there's still quite a few companies out there on 2000 or less, so it might be worth while knowing a bit about them...

--------------------
Procrastinate Now!

 

[AlexCuse]

While the query language is the same, there are a LOT of new features in 2005 that are most certainly not 'bells and whistles'. In my opinion, SQL 2000 will probably be fairly intuitive for you (at least from the programming side) if you SQL skills are strong. SQL 2005 will be also, but you don't want to become reliant on the new features right out of the gate. As Crowley16 says, there are many companies out there still on 2000, so if you are reliant on some of the newer stuff you could end up SOL.

Do you want to get into programming or administration? That will play a big role in how to proceed.

What I would do if you want to get into the programming side is pick up a copy of this book, download SQL 2005 express edition (its' free), and start trying to build some of the access databases you know and (maybe) love in SQL Server. This will prepare you better than simply reading any book ever could.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

2005 has some additional functionality
new isolation level
partitioning functions
windowing functions
try catch error trapping
dynamic managment views (IMHO the best new feature)
Big Time XML improvments
varchar(max), no more nasty text dataype manipulation nonsense
DDL triggers
SQLCLR
online index rebuilding
and much more


it is not bad to learn 2000 since whatever you learn there you can apply in 2005

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[Melagan]

Alex ~~

I'm interested with more focus on the programming than administration side. I am really wanting to learn SQL Server mostly to support my other focus, which is ASP.NET. Ultimately, I want to learn how to build a web application with SQL Server backend to do just about anything - from eCommerce and online stores to company intranet applications.

The level I'm currently at is building light-weight small business sites and hobby sites with an MSAccess backend =\

~Melagan
______
"It's never too late to become what you might have been.

 

[AlexCuse]

Melagan - I think in your situation you would do well to do exactly as I suggested in the last paragraph of my previous post. Just do it on a few of the 'hobby' sites first as practice ;-)

IMO, Guru's Guide is THE book to have for anyone interested in t-sql programming, so it can not hurt to have that on your shelf. SQL Server has a great help file (commonly referred to here as BOL) as well.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom