Ambiguities and the wild object.oriented brigades

Phil Factor, 2010-07-29

–Whilst trying to design a more effective way of detecting dependencies in a SQL Server database, I was mulling over the cruelty of SQL Server’s parser in allowing this to compile and run..
create schema int
create table int
.int (int int)
select int from
create schema character
create table character.character (character character)
select character.character character from character.character

Yes, it runs perfectly happily. INT isn’t even a reserved word. You can get up to all sorts of mischief. In the first example, we have ended up with a schema called int, a table called int and an integer column in that table called int.

Now, imagine that you are using SQL Search, or my splendid SQL expression for doing searches, to track down references to an entity such as a column name. This isn’t easy, because all these objects will have the same name. No worries, you say, because you can always give an entity its full path. Attaching a schema and maybe a parent ‘object’ name should guarantee uniqueness. Hmmm. Up to a point. For a start, we don’t always qualify column names, though it is a good idea. Secondly, some of us use stored procedures that access different objects sepending on the context they are executed in. Thirdly, there are some ambiguities now in TSQL that have been iinadvertently been introduced by the wild object-oriented brigades of CLR and XML.

Ambiguous references are one of the stumbling blocks to being able to reliably tease-out dependencies. */

–so let’s now create a simple function
BEGIN return
(select top 1 int from END
–now prime the pump
Insert into (int) select 1 int

—we call it
SELECT int.bogus() FROM int;


(1 row(s) affected) */

/* We’ll pop it into a stored procedure */

create procedure int.character
.bogus() FROM int;

/* Now, it has correctly worked out that you actually wanted the inline function . However, what would have happened if your ‘int’ column, instead of being an INT, was a UDT column named int with a method named bogus()? What, on the other hand, the int.bogus() was actually an xquery reference to a column of type xml*/
/* Yessir, without getting too deep, we would than have an ambiguous reference. If you start putting that into routines, you’ll end up making it very difficult to work out your dependencies since SQL Server will have to try to work out what you mean at run-time. This is an ambiguous reference. Now do you have to actually have two or more objects in existence that create an anbiguity, or is it just sufficient to create a reference that has a potential to be ambiguious if you were foolish enough to create competing objects with the same name? It looks as the latter is the case. */

SELECT referencing_schema_name+‘.’+referencing_entity_name
FROM sys.dm_sql_referencing_entities (‘int.bogus’, ‘OBJECT’);


(1 row(s) affected)

–find all referenced objects that are ambiguous.
select referenced_schema_name+‘.’+referenced_entity_name
from sys.sql_expression_dependencies
where is_ambiguous=1


(1 row(s) affected)





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads