Visual Studio Database Edition 2010 – Static Code Analysis Review (Part 1)


I’ve previously completed two reviews on the Static Code Analysis features provided in Visual Studio Database Edition (aka Data Dude). The first was on the 2005 Power Tools in October 2007, and the second on the 2008 Power Tools in May 2008. I didn’t get an opportunity to review the ‘baked in’ version provided with the GDR 2
release, but it looks pretty much identical to that provided in Visual Studio 2010.

I’m a huge fan of Static Code Analysis regardless of the language; in fact I even started a group on facebook called ‘I Run Code Analysis’. Feel free to join, particularly if you would like to fund an annual global summit somewhere nice and warm. On a more serious note though, those who read my reviews will know that I was not overwhelmed by the new feature on offer to SQL developers. I’ve had my hands out of the SQL pot for a while now, so I thought it would be interesting to see how much improvement there has been on the Static Code Analysis front.

Upgrade Note: if you are using GDR 2 and plan on moving to SQL Server 2008 R2, you will need to upgrade to Visual Studio 2010 as GDR 2 does not support SQL Server 2008 R2.

Rules

The rules provided in Visual Studio 2010 are the same as the Power Tool release and it looks like they have chosen not to adopt the new RuleSet design we see in languages like C#. This is probably because there really aren’t that many rules, which is unfortunate as I still believe more can be added (discussed further in part two)

Code Sample 1

CREATE
PROCEDURE
[dbo].[sp_RealBadCode]
(

@Value1 INT

,@Value2 INT

,@Value3 INT

,@Sum INT OUTPUT

)

AS

DECLARE @Comment NVARCHAR(1000)

DECLARE @CommentChecksum INT

DECLARE @NewID INT

DECLARE @Email NVARCHAR(100)

DECLARE @Small NVARCHAR(2)

SET @Email = ‘nobody@nnn.com’

SELECT [Alter]

FROM dbo.Sums

IF EXISTS
(SELECT 1 WHERE
LTRIM(RTRIM(@email))
LIKE
N’%[^a-z0-9._-]%@%’)

BEGIN

RAISERROR (60022, 16, 1,
@Value1,‘@Email — [sp_RealBadCode]’)

END

SELECT
@Comment
=
S.Comment

FROM
dbo.Sums
S

WHERE
S.Summation = @Sum

AND
S.Value1 IN
(1,2,3)

INSERT
INTO
Sums
(Value1, Value2, Summation, Comment)

VALUES (9, 19, 28,
‘comment 98’)

SET
@NewID
=
@@IDENTITY

SELECT
S.SumID

FROM
dbo.Sums
S

WHERE
S.Summation * @Value1
>
S.Summation * @Value2

SELECT
*

FROM
dbo.Sums
S

WHERE
S.Summation = @Sum

How many issues do you see?

The May 2008 Power Tool review found 23 errors with the sample stored procedure I wrote to be analysed. Using Visual Studio 2010, that number reduces to 16. Why? Well it appears that some necessary tweaks have been made, especially to Microsoft.Design#SR0014 which was overly aggressive in finding implicit conversions. This will be especially helpful to users who are running Static Code analysis for the first time as a lot of the previous noise will be avoided.

Visual Studio 2010 Analysis Results

Below are the results of the original analysis along with the Visual Studio 2010 analysis. I think it’s worth noting that at the time I wrote my previous reviews, there was no documentation on them. I’m very happy to see that we now have extensive documentation here. This documentation is so concise that I’ll not cover each violation like previous reviews, but rather encourage you to visit the documentation for each result you may not fully understand.

— 1. Microsoft.Naming#SR0016: Stored procedure(sp_RealBadCode) includes sp_ prefix in its name

— 1. Microsoft.Naming#SR0016:
Stored procedure(sp_RealBadCode) includes sp_ prefix in its name. (VS2010)

CREATE
PROCEDURE [dbo].[sp_RealBadCode]

(

@Value1 INT

,@Value2 INT

,@Value3 INT

— 2. Microsoft.Design#SR0013: Output parameter (@Sum) is not populated in all code paths

— 2. Microsoft.Design#SR0013: Output parameter (@Sum) is not populated in all code paths. (VS2010)

,@Sum INT
OUTPUT

)

AS

DECLARE @Comment NVARCHAR(1000)

DECLARE @CommentChecksum INT

DECLARE @NewID INT

DECLARE @Email NVARCHAR(100)

— 3. Microsoft.Design#SR0009: Avoid NVARCHAR of only 2 elements

DECLARE @Small NVARCHAR(2)

— 4. Microsoft.Design#SR0014: Data loss may occur when casting from ASCII String(14) to NVarChar(100)

— 3. Microsoft.Design#SR0014: Data loss might occur when casting from ASCII String(14) to NVarChar(100). (VS2010)

