You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
> For content related to previous versions of SQL Server, see [Add Columns to a Table (Database Engine)](https://msdn.microsoft.com/en-US/library/ms190238(SQL.120).aspx).
26
27
27
28
28
-
This topic describes how to add new columns to a table in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] or [!INCLUDE[tsql](../../includes/tsql-md.md)].
29
-
30
-
## <aname="BeforeYouBegin"></a> Before You Begin
31
-
32
-
### <aname="Restrictions"></a> Limitations and Restrictions
33
-
Using the ALTER TABLE statement to add columns to a table automatically adds those columns to the end of the table. If you want the columns in a specific order in the table, use [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)]. However, note that this is not a database design best practice. Best practice is to specify the order in which the columns are returned at the application and query level. You should not rely on the use of SELECT * to return all columns in an expected order based on the order in which they are defined in the table. Always specify the columns by name in your queries and applications in the order in which you would like them to appear.
34
-
35
-
### <aname="Security"></a> Security
36
-
37
-
#### <aname="Permissions"></a> Permissions
38
-
Requires ALTER permission on the table.
39
-
40
-
## <aname="SSMSProcedure"></a> Using SQL Server Management Studio
41
-
42
-
#### To insert columns into a table with Table Designer
43
-
44
-
1. In **Object Explorer**, right-click the table to which you want to add columns and choose **Design**.
45
-
46
-
2. Click in the first blank cell in the **Column Name** column.
47
-
48
-
3. Type the column name in the cell. The column name is a required value.
49
-
50
-
4. Press the TAB key to go to the **Data Type** cell and select a data type from the dropdown. This is a required value, and will be assigned the default value if you don't choose one.
51
-
52
-
> [!NOTE]
53
-
> You can change the default value in the **Options** dialog box under **Database Tools**.
54
-
55
-
5. Continue to define any other column properties in the **Column Properties** tab.
56
-
57
-
> [!NOTE]
58
-
> The default values for your column properties are added when you create a new column, but you can change them in the **Column Properties** tab.
59
-
60
-
6. When you are finished adding columns, from the **File** menu, choose **Save***table name*.
61
-
62
-
## <aname="TsqlProcedure"></a> Using Transact-SQL
63
-
64
-
#### To insert columns into a table
65
-
66
-
1. Connect to the [!INCLUDE[ssDE](../../includes/ssde-md.md)].
67
-
68
-
2. From the Standard bar, click **New Query**.
69
-
70
-
3. The following example adds two columns to the table `dbo.doc_exa`. Copy and paste the following example into the query window and click **Execute**.
71
-
72
-
```
73
-
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;
74
-
```
75
-
76
-
#### <aname="FollowUp"></a> For more information, see [ALTER TABLE (Transact-SQL)](../../t-sql/statements/alter-table-transact-sql.md)
77
-
29
+
This topic describes how to add new columns to a table in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] or [!INCLUDE[tsql](../../includes/tsql-md.md)].
30
+
31
+
## <aname="BeforeYouBegin"></a> Before You Begin
32
+
33
+
### <aname="Restrictions"></a> Limitations and Restrictions
34
+
Using the ALTER TABLE statement to add columns to a table automatically adds those columns to the end of the table. If you want the columns in a specific order in the table, use [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)]. However, note that this is not a database design best practice. Best practice is to specify the order in which the columns are returned at the application and query level. You should not rely on the use of SELECT * to return all columns in an expected order based on the order in which they are defined in the table. Always specify the columns by name in your queries and applications in the order in which you would like them to appear.
35
+
36
+
### <aname="Security"></a> Security
37
+
38
+
#### <aname="Permissions"></a> Permissions
39
+
Requires ALTER permission on the table.
40
+
41
+
## <aname="SSMSProcedure"></a> Using SQL Server Management Studio
42
+
43
+
#### To insert columns into a table with Table Designer
44
+
45
+
1. In **Object Explorer**, right-click the table to which you want to add columns and choose **Design**.
46
+
47
+
2. Click in the first blank cell in the **Column Name** column.
48
+
49
+
3. Type the column name in the cell. The column name is a required value.
50
+
51
+
4. Press the TAB key to go to the **Data Type** cell and select a data type from the dropdown. This is a required value, and will be assigned the default value if you don't choose one.
52
+
53
+
> [!NOTE]
54
+
> You can change the default value in the **Options** dialog box under **Database Tools**.
55
+
56
+
5. Continue to define any other column properties in the **Column Properties** tab.
57
+
58
+
> [!NOTE]
59
+
> The default values for your column properties are added when you create a new column, but you can change them in the **Column Properties** tab.
60
+
61
+
6. When you are finished adding columns, from the **File** menu, choose **Save***table name*.
62
+
63
+
## <aname="TsqlProcedure"></a> Using Transact-SQL
64
+
65
+
#### To insert columns into a table
66
+
67
+
1. Connect to the [!INCLUDE[ssDE](../../includes/ssde-md.md)].
68
+
69
+
2. From the Standard bar, click **New Query**.
70
+
71
+
3. The following example adds two columns to the table `dbo.doc_exa`. Copy and paste the following example into the query window and click **Execute**.
72
+
73
+
```
74
+
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;
75
+
```
76
+
77
+
#### <aname="FollowUp"></a> For more information, see [ALTER TABLE (Transact-SQL)](../../t-sql/statements/alter-table-transact-sql.md)
You can change the order of columns in Table Designer in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)].
25
-
26
-
> [!CAUTION]
27
-
> Changing the column order of a table may affect code and applications that depend on the specific order of columns. These include queries, views, stored procedures, user-defined functions, and client applications. Carefully consider any changes you want to make to column order before making it. Best practice is to specify the order in which the columns are returned at the application and query level. You should not rely on the use of SELECT * to return all columns in an expected order based on the order in which they are defined in the table. Always specify the columns by name in your queries and applications in the order in which you would like them to appear.
28
-
29
-
**In This Topic**
30
-
31
-
-**To change the column order, using:**
32
-
33
-
[SQL Server Management Studio](#SSMSProcedure)
34
-
35
-
[Transact-SQL](#TsqlProcedure)
36
-
37
-
## <aname="SSMSProcedure"></a> Using SQL Server Management Studio
38
-
39
-
#### To change the column order
40
-
41
-
1. In **Object Explorer**, right-click the table with columns you want to reorder and click **Design**.
42
-
43
-
2. Select the box to the left of the column name that you want to reorder.
44
-
45
-
3. Drag the column to another location within the table.
46
-
47
-
## <aname="TsqlProcedure"></a> Using Transact-SQL
48
-
**To change the column order**
49
-
50
-
This task cannot be performed using Transact-SQL statements.
51
-
52
-
### <aname="TsqlExample"></a>
25
+
You can change the order of columns in Table Designer in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)].
26
+
27
+
> [!CAUTION]
28
+
> Changing the column order of a table may affect code and applications that depend on the specific order of columns. These include queries, views, stored procedures, user-defined functions, and client applications. Carefully consider any changes you want to make to column order before making it. Best practice is to specify the order in which the columns are returned at the application and query level. You should not rely on the use of SELECT * to return all columns in an expected order based on the order in which they are defined in the table. Always specify the columns by name in your queries and applications in the order in which you would like them to appear.
29
+
30
+
**In This Topic**
31
+
32
+
-**To change the column order, using:**
33
+
34
+
[SQL Server Management Studio](#SSMSProcedure)
35
+
36
+
[Transact-SQL](#TsqlProcedure)
37
+
38
+
## <aname="SSMSProcedure"></a> Using SQL Server Management Studio
39
+
40
+
#### To change the column order
41
+
42
+
1. In **Object Explorer**, right-click the table with columns you want to reorder and click **Design**.
43
+
44
+
2. Select the box to the left of the column name that you want to reorder.
45
+
46
+
3. Drag the column to another location within the table.
47
+
48
+
## <aname="TsqlProcedure"></a> Using Transact-SQL
49
+
**To change the column order**
50
+
51
+
This task cannot be performed using Transact-SQL statements.
0 commit comments