dbForge Team Blog

Devart Team is glad to announce the new release of SQL Complete v3.5. It includes support of all versions and editions of SQL Server, column picker, extended T-SQL syntax support and many other features.

In the new version, we’ve made a huge effort to improve usability of our Intellisense and paid a lot of attention to the application quality in general. Together with the new features, meet new look of the suggestion list and new level of the usability of our application!

New features

• Support of SQL Server 2012 and SQL Server Compact Edition As support for the new version of SQL Server was introduced together with full support for Compact Edition, developers got an opportunity to use the advanced SQL Intellisense functionality with any server version and edition, starting with SQL Server 2000 and up to SQL Azure.
• Column picker feature New functionality will make it possible to create SELECT and INSERT queries, as it allows defining column lists for selecting, grouping, sorting and inserting without typing any code at all. The suggestion list appearance was changed – now it supports hierarchical view for table columns and shows information about data type.
• Intellisense in SQLCMD mode Scripts that are executed with the help of the command line utility, are usually parameterized. But often it is the reason of the fact that ability to show correct suggestions for objects is lost, because database or schema name can be a variable. Now SQL Complete solves this problem. Values of variables are taken from the text of the document and object suggestions work as with an ordinary SQL script.
• Automatic formatting of keywords on typing Keywords are formatted according to the option settings. This will help to avoid appearance of keywords written in different registry, when part of them was inserted from the suggestion list and part was typed manually.
• Extended and revised SQL Snippets We’ve revised existing code templates, added new ones, and changed the behavior of the suggestion list on selecting a template. This will allow choosing the needed template as quickly as possible and reducing time needed for typing recurring code constructions. Also keep in mind that users can create their own SQL snippets with the help of the special editor.
• Extended T-SQL syntax support Context-sensitive prompt of objects in the MERGE statement and a number of SQL Server 2012 T-SQL construction is supported.
• SQL formatting improved Formatting of procedure and function code, CASE statements, comments, and column list inserted on wildcard expansion is improved.
• Express edition feature set extended The free Express edition of our product is also improved. Now it supports SQL Server 2012 and SQL Server Compact Edition. Besides, information on columns is expanded, and hierarchical list structure that simplifies working with SELECT and INSERT statements is also available in the free edition.

(more…)

Devart Team is glad to announce the new release of SQL Complete v3.1. The new version includes some improvements in both Standard and free Express editions to make your work easier, more effective and convenient.

SQL Complete SSMS add-in

New Features

• Linked servers support The new version of SQL Complete features support for IntelliSense when working with linked servers. Just imagine – now you can type your queries with the kind of IntelliSense you are accustomed to even when using all advantages of linked servers configuration!
• SQL statements support extended Standard edition provides support for GRANT, REVOKE, DENY, ENABLE/DISABLE TRIGGER, and ALTER SCHEMA statements.
• New Highlight Occurrences option The new option allows you to turn highlighting of occurrences on and off. Besides, all existing options were rearranged to improve product’s usability.

Improvements

• Usability of code completion improvement We revised rules for showing suggestion list especially when editing existing SQL queries. Now the suggestion list does not appear for arithmetic operations, numbers, and semicolons.
• Installation process customization We improved the product’s usability basing on users’ requests. Now you can select the development environment in which SQL Complete will function when installing the add-in – just select the needed environment from the list of available environments on a special page of the installation wizard.
• Tracing support We added tracing support for you to help us find and fix errors in our application – if you encountered any problems with SQL Complete, start tracing and send the created file to Devart afterwards.

Express Edition Extension

• SQL statements support extended Support for DROP, EXEC, DECLARE, and SET statements was added.
• Variables and parameters support
• Qualify Column Names option is available

Availability

Consumers can give the updated dbForge SQL Complete a test drive by downloading the 30-day trial Standard edition at the product download page. SQL Complete’s Standard edition is available for an estimated retail price of $99.95.

