Managing SQL Stored Procedures - Categorize for better Maintenance

Next Free Tool is SQLTreeO. I came to know about this tool from SQL blog post. Andy Leonard has provided clear walkthrough for the tool.

Installation is pretty easy. This tool is a great way to categorise and organize procedures. Please find below snapshot. I have categorised procedures for validation, reporting, loading data.



Happy Learning!!!

SQL Everywhere - SQL Server IDE - Nice Free IDE

SQL Everywhere is the Tool we will be learning in this post. This is Free IDE for SQL Server Development. Impressive list of Free SQL Tools from Atlantis Interactive. We have explored Schema Surf and Data Surf in earlier posts.

Below are list of features I am impressed based on my first look using SQL Everywhere

• Suggestions intelli-sense support
• Run queries in Test Mode, Automatic Rollback of Updates / Changes made
• Code Formatting
• Good Execution Plan Details to get started

Let's get started with the Tool

Step 1. Download tool from link

Step 2. Features snapshots are provided in link

Step 3. After Installing the Tool, Connect to DB. Select Server and DB details.

Step 4. After Connecting, You can view in Databases / Objects Tab

Step 5. After selecting browse options you would see below new window. When you hover over particular table you would see Tool Tip - Schema Details, Create Table statement, Index Details

Step 6. Another alternate option is selecting Object Browser

Step 7. Moving to Query Execution, Query Results, Execution plan details are returned when you provide query details. IDE provides intellisense support to select Table Name, Column Details etc. Execution Plan also provides cost details of operators involved in query

Step 8. Operators also provides details on Estimated cost as a Tool Tip when you hover over the query plan

Step 9. One feature I observed different from SSMS is, Query execution reports execution time plus network time as well. This looks cool

Step 10. Next Option we would check is Generate Insert Statements. Right click on object browser to select the option for Generate Inserts

Step 11. Generate Inserts has option to include / exclude Identity column values

Step 12. Next is Test Mode feature. All updates / Data / Schema changes would be rolled back automatically when you exit Test Mode. Nice feature. This relieves us from the burden to rollback changes made.

Step 13. Code Formatting, I have posted my code before and after formatting. Looks good with after formatting :)

Step 14. After Code Formatting, Modified Proc

I'm impressed with SQL Everywhere. I'm going to use this tool. I would be updating this post in coming weeks.

Happy Learning !!!

SQL Search 1.0

Next free tool we are going to learn today is SQL Search 1.0. This is pretty good to search for objects within database.
Lets get started with the tool.

Step 1. Download from link

Step 2. After Installation in SSMS you would see this as listed below

Step 3. Good Snapshots of usage provided in link

Step 4. Search Preference is impressive.

• Table Name
• Functions
• Procedures

We are using AdventureWorks Database. This database can be dowloaded from link.

Search Feature is also present in SSMS tool addin. We checked it in earlier post.

Advantage is here, You need not right click and search. As you type you will see results listed
CONS - This does not search for data in tables. SSMS tools does provide data search


Search within Stored Procedures also lists the stored procedure content in preview tab

Search within database objects is very good with this tool.


Great Tool. Both SSMS Tools and SQL Search Complement each other.

Happy Reading!!

SQL Reverse Engineer - Learn the Database System from Database Tables and Data

Today we will look at two powerful free tools which would help to understand the database based on data and schema. Data Surf and SQL dependency & live entity ER diagram tool are very interesting and impressive tools.

First tool for Discussion is Data Surf. Analyze the table based on Data.  This helps to understand data flow, relationships between tables.

Let's get started
Step 1. For this example lets download adventureworks database. Download adventureworks from link

Step 2. Download the tool from link

Step 3. Install AdventureWorks database. After installation you would see the new databases as in below snapshot

Step 4. Double click on Atlantis Data Surf link placed in desktop

Step 5. Select the Server and Database name to get started

Step 6. Select the Table in next step

Step 7. Select the rows from the table to view relationships / dependencies

Step 8. Based on the select row, You can view parent and child records. In SSMS, You would check with schema, foreign key dependencies. This is a very cool way to view relationships, foreign keys, visualize data flow

Great Tool. Nice Approach to understand Database Tables. Thanks to the creator of this tool for providing this as Free tool.


Next tool is SQL dependency & live entity ER diagram tool

You are going to work on larger database system. You need to understand tables and relationships. You would like to learn it interactively. Analyzing based on schema, viewing multiple levels of dependencies is possible with this tool. Both the tools help us visualize the entities in database.

Step 1. Download and Install the tool from link. We will reuse AdventureWorks Database for this example

Step 2. After Installing the tool, Provide Database and Server Details to get started

