Maintaining Uniqueness with Clustered ColumnStore Index | SQL Server 2014


Column Store indexes were introduced in SQL Server 2012 with a flavor of Non-Clustered index i.e. “Non-Clustered ColumnStore” index. However there is a big limitation that the underlying table becomes read-only as soon as you create one.

In SQL Server 2014 this behavior is unchanged and addition to this you can also create ColumnStore index as a Clustered index. And the good thing is that the table having “Clustered ColumnStore” index can also be updated. However there is one more big limitation here that there is no Clustered Key with this type if index, thus risking the Uniqueness in the table.

–> Here we will see this limitation and a workaround which can be used in some scenarios:

USE tempdb
GO

-- Create a simple table with 3 columns having 1st column to contain Unique values:
CREATE TABLE dbo.TableWithCCI
(
	PKCol int NOT NULL,
	Foo int,
	Bar int
)
GO

-- Now create a "Clustered ColumnStore" index on this table:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TableWithCCI ON dbo.TableWithCCI
GO

–> Notice: While creating this index there is no provision to provided the “Clustering Key”, as this index includes all of the columns in the table, and stores the entire table by compressing the data and store by column.

On checking the metadata (by ALT+F1) of the table, you will see NULL under the index_keys column:
SQLServer2014_Unique_CCI

Now let’s check this feature of absence of Uniquenes. We will enter 2 records with same value:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33
GO

SELECT * FROM dbo.TableWithCCI
GO

You will see 2 records with same duplicate value.

Now, let’s create another Unique index to enforce this constraint:

CREATE UNIQUE INDEX UX_TableWithCCI ON dbo.TableWithCCI(PKCol)
GO

We get an error that you cannot create more indexes if you have a Clustered ColumnStore index:

Msg 35303, Level 16, State 1, Line 25
CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.

–> Workaround: As a workaround we can create an Indexed/Materialized View on top this table, with Clustering Key as the PK (1st column of the table/view):

CREATE VIEW dbo.vwTableWithCCI 
	WITH SCHEMABINDING
AS 
	SELECT PKCol, Foo, Bar
	FROM dbo.TableWithCCI
GO

-- Delete duplicate records entered previously:
DELETE FROM dbo.TableWithCCI
GO

-- Create a Unique Clustered Index on top of the View to Materialize it:
CREATE UNIQUE CLUSTERED INDEX IDX_vwTableWithCCI ON dbo.vwTableWithCCI(PKCol)
GO

Now let’s try to enter duplicate records again and see if these can be entered or not:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33
GO

Now, as expected we get an error after we inserted 1st records and tried to insert the 2nd duplicate record:

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 48
Cannot insert duplicate key row in object ‘dbo.vwTableWithCCI’ with unique index ‘IDX_vwTableWithCCI’. The duplicate key value is (1).
The statement has been terminated.

Not sure why Microsoft has put this limitation of not maintaining the Uniqueness with these indexes. While using this workaround you need to consider this approach if possible. Like in some scenarios where the table is very big and there are frequent updates (INSERT/UPDATE/DELETES) this approach of maintaining another Indexed-View would be expensive. So this approach should be evaluated before implementing.

-- Final Cleanup:
DROP VIEW dbo.vwTableWithCCI
GO
DROP TABLE dbo.TableWithCCI
GO

I look forward in new versions of SQL Server to address this limitation.
You can also refer to MSDN BOL [here] for checking all limitations with ColumnStore Indexes.

Microsoft AZURE | The Cloud for Modern Business


Azure_OnPrem2Cloud

Microsoft Azure is an Open and Flexible cloud platform that enables you to quickly Build, Deploy and Manage applications across a global network of Microsoft-managed Data-Centers. You can build applications using any language, tool or framework. And you can Integrate your public cloud applications with your existing IT environment.

Azure

–> Microsoft AZURE provides you:

1. Always up. Always on: Delivers a 99.95% monthly SLA and enables you to build and run highly available applications without focusing on the infrastructure.

2. Open: Enables you to use any language, framework, or tool to build applications. Features and services are exposed using open REST protocols.

