DEV Community: Alejandro Cobar

Increasing database development productivity with SQL Complete (Part 2)

Alejandro Cobar — Wed, 23 Nov 2022 17:01:18 +0000

Moving forward with the series, in this article, I will continue with the coverage of the SQL IntelliSense tool to boost DB Devs’ productivity. If you missed Part 1 and want to catch up before diving into this one, follow this link and come back once you’re done.

Ways to export the data
While working with different result sets, how often have you needed to move/export the data to someplace else, regardless of the reason? I have had that need way too many times in the past, and SQL Server Management Studio lets you export the data as a .csv file right out of the box. However, when you super-charge SSMS with the SQL IntelliSense tool, many context menus will now have myriad additional functionalities, so let’s look at what the tool can do for us in data exporting.

When you right-click in the result set grid, you will see the following additional options:

Below XML, there are also HTML and JSON options (yes, HTML). It’s just that those didn’t cut while I was taking the screenshot.

Generate Script As
This one is very valuable to me because it can serve many purposes (depending on your particular needs, of course). As the name implies, it will generate a script based on the option that you pick, so let’s explore the very first one, which is “INSERT”:

For simplicity, I will choose the “To New Query Editor Window” option, and this is what I get:

I guess there’s no need to explain the script, but I’d like to mention why I see this as a big feature regardless. To make it easier for the reader’s eye, let me break it down into different points:

Backup of an individual table: sometimes, when you are about to apply a data modification query, you’d like to make sure that there’s a backup of the data somewhere so that you can revert to its original state if necessary (assuming you’re past the respective commit of course). But guess what? The native backup solution from Microsoft doesn’t let you either backup or restore individual tables (and 3rd party solutions are not always an option due to highly-constrained budgets), and the other native alternatives imply way too many steps, keeping you out of the productive side of the court, unfortunately.
However, with this solution, you can script the entire table with a couple of clicks and have it sitting there in case you need it. Or you can immediately restore it someplace else and have a backup of your result set for immediate access.
Backup of an individual table with its table definition: there’s a tiny catch with the previous point. You don’t have a table definition to swiftly put the data somewhere else and call it a day. However, I’ve found a neat workaround that can also be very practical: the “INSERT #tmpTable” option.

You just have to remove the “#” symbol, and you’re good to go! Remember that doing this will not carry over any indexes, constraints, triggers, etc., but it is still very useful to move the data around easily.

Copy Data As
Moving to the next category, let’s explore the options that the tool has to offer:

Copy Column Names Only
This one is pretty straightforward. From the result set in the result grid, select all or a few columns and hit “Copy Column Names Only.” After that, you can paste the column names anywhere, and you’re ready. Sometimes, this is quite handy when you want to quickly identify the header of a report, in case you just copied the data without it.

Copy Column Names and Types
Very similar to the previous one, except that it adds the data type of each column to the mix. Perhaps you are thinking of creating an empty temp table or temp variable to work in an intermediate step of your logic, results in a very handy option to get yourself going.

The real beauty of this feature shines with queries where you join two or more tables instead of going through the tables within the Object Explorer to know any column data type that you’re curious about. You can simply right-click the column(s) you want and you’re set!

Moving forward with the rest of the options, even though it is quite obvious that it is an export of the data in CSV, XML, HTML, and JSON formats, let’s see how easy it is to do so.

*All four options offer an export “To File…” and “To Clipboard.”

CSV

XML

HTML

JSON

I think it’s pretty convenient to have these export formats options right at the tip of your finger, so that you can choose the one that fits your particular needs at any given time.

Important Note

I would like to remark that the data that will be exported will be exactly what you select in the result grid, so make sure to select everything if that’s what you want.

After seeing all the options we get from the result grid, let’s look at what options we get if we right-click any table from Object Explorer.

As you can see, we have one option called “Script Table as CRUD”, so let’s see what it does.

Interestingly enough, it will generate for us the TSQL code to create 4 Stored Procedures: one for SELECT, one for INSERT, one for UPDATE, and one for DELETE. You don’t have to use them if you don’t want to, but it is nice to have if you quickly need your application to interface with your database. You can also tweak the generated TSQL to fully meet your custom needs, but the amount of code generated for you should help save a good chunk of time.

Final thoughts

Even if the presented options seem like simple/trivial things, they are present in some shape/form in the day-to-day tasks of a database developer, so SQL Autocomplete can help you boost your productivity in any of those areas.
As I’ve mentioned before, the tool by itself is not going to boost your productivity right away. It requires a time investment from your end, but make no mistake about it. The return on that investment that you are making is definitely worth it. And, as we saw in this article, SQL Complete smoothens the learning curve by a mile.
Stay tuned for more articles where we can see how SQL Complete can help you boost your productivity!

Increasing database development productivity with SQL Complete (Part 1)

Alejandro Cobar — Tue, 11 Oct 2022 00:52:17 +0000

In today’s game, IT professionals are expected to be highly productive and very efficient with the resources within their grasp. However, in many cases, we as database professionals are left with the bare minimum tools to fulfill our duties, which is not perfect, to say the least.
But, what does it really mean to be productive? The definition from the web states the following:

“Productivity is a measure of how efficiently a person completes a task. We can define it as the rate at which a company or country produces goods and services (output), usually judged based on the amounts of inputs (labor, capital, energy, or other resources) used to deliver those goods and services.”

With that in mind, let’s take a look at how the SQL IntelliSense tool can help us DB Developers to increase our productivity.

Installation
You can go to Devart’s website to choose one of the download options for dbForge SQL Autocomplete. Here’s a screenshot of what you’ll see:

I recommend the first option as it’s directly the SQL Server Management Studio (SSMS) extension to get you going. The other two options are big gun installers that contain the rest of the suite if you’d like to check them out as well. But for the purposes of this article, the first option should be perfectly fine.

Use Cases
Disclaimer: I’ll be using Microsoft’s AdventureWorks sample database to demonstrate the capabilities of the tool.

Objects suggestions on the fly while typing

Immediately after you start typing your query, the tool will present a context menu so that you can easily spot the object you are looking for, and see the rest while you are at it.

But not only that. If you start clicking objects, you will see the context menu expanded to show additional relevant information for you.

This screenshot contains the information about the selected object (in this case, the Product table of the Production schema). You can see the list of columns, their data types, which allow NULL and which don’t, which are foreign keys referencing another table, and even an estimated row count.

This is very useful because it can save you several roundtrips to the Object Explorer in SSMS, expanding the tables and figuring out what you really need from all the participating tables of the query you are crafting.

A quick tip: if you hit the tab after selecting the object you intend to use, this SQL IntelliSense tool will complete the statement and assign an alias for you to carry on with your query:

