Category / Uncategorized

by Arvind Shyamsundar

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);
                    }
                }
            }

Conclusion

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.

by Arvind Shyamsundar

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

image

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!

by Arvind Shyamsundar

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,
Th.os_thread_id
FROM   sys.dm_exec_requests AS R
INNER JOIN
sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
INNER JOIN
sys.dm_os_tasks AS T
ON R.session_id = T.session_id
INNER JOIN
sys.dm_os_workers AS W
ON T.worker_address = W.worker_address
INNER JOIN
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!

by Arvind Shyamsundar

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.

by Arvind Shyamsundar

T-SQL formatter: a PowerShell script

EDIT 19 Aug 2022: This post has not been updated for a long time, and the link to the sample code is broken. So, I recommend you take a look at Mala’s more recent blog post and associated script at https://www.sqlservercentral.com/articles/formatting-t-sql-scripts-using-scriptdom-and-powershell If you really want to look at my original sample code, a member of the community updated my older script and shared it as a gist: https://gist.github.com/jheidt/5320803

This is a ‘quick-and-not-so-dirty’ version of a T-SQL formatter / ‘pretty printer’ in PowerShell. To use this, please install the SQL 2012 SP1 version of the SQLDOM from the Feature Pack page. (If you already have SQL 2012 client tools on your machine, you should be ready to go.) And you will need PowerShell of course Smile

Here is a sample usage of this script:

tsqlpp.ps1 -Source c:temptestcases.sql -Target c:tempout.sql

Some usage tips:

  • Right click and save the file to your machine, do NOT run it directly from this blog site
  • To execute the script your PowerShell execution policy must be set to RemoteSigned
  • The parser is by default set to 110 compatibility i.e. SQL 2012 syntax. If you are working with older compatibility levels feel free to edit the script to reference the 80, 90, 100 versions of the parser.
  • The generated / formatted script will NOT contain comments, you will lose any comments you had in the unformatted version

Please leave your comments, suggestions and asks for future versions of the script in the Comments section of this post. Thank you and I’m glad to be of help to you!

Disclaimer: This script is provided AS IS with no warranties, and confer no rights.

by Arvind Shyamsundar

Named constraints: two sides to the story!

Background

Constraints in SQL Server are of the following types:

  • CHECK constraints
  • DEFAULT constraints
  • Foreign key constraints
  • NULLable constraint
  • UNIQUE constraint
  • PRIMARY KEY constraint

BTW, if you are wondering what a NULLable constraint is, it is the formal representation of a NULL / NOT NULL definition for a field. We don’t normally think of NULL definitions in that way but in the parser that is how it is represented Smile

Anyways, constraints such as CHECK, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY can be named, for example:

CREATE TABLE #TableWithNamedConstraints
(
    i int not null constraint PK_TableWithNamedConstraints primary key,
    j int constraint DF_j default 100,
    k int constraint CK_k check (k > 0)
)

The issue

While this is generally considered to be a good practice for base tables, for a temp table such as the above, it can be a real problem if you have multiple connections executing the above CREATE TABLE code at the same time. The problem arises from the fact that while the temp table name is uniquified, the constraint names are not. In such cases of concurrent execution, you typically receive an error such as the below:

Msg 2714, Level 16, State 5, Line 1
There is already an object named ‘PK_TableWithNamedConstraints’ in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

You can see the evidence of this in sys.objects:

select name from sys.objects
where parent_object_id = object_id(‘#TableWithNamedConstraints’)
or object_id = object_id(‘#TableWithNamedConstraints’)

The output shows that the table name is uniquified but the constraint names are not:

PK_TableWithNamedConstraints
DF_j
CK_k
#TableWithNamedConstraints_________…_____00000000001D

Workaround

This issue is described in this Connect bug as well. The workaround is therefore to not name constraints in the case of temporary tables. So for the example shown above, the more ‘robust’ version (for the temp table case) would look like this:

CREATE TABLE #TableWithNamedConstraints
(
    i int not null primary key,
    j int default 100,
    k int check (k > 0)
)

Conclusion

So the next time you have a CREATE TABLE with constraints, consider the two cases:

  • For base tables, you almost ALWAYS want to name them
  • For temporary tables, you almost NEVER want to name them

Hope this helps!

by Arvind Shyamsundar

The Top 10 Issues uncovered by the SQL Server Risk Assessment Program (SQLRAP)

Firstly – Happy New Year, everyone! In my job as a Principal Premier Field Engineer at Microsoft Services, I am a regional lead for a proactive risk identification program called the SQLRAP. For over 7 years now we have evolved this from a manually conducted ‘Health Check’ offering to a highly automated and comprehensive Risk Assessment platform. I have the honor of sharing the top 10 most common patterns (a.k.a. Issues) we uncover as part of the program – you can read more about this at our MSPFE team blog.

by Arvind Shyamsundar

“The operation has timed out” while using Power View in Excel 2013

This one is a quick tip. I recently upgraded to Office 2013 RTM and wanted to use the excellent Power View feature which is now an integral part of Excel! However, after I setup my tables (actually I used the Power Pivot data model) I was consistently presented with the below error message whenever I tried to open the Power View add-in: “The operation has timed out” with a title of “Power View error”.

If you encounter this, my tip to you is to reinstall the Silverlight runtime on your PC. I don’t know exactly why, but that was in some way causing the above issue. I hope it will save you a few hours of troubleshooting!

by Arvind Shyamsundar

Microsoft Report Viewer 2012 update: a ‘gotcha’ to be aware of

Something which I have been waiting for a long time has finally been released! The ReportViewer 2012 redistributable RTM package is available for download now!

Deployment notes

FYI, ReportViewer is used by Management Studio (SSMS), other utilities and also by any custom application which uses this to render local RDLC reports, or within a web application to view remotely rendered reports.

SQL 2012 installation deploys ReportViewer if the Management Tools are selected for installation. The other shipping vehicle for the ReportViewer control is Visual Studio 2012. This blog post pertains more to the case where we installed ReportViewer through the normal SQL 2012 installer.

FYI, you can view the ReportViewer 2012 assembly version at C:windowsassemblyGAC_MSILMicrosoft.ReportViewer.WinForms11.0.0.0__….. Right clicking on the assembly, and viewing the Details tab will give you the version of the DLL.

Note the ‘gotcha’

Now, the updated runtime release will deploy the equivalent of SQL 2012 SP1 binaries, so you get the latest and greatest bits! These should deploy a 11.0.3010 version for the Microsoft.ReportViewer.WinForms.dll file (and other files as well.)

Now, here’s the ‘note from the field’ thing which you can only get from me Smile If you just install SQL 2012 SP1 (without later running the above download) it does not seem to update the ReportViewer control. Normally this may not have much visible impact, but if you are like me, you may want to keep the runtime up to date due to the number of important fixes in such updated versions.

Test case

In my tests, just applying SQL 2012 SP1 installation did NOT upgrade the runtime to 11.0.3000. It was still at 11.0.2100. However, applying the above updated runtime MSI will upgrade the runtime to 11.0.3010.

Your checkpoint is that the version of ReportViewer 2012 assembly under C:windowsassemblyGAC_MSILMicrosoft.ReportViewer.WinForms11.0.0.0__….. should finally be 11.0.3010 or higher. (repeat this check for the other controls such as Microsoft.ReportViewer.WebForms as well.

Conclusion

So in short, if you use ReportViewer – either indirectly (like in SSMS) or directly (through custom applications developed using Visual Studio 2012) it is highly recommended to update your RTM ReportViewer 11.0 runtime to the latest version using the MSI from the download link.

by Arvind Shyamsundar

Extra T-SQL checks to complement SQL Upgrade Advisor

Background

More and more customers are looking to upgrade to SQL Server 2012, which offers some exciting features like AlwaysOn Availability Groups, ColumnStore Indexes and much more. As part of my job in the Microsoft Premier Field Engineering team, we are called upon by customers to assess their readiness to upgrade to the new version.

Normally, when we perform an upgrade readiness assessment, we also review the T-SQL code that the customer is running. This is done to uncover breaking changes and other patterns which might cause poor performance or such behavioral change post the upgrade. For breaking changes, we normally recommend customers to use the publicly available SQL Server Upgrade Advisor (SSUA) tool. However, not all breaking changes can be detected by the current SSUA toolset.

Stuff outside of SSUA scope

Here are some examples of T-SQL breaking changes which cannot be uncovered by SSUA. Some of these are boundary scenarios, but I feel it is important to list them so that you are aware of the issues. Also, some of these are known to the SSUA team but they would not incorporate checks for them in SSUA due to their ship timeline and priority ratings.

Issue Conflicting locking hints
Description A specific issue in SQL Server 2000 caused locking hints in a FROM clause for an UPDATE statement, to be ignored. While this bug was fixed in SQL 2005, it potentially gave rise to upgrade issues if the locking hint in the FROM clause was different from the locking hint for the UPDATE source table.
Example See this Connect bug for an example.
Recommendation If at all used, locking hints need to be consistent between the table source and the FROM clause of the UPDATE.

 

Issue Objects in the FROM clause must have distinct exposed names
Description In SQL 2000, identically named tables from different databases did not require an alias
Example This code will work in compatibility mode 80 while it will break on compatibility 90 and above:

select *
from DB1.dbo.sometab
join DB2.dbo.sometab
on DB1.dbo.sometab.name = DB2.dbo.name
Recommendation It is generally a good practice in any case to alias all tables in a JOIN. That is the recommended solution in this case as well.
Additional Reading You can read more about this at Nacho’s blog.

 

Issue Subqueries are not supported in GROUP BY clause
Description The GROUP BY clause cannot contain a subquery in an expression that is used for the group by list. In earlier versions of SQL Server, this was allowed. In SQL Server 2008, error 144 is returned.
Recommendation Be aware of this breaking change and revise the query logic accordingly.
Additional Reading There are some Connect articles which describe this. Read them here, here and here.

 

Issue SETUSER statement usage
Description SETUSER may not be supported in a future release of SQL Server.
Recommendation We recommend that you use EXECUTE AS instead.

 

Issue ORDER BY clauses in views
Description The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
Recommendation Specify the ORDER BY clause only in the outermost query and not inside views.

 

Issue FASTFIRSTROW table hint usage
Description The usage of FASTFIRSTROW as a table hint has been disallowed in SQL 2012. We recommend that hints be used only as a last resort by experienced developers and database administrators.
Recommendation You can evaluate the query hint OPTION (FAST 1) instead.

 

Issue COMPUTE clause is not allowed in database compatibility 110
Description The COMPUTE clause generates totals that appear as additional summary columns at the end of the result set. However, this clause is no longer supported in SQL Server 2012.
Recommendation Use the ROLLUP operator instead.
Additional Info A Connect request has been logged to include this check in SSUA. Vote for it!

 

Conclusion

So, now you know the most common patterns which you need to check for in addition to what SSUA already uncovers. However let’s say you have a huge T-SQL code base and cannot do this manually. Our PFE team are working on some offerings: SQL Server Upgrade Assessment and also a T-SQL Patterns and Practices Review, which among other things will also cover the above deprecation / upgrade checks. If you need more information, you can contact me for more details.

Also if you have any comments, additional cases which are not detected by SSUA, please leave a feedback item in the Comments section below. I read each one of them!