Hide Notes 

Slide 1: Eric Nelson Developer Evangelist Eric.nelson@microsoft.com – I will reply http://blogs.msdn.com/ericnel - tends to be about .NET and data http://blogs.msdn.com/goto100 - all about Visual Basic http://twitter.com/ericnel - pot luck :-) http://blogs.msdn.com/ukdevevents The ONLY LINK you need!
Slide 2: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. First PC – ZX80 in 1980 (then BBC Micro, Atari 520STFM and Macintosh LC) First computer job... FORTAN in 1986 Wrote most LOC on... Unix using C Favourite IDE of all time ...GNU Emacs Joined Microsoft DRG in 1996 Early adoption work on ASP, SQL 6.5, MTS... Went “Back to development” in July 2008 – in VB I Geek to Live (not Live to Geek) I am editor of the UK MSDN Flash I am fascinated by ALT.NET values and practices http://blogs.msdn.com/ukdevevents The ONLY LINK you need!
Slide 3: I live near Bath with wife, 2 kids, 1 dog (and many pet graves in the garden) I originally intended to be a naval officer I am very shy...seriously! http://blogs.msdn.com/ukdevevents The ONLY LINK you need!
Slide 4: Sign up to the MSDN Flash Feedback, vote etc Technical articles for the UK MSDN Flash 400 to 500 words War stories around VB6 to .NET Your right (or left) arm in the air NB: not all the time 
Slide 5: Give a flavour of the new “stuff” in SQL Server 2008 for development and an insight into the direction we are taking with data
Slide 6: TSQL Enhancements SQL-2006 Major and minor In one hour Beyond Relational Filestream Full text search Spatial Semi-structured Futures* Object Relational Management SQL Data Services
Slide 7: Released August 6th 2008 Many versions!
Slide 9: Delighters SQL language Date types • IDE improvements • Compound assignment operators • Declaring and initializing variables • • • • Table value constructor using VALUES clause MERGE Table Types and Table Value Parameters GROUPING SET • Date & Time
Slide 10: IDE Improvements Debugging is back! Intellisense has made it in Compound Assignment operators: +=, -=, *=, /= UPDATE Inventory SET quantity += s.quantity FROM Inventory AS i INNER JOIN Sales AS s ON i.id = s.id Variable initialization during declaration DECLAER @v int = 5; DECLARE @v1 varchar(10) = ‘xxxxx’;
Slide 12: Insert multiple rows based on values in a single INSERT statement SQL 2006 standard compatible INSERT INTO dbo.Customers(custid, companyname, phone, address) VALUES (1, 'cust 1', '(111) 111-1111', 'address 1'), (2, 'cust 2', '(222) 222-2222', 'address 2'), (3, 'cust 3', '(333) 333-3333', 'address 3'), (4, 'cust 4', '(444) 444-4444', 'address 4'), (5, 'cust 5', '(555) 555-5555', 'address 5');
Slide 13: Single statement that combines multiple DML operations Operates on a join between source and target SQL-2006 compliant Pre-SQL 2008 UPDATE TGT SET TGT.quantity = TGT.quantity + SRC.quantity, TGT.LastTradeDate = SRC.TradeDate FROM dbo.StockHolding AS TGT JOIN dbo.StockTrading AS SRC ON TGT.stock = SRC.stock; INSERT INTO dbo.StockHolding (stock, lasttradedate, quantity) SELECT stock, tradedate, quantity FROM dbo.StockTrading AS SRC WHERE NOT EXISTS (SELECT * FROM dbo.StockHolding AS TGT WHERE TGT.stock = SRC.stock); SQL 2008 MERGE INTO dbo.StockHolding AS TGT USING dbo.StockTrading AS SRC ON TGT.stock = SRC.stock WHEN MATCHED AND (t.quantity + s.quantity = 0) THEN DELETE WHEN MATCHED THEN UPDATE SET t.LastTradeDate = s.TradeDate, t.quantity += s.quantity WHEN NOT MATCHED THEN INSERT VALUES (s.Stock,s.TradeDate,s.Quantity)
Slide 14: Source Table (Stock Trading) Target Table (Stock Holding) INSERT Merged Table (Stock Holding) UPDAT E DELETE
Slide 15: new user defined type - Table Can define indexes and constraints an be used for declaring table variables Input parameters of Table type on SPs/Functions ptimized to scale and perform better for large data ehaves like BCP inside server CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100),qty INT); n ADO.NET SqlDbType.Structured CREATE PROCEDURE myProc (@tvp myTableType READONLY) AS …
Slide 16: Define multiple groupings in the same query Produces a single result set that is equivalent to a UNION ALL of differently grouped rows SQL 2006 standard compatiable Pre-SQL 2008 SELECT customerType,Null as TerritoryID,MAX(ModifiedDate) FROM Sales.Customer GROUP BY customerType UNION ALL SELECT Null as customerType,TerritoryID,MAX(Modifie dDate) FROM Sales.Customer GROUP BY TerritoryID order by TerritoryID SQL 2008 SELECT customerType,TerritoryID,MAX(ModifiedDa te) FROM Sales.Customer GROUP BY GROUPING SETS ((customerType), (TerritoryID)) order by customerType
Slide 19: DATE • Large year range (1~9999) • Storage saving • Easy programming CREATE TABLE Employee { FirstName VARCHAR(10), LastName VARCHAR(10), Birthday DATE, … } SELECT Birthday AS BirthDay FROM Employee TIME • Large or optional precision (0 ~ 100ns) • Easy programming INSERT INTO T (time_col) VALUES (’12:30:29.1176548’) DATETIME2 • Large year range • Large or optional precision INSERT INTO T (datetime2_col) VALUES (‘1541-01-01’) DATETIME OFFSET • Datetime + time zone offset • UTC enabled • Easy programming CREATE TABLE online-purchase-order { item-id int, item-name VARCHAR(30), qty int, purchase-time datetimeoffset, … } // For value ‘2005-09-08 12:20:19.345 -08:00’ INSERT INTO online-purchase-order VALUES (…., ‘2005-09-08 12:20:19.345 -08:00’ ,..)
Slide 22: SQL Server 2005 Documents & Multimedia • Full Text Indexing SQL Server 2008 • Filestream • Integrated FTS Spatial • Fully supported Geometry and Geography data types and Functions • User Defined Types • XML Data Type and Functions •HierarchyID •Large UDTs •Sparse Columns •Wide Tables •Filtered Indices •XML Upgrades Relational Semi Structured
Slide 23: Documents & Multimedia Use File Servers Application BLOB DB Store BLOBs in Database Application Store BLOBs in DB + File System Application BLOB Dedicated BLOB Store Application BLOB BLOB DB DB DB Remote BLOB Storage SQL BLOB FILESTREAM Storage
Slide 24: Documents & Multimedia Store BLOBs in DB + File System Application BLOB Storage Attribute on VARBINARY(MAX) Works with integrated FTS Unstructured data stored directly in the file system (requires NTFS) Dual Programming Model TSQL (Same as SQL BLOB) Win32 Streaming APIs with T-SQL transactional semantics DB Advantages Integrated Manageability SQL Server Security Stack Dual model
Slide 26: Documents & Multimedia Full-Text Engine and Indexes fully integrated Catalog, index and stopword lists now inside the database Better performance in many common scenarios Make mixed queries perform and scale Optimizer has knowledge about FT index SELECT * FROM candidates WHERE CONTAINS(resume,’”SQL Server”’) AND ZipCode = ‘98052’
Slide 27: Documents & Multimedia 2 min 1 min Populating an index of 20million rows of 1k data on identical hardware (time in minutes)
Slide 28: Spatial Proliferation of geographical data GPS Systems, Virtual Earth, Live Search Maps etc New opportunities for spatially aware apps Storage and retrieval of spatial data using standard SQL New Spatial Data Types + methods + indexes geometry - Flat Earth (Planar) geography - Round Earth (Geodetic) Offers full set of Open Geospatial Consortium components
Slide 30: Relational Semi Structured 1 2 3 4 // Sparse column Create Table Products(Id int, Type nvarchar(16)…, Resolution int SPARSE, ZoomLength int SPARSE); // Filtered Indices Create Index ZoomIdx on Products(ZoomLength) where Type = ‘Camera’; // HierarchyID CREATE TABLE [dbo].[Folder] ( [FolderNode] HIERARCHYID NOT NULL UNIQUE, [Level] AS [FolderNode].GetLevel() PERSISTED, [Description] NVARCHAR(50) NOT NULL ); 5 1 2 3 4 5 HierarchyID Store arbitrary hierarchies of data and efficiently query them Large UDTs No more 8K limit on User Defined Types Sparse Columns Optimized storage for sparsely populated columns Wide Tables Support thousands of sparse columns Filtered Indices Define indices over subsets of data in tables
Slide 34: What is it? Technique for working with relational tables as if they were objects in memory Intention is to hide away the complexity of the underlying tables and give a uniform way of working with data Why use it? Productivity Retain database independence Which ORM? There are many ORMs for .NET developers already in existence. E.g. LLBLGen Pro http://www.llblgen.com/ Nhibernate http://www.hibernate.org/343.html EntitySpaces http://www.entityspaces.net/Portal/Default.aspx
Slide 35: LINQ to SQL .NET Framework 3.5, Nov 2007 Only SQL Server Simple Easy to learn and master LINQ to Entities (ADO.NET Entity Framework) .NET Framework 3.5 SP1, Aug 2008 “Any” RDBMS Complex Easy to learn, hard to master Strategic
Slide 38: Clients (Tools, Libraries, etc) HTTP (AtomPub) ADO.NET Data Services Framework SQL Data Services SQL Server (Cloud data service) (On premises data service)
Slide 39: REST / SOAP SDS Runtime Data Access Lib REST / SOAP SDS Runtime Data Access Lib REST / SOAP SDS Runtime Data Access Lib REST / SOAP SDS Runtime Data Access Lib REST / SOAP SDS Runtime Data Access Lib REST / SOAP SDS Runtime Data Access Lib REST / SOAP SDS Runtime Data Access Lib SQL Server Distribute d Data Fabric Mgmt. Services SQL Server Distribute d Data Fabric Mgmt. Services SQL Server Distribute d Data Fabric Mgmt. Services SQL Server Distribute d Data Fabric Mgmt. Services SQL Server Distribute d Data Fabric Mgmt. Services SQL Server Distribute d Data Fabric Mgmt. Services SQL Server Distribute d Data Fabric Mgmt. Services
Slide 40: Unit of geo-location and billing Tied to DNS name Collection of Containers
Slide 41: Entity properties may differ in type and instance
Slide 46: Drop me an email if I confused you about anything! eric.nelson@microsoft.com http://blogs.msdn.com/UKDevEvents Post event resources for all Microsoft UK developer focused sessions The team Eric Nelson http://blogs.msdn.com/ericnel Mike Ormond http://blogs.msdn.com/mikeormond Mike Taulty http://mtaulty.com