Moving forward with the elaboration of the query, if I start typing the word JOIN and hit space, another context menu will appear. However, this time it will be a jaw-dropping one that lists all the tables that you can join the table with, even with the specific fields!

Again, if you hit Tab right after picking the one you want, it will be completed for you.

Information within the result grid

Giving continuity to the example I’ve been presenting, let me go ahead and execute the query from the previous screenshot:

Several things to remark here:

● At the bottom of the result grid, you’ll notice a gray bar with several results (MAX, MIN, AVG, SUM, etc.).
● When you either select a few rows of the same column or click the entire column, the true value of the feature will shine. Depending on the data type of the column and the number of rows selected, the values in the bar will change as well.
● In my particular case, I’ve selected the entire contents of the Name column, which tells me that the longest string is 32 characters long, the shortest one has 4 characters, the amount of rows selected is 1069, and that out of those 1069 records 432 are distinct. The AVG and SUM results will start reacting as soon as you pick a column with numeric data type values.
● To be honest, there were way too many times I needed to copy/paste the entire result set to an Excel sheet to obtain the same information SQL Complete puts right there within your sight. Awesome!

Long strings value visualization at a glance

How many times have you had the need to quickly visualize the contents of a column that holds long string values? When that happens you’re pretty much forced to copy/paste the values someplace else to look at them because stretching the column all the way to the right just doesn’t cut it. However, this SQL autocomplete tool includes the Show Data Viewer option in the result grid, so let’s see it in action.

Using the same result set from my previous screenshot, let me find a string that isn’t visualized entirely to quickly demonstrate the feature. All you have to do is right-click on the result grid and pick Show Data Viewer or press Crtl+Alt+D on your keyboard.

In the new window, you can visualize your entire content, no matter how long it is. Also, at the very bottom of the window, you’ll see how many chars conform to the string.

Now, there might be cases where you store image data in your database, which are usually saved as string representations. For this particular scenario data viewer really shines.

I’m going to perform a simple SELECT * from the Production.ProductPhoto table, which obviously should contain image data. After that, with the Data Viewer open, I’m going to select 1 random value from one of the photos columns. This is what I see:

This feature alone will save you quite a lot of time versus any traditional method you were previously using to quickly visualize the imagery data inside your database.

Final thoughts

I’ve showcased a very tiny set of awesome features that the Devart SQL autocomplete tool puts at our service. Every single feature available is definitely targeted toward helping us SQL Server DBAs to be highly productive with the daily database development tasks, so kudos to the Devart team!

I’m planning to create a series of articles covering as much as possible of the entire set of features just because it is totally worth it on its own, and because I’m sure that any SQL Server database developer out there will benefit from it.

Checking out dbForge’s SQL Complete for SQL Server (Part 3)

Alejandro Cobar — Wed, 06 Jul 2022 02:43:43 +0000

In Part 2 of the coverage of SQL Complete for SQL Server, I continued with the review of what the tool has to offer for SQL Server database developers (you can check it out here). Therefore, it is time for me to continue with the review of this awesome tool, so let’s get right into it!

Statement Expansion
Have you ever started writing a SELECT * statement only to realize that you do need to list the columns in the table(s)? Of course, before even writing your TSQL statement, you can right-click on your table in Management Studio and pick “Select Top 1000 Rows”, but what if you already went straight into typing your query?

Let’s see what Statement Expansion can do for us:

Here’s the query I’d like to use as an example. I’m specifying a JOIN operation on purpose to demonstrate that Management Studio can’t help you out in cases like this, but SQL Complete definitely comes to the rescue!

In a typical JOIN statement, you sometimes want to know what columns are involved in the operation to choose the ones you really want to return.

All you have to do is put the cursor next to the “” and a tooltip will appear indicating that you can just press *Tab** to do the magic.

Awesome stuff right!

SQL Code Formatting
When you are building dynamic TSQL code (whether in a Stored Procedure that will be executed frequently or an ad-hoc dynamic query) often times you want to work with it to either give maintenance to it or to perform the respective troubleshooting when a related issue is reported. Sometimes the end result is a query that is hard to work with and you have to spend time formatting it in a way that is digestible enough for you to do your work, which is precisely where SQL Code Formatter shines.

To demonstrate the feature, I have written a one-line query to see what exactly SQL Complete does for me.

If I click on the “Format Document” option, this is what I end up with:

I know that there are 3rd party websites that perform the exact same task, but it is something that is baked right into the tool and I find it very convenient to have right at my fingertips. Additionally, what if the server where you are executing your query doesn’t have an internet connection (which is a very common thing in a production environment)? Point for SQL Complete!

On top of this, SQL Complete gives you the option to format queries inside.sql files that you might have within a specific directory. Let’s take a look at a quick example:

I have created a directory called Queries in my Desktop folder.

Both files have an unformatted TSQL statement.

To access the feature, you click on the SQL Formatter… option within the SQL Complete option in the menu bar.

_*I apologize for the poor DPI scaling presented in the following screenshots. It is very likely that it will display correctly when you give it a shot. _

It's a nice option to have around if you ever have the need for it.

SQL Refactoring

Variables Renaming
Sometimes, when you have to deal with stored procedures and/or functions that you have to refactor, a common thing that you do is rename the variables used, whether it is for best practices or to adhere to a specific naming convention within your organization. Attempting this directly through SQL Server Management Studio can be a bit inconvenient.

This is a sample function I have created for demonstration purposes. Let’s suppose that I want to change the name of the variable @age to @result.

In SQL Server Management Studio, I go to the Find and Replace option and specify that I will be replacing the word age with result (yes, I know that the inconvenience I’m about to report is easily solved by replacing the whole variable name @age instead of just the word age, but it is something that can happen to anyone).

After proceeding with the replace, I can see that not only were my variables successfully changed, but it also changed the name of the function and my comment.

To avoid this, let’s use the renaming feature provided by SQL Complete. To achieve this, simply highlight the word of the variable that you want to change (in my case, I highlight the word “age”), right-click on it and click the Rename… option.

After doing that, you can proceed with the word replacement in the word that you highlighted. Note that a dotted square will appear in all the words where the change is going to be applied and a tooltip will appear stating that you can hit Enter/Tab to complete the operation.

And this is the end result after proceeding with the modification. As you can see, only the matching variables were modified, and the name of my function and my comment were left intact.

Semicolon Insertion
I know, this sounds like an extremely trivial thing because most of us omit the use of semicolons as a statement terminator when we are writing our queries. However, based on Microsoft’s official documentation, semicolons will be required at some point in a future release of SQL Server. With that said, if such a day comes, you need to be prepared so that your applications don’t start shooting sparks all over the place.

To do this, simply right-click on your query page and click the “Insert Semicolons” option and voila!

Final Thoughts
● In this 3rd part, we continued to explore very useful features that can definitely improve the productivity of database developers over time. It is remarkable the amount of features that we have looked so far throughout the series, considering the fact that we have been talking about an extension for SQL Server Management Studio and not a stand-alone tool!

● Given the fact that SQL Complete is stuffed with a ton of useful features, it will require a time investment for any new SQL Server developer to grasp everything the tool has to offer, but the return from that time investment is totally worth it, especially since the Devart team is constantly addressing bug fixes to have the end product as polished as possible.

Checking out dbForge’s SQL Complete for SQL Server (Part 2)

Alejandro Cobar — Wed, 06 Jul 2022 02:30:56 +0000

In Part 1 of the coverage for SQL Complete for SQL Server, I gave a quick glimpse at what the tool has to offer for SQL Server database developers (you can check it out here). Therefore, it is time for me to continue with the coverage of the features that didn’t make the cut in the previous article, so let’s get right into it!

I’d like to take a brief pause because right after firing up SQL Server Management Studio, I got a popup window that looks like this:

So I’m getting v6.11.3 before moving on with the article!

After clicking the “Update” button, this is what you’ll see:

After clicking “Run Installation," SQL Server Management Studio will close and you’ll see a window like this one:

As part of the setup process, there’s a “Startup Optimization” step, which is a very welcome thing from the Devart team, so I suggest you enable that as well to enhance your experience with the tool:

As a quick side note, here are the release notes for SQL Complete version 6.11.3:
New features and improvements

● SSMS v19 Preview 2 integration is added

User-reported and internal bug fixes

● Fixed an application error that occurred when starting SSMS 2014
● Fixed the display of data in Data Viewer when changing the order of columns in the Editor
● Fixed an error that might occur when opening documents
● Fixed an issue with displaying Quick Info on an incomplete script
● Fixed an issue with missing hints for Linked Server objects
● Added temporary table hints that do not depend on the selected database
● Fixed the problem with incorrect formatting of procedure parameter names
● Fixed an unexpected exception that might occur when copying data to the clipboard (D89130)
● Fixed the reset of tabs color settings after closing SSMS with SQL Complete activated (D85351)
● Fixed an issue with setting the database name in the Query Editor window when starting SSMS using the -d argument ** (D81862)
● Fixed the disappearance of the Document Outline window (D81156)
● Fixed an error that occurred when starting SSMS (D87321, D89325, D90091, D90192, D90396, D90381, D90374, D90368)
● Fixed memory leak issue that occurred when executing large scripts (D89598, D90100)

Moving on with the review of the rest of the tool, here’s what you can do with it:

Tabs and database colors:
When you are performing database development work and you have to work with multiple databases across different SQL Server instances, sometimes you can have a hard time recognizing right away to which environment each tab belongs.

You can also create your own categories as you please, so that you can structure your workflow to your own liking.

Result set grid:
The first option is a cool complementary bar at the bottom of the result set grid where you can see aggregate information about the data you manually select. Let me give you a quick example of what this is about:

As you can see, I have manually selected the first 10 values of my first column, and since that’s an IDENTITY PK field, then it has consecutive INT numbers. After I select them, then I can see at the bottom that the MAX selected value is 10, the SUM of all selected is 55, and so on. The results calculated will vary by the number of values selected, which makes it the very first time I’ve seen anything like this.

Now, if you want to look for a specific string within your result set, then you can click the Find option and any match will be highlighted:

The next option is called Data Viewer. Let’s suppose that you have a table with an NVARCHAR(MAX) column and you have very long strings in there. If you right-click your result set and select the Show Data Viewer option, then you will have a new view to display the information in a more “digestible” way.

As you can see, the view at the bottom shows the entire string, while the original view has limited visibility, and you would have to drag the size of the column all the way to the left to give it a shot. This is very handy when you have to deal with columns that store large strings.

The Data Viewer allows you to quickly visualize image data that you might have stored in your database, which is a very cool feature on its own:

*The following screenshot is taken from Devart’s website, for demonstration purposes.

The next option is Generate Script As, which gives you different options to work with your result set.

If I pick the INSERT option, then it will create the TSQL statement to perform the insert of my data, which is very useful if you are trying to migrate a particular database table to another place, something that SQL Server’s native backup hasn’t been able to bring to the table yet.

Let me show you another example with one of the other options (in this case, “INSERT #tmpTable”):

I’ve selected the rows of another table that I have just to show that any of the selected options will work only against the manually selected rows, as follows:

The next feature I want to demonstrate is related to the export of the data. Natively, SQL Server Management Studio lets you export your data to different file formats, but it is not as straight-forward as compared to SQL Intellisense. Let me show you how, with a couple of clicks, I can export my result set to a JSON file:

Again, the export will work on the data that you have selected in the result grid, so keep that in mind.

Final thoughts
● Right after I started working on the first part of the review for this tool, I got notified that SQL Complete had a new release ready to be installed, which tells you that the Devart team is constantly polishing their suite of products so that DB developers can maximize their productivity. And not only that, the release notes specifically state that the bugfixes issued are user-reported ones, which is very cool because they definitely listen to their audience!

● As you just saw in this follow-up review of SQL Complete, the tool brings to the table a variety of options to enhance the productivity of SQL Server database developers.

● I’m thinking of making a Part 3 because there are options that I haven’t covered yet, but would still like to keep the format of these reviews as digestible as possible for the reader, so stay tuned!

Checking out dbForge’s SQL Complete for SQL Server (Part 1)

Alejandro Cobar — Thu, 30 Jun 2022 14:36:21 +0000

Installation

You can get dbForge SQL Complete by clicking the Get Trial button. If you go for the SQL Tools Professional Trial, it will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.

Note: SQL Complete is an add-in for SQL Server Management Studio, so I would recommend having the latest version installed, although if you have an older version (e.g. 2012, 2014), then it will probably work as well.

Since SQL Complete is not a stand-alone tool, the first thing you must do is open SQL Server Management Studio and establish a connection against a target SQL Server instance of your choosing. Once in there, you can fire up a new query window and start laying out a query, just to see what it does:

In my case, I’m currently connected to my database called “test2” which only has 1 table right now. As I’m writing my SELECT statement, you can see that SQL Complete is listing that table for me in the context menu, which is very helpful when your databases are flooded with dozens of objects.

Now, if you don’t want to type the whole name of the desired table, then you can simply select it and it will complete it within your query.

For demonstration purposes, I have created a new table called “table2” and re-tried writing the same SELECT statement again. However, this time I noticed that only “table1” was showing in the context menu; it showed only after I dropped the connection to the instance and re-established it, so that you are fully aware. As you can see in the screenshot, it is showing as expected.

There’s one additional detail that I would like to bring up, and it is that if you select a particular table with the arrow keys on your keyboard, then it will list you all the fields with their respective data types. It will also tell you which fields conform the Primary Key, and an estimate of the row count. That feature by itself is a massive productivity booster for all of us SQL Server DB Developers!

Just out of pure curiosity, I have created a third table called “table3” with 31 fields (1 id and 30 NVARCHAR fields) just to see how SQL Complete presents a wide table:

With my on-going curiosity, I want to try with an even wider table (61 fields) to see the behavior:

It will display as much as your screen can handle. It would be neat to include a scrollable view for the context menu for cases like this though, but I’m sure that the Devart team will address it if the demand for it is high enough because they are constantly improving their suite of tools for the greater good.

*I would like to make a tiny pause here and take back what I mentioned earlier in regards to the need to re-establish a connection to the SQL Server instance so that my newly-created table would appear in the context menu. It turned out that after I created my tables “table3 and table4”, I got up from my seat for a few minutes, and when I got back, the tables were there, without the need for me to re-establish my connection.

Now let me move on to an INSERT statement to see what we get:

Interestingly enough, here you get a scrollable view for all the fields if you have wide tables!

With that detail out of the way, I want you to notice that next to each field name there’s a white/unmarked checkbox, so if you start clicking them you will see how your INSERT statement is being completed for you (it’s just SQL Complete living up to its name ;) ):

