SQL Server 2008 for .NET Developers

1. SQL Server 2008 for .NET Developers • Lynn Langit – Developer Evangelist – Microsoft • http://blogs.msdn.com/SoCalDevGal

2. What Will We cover? • SQL Server Spatial support • BLOB storage (Filestream) • T-SQL Enhancements • Table-valued parameters • New date / time • More • Visual Studio integration • SQL CLR • Business Intelligence • Reporting Services • More

3. What is Spatial Data? • Location-based questions • Which roads intersect property or each other? • How many stores/restaurants/houses/car dealerships are located within a geographical area? • Spatial Data Types • Geometry • Flat Earth model & X, Y coordinates • Geography • Round Earth model uses Latitude and Longitude coordinates • Can span only one logical hemisphere (more will throw exception) • Query for location info as any SQL Server data type • Data types dictated by OpenGIS standard • New spatial index type

4. Representing Spatial Data • Geospatial data in SQL Server • Stored in proprietary binary format • Can be input using simple text • Parse methods convert text into internal storage format • Represents areas using • Points, Linestrings, Polygons • MultiPoints, MultiLinestrings, MultiPolygons • GeomCollections • Define Polygons in counter-clockwise direction • Interior areas (“holes”) must be defined in clockwise direction • Supports various calculations and methods • Spatial Reference ID (SRID) identifies ellipsoid (flat/round, meters/feet) • Only instances with like SRID can be directly compared

5. SQL Server Geospatial and Virtual Earth

6. About BLOBs • 1. Old way #1 • Store BLOBs in DB as varbinary (max) up to 2 GB each • Easier management, but can be poor performance • 2. Old way # 2 • Store BLOBS in File system, path (link) in db • Good performance, but complex management • 3. New way • Best of both via Filestream attribute • Applied to varbinary(max) data type • Integrated database security • Uses dedicated Filegroup (easier maintenance) • Limits *no snapshots or mirroring and more

7. Using Filestream • 1. Enable Filestream for the machine • For T-SQL access • For file I/O streaming access • Name of share for storage • For streaming remote clients • Required integrated security • 2. Enable Filestream for the server instance • sp_configure filestream_access_level, (0, 1, 2) • Can limit to T-SQL access only (option 1) • 3. Create Filestream-enabled database • FILEGROUP FgName CONTAINS FILESTREAM (NAME = …) • Name is the directory (storage) name • Name (i.e. folder) must NOT exist prior to creating DB • Create table with Filestream column(s) • With specific requirements, next slide>>

8. Filestream Columns • Tables with Filestream columns must… • Contain a Uniqueidentifier column that is • Not nullable • Uses the ROWGUIDCOL attribute • Max one per table • Has a unique constraint on it • Can have more than ‘Filestream’ column • Varbinary(max) with Filestream attribute • Will probably have a default value assigned • Can be manipulated using • T-SQL • OpenSqlFilestream function - sample • Performance comparison here

9. Using Filestream

10. Enhancements to T-SQL • Table Value Parameters • New Date/Time types • Time zone awareness • New MERGE keyword • New shorthand notation

11. Table Value Parameters • CREATE TYPE as TABLE • Multiple tables inserted/updated within single transaction (MARS-like capability for writing) • Can pass strongly-typed arrays to stored proc as single call • ADO.NET updated to include support • Param type: SqlDbType.Structured • Can use PK, Unique, Check constraints • Index only for PK or Unique, no stats • Can specify the sort order • Can NOT use Defaults

12. When to use TVPs • TVPs • Pass large number of rows – uses tempdb • Results in simpler CRUD stored procs • READONLY after initial population • Temp table or CTE • CTEs – no indexing, in memory (unless out of memory, then in tempdb) • Can not pass as param

13. T-SQL EnhancementsTable Valued Parameters

14. Dates and Time • Several new types added • datetime2 • date • time • datetimeoffset • New functions added • CONVERT • SYS* Functions • DATEDIFF

15. MERGE • Insert, Update, Delete with one keyword • Merge two tables, source and target • WHEN MATCHED • WHEN TARGET NOT MATCHED • WHEN SOURCE NOT MATCHED • Syntax and use can be complex • MERGE syntax from SQL Server BOL

16. Shorthand Notation • Like .NET languages (C# or Visual Basic .NET) • Declare variable & assign value in one line of code • Compound assignment, i.e ‘+=‘, etc..

17. T-SQL EnhancementsDate/Time, Merge, Shorthand

18. Visual Studio Integration • Enhanced project types • Analysis Services • Integration Services • Reporting Services • Entity Framework • CLR Integration

19. Entity Framework • Models relational data using entities (OR/M tool) • Can model any relational data source for data access (provider may be required) • Application-specific domain • You can work at a higher level of abstraction • Appropriate amount of information returned • Lazy-loaded • What is an entity? • Structurally like an object (noun) • Has scalars and relationships • Has identity • Not tied specifically to SQL Server

20. Visual Studio IntegrationEntity Framework

21. SQL CLR • SQL CLR is “off by default” for security reasons • Must activate (instance) then per database to enable • Code executes in SQL Server processing stream • SQL Server has the ability to execute managed code • Stored procedures & Triggers • User-defined Types & Aggregates – extended size (up to 2 GB) • Regular Functions & Table value functions • Use mostly for complex processing • Use for Functions or Aggregates • Do not use for CRUD operations

22. Programming SQL CLR • Use Visual Studio templates for SQL CLR constructs • Uses Database project type • Can be remotely debugged • Metadata (attributes) control SQL Server behavior • Microsoft.SqlServer.Server.xxx • SqlProcedure, SqlFunction, SqlUserDefinedAggregate, SqlTrigger, SqlUserDefinedType, SqlFacet, SqlMethod • Microsoft.SqlServer.Server namespace (retrieves/returns data) • Very ADO.NET-like, but not ADO.NET • Uses SqlContext.Pipe • Assign CAS permission set (Safe, External, Unsafe) • Use for complex processing, i.e. RegEx parsing, etc…

23. Visual Studio IntegrationSQL CLR

24. Business Intelligence • SSAS – Analysis Services • Date storage and query • OLAP cubes or Data Mining Structures • BIDS to create • SSMS to administer • SSIS – Integration Services • ETL tool • SSRS - Reporting Services • Enterprise Reporting • BI can use any data source • Data Warehousing • Data Mining

25. Reporting Services • SQL Server standard for reporting • Managed enterprise reporting • Ad-hoc reporting • Embedded reporting • Web-based reporting • Included in SQL Server 2008 Express w/Advanced Services • Reports can be formatted in a variety of ways • PDF, Word 2007, HTML • Reports are exposed via a Web Service • Can be integrated with SharePoint • Dependency on IIS removed (uses http.sys)

26. Programming Reports • Design reports in Visual Studio 2008 SP1 • Query for data • Assign query results to report • Design report style • Colors, Fonts, etc… • Deploy report • Web Service • SharePoint Services • Wizard available for initial report design – also Report Builder • Enhanced configuration and administration tool

27. Visual Studio IntegrationReporting Services and more

28. But there’s more… • For Developers • Change Data Capture • Filtered Indexes • Hierarchy ID ( ) • Sparse data columns • XML Enhancements • Integrated Full-Text Search • For Administrators • Policy-based management • Auditing enhancements • Transparent Encryption • Compression • PowerShell integration • More…

29. Microsoft Press Publications Lynn’s blog http://blogs.msdn.com/SoCalDevGal

30. Get Microsoft Software for FREE For high school and college students - DreamSpark For startup businesses - BizSpark For everyone - Express editions – VS and SQL - here