Autocomplete - when nolock is used?

jj14

Hi!
I'm using SSMS (when connected to SQL server 2000 database) on Win XP Pro SP 2.
SQL Prompt 3.0.0.868

When I start typing in
SELECT column1, column2, column3 FROM Mytable1 WHERE
as soon as I type the space after the WHERE, I get the list of the columns from Mytable1 for me to select from.

But, if I type
SELECT column1, column2, column3 FROM Mytable1 (nolock) WHERE
I don't get the list of columns to select from. Since our DBAs recommend using the nolock hint when getting data from production environment (and I am not too worried about dirty reads for this table), this is an inconvenience.

Is there an option that can be turned on to allow the autocomplete to work even with nolocks? Any other workaround?

Thanks!

Bart Read

Hi there jj,


That looks like a bug to me. Here's an example from AdventureWorks 2005.

The following doesn't work:

SELECT AddressID,
AddressLine1,
AddressLine2,
City,
StateProvinceID,
PostalCode,
rowguid,
ModifiedDate FROM Person.Address (NOLOCK) WHERE

whereas the following does:

SELECT AddressID,
AddressLine1,
AddressLine2,
City,
StateProvinceID,
PostalCode,
rowguid,
ModifiedDate FROM Person.Address WITH (NOLOCK) WHERE

so my suggestion for now is that when working with table hints you use the WITH keyword. This is obviously slightly irritating when using a single hint where it's optional but it does mean that you get intellisense in the where clause. You could work around this by created a hint called "nl" or something that automatically inserted "WITH (NOLOCK) WHERE" for you.

Hope that helps.


Thanks,
Bart

jj14

Thanks for the quick response Bart.
Is this something that will be fixed in the near future?

Thanks
- Joseph

Bart Read

Hmm... possibly. It depends on whether it's a parser problem or not, which I have a nasty suspicion it may be. We aren't going to be touching the parser for a while since it's embedded in another component. That said, if I can work around it easily enough we might put a fix in.
Thanks,
Bart