3. Unlimited servers. Unlimited storage: Enables you to easily scale your applications to any size. It is a fully automated self-service platform that allows you to provision resources within minutes. Elastically grow or shrink your resource usage based on your needs.

4. Powerful Capabilities: Delivers a flexible cloud platform that can satisfy any application need. It enables you to reliably host and scale out your application code within compute roles.


Azure_Features

–> Solutions Microsoft AZURE provides:

1. Virtual Machines: On-demand infrastructure that scales and adapts to your changing business needs.

2. Web: Secure and flexible development, deployment, and scaling options for any sized Web application.

3. Mobile: Fast and easy to build mobile apps that scale. Within minutes, you can store data in the cloud, authenticate users, and push notifications to millions of devices.

4. Dev & Test: Develop and test applications faster, at reduced cost, and with the flexibility to deploy in the cloud or on-premises.

5. Big Data: Reveal new insights and drive better decision making with Azure HDInsight, a Big Data solution powered by Apache Hadoop. Surface those insights from all types of data to business users through Microsoft Excel.

6. Media: Azure Media Services allows you to build scalable, cost effective, end-to-end media distribution solutions that can stream media to Adobe Flash, Android, iOS, Windows, and other devices and platforms.

7. Storage, Backup & Recovery: Scalable, durable cloud storage, backup, and recovery solutions for any data. It works with the infrastructure you already have to cost-effectively enhance your business continuity strategy and provide storage required by your cloud applications.

8. Identity & Access Management: Azure Active Directory delivers an enterprise ready cloud identity service enabling a single sign-on experience across cloud and on-premises applications. It allows multi-factor authentication for added security and compliance.


–> Current AZURE footprint:
Azure_Footprint

–> For more information please visit Microsoft AZURE:
- Official Site: http://azure.microsoft.com/en-us/
- Official Blog: http://azure.microsoft.com/blog/

Categories: Azure Tags: ,

Book Review – Getting Started with SQL Server 2014 Administration


I started working on SQL Server with version 2000 (back in yr2005), then upgraded to 2005 (in yr2008), skipped 2008 version, jumped to 2008 R2 (in yr2011), then 2012 (in yr2012) and now finally 2014 very recently.

Now “SQL Server 2014″ looks very competitive if you compare it with other vendors in terms of DB Engine, BI Suite, Administration, Cloud Computing, and the latest In-Memory processing, all bundled in a single suit.

–> SQL Server 2014 is packed with new and robust features like:
1. In-Memory OLTP
2. Updatable ColumnStore Indexes for Data Warehouse
3. Enhanced AlwaysOn, Azure VMs for Availability replicas
4. Managed Backup to Azure
5. SQL Server Data Files in Azure
6. Encrypted Backups
7. Delayed durability
8. Buffer Pool Extension (with SSD)
9. Incremental Stats

“Getting Started with SQL Server 2014 Administration” book is authorized by Gethyn Ellis {B|L|T} and covers most of these features in Detail and in simple steps. I’ve also talked about some of these features on my previous blog post [link], and will be writing in future also.

Getting started with SQL server 2014 Adm_Front cover_2413EN

–> The book contains following chapters:

Chapter 1: SQL Server 2014 and Cloud
Chapter 2: Backup and Restore Improvements
Chapter 3: In-Memory Optimized Tables
Chapter 4: Delayed Durability
Chapter 5: AlwaysOn Availability Groups
Chapter 6: Performance Improvements

The book starts (Chapter-1) by giving an introduction to the Cloud and how Microsoft Azure SQL Database enables your SQL Server database on Cloud in easy & graphical steps, which includes:
1.1. Creating Azure SQL DB
1.2. Integrating Azure Stirage
1.3. Creating Azure VMs

On Chapter-2 its talks about Backup & Restore improvements in 2014, which includes:
2.1. Database backups/restore to a URL and Azure Storage
2.2. SQL Server Managed Backup to Microsoft Azure
2.3. Encrypted Backups

Chapter-3 tells you about new In-Memory functionality by creating:
3.1. In-Memory Tables & Indexes
3.2. Native compiled Stored Procedures

Chapter-4 discuss about Delayed Durability and how it can help improve performance by using in-memory transaction log feature, which delays writing transaction log entries to disk.

