Main Considerations When Moving From MS Access Programming to SQL Server

I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store all data and do more of the 'work'.

In essence my question is; what subject matters do I need to know for SQL Server that I probably didn't learn or need when using Access?

Not looking for you to tell me how to do anything, more what you think are the most important things I should go an research - there's a lot of subjects and a hell of a lot of detail, don't want to find myself a long way down a less valuable path...

Brain dump:

• Maintenance: what are the most important (check database, reduce

database, update statistics, rebuild etc)

• Indexes - I don't know as much as I should, is there a good book/

blog etc that can teach me the basics upwards?

• Anything else I have missed (there's probably lots, as I said I am

new to SQL Server...)

If it helps I work for a mid sized retailer and the databases I predominantly work on cover such things as

• Reporting platform (summarises sales/ receipts/ inventory etc from

main system and provides fast reporting)

• A reconciling tool between a third part and what our stores put

through registers (imports data from third party and cross references
the transaction logs)

• Stores all data to do with our promotions; product, prices,

projections, actual results etc

As you learn more about SQL Server you will discover (to your delight) a number of things you can do in SQL Server at the database level that you previously had to do in Access at the application level. Some examples include:

Triggers: Procedures defined at the table-level to make stuff automatically happen whenever there is an INSERT/UPDATE/DELETE on the table.

Stored Procedures: Somewhat similar to Access macros and those little VBA procedures (Subs) you built in Access to do "useful stuff", except that in SQL Server they are part of the database itself. So, you can write a Stored Procedure (SP) once and use it (almost) anywhere, even across applications that share the same database.

Functions: These are somewhat analogous to the little VBA Functions you wrote in Access to incorporate into queries, except that SQL Server Functions, like SPs, are more tightly bound to the database. Also SQL Server Functions can be Scalar-valued (return a single scalar value) or Table-valued (return a rowset).

Fun SQL tricks: There are lots of SQL features available in SQL Server that are not supported in Access (Jet/ACE) SQL. "Common Table Expressions" (CTEs) and "RANK and PARTITION" are the ones that gave me "'aha' moments" as I was getting started.

I could go on, but these are the things I remember discovering early on that got me "thinking in SQL Server".

Don't get me wrong, I still think Access is an excellent tool when used appropriately, and should be given serious consideration as a way to

-
build (or just mock up) front-end applications against a SQL Server back-end, and

-
do data cleanup, create ad-hoc queries and reports, and perform similar tasks on your SQL Server data.

Finally, even though you will be working with SQL Server 2005 I highly recommend that you get a copy of SQL Server 2008 (or 2012) Express for learning, mainly because of the IDE enhancements in SQL Server Management Studio (namely, auto-completion and interactive debugging). Just be aware of any newer (2008+) features you may encounter and don't rely on them for your production code. (Or, use them as bullet points for your pitch to upgrade from SQL Server 2005....)

StackExchange Database Administrators Q#39611, answer score: 5