SQL Server 2008 Overview

1. SQL Server 2008Overview Presented by TarekGhazali IT Technical Specialist Microsoft SQL Server MVP, MCTS Microsoft Web Development MCP ITIL V3 Foundation Certified © 2009 Tarek Ghazali. All rights reserved.

2. What is new in SQL Server 2008 • More powerful management • Policy Management feature • Improved performance and scalability • Storing high volume of data • Support for diverse types of data • Better security and availability • Security increased by data encryption • Availability increased by database mirroring • Changes for developers

3. LINQ (Language Integrated Query) • LINQ enables database developers to issue queries against a database using a .NET-based programming language instead of the normal T-SQL statements.

4. LINQ (Language Integrated Query) (Cont.) • SQL Server 2008 enhances LINQ by providing a new LINQ to SQL provider that allows developers to issue LINQ commands directly against SQL Server tables and columns • This will reduce the amount of time it takes to create new data queries

5. Enhancement of T-SQL Statements • MERGE statement: It allows the developer to check for the existence of data before trying to insert the data. • Allows you to merge two tables together based on a set of criteria • Useful for synchronizing data • Great for consolidating in a Data Warehouse

6. Enhancement of T-SQL Statements (Cont.) • No longer it is necessary to create complex joins in order to update data that exists and to insert data that does not already exist, all during a single statement.

7. Table Valued Parameters (TVP) • TVP's make possible to use a "table" as a parameter for a procedure • Limitations: • TVP's can only be READONLY in the procedure that define them as a parameter • Can only be used as an input parameter. • Apart from this the same rules apply to TVP's as to table variables for example no DDL can be executed against a TVP and no statistics are kept for TVP's.

8. Data Compression • Works on tables and indexes • Works on clustered and non-clustered indexes • Compression types: Page and Row • T-SQL extensions to Table and Index DDL

9. Data Compression (Cont.) • CREATE TABLE... [WITH (<table_option> [,...n])]<table_option>:={ DATA_COMPRESSION = {NONE|ROW|PAGE}[ON PARTITIONS ({<partition_number_expression>|<range>} [,...n])] • New SP to estimate cost savings • sp_estimate_data_compression_savings

10. New DatatypesDate and Time Datatypes • DATE – a date only type (precision 1 day) • TIME – a time only type (precision 100 nano seconds) • DATETIMEOFFSET –a time zone aware datetime type (precision 100 nano seconds)

11. Date and Time Datatypes (Cont.) • DATETIME2 – a datetime type with larger fractional seconds and year range than the existing DATETIME type (precision 100 nano seconds)

12. HierarchyId: New datatype • Enables database applications to model tree structures in a more efficient way than currently possible • Stores values that represent nodes in a hierarchy tree • Implemented as a CLR UDT that exposes several efficient and useful built-in methods for creating and operating on hierarchy nodes

13. Spatial Datatypes • Geography datatype • Round Earth geospatial model • Define points, lines, and areas with longitude and latitude • Account for planetary curvature and obtain accurate “great circle” distances

14. Spatial Datatypes (Cont.) • Geometry datatype • Planar (“Flat Earth”) geospatial model • Define points, lines, and areas with coordinates • Use for localized areas or non-projected surfaces • Both types provide static and instance methods • Calculate distances, find intersections, etc.

15. FILESTREAM datatype • Stores data in the File System but, allows it to be queried via normal T-SQL • Allows easy transition between relational and non-relational data, so application can access documents as data • Under the covers uses the Win32 APIs to speed streaming of data.

16. FILESTREAM datatype • Stores data in the File System but, allows it to be queried via normal T-SQL • Allows easy transition between relational and non-relational data, so application can access documents as data • Under the covers uses the Win32 APIs to speed streaming of data.

17. FILESTREAM datatype (Cont.) • Allows developers Use T-SQL SELECT/INSERT/UPDATE/DELETE statements to query or modify FILESTREAM data • Data stored outside of the database on more cost-effective storage without comprising features for accessing such data.

18. New Security Features • SQL Server 2005 introduced data security in the form of data encryption • With SQL Server 2008, encryption is greatly enhanced with the introduction of two features: Extensible Key Management and Transparent Data Encryption

19. New Security Features (Cont.) • Extensible Key Management allows for an enhanced structure to safely store the keys used in the encryption infrastructure—not only in the database itself but also outside the database in third-party software modules or with a Hardware Security Module

20. New Security Features (Cont.) • Transparent Data Encryption offers improved flexibility for encrypting data by allowing encryption to be a property of the database and not just the result of functions in a line of code

21. Demo

22. Resources & Questions • Microsoft Resources: • http://msdn.microsoft.com/sqlserver/ • http://www.microsoft.com/sql/community • Contact me: • tghazali@sqlmvp.com • Download Presentation: • http://www.sqlmvp.com