Chapter-5 talks about enhancements to AlwaysOn Availability Groups and following:
5.1. Using Microsoft Azure Virtual Machines as replicas
5.2. Building AlwaysOn Availability Groups
5.3. Creating/Troubleshooting Availability Group

Last Chapter-6 talks about lot of improvements in Performance, which includes:
6.1. Partition switching and indexing, now it is possible for individual partitions of partitioned tables to be rebuilt.
6.2. Updatable and new Clustered ColumnStore Indexes.
6.3. Buffer pool extensions, will allow you to make use of SSD (Solid-State Drives) as extra RAM on your DB server, thus by providing an extension to the Database Engine buffer pool, which can significantly improve the I/O throughput.
6.4. New Cardinality estimator and better query plans.
6.5. Update Statistics incrementally instead of a full Scan.


PROS: The book covers most of the new features in SQL Server 2014, so it is good for DBAs and Developers who already have prior experience in SQL Server 2012 Admin and Dev. Overall a good book which gives good insights into SQL Server 2014, Azure and new features.

CONS: Not on negative side, but for newbies and junior DBAs I would suggest to get hold of some basic DBA book and stuff first then graduate to this book.

Download/Buy book Here [Packt Publishing].

Identify potential free space in Database files before shrinking – TSQL Query

April 27, 2014 2 comments

Today someone in office asked me:

"How can I know how much free space might be left in a Database to Shrink 
before actually Shrinking it?"

–> I told it is simple, go to SSMS, select Database -> Right click -> select Task -> select Shrink -> select either Database/File.
DatabaseFreeSpace01

–> It will show you Available Free Space, of the whole Database or each mdf/ldf/ndf file(s).
DatabaseFreeSpace02

–> He said he already knows it, his real ask was:

"what if you've hundreds of Databases and want to know the stats for each 
of them? Is there any SQL Query which can give you this stats, so that you can 
run it against each or all Database?"

I opened SQL Profiler, ran it against the instance and again performed all manual steps defined above to check the Free space. After the Shrink File pop-up window appeared I went back to the Profiler and stopped it, which threw me lot of typical SQL Queries generated by SQL Server DB engine. I searched and found the desired query that I was looking for. I slightly tweaked the query according to my needs and here it is:

USE [AdventureWorks2012]
GO

select 
	db_name()			AS [DatabaseName],
	s.name				AS [DB_File_Name],
	s.physical_name		AS [FileName],
	s.size * CONVERT(float,8) AS [TotalSize],
	CAST(CASE s.type WHEN 2 
			THEN s.size * CONVERT(float,8) 
			ELSE dfs.allocated_extent_page_count*convert(float,8) 
		END AS float)	AS [UsedSpace],
	CASE s.type WHEN 2 
		THEN 0 
		ELSE s.size * CONVERT(float,8) - dfs.allocated_extent_page_count*convert(float,8) 
	END					AS [AvailableFreeSpace] 
from sys.database_files AS s 
left outer join sys.dm_db_file_space_usage as dfs 
	ON dfs.database_id = db_id() 
	AND dfs.file_id = s.file_id
where (s.drop_lsn IS NULL)

–> Output:
DatabaseFreeSpace03

To Shrink Database Files check my earlier blog post: http://sqlwithmanoj.wordpress.com/2011/07/02/shrink-database-shrink-file/

Top SQL Blogs – Windows 8 App | from MSDN, MVPs and Top Bloggers

March 14, 2014 Leave a comment

Dear Readers,

After my first App [SQL with Manoj] got published on Windows 8 Store, I thought to create an another App on a broader level, which will cover latest SQL Server (TSQL) topics from MSDN blogs, MVPs and other famous Bloggers, like Paul S. Randal, Kimberly Trip, Sam Lester, Brent Ozar, etc.

And today I’m very happy to announce the release of my 2nd Windows 8 App for and its availability on Windows 8 store.

Top SQL Blogs

With this App you can view and read SQL Server related topics from various sources, like MSDN blogs, MVPs and other famous Bloggers at one place.

Please download this App on your Windows 8 PC or Tablet and let me know your feedback.

Thanks!!!

Follow

Get every new post delivered to your Inbox.

Join 388 other followers