Situation Microsoft introduced a great feature in SQL Server 2016 that support JSON data. However, you can get the error Invalid object name OPENJSON when you work on databases that their compatibility level are lower than 130. Solution To fix this error, you need to update your database compatibility to 130 ALTER DATABASE <Database … Continue reading How to fix “Invalid object name OPENJSON” in SQL Server 2016
A couple of weeks, my friends asked me the best suitable approach to move data out a large table. I was interested in this question because there was no right answers. Depending on the situation, environment even database structure to determine what approaches. Actually, there are a lot of techniques you can apply how to delete data … Continue reading TSQL: How to delete large volume data quickly
Problem In my article Working with PIVOT, I introduced a simple way to implement PIVOT table in SQL Server. However, the data set is static and if we insert more data, the PIVOT query will not work. For example, there is a Sales table as below Product Date … Continue reading How to work with dynamic PIVOT in SQL Server
Introduction In this article , I introduced one of fantastic features of Microsoft COLUMNSTORE and had a comparison between a rowstore table and a columnstore table. The result was very interesting. However, if your table is in rowstore style, how we can convert a rowstore to columnstore Solution Converting a rowstore , there are two cases … Continue reading How to convert a rowstore table to columnstore table?
Introduction From SQL Server 2012, Microsoft released one of the most important features for querying a large dataset COLUMNSTORE INDEXES. The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse … Continue reading How does COLUMNSTORE INDEXES improve performance of Data Warehouse?
Problem When you work on SQL 2000 or some higher version but not Enterprise edition, you often encounter the difficulty for creating table partitioning. You often create some base tables such as Sales2007, Sales2008,... to store yearly data and then create a view to query those data. This approach is sometime called physical partitioned table … Continue reading How to create a partitioned table in SQL Server
PROBLEM When you work on SQL Server Editions that are prior to SQL Server 2016, it is very difficult for us to split a string with a specified separator such as comma,... You must build your own table-value function to split the string. SOLUTION However, we have a built-in function that named STRING_SPLIT function which is one … Continue reading How to work with STRING_SPLIT in SQL SERVER 2016