Step 3. Select the table to view schema details and dependencies. I like the Grid View Layout Approach

Step 4. To view two level relationships you can select the option as highlighted below.

I'm Impressed with this two tools. They would be of great help for learning any backend system.


Happy Learning and Reading!!

SQLCop - Auditing Database for Best Practices

Last few posts we looked into SQL Server Free tools. In this post we will look at SQLCop. Auditing your database for SQL Server Best practices. Pretty simple to use and powerful tool, Let's get started.

Step 1. Download SQLCop from link

Step 2. Install the tool

Step 3. Provide database name, server name to connect. Snapshot provided below

Step 4. You would see results displayed. Red is violation of best practice, Also suggested solution for the same is displayed.

Step 5. Range of issue that is detected / analyzed by the tool are provided in link. Detected Issues

Step 6. I have not checked on SQL Best Practices Analyzer. I need to give it a try to compare both the tools.


Awesome tool, easy installation and very useful results along with suggestions for fixing identified issues.


More Reads - Custom SQL Stored Procedure Best Practices Analyzer - SQL Cop, Maybe?
Visual Studio Database Edition 2010 – Static Code Analysis Review (Part 1)
Visual Studio Database Edition 2010 – Static Code Analysis Review (Part 2)


Happy Reading!!!

Simplify TSQL Development with SSMS Tools

Tool for the day - SSMS Tools Pack (Free tool :)). This tool has very good features that would simply TSQL Development. Thanks to brent for his post. I am trying free tools suggested in the article. I'm going to try below three features.

• Formatting TSQL Code
• Generating Insert Statements from Table
• Search Results and Search Database Tables

Step 1. Download SSMS tool pack from link

Step 2. Feature documentation is provided in link

Step 3. After installing the tool, When you open SSMS you would see SSMS tools tab listed. Please find snapshot below

Step 4 - First and foremost aspect that every developer should to take care is Coding standards. Code should be readable, maintainable. I would love this tool for formatting raw TSQL code. After writing the code right click and select Format SQL Option as provided below. One click your code is formatted. Sounds cool.

Step 5 - Next aspect is generating insert statement from Tables.  Pushing configuration data from one environment to another environment would be easier with this feature. Right click in database select SSMS tools->Generate Insert Statements.

Step 6 - Select the table to generate insert statements, Specify option to generate script in new window / sql file. Very cool feature.

Step 7 - Next feature is search for data within database and searching the query results. Below provided are example snapshots

Very useful features. Above all free tool :).

More Reads

Extend Functionality in SQL Server 2005 Management Studio with Add-ins
The Mega Guide to Free SQL Server Tools


Happy Learning & Reading!!!

SQLSentry PlanExplorer

Most of the times at work, I was focussed on getting things done. There are lot more tools / ideas which could have been leveraged if I had taken a step back to try out different tools/ideas. Time vs Task always compete with each other. Best alternative is to take time to learn outside work.

Today we will look at another free tool SQL Sentry. This tool provides richer GUI interface for interpreting SQL Query Plans.

Step 1. Download Tool from link

Step 2. Installed the Tool. Shortcut SQL Sentry Plan Explorer would be created in desktop

Step 3. Provided below query and clicked on Actual Plan. In the pop up window provide database settings to execute the query.

Step 4. Quick Observation here, Test Connection button is not provided. If provided this can be used to test connection is successful


Step 5. Click on Estimated Plan. Actual Rows columns are blank here.

Step 6. Click on Actual Plan. You can see estimated vs actual rows.

Step 7. Let us reuse one of the procedures in previous posts and compare cost/plan against SSMS and SQL Sentry Plan Analysis Tool. Reusing from post

Step 8. Executed below TSQL Code

DECLARE @ID INT, @Name VARCHAR(100), @City VARCHAR(100)
SET @ID = NULL
 SET @City = 'City 1'
 SET @Name = NULL
 IF @ID IS NOT NULL
     SELECT * FROM TestforCode WHERE (ID = @ID)
 ELSE
    IF @Name IS NOT NULL
    SELECT * FROM TestforCode WHERE (NAME = @Name)
 ELSE
    IF @City IS NOT NULL
    SELECT * FROM TestforCode WHERE (CITY = @City)

 SET @ID = NULL

 SET @City = 'City 1'
 SET @Name = NULL
 SELECT * FROM TestforCode WHERE (ID = @ID) OR
(NAME = @Name) OR (CITY = @City)

Step 9. Similar to SSMS when you hover over it would provide you Actual vs Estimated Rows and Stats. This is also provided in the tool

Provided below is SSMS snapshot

