Clustered Column Store Index: Concurrency with INSERT Operations

Clustered Column Store: Insert Operations

As described in the blog , the clustered column store index has been optimized for typical DW scenario supporting nightly or trickle data load with fast query performance. Multiple inserts can load the data in parallel concurrently while DW queries are being run in read uncommitted transaction isolation level.

This blog describes locking behavior when data is inserted concurrently. For the scenarios below, we will use the following table


       [accountkey] [int] IDENTITY(1,1) NOT NULL,

       [accountdescription] [nvarchar](50) NULL



            — create a CCI



Insert Operations

Let us insert 1 row and see the locks taken. Note, we did not commit the transaction

begin tran

       insert into T_ACCOUNT (accountdescription ) values (‘row-1′);

 Here are the locks. Note, the new row is inserted into delta rowgroup which is organized as a btree in traditional row storage format. There is a new resource ROWGROUP in the context of CCI. The current transaction has taken IX lock on the ROWGROUP


Now, let us insert another row in another session as follows and look at the lock

begin tran

       insert into T_ACCOUNT (accountdescription ) values (‘row-2′);

Note, that the second transaction in session-55, also inserted the row into the same rowgroup. In other words, concurrent inserts can load the data into same rowgroup without blocking each other.


 In summary, the insert into CCI does not block other concurrent inserts and concurrent inserts load data into the same delta rowgorup. In the next blog, we will look into BulkLoad Operations


Sunil Agarwal


SharePoint, Office365 & Yammer Nuggets of weeks 29 and 30

last nuggets before entering my 2 weeks of vacation;

  • Alternative approach of using SharePoint forms
    Slidedeck from Karsten Pohnke, ShareConf 2014 
  • New Azure templates to build a SharePoint 2013 farm out-of-the-box
    really impressing,  try here 
  • New interactive training for hybrid Office365 & SP2013  available
    official blogpost  
  • SharePoint Search Content Sources: Script to rule them all
    Max Melcher shares his script 
  • What´s new in Apps for Office?
    Office Garage has published a new video 
  • Data Collection Process for SharePoint
    This whitepaper describes data collecting processes for troubleshooting
  • SharePoint 2013 and SharePoint Online solution pack for branding and site provisioning
    Download it here
  • SharePoint Hybrid worksheets
    Download them here 

SQL – Some useful commands and features

SET NOCOUNT -  Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
SCOPE_IDENTITY( ) – Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function,
or batch.
@@IDENTITY -  Returns the last inserted identity column value inserted across any scope in the current session.
CHAR(13) - For inserting line break (new line character) in the result set.
ISNULL( ) – Replaces NULL with the specified replacement value.
CAST( ) - Returns expression translated to data_type.
CONVERT( ) - Returns expression translated to data_type. Its also supports formatting of the data returned.
CASE Statement – It evaluates a list of conditions and returns one of multiple possible result expressions. It cannot be used outside SELECT statement. 


Temporary Tables – These
are created to store data for temporary use. Once created, these tables
can be viewed under “System Databases -> Tempdb -> Tables ->
Temporary Tables” section. There are two kinds of temporary tables.

Local Temporary Tables: Once
created, these are available only for the current sql connection
established by a user. These are deleted when the user closes the sql

Global Temporary Tables: Once
created, these are available under any sql connection established by any
user. These are deleted when all the sql connections are closed.


Convert values in a table into a string – Substring((Select ‘,’ + Table.ColName for XML Path(”)), 2, 500)

Single or double quotes in text - Between every two single quotes all double quotes are converted into one. Example:-  ‘He””llo World’ is converted into ‘He”llo World’

Azure ?????????: ?? PDF ????????????

原文發表於 PDF Generation and loading file based certificates in Azure Websites

在 Azure 網站服務上,我們致力確保它與開發環境中、一般的 IIS 平台有一致的環境,這樣一來,開發人員便能夠讓應用程式在既有機房與雲端之間無縫切換執行。不過身為一個多租戶服務(multi-tenant service),Azure 網站服務設計了一些確保安全的遊戲規則,這可能會讓一些既有的應用程式發生一些相容性問題。所以今天我們宣佈 Azure 網站服務將新增兩個功能讓使用基本或標準方案的用戶先預覽,以確保應用程式的相容性。

使用 SQL Server Reporting Service 生成 PDF 文件 (Report Viewer)

在 Azure 網站服務上您可以用許多工具或開發框架來生成 PDF 文件,其中之一是過去受到開發人員歡迎的 SQL Server Reporting Services,透過這次的更新,我們讓使用基本標準方案的用戶能夠直接使用它來產生 PDF 文件。

支援載入 PFX 憑證檔案

