Home > T - SQL > SQL Server 2005 Vs SQL Server 2008

SQL Server 2005 Vs SQL Server 2008

Overview:

Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. Its primary query languages are MS-SQL and T-SQL.

Below is the Release History for SQL server:

Version

Year

Release Name

Codename

1.0 (OS/2)

1989

SQL Server 1.0

-

4.21 (WinNT)

1993

SQL Server 4.21

-

6.0

1995

SQL Server 6.0

SQL95

6.5

1996

SQL Server 6.5

Hydra

7.0

1998

SQL Server 7.0

Sphinx

-

1999

SQL Server 7.0
OLAP Tools

Plato

8.0

2000

SQL Server 2000

Shiloh

8.0

2003

SQL Server 2000
64-bit Edition

Liberty

9.0

2005

SQL Server 2005

Yukon

10.0

2008

SQL Server 2008

Katmai

 

SQL Server 2005

SQL Server 2005, released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used as a data type in database columns.

SQL Server 2008

The current version of SQL Server, SQL Server 2008, (code-named “Katmai”,) was released on August 6, 2008 and aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 will also include support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects).  SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc  as well as perform search, query, analysis, sharing, and synchronization across all data types.  Other new data types include specialized date and time types and a Spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the new FILESTREAM data type, which can be used to reference any file stored on the file system. Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL;

1

 

  • SQL Server 2008 delivers a rich set of integrated services that enable you to do more with your data such as query, search, synchronize, report, and analyze.
  • SQL Server 2008 provides the highest levels of security, reliability, and scalability for your business-critical applications.
  • SQL Server 2008 also natively supports hierarchical data, and includes T-SQL constructs to directly deal with them, without using recursive queries.

What’s new in SQL Server 2008 for SSIS

SSIS (SQL Server Integration Services) is a built in application for developing and executing ETL (extraction, transformation, and load) packages. SSIS replaced SQL 2000 DTS. Integration Services includes the necessary wizards, tools, and tasks for creating both simple import export packages, as well very complex data cleansing operations. SQL Server 2008 SSIS includes a number of improvements and enhancements such as better parallel execution. In SSIS 2005, the pipeline didn’t scale past two processors. SSIS 2008 will scale past two processors on multiprocessor machines. Also, the newly redesigned pipeline improves performance on large packages that contain long sub-trees. In addition, the SSIS engine is reported to be more stable with fewer incidents of deadlocks.

 


Improved Scripting

SQL Server 2008 (Katmai) introduces VSTA as our new scripting engine, which replaces the old VSA engine used in SQL Server 2005 (Yukon). There are a number of advantages to the new engine, but I think the feature users are most excited about is that it allows you to use C# as a scripting language (as well as VB.NET). It also makes it easier to reference all .Net assemblies, which enables a number of common scenarios (such as easier web service, email service and custom components integration).

Merge

SQL 2008 includes the TSQL command MERGE. Using this statement allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.


MERGE
 InventoryMaster AS im
USING
(SELECT InventoryID, Descr FROM NewInventory) AS src
ON
 im. InventoryID = src. InventoryID
WHEN
 MATCHED THEN
UPDATE
 SET im.Descr = src.Descr
WHEN
 NOT MATCHED THEN
INSERT
(InventoryID, Descr) VALUES (src. InventoryID, src.Descr);

Data Flow Task Improvements

It essentially boils down to smarter thread allocation and processing of our execution trees. Long chains of synchronous transforms are optimized, and most packages will see a performance increase on multi-processor machines.

 

2

This graph shows much better CPU usage distribution. In this case, the package received an 80% performance increase.

Enhanced ADO.Net Support

Katmai now has an ADO.Net source (replacing the DataReader source) and destination adapters. They function a lot like the OLEDB ones, supporting a custom UI, drop down list of tables/views, and query builder. Its properties are also expression-able. This means we now support ODBC as a destination, as well.

3

Import/Export Wizard