SET @Email =
‘nobody@nnn.com’

SELECT [Alter]

FROM dbo.Sums

— 5. Microsoft.Design#SR0014: Incorrect data type

— 6. Microsoft.Performance#SR0015: Deterministic function call (RTRIM) may cause unnecessary table scan

— 4. Microsoft.Performance#SR0015: Deterministic function call (LTRIM) might cause an unnecessary table scan. (VS2010)

— 7. Microsoft.Performance#SR0015: Deterministic function call (LTRIM) may cause unnecessary table scan

— 5. Microsoft.Performance#SR0015: Deterministic function call (RTRIM) might cause an unnecessary table scan. (VS2010)

IF
EXISTS
(SELECT 1 WHERE
LTRIM(RTRIM(@email))
LIKE
N’%[^a-z0-9._-]%@%’)

BEGIN

RAISERROR (60022, 16, 1, @Value1,‘@Email — [sp_RealBadCode]’)

END

— 8. Microsoft.Design#SR0014: Data loss may occur when casting from VarChar(50) to NVarChar(1000)

— 6. Microsoft.Design#SR0014: Data loss might occur when casting from VarChar(50) to NVarChar(1000). (VS2010)

SELECT @Comment = S.Comment

FROM dbo.Sums S

— 9. Microsoft.Design#SR0014: Expression is cast from Int to BigInt

— 10. Microsoft.Performance#SR0007: Nullable column can cause final result evaluted as NULL for the predicate

— 7. Microsoft.Performance#SR0007: Nullable columns can cause final results to be evaluated as NULL for the predicate. (VS2010)

WHERE S.Summation = @Sum

— 11. Microsoft.Performance#SR0004: Column without index used as IN predicate test expression may degrade performance

— 8. Microsoft.Performance#SR0004: A column without an index that is used as an IN predicate test expression might degrade performance. (VS2010)

— 12. Microsoft.Performance#SR0007: Nullable column can cause final result evaluted as NULL for the predicate

— 9. Microsoft.Performance#SR0007: Nullable columns can cause final results to be evaluated as NULL for the predicate. (VS2010)

AND S.Value1 IN
(1,2,3)

INSERT
INTO Sums
(Value1, Value2, Summation, Comment)

— 13. Microsoft.Design#SR0014: Expression is cast from ASCII String(10) to VarChar(50)

VALUES (9, 19, 28,
‘comment 98’)

— 14. Microsoft.Design#SR0008: Potential misuse of system function @@IDENTITY

— 10. Microsoft.Design#SR0008: Potential misuse of system function @@IDENTITY (VS2010)

SET @NewID =
@@IDENTITY

SELECT S.SumID

FROM dbo.Sums S

— 15. Microsoft.Design#SR0014: Expression is cast from Int to BigInt

— 16. Microsoft.Design#SR0014: Expression is cast from Int to BigInt

— 17. Microsoft.Performance#SR0006: Column in an expression to be compared in a predicate may cause a table scan and degrade the performance

— 18. Microsoft.Performance#SR0006: Column in an expression to be compared in a predicate may cause a table scan and degrade the performance

— 11. Microsoft.Performance#SR0006: A column in an expression to be compared in a predicate might cause a table scan and degrade performance. (VS2010)

— 12. Microsoft.Performance#SR0006: A column in an expression to be compared in a predicate might cause a table scan and degrade performance. (VS2010)

— 19. Microsoft.Performance#SR0007: Nullable column can cause final result evaluted as NULL for the predicate

— 20. Microsoft.Performance#SR0007: Nullable column can cause final result evaluted as NULL for the predicate

— 13. Microsoft.Performance#SR0007: Nullable columns can cause final results to be evaluated as NULL for the predicate. (VS2010)

— 14. Microsoft.Performance#SR0007: Nullable columns can cause final results to be evaluated as NULL for the predicate. (VS2010)

WHERE S.Summation * @Value1 > S.Summation * @Value2

— 21. Microsoft.Design#SR0001: The shape of the resultset produced by a SELECT * statement will change if the underlying table or view structure changes

— 15. Microsoft.Design#SR0001: The shape of the result set produced by a SELECT * statement will change if the underlying table or view structure changes. (VS2010)

SELECT
*

FROM dbo.Sums S

— 22. Microsoft.Design#SR0014: Expression is cast from Int to BigInt

— 23. Microsoft.Performance#SR0007: Nullable column can cause final result evaluted as NULL for the predicate

— 16. Microsoft.Performance#SR0007: Nullable columns can cause final results to be evaluated as NULL for the predicate. (VS2010)

WHERE S.Summation = @Sum

In Part 2 I’ll cover issues that have not been detected and suggest improvements that I believe will help SQL developers…

Base Scripts (alter the path N’C:\a\ to suit your system)

USE [master]
GO

