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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

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

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks 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 (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement