Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

Tools

Tag cloud

book bug business intelligence database dates dmv functions gemini gotcha how to howto indexing interview madison performance performance tuning postgresql programming sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 sqlserver t-sql tip trick xml

Authors

Main Categories

Search

XML Feeds

What is RSS?

Google Ads

« Read from your mirror in SQL ServerBackup and copy warm-standby (log shipped) databases in SQL Server »
0
comments

Running static code analysis on SQL Server database with Visual Studio Team System for database architects

by SQLDenis on Jan 31, 2010 in categories Data Modelling & Design, Database Programming, Database Administration, Microsoft SQL Server Admin, Microsoft SQL Server
Rate Post:
submit to reddit Digg!FacebookDotnetkicks

To me it seems that Visual Studio Team System 2008 Database Edition is the stepchild of the Visual Studio family. Even in shops that have MSDN Universal/Ultimate subscriptions this version is just not used that much. Maybe it is that long name of this product, I still prefer DataDude. I would like to show you that if you do have licenses for this tool then you should use it because it has some great features. Today we will focus on static code analysis.

Before we start, make sure to grab Microsoft Visual Studio Team System 2008 Database Edition GDR R2
and install that on top of your Visual Studio Team System 2008 Database Edition. This install adds the following things to Visual Studio

In addition to providing support for SQL Server 2008 database projects, this release incorporates many previously released Power Tools as well as several new features. The new features include distinct Build and Deploy phases, Static Code Analysis and improved integration with SQL CLR projects.

Database Edition no longer requires a Design Database. Therefore, it is no longer necessary to install an instance of SQL Express or SQL Server prior to using Database Edition.
You can download Microsoft Visual Studio Team System 2008 Database Edition GDR R2 here http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

Once you have everything installed, start up Visual Studio and create a new database project. You should see something similar to the pic below

Create a new database connection, you can do that by going to View-->Server Explorer. Under Data Connections add a new connection to your database that you want to connect to, follow the wizard and create the connection. Now I decided to run static code analysis against the aspnetdb database that is used with ASP.NET. If you want to follow along and use this same database, take a look at this post Setting up SQL Server with ASP.NET MVC to see how to set it up

Once your database is setup go to the Solution explorer, right click on the project and select Import Database Objects and Settings.

The following dialog will be shown

Pick your connection and then click start

The output will be similar to this

1/30/2010 11:26:40 AM Import of database schema has started.
1/30/2010 11:26:45 AM Adding all files to the project...
1/30/2010 11:26:46 AM Finished adding all files to the project.
1/30/2010 11:26:46 AM Done
1/30/2010 11:26:46 AM Import of database schema is complete.
1/30/2010 11:26:46 AM A summary of the import operation has been saved to the log file C:\SVN\InterrogateASP\InterrogateASP\Import Schema Logs\InterrogateASP_20100130042639.log.
1/30/2010 11:26:46 AM Press Finish to continue...

Here is what the solution explorer looks like, as you can see the folder hierarchy is very similar to the one in SSMS

Okay now we are ready to run our static code analysis, click on Data-->Static Code Analysis--Run
Run Analysis

Here is the output from that, this creates 216 warnings, I have not pasted the whole output here because it is longer than this whole blog post and most warnings are the same but just for different objects. Here is just a small part and we will look at two of those procedures mentioned in this