IF
EXISTS
(SELECT name FROM
sys.databases
WHERE name =

N’StaticAnalysis-FTD’)

BEGIN

ALTER
DATABASE [StaticAnalysis-FTD] SET
SINGLE_USER
WITH
ROLLBACK
IMMEDIATE

ALTER
DATABASE [StaticAnalysis-FTD] SET
SINGLE_USER

DROP
DATABASE [StaticAnalysis-FTD]

END

GO

RAISERROR (‘Creating Database: StaticAnalysis-FTD’, 0,1)
WITH
NOWAIT

CREATE
DATABASE [StaticAnalysis-FTD] ON
PRIMARY

( NAME =
N’StaticAnalysis-FTD’,
FILENAME
=
N’C:\demo\StaticAnalysis-FTD.mdf’

, SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )

LOG
ON

( NAME =
N’StaticAnalysis-FTD_log’,
FILENAME
=
N’C:\demo\StaticAnalysis-FTD_log.ldf’

, SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

COLLATE Latin1_General_CI_AS

GO

EXEC dbo.sp_dbcmptlevel
@dbname=N’StaticAnalysis-FTD’, @new_cmptlevel=90

GO

USE [StaticAnalysis-FTD]

GO

IF
EXISTS
(SELECT * FROM

sys.objects
WHERE
object_id =

OBJECT_ID(N'[dbo].[Sums]’)
AND
type
in
(N’U’))

BEGIN

RAISERROR (‘Dropping Table: Sums’, 0,1)
WITH
NOWAIT

DROP
TABLE [dbo].[Sums]

END

RAISERROR (‘Creating Table: Sums’, 0,1)
WITH
NOWAIT

CREATE
TABLE [dbo].[Sums]

(

[SumID] [int] NOT
NULL
IDENTITY(0, 1),

[Value1] [int] NULL,

[Value2] [int] NULL,

[Summation] [bigint],

[Comment] [varchar]
(50)
NULL,

[CCHKSM] AS (checksum([Comment])),

[Alter] [VARCHAR]
(10)
NULL

)
ON [PRIMARY]

GO

RAISERROR (‘Populating Table: Sums’, 0,1)
WITH
NOWAIT

INSERT
INTO Sums
(Value1, Value2, Summation, Comment)

VALUES (1, 2, 3,
‘Comment1’)

INSERT
INTO Sums
(Value1, Value2, Summation, Comment)

VALUES (3, 12, 15,
‘CommentTwo’)

INSERT
INTO Sums
(Value1, Value2, Summation, Comment)

VALUES (4, 24, 27,
‘Comment 3’)

INSERT
INTO Sums
(Value1, Value2, Summation, Comment)

VALUES (6, 5, 11,
‘Comment four’)

USE [StaticAnalysis-FTD]

GO

IF
EXISTS
(SELECT * FROM

sys.objects
WHERE
object_id =

OBJECT_ID(N'[dbo].[sp_RealBadCode]’)
AND
type
in
(N’P’, N’PC’))

BEGIN

RAISERROR (‘Dropping Procedure: sp_RealBadCode’, 0,1)
WITH
NOWAIT

DROP
PROCEDURE [dbo].[sp_RealBadCode]

END

RAISERROR (‘Creating Procedure: sp_RealBadCode’, 0,1)
WITH
NOWAIT

GO

CREATE
PROCEDURE [dbo].[sp_RealBadCode]

(

@Value1 INT

,@Value2 INT

,@Value3 INT

,@Sum INT
OUTPUT

)

AS

DECLARE @Comment NVARCHAR(1000)

DECLARE @CommentChecksum INT

DECLARE @NewID INT

DECLARE @Email NVARCHAR(100)

DECLARE @Small NVARCHAR(2)

SET @Email =
‘nobody@nnn.com’

SELECT [Alter]

FROM dbo.Sums

IF
EXISTS
(SELECT 1 WHERE
LTRIM(RTRIM(@email))
LIKE
N’%[^a-z0-9._-]%@%’)

BEGIN

RAISERROR (60022, 16, 1, @Value1,‘@Email — [sp_RealBadCode]’)

END

SELECT @Comment = S.Comment

FROM dbo.Sums S

WHERE S.Summation = @Sum

AND S.Value1 IN
(1,2,3)

INSERT
INTO Sums
(Value1, Value2, Summation, Comment)

VALUES (9, 19, 28,
‘comment 98’)

SET @NewID =
@@IDENTITY

SELECT S.SumID

FROM dbo.Sums S

WHERE S.Summation * @Value1 > S.Summation * @Value2

SELECT
*

FROM dbo.Sums S

WHERE S.Summation = @Sum

One thought on “Visual Studio Database Edition 2010 – Static Code Analysis Review (Part 1)

  1. Pingback: Visual Studio Database Edition 2010 – Static Code Analysis Review (Part 2) « FreeToDev

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s