Transcript SQL Server Denali : New development features BARBARIN DAVID SQL Server Senior Consultant Pragmantic.

SQL Server Denali :
New development features
BARBARIN DAVID
SQL Server Senior Consultant
Pragmantic
David BARBARIN
[email protected]
SQL Server Senior Consultant at Pragmantic SA
Collaborative solutions
Business solutions
Data Management and Business Intelligence
Unstructured data and
FILETABLE
FILETABLE
The FileTable feature builds on top of the SQL Server
FILESTREAM technology
Represent a hierarchy of folders and file storage
Non transactional access
Can be used by Windows applications without
changes
FILETABLE
A row in a FileTable contains :
A FILESTREAM column for stream data and a file_id(GUID) identifier. (NULL if a directory is
concerned)
Both path_locatorand parent_path_locatorcolumns
10 file attributes such as created date and modified date that are useful with file I/O APIs
A type column that supports full-text search and semantic search over files and documents.
A FileTable enforces certain system-defined constraints and triggers to maintain file namespace
semantics.
When the database is configured for non-transactional access, directory and files can be access by
the filestream network share name
demo…
FileTable
FullText search and semantic
search
Semantic search
Semantic search builds upon the existing full-text
search feature in SQL Server but enables new
scenarios that extend beyond syntactical keyword
searches
Full-text search lets you query the words in a
document
Semantic search lets you query the meaning of the
document
Semantic search
Before you can use Semantic Search, you have to
install, attach, and register the Semantic database.
This database contains the statistical language
models required by semantic search
Don’t forget to install the Latest Filters to allow
indexing the concerned documents
FullText search
Property Search is possible in the documents. The
result depends directly on the I-filters.
Customizable NEAR (CONTAINS(column_name,
'NEAR((John, Smith),2)') )
Update of all word breakers and stemmers used by
Full-Text Search except to Korean language
Meta Data Discovery
Meta Data Discovery
SET FMT ONLY will be replaced by
sys.sp_describe_first_result_set
sys.dm_exec_describe_first_result_set
sys.dm_exec_describe_first_result_set_for_object
demo…
Metada Discovery
Ad-hoc Query Paging
Ad-hoc Query Paging
Paging still exists in SQL Server by using different
methods
Implementation of paging like “MySQL” simplifies the
search of a range of values
ORDER BY clause has been enhanced with OFFSET
and FETCH NEXT … ROWS ONLY options
demo…
ORDER BY, OFFSET and FETCH
Execute statement enhanced
Execute statement
The EXECUTE statement can now specify the metadata returned from
the statement by using the WITH RESULT SETS argument
Allow the encapsulation of a stored procedure.
Can be a contract between a stored procedure and the Direct Access
Layer of an application
Change in the stored procedure doesn’t not imply change on the DAL
Select data doesn’t require anymore a temp table
Conversion with SSIS source and destination is simplier
demo…
Execute statement
Sequence objects
Sequences
Sequences are now possible with SQL Server 2012.
A Sequence object is a user-defined schema-bound
object. It operates similar to an identity column, but
sequence numbers are not restricted to use in a single
table
The last-used value is stored in memory
CREATE SEQUENCE and NEXT VALUE FOR
demo…
Sequences
THROW statement
Throw statement
Like Csharp or other language THROW raises an
exception and transfers execution to a CATCH block
of a TRY…CATCH construct
THROW does not need a predefined message in
sys.messages to work unlike RAISERROR
It's not possible to redefine the severity by THROW
(always 16) unlike RAISERROR
demo…
Throw statement
T-SQL enhancements
T-SQL enhancements
Conversion functions
PARSE
TRY_PARSE
TRY_CONVERT
T-SQL enhancements
Date and time functions
DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS
EOMONTH
T-SQL enhancements
Logical functions
CHOOSE
IIF
T-SQL enhancements
String functions
CONCAT
FORMAT
T-SQL enhancements
Logarithm computing :
SQL Server supported only two functions that compute logarithms:
LOG (for natural logarithm) and LOG10 (for logarithm with a base
of 10).
LOG function is modified with a second optional option : base that
allows to compute a LOG with an another base
SELECT LOG(256, 2) equivalent to SELECT LOG(256) / LOG(2);
T-SQL enhancements
New Analytic Functions are added
CUM_DIST()
PERCENTILE_CONT()
PERCENTILE_DISC()
PERCENTILE_RANK()
FIRST_VALUE()
LAST_VALUE()
LEAD()
LAG()
demo…
T-SQL enhancements
UTF16 Support Collations
UTF-16 Supplementary Characters (SC) Collations
SC collations can improve searching and sorting by
functions that use the Unicode types nchar and
nvarchar
Spatial types
New methods for geometry and geography data
types that work with circular arc segments
New static aggregate methods for geometry data
type and geography data type
SQL Server Developer Tools
SSDT (Juneau Project)
Juneau provides a unified set of tools that combines :
the projects found in SQL Server Business Intelligence
Design Studio - including projects of Integration
Services, Analysis and Reporting
the project database SQL Server.
demo…
SQL Server Developer Tools
Give us your feedback!
Please help us make TechDays even better by
Evaluating this Session. Thank you!
© 2011 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.