Create this simple table
create table #test(id int)
Now query it using the following code
select id from ...#test
It returns an empty result. The question is Why are leading dots on the table are ignored?
Exploring SQL server 2016 T-SQL functions- COMPRESS
One of the T-SQL functions introduced in SQL Server 2016 is COMPRESS function. It compresses the string values using GZIP algorithm. The advantage of GZIP is that it can be used in SQL Server as well as in the application layers. So you can easily have compressed data in SQL Server and DECOMPRESS it in the application layer and vice versa.
This function accepts any string value and returns binary data of type VARBINARY(MAX)
A simple example is
select
'a' as original_data,
compress('a') as compressed_Data
The result is
original_data compressed_Data ------------- --------------------------------------------------- a 0x1F8B08000000000004004B040043BEB7E801000000
What is the advantages of using this function? It reduces the space required for storing big string values.
Let us consider the following two tables. #test with original data and #test_compressed with compressed data
-- Original Data stored in varchar datatype
create table #test(data varchar(max))
insert into #test
select
top 10000 replicate(cast('a' as varchar(max)),100000)
from
sys.columns as c1 cross join sys.columns as c2
-- Compressed Data stored as varbinary
create table #test_compress(data varbinary(max))
insert into #test_compress
select
top 10000 compress(replicate(cast('a' as varchar(max)),100000))
from
sys.columns as c1 cross join sys.columns as c2
Now analyze the space occupied
exec tempdb..sp_spaceused '#test' exec tempdb..sp_spaceused '#test_compress'
The result is
Look at the values for the column named Data. The original value takes 1 GB whereas compressed value takes only 1.4 MB
Note : If you want to have historical data with lot of string columns and rarely queried, it is better to compress the values to save the space.
Other posts on new features of SQL Server 2016
Exploring SQL Server 2016 T-SQL functions – STRING_SPLIT
SQL Server 2016 – Drop If Exists
SQL Server 2016 – Drop If Exists
SQL Server 2016 has introduced many new features. One of them is Drop If Exists (DIE) , a DDL statement, which can be used to drop the objects if exists
In earlier version, you need query on the system functions or objects
Suppose you have a table named test and you want to drop it if it already exists in the database. In earlier versions, you can do
IF OBJECT_ID('test','U') is not null
DROP TABLE test;
Now from version 2016 onwards, you do not need an IF condition to find the existence of the table, use DROP IF EXISTS
DROP TABLE IF EXISTS test
It Drops the table if exists otherwise do nothing
You can also do the same thing for other objects as well like TRIGGER, PROCEDURE, etc
— To drop the Trigger named tr_test
DROP TRIGGER IF EXISTS tr_test
— To drop the Trigger named proc_test
DROP PROCEDURE IF EXISTS proc_test
This is really a handy feature which is also available in MySQL
Exploring SQL Server 2016 T-SQL functions – STRING_SPLIT
SQL Server 2016 has introduced some new T-SQL functions which are very handy to use. I will explore some of them starting with this post.
There are number of questions asked in the forums about splitting the values stored in the form of csv in the column. It has been always not a simple way to do this in SQL Server versions prior to 2016. But in 2016, with the introduction of STRING_SPLIT function this becomes easy. It accepts first parameter as the actual string to be split and second parameter as the delimiter.
Suppose you have a string “This,is,test” which you want to split and have them in separate rows. With STRING_SPLIT you can do it easily as shown below
declare @s varchar(100) set @s='This,is,test' select * from STRING_SPLIT(@s,',')
The result is
value ------------- This is test
What if the values are in a column in table and you want split them?
You can use CROSS APPLY operator with this function
declare @products table
(
product_id int,
product_names varchar(1000)
)
insert into @products(product_id,product_names)
select 1,'Samsung,LG,Sony' union all
select 2,'Apple,Nokia,Micromax,Xiami'
select
product_id,value as product_name
from
@products as t1
cross apply
(
select *
from STRING_SPLIT (t1.product_names,',')
) as t2
The result is
product_id product_name ----------- ----------------- 1 Samsung 1 LG 1 Sony 2 Apple 2 Nokia 2 Micromax 2 Xiami
Madhivanan's SQL blog 