Order by desc as default option for 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 :)

Solution 1:

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

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

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