SQL Complete: Parsing performance improved!

Recently we have released a free SQL Complete tool that provides IntelliSense and script layout for SQL Server. If you like IntelliSense in Visual Studio and write T-SQL, you’ll probably appreciate the productivity improvements with SQL Complete.

However, we received many letters with complaints about SQL Complete work with relatively large scripts with thousands of lines of code. When opening such a script our tool could display the “System.StackOverflowException” message. On some DML statements our add-in even could hang up SSMS. For example, the following query with a large number of OR and AND keywords in the WHERE clause caused hang-ups:

SELECT
   A.*
FROM
   Table1 A INNER JOIN Table2 B ON A.Id = B.Id	
WHERE
   ISNULL(A.Column1, '') <> ISNULL(B.Column1, '')
      OR ISNULL(A.Column2, '') <> ISNULL(B.Column2, '')
      OR ISNULL(A.Column3, 0) <> ISNULL(B.Column3, 0)
      OR ISNULL(A.Column4, '') <> ISNULL(B.Column4, '')
      OR ISNULL(A.Column5, 0) <> ISNULL(B.Column5, 0)
      ...
      OR ISNULL(A.ColumnN, 0) <> ISNULL(B.ColumnN, 0)

The same could have happened because of large numbers of mathematical operators:

SELECT
      'A,' + 'B,' + @var1 + ',' + ',' + ',' + @var2 + ',' +
      ISNULL(REPLACE(CONVERT(VARCHAR(10) , column1 , 103) , '/' , '') , '') + ',' 
      + @var3 + ',' + '"' +
      ISNULL(CAST(column2 AS VARCHAR) , '') + ISNULL(CAST(column3 AS VARCHAR) , '') +
      CASE WHEN ABS(column4) > ABS(column5) THEN '-1'
           ELSE ''
      END + '"' + ',' + '"",' + ISNULL(column6 , '') + ',' + ',' + ',' + ',' +
      ISNULL(CAST(CAST(( -1 * column5 ) AS DECIMAL(20 , 4)) AS VARCHAR) , '') + ',' + '"' +
      REPLACE(REPLACE(@var4 , '@@KST' , column7) , '@@DEBITOR' , column6) 
      + '"' + ',' + ',' + ',' +
      ISNULL(column10 , 'EUR') + ',' + ',' + ',' + ',' + 
      ISNULL(REPLACE(CONVERT(VARCHAR(10) , column1 + CAST(column8 AS DATETIME) , 103) ,
         '/' , '') , '') + ',' +
      ISNULL(CAST('' AS VARCHAR) , '') + ',' + ISNULL(CAST('' AS VARCHAR) , '') + ',' +
		--',' + ',' + ',' + ',' + ',' + ',' + ',' + ',' + ',' + ',' +
      REPLICATE(',' , 10) + ISNULL('"' + column9 + '"' , '""') + ',' + ',' 
      + ',' + ',' + ',' + ',' + ',' + ',' +
      ',' + ',' + ',' + ',' + ',' + ',' + ',' + ',' AS _output_
  FROM 
      tabl3

We have done a lot of work optimizing parcing of large scripts, and all these inconveniences were fixed. Also the “Error HRESULT E_FAIL has been returned from a call to a COM component.” error that occurred when working with several documents and closing one of them was fixed.

Also users can take advantage of color schemas that differ from the standard color schema of SSMS. Because selected object was marked with yellow color by default and could not be changed, in some cases in user-defined color schemas the color of symbols was the same as the schema color. Now the color of all elements of the completion drop-down list is selected from the current color schema.

Download the free SQL intellisense add-in for SSMS by Devart.