CoolInterview.com - World's Largest Collection of Interview Questions
Send Free SMS
 Interview Questions  
 Our Services  


INTERVIEW QUESTIONS DATABASE SQL SERVER DETAILS
Question :
What are the new enhancements in SQL server 2005?
Category SQL Server Interview Questions
Rating (2.4) By 5 users
Added on 4/3/2008
Views 821
Rate it!
Answers:

Comparing to SQL Server 2000 SQL Server 2005 have more features
1. Reporting?services are available
2. .Net integration
3.?HTTP protocols



1. SSIS,SSAS AND SSRS are available
2. .Net integration
3. HTTP protocols



 Posted by: UDHAYA    

Contact UDHAYA  Contact UDHAYA

Following are the some major differences between the two versions(Comparing to SQL Server 2000 SQL Server 2005 ):-
√(PG) The most significant change is the .NET integration with SQL SERVER
2005. Stored procedures, User-defined functions, triggers, aggregates, and user76
defined types can now be written using your own favorite .NET language
(VB.NET, C#, J# etc .). This support was not there in SQL SERVER
2000 where the only language was T-SQL. In SQL 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 two new data types varbinary (max)
and XML. If you remember in SQL SERVER 2000 we had image and text
data types. Problem with image and text data types is that they assign same
amount of storage irrespective of what the actual data size is. This problem is
solved using varbinary (max) which acts depending on amount of data. One
more new data type is included "XML" which enables you to store XML
documents and also does schema verification. In SQL SERVER 2000 developers
used varchar or text data type and all validation had to be done
programmatically.
√(PG) SQL SERVER 2005 can now process direct incoming HTTP request
with out IIS web server. Also stored procedure invocation is enabled using the
SOAP protocol.
√(PG) Asynchronous mechanism is introduced using server events. In Server
event model the server posts an event to the SQL Broker service, later the
client can come and retrieve the status by querying the broker.
√For huge databases SQLSERVER 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 client application accessing
the single data base 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 built 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 file format.
77
√In SQL SERVER 2000 there where maximum 16 instances, but in 2005 you
can have up to 50 instances.
√SQL SERVER 2005 has support of ?Multiple Active Result Sets? also called
as ?MARS?. In previous versions of SQL SERVER 2000 in one connection
you can only have one result set. But now in one SQL connection you can
query and have multiple results set.
√In previous versions 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 can not access the sys object directly as in older
version wewhere accessing master database.
√This is one of hardware benefits 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 servers,
data sources, cubes, dimensions, measures, and data mining models. You can
map AMO in old SQL SERVER with DSO (Decision Support Objects).
√Replication is now managed by RMO (Replication Management 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.
Note: - There is a question on this later see for execution context questions.
√In previous versions of SQL SERVER the schema and the user name was
same, but in current the schema is separated from the user. Now the user owns
schema.
Note: - There are questions on this, refer ? ?Schema? later.
Note:-Ok below are some GUI changes.
78
√Query analyzer is now replaced by query editor.
√Business Intelligence development studio will be used to create Business
intelligence solutions.
√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 database is
in actual production. If you look back in SQL SERVER 2000 you can not do
insert, update and delete operations when you are building indexes.
√(PG) Other than Serializable, Repeatable Read, Read Committed and Read
Uncommitted isolation level there is one more new isolation level ?Snapshot
Isolation level?.



 Posted by: Rahul Sharma    

Contact Rahul Sharma  Contact Rahul Sharma

Notification Services Enhancements


Notification Services is a new platform for building highly-scaled applications that send and receive notifications. Notification Services can send timely, personalized messages to thousands or millions of subscribers using a wide variety of devices.

Reporting Services Enhancements


Reporting Services is a new server-based reporting platform that supports report authoring, distribution, management, and end-user access.

New Service Broker


Service Broker is a new technology for building database-intensive applications that are secure, reliable, and scalable. Service Broker provides message queues the applications use to communicate requests and responses.

Database Engine Enhancements


The Database Engine introduces new programmability enhancements such as integration with the Microsoft .NET Framework and Transact-SQL enhancements, new XML functionality, and new data types. It also includes improvements to the scalability and availability of databases.

Data Access Interfaces Enhancements


SQL Server 2005 introduces improvements in the programming interfaces used to access data in SQL Server databases. For example, the SQL Native Client data access technology is new, and the .NET Framework Data Provider for SQL Server, also referred to as SqlClient, is enhanced.

Analysis Services Enhancements (SSAS)


Analysis Services introduces new management tools, an integrated development environment and integration with the .NET Framework. Many new features extend the data mining and analysis capabilities of Analysis Services.

Integration Services Enhancements


Integration Services introduces a new extensible architecture and a new designer that separates job flow from data flow and provides a rich set of control flow semantics. Integration Services also provides improvements to package management and deployment, along with many new packaged tasks and transformations.

Full-Text Search Enhancements


This section describes the programmability enhancements that have been made to Full-Text Search. These enhancements include the data definition language for Full-Text Search and the ability to specify languages in queries other than the default language, and the manageability enhancements, such as side-by-side installs, backup and restore full-text catalogs, and attach and detach full-text catalogs.

Replication Enhancements


Replication offers improvements in manageability, availability, programmability, mobility, scalability, and performance.

Tools and Utilities Enhancements


SQL Server 2005 introduces an integrated suite of management and development tools that improve the ease-of-use, manageability, and operations support for large scale SQL Server systems.



 Posted by: ayan basu    

Contact ayan basu  Contact ayan basu


If you have the better answer, then send it to us. We will display your answer after the approval.
Name :*
Email Id :*
Answer :*
Verification Code Code Image - Please contact webmaster if you have problems seeing this image code Not readable? Load New Code
Process Verification  Enter the above shown code:*
Inform me about updated answers to this question

   
Related Questions
View Answer
Difference between a "where" clause and a "having" clause?
View Answer
Write sql query for retrieving employee names from employee table who are having salary greater than 5000 without using where clause?
View Answer
Explain real time situation where you would use clustered and Non-clustered Indexes?
View Answer
What is the pivot operator in MS SQL Server2005? What is the use of it and how do we use it
View Answer
What are "HINTS"? What is "index covering" of a query?
View Answer
What are data objects?
View Answer
What is the difference between User Identification and Authentication?
View Answer
How to display the table data in XML format using SQL Server.
View Answer
What is XMLA in SQL Server 2005
View Answer
How many types of system privileges are there, Can we add or delete privileges?
View Answer

Please Note: We keep on updating better answers to this site. Subscribe to our newsletter to get notified when better answer is posted.

Notify me when better answer is posted!
Email:

View ALL SQL Server Interview Questions

User Options
Sponsored Links


Copyright ©2003-2010 CoolInterview.com, All Rights Reserved.
Privacy Policy | Terms and Conditions
Page URL: http://www.coolinterview.com/interview/14960/default.asp?cachecommand=bypass


Download Yahoo Messenger | Placement Papers| FREE SMS | ASP .Net Tutorial | Web Hosting | Free SMS | Dedicated Servers | Joke of the Day

0.82