desc订购SQL Server Management Studio默认选项?

Is there some way to make SQL Server Management Studio return rows descending by default ? Every time i open a table via the menu (for instance by selecting return all rows), i get the oldest rows at the top. I know i can add 'order by desc' in the sql statement, but typing that is getting annoying :)

有什么方法可以让SQL Server Management Studio返回行默认降序?每次我通过菜单打开一个表(例如通过选择return all rows)，我都会得到最老的行在顶部。我知道我可以在sql语句中添加'order by desc'，但是输入这样的语句会让人讨厌:

9 个解决方案

#1

You cannot change existing template for generating SELECTs from context menu in SSMS.

您无法更改ssm中从上下文菜单中生成选择的现有模板。

Thankfully, SSMS is extensible. This means that you can write your own extensions which can do exactly what you want, or you can use some existing solution. I would recommend Mladen's SSMS Tools Pack:

值得庆幸的是,一类是可扩展的。这意味着您可以编写自己的扩展，它可以实现您想要的功能，或者您可以使用一些现有的解决方案。我推荐Mladen的SSMS工具套装:

http://www.ssmstoolspack.com/

It was free until recently, and still is for versions 2008r2 and earlier.

直到最近它还是免费的，2008r2和更早的版本也是免费的。

#2

There is no default sort order in SQL

SQL中没有默认的排序顺序。

If you are seeing "oldest" on top then that may be the fastest way for the engine to retrieve it because that is how it is stored on disk.

如果你在顶部看到“最老的”，那么这可能是引擎检索它的最快方式，因为它是如何存储在磁盘上的。

You are not guaranteed to get it in this order, consider it "unordered" unless you specify an order!

您不能保证按此顺序获得它，除非您指定了一个订单，否则您可以认为它是“无序的”!

ORDER BY is the only way to have results in a specific order.

ORDER BY是唯一能使结果按特定顺序排列的方法。

Ordering can be an expensive operation depending on the table and order specified, so unordered is the norm.

根据所指定的表和顺序，排序可能是一个昂贵的操作，因此无序是常规。

#3

What JNK says is 100% correct.

JNK说的是100%正确的。

But if you just want it to normally work, and only when you open a table rather than when you query a table...

但是，如果您只是想让它正常工作，并且只在打开表时而不是查询表时……

Try adding a clustered index, with the first indexed field being indexed in descending order. This will likely actually cause what you need.

尝试添加一个聚集索引，第一个索引字段按降序索引。这很可能会导致你所需要的。

(If you already have a clustered index on that field, edit its properties and change its ordering.)

(如果您已经在该字段上有一个聚集索引，那么编辑它的属性并更改它的顺序。)

This is only a sensible idea if such an index is friendly to the actual use of the table. It would be self defeating to have an index that's useless programatically, just for your convenience ;)

如果这样的索引对表的实际使用是友好的，那么这只是一个明智的想法。如果有一个索引在编程上毫无用处，只是为了方便起见，这样做会适得其反;)

#4

Looking at the output of the Profiler, it seems the query is generated on the fly so I wouldn't put my hopes upon some template you can change somewhere

查看Profiler的输出，似乎查询是动态生成的，所以我不会将希望寄托在某个可以更改的模板上

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [field1]
      ,[field2]
      ,[field3]
      ,[last_modified]
  FROM [test_database].[dbo].[t_test]

As an alternative you could create a small stored procedure that takes the name of a table and then returns the data from said table. Assuming you have (recurring) logic in your tables that would indicate the 'age' of the records it shouldn't be too hard to figure out a default order for said table. If you then link this stored procedure to a hotkey you can easily get the top n records from said table in the order you wanted. I know it's not quite the same as having the information in the Object Explorer but personally I never use the Object Explorer and rather enjoy getting the content of a table by simply selecting the text in a query window and pressing CTRL-3.

