Microsoft Confidential Jon Jahren Produktsjef Applikasjonsplattform Microsoft.

Microsoft Confidential

SQL Server 2008 for utviklere

Jon JahrenProduktsjef ApplikasjonsplattformMicrosoft


Trusted. Productive. Intelligent

• Integrate any data

• Deliver relevant information

• Drive actionable insights

Intelligent• Manage by policies

• Simplify Application Development

• Store any information

Productive• Protect your

information• Ensure business

continuity• Predictable response

Trusted

Your Data Any Place, Any Time


AgendaDynamic Development

T-SQL improvementsDate & Time enhancementsImprovements with CLR integrationTable types & Table-valued parameters

Beyond RelationalFull-text improvementsFilestream storageHierarchy managementSparse columnsFiltered indexesSpatial support


Dynamic Development


T-SQL “Delighters”Can you spot the feature?

Continued investment and innovation in T-SQL

DECLARE @t int = 5;

INSERT dbo.myTVALUES (‘WA’, @t), (‘FL’, @t+1);

UPDATE dbo.myTSET instances+=1;

CTP 3


Where have I heard this…HiCould you please provide:

Separate date & time data typesLarger fractional second precisionLarger range of datesTime zone awarenessDatabase migration – compatibility with competitors’ solutions

Thanks,


Date & Time Enhancements4 new data typesSQL Standard compatible

• Date only• From 0001-01-01 to 9999-01-01 in

Gregorian calendar Date

• Time only• Optional user specifiable fractional

precisions up to 100 nanosecondsTime (n)• Time-zone aware/preserved UTC datetime • Optional user specifiable fractional

precisions up to 100 nanosecondsDateTimeO

ffset(n)• Large date range• Optional user specifiable fractional precisions up

to 100 nanoseconds (Default)• Time-zone NOT aware

DateTime2 (n)

CTP 4


Date and Time Data TypesCREATE TABLE t1 (c1 DATE, c2 TIME(3), c3 DATETIME2(7) NOT NULL DEFAULT GETDATE(), c4 DATETIMEOFFSET CHECK

(c4<CAST(GETDATE() AS DATETIMEOFFSET(0))));

INSERT INTO t1 VALUES ('0001-01-01', '23:59:59', '0001-12-21 23:59:59.1234567', '0001-10-21 23:59:59.1234567 -07:00');

INSERT INTO t1 VALUES ('9999-12-31', '23:59:59', '9999-12-31 23:59:59.1234567', '1111-10-21 23:59:59.1234567 -07:00');

SELECT c4, DATEPART(TZOFFSET, c4), DATEPART(ISO_WEEK, c4), DATEPART(MICROSECOND, c4) FROM t1;

CTP 4


CLR IntegrationUser-defined types

Support for large instances (>8000)User-define aggregates

Support for large serialization (>8000)Multi-column input

Table-valued functionsSupport for Order declarationEnforced at runtime

CREATE FUNCTION myTVF()RETURNS TABLE (@i int, @j int, @k int)WITH ORDER i ASC, j DESCAS EXTERNAL NAME …

CTP 5


Table-Valued ParametersHave you ever wanted to do something like:

create proc ins_data (@t table (a int)) as …

And got something like:

CTP 3


Introducing Table TypesUser-defined Table Types

A new user defined typeAligned with inlined table definition for table variablesCan be used for declaring table variablesCan define indexes and constraintsNew Catalog view for table types Sys.table_typesTVP (Table-valued Parameters) for Sps/UDFs

BenefitsUsability, Type Matching, Precise Typing

CTP 3


Beyond Relational


SQL Server 2008 XML UpgradesImproved XML Schema Validation

Support for lax validationFull support for storing & validating Office 12 Document formatsFull xs:dateTime support

Support for no timezone valuestimezone preservation

Support for lists and union typesAdded support for let-clause in XQueryAdded support insert sql:variable(“@xml”) into /a/b

CTP 5


Full-Text Indexes fully integrated into SQL ServerMake mixed queries perform and scale

SELECT * FROM candidates WHERE CONTAINS(resume,’”SQL Server”’)

AND ZipCode = ‘98052’

CTP 6

Full Text Indexing Enhancements


Filestream StorageStorage Attribute on VARBINARY(MAX)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

Data ConsistencyIntegrated Manageability

Back Up / RestoreAdministration

Size limit is the file system volume sizeSQL Server Security Stack

Store BLOBs in DB + File System

Application

BLOB

DB

CTP 5


Hierarchy ManagementNew data type: HierarchyIdRich built-in methods for manipulating hierarchies

Simplifies storage and querying of hierarchical data

Supports depth-first and breadth-first indexes

CTP 4


Sparse Columns Scenarios

Semi-structured Data: Property Set Storage

Databases with heterogeneous dataDistinct properties associated with subsets of dataLarge Number of sparsely populated properties

ExamplesProduct Catalogs