If you don’t need advanced code completion features but want to get more than SSMS IntelliSense can give, you are welcome to try free Express edition.

To leave feedback, users can go to the dbForge SQL Complete feedback page. The Devart team is looking forward to receiving any comments and suggestions.

When working on the new version of SQL Complete, we were focused on the idea of providing the most needed functionality to our users. Such course has already become the strategy of our product development. Our intention is not just to pack the product with features, but to give our users a tool that can make their work more effective. The result of this course was implementing more than a dozen of features and adding integration of SQL Complete with Visual Studio. We hope the integration will be useful for a wide range of prospective users.

Main New Features Highlights

New SQL Server versions support

SQL Server 2011 (codename Denali)
SQL Azure
SQL Server Compact edition

Added integration into Visual Studio

Visual Studio 2010: Professional, Premium, and Ultimate editions
Visual Studio 2008: Professional and Team System editions

“Go to definition” for database objects

This function allows navigating from code editor to the object in the Object Explorer (Server Explorer in VS) tree.

When the cursor is on the object identifier, the program will find the needed object and show it in the treeview on pressing F12 or clicking the corresponding item in the context menu.

If the object is declared in the script, the cursor will be moved to the beginning of its declaration.

Code Snippets

This feature makes it easy and convenient to use fragments of code over and over. The new functionality includes:

Showing code templates in the dropdown list
Opening templates on pressing Tab
Adding and editing templates with the help of templates manager
Possibility to add parameters to templates. Parameter values are entered by a user on inserting

New code completion features

IntelliSense for temporary tables and table variables
Expand EXEC statement feature. All parameters with value stoppers are inserted automatically on pressing Tab
sys.sp_* procedures are now suggested without schema prefix in EXEC statement
Expand ALTER statement for procedure, function, view, and trigger. Object body is inserted into the document automatically on pressing Tab
Suggestions for DROP statements added
Context-sensitive code completion for CREATE TRIGGER statement
Extended support for T-SQL DECLARE statement
Option for including default schema (dbo) into object name when pasting it into the text
Hierarchyid data type support in code completion

Document Outline window

This feature makes navigating through large SQL documents a real pleasure. The Document Outline window shows the structure of the current document. Besides, a user can synchronize structure with text right from the code.

Highlight occurrences of an identifier

This unique feature allows users to see all entries of an identifier in the document text. When the cursor is on the identifier, after a time lag all occurrences of this identifier into the text are highlighted.

SQL formatting component improved

Stream formatting for large documents. Application no longer hangs when formatting large SQL document. User can cancel the formatting process
Common table expression formatting supported
New formatting options for indenting JOIN clauses
Message for invalid statements

Quick database object information extended

Tool tip with parameters and descriptions of procedures and functions is shown
Tool tip with columns list when the mouse cursor hovers over the asterisk in the SELECT list is shown
For the alias in the FROM list, a table or expression with the list of columns is shown (if possible)
Now name, type, keys, and some other properties are shown for columns
Quick info is shown for the dropdown list items

“Execute current statement” feature

Now user can execute the current SQL statement (i.e. the one where the input cursor is located) in the document using the hot key.

Performance improvements

Formatting speed increased
Parsing speed of complex statements increased

What about SQL Complete Express?

Of course, most functionality is available only in the Standard edition of SQL Complete.

But all the same some improvements were made in the Express edition. There are the new features that were added:

Support for the new SQL Server versions
Visual Studio integration
SQL formatting component improvements
“Execute current statement” feature
Performance improvements

Availability

Consumers can give the updated dbForge SQL Complete a test drive by downloading the free Express edition or the 30-day trial Standard edition at the product download page. SQL Complete’s Standard edition is available for an estimated retail price of $49.95.

To leave feedback, users can go to the dbForge SQL Complete feedback page. The Devart team is looking forward to receiving any comments and suggestions.