應用程式在使用 HTTP 介接其它服務時,若是需要使用用戶端憑證 (client certificates) 來做身份驗證的情境,就會需要能夠從檔案系統中載入憑證檔案(使用 X509Certificate2 類別指向一個 PFX 檔案),然後用它來進行 HTTP 請求。為了要能夠從檔案系統中讀取 PFX 檔案,Windows 需要一個用戶配置(user profile)來完成讀取憑證。在一般的 IIS 伺服器上,因為支援 “per Application pool” 做設定,所以很容易就可以做到這個功能。經由這次更新後,我們讓 Azure 網站服務支援了一樣的設定(目前僅開放給基本標準方案的用戶),只要在管理後台的應用程式設定(app settings)中加入一個 WEBSITE_LOAD_USER_PROFILE 常數,並且將它的值設為 1,如此一來 Azure 網站服務便會替應用程式載入用戶配置,這樣一來也就能夠從檔案系統中讀取 PFX 檔案了。


這篇文章原始發佈於「Microsoft Azure 中文部��格」

How to build a multi-level container structure using Containerization process

Introduction We have received a number of questions regarding multi-level container structures in an outbound process. This blog post will show you how to achieve that by using the new Containerization feature in Microsoft Dynamics AX 2012 R3, and work through an example of building a two-level container structure. A multi-level container structure refers to nested containers, that is, containers inside other containers. In some situations, for example, you may want to pack your goods into cartons…(read more)

Introduction to Containerization – Automated packing process in Microsoft Dynamics AX 2012 R3

Overview In addition to the manual packing functionality that was introduced in my previous blog , the new Warehouse management system also provides a feature, Containerization, which supports an automated or guided packing process. In this process, containerization assists the user by: Automatically calculating required containers for the outbound shipment based on product and container physical dimensions. Optimizing packing structure according to a set of rules configured by the user. Generating…(read more)

The Sharks Cove is now available for Pre-order!

Back in April at the //Build conference, our group sent a couple of guys down to San Francisco to give a preview of some of the cool new stuff we’ve been working on over the past year.  At the end of the presentation, Viraf shared that the coolest of that stuff, the Sharks Cove, was targeted for release in Summer of 2014.  Given that it’s nearing the end of July, we’re still in the year 2014, and the title of this blog post provides a pretty strong hint (spoiler alert?), it appears to be pretty easy to guess what I’ll say next:


The Sharks Cove development board is now available for pre-order!

This marks a major milestone in our work, and we’re all pretty excited about it, to say the least.  This board is the product of a lot of collaborative effort amongst various groups from Microsoft, Intel, and the product manufacturer, CircuitCo.  This “Windows compatible hardware development board” is designed to facilitate development of software and drivers for mobile devices that run Windows, such as phones, tablets and similar System on a Chip (SoC) platforms. 

At $299, this is a board that we believe will find a home with Independent Hardware Vendors (IHVs) and hardware enthusiasts alike.  That price not only covers the cost of the hardware, but also includes a Windows 8.1 image and the utilities necessary to apply it to the Sharks Cove.  When you additionally consider that the Windows Driver Kit 8.1 can pair with Visual Studio Express and are both free with a valid MSDN account, the initial outlay for Windows driver developers is a lot less cost prohibitive than it once was.

We’ve also been busy posting content related to the Sharks Cove, settling into our new MSDN development-board Forum, and the launch of this blog.  Our goal is to ensure information is easily found and we have multiple ways to interact with
our community: is the site we have set up a site dedicated to the Sharks Cove board, where you’ll find specs and links to other content and our MSDN forums, as well as a link for the pre-order.

Pre-order the Sharks Cove direct link (via Mouser Electronics).

The Hardware Development Boards for Windows forum on MSDN is the new forum we have set up for discussion and support.

The Windows compatible hardware development boards MSDN page will consistently be updated with new information and act as a launch point to the various pages related to Windows driver development using these boards. 

As mentioned above, Peter Wieland and Viraf Gandi introduced and demoed the Sharks Cove at the //BUILD conference in San Francisco this past April – definitely worth the viewing!

Over the next few weeks and months, we’re planning to a number of articles related to the Sharks Cove and using it for driver development.  Among our planned posts, we will have series of posts from our summer college intern describing his introduction to driver development and using the Sharks Cove and the User Mode Driver Framework (UMDF) to develop a sensor driver.  We’ll also give a behind-the-scenes peek at how all of this came together, as well as a variety of other posts that feature the Sharks Cove.

We’re very excited and proud of the work done to make the Sharks Cove a reality.  We are looking forward to seeing the amazing things that can be done with these boards!

Issues with Galleries – Mitigated

Final Update: Saturday, 26 July 2014 11:04 AM UTC


We have restored connectivity to the backend environment and the galleries sites are working fine now. The users should not be experiencing theissue anymore.

We sincerely thank you for your patience and apologize for the inconvenience.

-MSDN Service Delivery Team



The Galleries site is currently unavailable due to a backend failure on the SQL cluster. We are currently working on restoring connectivity to the backend environment.

We apologize for the inconvenience and appreciate your patience.

-MSDN Service Delivery Team