barbarin david sql server senior consultant pragmantic sql server denali : new development features
Post on 30-Dec-2015
228 Views
Preview:
TRANSCRIPT
David BARBARIN david.barbarin@pragmantic.com
SQL Server Senior Consultant at Pragmantic SA Collaborative solutions Business solutions Data Management and Business Intelligence
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_locator and parent_path_locator columns
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
FILETABLE
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
Semantic 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
FullText search
SET FMT ONLY will be replaced by
sys.sp_describe_first_result_setsys.dm_exec_describe_first_result_setsys.dm_exec_describe_first_result_set_for_object
Meta Data Discovery
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
Ad-hoc Query Paging
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
Execute statement
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
Sequences
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
Throw statement
Date and time functions
DATEFROMPARTSDATETIME2FROMPARTSDATETIMEFROMPARTSDATETIMEOFFSETFROMPARTSSMALLDATETIMEFROMPARTSTIMEFROMPARTSEOMONTH
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()
T-SQL enhancements
UTF-16 Supplementary Characters (SC) Collations
SC collations can improve searching and sorting by functions that use the Unicode types nchar and nvarchar
UTF16 Support Collations
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
Spatial types
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.
SSDT (Juneau Project)
Please help us make TechDays even better by Evaluating this Session. Thank you!
Give us your feedback!
© 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.
top related