FileStream and FileTable in SQL Server 2012

Data explosion brought a need to store both structured and un-structured data together in the database so that the benefits of the database system (like transactional support, backup and restore support, integrated security support, Full-Text Search support, etc.) can be leveraged. SQL Server 2008 introduced the FileStream data type to store unstructured data, such as documents, presentations, videos, audios and images, on the file system with a pointer to the data in the database.

Storage of un-structured data in FileStream improves the performance by leveraging the NTFS APIs streaming along with additional benefits of the database system.

SQL Server 2012 enhanced this capability even further by introducing FileTable, which lets an application integrate its storage and data management components to allow non-transactional access, and provide integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata. I am going to talk about these two new features in detail, for more information click here.

Getting Started with the New Column Store Index of SQL Server 2012

Column Store Index is a new feature in SQL Server 2012 that improves performance of data warehouse queries several folds. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request along with the newly introduced batch mode processing.

In my last article I talked in detail about the new Column Store Index, how it differs from regular indexes, and why and how it improves the performance of the same query by several folds if column store index is used, where it can be used and what its limitations are.

In this article I am going to take this discussion to another level and show how you can create column store index, how you can use index query hint to include or exclude a column store index, how the performance differs when using column store index vs. row store index and more, click here for more information.

Understanding new Column Store Index of SQL Server 2012

Column Store Index is a new feature in SQL Server 2012 that improves performance of data warehouse queries several folds. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request. This article discusses the new Column Store Index in detail, how it differs from regular indexes, why and how it improves the performance of the same query by several folds if column store index is used, where it can be used and what its limitations are, for more information click here.

Understanding BI Semantic Model (BISM) of SQL Server 2012

SQL Server 2012 introduced an unified BI Semantic Model (BISM) which is based on some of the existing as well as some new technologies. This model is intended to serve as one model for all end user experiences for reporting, analytics, scorecards, dashboards, etc. In this tip, I will talk in detail about the new BISM, how it differs from earlier the earlier Unified Dimensional Model (UDM) and how BISM lays down a foundation for future; for more information click here.