Step 10. Very useful feature is IO cost, IO%, CPU cost, CPU% every thing is listed in the output. By one click you would be able to see the results. This is very handy and great information to get started.

This is very good information to get started on where to focus first for optimization. This is a great tool!!!

More Reads -

Five Favorite Free SQL Server Downloads
SQL Sentry's Plan Explorer : a closer look
SQL Sentry Plan Explorer Documentation
SQL Bits - SQL Server 2008 Database Internals

Happy Reading!!!

Query Stress Tool

Today's second post is for Query Stress Tool. Run your stored procedure for multiple datasets. In Testing terminology this tool is a great support for Data Driven Testing. Data Driven Testing - running the same test for different datasets.

The Tool has very good documentation available. I have tried to run this tool based on stored procedure created in previous posts.

Step 1 - Tool Overview is provided in link

Step 2 - Download tool from link. Author is Adam Machanic.

Step 3 - Installed the tool

Step 4 - Set the Database connection and Select the Database from Drop Down menu

Step 5 - Provide the stored procedure and parameter name as in below snapshot. Stored Procedure reused from post.

Step 6 - Click on Parameter Substitution tab and provide query which returns test input. Query provided in below snapshot. Click on Get Columns.

Step 7 - Click on GO button and run the test. One Click to run the test. Executes test for each test row if results. CPU and IO statistics returned.

Usage - Run your stored procedure for all your production data and check for Stats on execution

This is a very handy tool, I missed to learn and use this tool. Never Late, today I tried it out.

More Reads - SQLStress - A SQL Server Stress Test Tool

Happy Learning!!!

T.S.T - T-SQL Test Tool

In continuation with previous post, we will look at TST - TSQL Test Tool. Tool can be dowloaded from link. Documentation is very good and clear. Let's see step-by-step example for this tool .

Step 1 - Change to below value in TST.BAT File
SET TST_SqlServer=.\SQLExpress

Step 2. Executed Set up (TST is the core database required for running Tests)

Step 3. Setup TSTQuickStart Database (Example Database with Testcases in it)

Step 4. From previous posts, lets reuse the procs and write tests for the same
Reusing Tables and Procedure from post. AppDB is the Sample DB created for Demo.

Step 5. Create Test Procedure

CREATE PROCEDURE SQLTest_SaleCity

AS

BEGIN

DECLARE @City VARCHAR(100), @Sum INT

SET @City = 'Chennai'

DECLARE @TEMPDIAG TABLE (Name VARCHAR(100) NOT NULL, SaleDate DATETIME NOT NULL, SaleValue INT NOT NULL, City VARCHAR(100) NOT NULL)

INSERT INTO @TEMPDIAG

EXEC SaleCity @City

SELECT @Sum=SUM(SaleValue) FROM @TEMPDIAG

EXEC TST.Assert.Equals 'Sale in Chennai is', 9500, @Sum

END

GO

Step 6. Run the Test Procedure

Step 7. Creating Second Test Procedure

CREATE PROCEDURE SQLTest_SaleValue

AS

BEGIN

DECLARE @FromDate DATETIME, @ToDate DATETIME, @Sum INT

SET @FromDate = GETDATE()-100

SET @ToDate = GETDATE()

DECLARE @TEMPDIAG TABLE (Name VARCHAR(100) NOT NULL, SaleDate DATETIME NOT NULL,

SaleValue INT NOT NULL, City VARCHAR(100) NOT NULL)

INSERT INTO @TEMPDIAG

EXEC SaleValue @FromDate,@ToDate

SELECT @Sum=SUM(SaleValue) FROM @TEMPDIAG

EXEC TST.Assert.Equals 'Sum of Sales', 58090, @Sum

END

GO

Step 8. Run the Test Procedure

Benefits

• Ready to use
• Assertions are very helpful. More than 10 plus asserts are provided
• Generating XML results
• Exhaustive documentation

For DB test automation below parameters pass for TST - TSQL unit

• Ability to run suite of test cases
• Ability to run individual test case
• Handling Assertions without any extra coding
• Data Driven Testing can be implemented, This approach involves QA writing TSQL Code
• Below is a ideal case for Test Automation Scenario
• Invoke Web Services - Example post
• Invoke TST and verify values present

Good to have Additions

• Rerunning Failed Cases
• Email reports, GUI Interface

TST - is a good tool for DB Test Automation.

Demo Video



More Reads -
A Method For Testing Database Design
Database unit testing patterns
Taking this to next step, You can have a console app which can execute and email results.
Run a sql script file save the result in a text file and email it
Run all of these scripts against the database
Testing With Databases
CodePlex - xUnit.net - unit testing tool for the .NET Framework
TSQLUnit testing framework

Happy Reading!!