Denali – Day 20: More Programmability Feature

Denali – Day 20: More Programmability Feature

  • Native XML Web Services (SOAP/HTTP endpoints) is Removed:

 

Earlier version of sql server support Web services of End points to connect to sql server, could be due to security reason (my opinion) this has been removed, now you could not able to create and alter Endpoints on HTTP Web Services.

 

  • Statistical Semantic Search:

On Denali there is a new and improve search has been introduced “Statistical semantic search” which search the word and relevant/synonyms word search as well.

>> Core question of ,property or content, more specific

 

  • Check is it installed

 

Select SERVERPROPERTY(‘IsFullTextInstalled’)

 

  • For this you have to install an additional database explicitly from setup SematicLanguagedatabase.msi
  • Attach the “SemanticsDB” database
  • Register that database

    sp_fulltext_semantic_gegister_language_statistics_db @dbname=’ SemanticsDB’

     

For more information search “Install and Configure Semantic Search” at msdn/technet

 

Semantic search builds upon the existing full-text search feature in SQL Server, but enables new scenarios that extend beyond keyword searches. While full-text search lets you query the words in a document, semantic search lets you query the meaning of the document.”

 

 

  • Full-Text Search Enhancement:

Indexes everything in doc, from Denali it has included

“property Search” as well. – property of the index

Support NEAR clause.

 

 

  • fmtonly Replaced:

    Earlier to this we use Set fmtonly ON

    Which will display the metadata information of the query, without returning any rows output. Only shows list of columns.

    This will be decrypted in future version of sql server and is replaced with

    Sp_descibe_first_result_set

    Sp_describe_undeclared_parameters

    Sys.dm_exec_describe_first_result_set

    Sys.dm_exec_describe_first_result_set_for_object

     

     

  • Enhanced Execute Statement “With RESULT SETS):

    We can include Exec statement “With RESULT SETS this way we can specify the “metadata” column name and its data type for the output returned.

     

    Eg:

    EXEC (‘select name,id,xtype,crdate from sys.sysobjects’ )

    WITH RESULT SETS

    ( nm varchar(100), I int, t varchar(10), cdt datetime))

     

We can also execute a stored procedure using EXEC.

 

 

  • New Query Optimization hint – FORCESCAN

 

FORCESCAN is a new optimization hint has been introduced in Denali, it is like FORCESEEK, but here it will ask optimizer to forcefully use a to perform SCAN operation.

*Table hint is not good for performance point of view, please always test the query performance before use. Also keep monitoring performance of hint at regular basis.

Advertisements
This entry was posted in Denali and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s