SQL Server Intellisense

Given the following code:

	Code:
	CREATE TABLE elektra.kartyak ( id_kartya BINARY( 8 ) NOT NULL ,id_szamla BINARY(20) NOT NULL ,CONSTRAINT PK_kartyak PRIMARY KEY CLUSTERED( id_kartya ASC ,datum ASC ) ,CONSTRAINT FK_id_szamla FOREIGN KEY (id_szamla) REFERENCES elektra.szamlak ( id_szamla ) )

According to MSSQL syntax, SQL Assistant should recommend an opening bracket when I press space after keyword "KEY" or "CLUSTERED" just as it does after "CREATE TABLE" (actually, the bracket should be proposed after the table name) but it suggests a closing bracket (or the keyword "REFERENCES" in case of "FOREIGN KEY") instead. This behavior is not user-editable, is it?

This behavior is not user editable.

Let me try to describe why it does what it does, it may help to understand the internal working. The suggestions are based on the internal "database" of SQL syntax constructs and keywords. SQL is a very loosely defined programming language. The same keywords can be used in different places, have different usage syntax, and different meaning. For the sake of argument, let's consider the following simple example:

	Code:
	CREATE TABLE tab1 ( colA INT NOT NULL FOREIGN KEY REFERENCES tab2 (colA), CONSTRAINT someFK FOREIGN KEY (colA) REFERENCES tab3(colZZZ), [FOREING KEY] AS ("colA" * 2) PERSISTED NOT NULL PRIMARY KEY )

How do you like that? The above SQL is valid, but there are 2 completely different uses of "FOREIGN KEY" with and without CONSTRAINT, with and without column name in brackets after that, and there is also a column with the same name. And how do you like NOT NULL inclusions in a middle of that? Should NOT NULL be used in conditional boolean logic or in constraint definitions?

Now, if you press Ctrl+F1 to open SQL Reference topic and click the little house icon, you will open the SQL Reference index. In the index, expand Keywords section, In that section navigate to FOREIGN and expand that too. You should see several subtopics below. Column Constraint goes first, Table Constraint goes last. Their difference is that the first one describes "FOREIGN KEY REFERENCES" and the other describes "CONSTRAINT …FOREIGN KEY (…) REFERENCES"

When you type " FOREIGN KEY" in the editor, SQL Assistants searches the topics in the reference and it finds Column Constraint as the top most. That's why it suggests use of REFERENCES keyword after FOREIGN KEY.

I hope the above makes sense.

Well, while NOT NULL is ambiguous in its meaning (conditional or constraint), FOREIGN/PRIMARY is only ambiguous in its syntax (though I'm not sure of that anymore, there is so much more to learn :), but yes, that makes sense.

Just to be sure, correct me if I'm wrong. When SQL Assistant builds its suggestions list and finds a keyword it could suggest it only suggests its first variant it finds in internal database (I think I'm wrong about that). According to your examples it also makes its suggestions based on keywords and independently of their context (I might be wrong about that too). I guess it would require and extremely complex analysis to take the context into consideration and that's not worth the effort.

By the way, I found something strange in MSS Management Studio. When I clicked the little house icon, it opened the SQL Reference. Then I clicked the magnifier for search and entered a few letters for lookup. So far it was good but when I pressed backspace it did not delete the last letter entered into the search field but the letter in the editor where the cursor was when the editor had the focus. The cursor could not be seen, it blinked in the search field. I tried the same in Notepad++ but it worked normally there. Could you please check that?

Well, it tries to recognize the type of the entire SQL query, such as CREATE TABLE … PRIMARY KEY or DROP ROLE, etc…, which is ok if the query is complete, and nearly impossible, if it is not. It then searches for keywords before the cursor and for hem searches the index and matches syntax rules for the keywords, in our example, it is FOREIGN KEY for CREATE TABLE. The ambiguity begins when the same keywords can be used in a different context using a different syntax. In that case it picks the first found, which is the case with FOREIGN KEY REFERENSES, not FOREIGN KEY (…) REFERENCES.

As for the backspace, this sounds like an issue we need to address. Thank you for pointing that out. Some SQL editors require special treatment, I guess SQL Management Studio is one of them. I just logged a new issue in our internal tracking system, issue id #10533.

Thank you for explanation, I know now how to use it efficiently.

I see a report in out internal system that the issue with backspace in SQL reference search box was fixed yesterday and should be available in the next released build. Thank you again for the tip. It is also likely that some improvements will be available in that build for in-line text editing in SQL Reference windows. I cannot say right now when the new build is going to be released, target date isn't available yet.