Now, if you move on to the VALUES section of your INSERT statement and start filling in the values, then SQL Intellisense will indicate which column you are specifying the value for:

This visual feedback alone is worth every penny!

For the UPDATE statement you pretty much get the same treatment, as you are typing it you will be getting the help from SQL Complete to write a valid statement:

Moving to the DELETE statement, as soon as I enter the word DELETE, then I’m presented with 2 options:

If I pick the “DeleteFrom” option, you will notice that SQL Complete will layout a full skeleton for a formal DELETE statement that you simply have to accommodate to your own case:

Moving on to a SELECT with a JOIN statement, this is what I’m able to see when I reach the part where I have to specify the fields to perform the match:

As you can see, it is suggesting to me that the JOIN can be done using the id fields of both tables, and it will also show the participating tables along with their respective fields.

Moving on to the last section of this article, I want to show you a feature that SQL Complete also has baked in. If you right-click on any database, you will see a section called “SQL Complete” and then an option called “Find Invalid Objects”:

After you click that option, you will see something like this:

If I hit the “Analyze” button or link, this is what I’ll get:

For this particular demonstration, I have created on purpose a Stored Procedure that is trying to do something on a table that doesn’t exist within my “test2” database, just to show you that SQL Complete will not only let me know that there’s something wrong with my Stored Procedure, but it will also pin-point where the issue is. Once again, hats off to the Devart team!

Final thoughts
● I have definitely just shown you the surface of what SQL Complete has to offer, so if you’d like to dig deeper, then I highly recommend you check the documentation center for even more information. I will continue with the coverage of SQL Complete in a second part so that the content is presented to you in a digestible format.

● If your workflow revolves heavily around DB Development for SQL Server, then SQL Complete is definitely the tool to have to boost your productivity.

● SQL Complete is the perfect complement, whether you are an experienced senior or a not-so-experienced junior database developer.

Checking out dbForge’s Decryptor for SQL Server

Alejandro Cobar — Wed, 15 Jun 2022 15:33:54 +0000

There’s no mystery about the fact that Microsoft takes security very seriously, and SQL Server provides several different features to protect the databases of end-users. Now, just by hearing the name “Decryptor for SQL Server” it gives the impression that somehow it must be related to a security feature, so stick around to discover what Decryptor for SQL Server can do for the professional SQL Server database administrator.

Installation
You can get dbForge SQL Decryptor by clicking the Get For Free button. If you go for the SQL Tools Professional Trial, it will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.

Note: Make sure you have .NET Framework 4.5.2 or higher installed.

I will be showing the standalone version of the tool, so feel free to follow along either using the same version or through the SQL Server Management Studio extension.

After opening the application for the first time, it will immediately detect the SQL Server instance in your server, so pick one and fill in the connection string parameters to move on. You don’t need to tick the Dedicated Administrator Connection mode, I’m just showing that the option is there in case you ever need to use it.

Once you pick your SQL Server instance, you will see something like this:

It looks a lot like SQL Server Management Studio, so I have expanded all my user databases to show you that you will see the same directory layout in all of them: Procedures, Functions, Views, DML Triggers, and DDL Triggers.

To see what options the tool provides, let me pick a random Views directory, expand it, and see what it does after right-clicking any of the existing objects:

The Connect and Disconnect ones are pretty much obvious, so I’m going straight for the other ones. This is what you get after clicking Show DDL script:

If you click the Decryption Wizard option, this is what you will see:

Let me pick a File path and hit the Execute button to see what I get:

Ok, fair enough. That’s probably why the Decrypt in-place option was not enabled initially because that particular object isn’t encrypted.

With that said, let me take a step back and prepare a better example. So I’m quickly going to switch to SQL Server Management Studio to alter the definition of the view I tried in my previous attempt, and will add encryption to it:

Let me take a pause here to expand a bit on this WITH ENCRYPTION option. It turns out that right after altering the definition of the view and adding the encryption option, I can no longer see its definition, even if my user is the owner and I have sysadmin privilege granted:

By the way, it doesn’t matter if you try to get the definition of the view through the help of the sp_helptext Stored Procedure, because you will reach the same dead-end:

Now, to revert this, you need to have the source code of the ALTER VIEW command for the specific view that you want to modify, but what if you don’t? Well, without a backup that contains the original definition of the view, you are pretty much fried. But rest assured, because that’s precisely where SQL Decryptor will shine, so let me go to the tool again and re-attempt my previously failed demonstration:

As you can see, not only the Decrypt in-place option is enabled, but I can also see the definition of the view that I couldn’t back in SQL Server Management Studio, cool!

Now, if I hit the Decrypt in-place option, then it will prompt me with the following:

If I hit No and attempt to go through the Decryption Wizard one more time, this is what I will see:

The cool thing about the Decryption Wizard is that you can either generate the DDL script of the objects that you want to recover or revert the encryption right in the database:

For simplicity, I’m going to pick the Decrypt in-place (alter objects) option so that the object is modified directly in my database:

As you can see, the tool will put a green round-check icon next to the object that has been successfully altered.

If I go back to SQL Server Management Studio, I can retrieve the definition of the view once again, either through the GUI or through the sp_helptext Stored Procedure:

