Sql-server – SSMS gives error if column name is “MERGE” where as other keywords works

sql-server-2008-r2ssmstable

IN SSMS create table if we include sql keywords in column name then it will be wrapped around square brackets "[]" automatically and the table gets created without any error generally.

But if we include some keywords such as merge then SSMS will not insert square brackets around it and while saving table error is shown as

'Table_2' table
- Unable to create table.  
Incorrect syntax near the keyword 'MERGE'.
Incorrect syntax near the keyword 'NULL'.

Best Answer

This is yet another flaky bug in yet another inconsistent and unreliable visual designer in SSMS. My suggestions, in this order:

Stop using the visual designers. Learn CREATE TABLE and other DDL syntax - not only does this break your dependence on terrible tools, it is also much easier to place these in source control, send them to colleagues, etc. When you are relying on pointing and clicking in a UI, your documentation of what you did will have to resort to Camtasia or other screen recording tools.
Stop using reserved words for column names.
If you must use keywords, put the brackets around them yourself.

I filed a bug on Connect, but don't hold your breath. Many of these buggy components of SSMS have been barely touched in years, and many usability bugs continue to get ignored unless they cause real problems (like data loss).

Related Solutions

SQL Server – Fixing Errors When Attaching a Database in SSMS

This doesn't look like a permissions issue at all, but rather a bug in the UI.

If you are using Management Studio Express, then you should consider two options:

Ensuring you are on the most recent service pack (yes they apply to client tools as well), and perhaps the most recent cumulative update as well.
Many of the bugs in the 2008/2008 R2 version of the tools have yet to be fixed, and likely never will be. Never mind that the Express version has limited functionality. So stop using the crippled Express version from 2008 R2; the fully functional version of Management Studio is now absolutely free as of SQL Server 2012 Service Pack 1. Yes, you can manage all of your 2005, 2008, 2008 R2 and 2012 instances with the newest version of the tool.

In fact, as it turns out (thanks @Justicator), this bug has only been fixed in the 2012 version of the tools, so looks like option (2) is your best bet (or, just not using the UI for this type of task).

SQL Server – How to Execute SQL Statement Based on SQL Server Version

You can use dynamic SQL

I mean first check the version

Then build your SQL statement using a string variable, for example nvarchar(max)

Then execute it by sp_executeSQL

I think following script can work for this task

-- check for sql server version
declare @sql nvarchar(max)
if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) >= 10 

set @sql = N'CREATE unique nonclustered index ix1_table ON [table] (column1, column2)
        WHERE column1 is not null and column2 is not null'

        ELSE

set @sql = N'CREATE nonclustered index ix1_table ON [table] (column1, column2)'


exec sp_executeSQL @sql

Best Answer

Related Solutions

SQL Server – Fixing Errors When Attaching a Database in SSMS

SQL Server – How to Execute SQL Statement Based on SQL Server Version

Related Question