I have created my own blog with custom url and moved all my existing post there.

Please visit https://exploresql.com/ hereafter.

Also please subscribe to my new blog if you want to get notified on the new posts

Thank you all.

Let us create the following data set

use tempdb 
GO
create table test
(
	customer_id int, 
	customer_name varchar(100), 
	dob date, 
	address1 varchar(100),
	address2 varchar(100)
)

insert into test(customer_id,customer_name, dob)
select 10001,'Madhivanan','1990-01-01' union all
select 10002,'Murugan','1991-10-19' union all
select 10003,'Kandhan','1984-04-22'

Now see the result set

select * from test;

As you see, all the values of the columns address1 and address2 are NULL.

You can execute the following dynamic SQL to get the column names for which all values are NULL

declare @sql varchar(8000), @create varchar(1000),@insert varchar(100)

select 
	@sql='',
	@create='Create table #test (col_value sql_variant, col_name varchar(100)) ', 
	@insert=' insert into #test(col_value,col_name) '


select @sql=@sql+' '+@insert+
	' select min('+column_name+') as '+column_name+ ','''+column_name+
		''' from '+table_name 
	from information_schema.COLUMNS
where 
	TABLE_NAME='test'  and table_catalog='tempdb'

set @sql=@create+' '+ @sql+ ' select * from #test where col_value is null'
exec(@sql)

After execution, the result is

It listed out two columns address1 and address1 which have all values as NULL. This code can be modified to handle empty calues or any other values easily

One simple method is use while loop as shown below

Declare @s varchar(100)
set @s='  See      how many                        words this      has  '
set @s=ltrim(rtrim(@s))

while charindex('  ',@s)>0
Begin
	set @s=replace(@s,'  ',' ')
end

select len(@s)-len(replace(@s,' ',''))+1 as word_count

The result is 6

If the sentence has multiple consecutive spaces, replace them with single space until no more multiple spaces exist. If you count number of spaces and add 1, it gives you the count of words

declare @test table(variant_column sql_variant)
insert into @test
select 'test' union all
select 345 union all
select getdate() union all
select '3245.23'
select variant_column from @test

But in sql server 2005, it will throw error for datatype mismatch.

The proper way of doing this is to explicitely cast any one of the values to be of sql_variant datatype

declare @test table(variant_column sql_variant)
insert into @test
select cast('test' as sql_variant) union all
select 345 union all
select getdate() union all
select '3245.23'
select variant_column from @test

This is one of the behavioural changes itroduced in sql server 2005 and is followed in all the succeeding versions
 

SELECT 5number
SELECT 5.number
SELECT 5[number]
SELECT 5..number

Consider the following table

create table #testing(id int primary key, emp_name varchar(100))

Note that the column named id is unique by default. Now execute the following system procedure sp_special_columns

EXEC tempdb..sp_special_columns '#testing'

The result is as shown below

All you have to do is replace DBname by the actual Database Name

EXEC master..xp_cmdshell 
'bcp "Select routine_definition from 
DBname.information_Schema.routines 
order by routine_name" queryout "C:\scripts.sql" -c'

After it runs successfully, the file C:\scripts.sql will have the scripts

Note that due to security issues, xp_cmdshell is disable by default. You need to use sp_configure to enable it. You can find more information in this https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option

Only one query will produce different result than others. Find the odd one out

Have you ever used such a table?
What is the practical usage of table with just only one column with identity property?

Create table testing
(
test_id int identity(1,1) not null
);

set nocount on select space(17-len(replicate(
char(135),no)))+ replicate(char(135),no*2-1)
from (select top 10 row_number() over (
order by (select 1)) as no from 
(select 0 as no union all select 0 union 
all select 0) as t1 
cross join (select 0 as no union 
all select 0 union all 
select 0) as t2) as t union all 
select space(14)+replicate(char(124),5) union 
all select space(10)+
cast(0x486170707920486F6C6964617973 
as varchar(100))