The same behavior/functionality will be provided for Stored Procedures, Functions, and Triggers, which is very cool!

This was a rough introduction to the tool, but I think that if you reached this point, you now have a very good idea of the specific problem that it solves for you.

Final thoughts
● I can already see a big use case for this tool: imagine that you are a new database administrator that has just joined the team and has inherited a set of SQL Server instances to provide support to. Now, a DBA that previously was in the team had the magnificent idea to encrypt many database objects to hide their definition even from the DBAs with the highest possible privileges; but guess what, there’s currently no available database backup where the original definition of those objects can be found, so you cannot even give proper maintenance to those hidden structures. Luckily for you, Decryptor for SQL Server can save the day with a few clicks to avoid the completion of the perfect recipe for a wonderful disaster.

● Using the WITH ENCRYPTION hint within the definition of your database objects can make all the sense in the world, but you have to be very careful on how you manage things within your environment because if not done properly it’s like locking the door of your home, walking out and leaving the keys inside… but Decryptor for SQL Server is that spare key under the carpet to save the day ;)

Checking out dbForge’s Event Profiler for SQL Server

Alejandro Cobar — Wed, 15 Jun 2022 15:17:17 +0000

During our run as SQL Server DBAs, sometimes we need to capture everything going on in a particular SQL Server instance. Whether it is for troubleshooting a performance issue, capturing deadlock events, or tracking a specific activity, SQL Profiler will give you the right set of options, so let’s give it a quick look.

Installation
You can get dbForge Event Profiler by clicking the Get For Free button. If you go for the SQL Tools Professional Trial, it will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.

Note: Make sure you have .NET Framework 4.5.2 or higher installed.

I will be showing the standalone version of the tool, so feel free to follow along either using the same version or through the SQL Server Management Studio extension.

After opening the application for the first time, click New:

You will be presented with the following view:

First, specify a connection string where the application will try to connect to capture the configured events. Since we don’t have any connection set up yet, click <Manage…>.

Let’s click New…:

You’ll see a regular window where you have to specify basic connection string parameters. The cool part is that you can categorize your SQL Server instance, either using any of the four presets (Development, Production, Sandbox, and Test) or creating your categories (as I already have).

Now, you can click Test Connection to confirm if you have entered everything correctly:

If the connection attempt succeeds, click Connect, and you will see your new connection created:

You will return back to the first screen, but with the connection setup, so the next step is to pick a template for your capturing events. It will be very familiar to you if you are used to SQL Server Profiler.

For simplicity, I’ll be choosing Standard (default). A neat detail is that, depending on the template you select, it will tell you what you can achieve with that particular template.

Below the template section, you’ll see the following options:
● You can specify a particular date & time when the trace will stop.
● You can specify the path where the file(s) will be created.
● You can specify the size limit (in MB) for each file.
● You can specify the maximum number of files where the captured data will be distributed.

After setting all those parameters, click Next:

This is what you’ll see by default.

If you tick the Show all checkbox, then you will see a wide variety of events to capture:

After clicking Next, you will be presented with the list of global fields that your trace will capture. Again, go through them to pick whatever you consider will be useful.

In the next window, you can specify any desired filters in the next window to narrow your result set and have an even more digestible output.

In the next window, you can customize the fields that will be captured in your event session, along with useful descriptive information for each one.

After you click Execute, you will see the live-collected data for your configured session. If you click any of the visible rows, you will see the full command (displayed on the bottom-left section) and the list of fields, with their values, along with descriptive text for each event field (displayed on the bottom-right section).

If you want to manually stop your trace, then click the “Stop” button and a final entry named “Trace Stop” will appear.

If you want to save this output, for post-analysis or for any other reason, click on the “Save” icon in the top toolbar.

That way, if you close the application and open it at a later time, then it will be available for you to open the output again:

Back to the trace output, what if my session captured a large number of records and I want to perform searches for any matches, how can I do that?

You either enter Ctrl-F or navigate to the + option displayed in the screenshot.

If you are interested in obtaining a particular SQL Statement, you can either copy/paste it from the bottom-left section by clicking on top of the desired one, or you can right-click on the row that contains it and specify Extract Event SQL statement to.

That was a very brief demonstration of what Event Profiler can help you with. I hope you have found this helpful.

Final thoughts
● If you’d like to get more information about Event Profiler, you can check Devart’s official documentation center.

● You might be thinking “why would I pick dbForge Event Profiler over SQL Server Profiler if both do basically the same?”. That’s a very good question, and my best answer to you would be the fact that Microsoft has already deprecated SQL Server Profiler, which means that they will eventually kill the feature in a future release of SQL Server. Therefore, if you heavily use the feature in your workflow, then Devart has got you covered regardless of when Microsoft decides to pull the trigger ;).

Checking out dbForge’s Monitor for SQL Server

Alejandro Cobar — Mon, 06 Jun 2022 13:10:54 +0000

As professional production SQL Server DBAs, we have to deal with our SQL Server instances when the end-user reports that famous “slowness” with their queries. Even if the problem isn’t with the database server itself, the finger-pointing will be primarily against the database (pretty much like the Simpson’s meme where Bart is blamed for everything all the time, even if there are times when he didn’t do anything).

Whether it is an issue with the database server or not, you need to have a way to confirm if your SQL Server instance is working as expected or if it is indeed throwing sparks all over the place. Now, every SQL Server DBA has its way of figuring out if the SQL Server instance is healthy or not, but sometimes the process isn’t as straightforward, or maybe they don’t even have one (worst case scenario).

With that said, let’s look at what Monitor for SQL Server has to offer to see if we can incorporate it into our workflow.

Installation
You can get dbForge Monitor by clicking on the “Get For Free” button. If you go for the SQL Tools Professional Trial, it will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.

Note: Make sure you have installed SQL Server Management Studio 2012 or higher to proceed.

After completing the installation, head straight to SQL Server Management Studio and open it. Once opened, establish a connection to a SQL Server instance and right-click on it.

This is what you’ll be seeing:

After you hit the “Show Monitor” option, this is what you’ll see:

Important Note: if your dpi scaling is above 100%, some information will not be displayed properly because it will not fit the developer's intended. Let’s remember that SQL Server Management Studio doesn’t scale that well, but that’s something that happens with many applications; it is not exclusively to SQL Server Management Studio.

Within the view that you just saw, we can see a ton of general information, all below the “Overview” section, which displays the following:

● CPU utilization %
● Memory utilization (in GB)
● Disk Activity (in MB)
● Batch Requests/sec
● Compile/recompile per sec
● Waiting tasks
● Connections
● Deadlocks
● Transactions/sec
● Full scans/sec
● Range scans/sec
● Page reads/sec
● Page writes/sec
● Page faults/sec
● Page lookup/sec
● Page splits/sec
● Page Life Expectancy
● Read Latency (in ms)
● Write Latency (in ms)
● I/O Latency (in ms)
● Host Properties
● SQL Server properties

Let’s see what’s in the section labeled “Data IO”:

We get the total read/write amount of Megabytes and the read/write latency (in ms) per database data file (first the data files and then the transaction log files).

Let’s see what’s in the section labeled “Databases”:

We can see general information about all of our current databases.

Let’s see what’s in the section labeled “Wait Stats”:

We get information on the current wait stats, sorted from the highest one to the lowest one. Let me pause here to stress the importance of this information; when you’re having continuous problems with your SQL Server instance, the wait stats are one of those critical areas to keep an eye on.

Let’s see what’s in the section labeled “Top Queries”:

This section is also a very important one because it shows the top 10 executed queries in your instance, and you can sort that information by any of the columns presented there. Not only that, but you also get the execution plan for each query, and if you hover your mouse cursor on any of the plan operators, then you will get additional information. This, combined with the wait stats information, should be enough to call it a day, but let’s keep going to see what else we can find.

Let’s see what’s in the section labeled “Sessions”:

We get the list of sessions within our instance.

Let’s see what’s in the section labeled “Backups”:

We get information on our database backups. This section is another critical one because production SQL Server DBAs should be aware of the latest status of all the database backups within each SQL Server instance under his/her umbrella.

Final thoughts
● After going through all the information sections that the tool provides, I can conclude that it provides a ton of value to the production DBA that has to stay on top of their game, and SQL Server performance Monitor is an ideal tool to help him/her in the proactive and reactive situations.

● Devart provides more information about this tool in their official documentation center, so be sure to check that out.

● I’m going to be honest, I would’ve liked to see an option to export the information from each one of the sections. Something like that is very helpful when you have to report back to your manager or the business on any findings, right after your troubleshooting session; whether it is to report that everything is running smoothly like butter, or to pin-point any situation that has to be addressed. Regardless, I’m also aware that the Devart team is constantly enhancing their suite of SQL Tools, so I guess it will only be a matter of time before that feature (or any additional) sees the light of day.

Checking out dbForge’s Unit Test for SQL Server

Alejandro Cobar — Mon, 06 Jun 2022 13:05:01 +0000

In the coding/software development space, you have likely heard, at least once, the term “unit test”. Let’s take a look at a definition of a unit test for software development:

Unit testing is a software development process in which the smallest testable parts of an application, called units, are individually and independently scrutinized for proper operation. This testing methodology is done by software developers and sometimes QA staff during the development process. The main objective of unit testing is to isolate written code to test and determine if it works as intended.

But, how does that translate to the database development space? Let’s see if Unit Test for SQL Server answers that for us.

Installation
You can get Devart’s SQL Unit Testing tool by clicking on the “Get Trial” button. Doing so will also include the whole suite of SQL Server Management Studio extensions, which adds a lot of extra value.

Note: Make sure you have installed SQL Server Management Studio 2012 or higher to proceed.

After you have completed the installation, head straight to SQL Server Management Studio and open it. Once opened, establish a connection to a SQL Server instance and go to any of your databases, and right-click on it.

This is what you’ll be seeing:

Since it’s our very first time checking Unit Test, then let’s go straight to “Add New Test”:

You’ll immediately see this, so let me fill requested information to keep moving forward.

Important note: the tool will check if tSQLt framework is installed and will warn you that the unit test will not be created if that’s not the case, so let me go ahead and install it then.

This is what we get after hitting “Yes”:

So the block of code you see is for altering a stored procedure, so I guess that your unit test will be baked inside this stored procedure. If I take a look at the stored procedures inside my database, I can see a bunch with the prefix “tSQLt”, so now we know why the tool insisted on the installation then.

If we take a look at the comments within that block of code, we can see that it has 3 sections:

Assemble: This section is for code that sets up the environment. It often contains calls to methods such as tSQLt.FakeTable and tSQLt.SpyProcedure, along with INSERTs of relevant data.

Act: Execute the code under test like a stored procedure, function, or view and capture the results in variables or tables.

Assert: Compare the expected and actual values, or call tSQLt.Fail in an IF statement.

If I go ahead and execute the stored procedure exactly as it is, this is what I get:

Since it’s my first attempt at something like this, I have no idea how to proceed, but lucky for me, there are informative links right in the stored procedure template that I’m going to check out before I’m able to figure out what to do next.

Let me give you a tip if you simply don’t know where to start: after you install Unit Test, a new menu option will be made available right there in SQL Server Management Studio, called “Unit Test”, and this will allow you to install a sample database that has some test cases baked in for you to look around and get a feeling of how things work:

Now, if instead of clicking “Install Sample Database” you click “View Test List”, then this is what you will see:

These are all the available tests within the sample database, and if you click the icon being pointed by the green arrow, then you will run all of them, so let’s check those out.

Ok, so this gives me the outcome of each test so that I can see which ones succeeded and which failed. But what if I want to take a look at a failed test to address whatever it is that I have to address? How can I do that? Simply right-click on the row with the failed test and click “Open Test”.

With the stored procedure open that contains the failed test, I’m going to tamper with it just a bit to force a successful test case, just to confirm that’s how that works:

And yes, that worked like a charm, cool!

Let me continue with the one I originally tried to make:

This is a very basic example, so I will save it in my stored procedure and try to manually execute it without using the GUI that I showed previously.

So if I try to execute my test case stored procedure directly, then I’m getting this weird error.

But guess what? To test it properly I have to run the stored procedure called tSQLt.Run and pass my stored procedure as a parameter, just like this:

As you can see, it will tell me that my test case failed because “Expected: <5> but was: <10>”. I also get a fancy table with the summary of the test case, and I assume that as you add more test cases, this table will also grow if you specify to run the entire class instead of an individual stored procedure test case.

Now let me go to my stored procedure and change that 5 for a 10 and run it again to see if I get a different output:

Bingo, it worked! So I have shown you how to run unit tests manually and using the GUI.

Final thoughts
● Obviously, this was just a very brief look at the tool, but I encourage you to continue to dig deeper by checking Devart’s official documentation center.

● After looking at what the tool offers, I think we can safely say that the definition we saw earlier, on unit tests in the software development space, matches in the database development space. With that said, I see this as a precious tool for database developers to raise the bar in the quality department.

● Suppose you are currently not covering unit tests within your database development workflow. In that case, it goes without saying that it is something that takes time to build for your particular environment. However, once you get a good grasp on it, it is worth it!

Checking out dbForge’s Index Manager for SQL Server

