locked
Alter index or table RRS feed

  • Question

  • I have SQL2008 and I'm sys.admin on that server.

    But I can't create alter table or alter index script, it is disabled(grey). If I click on some index, I can't add columns or change index, while on some others, I can.

    Even if I create new index, I can't change it afterwards. Also other users(sys.admins, dbo,) can't change it.

    How is that possible? Does that mean that database is corrupted?

    br, Simon

    Thursday, June 13, 2013 9:56 AM

Answers

  • Some index works but others don't.


    You can't alter primary key indexes, others can be altered.

    Olaf Helper

    Blog Xing

    Thursday, June 13, 2013 11:33 AM

All replies

  • Are you you have Sysadmin,any specific erro you are getting while trying to do ur activity..(alter create)

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, June 13, 2013 10:03 AM
  •  or alter index script, it is disabled(grey).

    Hello Simon,

    That's by design, the only available ALTER INDEX (Transact-SQL) command is to rebuild/reorganize and for this you have separate context menu items.

    All other index changes are done by drop + create.


    Olaf Helper

    Blog Xing


    Thursday, June 13, 2013 10:13 AM
  • But why I can't add columns with SSMS, when I click on properties of index?
    Some index works but others don't. I'm not sure if that is by design?

    With drop and create it works but it is more nice to do with mouse clicks :)
    I have always do.

    Thursday, June 13, 2013 10:19 AM
  • Some index works but others don't.


    You can't alter primary key indexes, others can be altered.

    Olaf Helper

    Blog Xing

    Thursday, June 13, 2013 11:33 AM
  • No, mouse clicks is not nice when it comes to what you have in SSMS. The Table Designer is an awful piece of cr*p. Futhermore, mouse clicks cannot be stored in version control.

    By the way, in many cases you can use CREATE INDEX .... WITH (DROP_EXISTING = ON)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 13, 2013 9:58 PM
  • Olaf, I can't alter also some ordinary indexes. I can't find the pattern, it looks like random. Very strange.

    I use table designer rarely, but index designer quite often. It saves my time and it looks nice to me - but it could be better. Otherwise, it is more or less question of habit. I will have to use TSQL from now on and maybe I change my habit :)

    Monday, June 17, 2013 7:10 AM