What are the main differences between Access and SQL Server?
This article will try to explain some of the differences between Access and SQL Server. It is not an exhaustive list, and in no means should be considered an ultimate authority (you can also see this MSDN article for a more through treatment of the differences). If you have anything to add or correct, please let us know...
Here is a list of data types in each environment, and how they are different. Some datatypes from SQL Server were left out (e.g. SQL_VARIANT, TABLE).
Switching from Yes/No to BIT
MM/DD/YYYY format is strongly discouraged, because of the inevitable confusion (many such dates can look like DD/MM/YYYY to Canadians or Brits). The only safe format to use is YYYY-MM-DD in Access, and YYYYMMDD in SQL Server. See Article #2260 for more information. There is also further information on this later in this article.
In addition, Access allows you to store date and time independently. SQL Server, including SQL Server 2005 ("Yukon"), does not allow this (see Article #2206 for more info). To see if a date equals 2001-11-05 in SQL Server, you would have to convert the stored value (which includes time) to a date only. Here is how a typical query would have to change:
If you want to retrieve the current date and time, the syntax is slightly different:
If you want just the time:
If you want just today's date, there are other options:
To get tomorrow's date, here is how your queries would look:
To get the date and time 24 hours from now:
To get the first day of the current month:
To get the number of days in the current month:
To get the current millisecond:
To get the current weekday:
It would be nice if you could use the same syntax against both data sources, but alas that is not the case. Try using the following in Access:
The first should generate an error, the second should work fine. Now check out the problems when you use the following syntax in SQL Server:
Now, there is a way to make YYYY-MM-DD safe in SQL Server, and it involves using the canonical format for dates:
However, I don't believe Access will accept this format, so we're back to square one: writing different code for each database (I guess we were there already, since Access requires # delimiters -- but it would be nice if the formats were consistent).
Like switching from Currency to Money, when you present dates in SQL Server, you lose the convenience of the FORMAT() function, which accepts multiple ways of formatting a date (e.g. ). For more information on how to overcome this change, see Article #2464 for a cheat sheet of available formatting options with CONVERT(), and Article #2460 for a roll-your-own function that mimics the FORMAT() functionality, and then some.
(Yes, you can enable double-quote characters as string delimiters, but this requires enabling QUOTED_IDENTIFIERS at each batch, which impacts many other things and is not guaranteed to be forward compatible.)
Another change is the ability to concatenate NULL values to a string. If you do this in Access:
If either FirstName or LastName is NULL, you will still get the portion of the string that was not NULL. In SQL Server:
If any of the values is NULL, the whole expression will yield NULL (unless you change the default setting for CONCAT_NULL_YIELDS_NULL). A common workaround is to use COALESCE around each potentially NULL value:
(Or, avoiding NULLs in the first place... see Article #2073.)
Built-in CHR() constants in Access change slightly in SQL Server. The CHR() function is now spelled slightly differently. So, to return a carriage return + linefeed pair:
This one is confusing for many people because the CHAR keyword doubles as a function and a datatype definition.
Another thing to note is that Access can use & or + for string concatenation. SQL Server uses & for Boolean AND, so you need to use + for all string concatenation in SQL Server. Also, keep in mind that 'string' + NULL = NULL, so you should always use COALESCE() on column names / values that might be NULL, in order to avoid setting the whole result to NULL.
CINT(data) -> CAST(data AS INT)
This function converts NUMERIC data that may be stored in string format to INTEGER format for comparison and computation. Remember that SQL Server is much more strongly typed than VBA in Access, so you may find yourself using CAST a lot more than you expected.
INSTR(data, expression) -> CHARINDEX(expression, data)
This function returns an integer representing the character where the search expression is found within the data parameter. Note that the order of these parameters is reversed!
This function returns 1 if the supplied parameter is a valid date, and 0 if it is not. Aside from delimiters, the syntax is identical.
This function works a bit differently in the two products. In Access, it returns 1 if the supplied parameter is NULL, and 0 if it is not. In SQL Server, there are two parameters, and the function works more like a CASE statement. The first parameter is the data you are checking; the second is what you want returned IF the first parameter is NULL (many applications outside the database haven't been designed to deal with NULL values very gracefully). The following example will return a 1 or 0 to Access, depending on whether 'column' is NULL or not; the code in SQL Server will return the column's value if it is not NULL, and will return 1 if it is NULL. The second parameter usually matches the datatype of the column you are checking.
A more intuitive function to use in SQL Server is the ANSI standard COALESCE() function. Not only does it allow you to substitute a value when a NULL is found, it will allow you to step through a series of possible values, and stop at the first non-NULL.
This function returns 1 if the supplied parameter is numeric, and 0 if it is not. The syntax is identical.
This function returns the leftmost n characters of data. The syntax is identical.
This function returns the number of characters in data. The syntax is identical.
LCASE(data) -> LOWER(data)
This function converts data to lower case.
This function removes white space from the left of data. The syntax is identical.
REPLACE(data, expression1, expression2)
This function scans through data, replacing all instances of expression1 with expression2.
This function returns the rightmost n characters of data. The syntax is identical.
This function removes white space from the right of data. The syntax is identical.
CSTR(data) -> STR(data)
This function converts data to string format.
MID(data, start, length) -> SUBSTRING(data, start, length)
This function returns 'length' characters, starting at 'start'.
UCASE(data) -> UPPER(data)
This function converts data to upper case.
This function converts a string into 'proper' case (but does not deal with names like O'Hallaran or vanDerNeuts). There is no direct equivalent for StrConv in SQL Server, but you can do it per word manually:
There is a thread stored at Google dealing with proper casing an entire block of text; you could likely implement something like that in both Access and SQL Server.
This function combines both LTRIM() and LTRIM(); there is no equivalent in SQL Server. To mimic the functionality, you would combine the two functions:
Now, insert identical data into a similar table in Access 2000, and compare the SELECT results:
Notice the inconsistencies - Access (like Windows) treats underscore (_) as the highest non-alphanumeric character. Also, it ignores apostrophe (') and hyphen (-) in sorting. You can see the other slight differences in sorting this otherwise identical list. At least they agree on which names are first and last... if only all of our queries used TOP 1! Add on top of this that both database engines' concepts of sort order are sensitive to changes in the underlying operating system's regional settings. SQL Server is also variable in its server-level (and in SQL Server 2000, table- and column-level) collation options. So, depending on all of these variables, your basic queries that sort on a text/char/varchar column will potentially start working differently upon migration.
If you set ANSI_NULLS OFF and are trying to compare two columns, they won't equate. A column that contains a NULL will equate with an expression that yields NULL, as will two expressions that yield NULL. But two columns that contain NULL will never be considered equal, regardless of ANSI_NULLS settings or the ANSI standards. As a workaround, use the following comparison to determine that two columns are "equal" AND both contain NULL (without the extra AND condition, these two would also evaluate as equal if they both contained an empty string):
Yes, it's not pretty. For more information on how SQL Server handles NULLs (and why you might want to avoid them), see Article #2073.
OTHER SYNTAX CHANGES
There are possibly dozens of other slight syntax changes that may have to be made when moving from Access to SQL Server. Here are a few of the more significant ones:
SQL Server's CASE also supports multiple outcomes, for example:
To mimic this functionality in SQL Server, you will need to do a little more:
When creating tables and other objects, keep the following limitations in mind:
Stored queries in Access are a way to store query information so that you don't have to type out ad hoc SQL all the time (and update it throughout your interface everywhere you make a similar query). Being a non-GUI guy, the easiest way I've found to create a stored query in Access is to go to Queries, open "Create query in Design View", switch to SQL View, and type in a query, such as:
Be careful not to use any reserved words, like [name], as parameter names, or to give your parameters the SAME name as the column -- this can easily change the meaning of the query.
Once you have the same schema within SQL Server, when moving to stored procedures, the basic difference you'll need to know is syntax. The above stored query becomes:
You can create this stored procedure using this code through QUery Analyzer, or you can go into the Enterprise Manager GUI, open the database, open the Stored Procedures viewpane, right-click within that pane and choose New > Stored Procedure. Paste the above code (or a query that might make a bit more sense given *your* schema), click Check Syntax, and if it all works, click Apply/OK. Don't forget to set permissions!
Now in both cases, you can call this code from ASP as follows:
See Article #2201 for a quasi-tutorial on writing stored procedures.
Yes, Access has pretty little forms that you can create easily with VBA. There is no such thing in SQL Server; you will either need to develop an application (the most rapid to put together would probably be an ASP front end), or you could use Access as a front end (employing an Access Data Project, or ADP).
SQL Server has two authentication modes, and neither are much like Access security at all. You can use Windows Authentication, which allows you direct access to domain Users and Groups from within the interface. You can also use Mixed Mode, which allows SQL Server to maintain usernames and passwords (thereby negating the need for a domain or other Windows user/group maintenance).
Once you have determined an authentication mode, users have three different levels of access into the database: login (at the server level), user (at the database level), and object permissions within each database (for tables, views, stored procedures, etc). Just to add a layer of complexity, SQL Server makes it easy to "clone" users by defining server-wide roles, and adding users to that role. This is much like a Group in a Windows domain; in SQL Server, you can use the built-in definitions (and customize them), or create your own. Alterations to a role's permissions affect all users that are members of that role.
Microsoft has a thorough whitepaper you should skim through before jumping into SQL Server. If you're going to deploy your own SQL Server box (as opposed to leasing a dedicated SQL Server, or a portion of one), by all means read the SQL Server Security FAQ.
Article #2182 has a list of tools and tutorials that will aid in the migration process. Also be sure to read Microsoft's migration whitepaper for some helpful info from the vendors themselves. Finally, if you're into books, APress has a great title called From Access to SQL Server.
Related ArticlesAre there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?