SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. DMVs expose vital diagnostic information which are not only useful to indicate the overall health of the SQL Server, but also of the databases hosted by the given SQL Server instance. The list of DMVs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

A complete list of the full text and semantic search related DMVs and Functions is available at the link below:

• Full-Text Search and Semantic Search Dynamic Management Views and Functions (Link)
• Full-Text Search and Semantic Search Catalog Views (Link)

Let us looking at the Catalog Views related to the new feature introduced in Microsoft SQL Server 2012 – Semantic Search.

Catalog Views

sys.fulltext_semantic_language_statistics_database

Introduced In: SQL Server 2012

Purpose: The Semantic Language Statistics Database is a required component for the Statistical Semantic Search feature in Microsoft SQL Server 2012. This database can be downloaded and configured from http://www.microsoft.com/en-in/download/details.aspx?id=29069.

The catalog view sys.fulltext_semantic_language_statistics_database returns information about the statistical semantics database installed on the particular SQL Server instance. If the database is not installed, no records are returned.

USE AdventureWorks2012;
GO
SELECT * FROM sys.fulltext_semantic_language_statistics_database;
GO

The output looks similar to the screenshot shown below.

Permissions: Visibility is limited to securables that the user owns or has been granted permission to.

BOL Link: http://technet.microsoft.com/en-us/library/gg492069.aspx

sys.fulltext_semantic_languages

Introduced In: SQL Server 2012

Purpose: Returns a record for each language whose statistics model is registered with the given SQL Server instance through the statistical semantics database (see above).

USE AdventureWorks2012;
GO
SELECT * FROM sys.fulltext_semantic_languages;
GO

The output is quite simple (shown below):

 

Permissions: Visibility is limited to securables that the user owns or has been granted permission to.

BOL Link: http://technet.microsoft.com/en-us/library/gg492217.aspx

sys.registered_search_properties

Introduced In: SQL Server 2012

Purpose: Contains a row for each search property contained by any search property list on the current database. Property lists can be used to enable full-text searches to work with document properties like the Author name or title. For more information, please refer: http://technet.microsoft.com/en-us/library/ee677637.aspx

My environment has not yet been configured to use property lists and therefore no records were returned by the catalog view.

Permissions: Visibility is limited to securables that the user owns or has been granted permission to via a REFERENCE permission or otherwise.

BOL Link: http://technet.microsoft.com/en-us/library/ee677608.aspx

sys.registered_search_property_lists

Introduced In: SQL Server 2012

Purpose: Contains a row for each search property list in the current database. For more information, please refer: http://technet.microsoft.com/en-us/library/ee677637.aspx

My environment has not yet been configured to use property lists and therefore no records were returned by the catalog view.

Permissions: Visibility is limited to securables that the user owns or has been granted permission to via a REFERENCE permission or otherwise.

BOL Link: http://technet.microsoft.com/en-us/library/ee677629.aspx

Dynamic Management Views

sys.dm_fts_semantic_similarity_population

Introduced In: SQL Server 2012

Purpose: For all tables that have an associated semantic search index, this DMV returns the status information about the population of the semantic document similarity index.

As highlighted in http://msdn.microsoft.com/en-us/library/gg492085.aspx, the semantic indexing is essentially a two step process. The first step includes population of the full-text keyword index and the semantic key phrase index as well as extraction of document similarity data. The next phase uses this information to generate the semantic document similarity index.

On my test server, the generation was under the “starting” phase when the query was executed and screenshot captured.

USE AdventureWorks2012;
GO
SELECT * FROM sys.dm_fts_semantic_similarity_population;
GO

Permissions: VIEW SERVER STATE permission on the server.

BOL Link: http://msdn.microsoft.com/en-us/library/gg492070.aspx

Until we meet next time,

Be courteous. Drive responsibly.

When SQL Server 2005 came about, the following error message was encountered by many developers so many times that the answer for it would be available if asked in the middle of the night:

Msg 209, Level 16, State 1, Line 8
Ambiguous column name ‘BusinessEntityID’.

This message generally comes when one has the same column name/alias defined in an ORDER BY operation. Since ORDER BY operates on the SELECT-ed result set, it needs that the column names/aliases it operates on are unique and/or properly qualified if they are coming from different tables. Here’s an example which generates the message shown above:

USE AdventureWorks2012;

GO

–Query below results in error: “Ambiguous column name ‘BusinessEntityID'”

SELECT he.BusinessEntityID,

       heph.BusinessEntityID,

       heph.Rate,

       heph.PayFrequency,

       heph.RateChangeDate

FROM HumanResources.Employee AS he

