THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Accessors and Case-Sensitivity in T-SQL and CLR

So in my preparation for the 2006 PASS Community Summit presentation that I will be giving on Managed Types in SQL Server 2005, I was mulling over the statement that "Accessors are case-sensitive" and decided to test out some things.

Yes, if you have a UDT with a public field named "Test" in your CLR code (C# shown here)...

public Int32 Test;

...then you must refer to that field as Test in your T-SQL code.

DECLARE @d Date

SET @d = '2004-02-29'

SET @d.Test = 7

If you try to refer to "test", you will get an exception

Msg 6592, Level 16, State 3, Line 9

Could not find property or field 'test' for type...

But what happens if you implement both "Test" and "test"?

public Int32 Test;

public Int32 test;

Well, they are both distinct fields and therefore are both usable, regardless of the database being case-sensitive or case-insensitive.

DECLARE @d Date

SET @d = '2004-02-29'

SET @d.Test = 7

SET @d.test = 10

SELECT @d.Test AS Test, @d.test AS test

Returns...

Test        test

----------- -----------

7           10

(1 row(s) affected)

Now I am not suggesting that you create accessors that vary only by case, just that it's possible...

Published Monday, September 11, 2006 5:37 AM by Peter W. DeBetta

Attachment(s): test.sql

Comments

 

Phillip Juan McCLore said:

Thanks! I am a LAMP; mostly PERL programmer new to Windows application programmng. This was indeed a good experience to find a direct answer quickly. Keep up the good work.  And please wish me well in my challenge to master Windows aplication programming and databases on SQL Server and Express

August 21, 2007 9:22 AM
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement