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
 

Advertisements

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

unique_column

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

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

MySQL – Functions to get current date and time

You may often need to do some data filters based on the current date and time. In MySQL you can use the system functions available to get current date and time.

Refer the following examples.

1 Get current date

SELECT CURRENT_DATE() AS today_date

or

SELECT CURDATE() AS today_date

The result is

2017-11-20

2 Get current time

SELECT CURRENT_TIME() AS today_time

or

SELECT CURTIME() AS today_time

The result is

08:36:29

3 Get Current Date and time

SELECT NOW() AS today_date_time

The result is

2017-11-20T08:36:29Z