INNER JOIN HumanResources.EmployeePayHistory AS heph ON he.BusinessEntityID = heph.BusinessEntityID

ORDER BY BusinessEntityID;

GO

Recently, we were conducting an interview and one of the panel members came up with a modification to this query. Instead of having an ambiguous reference in the ORDER BY clause, the query was modified such that two columns had the same column alias in the SELECT statement:

–What will be the output of the query below?

–Will it return an error?

–Please explain your answers

USE AdventureWorks2012;

GO

SELECT he.BusinessEntityID AS EmployeeId,

       heph.BusinessEntityID AS EmployeeId,

       heph.Rate,

       heph.PayFrequency,

       heph.RateChangeDate

FROM HumanResources.Employee AS he

INNER JOIN HumanResources.EmployeePayHistory AS heph ON he.BusinessEntityID = heph.BusinessEntityID;

GO

The question therefore are very simply:

What will be the output of the query? Will it return an error? Please explain your answers.

Here’s the correct answer to this question:

This query will succeed compilation and execution. The output will be similar to the one shown below – first two columns will be named “EmployeeId”, followed by “Rate”, “PayFrequency” and finally “RateChangeDate”.

The query succeeds because column aliases do not affect query evaluation in any way – they are only for representational purposes. The real problem would come when the aliases are used in an ORDER BY clause or in another query as a sub-query or CTE.

–Use duplicate column aliases in CTE results in an error

USE AdventureWorks2012;

GO

;WITH HumanResourcesPayCTE (EmployeeId, EmployeeId, Rate, PayFrequency, RateChangeDate)

AS (SELECT he.BusinessEntityID AS EmployeeId,

           heph.BusinessEntityID AS EmployeeId,

           heph.Rate,

           heph.PayFrequency,

           heph.RateChangeDate

    FROM HumanResources.Employee AS he

    INNER JOIN HumanResources.EmployeePayHistory AS heph ON he.BusinessEntityID = heph.BusinessEntityID

    )

SELECT * FROM HumanResourcesPayCTE;

GO

Msg 8156, Level 16, State 1, Line 1
The column ‘EmployeeId’ was specified multiple times for ‘HumanResourcesPayCTE’.

This small twist in the question was enough to send the candidates scurrying to re-read the Books On Line!

I hope you found the post useful.

Until we meet next time,

Be courteous. Drive responsibly.

[EDIT: February 19, 2013, 14:20 IST]: Added clarification to the reason as to why ORDER BY returns an ambiguous column name error.

SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. DMVs expose vital diagnostic information which are not only useful to indicate the overall health of the SQL Server, but also of the databases hosted by the given SQL Server instance. The list of DMVs that changed in SQL Server 2008 R2/2012 is available in one of my previous posts here: http://bit.ly/XobXup.

A full list of documented database & index related DMVs is available here:

• Database DMV
  
• Index related DMVs

The database and index related DMVs that were introduced in SQL Server 2008 R2/2012 are:

• sys.dm_db_uncontained_entities
  
• sys.dm_db_log_space_usage
  
• sys.dm_db_fts_index_physical_stats

sys.dm_db_uncontained_entities

Introduced In: SQL Server 2012

Purpose: Microsoft SQL Server 2012 introduces the concept of “Contained Databases” – A contained database has no external dependencies on server-based settings or metadata to define the database. Users defined in a contained database do not have a dependency on logins created on the server, and therefore contain all information necessary to authenticate a user within the database.

You can get a complete hands-on on Contained databases via my three part series based on the labs conducted by Jacob Sebastian (B|T) during TechEd 2011.

• Part 01: http://bit.ly/YMgM7M
  
• Part 02: http://bit.ly/XQ6Tz6
  
• Part 03: http://bit.ly/VGDZDQ

The sys.dm_db_uncontained_entities DMV provides a list of all uncontained entities within a database. In simple terms, the objects corresponding to the records returned by this DMV depend upon objects that are outside of the database boundary. For example, when executed against the AdventureWorks2012 sample database, the following output result set is seen:

USE AdventureWorks2012;

GO

SELECT * FROM sys.dm_db_uncontained_entities;

GO

A quick analysis of the output shows us that the class and class_desc columns describe the nature of the uncontained database object. To verify this, let’s look up the object with major_id = 343672272 (If the class = 1, the major_id would contain the object_id of the object – refer the MSDN/BOL link below).

USE AdventureWorks2012;

GO

SELECT OBJECT_NAME(343672272) AS ObjectName;

GO

