<div>
the ordering of fields in a table in SQL Server is not crucial.<br><br>there is <strong>no direct way</strong> to do column re-ordering <strong>via SQL</strong>. (it actually consists of complex processes, such as drop the original table, create a new old, migrate data, drop the original one, and rename the new table)<br><br>if you got <strong>enough permission</strong>, you should able to re-order the column via <strong>Table Design</strong>.
</div>


<div>
In fact, this isn't even a SQL Server related thing: in virtually all relational database systems, the order of fields or columns does NOT matter! There is absolutely NO NEED at all to perform any kind of reordering! The question here should be: why do you even consider this? what's the purpose? what's the goal?
</div>


<div>
Your question is rather ambiguous…<br>The subject says "Renumber Column table" but the question text says something different - "I have a field in a table that I wanted to automatically renumber"<br><br>Then it is hard to decide whether 1-A, 2-B, 3-C, … represents the content of one field/column or whether it describes the column order on output when you issue SELECT * FROM table...<br><br><br><br>
</div>


<div>
The easiest way of table columns reordering is to create a view with given columns order.<br><br>
</div>


<div>
you have to have a separate column that deals with setting a sort orders&nbsp;will be handled on the data entry side.<br>Where if you shift C to 2 B shifts to 3<br><br>
</div>


<div>
Sorry, my question is wrong. In fact this is not the column i need to renumber but the value in record. If user write a low number in Col1, i need rewrite and renumber bellow records:<br><br>Original Order<br>Col1 &nbsp;Col2<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A<br>2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B<br>3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C<br>4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D<br>5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;E<br><br>For example i change the order of C to 2 then sould save in database like this:<br>Col1 &nbsp;Col2<br>1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A<br>2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C<br>3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B<br>4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D<br>5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;E
</div>


<div>
<blockquote>For example i change the order of C to 2 then sould save in database like this</blockquote><br>So, you have a <strong>Front End program</strong> to set the <strong>sequence of your data</strong>? If yes, then your Front End program should tell the SQL Server what sequence to be saved.
</div>


<div>
Ryan's correct! Reordering should be done "outside" the database. It might be possible to work something out using trigger(s), but that also relies on the database itself (vendor, possibilities etc.)<br>Hopefully, this column is NOT used as primary key ;-)
</div>


<div>
I would say the requirement is clear enough now<br>... but the solution is accepted already.<br><br>
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
<br />
I have a field in a table that I wanted to automatically renumber, how can I do?<br />
best regards<br />
<br />
Original Order<br />
1 -A<br />
2 - B<br />
3 - C<br />
4 - D<br />
5 - E <br />
<br />
For example i change the order of C to 2 then sould save in database like this:<br />
1 -A<br />
2 - C<br />
3 - B<br />
4 - D<br />
5 - E
</div>
</div>


Hi,

I have a field in a table that I wanted to automatically renumber, how can I do?
best regards

Original Order
1 -A
2 - B
3 - C
4 - D
5 - E 

For example i change the order of C to 2 then sould save in database like this:
1 -A
2 - C
3 - B
4 - D
5 - E

Renumber Column table

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.