Considerations when using the TransactSql.ScriptDOM parsers

Some of you might be aware of the above namespace, which holds an implementation of a first-class T-SQL parser. In this post I would like to explain some of the complexity you will face when dealing with the ScriptDOM yourselves, typically using Visitor pattern.

Case Study

Our objective in this case is to use the parser and implement a rule to find expressions which have a leading wildcard in them, within a WHERE Clause. Firstly, let us consider the T-SQL statement below:

SELECT * FROM Person.Contact
WHERE LastName LIKE ‘%mith’

If you parse this using ScriptDOM you can visit the LikePredicate element, and you can then typecast the SecondExpression to StringLiteral and then check if it starts with %.

public override void ExplicitVisit(LikePredicate node)
            if (node.SecondExpression is StringLiteral)
                if ((node.SecondExpression as StringLiteral).Value.StartsWith("%"))
                        Console.WriteLine((node.SecondExpression as StringLiteral).Value);

Now consider this second case, which has a complex expression (string concatenation)

SELECT * FROM Person.Contact
WHERE LastName LIKE ‘%’ + @blah + ‘%’

In this case, if you try to reuse the ExplicitVisit code mentioned above, it will fail to detect the issue. Because, the SecondExpression member of the LikePredicate is now a BinaryExpression. This complicates the check:

if (node.SecondExpression is BinaryExpression)
               if ((node.SecondExpression as BinaryExpression).SecondExpression is StringLiteral)
                   if ((((node.SecondExpression as BinaryExpression).SecondExpression) as StringLiteral).Value.StartsWith("%"))
                          Console.WriteLine((((node.SecondExpression as BinaryExpression).SecondExpression) as StringLiteral).Value);


For arbitrary AST shapes, you can see that this is a very difficult issue to resolve in code. So while the ScriptDOM parser is a great tool, it does requires a fundamentally different approach to navigating the AST. Something to be kept in mind if you are attempting to use the parser in any industrial strength application.


Education Resources: Mathematics and Chemistry add-ins

Kids in school today are more comfortable working with computers than with pen-and-paper. And for kids with special needs it may actually be the only way for them to express themselves. So I always wondered what we can do to make the learning experience more accessible and powerful.

I was recently informed of an Mathematics add-in for Word 2013. This is so cool – not only does it facilitate the entry and display of mathematical symbols for you, it actually will solve some for you as well. And it also offers a graphing facility to generate plots of the curves which you have entered as equations. Very nice stuff, and it makes me want to reopen my textbook and re-learn all the stuff that I have forgotten by now Smile


Want more?

If this got your interest, I’m pretty sure you will love the free and full-featured Microsoft Mathematics 4.0 package, specially created for education. That also has a teachers guide and documentation to go along. There’s even a Chemistry add-in for Word, which is now an open source project on CodePlex.

I’m really happy to see these products which Microsoft has made available to the education community. Do check them out and spread the word!

Getting worker thread IDs using DMVs

In SQL 2000, the kpid column in sysprocesses was a convenient way to find out the actual worker thread ID (OS level thread identifier) for a given task. How does one do this using the DMVs?

Here is a quick way:

select R.Session_Id, Th.os_thread_id from sys.dm_exec_requests R
join sys.dm_exec_sessions S on R.session_id = S.session_id
join sys.dm_os_tasks T on R.session_id = T.session_id
join sys.dm_os_workers W on T.worker_address = W.worker_address
join sys.dm_os_threads Th on W.thread_address = Th.thread_address
where S.is_user_process = 1
order by Session_id

The above script will get you the session ID and thread ID for any tasks currently running, and will also filter out any non-user processes. BUT… my hand-written T-SQL looks so crowded and ugly. Let us format it with the T-SQL formatting script I shared a while back on my blog:

SELECT R.Session_Id,
FROM   sys.dm_exec_requests AS R
sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
sys.dm_os_tasks AS T
ON R.session_id = T.session_id
sys.dm_os_workers AS W
ON T.worker_address = W.worker_address
sys.dm_os_threads AS Th
ON W.thread_address = Th.thread_address
WHERE  S.is_user_process = 1;

Looks a lot easier to read! Now, by switching the last is_user_process flag to 0, you can switch to viewing the OS thread IDs of system processes. In the next post, I will show you how to use this knowledge and information to dig even deeper and view the operation of system threads like the lazy writer. Stay tuned!

TechEd India 2013 – Bengaluru

I just delivered a session on ‘T-SQL Horrors: how NOT to code’ at TechEd India. The response has been great, and those of you who see this after attending my session, thank you very much for your interest and participation! What I appreciate the most is that people stayed till the very end despite the session being at the end of a long day. Kudos and I hope you enjoyed the session, do leave a comment or use the ‘Contact me’ link on this blog to send your feedback.

TechEd India @ Pune, here we come! All my Mumbaikar SQL fans and customers, do see if you can make it on the 25th and 26th at the Pune Marriot.

Here’s a photo (a bit too colorful and blurry but passable Smile) taken by my good friend and colleague Balmukund who presented two great sessions – one on AlwaysOn AGs and the other on Backup myths.