Feature: selecting from pop-up

Pressing space after a SELECT keywords results in a pop-up where I can select fields either by mouse or by right arrow key. Typing while the pop-up is visible applies a filter to the fields. But after selecting a field either by right arrow key or by clicking on its checkbox the filter is cleared and if I want to select several fields that can be narrowed down by a filter I have to type it over and over again.

It would be very helpful if that filter was still in effect after the right arrow/mouse click. Or to have a switch that alters its behavior in case some people like it better this way.

I'm afraid this is not currently possible. When you press comma key after last inserted column name, the entire popup window is internally rebuild, it appears in the same spot, but it’s a different type of popup within a different context, and it doesn't know anything about what you typed in the previous popup.

Yes, that happens when I press comma.

But actually I do not press comma (I press the right arrow key), and comma is not even inserted until another field is selected. The pop-up does not even flicker so I guess it does not get rebuilt and it certainly has some knowledge about the context because the checkbox next to the item gets ticked. I can wander up and down with cursor keys (or even the mouse) and select and deselect fields (the checkboxes change accordingly) without having the same field appear more than once. They are inserted or removed. Neither does the pop-up vanish.

I know nothing about the internal mechanism but it seems to me that it is the same pop-up with the same context and with the filter cleared.

I still don't understand the issue. If you type comma after picking a column name using mouse or arrow keys while the popup is still on the screen, don't you get the filter reset? When I do it on my system, I can see all objects and columns again?

Sorry, you're right, I might have been more into details. Here comes a test case and code for a test table (that could be a heap of fields from a bunch of joins as well):

Code:

USE tempdb
GO

CREATE TABLE test_popup
(
arbitrary_field_1 INT, arbitrary_field_2 INT, arbitrary_field_3 INT, arbitrary_field_4 INT, arbitrary_field_5 INT, arbitrary_field_6 INT, arbitrary_field_7
INT, arbitrary_field_8 INT, arbitrary_field_9 INT, arbitrary_field_10 INT, arbitrary_field_11 INT, arbitrary_field_12 INT, arbitrary_field_13 INT,
arbitrary_field_14 INT, arbitrary_field_15 INT, arbitrary_field_16 INT, arbitrary_field_17 INT, arbitrary_field_18 INT, arbitrary_field_19 INT,
arbitrary_field_20 INT, arbitrary_field_21 INT, arbitrary_field_22 INT, arbitrary_field_23 INT, arbitrary_field_24 INT, arbitrary_field_25 INT,
arbitrary_field_26 INT, arbitrary_field_27 INT, arbitrary_field_28 INT, arbitrary_field_29 INT, arbitrary_field_30 INT, arbitrary_field_31 INT,
arbitrary_field_32 INT, field_wanted_1 INT, field_wanted_2 INT, field_wanted_3 INT, field_wanted_4 INT, field_wanted_5 INT, field_wanted_6 INT,
field_wanted_7 INT, field_wanted_8 INT, other_arbitrary_field_1 INT, other_arbitrary_field_2 INT, other_arbitrary_field_3 INT, other_arbitrary_field_4 INT,
other_arbitrary_field_5 INT, other_arbitrary_field_6 INT, other_arbitrary_field_7 INT, other_arbitrary_field_8 INT, other_arbitrary_field_9 INT,
other_arbitrary_field_10 INT, other_arbitrary_field_11 INT, other_arbitrary_field_12 INT, other_arbitrary_field_13 INT, other_arbitrary_field_14 INT,
other_arbitrary_field_15 INT, other_arbitrary_field_16 INT, other_arbitrary_field_17 INT, other_arbitrary_field_18 INT, other_arbitrary_field_19 INT,
other_arbitrary_field_20 INT, other_arbitrary_field_21 INT, other_arbitrary_field_22 INT, other_arbitrary_field_23 INT, other_arbitrary_field_24 INT,
other_arbitrary_field_25 INT, other_arbitrary_field_26 INT
)
GO

Have the following in the editor:

	Code:
	SELECT\| FROM dbo.test_popup tp

And now forget about coma. It is never typed. You press space and you have a popup. You only want to select fields that start with "field_wanted_", so you type "f". This filters out all the other fields, so you select the first one with the right arrow key and you would like to select all of them except, eg. "field_wanted_5". Since there is a context menu item for selecting them all, that would not be a problem, though it is only triggered by right click, which is rather inconvenient. Doesn't matter.

But the filter is cleared and the cursor is thrown back to the first field. You see all of them, once again. So you either have to type the filter again, or scroll down in the field list using arrow keys or page up/down. I admit, it is not a hard work to do it using this test table, they are all in the neighborhood. But imagine having a join of a dozen tables, each having a "valid_from" datetime field and let's say, about a dozen or more other fields. Using "valid_from" as filter those field will be next to each other (er... I mean under, under each other). When unfiltered, those fields may be miles away from each other, having to scroll down in search for them for ages.

By the way, the home/end keys make the popup vanish. Is that on purpose?

Thank you, it makes sense now. I was experimenting with the popup appearing right after the SELECT keyword and having a different behavior.

So, if you type SELECT and hit space (there is no FROM clause yet in the current statement), you get a different popup. Type letter t, the list is filtered to show object names starting with letter t. Now select test_popup table and press Arrow Right key to expand the selected table. Type backspace and then type letter f. Here you are going to get test_popup expanded and only columns starting with f listed. And that filter is sticky. You can use mouse or keyboard to select multiple columns.

PS. Internally this is not the same type of popup that is handled in case of SELECT ... FROM construct
PS. End and Home keys are handled differently on purpose to allow moving cursor to the end/begging of the text line, not list in the popup. To jump to the beginning/end of list use ALT+End and ALT+Home

Silly me. I tried that with Shift and Control without any success so I gave up. I don't know why did not try Alt. I was missing this for months :) Thank you very much.

The tip on selecting fields without having a FROM clause will also come in handy some days but it obviously does not work when selecting from joined tables. If the SELECT ... FROM type popup could have the same sticky filter, it would be the most welcome.