If we look at the text for the stored procedure, we can see that on line #20, system function serverproperty is used. This is also shown in the output under the “feature_name” column and is used to assign a value to a variable (also indicated by the “statement_type” column in the output). Since the system built-in function serverproperty will be available on all SQL Server 2012 instances, we are okay in this being a non-contained reference.

The end objective of this output is to provide a list of all non-contained objects to the user so that it can be ensured that all possible objects can be contained within the database, thereby making administration and maintenance of the database easier.

Permissions: The DMV sys.dm_db_uncontained_entities returns information only about the objects to which a user has access to. A database may have uncontained entities within a user-defined schema, but if the user does not have permissions on the schema, these objects will not be reported. It is therefore recommended that this be executed under the context of a user who is a member of the sysadmin fixed server role or is a db_owner.

BOL Link: http://msdn.microsoft.com/en-in/library/ff929336.aspx

sys.dm_db_log_space_usage

Introduced In: SQL Server 2012

Purpose: To help identify the amount of transaction log space consumed for the current database. This view can also be treated as a replacement for the command DBCC SQLPERF(LOGSPACE) with the only difference being that DBCC only on all databases on a given SQL Server instance, whereas sys.dm_db_log_space_usage works only on the current database.

Here’s an example:

USE AdventureWorks2012;

GO

–Using DBCC

DBCC SQLPERF(LOGSPACE);

GO

–Using DMV

SELECT su.database_id AS DatabaseId,

       DB_NAME(su.database_id) AS DatabaseName,

       su.total_log_size_in_bytes AS TotalLogSizeInBytes,

       (su.total_log_size_in_bytes/1024)/1024.0 AS TotalLogSizeInMB,

       su.used_log_space_in_bytes AS UsedLogSpaceInBytes,

       (su.used_log_space_in_bytes/1024)/1024.0 AS UsedLogSpaceInMB,

       su.used_log_space_in_percent AS UsedLogSpacePercentage

FROM sys.dm_db_log_space_usage AS su;

GO

As can be seen from the example above, all columns from the output of the DBCC statement can be derived from sys.dm_db_log_space_usage.

Permissions: VIEW SERVER STATE permissions (not tested).

BOL Link: Undocumented

sys.dm_db_fts_index_physical_stats

Introduced In: SQL Server 2012

Purpose: Returns the logical size of each full-text or semantic index in every table that has an associated full-text or semantic index. Here’s an example for the AdventureWorks2012 sample database:

USE AdventureWorks2012;

GO

SELECT OBJECT_NAME(ftsi.object_id) AS ObjectName,

       ftsi.object_id,

       ftsi.fulltext_index_page_count,

       ftsi.keyphrase_index_page_count,

       ftsi.similarity_index_page_count

FROM sys.dm_db_fts_index_physical_stats as ftsi;

GO

Permissions: VIEW SERVER STATE permissions

BOL Link: http://msdn.microsoft.com/en-us/library/gg492194.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Microsoft SQL Server 2008 came with a wide array of T-SQL enhancements. One of them was the ability to split a DATETIME value into DATE and TIME values. From a storage standpoint this allowed us to store and bind (on the UI) date and time values separately, while appending the values when displaying on a report or exporting to a 3rd party system. They are also useful to store time revisions with respect to a DATETIME value.

Here’s a quick example:

SQL Server 2008/2008 R2

In the query below, I will be taking two variables – a DATETIME and a TIME value and then add and subtract the TIME value from the DATETIME value to get the final result.

--Please execute on a SQL 2008/2008 R2 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + @tTime) AS DateTimeAddResult,
       (@tDate - @tTime) AS DateTimeSubtractResult;
GO

The output of the above query is shown below:

As you can see, we were able to successfully add and subtract the DATE and the TIME values to create corresponding DATETIME values.

Changes in SQL Server 2012

If your application uses code similar to the one shown above, it will not work as expected in SQL Server 2012 (SQL 11/”Denali”):

--Please execute on a SQL 2012 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + @tTime) AS DateTimeAddResult;

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate - @tTime) AS DateTimeSubtractResult;
GO

The following error message is encountered:

Msg 402, Level 16, State 1, Line 6

The data types datetime and time are incompatible in the add operator.

Msg 402, Level 16, State 1, Line 10

The data types datetime and time are incompatible in the subtract operator.

As you can see, SQL Server 2012 no longer allows us to add/subtract a TIME value from a DATETIME value using the conventional Add (+) and Subtract (-) operators.

Workaround

A workaround to this issue is to modify the code such that the TIME value is converted to a DATETIME value before the Add (+) or Subtract (-) operation takes place.

--Please execute on a SQL 2012 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';