作为一种替代方法，您可以创建一个小的存储过程，该过程接受一个表的名称，然后从该表返回数据。假设您的表中有(重复出现的)逻辑，它将指示记录的“年龄”，那么对于这个表来说，找出一个默认的顺序应该不会太难。如果您随后将此存储过程链接到热键，您可以轻松地按所需的顺序从上述表中获得前n条记录。我知道这与在Object Explorer中拥有信息并不完全相同，但就我个人而言，我从不使用Object Explorer，而是喜欢通过在查询窗口中选择文本并按CTRL-3来获取表的内容。

To get you started, it would look something like this

为了让你开始，它看起来像这样。

IF OBJECT_ID('p_select_top_100_desc') IS NOT NULL DROP PROCEDURE p_select_top_100_desc 
GO
CREATE PROCEDURE p_select_top_100_desc ( @table_name sysname)
AS

DECLARE @object_id int
DECLARE @order_by_col nvarchar(max)
DECLARE @sql nvarchar(max)

SELECT @object_id = Object_id(@table_name),
       @order_by_col = ''

IF @object_id IS NULL
    BEGIN
        RaisError('Could not find table %s ?!', 16, 1, @table_name)
        Return(-1)
    END

-- find order by column
SELECT TOP 1 @order_by_col = c.name
  FROM sys.columns c
 WHERE c.object_id = @object_id
   AND lower(c.name) in ('modified', 'last_modified', 'change_date', 'crdate', 'etc')

-- if none found, use the identity column
SELECT @order_by_col = c.name + ' DESC'
  FROM sys.columns c
 WHERE c.object_id = @object_id
   AND c.is_identity = 1
   AND @order_by_col  = ''

-- if still none found, use the PK (reverse order)        
SELECT @order_by_col = @order_by_col
        + (CASE WHEN ic.index_column_id = 1 THEN '' ELSE ',' END)
        + c.name 
        + (CASE WHEN ic.is_descending_key = 0 THEN ' DESC' ELSE ' ASC' END)
  FROM sys.indexes i 
  JOIN sys.index_columns ic
    ON ic.object_id = i.object_id
   AND ic.index_id  = i.index_id
  JOIN sys.columns c
    ON c.object_id  = ic.object_id
   AND c.column_id  = ic.column_id
 WHERE i.object_id  = @object_id
   AND i.is_primary_key = 1
   AND @order_by_col  = ''
 ORDER BY ic.index_column_id

-- actual query
SELECT @sql = 'SELECT TOP 100 * FROM ' 
            + @table_name
            + (CASE WHEN @order_by_col = '' THEN '' ELSE ' ORDER BY ' + @order_by_col END)

PRINT @sql
EXEC (@sql)

Return 0

GO

EXEC p_select_top_100_desc 't_test'

To 'link' it to a hotkey you'll need to go to the Tools \ Customize menu, click the [Keyboard...] button. Expand the Keyboard branch in the tree and go to the Query Shortcuts leaf. You then get an annoying grid that allows you to link a stored procedure to a CTRL-nbr combination. Mind that some of them are fixed + after you configure it and press OK the setting will only work for query windows that you newly open, existing ones will work with the 'old' config.

要将它链接到热键，你需要到Tools \ Customize菜单，点击[键盘…]]按钮。展开树中的键盘分支，进入查询快捷方式叶。然后得到一个烦人的网格，允许您将存储过程链接到CTRL-nbr组合。注意，在您配置它并按下OK后，其中一些是固定+的，该设置将只适用于您新打开的查询窗口，现有的将与“旧”配置一起工作。

Hope this helps a bit...

希望这能有所帮助……

PS: if you name it sp_select_top_n_desc and compile it in the master database you should be able to use it all over the server without the need to deploy it in each database. However, you'll probably need to switch to dynamic-sql then en prefix all sys.table queries with the output of DB_Name() as otherwise it will probably look in the master.sys.columns table etc.. which is not what you want =)

