SQL SERVER – SELECT TOP Shortcut in SQL Server Management Studio (SSMS)

This is tool is pretty old, yet always comes as a handy tip. I had a great trip at TechEd in India. And, during one of my presentations, I was asked if there are any shortcuts to SELECT only TOP 100 records from SSMS.

I immediately told him that if he explores the table in SSMS, he can just right click on it and SELECT TOP 1000 records. If he wanted only 100 records, then he could edit that 1000 to 100 by means of going to Options.

Go to Options, then hover the mouse over the SQL Server Object Explorer, then proceed to Commands. Afterwards, change the Value for Select Top <n> Audit Records.

After narrating the steps, he told me that he was not looking for the right click option; rather he was asking if there is any kind of keyboard shortcut for convenience’s sake.

Actually, a keyboard shortcut is also possible. SQL Server Management Studio (SSMS) lets you configure the settings you want using a shortcut. Here is how you can do it.

Go to Options, then to Environment. Proceed to Keyboard, and from there, configure your T-SQL with the desired keyword.

Now, open SSMS New Query Window, and then click and type in any table name.  After that, just hit the shortcut you just made earlier. Doing this should display TOP 100 records in the Result window.

I am sure this trick is quite old, but it is still helpful to many.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

25 thoughts on “SQL SERVER – SELECT TOP Shortcut in SQL Server Management Studio (SSMS)

  1. Thats help full. Thanks.

    Question:
    In ssms, I saw that for some of the clients there is a option to create a project but for some there isn’t? I am not sure if there is a edition difference. If so what are all the editions provides us an option to create a solution.

    Thanks for all the help.

  2. Does anyone know how to edit the statement? I would like to get the top 1000 rows , but ordered by the primary key descending. This way, the newest records will be displayed first. This would be especially handy for history tables.

    Thanks
    curio

  3. Can this be down in SSMS 2005 as well? I don’t have the SQL Server Object Explorer Node when I go to options. Thanks!

  4. Yeah, I dont see SQL Server Object Explorer Node when I go to options in SSMS 2005…..How do we proceed in this case??

  5. I also can’t see the SQL Server Object Explorer option in options – also in SSMS 2005. Any updates on this? Thanks

  6. Hi curio,
    to get the top 1000 rows , ordered by the primary key descending

    Type the below in query window and Click Cntrl + 3
    AgreementTable order by AgreementID desc

  7. Hi
    How is the selected text is passed to the SP
    ex:
    I have this SP
    ALTER PROC [dbo].[sp_MyQuickQueries] @QryId VARCHAR(30), @tbl VARCHAR (128)
    and different CTRL-? use different @QryId so that I only have ONE proc instead of having one proc per shortcut key.

    For CTRL-5 I assigned
    dbo.sp_MyQuickQueries ‘COLUMNS_ORDERBY_NAME’, <– notice comma at end. I expected that SSMS would add the selected text after the comma but no.

    In my SP I have several IF like this
    IF @QryId = 'COLUMNS_ORDERBY_NAME' Begin

    The SP works well when called directly.
    But not from a shortcut key.
    I get error

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'MYTABLE'.
    MYTABLE beeing the selected text when i hit CTRL-5

    Any idea ?

  8. Pingback: SQL SERVER – Auto Recovery File Settings in SSMS – SQL in Sixty Seconds #034 – Video « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Wrap SQL Code in SSMS – SQL in Sixty Seconds #038 – Video « SQL Server Journey with SQL Authority

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s