Introduction


Microsoft Query Designer is a visual tool which exists in SQL Server Management Studio(SSMS) that makes it easy to write T-SQL queries. It is visual and handy. But using this tool has its negative effects. In this short writing, we see some of these effects.


Illegibility

Although query designer rapidly produces T-SQL code, the produced code is not readable enough. We can see the next query which is generated using query designer in just seconds. But it is not readable to edit later. If we want to use it for rapid development, it would be better to use an extra minute to add aliases and format our code. This minute helps next developer when testing or editing it.

SELECT        Production.TransactionHistory.TransactionID, Production.TransactionHistory.ReferenceOrderID, Production.TransactionHistory.ReferenceOrderLineID, Production.TransactionHistory.TransactionDate,
                         Production.TransactionHistory.ModifiedDate, Production.Product.Name, Production.Product.SafetyStockLevel, Production.Product.SizeUnitMeasureCode, Sales.SalesOrderDetail.SpecialOfferID,
                         Sales.SalesOrderHeader.PurchaseOrderNumber, Sales.SalesOrderHeader.SalesPersonID, Sales.SalesOrderDetail.LineTotal, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderHeader.DueDate,
                         Sales.SalesOrderHeader.OrderDate
FROM            Production.TransactionHistory INNER JOIN
                         Production.Product ON Production.TransactionHistory.ProductID = Production.Product.ProductID INNER JOIN
                         Sales.SalesOrderDetail ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID INNER JOIN
                         Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID AND Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
WHERE        (Sales.SalesOrderHeader.OrderDate > GETDATE() - 30)


Wrong JOIN Condition Suggestions

This is another common issue when using query designer or other tools that make code for us. Query designer join condition suggestions are based on foreign keys. In many situations, this is helpful. But there are as many as those situations that it's the wrong one. To avoid this issue we have to check each join conditions when adding a table or view.

Wrong JOIN Order

There are many possibilities that can occur when using this tool. We were faced with an old query that was built with query designer. We saw that the join order was wrong. This can lead to poor execution plans. You can find more information in this great article. So, it needs a bit of care when adding extra tables in the correct order.

WHERE Conditions in JOIN

Another issue is a rare one. This usually occurs when using INNER JOIN. This issue can occur because we have to write where conditions when using query designer, but putting JOIN conditions is simpler. We know that conditions are interchangeable between INNER JOIN and WHERE clause. This is true about the output result. But, there are some situations that moving a condition from INNER JOIN to WHERE clause can boost performance. They are two separate phases; so, it needs a little attention to this point. If we put one bad condition in INNER JOIN, there is a possibility of having a bad execution plan that increases I/O.

Unwelcome Changes

There are some scenarios that can lead to undesired changes when using query designer to change our query. We can write a query or having one query that was not produced by this tool. Then select the query and put it in query designer by shortcut key or using right click menu. After doing this, we have to check the query for unwelcome automatic changes! Assuming we have the next code:

SELECT  BusinessEntityID ,
        DepartmentID ,
        ShiftID ,
        StartDate ,
        EndDate ,
        ModifiedDate
FROM    HumanResources.EmployeeDepartmentHistory
WHERE   EndDate IS NOT NULL
        AND ( ShiftID = 1
              OR DepartmentID IN ( 4, 5 ) )

SELECT        BusinessEntityID, DepartmentID, ShiftID, StartDate, EndDate, ModifiedDate
FROM            HumanResources.EmployeeDepartmentHistory
WHERE        (EndDate IS NOT NULL) AND (ShiftID = 1) OR
                         (EndDate IS NOT NULL) AND (DepartmentID IN (4, 5))

In the above code, if we pay attention to WHERE conditions, it is obvious that the condition was changed. Although, it is clear that the whole condition's functionality remained. But when dealing with large queries with more conditions, this behavior can be problematic.


Conclusion

Query designer is a great tool that helps us in rapidly writing our queries when developing our software applications. But knowing its negative impacts help us to avoid falling into the maintenance issues.




See Also