Q.  What's the main difference between ACCESS and SQL SERVER?

Ans:  As said before access fulfill all of the CODD rules and behaves as a true RDBMS.  But there's a huge difference from architecture perspective, due to which many developers prefer to use SQL SERVER as a major rather than access.  Following is a list of architecture difference between them:

Access uses file server design and SQL SERVER uses the Client/Server model.   The major difference between SQL SERVER and ACCESS.

NOTES: Just to clarify what is client server and file server.   I will make a quick description of widely accepted architectures.  There are three types of architecture:

• Mainframe architecture(This is not related to the above explaination but just mentioned it as it can be useful during interview and also for comparing with other architectures)
• File sharing architecture (Followed ACCESS)
• Client Server architecture (Followed SQL SERVER)

In Mainframe architecture all the processing happens on central host server.   User interacts through dump terminals which only send keystrokes and information to host.  All the main processing happens on the central host server.  So advantage in such type of architecture is that you need least configuration clients.  But the disavantage is that you need robust central host server like mainframe.

In file sharing architecture which is followed by access database, all the data is sent to the client terminal and then processed.  For instance you want to see customers who stay in VietNam, in File Sharing architecture all customer records will be send to the client PC regardless whether the customer belong to Vietnam or not.  On the client PC customer records from Vietnam is sorted/filted out and displayed, in short all processing logic happens on the client PC.  So in this architecture the client PC should have heavy configuration and also it increases network traffic as lot of data is sent to the client PC.  But advantage of this architecture is that your server can be of low configurations.

1. Client sends request for data to access with certain criteria.
2. Access Database streams data to client regarless of the criteria.
3. Criteria is applied on the access streamed data and displayed to the client.

In the client server architecture the above limitation of the file server architecture is removed.  In client server architecture you have two entities client and the database server.  File server is now replaced by the database server.  Database server takes up the load of processing.  Anydatabase related activity and the client any validation aspect of database.  As the work is distributed between the entities, is increases scability and reliability.  Second the network traffic also comes down in compared to file server.  For example if you are requesting customers from Vietnam, database server will sort/filter and send only vietnam customer detail to the client, thus briging down the network traffic tremendously.  SQL SERVER follows the client server architecture.

Client Sends requeste for data to SQL Server with certain criteria

Example IBM Compatible  roundtrip to SQL SERVER Database.

Client Server Architecture of SQL Server

Second issue comes in terms of reliability.  In Access the client directly interacts with the access file, in case there is some problem in middle of transaction there are chances that access file can get corrupt.  But in SQL Server the engine sits in between the client and the database, so in case of any problems in middle of the transaction it can revert back to its original state.

NOTE: SQL SERVER maintain a transaction log by which you can rever back to your original state in case of any crash.

When your application has to cater to huge load demand, highly transactional environment and high concurrency then its better go for SQL SERVER or MSDE.

But when it comes to cost and support then access stand better than SQL SERVER.  In case of SQL Server you have to pay per client license, but access runtime is free.

Summarizing: SQL SERVER gains points in term of network traffic, reliability and scalability vice-versa access gains points in term of cost factor.

Q.  What's the difference between MSDE and SQL SERVER 2000?

Ans:  MSDE is a royalty free, redistributable and cut short version of the giant SQL SERVER databae.  It's primarity provided as low cost option for developers who need database server which can easily be shipped and installed.  It can serve as good alternative for Microsoft Access Database as it over comes a lot of problemss which Access has

Size of database:-