In the previous article we told that we’ve performed a lot of work concerning optimization of parsing queries in SQL documents. Also we informed that we’ve fixed all problems that were found by users of our intellisense… But recently one user sent us quite a small script that caused freezing of SSMS by Devart SQL Complete. Here it is:
(more…)

Recently we have released a free SQL Complete tool that provides IntelliSense and script layout for SQL Server. If you like IntelliSense in Visual Studio and write T-SQL, you’ll probably appreciate the productivity improvements with SQL Complete.
(more…)

In some cases, for example, when migrating or updating objects, it’s necessary to update metadata for views created without evident listing of columns in the select-list, i. e. for views of the SELECT * FROM some_table type. The built-in sp_refreshview procedure is developed for these goals, it updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Metadata of other objects containing the code can be changed using the built-in sp_refreshsqlmodule procedure, that is designed to update metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger or database-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.

So using the script specified below, the updates of all objects containing the code for the current database can be performed (i.e. update metadata information for all VIEWS, DML TRIGGERS, PROCEDURES, FUNCTIONS, DDL TRIGGERS):

(more…)

To get information from a database it is necessary to execute a query to get this data.

Usually an ordinary SQL editor is used to create queries. To use such editor, one should remember the syntax of the SELECT operator and the names of tables and columns.

Let’s use a visual instrument developed specially to design queries, and see that it’s much easier to create queries visually instead of typing them in an editor.

Task:

It’s necessary to show the salaries of the employees of departments situated in different cities for the 2008 year in descending order.

(more…)

Database development is not limited to an initial copy of a database. On your road you meet multiple enhancements, refactoring, upgrade and many other cases that require changes in a database structure. One of most common cases is to upload database objects from one schema to several schemas. Here the simplicity ends and you are stuck with the questing ‘How to do this?’

Upload manually or automatically?

(more…)

Devart development team is glad to present dbForge Query Builder for SQL Server, a modern and affordable tool that will bring you confidence in creating queries of any complexity. With dbForge Query Builder, Devart continues its initiative to produce efficient database experiences for all the people in SQL Server world.

dbForge Query Builder for SQL Server

Designed specifically for people who are working with databases, dbForge Query Builder blends rich functionality and a simple user interface to provide:

(more…)

We compared some best-of-breed data comparison tools with dbForge Data Compare 2.00 to find out which one is speedier to compare and synchronize data in SQL Server databases.

The participant tools were tested with default settings on live databases on SQL Server 2008 that installed with default settings on a desktop (with a dual-core processor and 2Gb of RAM). Both the SQL Server and the tools were installed on the same desktop.

Live Databases:
Two databases with total size of 1Gb: 115 tables containing one with 3 million records of numeric and string data types, the other with middle size BLOB data.

Task №1: We measured the speed of each tool while comparing the data, generating an update script, and at last synchronizing the databases.

Data Comparison/Synchronization Performance

Result №1: Tests results show that dbForge Data Compare for SQL Server v2.00 has leaved the competitors far behind.

Task №2: We decided to compare the performance of the selected participant tools based on the main industry-leading competitor’ performance. Besides we took into account the product price and tried to calculate the efficiency as best performance for less money.

Performance/Price

Result №2: This graph shows that not all the tools can be proud of delivering an expected combination of best performance and best price. dbForge Data Compare handles this task well.

Check Shot

To fully complete our research, we decided to go the whole hog and use the aforementioned tools to compare large databases with the size of 120Gb (some tables in these databases have 2.4 billion records, some tables contain 1.5Gb of LOB data, the FILESTREAM table is with 5.3Gb of records). dbForge Data Compare was good at comparison, while neither of the competitive tools compared tables with LOB data and records in the FILESTREAM table. When we excluded such tables, only one tool coped with 2.4 billion records.
Speed rate of dbForge Data Compare is several times higher than other popular competitive tools have.

Try dbForge Data Compare on your database. Delivering qualitative data comparison and synchronization is our fame.

We guarantee quick support and product improvement if dbForge Data Compare fails to compare and synchronize your database. We even may give you the product license for free.