Madhivanan's T-SQL blog
0 FOLLOWERS
Madhivanan is a SQL Server MVP, blogger and SME.
Madhivanan's T-SQL blog
3y ago
Hello my blog readers
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 ..read more
Madhivanan's T-SQL blog
3y ago
Sometimes you may need to find out the fill rate of each column to know the accuracy of given data. One of the things to know is how many columns do not have any values at 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 ..read more
Madhivanan's T-SQL blog
3y ago
There was a question on counting number of words in a sentence by my friend
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 ..read more
Madhivanan's T-SQL blog
3y ago
When you use sql_variant datatype in sql server 2000, and add data to a table
using union all, it will work though datatype of data differs from each other
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('te ..read more
Madhivanan's T-SQL blog
3y ago
In SQL Server, only one out of the following four SELECT queries will throw an error. What is it?
SELECT 5number
SELECT 5.number
SELECT 5[number]
SELECT 5..number ..read more
Madhivanan's T-SQL blog
3y ago
Without seeing the table structure,If you are interested in knowing which columns uniquely identify a row in a table, you can use this system procedure
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 ..read more
Madhivanan's T-SQL blog
3y ago
One of my friends asked me if there is an way to script out the definitions of stored Procedures and Functions using Query. There can be several methods. This is one of the methods that uses BCP utility
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 en ..read more
Madhivanan's T-SQL blog
3y ago
In SQL Server server, consider the following queries
query 1 : select 12a4;
query 2 : select 12e4;
query 3 : select 12f4;
query 4 : select 12g4;
Only one query will produce different result than others. Find the odd one out ..read more
Madhivanan's T-SQL blog
3y ago
I wonder why it is possible to create a table with only one column that too with identity property.
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 ..read more
Madhivanan's T-SQL blog
3y ago
Curious to know the result of the following query? All you need to do is, in SQL Server, goto SSMS, set the result mode to Text (Press CTRL+T) and then execute the following code
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(0x486170707920486F6C69646179 ..read more