Share via

Walkthrough: Analyze Transact-SQL Code for an Existing Database

  • Article

You can improve the quality of the Transact-SQL code in a database schema by importing it into a database project and analyzing the code against a set of rules. For example, you might want to find any errors in a schema that you must use but that you did not develop and whose quality has not been verified. For more information, see Analyzing Database Code to Improve Code Quality.

Before you can analyze your database code, you must have already imported the schema for an existing database into a database project. That project contains code of an unknown level of quality. You want to analyze the Transact-SQL code, applying all available rules for static code analysis. Later, you might decide to turn off some rules for your team, but for this initial assessment, you want to find all the potential problems in the database code. You review the warnings and the code that caused those warnings. You correct a warning, suppress more warnings, and then re-analyze the database project.

Prerequisites

Before you can complete this walkthrough, you must complete Walkthrough: Put an Existing Database Schema Under Version Control. In that walkthrough, you create a solution that contains a database project that is named MyAdvWorks.

To configure rules for analyzing your database project

  1. In Visual Studio, open the MyAdvWorks solution.

  2. In Schema View, expand the node for the database project if it is not already expanded.

  3. On the Data menu, point to Static Code Analysis, and click Configure.

    The code analysis properties for your database project appear.

  4. In the Rules list, expand the Design, Naming, and Performance nodes to display all the rules that are available to analyze Transact-SQL code.

  5. Verify that the check boxes for all rules are selected.

    You can select or clear the check box for a category of rules, such as Design, to select or clear the check boxes for each rule in that category.

    Note

    You can treat a rule as an error instead of a warning by selecting the Treat Warning as Error check box for that rule.

  6. On the File menu, click Save All.

    Next, you will analyze the Transact-SQL code in your database project. In this walkthrough, you will start the analysis manually, but you can configure it to start after each successful build of your database project. For more information, see How to: Enable and Disable Static Analysis for Database Code.

To analyze your database project

  • On the Data menu, point to Static Code Analysis, and click Run.

    The Transact-SQL code in your database project is analyzed, and warnings appear in the Error List. If the Error List does not appear, open the View menu, and click Error List.

    Next, you will view and correct one of the warnings.

To view and correct a warning

  1. In the Error List, find the following warning:

    SR0014 : Microsoft.Rules.Data: Data loss might occur when casting from ASCII String(1) to SmallInt.

    The code that caused this warning is in the file that is named "ufnGetStock.function.sql". You can find the code in line 12, column 30.

  2. Right-click the warning in the Error List, and click Show Error Help.

    The Help topic for rule SR0014 appears. You can learn about what triggered the rule, how to resolve the warning, and when you might want to ignore the warning. You can also see an example of Transact-SQL code that would cause this warning and updates to that code that would resolve the warning.

  3. In the Error List, double-click the warning, or highlight it and press ENTER.

    The Transact-SQL editor opens and displays the code that caused the warning. The cursor appears at the start of the code that caused the warning. In this case, the cursor appears in the FROM clause because the integer column, LocationID, is being compared with a single character constant, '6'. The following code appears:

    CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int] 
AS 
-- Returns the stock level for the product. This function is used internally only
BEGIN
    DECLARE @ret int;

    SELECT @ret = SUM(p.[Quantity]) 
    FROM [Production].[ProductInventory] p 
    WHERE p.[ProductID] = @ProductID 
        AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage

    IF (@ret IS NULL) 
        SET @ret = 0

    RETURN @ret
END;

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'FUNCTION', @level1name = N'ufnGetStock';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'FUNCTION', @level1name = N'ufnGetStock', @level2type = N'PARAMETER', @level2name = N'@ProductID';

  4. Update the code for the SELECT statement to match the following example:

        SELECT @ret = SUM(p.[Quantity]) 
    FROM [Production].[ProductInventory] p 
    WHERE p.[ProductID] = @ProductID 
        AND p.[LocationID] = 6; -- Only look at inventory in the misc storage

  5. On the File menu, click Save ufnGetStock.function.sql.

    Next, you will review and suppress a second warning.

To view and suppress a code analysis warning

  1. In the Error List, find the following warning:

    SR0011 : Microsoft.Rules.Data: Object name(Database Version) contains special characters.

    The code that causes this warning is in the file that is named "AWBuildVersion.table.sql". You can find the code in line 3, column 5.

    At this point, you must decide whether to eliminate the special characters from the name of the object. By using database refactoring, you can automate updating all references to this object so that they contain the correct name. However, you will break any applications that depend on the old name. If you do not have enough information to decide on the best approach, you can suppress the warning until you have investigated the ramifications of the change. You might also create a work item in Visual Studio Team Foundation Server to track this task or even assign it to someone else.

  2. In the Error List, click the heading for the Description column.

    The Error List sorts the warnings by their descriptions, grouping all the SR0011 warnings together.

  3. Scroll the Error List until the SR0011 warning appears, and then highlight that warning.

    If you wanted to ignore multiple warnings, you could highlight a contiguous list of warnings by clicking the first warning in the list and then pressing the SHIFT key as you click the last warning in the list.

    Note

    You can create a work item from this list by right-clicking any highlighted row, pointing to Create Work Item, and clicking a type of work item. If you suppress the warning until the name can be changed, you should include an instruction in the work item to unsuppress the warning after the name is changed. To keep this walkthrough brief, you will skip creating the work items.

  4. Right-click any highlighted row, and click Suppress Static Code Analysis Message(s).

    A file that is named StaticCodeAnalysis.SuppressMessages.xml is added to your database project. The MyAdvWorks.dbproj file is checked out from version control. The suppressed warnings disappear from the Error List, leaving fewer warnings in the list.

    Note

    If you suppress a warning for a file in your database project, you suppress all instances of that warning for that file.

    The XML file contains the list of suppressed warnings. You can delete that file if you want to unsuppress all suppressed warnings. For more information, see How to: Stop Suppressing Warnings for Database Code Analysis.

    In the final procedure, you will re-analyze the database project.

To re-analyze your database project

  • On the Data menu, point to Static Code Analysis, and click Run.

    The Transact-SQL code in your database project is analyzed again, and the remaining warnings appear in the Error List. The warnings that you corrected or suppressed do not appear.

Next Steps

In a typical environment, you would analyze each warning that appeared in the Error List. You would then fix the issues that you can fix immediately, suppress the issues that can be ignored, and create work items for issues that must be fixed later. For some of the issues, you can use database refactoring to resolve the warnings. For more information about database refactoring, see Refactor Database Code and Data.

After you resolve or suppress each warning, you should run your database unit tests and application tests to verify that the changes have not introduced problems. For more information about database unit tests, see Verifying Database Code by Using Unit Tests.

See Also

Concepts

Analyzing Database Code to Improve Code Quality