Sir-Auto-Completes-A-Lot a.k.a. how to break IntelliSense, SQL Prompt and SQL Complete

I like big schemas and can not lie
You other DBAs can’t deny
That when studio opens up with a funky ass database
and a million objects in your face
It gets hung, your coding gets rough
’cause you notice your base was stuffed
Deep in the schema it’s searchin
I’m stuck and IntelliSense is lurchin
Oh baby, I wanna finish this query
But this lag is makin’ me weary
My co-workers tried to warn me
Cause this SQL rhyme got way to corny

So coders (Yeah!) Coders (Yeah!)
Can you code better than Hodor (Hell yeah!)
Then fire it up. Type it out.
Even SAN admins got to shout
DBA got back…….
to typing out queries because auto complete tools aren’t really working out
.

I have a love/hate relationship with some of my databases. I love hating them as they are great fodder for testing/breaking tools/procedures, blog posts and most importantly Sir Mix-A-Lot parodies. I’ve put up with IntelliSense stalling and even locking up SSMS when I’m writing a query against one of the 162 thousand tables containing 3.3 million columns. I know how to turn it off, but I don’t have the human RAM to hold the names of everything.

20150421-TandC
This wouldn’t fit in a SMALLINT, but maybe it was really important to save those 2 bytes and not go INT.

IntelliSense is far from perfect for me here so I tried out a few other options, Red Gate’s SQL Prompt and ApexSQL Complete. Both struggled in my setup and both had some very cool features. This is a hot take and not a review/recommendation, your mileage may vary. I haven’t reached out to support yet for either product or fully explorer all of their features.

SQL Prompt
Downloading a trial version and installing was a snap. It shows up as a tool bar and menu item. When you connect to a database SQL Prompt loads objects or at least it tries to. 20150421-LoadingThis is where I hit my first problem. The loading worked great on database with a small number of objects, but once I switched to a large db I ran into the following 2 errors, OutOfMemoryException and timeout. The timeout period is around 1 minute and I haven’t found a way to adjust this.

20150421-PromptErrors

The prompting post errors were limited to the database level and it appeared that none of the tables had been loaded. I know the names of my databases and SSMS already has a cute little drop down to show this. I don’t think I need help at this level.

20150421-Tables
I need help on a deeper level

Promptless Prompt aside I like the Tab History, formatting and expanding wildcards. Expanding out my SELECT * when the table has over 200 columns is pretty great. Especially when those columns often use spaces or odd characters. I kid you not, I have a column that has both and & and ? IN THE NAME!!!

ApexSQL Complete                                                                                                                Complete had the same issues with loading a large number of objects. SSMS returned a busy message and objects weren’t loaded. Once I started to write out my query Complete threw the error, “An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown.”

20150421-Apex
Why load table names when you can load random code snippets

I uninstalled Apex without getting to deep into its features, but I did like Auto aliasing. On a small db Apex would automatically tack on an alias to your table. I once had a coworker who would alias their tables in alphabetical order. The first table would be “a”, the second “b” and so on a so forth. Apex would have been a vast improvement there.

#SQLNewBlogger
Thanks again Ed Leighton-Dick (T|B)

Advertisements

2 thoughts on “Sir-Auto-Completes-A-Lot a.k.a. how to break IntelliSense, SQL Prompt and SQL Complete

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s