remove unnecessary square brackets

torsten.strauss

Hi Redgate Team,

I guess that the feature add/remove square brackets / remove unnecessary square brackets are not working as intended.
I expect that the square brackets for reserved words will not be removed, otherwise the feature makes no sense for me.

Thanks for fixing!

Torsten
MVP | FoRG

CREATE TABLE test

(

password nvarchar(100) NOT NULL

  , delay int NOT NULL

  , object AS sysname

);

Dan_J

Hi @torsten.strauss

Thanks for reaching out to us regarding this.

Just so we can ensure we understand your query here, it would be useful if you could provide 'before' and 'after' scripts. Is this something you'd be able to do for us?

Also, just to mention that the SQL Prompt documentation states the following regarding the square brackets options/settings: 'SQL Prompt can add square brackets around all identifiers or remove square brackets from all identifiers that don't need to be delimited.'

torsten.strauss

Hi Dan,

thanks for looking into this!
According to Database Identifiers - SQL Server | Microsoft Docs Classes of Identifiers, reserved keyword must be surrounded by square brackets.

When you format the code

CREATE TABLE test ( [password] nvarchar(100) , [delay] int NOT NULL , [object] sysname , [test1] CHAR(1) , test2 CHAR(1) );
SQL Prompt removes the brackets from the (first three) reserved words - this shouldn't be the case.
Instead the code should look like this:

CREATE TABLE test ( [password] nvarchar(100) , [delay] int NOT NULL , [object] sysname , test1 char(1) , test2 char(1) );
I assume that this is not intented since SQL prompt will not remove the brackets from the following code which is ocrrect following the Code recommendations from Microsoft.

CREATE TABLE test ( [password test] nvarchar(100) , [123delay] int NOT NULL );<br>
BTW. The remove square brackets feature currently works in Visual Studio only but not in SSMS anymore (last update)

Thanks for reading!

Torsten

ostandage

Hi @torsten.strauss

The remove square brackets functionality removes square brackets where they are not necessary for the script to execute correctly. While this is sometimes against the Microsoft documentation best practice, this feature is completely optional within SQL Prompt and it is up to the user to decide whether they would like square brackets or not. 

The reason the last code block you shared does not remove the square brackets is because the first column has a space in the name and hence requires brackets, and the second column starts with a number and hence requires square brackets.

CREATE TABLE test
(
	[password test] nvarchar(100)
  , [123delay] int NOT NULL
);

I was able to replicate the expected behaviour in the latest version of SQL Prompt in SSMS, please feel free to open a support ticket if you are experiencing difficulties. It is worth noting that the behaviour between SSMS and VS for refactoring should be identical.

Thanks, 

Owen

torsten.strauss

Hi Dan!

The reason the last code block you shared does not remove the square brackets is because the first column has a space in the name and hence requires brackets, and the second column starts with a number and hence requires square brackets.

You are right, and that was the reason why I mentioned that SQL Prompt considers MS policies in this case, but not for reserved words ...

ostandage

Hi, 

As far as I can tell from the documentation, password, delay, and object are not reserved keywords in transact SQL. They appear to be keywords, however they are not reserved words as mentioned in the Database identifiers article.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver15

Thanks, 

Owen

torsten.strauss

You are right - I wonder why these words are highlighted in a different color then ...

Dan_J

Hi @torsten.strauss

Reading about this online, it appears various keywords were chosen by Microsoft and placed into a list within the query editor syntax coloring file, hence password, delay, and object are highlighted in blue.

You could apply square brackets around those words to stop them being highlighted, but of course that isn't ideal.