PS:如果您将它命名为sp_select_top_n_desc并在主数据库中编译，您应该能够在整个服务器上使用它，而无需在每个数据库中部署它。然而，您可能需要切换到dynamic-sql然后en前缀all sys。表查询的输出是DB_Name()，否则它可能会在master.sys中查找。列表等。这不是你想要的=)

#5

Try creating a view on that table like this and use that in your select clause or adhoc query

尝试在该表上创建这样的视图，并在select子句或adhoc查询中使用该视图

CREATE VIEW dbo.yourTable_vw
AS 
    SELECT TOP 100 PERCENT *
    FROM yourTable
    ORDER BY yourcolumn DESC
GO

#6

Actually you can create an addin for ssms that adds a new item to the object explorer's context menu.

实际上，您可以为ssms创建一个插件，该插件向object explorer的上下文菜单添加一个新项。

Check this question: Create custom menu item in Object Explorer

检查这个问题:在Object Explorer中创建自定义菜单项

Another way is to create an SP which generates and executes the select statement with the ORDER BY clause in the master db (on all servers) and bind a keyboard shortcut to that sp.

另一种方法是创建一个SP，它在主数据库(在所有服务器上)的ORDER BY子句中生成并执行select语句，并将一个键盘快捷方式绑定到该SP。

#7

Quoted from Itzik Ben-Gan's Microsoft SQL Server 2012 T-SQL Fundamentals -> Chapter 1 -> Theoretical Background -> Set Theory:

引用Itzik Ben-Gan的微软SQL Server 2012 T-SQL basic ->第一章->理论背景-> Set理论:

... when you write T-SQL queries against tables in database (such as a table of employees), you should think of the set of employees as a whole rather than the individual employees. ... In other words, a query against a table can return table rows in any order unless you explicitly request that the data be sorted in specific way, perhaps for presentation purpose.

…当您对数据库中的表(例如雇员表)编写T-SQL查询时，您应该将雇员集看作一个整体，而不是单个雇员……换句话说，对表的查询可以以任何顺序返回表行，除非您显式地请求以特定的方式对数据进行排序，可能是为了表示目的。

SSMS doesn't support customized default SELECT statement. If it does support, which column should it put after ORDER BY clause, Considering tables

SSMS不支持定制的默认选择语句。如果它确实支持，那么它应该在ORDER BY子句之后添加哪个列，考虑表。

don't have a column like 'CreatedDate';
没有“CreatedDate”这样的专栏;
or whose primary key is GUID (order is not obvious)
或者主键为GUID(顺序不明显)
or don't have a primary key or clustered index
或者没有主键或聚集索引

Even SQL SERVER will be able to list newest data some day, it's not a nature way to think individual rows (newest/oldest) against tables. Try to use UPDATE statement combined with ORDER BY clause to update newest data.

甚至SQL SERVER将来也能列出最新的数据，这不是一种自然的方式来考虑单个行(最新的/最老的)与表之间的关系。尝试使用UPDATE语句结合ORDER BY子句更新最新数据。

#8

Although officially there is no default sort order for the simple linear input I'm getting satisfactory DESC default sort order with PK or IX sort order. Let's say for log tables where I'm interested the most for last entries.

虽然正式地说，对于简单的线性输入，没有默认的排序顺序，但我得到了满意的DESC默认的排序顺序，用PK或IX排序。假设对于日志表，我对最后的条目最感兴趣。

CREATE TABLE [dbo].[tableA]([DateTime] [datetime] NOT NULL,
CONSTRAINT [PK_tableA] 
PRIMARY KEY CLUSTERED ([DateTime] DESC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

Or in SSMS ...

或者在地对地导弹……

#9

for me - I use EF Code first, but do this whenever I create a new table: Right click table, Script Table as -> Drop & Create table, and Edit the SQL and change the key to DESC, then run the script.. done (no view or anything messy)

对于我来说——我首先使用EF代码，但是每当我创建一个新表时，就这样做:右键单击表，脚本表as -> Drop & create表，编辑SQL并将键改为DESC，然后运行脚本。已完成(无视图或任何混乱)

#1