As a developer, it is important to have a solid understanding of T-SQL when working with SQL Server. While the Design feature in SQL Server Management Studio (SSMS) may seem convenient, there are several reasons why using T-SQL is a better choice.
One of the main issues with using the Design mode in SSMS is the potential for added overhead and database locks. In previous versions of SSMS, even a small change could cause the entire application to hang and become unresponsive. This is because when certain operations, such as adding a column or changing the datatype of a column, are performed in Design mode, SSMS has to perform additional tasks behind the scenes.
For example, when adding a new column to an existing table, SSMS renames the table to a temporary table, creates a new table with the new column specification, and then moves the data from the old table to the new table. This process is resource-intensive and can put locks on SQL Server tables, causing SSMS to hang.
However, SQL Server 2008 SSMS introduced a new feature to address this issue. It now prevents users from running operations that require the above-mentioned process. When attempting to save changes that would trigger this process, SSMS displays a warning message and prevents the operation from proceeding.
While this feature is helpful in preventing SSMS from hanging, it can also be inconvenient in certain situations where saving a table using SSMS is necessary. In such cases, the feature can be disabled by going to Menu >> Tools >> Options >> Designers and unchecking the “Prevent Saving changes that require table re-creation” option.
It is important to note that this restriction only applies to operations attempted using SSMS’s Design mode. If modifications to tables are made using T-SQL in the Query Editor, they will work without any issues.
Overall, having a strong knowledge of T-SQL is beneficial for developers working with SQL Server. It not only helps avoid potential performance issues and database locks but also provides more control and flexibility in managing database operations.