Order by desc as default option for SQL Server Management Studio?

Question

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 :)

Solution 1

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

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:

http://www.ssmstoolspack.com/

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

Solution 2

There is no default sort order in 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.

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

Solution 3

What JNK says is 100% correct.

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 ;)

Solution 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

/****** 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.

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.

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 =)

Solution 5

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

Check this question: Create custom menu item in 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.

View more solutions

Comments

[JNK]

+1 for providing a workaround for what he probably wants (instead of just telling him he's wrong like I did :) )

[UnhandledExcepSean]

Wouldn't that be terrible for insert performance if they are clustering on an identity column?

[Martin Smith]

@SpectralGhost Yes I think this would likely cause logical fragmentation issues. New extents allocated would probably be at later page numbers in the file when logically they would need to be earlier in the file.

[Martin de Wildt]

I understand what you are saying, but i was just wondering if there was some setting in SQL Server Management Studio. When you right click on a table you get the option to retrieve 'top (x)' and i was hoping for some setting 'order desc'

[Martin de Wildt]

The idea is not to change the values in the database, just they way they are presented to me when looking at them Just like i can get 'top (x)' from the context menu i was hoping i could somehow get 'order by id desc' :)

[JNK]

@MartindeWildt That TOP X is just to limit the result set. You can certainly add an ORDER BY. Under normal circumstances if there are no joins the returned results will likely be in clustered index key order.

[Martin Smith]

Yep definitely not good for fragmentation assuming that they are inserting in ascending order.

[Simon Tower]

I think a better title for this question might be "Is there a way to change the default SELECT query in SQL Server Management Studio"?

[dev_etter]

And an even better title would be "Is there a way to change the default SELECT query in SQL Server Management Studio per table?" Most workarounds you'll find would be at the SSMS installation level and not specific to each table, which would be necessary because of column differences.

[deroby]

-1 Relying on the clustered index for order is bound to bite back to you someday. It will probably work 9999 out of 10000 times and then that one crucial time you'll go mental because you're missing that one record that really really should be there but isn't showing up in the grid. Just don't...