SQL Prompt is the SQL IntelliSense and code-formatting tool, and is an asset in developer flexibility and output. It will both increase personal coding efficiency and improve team-based development practices, leading to advanced quality, more consistent and more consistent code.
Why Developers need SQL Prompt
Improved coding productivity (code suggestions and completion)
The most immediate benefit the development team will likely notice, from use of SQL Prompt IntelliSense and code completion, is an increase in raw coding speed, but it will also increase a developer’s syntactical understanding of the code, and reduce basic syntax errors.
SQL Prompt leads developers interactively through the syntax of SQL commands and queries, as you type them. It suggests valid entries at each stage. It will auto-complete the SELECT clause with schema-qualified table names, suggest valid JOIN conditions, help fill in the WHERE and GROUP BY clauses, and more.
One simple side effect of this is that your team will produce correct SQL, quicker. In our unscientific speed challenge, between Steve Jones and Grant Fritchey, two MVPs of roughly equal SQL skills, SQL Prompt made typing in a known query twice as fast.
It can also help developers be productive very early on in the development cycle. Very few developers claim SQL as their primary language, so before they even start writing code, they will often spend development time sifting through Microsoft documentation, navigating Backus-Naur Form (BNF) diagrams to work out the correct syntax for a command, or reverse engineering it from code examples in books, or online. Working with SQL Prompt is like using an interactive SQL version of a decision tree, or railroad diagram. It is the easiest and fastest way to navigate through a complex SQL command and, with it, developers will quickly gain a syntactical understanding of the SQL, while producing working code.
Readable, consistently-formatted code (Prompt formatting styles)
Developers tell us that they can cut their SQL development time by 25%, just from the ability to auto-style code using SQL Prompt.Formatting is all about making sure code is easy to read and understand. SQL Prompt’s formatting styles make it easy to define and implement a standard SQL style across your development team, while still affording each developer the flexibility of applying different styles, locally, for different uses. This both reduces time spent styling code, and makes code much easier to share and review among the team.
SQL code formatting
Code formatting, or style, changes are ‘decorative’ changes that affect only white-space, such as line breaks, tabs and spaces; they do not change the meaning of that code in any way. If we remove all formatting from a piece of code, it will continue to function exactly as before.When a developer spends many hours, each week, staring at SQL code, he or she gets to like it laid out to a style that is natural for them to read and understand. If done manually, the developer spends time changing the case of object names, hitting the return key to insert line breaks, then banging the space bar to get all the clauses lined up, before the real work even starts.
SQL Prompt reduces code formatting time to an absolute minimum. On their local workstation, a developer can apply a personalized formatting style for developing code, perhaps a different style to make comparing code easier, and so on. Having defined the styles, switching between them is fast and easy; a couple of mouse clicks will set the required style as the Active Style, then a few keyboard strokes (Ctrl K, Ctrl Y) will apply it to all code, or selected code, within an SSMS query window.
When a developer is ready to commit the code to version control, or needs to ask another team member to review the code, simply select and apply the “team standard” style first, to ensure that the team always gets to review consistently-formatted code. The shared team style is treated like any other asset of the development project. It is stored in version control, and everyone in the team can access the latest version.
Standardizing the T-SQL code formatting across a team, when applied alongside standardized naming conventions, also offers time-saving and productivity gains in many other ways:
More efficient code reviews – it is much easier for any team member to review another’s code with minimal confusion, and edit code without introducing inconsistencies and errors.
Faster troubleshooting of T-SQL scripts – it is much easier to compare different two versions of the same script, or block of code, and work out what change caused a problem.
Smoother joint development efforts – a common standard will facilitate handing off code to other teams, such as Operations, for security, compliance and performance review
Better database design and code quality (Prompt refactoring)
SQL Prompt provides code refactoring actions that can be applied from directly within an SSMS query window, to help eliminate patterns that can make SQL code code error prone, harder for others to read and understand, and hard to edit without making mistakes.
A SQL Prompt action can be applied to any highlighted text in a SSMS query window. Typical actions include:
Qualify object names – failure to qualify the owner of an object forces the engine to check for the object in two places, first in the authenticated user’s default schema and then in the dbo schema. Also, SQL Server can fail to reuse an execution plan if, for example, a query is executed first with and again then without object qualification.
Wildcard expansion – SELECT * queries may be a convenience during development, but are the cause of performance problems in production code.
Remove square brackets – often used in code where they are not required, often due to poor object naming conventions
It also provides full database code refactoring processes that will help eradicate database design mistakes as early as possible in the development cycle, before they become very expensive to fix. For example, its Smart Rename refactoring will rename objects and detect any dependent objects affected by the change. It also provides a Table Split refactoring to help correct database design mistakes, with minimal pain.
Effective reuse of standardized code (Prompt snippets)
Your team can use SQL Prompt to create and customize snippets for frequently used code, to save time writing SQL, standardize common code structures and modules, reduce the risk of syntax errors, and make code easier to analyse and test.
Prompt snippets perform a similar function to SSMS templates, but are easier to use and adapt since they provide replacement points for various parameters. You can insert a snippet into a query window, at the cursor position, just by right-clicking and locating the required snippet. You can create a snippet from any highlighted test in an SSMS query window
SQL Prompt offers many built-in snippets, but you easily create your own custom snippets from any pre-defined block of code. You can store your custom snippets in a central repository, such as GitHub, where they can be accessed by all members of the team.
You can use snippets to pre-define the basic structure for a commonly-required block of code, such as to build a table, or a T-SQL statement that creates the common types of database objects. You can use them to store customizable scripts for testing recording code execution times, or for troubleshooting slow running queries.
Reuse among the team of easily-customizable code snippets offers some obvious productivity benefits, including time saved by each developer, from not having to dig a script out of his or her archive, or because from reusing another team member’s snippet rather than write code from scratch.
Also, by using snippets, the team can introduce standards and consistency to the way they build modules, for example in terms of structure of any headers, inclusion of guard clauses, and so on. This will make code safer to execute, easier to understand and faster to test.
Standardized snippets for testing code, or investigating slow-running code, will mean the results will always be provided in a standard format, which makes for much easier team collaboration during troubleshooting.
Higher SQL coding standards (Prompt static code analysis)
SQL Prompt’s built-in static code analysis rules allow teams to check for the problems automatically, during development and testing work, therefore minimizing the number of ‘code smells’ that creep into the application and database builds, and improving build reliability.
Static code analysis parses the source code, checking the syntax for compliance with a built-in set of rules. These rules are designed to encourage good coding practices, and to alert the team to possible mistakes, omissions and vulnerabilities in the code base. These ‘code smells’ could include problems with table design, such as a missing clustered index, or naming problems, such as use of reserved words, or problems with query logic that could cause unpredictable performance or behavior, or even cause security problems.
If these database ‘code smells’ are detected only at deployment time, when a DBA reviews the code manually, it causes inevitable delays in the database deployment and delivery processes, and is a major concern for organizations striving to move towards more frequent delivery cycles.
With SQL Prompt’s static code analysis, teams can detect, and resolve, many of these issues automatically, early in the development cycle, increasing the reliability of database builds, and reducing the delays between build and deployment.
The broader benefit to the DevOps team is a reduction in maintenance costs, often reported as accounting for between 60-90% of the total application lifecycle costs. For the database, this means significantly less time spent diagnosing erratic or slow performance, or resource contention issues, such as blocking, or fixing data integrity issues, or security problems.
No more lost work (Prompt tab management and execution warnings)
SQL Prompt includes several ‘safety net’ features that can reduce human error that leads to business-critical mistakes, such as data loss, or the need to perform data recovery operations, and minimize accidental code loss, and therefore many hours spent rewriting.
Mistakes happen. Sometimes SSMS crashes. Sometimes you accidentally close a code tab without saving it, before realizing it contained an essential bit of code. Sometimes, you’re working late, switching between test and development servers, and accidentally execute a modification statement against the wrong server.
With SQL Prompt installed into SSMS, the development team benefit from several safeguards to ensure these mistakes are minimized, or completely avoided.
With Tab History developers can recover lost code, whether they saved it or not, with a handy search function.
With Tab Coloring the team will always know to which SQL Server instance a query session is connected environment they are working in. Make your Production and Test servers different colors to ensure your developers are clear where they are making changes.
Code execution warnings – provide a pop-up alert if you’re about to execute a statement that may cause problems, such as a DELETE with a missing WHERE clause
SQL Prompt automates the retrieval of database object names, syntax, and snippets as you write, intelligently offering only appropriate code choices. Automatic script layout provides easy code readability – particularly useful when working with unfamiliar scripts. SQL Prompt is highly productive straight out of the box. It is also customizable so you can make it perform exactly the way you want it to. Using SQL Prompt will improve your productivity and dramatically reduce your time at the keyboard.
- Write SQL fast and accurately with code completion
- Understand code more easily with script layout
- Continue to use your current editor – SQL Prompt works within SSMS, Query Analyzer, and Visual Studio
- Keyword formatting, join completion, code snippets, and many more powerful features
SQL Prompt simply sits behind the scenes and provides unobtrusive help when you press CTRL+SPACEBAR or when you type “.” after a table/view/alias name.
FEATURES OF SQL PROMPT
Major features provided by SQL Prompt are:
1)IntelliSense-style code completion
2)Customizable code formatting
3)Code snippet library
4)Refactor SQL code
5)SSMS tab history
6) SSMS tab coloring
IntelliSense and Formatting
This feature is a huge enhancement over the out of the box experience you get with SSMS and VS. As you can see in the figure below, there is sufficiently more information present in the SQL Prompt pop ups.
Suggestions work properly and we can filter that behavior using the drop down list at the bottom of the first popup in the figure above. Placing the cursor at the end of the * allows you to hit tab and expand the column list as seen below:
From there we can easily edit the column list and save tons of time when dealing with tables that have a large number of columns often used in queries. While we’re working with this query, I also want to show you how easily you can change the format. Just highlight the SQL and right click to make your query look great!
Properly formatted SQL is easier to maintain, and SQL Prompt allows you to have a lot of control over how you prefer the formatting to work.
SSMS has supported templates for several versions that allow you to reuse boilerplate scripts to easily create objects in your databases. SQL Prompt takes this concept further by implementing the code snippet library. There are many snippets already built into SQL Prompt and activating a snippet is as easy as typing the snippet definition like “st100” to select the top 100 rows from a table as shown below:
It’s easy to highlight an existing query and turn it into a snippet using placeholders. Built in placeholders are reserved words that are variables for specific pieces of information or they represent actions that can be applied to your snippet. To get a feel for how they are structured, simply open up the snippet manager and look over the definitions of existing snippets. You also have the ability to create custom placeholders which are basically free text fields. Alasdair Daw has a wonderful article here that examines the subtleties in this great feature: http://www.red-gate.com/blog/snippet-manager
The ability to refactor code is one of the most valuable features of any toolset. I often find myself working on large legacy systems that employ poor, or in most cases, no naming conventions or consistent coding standards. I mentioned earlier when showing off the formatting features of SQL Prompt that well formatted code is easier to maintain thus leading to saving time and ultimately money.
SQL prompt has many amazing refactoring features starting with Smart Rename which allows you to right click and rename objects without breaking dependencies. This makes it easier to apply a naming standard to existing tables, views, stored procedures and functions along with their respective column names and parameters. You can also rename scripted variables.
Tab history (SQL 2008 or later) is a nice feature that stores the list of tabs you’ve opened and allows you to easily get back to them along with the ability to rename or delete them from the list. This may seem simple, but as I get older I sometimes forget where I parked at the mall so it is helpful for me to have that historical list handy.
SQL Prompt features has the new addition of colored tabs (SQL 2012 or later). This allows you to select different colors for tabs to represent different servers or database connections as shown in the figure below:
This feature is a godsend when you have to work in different environments and need to stop the madness of running a script in the wrong environment. This is another simple feature that eliminates confusion with support for coloring at the database and server group levels and support for wildcard matches.
Integration With Visual Studio
Just about all the great features we’ve looked at and more are also available in VS. As you can see from the figure below, SQL Prompt adds a menu entry in the VS development environment just like we have in SSMS as well as additional entries when you access a popup menu:
Major Features of sql prompt
- Table/View/Column/Sproc name completion
- Layout makes scripts easy to read
- JOIN/JOIN ON syntax completion
- Expand wildcard to column names list
- Object creation-SQL viewing pane
- Mouse-over tool tips that give object definitions including a useful summary
- Parameter hinting for user-defined functions
- Cross-database queries
- Distributed queries with linked SQL Server instances
- Scripted objects included in candidate list
- Cursors, cursor variables, and related statements
- Auto-uppercasing of keywords and pop-up after keywords
- Column selector screen, plus qualify object/column names option
Installation Instructions For Windows
A) Click the Windows “Start” button and select “All Programs” and then point to sql prompt
B) RDP Connection: To connect to the operating system,
1) Connect to virtual machine using following RDP credentials :
- Hostname: PublicDNS / IP of machine
- Port : 3389
Username: To connect to the operating system, use RDP and the username is Administrator.
Password : Please Click here to know how to get password .
C) Other Information:
1.Default installation path: will be on your root folder “C:\Program Files (x86)\PoorMansTSqlFormatterSSMSAddIn\”
- Windows Machines: RDP Port – 3389
- Http: 80
- Https: 443
Configure custom inbound and outbound rules using this link
Installation Step by Step Screenshots