Distinct Product specific propertiesDocument Management Systems

Document specific/user-defined propertiesGPS/Mapping Systems

Location/Business specific Properties

CTP 6


Sparse ColumnsNULL values: 0 bytes. Non-NULL: +(2-4)b. Slightly slower to access

pk c1 sc1

sc2

sc3

sc4

sc5

sc6

sc7

sc8

sc9

1 A (sc1, sc9) (1,9)2 B (sc2, sc4) (2,4)3 C (sc6, sc7) (6, 7)4 D (sc1, sc5) (1,5)5 E (sc4, sc8) (4, 8)6 F (sc3, sc9) (3,9)7 G (sc5, sc7) (5, 7)8 H (sc2, sc8) (2, 8)9 I (sc3, sc6) (3, 6)

CTP 6


Sparse Columns – Wide TablesVery large number of sparse columns per table –

100,000!pk c1 sc

1sc2

sc3

sc4

sc5

sc6

sc7

sc8

sc9

… sc100000

1 A (sc1, sc9) (1,9)2 B (sc2, sc4) (2,4)3 C (sc6, sc7) (6, 7)4 D (sc1, sc5) (1,5)5 E (sc4, sc8, sc100000) (4, 8, 100000)6 F (sc3, sc9) (3,9)7 G (sc5, sc7) (5, 7)8 H (sc2, sc8) (2, 8)9 I (sc3, sc6, sc50000) (3, 6, 50000)

CTP 6


Sparse ColumnsColumn Set

A logical grouping for all sparse columns in a table

Create Table Products(Id int, Type nvarchar(16), ProductProperties XML COLUMN_SET

FOR ALL_SPARSE_COLUMNS);Updateable, computed XML columnSelect * returns all non-sparse-columns, sparse column set Allows generic retrieval/update of sparse columns as a set

Limits are maintained throughout the system

CTP 6


Filtered Indexespk c1 sc

1sc2

sc3

sc4

sc5

sc6

sc7

sc8

sc9

1 A 1 92 B 2 43 C 6 74 D 1 55 E 4 86 F 3 97 G 5 78 H 2 89 I 3 6

CTP 6


Filtered Indexes

Create index i1 on T(sc1) where C1=A or C1=D; select sc1 from T where c1=A and sc1>5

pk c1 sc1

sc2

sc3

sc4

sc5

sc6

sc7

sc8

sc9

1 A 1 92 B 2 43 C 6 74 D 1 55 E 4 86 F 3 97 G 5 78 H 2 89 I 3 6

CTP 6


Filtered Indexes

Create index i1 on T(sc1) where C1=A or C1=D; select sc1 from T where c1=A and sc1>5

Create index i2 on T(sc7) where sc7 IS NOT NULL; select sc1 from T where sc7=5

pk c1 sc1

sc2

sc3

sc4

sc5

sc6

sc7

sc8

sc9

1 A 1 92 B 2 43 C 6 74 D 1 55 E 4 86 F 3 97 G 5 78 H 2 89 I 3 6

CTP 6


Putting it all together…

1

2

3

4

5

HierarchyID Store arbitrary hierarchies of data and efficiently query them

Large UDTs No more 8K limit on User Defined TypesSparse Columns Optimized storage for sparsely populated columns

Wide Tables Support for hundreds of thousands of sparse columnsFiltered Indices Define indices over subsets of data in tables

1 3

4

5

2


Spatial in SQL Server 2008Provide vector supportTarget geospatial

Spatial data which is referenced to a location on the EarthTypically uses spherical coordinates or projected planar coordinates

No restriction on data actually being geospatial

Location aware applications become mainstream!


Sample Query

Which roads intersect Microsoft’s main

campus?

SELECT *FROM roads WHERE roads.geom.Intersects(@ms)=1


Round Earth / Flat Earth ModelsGeodetic Planar


Spatial UsesMapping

Many applications make direct use of mappingThe map may very well be the primary output of these applicationsExamples:

Consumer mapping products (Virtual Earth, etc.)Utility (electrical / water / gas) grid layoutsBusiness geographics

Non-MappingMany applications may make use of spatial data, even if they do not explicitly make mapsExamples:

Send warehouse pickers on efficient runsPredict bus arrival timesYour favorite LOB app here


Two New Data TypesGeometry

Geometry can store instances of various types

Points, Line strings, PolygonsCollections of the above

Methods for computingSpatial relationships: intersects, disjoint, etc.Spatial constructions: intersection, union, etc.Metric functions: distance, areaPlanar is conceptually simpler, but more specialized

Geography

Very similar interface to geometry

Some methods have different semantics

Most data commonly available user data is geodetic

CTP 5

Spatial Indexing supported for both data types


© 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after

the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Microsoft Confidential Jon Jahren Produktsjef Applikasjonsplattform Microsoft.

Documents

Transcript of Microsoft Confidential Jon Jahren Produktsjef Applikasjonsplattform Microsoft.