New data compression - Columnstore Archive

All who have used columnstore index must know that columnstore index also compress data.
Many cases after some time this data become historical and use of this data reduces over time.
In this case we can further compress data sung columnsore_archive compression option. Retrieving data takes time after this compression but its acceptable as it reduces storage and its usage frequency is low compare to current data

Lets see with example

     id   INT,
     data VARCHAR(max)


SET @i = 1

WHILE @i <= 100000
      INSERT INTO test
      VALUES      (@i,
                   'The value of i is '
                   + CONVERT(VARCHAR(10), @i))

      SET @I = @i + 1


Normal Table

Now we will create clustered columnstore index here.
It will compress data


Table with clustered columnstore index

Now we will compress more using columnstore_archive option 
This will compress data even more. 

ALTER INDEX c1 ON test REBUILD WITH (data_compression = columnstore_archive)

Table with columnstore_archive compression option


Here we can see size reduces from 8848 KB to 2704 KB and then 1232 KB.

Inline indexes

SQL 2014 has many silent features one of them is inline indexes.
Whats inline indexes?
Inline indexes are which can be created while creating table.
We can specify index in Create Table statement itself.

Here is an example

CREATE TABLE test_table
     id   INT,
     data VARCHAR(max),
       INDEX idx1 (id)

1.We can create filtered index
2.We can not create columnstore index.

Good news is that both this issues resolved in SQL Server 2014.

Optimized select into in SQL Server 2014

Lets have quick glance of this feature.
As we all know select .... into clause and how to use it.
Now in SQL 2014 its more optimized and will operate in parallel also for better performance.

Lets see with example

I have one table test_table with 200 Million records in SQL Server 2014 and 2012 both.

We will run Select .... into query and check execution plan

SQL Server 2012

INTO   new_table
FROM   xyz

SQL Server 2014

INTO   new_table
FROM   xyz

As we can see in execution plan optimizer is using parallelism operator to dump data.

SQLPS Powershell in SQL Server -4

Lets see some more cmdlets

first we need to check examples how to use Backup_Sqldatabase

Run this command
Get-Help Backup-Sqldatabase -Examples

It will give us examples
Similarly for all cmdlets we can get examples how to use it.

Now we will run a sample how take backup

Backup-Sqldatabase -Database Test

It will take backup of Database Test.

How to redirect  result to HTML page?

Run this command

 Invoke-Sqlcmd "select * from sys.tables" | ConvertTo-Html > c:\test.html

Lets see the result in browser by opening test.html in browser.

Amazon Contextual Product Ads

Popular Posts