Alejandro Cobar — Wed, 01 Jun 2022 12:46:53 +0000

In this article, we’re going to be taking a look at Index Manager for SQL Server, from Devart. See what it has to offer for users like you and me and if it is a tool that you can integrate into your professional workflow.

There will be times when query performance problems are going to be tied to indexes, so this tool is a perfect fit to help you navigate that road. Every professional DBA should have a way to know the status of the index structures within all the databases under his/her umbrella; that way, he/she can adopt a proactive approach instead of a reactive one to help prevent performance issues due to fragmentation within the index structures.

Keep in mind that this targets on-premise deployments of SQL Server, Azure SQL Managed Instance, and/or SQL Server deployed in an Azure VM and Azure SQL DB. Also, if your database infrastructure is heavily based on Solid State Drives, then index fragmentation will be the least of your problems (it’s costly but totally worth it).

Installation
You can get SQL Index Manager by clicking on the “Get Trial” button. Doing so will also include the whole suite of SQL Server Management Studio extensions, which adds a lot of extra value.

Note: Make sure you have installed SQL Server Management Studio 2012 or higher to proceed.

After you completing the installation, head straight to SQL Server Management Studio and open it. Once opened, establish a connection to a SQL Server instance and go to any of your databases, and right-click on it.

This is what you’ll be seeing:

As you can see, the only option you get is “Manage Index Fragmentation”. The “Help” one is simply information about the product, nothing pragmatical within the tool itself.

Once I click on “Manage Index Fragmentation”, this is what I get:

You can immediately see that, for my particular database, found no fragmented indexes, so no actions are required. I know that’s not very helpful, so let me prepare a better sample database to show you what the tool offers (at least you get to see what output you get when everything is fine ;) ).

Ok, so this is what I now see after a 2nd attempt:

I created a new table, created a non-clustered index over one of the columns, set the fill factor to 50%, and inserted a bit more than a million records of purely random data. So now, the tool shows us an item that must be addressed, so let me cover that and the rest of the visible elements within the tool.

So, in the “Action required” section, you can see that it’s telling me that the non-clustered index I created has a 99.01% of fragmentation and that the “Fix Type” suggested is a Rebuild. Let me make a quick pause here because I have to mention something important here:

I’ve seen that many states that the ideal course of action is to reorganize your indexes if the fragmentation percent is between 10% - 30% and to rebuild if the fragmentation is higher than 30%. However, I’d suggest you decide that based on your particular scenario; regardless, if you click the “Options” button in the application, you’ll see that Devart has these same values set right out of the box, so make sure to test thoroughly.

*Be very careful with that “Online” checkbox because if it’s not ticked, then an index rebuild operation will block the entire table to make it happen, and that could negatively impact your business operations. If you’re running this in a sandbox environment, that shouldn’t be that big of a deal.

For demonstration purposes, I’m going to execute the index operations with the values as they are:

*By the way, if you click on the “Export to CSV” button, you can generate a report that you can either share with your Manager, fellow DBAs, or simply for yourself.

With that out of the way, to proceed with my index operations, I click the checkbox on each one and hit the “Fix” button:

You can also script the actions that the tool intends to apply so that you can address them at your convenience.

After the index operation is complete, you can hit the “Reanalyze” button to see a new status:

And as you can see, the previously reported fragmented index was taken care of, and everything is looking good for my indexes ;).

Final thoughts
● dbForge Index Manager gives you suitable options to manage your index structures. Index Management is a crucial task that every DBA should have within their umbrella, and this tool comes right to the rescue.

● I’m not going to lie, I would’ve liked to see something to automate index management within my SQL Server instance, but I also know that the Devart team is constantly making improvements to their entire suite, so I’m sure it will only be a matter of time.

● If you’d like to get even more information, you can head to the official documentation center.

● Something that I didn’t cover during the development of this post, is the possibility that the tool provides to perform operations using the command line. This opens the door for you to automate index operations using PowerShell and/or Windows Scheduled Tasks, cool!

Checking out dbForge’s Query Builder for SQL Server

Alejandro Cobar — Tue, 31 May 2022 12:46:18 +0000

In this article, we’re going to be taking a look at Query Builder for SQL Server, from Devart. See what it has to offer for users like you and me and if it is a tool that you can integrate into your professional workflow.

Installation
You can get SQL Query Builder by clicking on one of the “Get Trial” buttons. However, I highly recommend clicking the one that says “dbForge SQL Tools Professional Trial” because that will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.

Note: As you can see, the site states that the tool requires .NET Framework 4.5.2 or higher installed, so make sure that’s the case so that you can follow along. On top of that, by now you probably already figured out that this will be a Windows-only thing, but that shouldn’t be a showstopper to give the tool a chance.

Since it’s the very first time that we will be opening the tool, then let’s click on “New”:

On the very next screen, this is the view that we are presented:

In the left section, we can see an area that says “Database Explorer”, which is probably where the connection to our SQL Server instance has to be specified.

In the upper-center section, we simply see some sort of grid and a text that says, “You can drag objects from Database Explorer here.”.

The bottom section has several options (Selection, Joins, Where, Group By, Having, Order By).

So to give the tool an initial shot, let’s first create the connection to the SQL Server instance that we will be targeting (in my case, for demonstration purposes, I’ll go with the default instance I have setup):

Let’s click first on this icon with the green cross.

You’ll see a regular window where you have to specify basic connection string parameters. The cool part is that you can categorize your SQL Server instance, either using any of the 4 presets (Development, Production, Sandbox, and Test) or create your categories (as I already have).

If you click on the “Test Connection” button, a popup will trigger stating that you have successfully connected to your instance, and it will even tell you the SQL Server version detected (if the specified values are the correct ones, of course).

If the connection succeeds, then hit the “Connect” button, and you’ll be taken back to the view with the grid, but with the connection to the instance open and with the list of databases currently hosted in there.

Now, let’s do exactly what the text in the grid section says - drag and drop objects from the “Database Explorer” section.

So I have 2 tables in my database called db1, and one of them has a foreign key to the other one. Let me drag first the table called “table1”:

Pretty straightforward, I get a window with the name of the table, and the list of fields, and it shows me a small key icon next to the field that currently is the Primary Key.

But you might be wondering, “why I’m not able to see the data type of each field in that area”? Well, it turns out that you can see that when you click a field and take a look at the information that appears in the “Properties” area, right below the “Database Explorer section”.

Ok, now let me drag the other table to see what happens:

It automatically detects that there’s a relationship between them (the foreign key I mentioned before), nice!

But you know what, the tool is called “Query Builder”, not “Diagram Builder”,… so this might not be really what it is intended for, so let’s explore a bit more to see what we’re able to find:

So if you start ticking the checkbox for each column, then things will start happening in the bottom section of the screen. As you can see, it will list each one I’ve ticked, and I can give it an alias.

So just by doing that, I’m getting the vibe that a query is being auto-generated for me, based on my selection of columns and the information I provide, so let me see if I can find it:

I guess it was right there all along (thanks, Captain Obvious):

Ok, looking good so far. But what else can I do here? Let’s right-click on this window to find out:

All right, I’ll click the “Execute” option to see what it does:

A new tab called “Data” appears, and you can see the result of the query that you just saw previously. It will also specify the specific data type of each column.

With that alone, I would have had enough to call it a day because the tool does so much with so little action from the user’s end, but I’m curious enough to see if I can find even more cool things to share with you, so buckle up:

If I right-click on the result set, go to “Data Layout” and then click on “Card View”, this is what I see.

You know what, this is the very first time (no joke) I’ve seen something like this. Even if you might argue that it is not the best way to consume/visualize the data, you cannot argue with the fact that it is something that differentiates Devart from the rest, and I’m not talking just for this feature, but as a whole in general.

What if I need to export the result set to generate a report or whatever? We can right-click on the result set and click on “Export Data…”.

We get the following set of file formats to export our data:

This is what my PDF looks like after I complete the process:

As you can see, I can even go ahead and format the look of my table, sweet!

This was just the “tip of the iceberg”. You can do so much more with the tool, but I will leave it here for today, so you can think of this post as an appetizer ;).

Final thoughts
● My first impression when I saw the tool was, “ok, I guess this is just another database diagraming tool”, but boy, I was wrong, Query Builder is way more than that. In this article, I showed you the very basics, and as much as I would’ve liked to show you everything, it would’ve been way too much to digest for the reader.

● If you’d like to go deeper on the tool, I highly recommend going to their official documentation center.

● I see a massive particular use case for this tool. If you are a business that hires interns or junior database developers, then this is a perfect fit because they can practice and enhance their skills with Query Builder, something that is very valuable, especially in these modern times where the demand for data engineers has never been so high!

Checking out dbForge’s Data Generator for SQL Server

Alejandro Cobar — Fri, 27 May 2022 13:17:51 +0000

When you are working as a data/database architect, sometimes it’s a bit hard to determine/figure out right away if a database table will require an auxiliary index structure to guarantee that queries will always run fast (at least most of the time), or even if the choice you made for a particular Clustered Index will eventually negatively impact the performance of your insert, update and/or delete statements. For that reason, I think there should be a scientific process to get to the conclusion if the decisions around such index structures will benefit performance or not, and that’s where the SQL Data Generator tool for SQL Server comes into play.

As you’ll see during this post, the tool can let you quickly and effortlessly fill your database with enough test sample data so that you can evaluate your test cases.

Installation
You can download dbForge Data Generator by clicking on one of the “Get Trial” buttons. However, I highly recommend clicking the one that says “dbForge SQL Tools Professional Trial” because that will also include the whole suite of SQL Server Management Studio extensions, which is worth every bit.

Note: As you can see, the site states that the tool requires Microsoft SQL Server Management Studio 2012 or higher installed, so make sure that’s the case so that you can follow along.

The very first time you open Data Generator, you’ll see this pretty much empty screen, so let’s click on “New” to get things going:

You are immediately being requested to enter a connection for your SQL Server instance and a database that the tool will target.

Before going any further, let’s first explore what does the “Save Command Line” button at the bottom corner does:

So based on the values that we specify for those parameters, it will build a command-line instruction and will also give us the possibility to save the instruction as an executable .bat file. This can be used for automation purposes, pretty cool if you ask me!

Moving forward, let’s specify a connection to our SQL Server instance then:

Since we haven’t, because we are opening the tool for the first time, then let’s click on “Manage” to create one.

Let’s click on “New:

You’ll see a regular window where you have to specify basic connection string parameters. The cool part is that you can categorize your SQL Server instance, either using any of the 4 presets (Development, Production, Sandbox, and Test) or create your categories (as I already have).

After you succeed with your connection creation, then hit the “Connect” button, and it will now appear listed in the “Connection Manager” window:

Now with your connection selected, the list of databases will be retrieved, and you can select your target database.

On the next screen, you’ll get all the different options to generate your test data. I’d recommend testing the different combinations until you hit the sweet spot for your particular case:

Things will start to get interesting because, for each column of your desired table, you will get several options to concrete your data generation. Let me show you different screens for the same table:

The cool part is that for every option that you tick, you will get a preview at the bottom of the data that will be generated, instead of ticking a ton of checkboxes to realize at the end (and probably after wasting quite some time) that it is not what you intended to do.

You can even mess around with regular expressions - for string columns or a myriad of options available. This is a characteristic that truly sets apart the tool from anything I’ve ever seen before!

Now, when you are comfortable with the set of options for all the columns that you want to populate with data, then click on the green arrow:

In the next screen, you are given the option to execute the data population script against the selected database, save the script file to a location in your machine, or open it within the tool to take a look at it:

Important Note: You’ll notice that the tool will tell you that the trial version only allows the generation of 50 rows per table, so don’t get confused if you’re not getting the thousands of results that you configured.

Regardless of the option you choose, the tool will give you one additional screen to configure the resulting script, along with the option to backup your target database in case you think that your particular use case requires it. In every tool that I’ve reviewed from Devart, I always get a smile on my face whenever I see that they give the option to the DBA to backup the data in case something goes wrong, thanks Devart!

Before getting to the final step, you’ll get a warning if you selected that you want to truncate your table. That’s a very user-friendly approach because the end-user could probably tick the “truncate data” checkbox by mistake, choose not to backup the data, and have the perfect recipe for an awful day (with several roundtrips to the bathroom for sure).

Besides any warning, you will also be able to see the action plan that the tool intends to execute:

The final step will be the output of the option you previously selected. In my case, I wanted to view the generated script in the built-in editor:

So after clicking “Execute”, I can see the backup file successfully generated, and as advertised, my 50 sample records are right there in my database:

Final thoughts
● As we saw during the development of the post, the trial version of the tool will put a cap on the amount of sample data generated. However, the full functionality, in my opinion, is worth every penny!

● A tool like this one can ease a lot of the process of testing how your database server will serve the required queries against databases that have grown to a large scale; this will allow you to implement a strategy well in advance and adopt a proactive measure instead of a reactive one.

● If you want to get a deeper dive into the tool, you can check the corresponding official documentation center.

● I’m not going to lie, and I had a jaw-dropping moment when I saw all the options available to populate each specific field. It’s the first time I’ve seen something like this, so a round of applause for the Devart team!