Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
SQL 2005 Features
Posted Date:
29 Aug 2008
Total Responses:
5
|
Posted By: baranidharan Member Level: Silver Points: 1
|
hi, can you please explain the features of sql server 2005...in comparing with 2000
|
Responses
|
| Author: ANIL PANDEY 29 Aug 2008 | Member Level: Diamond | Rating:  Points: 4 |
hi,
www.microsoft.com/sql/prodinfo/features/top30features.mspx
searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1137301,00.html
www.aspfree.com/c/a/MS-SQL-Server/SQL-Server-2005-Whats-New/
media.wiley.com/product_data/excerpt/57/07645775/0764577557.pdf
Thanks
Anil
Thanks & Regards
Anil Kumar Pandey
| | Author: Sandeep Chougule 29 Aug 2008 | Member Level: Gold | Rating: Points: 6 |
SQL Server 2005 Features at a Glance
The following table provides an overview of several key enhancements in SQL Server 2005, focusing on enterprise data management, developer productivity, and business intelligence. A wider range of compared features is available at the SQL Server Developer Center.
Enterprise Data Management
Technology
Capabilities
High Availability
Failover clustering and database mirroring technology in SQL Server 2005 enables enterprises to deliver highly reliable, available applications to employees, customers, and partners.
Management Tools
SQL Server 2005 introduces an integrated suite of management tools and management application programming interfaces (APIs) to provide ease of use, manageability, and support for operating large-scale SQL Server deployments.
Security Enhancements
SQL Server 2005 has been designed to help provide the highest level of security for enterprise data through features such as database encryption, more secure default settings, password policy enforcement, granular permissions control, and an enhanced security model.
Scalability
Scalability advancements in SQL Server 2005 include table partitioning, replication enhancements, and 64-bit support.
Developer Productivity
Technology
Capabilities
Common Language Runtime (CLR) Integration
SQL Server 2005 introduces the capability to develop database objects using any Microsoft .NET language.
Deep XML Integration
SQL Server 2005 delivers a new XML data type, enabling the storage of XML fragments or documents in SQL Server databases.
Transact-SQL Enhancements
New query types and the capability to use error handling in transactions provide developers with more flexibility and control in SQL Server query development.
SQL Server 2005 Compact Edition
Full integration of SQL Server 2005 Compact Edition with SQL Server 2005 and Visual Studio 2005 provides a platform for developers to rapidly build applications that extend enterprise data management capabilities to mobile devices.
Business Intelligence
Technology
Capabilities
Analysis Services
Analysis Services provides a unified and integrated view of all your business data as the foundation for all of your traditional reporting, OLAP analysis, Key Performance Indicator (KPI) scorecards and data mining.
Integration Services
SQL Server Integration Services (SSIS) is a next generation data integration platform that can integrate data from any source. SSIS provides a scalable and extensible platform that empowers development teams to build, manage, and deploy integration solutions to meet unique integration needs.
Reporting Services
SQL Server Reporting Services is a comprehensive, server-based reporting solution designed to help you author, manage, and deliver both paper-based and interactive Web-based reports.
Data Mining
SQL Server 2005 Analysis Services (SSAS) provides tools for data mining with which you can identify rules and patterns in your data, so that you can determine why things happen and predict what will happen in the future—giving you powerful insight that will help your company make better business decisions.
Database Mirroring
Database mirroring allows continuous streaming of the transaction log from a source server to a single destination server. In the event of a failure of the primary system, applications can immediately reconnect to the database on the secondary server. The secondary instance detects failure of the primary server within seconds and accepts database connections immediately. Database mirroring works on standard server hardware and requires no special storage or controllers. Figure 1 shows the basic configuration of database mirroring.
Figure 1 Basic Configuration of Database Mirroring
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
Results
Well, the guy in the 2005 model blasted to the next stoplight in 18.5% less time than it took SQL 2000 , and he wasn't even working as hard as you were.
Average Elapsed Time (hh:mm:ss)
SQL Server 2000
1:23:55
SQL Server 2005
1:08:25
Difference
0:15:30
Percentage
18.5%
Sql Server 2005 Partitioning
A very interesting an powerful feature of Sql Server 2005 is called Partitioning. In a few word this means that you can horizontally partition the data in your table, thus deciding in which filegroup each rows must be placed.
This allows you to operate on a partition even with performace critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available, all the data in that partition are available for quering, even if the restore is not yet fully completed.
Here a simple script to begin to make some test on your own:
use adventureworks
go
-- Setup a clean system
drop partition scheme YearPS;
drop partition function YearPF;
-- Create a partitioning functions.
-- Here we're creating two partitions based on date values: all values from and after 2005-01-01
-- will go in the second partition and al the values before goes in the first one.
create partition function YearPF(datetime)
as range right for values ('20050101');
-- Now we need to add filegroups that will contains partitioned values
alter database AdventureWorks add filegroup YearFG1;
alter database AdventureWorks add filegroup YearFG2;
-- Now we need to add file to filegroups
alter database AdventureWorks add file (name = 'YearF1', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF1.ndf') to filegroup YearFG1;
alter database AdventureWorks add file (name = 'YearF2', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF2.ndf') to filegroup YearFG2;
-- Here we associate the partition function to
-- the created filegroup via a Partitioning Scheme
create partition scheme YearPS
as partition YearPF to (YearFG1, YearFG2)
-- Now just create a table that uses the particion scheme
create table PartitionedOrders
(
Id int not null identity(1,1),
DueDate DateTime not null,
) on YearPS(DueDate)
-- And now we just have to use the table!
insert into PartitionedOrders values(getdate()-200)
insert into PartitionedOrders values(getdate()-100)
insert into PartitionedOrders values(getdate())
insert into PartitionedOrders values(getdate()+100)
insert into PartitionedOrders values(getdate()+200)
-- Now we want to see where our values has falled
select *, $partition.YearPF(DueDate) from PartitionedOrders
-- You can also view how many partitions we did
select * from sys.partitions where object_id = object_id('PartitionedOrders')
| | Author: Sandeep Chougule 29 Aug 2008 | Member Level: Gold | Rating: Points: 6 |
Feature SQL Server 2000 SQL Server 2005
Server Programming Extensions Limited to extended stored procedures, which are difficult to write and can impact the server stability. The incorporation of the CLR into the relational engine allows managed code written in .NET languages to run. Different levels of security can protect the server from poorly written code.
T-SQL Error Handling Limited to checking @@error, no much flexibility. Addition of TRY/CATCH allows more mature error handling. More error_xx functions can gather additional information about errors.
T-SQL Language SQL Language enhanced from previous versions providing strong data manipulation capabilities. All the power of SQL Server 2000 with the addition of CTEs for complex, recursive problems, enhanced TOP capabilities, PIVOT/APPLY/Ranking functions, and ROW_NUMBER
Auditing Limited support using triggers to audit changes. Robust event handling with EVENT NOTIFICATIONS, the OUTPUT clauses, and DDL triggers.
Large Data Types Limited to 8k for normal data without moving to TEXT datatypes. TEXT is hard to work with in programming environments. Includes the new varchar(max) types that can store up to 2GB of data in a single column/row.
XML Limited to transforming relational data into XML with SELECT statements, and some simple query work with transformed documents. Native XML datatype, support for schemas and full XPATH/XQUERY querying of data.
ADO.NET v1.1 of ADO.NET included enhancements for client development. v2 has more features, including automatic failover for database mirroring, support for multiple active result sets (MARS), tracing of calls, statistics, new isolation levels and more.
Messaging No messaging built into SQL Server. Includes Service Broker, a full-featured asynchronous messaging system that has evolved from Microsoft Message Queue (MSMQ), which is integrated into Windows.
Reporting Services An extremely powerful reporting environment, but a 1.0 product. Numerous enhancements, run-time sorting, direct printing, viewer controls and an enhanced developer experience.
ETL DTS is a very easy to use and intuitive tool. Limited capabilities for sources and transformations. Some constructs, such as loops, were very difficult to implement. Integration Services is a true programming environment allowing almost any source of data to be used and many more types of transformations to occur. Very complex environment that is difficult for non-DBAs to use. Requires programming skills.
Full-Text Search Workable solution, but limited in its capabilities. Cumbersome to work with in many situations. More open architecture, allowing integration and plug-ins of third party extensions. Much more flexible in search capabilities.
| | Author: D.Jeya kumar(JK) 29 Aug 2008 | Member Level: Diamond | Rating: Points: 6 |
Hi,
There are many new features listed in sql server 2005 compared
with sql server 2000 and some of the festure that are new in
sql server 2005
Varchar(max) , Nvarchar(max) etc., as new datatype
SSRS(Sql server reporting service) to create reports just like crystal reports ,SSIS (Sql server integration service) to take the backup and other features, SSAS etc., as new Components in sql server 2005
Sql server management studio replaced by Enterprise manager etc.,
check the top 10 festure of Sql server 2005
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1137301,00.html
check the top 30 festure of Sql server 2005
http://www.microsoft.com/sql/prodinfo/features/top30features.mspx
Regards
JK
| | Author: http://venkattechnicalblog.blogspot.com/ 31 Aug 2008 | Member Level: Diamond | Rating: Points: 1 |
Check my blog..
http://venkattechnicalblog.blogspot.com/
Regards,
Venkatesan Prabu .J
|
| Post Reply |
|
|
|
You must Sign In to post a response.
|
|
|
|