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!!

Feedback for my Blog

I dropped a note to Brent Ozar to thank his post on SQL Server Free Tools and useful SQL Articles in his blog. His post on free tools is my motivation for exploring free tools. I have also referenced his article in one of my post.

Below is Brent's reply for my mail.

Glad I could help!  Your step-by-step walkthroughs are great.

Have a good weekend!
Brent
---
Brent Ozar - Founder, Brent Ozar PLF, LLC
Microsoft Certified Master, MVP
http://www.BrentOzar.com

I'm happy to hear his feedback on blog post presentation (step-by-step). This appreciation is a great motivation for me to learn more. Happy to hear positive feedback from Brent Ozar, SQL Server Expert and Expert blogger.


Happy Learning!!!

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!!!

Stored Procedure Testing using Nunit

In this post we will look at stored procedure testing using Nunit. We will be

• Reusing stored procedure created for SSRS report link
• Create Table Earnings and procedure SaleCity
• Earlier we looked into TST codeplex tool

This example is In continuation with previous Nunit post. Stored Proc test approach I learnt from book Expert SQL Server 2008 Development.

Step 1 - Created VS 2008 Class Library

Step 2 - Added Reference to  DLL nunit.framework.dll in the project

Step 3 - Copy below code in the Class1.cs file

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using NUnit.Framework;

namespace ClassLibrary1

{

    [TestFixture]

    public class Class1

    {

        [Test]

        public void TestSaleCity()

        {

            // Set up a command object

            SqlCommand comm = new SqlCommand();

            // Set up the connection

            comm.Connection = new SqlConnection(

            @"server=.\SQLExpress; trusted_connection=true; database=AppDB");

            // Specify Procedure Name

            comm.CommandText = "dbo.SaleCity";

            comm.CommandType = CommandType.StoredProcedure;

            comm.Parameters.AddWithValue("@City", "Chennai");

            // Create a DataSet for storing the results

            DataSet ds = new DataSet();

            // Define a DataAdapter to fill a DataSet

            SqlDataAdapter adapter = new SqlDataAdapter();

            adapter.SelectCommand = comm;

            try

            {

                // Fill the dataset

                adapter.Fill(ds);

            }

            catch

            {

                Assert.Fail("Exception occurred!");

            }

            // Validate Actual Results

            Assert.IsTrue(ds.Tables.Count == 1,

            "Result set count != 1");

            DataTable dt = ds.Tables[0];

            // There must be exactly four columns returned

            Assert.IsTrue(

            dt.Columns.Count == 4,

            "Column count != 4");

            // There must be columns City, SaleValue, SaleDate and Name

            Assert.IsTrue(

            dt.Columns.IndexOf("City") > -1,

            "Column City does not exist");

            Assert.IsTrue(

            dt.Columns.IndexOf("SaleValue") > -1,

            "Column SaleValue does not exist");

           

            Assert.IsTrue(

            dt.Columns.IndexOf("SaleDate") > -1,

            "Column SaleDate does not exist");

           

            Assert.IsTrue(

            dt.Columns.IndexOf("Name") > -1,

            "Column Name does not exist");

            // There must be more than one row returned

            Assert.IsTrue(

            dt.Rows.Count >= 1,

            "Result rows returned");

        }

    }

}

Step 4 - Build the Project

Step 5 - Using Nunit Open the Debug folder, You will see ClassLibrary1 DLL. Below is the snapshot of results

More Reads

Writing Test Cases for Database Queries and Stored Procedure using VS 2010Testing Times Ahead: Extending NUnit

Using SQL Data Generator with your Unit Tests

Testing framework (Nunit/Xunit/MbUnit/MsTest) attributes comparision

Setting up Continuous Integration with Cruise Control.Net
Running NUnit Tests in TFS 2010 Continuous Build Environment

Guide to building .NET projects using Hudson

CruiseControl + MsBuild + NUnit

Test Driven Design and levels of testing
Testing database and Test database

NUnitit - a Visual Studio Addin for NUnit

TDD : Introduction to Rhino Mocks 
TDD: Test-Driven Development with Visual Studio 2008 Unit Tests
TDD Tests are not Unit Tests

Happy Reading!! 

TSQL Tip of the Day

Today we will look at use of INTERSECT and EXCEPT.

• EXCEPT - Data exists in one table and does not exist in another table. In SET operations it is represented as A-B.
• INTERSECT - Data exists in both the table. Intersection in SET Operations.

Let's try an example. Step 1 - Creating Test Tables

 --STEP 1

 IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
 CREATE TABLE TestTable1
 (
 Number INT IDENTITY(1,1) PRIMARY KEY,
 A VARCHAR(20) NOT NULL,
 B VARCHAR(20) NOT NULL,
 C VARCHAR(20) NOT NULL,
 D VARCHAR(20) NOT NULL
 )
 GO
IF OBJECT_ID ('TestTable2') IS NOT NULL DROP TABLE TestTable2
 CREATE TABLE TestTable2
 (
 Number INT IDENTITY(10,5) PRIMARY KEY,
 A VARCHAR(20) NOT NULL,
 B VARCHAR(20) NOT NULL,
 C VARCHAR(20) NOT NULL,
 D VARCHAR(20) NOT NULL
 )

Step 2 - Populate Test Data
--STEP 2
--Insert Records into this table
 DECLARE @I INT
 SET @I = 1
 WHILE 1 = 1
 BEGIN
 SET @I = @I + 1
 INSERT INTO TestTable1(A,B,C,D)
 VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
 (CONVERT(CHAR (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))

 INSERT INTO TestTable2(A,B,C,D)

 VALUES ((CONVERT(CHAR(5),@I)+'A'),(CONVERT(CHAR(5),@I)+'B'),
 (CONVERT(CHAR (5),@I)+'C'),(CONVERT(CHAR(5),@I)+'D'))
  IF @I=1000
 BREAK;
 END

Step 3 - EXCEPT Example
--Number which is present in TestTable1 and not in TestTable2
SELECT Number
FROM dbo.TestTable1
EXCEPT
SELECT Number
FROM dbo.TestTable2

--Multiples of 5 excluded in results
--Alternate query

SELECT T1.Number
FROM dbo.TestTable1 T1
WHERE NOT EXISTS
(SELECT T2.Number
FROM dbo.TestTable2 T2 WHERE T2.Number = T1.Number)

Step 4 - INTERSECT Example 

--Number common in both TestTable1 and TestTable2
SELECT Number
FROM dbo.TestTable1
INTERSECT
SELECT Number
FROM dbo.TestTable2

--Alternate query
SELECT T1.Number
FROM dbo.TestTable1 T1
WHERE EXISTS
(SELECT T2.Number
FROM dbo.TestTable2 T2 WHERE T2.Number = T1.Number)

--Multiples of 5 listed in results

From MSDN - Reference

Happy Reading!!!