The SQL Server Import/Export Wizard (which generates SSIS packages) has been improved as well.

  • ADO.Net support (which gives you ODBC)
  • Data type conversion – mappings are read from customizable (xml) files, and data convert transforms will be inserted into the data flow if required
  • Scales better – large numbers of tables will be split across multiple data flows (up to five sources per data flow)

Cached Lookup

The main feature is that you can now create a lookup cache in a separate data flow using the new Lookup Cache Transform and Cache Connection Manager, and persist it to disk. This cache can be created from any data source supported by SSIS, which removes the need to stage your lookup table in a database.

This persisted cache is especially useful in scenarios where reference data is updated periodically – say, once a week. You can run a package to generate your cache when the update comes in, and re-use it in the rest of your packages until it needs to be updated again.

Another scenario is managing large lookup tables, where a small number of keys are used a large percentage of the time. For example, 5% of your customer IDs show up in 80% of your rows. Instead of caching the entire lookup table, you can use a cached lookup for the top 5%, and cascade another lookup in partial / no-cache mode for the infrequent rows.

4

5

 

As you can see from the screen shot, Lookup also has a new “No Match” output – rows with no matches aren’t directly sent to the error output anymore. We also maintain a “Miss Cache” internally, so missing keys aren’t constantly re-queried.

 


SQL Server 2008 Top New Features

Policy-Based Management

Policy-Based Management enables the efficient management of multiple SQL Server instances from a single location. Easily create policies that control security, database options, object naming conventions, and other settings at a highly granular level. Policies can evaluate servers for compliance with a set of predefined conditions and prevent undesirable changes being made to servers.

  • Management is centralized, thereby reducing the need to configure each server separately
  • Administration is simplified, reducing the effort required to maintain standardization and compliance, even in complex environments
  • Configuration is straightforward and can be done entirely within SQL Server Management Studio
  • Out-of-the-box predefined policies make it easy to get started
  • Backwards compatibility supports managing instances of SQL Server 2008, SQL Server 2005, and SQL Server 2000

 

Performance Data Collection

The Data Collector provides a convenient way to collect, store, and view performance data automatically. It collects disk usage, server activity, and query statistics data, which it loads in a management data warehouse and performance data can be reviewed in SQL Server Management Studio.

  • Setup wizard makes configuration simple
  • Data collection is automated by using set of SQL Server Agent jobs and SQL Server Integration Services packages

Data Compression

Data compression reduces the amount of storage space needed to store tables and indexes, which enables more efficient storage of data. Data Compression does not require changes be made to applications in order to be enabled.

  • Save disk storage
  • Enable compression option for individual tables or indexes
  • Configuration is easy using the Data Compression wizard
  • Applications do not need to be reconfigured as SQL Server handles compression and decompression of data
  • Compression can improve disk I/O and memory utilization

Resource Governor

The Resource Governor enables administrators to control and allocate CPU and memory resources to high priority applications. This enables predictable performance to be maintained and helps avoid performance from being negatively affected by resource-intense applications or processes

  • Prioritize applications, users, and computers competing for the same resources
  • Prevent runaway queries that hold resources for extended periods of time
  • Limitations are not enforced when there is no competition for resources

Transparent Data Encryption

Transparent Data Encryption enables data to be stored securely by encrypting the database files. If the disks that contain database files become compromised, data in those files is protected because that data can only be de-encrypted by an authorized agent. SQL Server performs the encryption and de-encryption directly, so the process is entirely transparent to connecting applications. Applications can continue to read and write data to and from the database as they normally would. Backup copies of encrypted database files are also automatically encrypted.

  • Implements strong encryption keys and certificates to secure data
  • Applications do not need to be modified to support Transparent Data Encryption
  • Enables compliance with data privacy regulations
  • Does not increase the size of the database

Data Auditing

Data Auditing provides a simple way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events. Tracking these events helps maintain security and can also provide valuable troubleshooting information. The results of audits can be saved to file or to the Windows Security or Application logs for later analysis or archiving.

·         Enables compliance with security regulations

·         Simple configuration using SQL Server Management Studio

·         Minimal impact on performance because audit data is stored outside of SQL Server database files

Backup Compression

Backup compression enables the backup of a database to be compressed without having to compress the database itself. All backup types, including log backups, are supported and data is automatically uncompressed upon restore.

·         Save storage space

·         Compressed backups can be stored on tape or on disk

·         Simple configuration using SQL Server Management Studio

·         Default state of all backups on a server to be compressed can be configured

Table-Valued Parameters

Table-Valued Parameters (TVPs) allows stored procedures to accept and return lists of parameters. Developers can write applications that pass sets of data into stored procedures rather than just one value at a time. Table-valued parameters make the development of stored procedures that manipulate data more straightforward and can improve performance by reducing the number of times a procedure needs to call a database.

·         Eliminates the need to use less efficient methods of processing data sets, such as passing XML data into stored procedures

·         Reduces complexity and simplifies the process of working with data sets for developers

Virtual Earth Integration

Use the new spatial data types in SQL Server 2008 with Microsoft Virtual Earth to deliver rich graphical representations of the physical locations stored in a database. Use Virtual Earth support to create applications that display data about locations in desktop maps or web pages. For example, SQL Server 2008 makes it easy to show the locations of all company sites that are less than 50 kilometers from Denver.

 

·         Spatial data types enable geographical data to be stored natively in SQL Server2008 databases

·         Built-in library of industry-standard spatial functions streamlines development

·         Provide meaningful representations of geographical data

 

DATE / TIME Data Types

SQL Server 2008 introduces several new date and time based data types. DATETIME2 references the Coordinated Universal Time (UTC) instead of the system time for greater accuracy and can store date and time data to a precision of 100 nanoseconds. The new DATE and TIME data types enable you to store date and time data separately. The new DATETIMEOFFSET data type introduces time zone support by storing date, time and offset such as ‘plus 5 hours’.

·         Greater flexibility for storing date and time data.

·         Greater accuracy and precision for date and time data.

·         DATETIME and SMALLDATETIME data types still supported.

 

Partitioned Table Parallelism

Parallelism refers to using multiple processors in parallel to process a query, which improves query response time. On a multiprocessor system, SQL Server 2008 uses parallel processing when you run queries that reference partitioned tables.

When SQL Server 2008 processes such a query, rather than allocating just one processor for each partition referenced by the query, it can allocate all available processors, regardless of the number of partitions referenced.

·         Queries against partitioned tables run faster

·         More efficient use of processor resources than in previous versions of SQL Server

·         Operations involving partitioned indexes are optimized in the same way

 

Reporting Services

Processing and performance have been improved in SSRS (SQL Server Reporting Server). Large reports will no longer consume all available memory. In addition, there is greater consistency between layout and render. Also, the TABLIX, a cross between a table and a matrix is included in SQL SSRS 2008. Application Embedding allows URLs in reports to point to a calling application.

Microsoft Office 2007

SQL Server 2008 can tightly integrate with Microsoft Office 2007. For example, in SQL Server Reporting Server reports can now export directly to Word. In addition, both Word and Excel can be used as templates for SSRS reports by using the Report Authoring tool. Excel SSAS performance has been improved and there is a data mining add-in.

 

References:

Categories: T - SQL
  1. June 5th, 2009 at 00:25 | #1

    da best. Keep it going! Thank you

  2. Ningaraj
    June 9th, 2010 at 09:48 | #2

    Fantastic one and i learnt a lot from this.. Thanks..:)

  3. Anil
    July 1st, 2010 at 01:36 | #3

    Nicely done! Exactly, what I was looking for. Thanks!

  4. DBA
    July 29th, 2010 at 19:27 | #4

    I’ve Found a SQL scanner that can locate any MSSQL server at your network (multi-subnet) it can also try to brute force the SA user account (or any other account) to make sure the password is not easy.
    you can get it here: http://www.softpedia.com/get/Internet/Servers/Database-Utils/SQL-Locator.shtml

  5. dghd
    February 25th, 2013 at 16:43 | #5

    dghgj

  1. No trackbacks yet.
You must be logged in to post a comment.