How to sort sql columns in alter table

Asked

Viewed 1,563 times

2

I have a change to make in the table Inclusão de colunas the doubt is have as I set the order when I am making the change in sql server 2008 ?

ALTER TABLE dbo.Arquivos 
ADD IdTipoDocumento int NULL, -- order 3
    NomeInterno nvarchar(200) NULL, -- order 4
    DirArquivo nvarchar(200) NULL,-- order 5
    DescrArquivo nvarchar(max) NULL, -- order 6
    LarguraArquivo int NULL, -- order 12
    AlturaArquivo int NULL, --  order 13
    FlagAtivo bit NULL, -- order 14
    IdUsuarioUpload int NULL, -- order 15
    DtInclusao datetime NULL -- order 16

inserir a descrição da imagem aqui

sql-server

  • I have no idea if you have how to do this, but one way would be to create a temporary table and recreate your table in the correct order, and do the insert of table records dbo.files in the temporary, something like this.

  • I know how it works... but to give a job, I thought I’d have something easier...

1 answer

3


Yes, only by SQL Server Management Studio. By Transact-SQL this is not possible.

For the database, the order of the columns is irrelevant. This functionality can even be considered cosmetic, since it does not affect at all the performance or functioning of the table.

  • it might be interesting to mention that management studio will recreate the table and if something goes wrong, the table may end up in limbo or corrupted.

  • 1

    Gee. Really? I just need the source.

  • @Ciganomorrisonmendez Yes, in terms of performance does not even affect, it was something more presentable for my application even. I just didn’t know if there was anything I could do on my own alter table. Vlw.

  • 2

    hauhauha I was going to tell to create a virtual table, export to it the data, drop the table and recreate it, then insert the data from the virtual table ;P must be what management does hauauha

  • @Rod’s possible to do it that way, but it’s a little tricky. What SQL Server Management Studio does behind to order... just talking to Bill Gates

  • 1

    @Rod, this is exactly what the Management Studio, you can check this by opening Design to a table, re-ordering the columns and instead of saving the change use the option to Gerar Script de Alteração, it will give you a script that creates a temporary table, copies the data to this table, deletes the previous one along with the relationships and renames the temporaria and recreates the relationships.

  • 2

    @Ciganomorrisonmendez, experimente do the following: Ferramentas -> Opções -> Designers, then check the option Evitar salvar alterações que exijam recriação de tabela... If you do this, you will not be able to save changes to the Table Design like changing the order of the columns, turning a NULL type into NOT NULL, etc... If you do this and try to save, Management Studio will inform you that you cannot proceed.

  • @Tobymosque Sensational tip. I won’t even supplement the answer to give you the proper credit.

Show 3 more comments

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.