T-SQL: Negative Effects of SSMS Query Designer
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 the 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 the 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 on 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 an INNER JOIN. This issue can occur because we have to write where conditions when using the 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 the query designer to change our query. We can write a query or have 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.