CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2sql-server-2016
Gopakumar N.Kurup
Gopakumar N.Kurup

Reputation: 936

Tool to convert queries with Non-ANSI syntax to ANSI syntax in SQL Sever 2008 R2

As a background, our system was working from SQL 2000 and was using the Non-ANSI syntax to write the queries. When we migrated to SQL 2008 R2, we have modified the "= * " and " * =" with RIGHT as well as LEFT joins. But now we are planning to migrate to SQL 2016. The Comma JOINs are deprecated. We can use the Comma Joins in this version also, but Microsoft suggests like this will impact the performance of the queries. Also we don't know how long SQL Server will extend the support on these Non-ANSI components.

Now we are looking for a tool (either Microsoft or Thirdparty) which can scan the stored procedures and will be able to change the Non-ANSI syntax like ',' and to replace this with INNER JOIN or JOIN.

So far I have tried with

Toad for Oracle https://www.toadworld.com/products/toad-for-oracle/f/10/t/9518

and noticed that it is not giving the proper output.

Also used couple of other tools, both of them not serving the purpose.

http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/rewrite-oracle-propriety-joins-to-ansi-sql-compliant-joins/

http://info.swissql.com/products/sqlone-apijava/sqlone-apijava.html

It will be helpful if anybody who came across the similar scenario can shed some light on this. Any help will be much appreciated.

Upvotes: 1

Views: 2419

Answers (1)

HLGEM
HLGEM

Reputation: 96572

Frankly I don't think I would trust any tool to get this right for anything except a single join. You will need to run the old code and the new code and make sure you get the same results.

What I would suggest is that you target which queries to fix by identifying the ones that take the longest in your current environment (and maybe look at some performance tuning if they are truly long-running while you are at it) and those queries run most frequently. Since the 2016 version is going to allow you to use the deprecated syntax, by fixing the most critical ones first, you will avoid the worst of the potential performance issues. I would suggest getting the top 10 longest running queries and the top ten most frequently used ones, fixing those, then moving on to the next ten of each category.

Most critical is to train your devs not to use this syntax anymore. Also you can have them fix any query they need to touch for maintenance or bugs while they are at it and enforce that through 100% code review.

Upvotes: 2

Related Questions