Mr. DiGi's List: MSSQL Novinky

• SQL Management Objects

  SQL Management Objects (SMO) is a new set of programming objects that exposes all of the management functionality of the SQL Server database. In fact, Management Studio was built with SQL Management Objects. SMO is implemented as a Microsoft .NET Framework assembly.

• Availability

  Investments in high-availability technologies, additional backup and restore capabilities, and replication enhancements enable enterprises to build and deploy highly available applications. Innovative high-availability features such as database mirroring, failover clustering, database snapshots, and enhanced online operations will minimize downtime and help to ensure that critical enterprise systems remain accessible.

• 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.

  

• Fast Recovery

  SQL Server 2005 improves the availability of SQL Server databases with a new and faster recovery option.

• Online Operations (Index Operations and Restore)

  The ability to create, rebuild, or drop an index online is an enhanced feature of SQL Server 2005 that augments the indexing capabilities of earlier versions of SQL Server. The online index option allows concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and any associated indexes during index data definition language (DDL) execution.
• Support for 64-bit

• CLR/.NET Framework Integration

  With the release of SQL Server 2005, database programmers can now take full advantage of modern programming languages to implement functionality within the server as well as many assemblies in the Microsoft .NET Framework class library.

• Enhanced programming model. Programming languages that are compatible with the .NET Framework are in many respects richer than Transact-SQL, offering constructs and capabilities that were previously not available to SQL Server developers.

  •

  Enhanced safety and security. Managed code runs in a CLR environment, hosted by the Database Engine. This allows .NET Framework database objects to be safer and more secure than the extended stored procedures available in earlier versions of SQL Server.

  •

  User-defined types and aggregates. Two new database objects that expand the storage and querying capabilities of SQL Server are enabled by hosting the CLR.

  •

  Common development environment. Database development is integrated into the Microsoft Visual Studio 2005 development environment. You can use the same tools for developing and debugging database objects and scripts that you use to write middle-tier or client-tier .NET Framework components and services.

  •

  Performance and scalability. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

• Transact-SQL Enhancements

  Transact-SQL has long been the basis for all SQL Server programmability. SQL Server 2005 provides many new language capabilities for developing scalable database applications. These enhancements include error handling, new recursive query capabilities, and support for new SQL Server Database Engine capabilities. Transact-SQL enhancements in SQL Server 2005 increase your expressive powers in query writing, enabling you to improve the performance of your code and extend your error management capabilities. The continuous effort that is being put into enhancing Transact-SQL shows a firm belief in its significant role in SQL Server.

• Cross-Tier and Cross-Language Debugging

  SQL Server 2005 and Visual Studio 2005 together provide even deeper integration in the area of application debugging.

• Report Builder

  Report Builder, a new component of SQL Server 2005 Reporting Services, allows business users to create their own reports using a user-friendly model of their data.

  • Rozhodně to vypadá lépe než Rave reporty... Ale Fast Reports jsou přece jen asi o kus dále (a jsou především nativní Delphi kompoenty)

9 more annotations...

• 
• Dopytovanie v tabuľkách je podstatne výkonnejšie na systémoch od 8 procesorov – dopyty sa vykonávajú paralelne cez rôzne partície. Redukuje sa tým čas procesora.

• ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> )
• Ošetrenie chýb pomocou konštrukcie TRY CATCH
• SET XACT_ABORT OFF BEGIN TRY  BEGIN TRAN  INSERT INTO VynimkaT2 VALUES (4)    INSERT INTO VynimkaT2 VALUES (5)   /* !!! cudzi kluc */    INSERT INTO VynimkaT2 VALUES (6)   COMMIT TRAN  PRINT 'Transakcia potvrdena' END TRY BEGIN CATCH  ROLLBACK  PRINT 'Transakcia odvolana' END CATCH

1 more annotation...

• 
• public class ContactCode  {  [SqlProcedure(Name="GetContactNames")]  public static void GetContactNames()  {  SqlCommand cmd = SqlContext.GetCommand();  cmd.CommandText = "SELECT FirstName + ' ' + LastName" +   " AS [Name] FROM Person.Contact";  SqlDataReader rdr = cmd.ExecuteReader();  SqlPipe sp = SqlContext.GetPipe();  sp.Send(rdr);  } }

• Enhanced Database Mirroring

  SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.
• Automatic Recovery of Data Pages

  SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.
• Log Stream Compression

  Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.
• Resource Governor

  Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.
• Predictable Query Performance

  Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.
• Data Compression

  Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.
• Hot Add CPU
• Performance Data Collection

  Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.

  • Toto zní moc zajímavě
• Language Integrated Query (LINQ)
• new date and time data types
• HIERARCHY ID

  Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.
• FILESTREAM Data

  Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.
• Integrated Full Text Search

  Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.
• Sparse Columns

  NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.
• Large User-Defined Types

  SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs)
• Backup Compression
• Partitioned Table Parallelism

  Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.

  • Tohle je přesně to, jak by měl být řešen Archív...
• Star Join Query Optimizations
• Grouping Sets

  Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
• Change Data Capture

  With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.
• MERGE SQL Statement

  With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.
• Learn more about SQL Server 2008 Integration Services and Data Warehousing
• SQL Server 2008 Analysis Services
• Enterprise Reporting Engine

  Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.

22 more annotations...

• SQL 2000 Support Ends

    

  Mainstream Support for SQL 2000 is coming to an end.

  • Prostě budeme muset přejít...

• Encryption

    

  There are several encryption options; the first is called Transparent Data Encryption. In SQL Server 2008, the entire database can be encrypted by the SQL Engine.

• Auditing

    

  In addition to the standard auditing of logon / logoffs and permission changes, SQL Server 2008 allows for monitoring of data changes or access.

• Data Compression

    

  Usually, data compression is associated with general hard disk savings, and with smaller physical files, backup times are reduced. While this holds true for SQL Server Data Compression, the main goal is Fact Table size reduction.

• Resource Governor

      

  New in SQL Server 2008 is the Resource Governor. The Governor is used to restrict users or groups of users from consuming high levels of resources. Items that can be monitored include CPU bandwidth, timeout waits, execution times, blocking times, and idle times.

• Performance Data

      

  The amount of Performance Counters in SQL Server 2008 has been expanded compared to earlier versions. IO and memory usage counters are just a couple of the items that can be collected to monitor performance. The data collected by the counters is stored in a centralized data warehouse. Microsoft states that running the default set of performance related monitors will consume less than 5% of CPU and memory resources.

      

  There is also now a Performance Dashboard tool that can read saved performance data. In addition, historical and baseline comparisons can be made and used to create action triggers. For example, if memory use exceeds a threshold for more than five minutes, a more detailed data collection can be automatically triggered.

      

  New in SQL Server 2008 is the Performance Studio. The Studio is a collection of performance tools. Together they can be used for monitoring, troubleshooting, tuning and reporting. The Data Collector component of the studio is configurable and low overhead. It supports several colleting methods including TSQL queries, SQL Trace, and Perfmon Counters.  Data can also be collected programmatically. Once data is collected, there are drill-down and aggregate reporting options. Microsoft lists these six client side features of the Performance Studio:

    
   
  •  SQL Server dashboard
    
  •  Performance monitoring
    
  •  Current and historical data analysis
    
  •  Suggestions for potential performance tuning
    
  •  Data collection sets-based reports
    
  •  MDW-based reports

4 more annotations...

• Dynamic Development

    

  SQL 2008 leverages the new Dot Net Framework 3.0 with LINQ (Language Integrated Query). In addition, there is more efficient support for Business Data Entities along with data synchronization options. Also, there are new ADO and Visual Studio development options.

• LINQ

      

  LINQ provides a standard development syntax for accessing data, regardless of where the data resides. For example, the same syntax can access either SQL Server or XML data. LINQ is used rather than TSQL inside the application language, such as C# or VB.

• Data Synchronizing Features

      

  The combination of SQL 2008, Visual Studio, and ADO.NET bring together new methods of creating synchronizing or frequently disconnected applications, making it easier to create client applications that synchronize with a central database. SQL 2005 started by providing support for change tracking by using triggers. SQL 2008 synchronizing is better integrated and optimized.

• Table Value Parameters

      

  In previous versions of SQL Server, there wasn’t a native way to pass a table to a stored procedure. The usual workaround was to pass a large varchar or XML type and parse through it. Now, in SQL Server 2008, Table Parameters are available. The following provides a simple example of passing a table into a Stored Procedure.

        

  CREATE TYPE PartType  AS Table (PartID varchar(50), Descr varchar(100), createdate datetime);   CREATE PROCEDURE AddPart(@PartList PartType READONLY) AS SELECT * FROM @PartList    DECLARE @PartTable PartType; INSERT INTO @PartTable values('Part1', N'Table Test', '2007-08-20'); EXEC AddPart @PartTable 

  • Tak tohle je ale super!

• Full Text Search

      

  There are Full Text Search changes in SQL Server 2008 including native indexes, thesaurus files stored as metadata, and the ability to perform a Backup.

• Reporting Server

      

  Memory management in SQL Server 2008 Reporting Service is improved. So running large reports will not consume all available memory. In addition, report rendering has more consistency than before.
• •  Transparent Data Encryption allows for an entire database, all tables and data, to be encrypted on the fly without application programming.
    
  •  Backups can be encrypted to prevent data disclosure or tampering.
    
  •  Data changes and access can now be audited.
    
  •  Fact Tables can be compressed for performance benefits.
    
  •  The Resource Governor can prevent runaway resource usage.
    
  •  SQL 2008 supports Hot Plug CPU.
    
  •  Performance Counters have been greatly expanded.
    
  •  Installation has been simplified.
• •  Data Integration Features such as the MERGE statement, Parallelism, SSIS multiple processor improvements, and look up performance improvements.
    
  •  Analysis Service Improvements including BI Stack performance, Scale out analysis, Block computations and Perspectives.
    
  •  Microsoft Office 2007 Integration such as Exporting Reporting Service reports as Word docs, SSRS format and font improvements, and the Office Tool Bar.

6 more annotations...

• Users will notice a completely overhauled reporting engine and greatly improved OLAP performance. This should allow more users to run OLAP queries, or enable existing OLAP users to run more complex queries than are currently possible.
• Another groundbreaking addition is SQL 2008’s FileStream data type. These records can contain arbitrary data — such as text, sound or video — and can be searched using normal database commands. The difference is that FileStream records are stored on the local NTFS filesystem, which means they can contain much more data than a normal SQL record, and performance can be better too.
• Microsoft claims SQL Server 2008 will run 14 per cent faster than SQL Server 2005 on a quad-socket server
• Database mirrors have been improved in order to store null values more efficiently, and the logs that synchronise database mirrors can now be compressed. This should reduce the amount of bandwidth needed to maintain duplicates of a database. Finally, a new resource governor enables administrators to assign CPU power to particular applications.

2 more annotations...

• WITH SalesCTE(ProductID, SalesOrderID) AS  (  SELECT ProductID, COUNT(SalesOrderID)   FROM Sales.SalesOrderDetail   GROUP BY ProductID ) SELECT *   FROM SalesCTE  WHERE SalesOrderID > 50