Running Code Analysis...
Verifying project state...
Finished verifying project state.
Loading project references...
Loading project files...
Building the project model and resolving object interdependencies...
Validating the project model...
ASPNET_APPLICATIONS_CREATEAPPLICATION.PROC.SQL(7,79)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_APPLICATIONS_CREATEAPPLICATION.PROC.SQL(24,15)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_CHECKSCHEMAVERSION.PROC.SQL(9,35)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_MEMBERSHIP_CHANGEPASSWORDQUESTIONANDANSWER.PROC.SQL(11,38)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_MEMBERSHIP_CHANGEPASSWORDQUESTIONANDANSWER.PROC.SQL(11,58)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_MEMBERSHIP_CHANGEPASSWORDQUESTIONANDANSWER.PROC.SQL(12,31)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_MEMBERSHIP_CHANGEPASSWORDQUESTIONANDANSWER.PROC.SQL(14,13)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_GETUSERSINROLES.PROC.SQL(9,75)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_GETUSERSINROLES.PROC.SQL(17,14)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_GETUSERSINROLES.PROC.SQL(23,32)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_ISUSERINROLE.PROC.SQL(10,75)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_ISUSERINROLE.PROC.SQL(20,31)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_ISUSERINROLE.PROC.SQL(27,31)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(10,64)Warning : SR0015 : Microsoft.Performance : Deterministic function call (LOWER) might cause an unnecessary table scan.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(52,32)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(60,83)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(86,32)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(94,83)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(102,35)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(102,47)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(108,23)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(108,36)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(108,56)Warning : SR0010 : Microsoft.Design : Old-style JOIN syntax is used.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(110,7)Warning : SR0004 : Microsoft.Performance : A column without an index that is used as an IN predicate test expression might degrade performance.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(111,7)Warning : SR0004 : Microsoft.Performance : A column without an index that is used as an IN predicate test expression might degrade performance.
ASPNET_USERSINROLES_REMOVEUSERSFROMROLES.PROC.SQL(118,8)Warning : SR0004 : Microsoft.Performance : A column without an index that is used as an IN predicate test expression might degrade performance.
ASPNET_WEBEVENT_LOGEVENT.PROC.SQL(44,9)Warning : SR0014 : Microsoft.Design : Data loss might occur when casting from Decimal to Decimal.
ASPNET_WEBEVENT_LOGEVENT.PROC.SQL(45,9)Warning : SR0014 : Microsoft.Design : Data loss might occur when casting from Decimal to Decimal.
Code analysis complete -- 0 error(s), 216 warning(s)

Now I was intrigued so I picked the aspnet_Applications_CreateApplication stored procedure from that list.

tsql
Line number Off | Hide | Select all
  1. USE [aspnetdb]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[aspnet_Applications_CreateApplication]    Script Date: 01/17/2010 15:51:15 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER OFF
  7. GO
  8.  
  9. ALTER PROCEDURE [dbo].[aspnet_Applications_CreateApplication]
  10.     @ApplicationName      NVARCHAR(256),
  11.     @ApplicationId        UNIQUEIDENTIFIER OUTPUT
  12. AS
  13. BEGIN
  14.     SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
  15.  
  16.     IF(@ApplicationId IS NULL)
  17.     BEGIN
  18.         DECLARE @TranStarted   BIT
  19.         SET @TranStarted = 0
  20.  
  21.         IF( @@TRANCOUNT = 0 )
  22.         BEGIN
  23.             BEGIN TRANSACTION
  24.             SET @TranStarted = 1
  25.         END
  26.         ELSE
  27.             SET @TranStarted = 0
  28.  
  29.         SELECT  @ApplicationId = ApplicationId
  30.         FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
  31.         WHERE LOWER(@ApplicationName) = LoweredApplicationName
  32.  
  33.         IF(@ApplicationId IS NULL)
  34.         BEGIN
  35.             SELECT  @ApplicationId = NEWID()
  36.             INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
  37.             VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
  38.         END
  39.  
  40.  
  41.         IF( @TranStarted = 1 )
  42.         BEGIN
  43.             IF(@@ERROR = 0)
  44.             BEGIN
  45.             SET @TranStarted = 0
  46.             COMMIT TRANSACTION
  47.             END
  48.             ELSE
  49.             BEGIN
  50.                 SET @TranStarted = 0
  51.                 ROLLBACK TRANSACTION
  52.             END
  53.         END
  54.     END
  55. END

As you can see it uses the LOWER function in this line

WHERE LOWER(@ApplicationName) = LoweredApplicationName

It actually tells you on which line and position this is actually used (ASPNET_APPLICATIONS_CREATEAPPLICATION.PROC.SQL(7,79)Warning) so this means line 7, position 79

Here is another stored procedure, this one uses the LOWER function and old style joins

