Peter DeBetta's blog about programming in SQL Server 2008, 2005, etc. using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.
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...
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
About Peter DeBetta
Peter DeBetta is an independent consultant specializing in design, development, implementation, and deployment of Microsoft SQL Server, Microsoft SharePoint 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!
Peter is a Microsoft MVP for SQL Server, an MCP, President of the
North Texas SQL Server User Group, and a member of PASS.
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.