Finally had a chance to sit down and play with Datasparc’s DBHawk utility to see what I can do here. One piece of the application’s functionality is table creation. For now I am working with SQL Server. I wanted to see how easy and useful it would be for us out in the industry. Since the tool is more than capable of working with other database types, I’ll be testing some of those as well in the future.
My test environment consisted of using SQL Server 2014 with a remote DBHawk application server. Since this is a web based tool, I wanted to get a feel for the performance as well. All in all, everything went well.
I won’t hide that I can find learning new tools to be a challenge at times, I was concerned that there may be some learning curves here. Pleasantly surprised how easy it was to become familiar with what I was testing. The documentation looks to be helpful, but I wanted to try this without reading it ahead of time.
So, I turned on my database and the application server. Logged in via my web browser and was ready to test creating a new table. It’s crunch time.
A quick summary of my steps (Cliff Notes) are as follows:
- Log into DBHawk.
- Connect to the database to create the new table.
- Create the table with the column definitions.
- Add constraints, keys, and referential integrity.
As you can see, this is not very complicated. Let me break this down with some images on what I went through and what I was thinking.
Connected to my DBHawkPlayground database and right clicked on ‘Tables’ then clicked the ‘Create New’ menu item.
After clicking the ‘Create New’ menu the table wizard came up. I entered the table name and then clicked the ‘Next’ button on the bottom of the wizard. I could see that the tool is going to guide me through the process at this point. Keeping it simple.
After clicking 'Next' it was time to start adding the columns. Bear in mind that this is my first pass through this and was starting to get curious about the SQL being created behind the scenes. The ‘Data Type’ drop down list was checked to see what types where in there. Most of them for SQL Server were available. Didn’t find the DATE type and tried to manually enter it to see what would happen. The application would not allow for a manually entered data type, which is probably a good thing.
Was happy to see the comments section when adding the column. It’s right there for you to work with. For you SQL Server types, this is adding the metadata using the sp_addextendedproperty stored procedure. Lovely!
Added my columns for the new table as you can see below. Played with a couple default values and nullability. At this point I am wondering how, or if I can add indexes, constraints, etc. Not seeing this functionality. Maybe I should have gone through the documentation? Oh well, onward and forward….
Once the columns were ready I clicked the ‘Next’ button again and had an opportunity to add some metadata for the table. Took a look at the SQL generated by DBHawk and gave it my personal Database Administrator review. Was given a chance to save the SQL to disk too. I created the table and it was now listed under the ‘Tables’ node in the tree.
I am still wondering about adding constraints. The add column part in the wizard let me add the default constraints, but I want a foreign key from ManagerID to EmployeeID. Checked the ‘Options’ drop down and found what I needed. Found some other useful options for future use too.
Clicked on the ‘Constraints’ menu and up popped the ‘New Constraint’ wizard. Created a primary key on EmployeeID and then created a foreign key from ManagerID to EmployeeID. The tool went ahead and created a unique clustered index on the primary key.
Quick look at the foreign key creation below. Everything is still straight forward.
There, I was done creating the table the way I wanted too. Checked the generated SMO from Management Studio and everything looked to be in order. Looked at the architecture of the table through the tool as well, just to see how this piece works. The tab layout shown below was nice to work with. It keeps the screen from being cluttered and the table’s details sorted in a user-friendly environment.
There you have it…Table is created and it is time to play with some other features!