• MS Access and MSDE have limitation of 2GB, SQL Server has 1,048,516 TB1. 
• Performance degrades in MSDE 2000 when maximum number of concurrent operations goes above 8 or equal to 8.  It does not mean that you can not have more then eight concurrent operation but the performance degrades.  Eight connection performance degradation is implemented by using SQL SERVER 2000 work load governor (we will have to looking into to more detail of how it works).  As compared to SQL SERVER 2000 you can have 32, 767 concurrent connections.
• MSDE does not provide OLAP and Data ware housing capabilities.
• MSDE does not have support facility for SQL mail.
• MSDE does not have GUI administrative tool such as enterprise manager, query analyzer or profiler.  But there are round about ways by which you can manage MSDE 2000.
•   Old command line utility OSQL.EXE,
•   VS.NET  IDE server explore: Inside VS.NET IDE you can have a functionality which can give youa nice GUI administrater tool to manage IDE
• SQL SERVER WEB Database administrator installs a web based GUI which you can use to manage your database.   For any details refer http://www.microsoft.com/downloads/details.asp?familyid=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en
• SQL-DMO objects can be used to build your custom UI.
• There are a lot of third party tools which provide administrative capability GUI, which is out of scope of the book as it's meant for interview questions.
• MSDE does not support Full text search.

Please note: There are two major differences first is the size limitation (2GB) of database and second are the concurrent connections (eight concurrent connections) Which are limited by using load govenor .  During interview this answer will suffice if he is really testing your knowledge.

Q.  What is SQL SERVER 2005 Edition?

Ans:

Twist: What's difference between SQL SERVER Express 2005 and MSDE 2000

NOTE:  Normaly conmparison  is when the product is migrating from one version to other version.  When SQL SERVER 7.0 was migrating  to SQL Server 2000, asking differences was one of the favorite questions.

SQL SERVER Express edition is a scaled down version of SQL SERVER 2005 and next evolution of MSDE.

Listed are some major difference between them:

• IN term of programming language support MSDE has only TSQL, but SQL SERVER Express has TSQL and .NET.  In SQL SERVER Express 2005 you can write your stored procedures using ,NET
• SQL SERVER Express does not have connection limitation which MSDE had and was controlled through the work load govenor.
• There was no XCOPY support for MSDE, SQL SERVER Express has it.
• DTS is not present in SQL SERVER Express while MSDE has it.
• SQL SERVER Express has reporting services while MSDE does not.
• SQL SEVER Express has native XML support while MSDE does not.

NOTE: Native XML support mean now in SQL SERVER 2005

• You can create a field with data type"XML".
• You can provide new XML manipulation technique like "XQUERY" also called as "XML QUERY".

There is complete chapter on SQL SERVER XML support so till then this will suffice.

Sumarizing:  Major difference is database size (2GB and 4GB), support .NET support in stored procedures and native support for XML.  This is much can convince the interviewer that you are clear about the differences.

Q. What is SQL SERVER 2000 Workload Govenor?

Ans:  Workload governor limits performance of SQL SERVER Desktop engine (MSDE) if the SQL engine receives more  load than what is mean for MSDE.  MSDE was always meant for trial purpose and non-critical projects.  Microsoft always wanted companies to buy there full blown version of SQL SERVER so in order that they can put limitation on MSDE performance and number of connections they introduced workload governor.

Worload governor sits between client and database engine and counts number of connection per database instance.  If workload governor finds that the number of connection exceeds eight connections, it starts stalling the connections and slowing down the database engine.

NOTE:  It does not limit the number of connections but makes connections request go slow.  By defaul 32, 767 connections are allowed bith SQL SERVER and MSDE.  But it makes the database engine go slow above eight connections.

Q.  What's the difference between SQL SERVER 2000 and 2005?

Ans:

Twist: What's the difference between Yokon and SQL SERVER 2000?

NOTE:  This question will be one of the favorites during SQL SERVER interview.  I have marked the points which should be said by developers as PG and DBA for Database Administrator.

Following are the some major differences between the two versions:

• (PG) the most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, User-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language(VB.NET, C#, J# etc.).  This supportwas not there in SQL SERVER 2000 where only language was in TSQL.  In SQL SERVER 2005 you have support for two languages T-SQL and ,NET.
• (PG) SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000.  It was a seperate installation for SQL SERVER 2000.
• (PG) SQL SERVER 2005 has introduced tow new data types virbinary (max) and XML.  If you remember in SQL SERVER 2000 we had image and text data types.  Problem with image and text datatypes is that they assign same amount of storage irrespective of what the actual data size is.  This problem is solved using varbinary (max) which arts depending on amount of data.  One more new data type is included "XML" which enables you to store XML documents and also does schema vertification.  In SQL SERVER 2000
• developers used varchar or text data type and all validation had to be done programmatically.
• (PG)SQL SERVER 2005 now can process direct incoming HTTP request with out ISS web server.  Also stored procedure invocation is enabled using SOAP protocol.
• (PG) Asynchronous mechanism is introduced using server events.  In Server event model the server post an event to the SQL Broker Service, later the client can come and retrieve the status by querying the broker.
• For huge database SQL SERVER has provided a cool feature called as "Data partitioning".  In data partitioning you break a single database object such as a table or an index into multiple pieces.  But for the clientapplication accessing the single database object "partitioning" is transparent.
• In SQL SERVER 2000 if you rebuilt clustered indexes even the non-clustered indexes where rebuilt.  But in SQL SERVER 2005 building the clustered indexes does not buil the non-clustered indexes.
• Bulk data uploading in SQL SERVER 2000 was done using BCP(Bulk copy program's) format files.  But now in SQL SERVER 2005 bulk data uploading uses XML format.
• In SQL SERVER 2000 there where maximum 16 instances, but in SQL 2005 you can have up to 50 instances.
• SQL SERVER 2005 has support of "Multiple Active Result Sets" also called as "MARS".   In previous version of SQL Server 2000 in one connection you can only have one result set.  But now one SQL connection you can query and have multiple result set.
• In previous version of SQL SERVER 2000, system catalog was stored in master database.  In SQL SERVER 2005 it's stored in resource database which is stored as sys object, you cannot access the sys object as in older version we were accessing master database.
• This is one of the hardware benifits which SQL SERVER 2005 has over SQL SERVER 2000- support of hyper threading.  WINDOWS 2003 supports hyper threading; SQL SERVER 2005 can take the advantage of the feature unlike SQL SERVER 2000 which did not support hyper threading.

NOTE:  Hyper threading is a technology developed by INTEL which creates two logical processors on a single physical hardware processor.

• SMO will be used for SQL Server Management.
• AMO(Analysis Management Objects) to manage Analysis services server, data sources, cubes, dimensions, measures, and data mining models.  You can map AMO in old SQL SERVER with DSO(Description Support Objects). NOTE: SMO, AMO, And RMO are all using .NET Framework.
• SQL SERVER 2005 uses current user execution context to check rights rather than ownership link chain, which was done in SQL SERVER 2000.
• In previous versions of SQL SERVER the schema and the user name was the same, but in current the schema is seperated from the user.   Now the user own schema.
• Query analyzer is now replaced by query editor
• Business Intelligent development studio will be used to create Business Intelligent Solution.
• OSQL and ISQL command line utility is replaced by SQLCMD utility.
• SQL SERVER Enterprise manager is now replaced by SQL SERVER Management Studio.
• Server Manager which was running in system tray is now replaced by SQL Computer Manager.
• Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.
• In SQL SERVER 2005 Indexes can be rebuild online when the databaase is in actual production.  If you look back in SQL SERVER 2000 you cannot do insert, update, and delete operations when you are building indexes.
• (PG) Other than Serialiable, Repeatable Read, Read Committed and Read Uncommitted isolation level there is one more new isolation level "snapshot Isolation level".

Summarizing:  The major significant difference between SQL SERVER 2000 and SQL SERVER 2005 is in term of support of .NET Intergration, snapshot isolation level, Native XML support, Handling HTTP request, Web Service support and Data partitioning.  You do not have to really say all the above points during interview, a sweet summary and you will rock.

Continuing in Database Questions and Answers Concepts Part3.  and don't forget check out my personal website: http://m.aatechsources.com Article and Forum