SQL versus NoSQL databases

I recently saw an Information Management To-Be Architecture Overview diagram at a client. It has clearly depicted the current landscape for the Relational Database side. Then came the surprise, a single belittled square box called “NoSQL”.

What does that mean?

Most people, including data professionals, don’t know that NoSQL is a collective name for several database technologies. Each solve very different data problems.

NoSQL databases fall into 4 main categories (some examples added):

James Serra wrote a very informative article explaining it. Read it here.

In a followup post I will provide some information to assist you in understanding the strengths and weaknesses of SQL and NoSQL databases. This will help understand the impact of your database technology choices.

Tip: Be very clear on what problem you want to solve and then choose the correct database technology to assist.

Posted in NoSQL | Tagged | Leave a comment

Rebuild a Specific Partition

The following code snippet will enable you to rebuild a specific partition on an index in SQL Server. This is very useful when the table in question is very large and you only want to rebuild the specific partition that you deleted data, inserted or partition switched data into. To rebuild the entire index on very large tables will just take to much time and the SQL Server engine might run out of memory.

    Declare @SqlCommand varchar(2000) = ”
    Declare @SchemaName varchar(3) = ‘dbo’
    Declare @TableName varchar(10) = ‘FactSales’
    Declare @IndexName varchar(50) = ‘CCI_FactSales’
    Declare @PartitionNumber int = (Select Top 1 $partition.pfDaily(‘2015-01-01’) From [dbo].[FactTable])

    Set @SqlCommand = N’ALTER INDEX ‘ + Quotename(@IndexName) + N’ ON ‘ + Quotename(@SchemaName) + N’.’ + Quotename(@TableName) + N’ REBUILD PARTITION = ‘ + Cast(@PartitionNumber As NVarchar(10)) + ‘;’;’


You can use this code after the Incremental load on a table to rebuild only the specific partition to ensure the data in the partition is not fragmented.

Feel free to comment or ask a question on this topic

Posted in SQL Server 2008, SQL Server 2012, SQL Server 2014 | Tagged , , | 1 Comment

Clustered Columnstore Index in SQL Server 2014

The clustered columnstore index which shipped with SQL Server 2014 is busy revolutionizing the Data Warehousing landscape in SQL Server. When creating a Fact Table in a data warehouse you will almost without question put a Clustered Columnstore (CCI) on the table instead of the traditional Clustered Index.

You get numerous benefits when using a CCI index. Below are a few of these benefits.

  • 90 % compression on the data
  • Lightning fast aggregate queries
  • The index is write-enabled. You can thus run insert, update and delete queries against the table unlike the Non-Clustered Columnstore index in SQL Server 2012

The clustered columnstore index changes the internals of the table structure completely. Instead of the traditional row based heap type storage the clustered columnstore index stores the data in a columnar fashion.

If you have not used Clustered Columnstore Indexes yet then this is definitely worth trying out on your Data Warehouse. Stay posted for more blogs on this topic.

Additional Reading

Posted in SQL Server 2014 | Tagged , , , | Leave a comment

Partition Switching for Big Data Loads – Partition Switch Out

This is the last in a four part series of blog post explaining Partition Switching using SQL Server 2014.

Before switching data into a specific partition we must first ensure the relevant partition is empty, otherwise the Partition Switch In operation will fail. The Partition Switch Out command will work perfectly in removing the data from the destination table into a separate staging table.

A staging table with exactly the same structure as the destination Fact table must exist for this operation. The data will be moved from the Fact table to the Staging table.

As in the previous posts we used monthly filegroups where the Staging Fact table should be on the correct filegroup before the Switch Out command is executed. The following code snippets will perform the Partition Switch Out. You can amend the code to fit into your design and structure as you please.

Firstly remove the columnstore index

  • If Exists(Select * From Sys.Indexes Where OBJECT_ID = Object_Id (‘dbo.Staging_Fact_Out’) And NAME =’CCI_Staging_Fact_Out’)Drop Index [CCI_Staging_Fact_Out] On [dbo].[Staging_Fact_Out] WITH ( ONLINE = OFF )

Then create the clustered columnstore index on the correct filegroup

  • Declare @FileGroupMonth varchar(40) = ‘Month_’ + Convert(Varchar,DatePart(Month,@LoadDate))
  • Declare @Sql varchar(1000) = ‘Create Clustered Columnstore Index [CCI_Staging_Fact_Out] On [dbo].[Staging_Fact_Out] With (Drop_Existing = Off) On ‘ + @FileGroupMonth
  • If Not Exists(Select * From Sys.Indexes Where OBJECT_ID = Object_Id(‘dbo.Staging_Fact_Out’) And Name =’CCI_Staging_Fact_Out’)
    Exec (@Sql)

Retrieve the specific partition number

  • Declare @PartitionNumber Int = (Select Top 1 $partition.pfFunction(@Date) From [dbo].[Fact_Table])

Finally do the partition switch out 🙂

  • Alter Table Fact_Table
    Switch Partition @PartitionNumber To Staging_Fact_Out

Now you can perform the Partition Switch In explained in the previous article because the relevant partition will be empty.

Posted in SQL Server 2008, SQL Server 2012, SQL Server 2014 | Tagged , , | Leave a comment

SQL Server 2016 CTP 2

The first Public SQL Server 2016 Preview which is called SQL Server 2016 CTP2  is now available for download. Follow the link to the download site and get your copy.

The following TechNet webpage will give you a brief overview of the new the features and enhancements of the latest edition of SQL Server.

Enjoy !!


Posted in SQL Server 2014 | Tagged | 1 Comment

SQL Server SSMS Color Coded Status Bar

Have you ever experienced the nauseating feeling when running an update, delete or truncate statement thinking you are working on the development server while the connection is actually pointing to production database server in SQL Server Management Studio (SSMS). You then have to spent hours trying to recover the data that was incorrectly modified.

This can be prevented by using the Custom Color option to color code the status bar in SSMS. When the Status Bar at the bottom of SSMS is for instance red you will know you are connecting to the production server and thus will be more cautious while running scripts.

Just follow the following easy steps in SSMS to achieve this.

  •  Right click on the Database Server instance and choose connect.SSMSColor1
  • Click on the Options button in the bottom right corner.
  • Check the Use custom color checkbox and choose the appropriate color for the database instance                                                                               SSMSColor2
  • To see the change in the Color close down SSMS, open SSMS again and create a new query and you will see the status bar will the color that you selected.SSMSColor4

Now you can color code the QA, Development and Production servers to different colors and be more aware of which one you are connected to. This feature is available from SQL Server 2012 onwards.

Posted in SQL Server 2012, SQL Server 2014 | Tagged , | Leave a comment

Best Practices for Building a Large Scale Data Warehouse

You walk into the office today and your client, or your boss, asks you to build a warehouse. All seems fine and dandy, right. You are the developer and this should be easy. “O, and by the way, our 5 OLTP systems will generate roughly 10TB of data to load each day” – says the client.

All of a sudden you find yourself in a whole new ball-game.

Don’t sweat we have this covered, well actually the SQL CAT team covered it. Thanks team!

Simply follow these guidelines and you will fly. (Link below)

My top tips:

  1. Perform partition switching – check details how to here.
  2. Bulk load data into heap tables
  3. Update statistics manually

Link: http://blogs.msdn.com/b/sqlcat/archive/2013/09/16/top-10-best-practices-for-building-a-large-scale-relational-data-warehous.aspx

Posted in SQL Server 2014 | Tagged , , | Leave a comment