The skills of writing different types of SQL Server queries require you to have good knowledge in the SQL Server T-SQL language. T-SQL stands for Transact Structure Query Language, which is a database procedural programming language that is extending the SQL language for Microsoft SQL Server RDBMS product.
The best and direct way of learning the T-SQL language is installing the sample SQL Server databases and use the Microsoft T-SQL Reference to learn the language components and commands. In addition, being an experienced SQL Server developer requires you to be familiar with the SQL Server development tools, such as the graphical user interface tool: SQL Server Management Studio and the command line tool: sqlcmd utility.
Assume that you need to create a new table in an existing database. The first step is to visit the Microsoft Online Help for the CREATE TABLE T-SQL statement. After that, you should design a draft for your own CREATE TABLE T-SQL statement depends on the columns data types, properties and tables relations. Once the table draft is ready, open the SQL Server Management Studio, connect to your SQL Server instance, browse your database and click on the 
button or simply right-click on the database and choose New Query.
In the opened New Query window, write the CREATE TABLE T-SQL statement, that will be used to create your new table, as shown below:
Before executing the command, click on the 
parse button to make sure that there is no syntax error on the provided query. Once parsed, click on the 
, and the table will be created successfully.
If there is no SQL Server Management Studio installation on your machine, you still have a chance to execute the same CREATE TABLE T-SQL command using sqlcmd utility. The sqlcmd utility allows you to execute your T-SQL statement, stored procedure or script file that contains multiple commands. You need to open the CMD command prompt window, write the sqlcmd command to connect to your SQL Server instance, or simply writing sqlcmd as in our case if connecting to a local instance with the logged in credentials, as shown below:
After connecting to the SQL Server instance, write the same CREATE TABLE T-SQL command, line by line, or as one line, then write GO to execute the provided batch of commands, and the table will be created as shown below:
To insert data into the created table, you need to visit the Microsoft Online Help for the INSERT T-SQL statement, prepare a draft for an INSERT INTO T-SQL statement to fill the table for your data then write and execute the statement in a New Query window on SQL Server Management Studio, as shown below:
Or copy the same command and run it in CMD using sqlcmd utility, followed by GO command, and the record will be inserted successfully, as below:
To modify an existing record, review the Microsoft Online Help for the UPDATE T-SQL statement, prepare a draft for the changes that will be performed on your record then write and execute the query using a New Query window within the SQL Server Management Studio, as shown below:
Or using the sqlcmd utility, followed by the GO command, and the record will be updated as shown below:
Deleting an existing record also can be performed using the DELETE T-SQL command, that should be handled with care if the WHERE statement is incorrect or not included, as in the SQL Server Management Studio example below:
The same command can be used within the sqlcmd utility, as in the example below:
To retrieve the data stored in a specific table, or number of tables, visit the Microsoft Online Help for the SELECT T-SQL command and use it to draw your statement, as in the SQL Server Management Studio example below:
That can be also viewed using the sqlcmd utility in catastrophic way, as shown below:
The situation will be more complex, and requires deeper knowledge in the T-SQL language, when retrieving data from number of tables, using the JOIN clause, that connects more than one table depends on a common criteria, as in the SQL Server Management Studio example below:
Using dbForge Query Builder for SQL Server tool
In this speeding world, the time is a very important factor in all businesses and costs money! For the SQL Server administrators and developers with large number of requirements to be accomplished in short period, the market provides number of third-part tools that help in building complex T-SQL queries in no-time. dbForge Query Builder for SQL Server considered as one of the most useful, smart and easy-to-use tools for designing complex DML T-SQL queries visually and interactively without the need to write any code manually.
The dbForge Query Builder for SQL Server tool can be easily downloaded from the dbForge Download page and installed on your server or machine, via an interactive installation Wizard, by clicking on the INSTALL button, as shown below:
After that, the installation wizard will ask you to specify the installation path of the tool, weather to create a shortcut on the Desktop, the extension of the files to be associated with this application and the startup optimization option. During the installation process, an indicative progress bar will show you the current DML that is processed at that moment. And finally, the Query Builder tool will be installed on your machine and ready for use, as below:
If you are familiar with the SQL Server Management Studio, the dbForge Query Builder will be your friend! You can connect to a local or remote SQL Server instance by providing the instance name and the credentials, as shown below:
To design a query using the dbForge Query Builder, no need to be a T-SQL expert. You can use 
, option to open a new window and write a query, without the need to remember all names and complex rules, with the interactive autocomplete feature that allows you to select the required data from the lists and enjoy results, as follows:
After writing your query, just right-click on the white space beside the query and choose Execute Current Statement or Execute Selection in case of multiple commands, as shown in the snapshot below:
For DML changes, you can simply choose the 
option to design a DML query visually. If you choose New Query option, a query design window will be displayed with the ability to choose the DML operation to be performed, as shown below:
Assume that you need to design an INSERT statement into the previously created table, but you are not familiar with the Syntax for that statement, or the design of the table. Just drag the table to be filled with the data and drop it in the design workplace, check beside the columns you are interested in and fill with the values for each Column, or tick to use Default value for this column, as shown below:
dbForge Query Builder tool allows you also to insert the values of the columns in the shape of sub-query, as below:
And a new tabbed window will be displayed to edit the sub-query in a separate window and easily access the main query, as follows:
To view the generated INSERT T-SQL statement, click on the 
button, at the left down side of the window, as follows:
You have the option to save the INSERT query, copy it to execute it later on another environment, or execute the generated INSERT statement directly using the Query Builder tool, by right-clicking on the white space beside the query and choose Execute Current Statement option:
And the query will be executed with a meaningful output messages in the Output box, as shown below:
Assume that you need to modify the table’s data using the UPDATE statement. Click on New Query option and change the type of the DML operation to UPDATE. To design the UPDATE statement, drag the table to be modified and drop it into the design workplace. After that, choose the column(s) to be modified and fill with the new column value in the Update tab, as shown below:
In the Where tab, draw the condition that will be used to filter the rows to be modified, by easily specifying the column(s) participating in the WHERE clause and the value or range of values for each column to be filtered as below:
After designing the Update query, the generated query can be viewed by clicking on the Text button, at the left down side of the window, as follows:
If you plan to delete data from your table, the Query Builder tool will help you to visually design the DELETE query. Change the type of the DML query to DELETE, drag the table to be purged, drop it into the design workplace and design the filter of the data to be deleted in the Where tab, as shown below:
Once the DELETE statement is designed, the generated query can be viewed by clicking on the Text button, at the left down side of the window, from where you can save or execute directly, as follows:
The dbForge Query Builder tool allows you to be a creative designer for the SELECT queries, by the various options available when changing the DML type to SELECT. Drag the source table, drop it into the design workplace and check the columns to be retrieved from the table, as below:
And in the Where tab, specify the condition that will be used to filter the retrieved data, the columns that will participate in the Group By or Having clauses and the sort criteria of the columns in the Order By clause if needed as below:
And the generated query can be viewed by clicking on the Text button, at the left down side of the window, from where you can save or execute directly, as follows:
If you choose to run the SELECT query in the Query Builder, it will provide you with two options, open the result set in a read-only grid, or open it in an editable grid, upon your choice as below:
If you choose to open it as editable grid, it will allow you to modify the data directly and replicate the change to the source table, as shown below:
Writing complex SELECT queries to retrieve data from multiple tables using the JOIN clause is a nightmare for most of the T-SQL beginners, that requires good knowledge in the tables designs, relations and the JOIN clause. This is not the case with the dbForge Query Builder. You need only to drag the source tables, drop it to the design workplace, that will be dropped with its relations automatically, and choose the list of columns to be retrieved from the tables, as shown below:
If you click on the Joins tab, you will see that the Join graph is created automatically, as follows:
And the complete generated query will be viewed by clicking on the Text button, at the left down side of the window, from where you can save or execute directly, as follows:
dbForge Query Builder tool provides you also with the ability to execute a large script file that contains multiple or large number of commands. Click on the 
button to open the Execute Script wizard, specify the connection parameters, the location of the script file and the code page for the script file, as below:
After specifying all required information, click on the 
button and the script execution will start. Once the execution is completed successfully, a report with the error messages, if any, will be displayed as below:
The dbForge Query Builder tool is not only a query designer tool. With this tool, you can enjoy the customizable visual export feature of your data to 10 widespread formats: HTML, Text, MS Excel, MS Access, RTF, PDF, XML, CSV, DBF, ODBC. Just click on the 
button and the Data Export wizard will be displayed. In the Export Format page, choose the format of the file to which the data will be exported then click Next as below:
In the Source page, specify the Server, database and table(s) to be exported then click Next, as follows:
In the Data Formats page, specify the list of columns to be exported then click Next, as shown below:
Specify the location and properties of the output file in the Output Settings page, then click Next as below:
In the Exported Rows page, specify to export all the table rows or a range of rows, as shown below:
Once all the export settings are configured, click on the 
button and the export process will start directly. When the export operation completed successfully, you have the option to open the generated file, open the folder that contains the exported file or start a new export operation, as below:
The dbForge Query Builder is a great tool for both the SQL Server administrators and developers to perform many tasks in short time. It deserves downloading a trial for now and see how it will make your life easy!
Useful Links:
- SQL samples https://docs.microsoft.com/en-us/sql/samples/sql-samples-where-are?view=sql-server-2017
- Transact-SQL Reference (Database Engine) https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-2017
- SQL Server Management Studio (SSMS) https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms?view=sql-server-2017
- sqlcmd Utility https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017
- dbForge Query Builder for SQL Server https://www.devart.com/dbforge/sql/querybuilder/
Useful tool:
dbForge Query Builder for SQL Server – allows users to build quickly and easily complex SQL queries via an intuitive visual interface without manual code writing.
