Here's an interesting little snip of code to catalog up as assembly and function.
using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class InterestingFunctions
{
[SqlFunction]
public static SqlString DuckTypedTo(object o)
{
return new SqlString(o.GetType().FullName);
}
};
Try it out with queries like SELECT DOB.DUCKTYPEDTO(3) and SELECT DOB.DUCKTYPEDTO('Rabbit'). Nothing unusal, right?
Heck, even this works as desired:
declare @z sql_variant
set @z = 3
select dbo.DuckTypedTo(@z)
Then try this:
declare @z sql_variant
set @z = 3.0
select dbo.DuckTypedTo(@z)
SqlDecimal? Why not SqlDouble or SqlSingle? Its a totally attribary decision by SQL Server based on the value. And that's the real problem with Duck Typing, at least as we have it SQL Server 2005. We have no way of suggesting how it should do the typing at the T-SQL level. We gets whats we gets. But wait, there's more. Add a UDT to your project and into this problem. Try passing an instance of that into the DuckTypedTo funciton. Now you get an error...
Msg 206, Level 16, State 2, Line 3
Operand type clash: [whatever] is incompatible with sql_variant
More or less the same thing happens if you try assigning an instance of your UDT to Sql_Variant, doesn't it?
What's interesting here is that sql_variant isn't like a .NET object in the sense that sql_variant isn't a base type, its more like what's been planned for the C# 3.0 Var type-aliaser. And as Ian Griffins pointed out Var isn't Object.