Share a SQL Trick, Get a Treat!

Everyone who shares a SQL Sever or other helpful “technical” trick (i.e., hack/tip/shortcut) as a comment to this post by Saturday, Oct. 31, will receive a SQL Solutions Group reusable cotton face mask and pen (pumpkin orange!). Plus, everyone who participates is eligible for our grand prize: A $50 Amazon gift card. We’ll select one random submitter to win!

Here’s a trick from Senior Consultant Scott Klein.

Please share this

This Post Has 13 Comments

  1. Zach S.

    One of my favorite tricks is also one of the most simple!

    The CTRL+X keyboard shortcut (or “Control X”) is “cut”– but it doesn’t work like cutting does in most other Microsoft applications. In MS Word, for example, CTRL+X is also cut, but it only cuts what you’ve selected. If you haven’t selected anything and just have a flashing cursor, MS Word doesn’t do anything.

    SSMS is way cooler. In SSMS, if you have text selected, it works just like MS Word. However, if you DON’T have anything selected, it cuts the entire line! If your cursor is just flashing, not just the contents of the line are removed, but the entire line itself is removed.

    This is great for two reasons. Obviously, it’s a quick and easy way to cut– but that wouldn’t be that useful in itself. Rather, the real benefit here is making it easy to rapidly delete lines of code. Whether you have old code you’ve commented out and need to remove, or empty lines you need to clean up, CTRL+X is a very, very quick way to rapidly remove entire lines from your query with minimal effort. I use it a fair bit.

    Hope this helps someone!

    1. Jason Russell

      Thanks for sharing, Zach!

  2. Aaron N. Cutshall

    One thing I have used is a little known trick is to use Common Table Expressions (CTEs) to delete duplicate data:

    — Delete Duplicate Data – typically used in staging tables without primary key
    CREATE TABLE #Patients (
    PatientKey int IDENTITY(1,1),
    PatientID int NOT NULL,
    LastName varchar(40) NOT NULL,
    FirstName varchar(40) NULL,
    MiddleInit char(1) NULL,
    Gender char(1) NULL,
    DOB date NULL
    );

    INSERT INTO #Patients(PatientID, LastName, FirstName, MiddleInit, Gender, DOB)
    VALUES (123456, ‘Duck’, ‘Daffy’, ‘D’, ‘M’, ’03/05/1964′),
    (234567, ‘Bunny’, ‘Bugs’, ‘B’, ‘M’, ’02/29/1964′),
    (345678, ‘Coyote’, ‘Wile’, ‘E’, ‘M’, ’01/15/1966′),
    (123456, ‘Duck’, ‘Daffy’, ‘D’, ‘M’, ’03/05/1964′), — Duplicate
    (456789, ‘Runner’, ‘Road’, NULL, ‘F’, ’04/23/1974′),
    (345678, ‘Coyote’, ‘Whylee’, NULL, NULL, ’01/15/1966′), — Duplicate
    (123456, ‘Duck’, ‘Daffy’, ‘D’, ‘M’, ’03/05/1964′), — Duplicate
    (567890, ‘Duck’, ‘Daisy’, NULL, ‘F’, ’05/03/1966′),
    (564231, ‘Fudd’, ‘Elmer’, NULL, ‘M’, ’03/20/1946′),
    (853156, ‘Bird’, ‘Tweety’, NULL, ‘M’, ’10/10/1960′),
    (462137, ‘Cat’, ‘Sylvester’, ‘T’, ‘M’, ’12/15/1955′),
    (134679, ‘LePue’, ‘Peppy’, NULL, ‘M’, ’11/11/1959′),
    (963741, ‘Duck’, ‘Huey’, NULL, ‘M’, ’09/09/1979′),
    (963742, ‘Duck’, ‘Louie’, NULL, ‘M’, ’09/09/1979′),
    (963743, ‘Duck’, ‘Dewey’, NULL, ‘M’, ’09/09/1979′);

    SELECT * FROM #Patients ORDER BY PatientID;

    — Show duplicate data
    WITH cteDups AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY P.PatientID ORDER BY P.PatientID, P.PatientKey) AS RowNbr
    FROM #Patients P
    )
    SELECT *
    FROM cteDups D
    WHERE D.RowNbr > 1;

    — Delete duplicate data
    WITH cteDups AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY P.PatientID ORDER BY P.PatientID, P.PatientKey) AS RowNbr
    FROM #Patients P
    )
    DELETE
    FROM cteDups
    WHERE RowNbr > 1;

    As long as you can specify the criteria that determines how to identify duplicates, this trick works beautifully! By how you arrange the ORDER BY in the ROW_NUMBER() function, you can do things like keeping the latest record or other such criteria.

    1. Jason Russell

      Thanks for sharing, Aaron!

  3. anil giri

    I would like to share the easy and simple tip that helps most of them working with Schemas. The trick is to view the Schema object from TSQL editor without many of the commands flow in.

    ALT + F1 ( Schema definition)

    Highlight the <> and be on the database where the table lies and press ALT + F1 give your definition identity/index/datatypes/constraints etc.,

    For procedures, same trick works as knowing what all parameters name, type defined and order or parameter.

    Hope this tip(ALT + F1) help and most of you may already know. help me a ton.

    Thanks
    AG

    1. Jason Russell

      Anil, thanks for sharing that tip!

  4. Tim Gensler

    I sometimes want to export my results to excel and traditionally, I just click on the results, copy them, open excel, and paste them in. It seems like a no-brainer by now as I do this alot to share results with auditors and such.

    However, with a free SSMS add on from APEXSQL.com called SQLComplete, when you right click on the results in grid form, one of menu options is to export to excel directly. It may not sound like much but after you do it a handful of times with a large number of columns and rows, it makes you wonder why Microsoft didn’t create it. No more parsing and figuring out what the separator should be.

    For the record, I don’t work for them. I just like free software to make me more productive.

    1. Jason Russell

      Tim, does seem strange that MS didn’t create that. Good tip. thanks!

  5. mmcdonald

    A few SSMS keyboard shortcuts I find useful

    CTRL+K+C / CTRL+K+U — comment / uncomment line

    CTRL+R to hide/unhide results pane

    Shift+Alt+Arrow selection — grab rectangular blocks of text/code for copy/paste

    Cheers

    1. Jason Russell

      We can all use some good keyboard shortcuts. Thanks!

  6. Jason Russell

    Congratulations to Tim Gensler who was our random winner of the $50 Amazon gift card!

    1. Alison Gonzalez

      Woo hoo! Congrats Tim!

  7. mmcdonald

    Congrats Tim!

Leave a Reply

Related Articles