How to count number of words in a sentence?

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
	set @s=replace(@s,'  ',' ')

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


SQL_VARIANT datatype and behaviour changes

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('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

SQL Server – How to Uniquely identify a row?

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


Scripting out Stored Procedures and Functions using Query

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 
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

Happy holidays from T-SQL

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)+
as varchar(100))