SELECT @tDate AS DateTimeSource, 
       @tTime AS TimeSource,
       (@tDate + CAST(@tTime AS DATETIME)) AS DateTimeAddResult,
       (@tDate - CAST(@tTime AS DATETIME)) AS DateTimeSubtractResult;
GO

Conclusion

Between just two releases, this is a considerably major change because one may have a considerable number of objects written using this ability of DATETIME and DATE/TIME data-types to add/subtract. With an upgrade of the database to SQL Server 2012, these objects will need to be modified. I would therefore present the following questions before you:

• Have you faced this issue in your migration from SQL Server 2008 to SQL Server 2012?
• What solution/workaround did you apply?

Reference:

• DATETIME datatype: http://msdn.microsoft.com/en-us/library/ms187819.aspx
• DATE datatype: http://msdn.microsoft.com/en-us/library/bb630352.aspx
• TIME datatype: http://msdn.microsoft.com/en-us/library/bb677243.aspx

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server Catalog Views & Dynamic Management Views (DMVs) take an administrator to the very heart of SQL Server. They allow administrators to carry out essential functions like monitoring, diagnostics and inventory preparations. This leads one to think that whenever a new functionality is introduced into SQL Server, corresponding DMVs and Catalog Views must either be updated and/or new ones introduced.

One of the major enhancements in SQL Server 2012 is the SEQUENCE (refer the “Further Reading” section for more information on Sequences and their usage). In this post, I will be studying the use of a related Catalog View: sys.sequences. Needless to say, this Catalog View is new to SQL Server 2012.

sys.sequences

To get started, let me create a sequence and use it to populate a result set (re-using Pinal’s example here):

USE AdventureWorks2012

GO

CREATE SEQUENCE [Seq]

AS [int]

START WITH 1

INCREMENT BY 1

MAXVALUE 20000

GO

— First Run

SELECT NEXT VALUE FOR Seq, c.CustomerID

FROM Sales.Customer c

GO

Now, I will execute the following query which fetches all records from the new Catalog View – sys.sequences.

SELECT ss.name,

       ss.object_id,

       ss.principal_id,

       ss.schema_id,

       ss.parent_object_id,

       ss.type,

       ss.type_desc,

       ss.create_date,

       ss.modify_date,

       ss.is_ms_shipped,

       ss.is_published,

       ss.is_schema_published,

       ss.start_value,

       ss.increment,

       ss.minimum_value,

       ss.maximum_value,

       ss.is_cycling,

       ss.is_cached,

       ss.cache_size,

       ss.system_type_id,

       ss.user_type_id,

       ss.precision,

       ss.scale,

       ss.current_value,

       ss.is_exhausted

FROM sys.sequences AS ss

As we can see from the output, this Catalog View returns one record for each sequence object in the database. Below is a detailed summary of the most important columns with respect to the values supplied in the CREATE SEQUENCE statement:

• start_value – Value defined in the START WITH clause
  
• increment – Value defined in the INCREMENT BY clause
  
• minimum_value – Value defined in the MINVALUE clause
  
• maximum_value – Value defined in the MAXVALUE clause
  
• is_cycling – 1 if CYCLE clause has a value of 1, else 0
  
• is_cached – 1 if CACHE clause has a value of 1, else 0
  
• cache_size – The cache size defined during sequence creation
  
• system_type_id – A sequence can be created for multiple data-types. This column returns the ID of the system type for sequence objects’ data type
  
• user_type_id – This column returns the ID of the multiplied type for sequence objects’ data type
  
• precision – (self-explanatory)
  
• scale– (self-explanatory)
  
• currrent_value– This contains the last obligated value of the sequence.
  
• is_exhausted – A value of 0 means that more values can be generated from the system and that a manual re-cycle of the Sequence is not required

Further Reading:

• From Pinal Dave, a.k.a. “SQLAuthority” (B|T)
  
  
  
  • Introduction to SEQUENCE
    
  • SEQUENCE is NOT same as IDENTITY
    
  • Fun with SEQUENCE (Part 1)
    
  • Fun with SEQUENCE (Part 2)
    
  • Fun with SEQUENCE (Part 3)
  
  
• From Vinod Kumar, a.k.a. “Extreme Experts” (B|T)
  
  
  
  • Using SEQUENCE
  
  
• CREATE SEQUENCE BOL Reference: http://msdn.microsoft.com/en-us/library/ff878091.aspx
  
• sys.sequences BOL Reference: http://msdn.microsoft.com/en-us/library/ff877934.aspx

Until we meet next time,

Be courteous. Drive responsibly.