[SA 9.0.199 Pro] - Trasformation of Keywords

[SA 9.0.199 Pro] - Trasformation of Keywords

I have two lines in Code Formatting > Keywords

	Code:
	IF EXISTS (SELECT 1 FROM\|) IF NOT EXISTS (SELECT 1 FROM\|)

I noticed that occasionally, selecting those keyword (phrases) will result in strange transformation where the selected item will end up in the editor as

	Code:
	IF EXISTS (SELECT 1 INFORMATION_SCHEMA) IF NOT EXISTS (SELECT 1 INFORMATION_SCHEMA

Any hints on why this happens?

This is a very interesting application for the Code Formatting > Keywords. I tried to reproduce that issue and failed so far. I'm now asking for help from the team.

Here is the explanation I was given.

That effect is possible and would be correct if you have in Auto Complete settings Name Matching Method set to something like "Name Contains Characters from Key String, Order by Best Match" In that case "FROM|" is matched to INFORMATION_SCHEMA. if INFORMATION_SCHEMA is the only match, and there are no other choices, it gets inserted automatically.

To avoid that happening, please add a space before |

Wouldn't it make more sense to make these code snippets?

"IE" is "If exists... " autocompletion
and "INE" is "if not exists" autocomplete

Just curious why you chose a keyword over a snippet for these?

	SysOp wrote:
	This is a very interesting application for the Code Formatting > Keywords.

Actually, it's stolen :) The idea comes from factory default containing a whole bunch of similar ones, mostly for those funny functions of SQL Server that incorporate two pairs of brackets like:

	Code:
	cume_dist() over (order by \|) cume_dist() over (partition by \| order by) dense_rank() over (order by \|) . . .

	SysOp wrote:
	That effect is possible and would be correct if you have in Auto Complete settings Name Matching Method set to something like "Name Contains Characters from Key String, Order by Best Match" In that case "FROM\|" is matched to INFORMATION_SCHEMA. if INFORMATION_SCHEMA is the only match, and there are no other choices, it gets inserted automatically.

Well, that explains why it worked in one database and not in another. I wanted to spare the surplus keypress so that upon pressing space after inserting IF EXISTS (SELECT 1 FROM|) I'd get the popup. I figured that if I made it put the cursor right behind FROM I'll achieve that goal. I wasn't aware that having the | gives the popup and having a space in front of it will actually do exactly what I wanted with even one less keys pressed.

Not the first time auto-completion or auto-expansion causes confusion or trouble. But at least it works very well this time. Thumbs up.

An other case of annoying auto-expansion is when I type in a popup to filter schemas and when there's only one it gets expanded. But the filter is left at schema level and not the stuff below, hence if I want to filter objects I first have to re-collapse schema and expand it again to type the filter for objects. Having it auto-expand would be nice if it auto passed the filtering to the level expanded as well, but then again, it would require exceptional reflexes to switch from typing the filter for schema to typing the filter for the object in middle of filtering the former the instant the auto-expansion happened. Also, works for schemas but it does not for databases, eg. typing cat will expand schema catalog as soon as t is pressed, but the database SSISDB won't expand when pressing d leaves it as the lone survivor.

Is there a way to disable that auto-expansion?

	Mindflux wrote:
	Wouldn't it make more sense to make these code snippets? "IE" is "If exists... " autocompletion and "INE" is "if not exists" autocomplete Just curious why you chose a keyword over a snippet for these?

Not really in my case. I like my snippets triggered with their last character. Having them assigned to such short snippet names would frequently trigger false positives (eg. whenever I'd have a table that aliases to ie or ine I'd go through hell to enter that alias). Putting them in Keywords gives the opportunity to select them after typing if or just keep typing something else. It also gives an instant popup with no sugar as soon as it is selected.
I know I'm crazy but I'm just that performance/productivity fanatic :)