SQL SERVER – Shortcut to SELECT only 1 Row from Table

If you watch any SQL Server Developer, you will notice one particular task them doing every day frequently. It is they select the row from the table to see what are the various kinds of data it contains. Most of the tables are very big so it is always advisable to retrieve only a single row from the table. It is very cumbersome for developers to continuously write following code to retrieve a single row to see what the table contains.

SELECT TOP 1 * FROM TableName

I suggest you try to write above code and there is good chance that one has to look at the keyboard to type above code as it requires numbers. Even expert typist often has to look at the keyboard when they have to deal with Special Symbole (in our case *) and number (in our case 1) when typing subsequently. The counter argument is to not use above command but use SP_HELP or any other SP which describes the schema of the table. Well, this may be a good idea but we developer like to do things our own way. Just to be clear it is never a good idea to do any development on production server.

There is a shortcut how we can make our life easier if we use SQL Server Management Studio Shortcut. You can see following image where I am configuring SSMS shortcut.

Go to SSMS Menu >> Tools >> Options >> Environment >> Keyboard >> Query Shortcuts.

Now on the right side click on the preferred shortcut (in this case I am selecting CTRL + 3) and now in the column of the Stored Procedure type following statement

SELECT TOP 1 * FROM

Pay special attention that I have not mentioned the name of the table. The syntax is incomplete as we will SELECT the name of the table later on of SSMS Query Editor.

Once that is done, now go to SSMS and select any preferred table name. While the table name is selected type CTRL + 3 and you will notice that the query will run and will return a single row as a resultset. Now developer just has to select the table name and click on CTRL + 3 or your preferred shortcut key and you will be able to see a single row from your table.

This trick can be very helpful when a developer is debugging a long stored procedure with lots of a table name or while writing complex query.

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

About these ads

31 thoughts on “SQL SERVER – Shortcut to SELECT only 1 Row from Table

  1. Is there any why to specify selected text value in T-SQL? for example: SELECT * FROM inner join SYS.tables t ON t.name = ”. I am thinking about order by PK and descending, as we always look for last table data…!

    Like

      • Hello Vikas,

        I spent nearly 10 minutes trying to understand your question, tried reading it in atleast 10 different ways but failed everytime.

        But to make a generic statement, you can create a stored procedure that accepts parameter and perform what every search you want to do, just put the logic inside the stored procedure, if you think table name will change then make it dynamic sql sp and then execute the sp from keyboard short cuts.

        If this doesn’t help then please elaborate your questions with some samples.

        ~ IM.

        Like

        • SELECT TOP 100 * FROM <SELECTED_TEXT> will list the top 100 rows, where selected_text will be the table name written in new query window, and as this post describes how we can use SQL Server short cut. Now in general if we have a transaction table, then we always look for latest entries, and if the table is just listing different status name then table may not have more that 10 to 100 records. This shortcut will be very helpful for tables having less records. But for me only, when I work with my database, I always have to see the last entry of any table, and as my database is about 5 year older, I have about 1 lakh to 1 crore entries in some of the tables.

          Now speaking directly, how we can have trick to list table rows order by PK (if table has any PK and it’ll be there most of the time)?

          What I was trying to do when i post my first comment is:
          I was trying to get the PK column name’s list from sys.columns, now to use sys.columns, I may need to use sys,tables to get the object id,

          I am wondering if we can use a sub query in order by clause!!

          For example:

          SELECT * FROM employees
          ORDER BY (SELECT TOP 1 C.name + ‘ DESC’ AS orderby_text FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id AND t.name = ‘employees’ AND c.is_identity = 1)

          Like

  2. Thanks Pinal but I must be missing something. I’m not clear when you say “select table name.” I tried clicking the table name in the object explorer to highlight it but I get “incorrect syntax near ‘from'”. I tried typing the table name in the query window then hitting ctrl-3 but that does not work either…

    Like

  3. hi Pinal ,

    this is kumar , having one query to ask…
    there are two input dates

    @d1= 01/02/2013
    @d2= 28/02/2013

    now what’s we write the query to generate bellow output.

    output:
    01/02/2013
    01/02/2013
    ……
    ……
    28/02/2013

    Like

    • Use below query…

      declare @d1 date=’01/01/2013′
      declare @d2 date=’02/28/2013′

      ;with cte_date (row_date)as(
      select @d1
      union all
      select DATEADD(DD,1,row_date) from cte_date where row_date<@d2

      )
      select * from cte_date

      Like

  4. Found small Issue….
    “SELECT TOP 1 * FROM ” looks grate, but I prefer some more data.
    I add in shortcut “SELECT TOP10 * FROM ” (yes, there is a mistake)
    Then I open New query and I try to run CTRL+3… Result?
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘from’.

    Ok, what did I wrong? I found my mistake and I correct shortcut into “SELECT TOP 10 * FROM “. Than quick CTRL+3 and…
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘from’.

    Oh! common… what`s wrong?!
    I check one more time shortcut – OK
    I saw comments to this post – all are happy…
    I run SQL Profiler and I saw: “SELECT TOP10 * FROM my_table”
    WTF?!

    The key point is that I changed shortcut while it was opened query window.
    SSMS load all shortcuts at the beginning and didn`t reload them later, even I changed one of them.

    My solution is:
    You change something in shortcuts? It will works only in new query windows. Old queries have “old” shortcuts.

    Like

  5. Pingback: SQL SERVER – Shortcut to SELECT Single Row from Table – SQL in Sixty Seconds #046 – Video | SQL Server Journey with SQL Authority

  6. I wish the application just let us choose our own shortcuts and not restricted to choose only 10 shortcuts. I would suggest to use a simple clipboard manager applications where you type a shortcut the query will fill in the editor.

    Like I type ssf it expands to select * from

    we can have our own custom shortcuts with table names and all

    Like

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