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.