Build and rebuild clustered columnstore indexes online in SQL Server 2019

Microsoft development team works around the SQL Server to make it as a central part of the Microsoft data platform and we cannot deny the truth about SQL Server that is an industry leader in operational database management systems (ODBMS). Microsoft has been introduced SQL Server 2019 preview which builds on previous releases to grow SQL Server as a platform that gives us choices of development languages, data types, on-premises or cloud, and operating systems. 

If we are talking for optimizing database performance then the indexes are one of the most powerful tools for it. This is the biggest fact for the indexes that they can become more fragmented and less effective in case we did any insert, update, and delete operations on the data table. This is a pain for DBAs to take care of indexes and on the regularly basis, index rebuild operations is required.

With the growing sizes of databases, index rebuilds can take a very long time. Build and rebuild clustered columnstore indexes online is the feature of SQL Server 2019 preview where creating clustered columnstore indexes (CCI) was an offline process in the previous versions of SQL Server - requiring all changes stop while the CCI is created. 

With SQL Server 2019 preview and Azure SQL Database you can create or re-create CCI online. Workload will not be blocked and all changes made on the underlying data are transparently added into the target columnstore table.

Examples of new Transact-SQL statements that can be used are:

---- Create Clustered Columnstore Index CREATE CLUSTERED COLUMNSTORE INDEX cci   ON <tableName>  WITH (ONLINE = ON); ---- Altered Clustered Columnstore Index ALTER INDEX cci   ON <tableName>   REBUILD WITH (ONLINE = ON);

Combine that with the business needs for our applications to be always available and performant and this can be an issue. Big OLTP environments with busy workloads often have very short maintenance windows with some too short to execute large index rebuild operations. 

Azure Data Studio

Microsoft Development team is going to be introduced Azure Data Studio in SQL Server 2019 which was released under the preview name SQL Operations Studio in previous version. Azure Data Studio is a lightweight, modern, open source, cross-platform database tool for data professionals using the Microsoft family of on-premises and cloud data platforms on Windows, MacOS, and Linux.

Azure Data Studio offers a modern editor experience with Intellisense, code snippets, source control integration, and an integrated terminal. It is engineered with the data platform user in mind, with built in charting of query result sets and customizable dashboards.

With the help of this tool, now that SQL Server can run on all the major platforms, the Mac and Linux users were having to use third party tools to manage SQL server. Currently you can edit whole table, but not limited data that you need to run query, and edit only the results of that query. 

1. Edit and run queries in a modern development environment with lightning fast Intellisense, code snippets, and source control integration.
2. Quickly visualize data with built-in charting of your result sets.
3. Create custom dashboards for your servers and databases using customizable widgets.
4. Easily manage your broader environment with the built-in terminal.
5. Analyze data in an integrated notebook experience built on Jupyter.
6. Enhance your experience with custom theming and extensions.
7. And explore your Azure resources with a built-in subscription and resource browser.
8. Supports scenarios using SQL Server Big Data Cluster.

Those of us who don't want to run Microsoft's OS in a VM are really appreciative of the efforts they're making with things like this and VS code in making them cross-platform. Keep in mind, this is in preview, and provides an open source extension platform, so if you want that functionality, you can likely build it if it ends up lacking that in the final release.
Reference:https://docs.microsoft.com

SSRS - Filling blank cells in matrix

In Reporting Services, tables, matrices, and lists are data regions that display paginated report data in cells that are organized into rows and columns. The cells typically contain text data such as text, dates, and numbers but they can also contain gauges, charts, or report items such as images. Collectively, tables, matrices, and lists are frequently referred to as tablix data regions.

Sometimes, when we have created a matrix/tablix/list with data in it, but there are quite a few blank cells where there is no data. In this case, we don't want to show the blank values on the report and we have to replace the blanks with zeroes.

There are some conditional expressions which can be helpful to full-fill this requirements-

1. Just right click on the text cells and go to the expression
2. In expression window, check if the cell contains Nothing then set 0 value else fill the actual value as given below- 

   =IIF(Fields!MyColumn.Value IS NOTHING , 0 , Fields!MyColumn.Value) OR =IIf(IsNothing(Fields!MyColumn.Value),0,Fields!MyColumn.Value)

   If the field is empty or blank then it puts 0, otherwise it uses the actual data from the dataset.

Please watch a live demo at our YouTube Channel-