T-SQL以特定顺序添加列。

Im a bit new to T-SQL, Coming from a MySQL background Im still adapting to the different nuances in the syntax.

Im对T-SQL有点陌生，来自MySQL背景的Im仍然适应不同的语法差异。

Im looking to add a new column AFTER a specific one. I've found out that AFTER is a valid keyword but I don't think it's the right one for the job.

我想在一个特定的专栏之后再添加一个新的专栏。我发现AFTER是一个有效的关键字，但我认为它不适合这个工作。

ALTER TABLE [dbo].[InvStockStatus]
ADD [Abbreviation] [nvarchar](32) DEFAULT '' NOT NULL ;

This is my current query, which works well, except it adds the field at the end of the Table, Id prefer to add it after [Name]. What's the syntax im looking for to represent this?

这是我的当前查询，它工作得很好，除了在表的末尾添加字段外，Id更喜欢在[Name]之后添加字段。我想要的语法是什么?

8 个解决方案

#1

You can't do it like that

你不能那样做

for example if you have a table like this

例如，如果你有一个这样的表

create table TestTable(id1 int,id3 int)

and you want to add another column id2 between id1 and id3 then here is what SQL Server does behind the scene if you use the designer

你想要在id1和id3之间添加另一个id2列，如果你使用设计器，这就是SQL Server在后台做的事情

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_TestTable
    (
    id1 int NULL,
    id2 int NULL,
    id3 int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestTable SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
     EXEC('INSERT INTO dbo.Tmp_TestTable (id1, id3)
        SELECT id1, id3 FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestTable
GO
EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
COMMIT

As you can see if you have a lot of data this can be problematic, why does it matter where the column is located? just use

正如您所看到的，如果您有大量的数据，这可能是有问题的，为什么列在哪里重要呢?只使用

select col1,col2,col3 from table

#2

While from a functional database perspective it is correct that the tuples can appear in any order.

从功能数据库的角度来看，元组可以以任何顺序出现是正确的。

However a database does not exist inside a vacuum. There is always a human that will want to read the table schema (dbas, devs) and get there head around it and maintain or write queries against it.

然而，数据库并不存在于真空中。总有人希望阅读表模式(dba、devs)，然后了解它，并对它进行维护或编写查询。

In the past, I've used conventions for a table's columns such as ordering a table with

在过去，我曾对表的列使用约定，如使用表排序

primary key(s) first
主键(s)
then foreign keys
然后外键
then frequently used columns
然后经常使用的列
then other columns
然后其他列
and lastly audit related columns
最后是审计相关栏目

and these help when scanning a table. Unfortunately, it appears you have to jump through hoops to maintain any order, so now I have to question whether it's worth having and maintaining these conventions. My new rule is just add it to the end.

这些在扫描表格时很有用。不幸的是，为了维持秩序，您似乎必须跨越重重障碍，因此现在我不得不质疑是否值得拥有和维护这些约定。我的新规则就是把它加到最后。

If you are really worried about order from a readability perspective, you should create your own 'readability' views (perhaps in a different schema) in any order you feel like. You could have multiple views of the same table (one for just the core columns and another including stuff that usually isn't relevant).

如果您确实从可读性的角度担心顺序，那么您应该按照自己喜欢的顺序创建自己的“可读性”视图(可能是不同的模式)。您可以有同一表的多个视图(一个只用于核心列，另一个包含通常不相关的内容)。

It would be nice to be able to be able to re-order columns in SQL Server database diagrams (as a display thing only), but this isn't possible.

能够在SQL Server数据库图中重新排序列(仅作为显示)是很好的，但这是不可能的。

#3

The sequence of columns is really irrelevant in a strict (functional) sense, in any RDBMS - it's just a "nicety" to have for documentation or humans to look at.

从严格的(功能的)意义上来说，列的顺序在任何RDBMS中都是无关紧要的——文档或人工查看只是一种“精细”。

SQL Server doesn't support any T-SQL commands to order the columns in any way. So there is no syntax in T-SQL to accomplish this.

SQL Server不支持任何T-SQL命令以任何方式排列列。所以T-SQL中没有语法来完成这个。

The only way to change that is to use the visual table designer in SSMS, which really recreates the whole table from scratch, when you move around columns or insert columns in the middle of a table.

惟一的改变方法是在SSMS中使用可视化表设计器，当您在表的中间移动列或插入列时，它将从头重新创建整个表。

#4

The schema comparison tools I have seen will create a new table with the desired ordering and then copy the data from the old table to the new one (with some renaming magic to make the new one resemble the old). Given how akward this approach is, I figure there isn't a T-SQL statement to add a new column in a specific place.

我看到的模式比较工具将创建一个具有所需排序的新表，然后将旧表中的数据复制到新表中(使用一些重命名的技巧使新表与旧表相似)。考虑到这种方法的好处，我认为没有T-SQL语句可以在特定位置添加新列。

#5

You should always add fields only at the end. You should select fields in the order you want, but never restructure an existing table to add a column inthe middle. This is likely to break some things (where people did dumb things like select * or inserts without specifying the columns granted people shouldn't do those things, but they do).

您应该始终只在末尾添加字段。您应该按照您想要的顺序选择字段，但不要对现有表进行结构调整，以在中间添加一个列。这很可能会破坏某些东西(比如人们做了一些愚蠢的事情，比如选择*或插入，但没有指定人们不应该做这些事情的列，但是他们做了)。

Recreating the table can be a long time-consuming process for no gain whatsoever and can cause lots of user complaints and lockups while it is going on.

重新创建表可能是一个耗时很长的过程，而且没有任何收获，并且在执行过程中会引起大量用户抱怨和锁定。

#6

Technically, or perhaps I should say, academically, the order in which columns are added to a table, or the order in which they are stored in the database's internal storage model, should not be of any concern to you. You can simply list the columns in the Select clause of your SQL queries to control the order that columns or computed expressions appear in the output of any query you run. Internally the database is free to store the actual data any way it sees fit to optimize storage, and or help align data elements with disk and/or memory boundaries.

从技术上来说，或者从学术上来说，我应该说，在表中添加列的顺序，或者在数据库的内部存储模型中存储列的顺序，都不应该引起您的关注。您可以简单地列出SQL查询的Select子句中的列，以控制列或计算表达式在运行的任何查询的输出中出现的顺序。在内部，数据库可以*地存储实际的数据，以它认为适合的任何方式来优化存储，或者帮助将数据元素与磁盘和/或内存边界对齐。

#7

This is a safe work around without using temp table. After you add the column towards the end, simply go to SQL Sever Management Studio. Click on the table, select -> Design (or Modify) and drag the last column to where ever position you want.

在不使用临时表的情况下，这是一个安全的工作。在将列添加到末尾之后，只需访问SQL Sever管理Studio。单击该表，选择->设计(或修改)并将最后一列拖到您想要的位置。

This way you do not have to worry about loosing data and indexes. The only other option is to recreate the table which can be problematic if you have large data.

这样您就不必担心丢失数据和索引了。惟一的另一种选择是重新创建表，如果您有大数据，这可能会带来问题。

This answer is for helping other people and not intended to be accepted as answer.

这个答案是为了帮助别人，而不是被别人接受。

#8

A fair amount of time has passed since this question was posted but it's worth noting that while the underlying raw SQL code to place columns in a specific order hasn't changed the process of generating scripts to do it is taken care of if you choose to use SQL Server Data Tools within Visual Studio to manage your database. The database you deploy to will always have the columns in the order that you specify in your project.

相当数量的时间已经过去了因为这个问题发布,但值得注意的是,虽然底层原始SQL代码将列在一个特定的顺序没有改变的过程生成脚本就是照顾如果您选择在Visual Studio中使用SQL Server数据工具来管理数据库。部署到的数据库将始终按照项目中指定的顺序拥有列。

#1