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

Answer 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.

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

Answer 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.

Answer 4

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.

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

Answer 5

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

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

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

1. don't have a column like 'CreatedDate';
2. or whose primary key is GUID (order is not obvious)
3. 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.

Answer 6

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

Answer 7

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

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

Answer 8

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.

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

Answer 10

create table MYTESTTABLE (
    ID numeric(18, 0) identity(1, 1) not null
    ,COL1 numeric(18, 0) null
    ,COL2 numeric(18, 0) null
    ,COL3 numeric(18, 0) null
    ,COL4 numeric(18, 0) null
 CONSTRAINT [PK_MYTESTTABLE] PRIMARY KEY CLUSTERED 
(
    [ID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO