Use SQL Server to edit the first 200 rows

In SQL Server 2005/2008/2008 R2, we can use the first 200 lines of SQL Server editing function. The purpose of this function is to simplify the use of update, let developers, DBAs and people who do not know T-SQL can directly modify the data in the database through the visual interface. When editing the first 200 rows, you can easily edit the first 200 rows of data. However, you can directly edit the data you want to edit on a visual page, instead of opening the editing interface and automatically reading the 200 entries, the 200 entries are in disorder. This is a very powerful function and seems to be a little tricky. By accident, you may find that you can read the data you want to read or sort the data you want to read by using the tips such as display condition pane and display SQL pane, this makes data modification much easier. Give a real case. There is a menu data table, which stores the System Menu information. The table structure is as follows:

 

Select Top 1000 [functioncode] -- menu number, primary key, [functionname] -- name, [functionurl] -- Link, [type] -- type, [description] -- description, [orderno] -- sort from [DBO]. [menu]

 

After you open the database, you can directly use the edit first 200 rows function, as shown in figure: the first 200 rows of graph editing are opened and the data in the menu is read, as shown in: shows the first 200 rows of graph editing, after you open the data in the menu, because the menu is not added to the data table at one time, it will not be sorted by the primary key column. This will cause inconvenience for you to directly use the database data to modify the menu data, you cannot put the data sequence of a menu item together. Later, you can use the display condition pane to modify the sequence of the first 200 rows of data read by default. In SSMs, open the display condition pane, as shown in: click "display condition" in the "display condition" pane of the graph, as shown in. The "display condition" pane is displayed. In the "display condition" pane, sort by functioncode, sort the data according to orderno, so that all the data in a menu can be put together. After the settings are complete, click Execute SQL, as shown in the figure. After the SQL statement is executed, the result is shown in the following figure: menu data arranged in order.

In this way, we can modify the data as needed, and put the data of a menu together to facilitate the modification of the primary key and sorting of the menu data.

 

In addition to the display condition pane, you can also use the display SQL pane and click the display SQL pane. The results are as follows, we can use T-SQL statements familiar to developers and DBAs to read data, read the data can be directly modified in the Results list, can read more than 200 pieces of data, you can use the Condition Statement and order by statement. to correspond to the condition pane, we use the following SQL statement to read menu data.

 

Select top (1000) functioncode, functionname, functionurl, type, description, ordernofrom menuorder by functioncode ASC, orderno ASC

 

A maximum of 1000 menu data records can be read here, which is identical to the data read using the condition pane. The displayed interface is shown in: graph Display SQL pane in addition to the display condition pane, the display SQL window, you can also use the display View pane, which is a visual operation interface for preparing unfamiliar T-SQL, it also provides comprehensive functions to facilitate the acquisition of SQL statements. Summary

In the database products, most people always prefer Oracle, MySQL, Oracle is preferred because of the stability and function of the powerful, is to use PL-SQL has a lot of simple tools can be used; I prefer MySQL because this guy is half-free, small, and cost-effective. SQL Server is rarely well received. Although it has strong functions, its criticism is continuously voiced. However, through the introduction of this tips, we gradually understand that SQL server features are not as weak as we think, but there are a lot of good things and skills we have not found and mastered. Compared with SQL Server 2000 to SQL Server ~ A version is available in six years. The time from SQL Server 2005 to SQL Server 2008 has been shortened to three years, which is a good news for SQL Server users. The shortening of product upgrade time also brings about some problems, such as compatibility, upgrade costs, and new features. These are all factors that need to be considered, this article only looks at this issue from a purely user perspective.