tsql
Line number Off | Hide | Select all
  1. USE [aspnetdb]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]    Script Date: 01/17/2010 15:53:59 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER OFF
  7. GO
  8.  
  9. ALTER PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
  10.     @ApplicationName  NVARCHAR(256),
  11.     @UserNames        NVARCHAR(4000),
  12.     @RoleNames        NVARCHAR(4000)
  13. AS
  14. BEGIN
  15.     DECLARE @AppId UNIQUEIDENTIFIER
  16.     SELECT  @AppId = NULL
  17.     SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
  18.     IF (@AppId IS NULL)
  19.         RETURN(2)
  20.  
  21.  
  22.     DECLARE @TranStarted   BIT
  23.     SET @TranStarted = 0
  24.  
  25.     IF( @@TRANCOUNT = 0 )
  26.     BEGIN
  27.         BEGIN TRANSACTION
  28.         SET @TranStarted = 1
  29.     END
  30.  
  31.     DECLARE @tbNames  TABLE(Name NVARCHAR(256) NOT NULL PRIMARY KEY)
  32.     DECLARE @tbRoles  TABLE(RoleId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
  33.     DECLARE @tbUsers  TABLE(UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
  34.     DECLARE @Num      INT
  35.     DECLARE @Pos      INT
  36.     DECLARE @NextPos  INT
  37.     DECLARE @Name     NVARCHAR(256)
  38.     DECLARE @CountAll INT
  39.     DECLARE @CountU   INT
  40.     DECLARE @CountR   INT
  41.  
  42.  
  43.     SET @Num = 0
  44.     SET @Pos = 1
  45.     WHILE(@Pos <= LEN(@RoleNames))
  46.     BEGIN
  47.         SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
  48.         IF (@NextPos = 0 OR @NextPos IS NULL)
  49.             SELECT @NextPos = LEN(@RoleNames) + 1
  50.         SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
  51.         SELECT @Pos = @NextPos+1
  52.  
  53.         INSERT INTO @tbNames VALUES (@Name)
  54.         SET @Num = @Num + 1
  55.     END
  56.  
  57.     INSERT INTO @tbRoles
  58.       SELECT RoleId
  59.       FROM   dbo.aspnet_Roles ar, @tbNames t
  60.       WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
  61.     SELECT @CountR = @@ROWCOUNT
  62.  
  63.     IF (@CountR <> @Num)
  64.     BEGIN
  65.         SELECT TOP 1 N'', Name
  66.         FROM   @tbNames
  67.         WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
  68.         IF( @TranStarted = 1 )
  69.             ROLLBACK TRANSACTION
  70.         RETURN(2)
  71.     END
  72.  
  73.  
  74.     DELETE FROM @tbNames WHERE 1=1
  75.     SET @Num = 0
  76.     SET @Pos = 1
  77.  
  78.  
  79.     WHILE(@Pos <= LEN(@UserNames))
  80.     BEGIN
  81.         SELECT @NextPos = CHARINDEX(N',', @UserNames,  @Pos)
  82.         IF (@NextPos = 0 OR @NextPos IS NULL)
  83.             SELECT @NextPos = LEN(@UserNames) + 1
  84.         SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
  85.         SELECT @Pos = @NextPos+1
  86.  
  87.         INSERT INTO @tbNames VALUES (@Name)
  88.         SET @Num = @Num + 1
  89.     END
  90.  
  91.     INSERT INTO @tbUsers
  92.       SELECT UserId
  93.       FROM   dbo.aspnet_Users ar, @tbNames t
  94.       WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
  95.  
  96.     SELECT @CountU = @@ROWCOUNT
  97.     IF (@CountU <> @Num)
  98.     BEGIN
  99.         SELECT TOP 1 Name, N''
  100.         FROM   @tbNames
  101.         WHERE  LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE u.UserId = au.UserId)
  102.  
  103.         IF( @TranStarted = 1 )
  104.             ROLLBACK TRANSACTION
  105.         RETURN(1)
  106.     END
  107.  
  108.     SELECT  @CountAll = COUNT(*)
  109.     FROM    dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
  110.     WHERE   ur.UserId = u.UserId AND ur.RoleId = r.RoleId
  111.  
  112.     IF (@CountAll <> @CountU * @CountR)
  113.     BEGIN
  114.         SELECT TOP 1 UserName, RoleName
  115.         FROM         @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
  116.         WHERE        u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
  117.                      tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
  118.                      tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
  119.         IF( @TranStarted = 1 )
  120.             ROLLBACK TRANSACTION
  121.         RETURN(3)
  122.     END
  123.  
  124.     DELETE FROM dbo.aspnet_UsersInRoles
  125.     WHERE UserId IN (SELECT UserId FROM @tbUsers)
  126.       AND RoleId IN (SELECT RoleId FROM @tbRoles)
  127.     IF( @TranStarted = 1 )
  128.         COMMIT TRANSACTION
  129.     RETURN(0)
  130. END

So, as you can see it is pretty simple to use Visual Studio Team System 2008 Database Edition to perform static code analysis, the question is of course if you dare to run this against your own database?

I will be back with another post to explore some other things that this tool can do




*** Remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

About the Author

User bio imageDenis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Social SitingsTwitterFacebookLinkedInHomePageLTD RSS Feed
479 views
More Posts by SQLDenis
code analysis, sql server 2008, visual studio 2008
submit to reddit Digg!FacebookDotnetkicks

Comments and Feedback

No feedback yet

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)

Powered by B2Evolution
Valid XHTML | Valid CSS