Transcript Developer Day What's new in SQL Server 2012 for Developers Meinrad Weiss Principal Consultant, Trivadis AG [email protected].

Developer Day
What's new in SQL Server 2012
for Developers
Meinrad Weiss
Principal Consultant, Trivadis AG
[email protected]
IT SOLUTIONS, SERVICES & PRODUCTS
Business Integration Services
Business Intelligence
Infrastructure
Engineering
Application Development
Managed Services
Training
Integration, Application Performance
Management, Security
TECHNOLOGIES
Microsoft, Oracle, IBM, Open Source
11 Trivadis locations with more than 600 employees
Financially independent and sustainably profitable
Key figures 2011
 Revenue CHF 104 / EUR 84 Mio.
 Services for more than 800 clients in over 1,900
projects
 200 Service Level Agreements
 More than 4,000 training participants
 Research and development budget:
CHF 5.0 / EUR 4 Mio.
Some new database feature
Professional database development with
SQL Server Data Tools
Agenda
A table of files and folders
User-created table with one row per file or folder
FILESTREAM and file attributes both available
Tree integrity maintained through system-defined
constraints
File and folder hierarchy
visible via a Windows share
Database storage is transparent
to Windows applications
Supports native Win32 APIs
CREATE TABLE ExternalFiles
AS FILETABLE WITH
(FILETABLE_DIRECTORY
='SharedFiles',
FILETABLE_COLLATE_FILENAME
= database_default);
FileTable is a fixed format table (SQL 2012)
Available as file system share or T-SQL table
Can be maintained through the share directly
Three circular arc data types
 CircularString
 CompoundCurve
 CurvePolygon
Based on ISO SQL/MM Part 3:Spatial standard
Drastically less data than representing curves by line segments
BufferWithCurves() Method
SQL Server 2008 results limited to a single
hemisphere
SQL Server 2012 supports full globe results
FULLGLOBE well-known text format
Static aggregate methods on GEOGRAPHY and
GEOMETRY classes
UnionAggregate()
EnvelopeAggregate()
CollectionAggregate()
ConvexHullAggregate()
UnionAggregate()
ShortestLineTo() provides shortest distance
between arbitrary objects
Not limited to just vertex points
Spatial data can be
easily visualized
using Bing-Maps
Similar to IDENTITY, but more flexibility
CREATE SEQUENCE SimpleSequence
START
WITH 1
INCREMENT BY
1;
SELECT NEXT VALUE
FOR SimpleSequence;
CREATE SEQUENCE SequenceWithCycle
START WITH 65
INCREMENT BY 1
MINVALUE
65
MAXVALUE 70
CYCLE
Common application requirement
to retrieve rows in “pages”
CREATE PROC GetPagedProducts_Paged
(@PageNumber
int, @NumberOfRowsPerPage int)
AS BEGIN
SELECT ProductID, Name, Size, Color
FROM AdventureWorks2012.Production.Product
ORDER BY ProductID
OFFSET
((@PageNumber - 1) * @NumberOfRowsPerPage) ROWS
FETCH NEXT @NumberOfRowsPerPage
ROWS ONLY;
END;
Some new database feature
Professional database development with
SQL Server Data Tools
Agenda
Next evolution of Visual Studio based
database development tools
Provides developers with a single tool to support database development needs
Most developers will not
need to install SSMS
Free availability
via Web Platform Installer
Connected model
Disconnected (project-based) development model
Code analysis
Dependency tracking
Refactoring
Intellisense
Data-tier Application (DAC Fx) model
Script generation also possible
SQL Server Data Tools
Deploy
“Get Definition”
SQL Server
Table
View
Proc
Func
…
Disconnected (
SQL Server Data Tools
(LocalDB)
Table
View
Proc
Func
F5
Table
View
Proc
Func
Snapshot
.dacpac
Deploy
Create Project
SQL Server
Table
View
Proc
Func
…
Replaces the need for SSMS for most developers
Rich set of functionality similar to Object Explorer in SSMS
Create/Drop and manipulate database objects
Build and Debug use LocalDB
No need for server-based
SQL Server installation on developer's machines
Multi-mode
table editor
GUI window
T-SQL window
Property window
Changes made in
one window
update in
other windows
Detailed information of differences between two
databases
Single project can target multiple platforms
SQL Server versions
SQL Azure
Project build provides
errors/warnings
Specific to target
No need to create
and execute
scripts to find issues
Design issues
Naming issues
Performance
issues
1. Preis
Wettbewerb
Am Trivadis Stand
2. Preis
Signed by
3. Preis
Thomas
Huber