Author: Latheesh NK

Zoomit – A tool to boost your presentation skills

If you are community player or even you want to present something to someone, you may need to zoom in and zoom out certain area of your presentations to make the audience clear about your key areas. I always find it difficult if I share a screen with someone on explaining things and showing it to them. Power point presentation has an option to zoom-in and zoom-out, however, this cannot be applied for other software like SSMS etc. Today, let us quickly see some of the options that could help us in similar situations.

There is a default option in SSMS to zoom out, however, its applicable for only Query part as below:

If you need something for results or any other area, then there is no default way of achieving in SSMS. To support in such situation, I would like to introduce a tool called Zoomit by Microsoft. You can download the tool from the below link:

https://docs.microsoft.com/en-us/sysinternals/downloads/zoomit

You can explore this tool and share your thoughts. If you are already using any other tools, please share the details, happy to explore and play with it. Looking forward your comments.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

How to identify Prime numbers in SQL Server

While on HackerRank puzzles, I came through a question to find out prime numbers in SQL Server. Here is my try as below. You may try your own and share the code in the comment.
declare @max INT = 1000,@s varchar(max) =''

;WITH Tally_Numbers(n) AS
(
    SELECT 2
    UNION ALL
    SELECT n+1 FROM Tally_Numbers WHERE n <= @max
)
,cte1 as(
select Tally1.n 
from Tally_Numbers Tally1
where not exists (select 1 from Tally_Numbers Tally2 where Tally2.n < Tally1.n AND Tally1.n % Tally2.n = 0)
)
SELECT STUFF((
SELECT '&' + CAST(r.N AS VARCHAR(10)) AS "text()"
FROM cte1 r
ORDER BY r.N
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(8000)'),1,1,'')
OPTION (MAXRECURSION 0);

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

How to get FQDN (fully qualified domain name) from SQL Server?

A fully qualified domain name (FQDN) is referred to as an absolute domain name, is a domain name that specifies its exact location in the tree hierarchy of the Domain Name System (DNS).

You can see this information easily throughput Control Panel – > System and Security – > System – > Look out “Computer name, domain and workgroup settings”

But, I was looking for some way to identify this information without switching my windows (actually I was working on something when I was asked this information) .

Select DEFAULT_DOMAIN()
EXEC master..xp_loginconfig 'Default Domain'

DECLARE @rootkey varchar(100) = 'HKEY_LOCAL_MACHINE',
@key varchar(100) = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\',
@value_name varchar(100) = 'Domain',@Domain_Name sysname -- output parameters
--Using xp_regread
EXEC master..xp_regread @rootkey = @rootkey, @key = @key, @value_name=@value_name
--Using xp_instance_regread
EXECUTE master.sys.xp_instance_regread
    @rootkey = @rootkey, @key = @key, @value_name=@value_name

/* Incase you need to assign to a variable*/
EXEC master..xp_regread @rootkey = @rootkey, @key = @key, @value_name=@value_name,@value=@Domain_Name OUTPUT 
SELECT @Domain_Name 'Domain Name'

Set @Domain_Name = ''

EXECUTE master.sys.xp_instance_regread
    @rootkey = @rootkey, @key = @key, @value_name=@value_name,@value=@Domain_Name OUTPUT 

SELECT @Domain_Name 'Domain Name'
Since, its domain information, I am not providing the screenshot, you may try it out yourself and share your thoughts.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

How to get Disk space information in SQL Server

Problem Statement:

While troubleshooting an issue in SQL Server, I wanted to know the space information of the server. Since we did not have access to the server, we have come up with a simple script as below. I am sharing the script here hoping this helps you in similar situation.

Script:

declare @svrName varchar(255)
declare @sql varchar(400)
--User Options(By default Server name)
set @svrName = Cast(SERVERPROPERTY('MachineName') as varchar(255))
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') 
	+ ' -Class Win32_Volume -Filter ''DriveType = 3'' '+
	'| select name,capacity,freespace |foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table
CREATE TABLE #Details
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #Details
EXEC xp_cmdshell @sql

--Retrieve the Capacity values in GB from PS Script Details
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #Details
where line like '[A-Z][:]%'
order by drivename

--Drop the temporary table
drop table #Details

See Also:

Using windows PowerShell to get the server disk space

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Compress and Decompress in SQL Server 2016

Recently, to enforce a very small level of masking of data, I was thinking of different ways and finally used COMPRESS & DECOMPRESS functions introduced in SQL Server 2016.

Beware, This is a very bad example of implementing as a solution for data encryption! Since, it was just so trivial functionality and I do not really want to spend more time on encryption and do not bother on its performance, also it was not for a full time production application and just for demo purpose, it has been taken as an option.But, again, I would like to reiterate this is a wrong example! Do not apply !

If you are looking for a full fledged solution for encryption/data masking – SQL Server has many options, you can refer the options here.

Now, Lets quickly look into the two functions introduced in SQL Server 2016 – COMPRESS & DECOMPRESS.

COMPRESS function quickly compresses the input using GZIP algorithm. More details here.

DECOMPRESS function otherwise, it decompresses the compressed value using GZIP algorithm. More details here.

Since Microsoft covers a good explanation on the subject, let us not try to re-invent the wheel, instead, let us quickly see some of characteristics and its usages.

Storage and its significance

Storage is important factor, hmm, not really in modern world, but yes since if you want to save cost. This is really matters a lot if you have any plan for cloud migration or something like “you pay for what you use”. So COMPRESS and DECOMPRESS makes more sense in such situations. Let us demonstrate a simple example as below.

I am going to create a table with varchar and nvarchar columns to store values.

drop table if exists SQLZealot_Compress_Test
CREATE TABLE SQLZealot_Compress_Test
(
ID int identity(1,1) NOT NULL Primary Key,
Varchar_Value VARCHAR(MAX),
NVarchar_Value NVARCHAR(MAX)
)

Now, let us insert some sample data as below.

INSERT INTO SQLZealot_Compress_Test(Varchar_Value, NVarchar_Value)
VALUES 
 ( 'This is a Compress test by SQLZealot!', N'This is a Compress test by SQLZealot!!')
,( '1234567890', N'1234567890')
,( REPLICATE('X', 9000), REPLICATE('X' , 4500))
,( REPLICATE(CAST('X' AS VARCHAR(MAX)), 9000), REPLICATE(CAST(N'X' AS NVARCHAR(MAX)), 4500))
,( REPLICATE(CAST('SQLZealot|' AS VARCHAR(MAX)), 9000), REPLICATE(CAST(N'SQLZealot|' AS NVARCHAR(MAX)), 4500))

Now, let us insert some more big sized data as below. I used a sample table from my database, you can use any of your big table in your environment by replacing “tablename”.

Insert into SQLZealot_Compress_Test
Select (Select * From tablename FOR JSON PATH, ROOT('Tables')) varcharval,
		(Select * From tablename FOR JSON PATH, ROOT('Tables')) nvarcharval

Here comes our testing script. We are going to see the length of the values using DATALENGTH as below.

Select ID,
		Varchar_Value,
		DataLength(Varchar_Value) DL_NonCompress_Varchar,
		Datalength(Compress(Varchar_Value)) DL_Compress_Varchar,
		NVarchar_Value,
		DataLength(NVarchar_Value) DL_NonCompress_NVarchar,
		Datalength(Compress(NVarchar_Value)) DL_Compress_NVarchar
From SQLZealot_Compress_Test

Complete Demo Script

drop table if exists SQLZealot_Compress_Test
CREATE TABLE SQLZealot_Compress_Test
(
ID int identity(1,1) NOT NULL Primary Key,
Varchar_Value VARCHAR(MAX),
NVarchar_Value NVARCHAR(MAX)
)

INSERT INTO SQLZealot_Compress_Test(Varchar_Value, NVarchar_Value)
VALUES 
 ( 'This is a Compress test by SQLZealot!', N'This is a Compress test by SQLZealot!!')
,( '1234567890', N'1234567890')
,( REPLICATE('X', 9000), REPLICATE('X' , 4500))
,( REPLICATE(CAST('X' AS VARCHAR(MAX)), 9000), REPLICATE(CAST(N'X' AS NVARCHAR(MAX)), 4500))
,( REPLICATE(CAST('SQLZealot|' AS VARCHAR(MAX)), 9000), REPLICATE(CAST(N'SQLZealot|' AS NVARCHAR(MAX)), 4500))

Insert into SQLZealot_Compress_Test
Select (Select * From OC_TABLEDEFS FOR JSON PATH, ROOT('Tables')) varcharval,
		(Select * From OC_TABLEDEFS FOR JSON PATH, ROOT('Tables')) nvarcharval

Select ID,
		Varchar_Value,
		DataLength(Varchar_Value) DL_NonCompress_Varchar,
		Datalength(Compress(Varchar_Value)) DL_Compress_Varchar,
		NVarchar_Value,
		DataLength(NVarchar_Value) DL_NonCompress_NVarchar,
		Datalength(Compress(NVarchar_Value)) DL_Compress_NVarchar
From SQLZealot_Compress_Test

Screenshot

Observations

1. Do not use COMPRESS & DECOMPRESS as a replacement for encryption/data masking.

2. If table has less size of data, then COMPRESS will not have a benefit, instead there may be a small fraction of overhead of long value of varbinary datatype.

3. If table has big size data, then COMPRESS seems to be a good option.

4. If table has unicode character datatype, the benefit seems to be lesser than character datatype.

5. Need to evaluate the CPU cycles effort (if you do COMPRESS & DECOMPRESS) in SQL Server.

6. It would be a good option to consider doing COMPRESS & DECOMPRESS functions in application layer.

7. If you have audit feature, it is a good option to consider the compressed data as part of audited info instead of actual data (if audited data is not being frequently used).

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!