How to change column order in a table using sql query

Sql server internally build the script. It create a temporary table with new changes and copy the data and drop current table then recreate the table insert from temp table. I find it from "Generate Change script" option ssms 2014. Script like this.


/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_emps
    (
    id int NULL,
    ename varchar(20) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_emps SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.emps)
     EXEC('INSERT INTO dbo.Tmp_emps (id, ename)
        SELECT id, ename FROM dbo.emps WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.emps
GO
EXECUTE sp_rename N'dbo.Tmp_emps', N'emps', 'OBJECT'
GO
COMMIT