Can I write SQL using speech recognition?

Question

I have wrist pain when I type and I would like to start writing SQL statements, stored procedure, and views using speech recognition.

Answer 1

Yes. SQL is well-suited to speech recognition (as well-suited as a programming language can be, that is), given it's limited vocabulary and sentence-like structure. Aside from formatting the SQL so that it looks nice, I can dictate it much faster than typing. Dictating code isn't for everyone, however. It can be quite frustrating in the beginning. The people who try this and stick with it will probably be those who have no other choice.

I use Dragon NaturallySpeaking 10 Professional. The Professional version has the tools that are needed to create a custom vocabulary like this. Version 9 should work fine, also. It's expensive, so try to get the company you work for to pay for it if possible. Get a decent headset microphone also. The one that comes with NaturallySpeaking isn't good enough (but you may want to try it first to see if it works for you). KnowBrainer is a good place for microphone recommendations.

2009-01-05 Update: I have added tips below specific to dictating in SQL Server Management Studio.

2012-01-04 Update: I have been keeping track of Microsoft's WSR for quite a while now, hoping tools would be added to easily create a completely custom vocabulary from scratch like I am doing in this tutorial with NaturallySpeaking. Unfortunately, it appears that this can only be done through the API (SAPI). I don't have the time to write that code, so I will continue to use NaturallySpeaking to write code until something better comes along.

Preparation

Clean up your database names and code

Dictating "SELECT PT_17, PT_28, PT_29 FROM HIK.dbo.PATINFO" would be a pain in the butt, but I guess it would be possible. You would have to set a lot of pronunciations, since NaturallySpeaking would have no idea how "PT_17" would sound. This would be preferable for dictation:

SELECT Patient.FirstName, Patient.MiddleName, Patient.LastName FROM Claim.dbo.Patient AS Patient WHERE Patient.LastName LIKE '%smith%' 

I switched to my TSQL vocabulary to dictate the above statement. Everything up to the LIKE statement is spoken just as it appears. '%smith%' was dictated as "open-single-quote percent-sign sierra mike india tango hotel percent-sign close-single-quote [PAUSE] compound-that". Using consistent table aliases and always preceding fields with them helps improve accuracy, since NaturallySpeaking keeps statistics of how often one word appears near another.

Create a word list of SQL keywords

Put one word on each line. You can optionally follow a word with a backslash (\) and a pronunciation. NaturallySpeaking uses a small backup dictionary of words to determine the pronunciation of words you add to a vocabulary, so it has no problem figuring out how SELECT, FROM, and WHERE are pronounced. It can sometimes figure out a compound word, and it makes its best guess for something like XACT_ABORT. I would provide pronunciations for cases like these. The database you use will determine what words the list contains - check your documentation for a list of keywords. Your list will look something like this, but be much longer.

SELECT WHERE FROM XACT_ABORT\exact-abort MAXDOP NOLOCK\no-lock LEN RETURNS CURSOR MONEY     

Also add these words

\New-Line \New-Paragraph \All-Caps \All-Caps-On \All-Caps-Off \Cap \Caps-On \Caps-Off \No-Caps \No-Caps-On \No-Caps-Off \No-Space \No-Space-On \No-Space-Off \space-bar \tab-key a\alpha b\bravo c\charlie d\delta e\echo f\foxtrot g\golf h\hotel i\india j\juliet k\kilo l\lima m\mike n\november o\oscar p\papa q\quebec r\romeo s\sierra t\tango u\uniform v\victor w\whiskey x\xray y\yankee z\zulu PM AM one two three four five six seven eight nine ten eleven twelve thirteen fourteen fifteen sixteen seventeen eighteen nineteen twenty thirty fourty fifty sixty seventy eighty ninety hundred thousand million billion trillion 

Keep this list around, since you'll probably modify it several times and re-create your vocabulary to get it the way you like it.

Create a word list of your database object names

This is how I do it in SQL Server:

SELECT DISTINCT * FROM  ( SELECT DISTINCT [name] FROM Database1.[dbo].[sysobjects] WHERE xtype not IN ('F', 'S', 'PK', 'D', 'UQ')  UNION  SELECT DISTINCT column_name AS [name] FROM Database1.information_schema.[columns] UNION SELECT DISTINCT [name] FROM Database2.[dbo].[sysobjects] WHERE xtype not IN ('F', 'S', 'PK', 'D', 'UQ')  UNION  SELECT DISTINCT column_name AS [name] FROM Database2.information_schema.[columns] ... ) AS UnionTable 

Copy and paste the results into a text file.

Create pronunciations for your database object names

Use the same format for pronunciations as listed above. An easy way to create these is to use a regex search and replace function. In SQL Server Management Studio or Visual Studio the following (non-standard) regex will create pronunciations for two word mixed case names.

Find: ^{[A-Z][a-z]+}{[A-Z][a-z]+}$ Replace: \0\\\1-\2 

Review the pronunciations and clean up anything that doesn't look right. For acronyms, ASP becomes `A.S.P.'. Keep this list around, as well. If you decide to make vocabularies for other programming languages, you will probably include these words if you're a database developer.

Create a text document that contains all of your SQL code (views, procedures, etc.)

SQL Server:

SELECT * FROM Database1.dbo.[View] UNION SELECT * FROM Database1.dbo.Routine UNION SELECT * FROM Database2.dbo.[View] UNION SELECT * FROM Database2.dbo.Routine  ... ORDER BY [Name] 

Remove comments and literal strings. Regex search and replace works well for this.

Build your vocabulary

Install NaturallySpeaking and create a new user if you have not already.

Create a new vocabulary

Click on "NaturallySpeaking | Manage Vocabularies...". Click New. Name the vocabulary something appropriate, such as "SQL". Base it on "Base General - Empty Dictation". When it asks you if you want to scan your email or documents, click cancel.

Import words

Click "Words | Import". Add the two word lists you created and import them.

Adapt to writing style

Click "Tools | Accuracy Center". Click "Add words from your documents to the vocabulary". Use the default settings, and select the document you created which contains your code.

Try dictating some SQL

The first thing you'll probably want to dictate is a select statement. Keep in mind that SELECT is what you use to begin a command in NaturallySpeaking that selects text. Because of this, you'll want to say "Cap" before dictating it so NaturallySpeaking doesn't get confused. That's it. Well, at least enough to get you started. Modify your word lists, pronunciations, and word properties as needed. There are other things you can do to increase accuracy and the speed at which you can dictate. As I think of them, I will edit this post and add them here.

Tips for dictating into SQL Server Management Studio

If you dictate into SQL Server Management Studio, you may notice very slow performance. Try the following to alleviate this:

• Turn off all toolbars (create macros to access commonly used functionality)
• Keep as few panes and documents open as possible
• Keep only one database open at a time
• Hide search results after you're done with them (Ctrl+R)
• If all else fails, close and reopen management studio
• Display the tab stops in the edit window to make it easier to format your SQL.

Query Analyzer from SQL Server 2000 does not have these issues.