What Were Your Game-Changing Discoveries in SQL?

If you like learning random tips & tricks, there’s a great discussion going on in Reddit:

What are your game-changing discoveries in SQL?

I’m only going to give away the first one just to get you started: if you need to repeatedly comment and un-comment groups of code, do this:

When you want to comment the whole thing out, just remove the top two dashes. You don’t have to put in the ending */ because it’s already there at the end, just silently getting ignored until it’s needed.

GENIUS. Head over to the Reddit thread for more.

Previous Post
Do You Have Tables In Your Tables?
Next Post
What’s Different About SQL Server in Cloud VMs?

6 Comments. Leave new

  • […] What Were Your Game-Changing Discoveries in SQL? […]

    Reply
  • I saw some code online where someone had written a cursor (Swear! Spit!) to iterate through a table to build up a delimited string.
    There’s a much easier way to do this with a single select statement.

    DECLARE @delimiter NVARCHAR(5) = N’~>’
    DECLARE @ResultString NVARCHAR(4000) = N”

    SELECT @ResultString = @ResultString + name + @delimiter from sys.tables st WHERE st.is_ms_shipped = 0
    SELECT LEFT(@ResultString,LEN(@ResultString)-LEN(@delimiter))

    Reply
    • Here’s a similar way to skin that cat:

      — & – magic ampersand to keep SQL Quotes ” unchanged, instead of converting them to Weird Unicode Quotes ’,”
      DECLARE @delimiter NVARCHAR(5) = N’~>’;
      DECLARE @ResultString NVARCHAR(4000) –we want this to start off life as NULL

      SELECT @ResultString = ISNULL(@ResultString + @delimiter + [name], [name])
      FROM sys.tables st
      WHERE st.is_ms_shipped = 0;

      SELECT @ResultString;

      Reply
  • johan JEZEQUEL
    August 30, 2018 3:22 am

    The snippet who really changed my DBA’s life for the huge treatments is :
    SET STATISTICS PROFILE ON;
    GO

    And, in a new window :
    EXEC _BaseTechnique.dbo.sp_whoisactive –(thanks Adam Mechanic) to identify the thread
    SELECT node_id,physical_operator_name, SUM(row_count) row_count,
    SUM(estimate_row_count) AS estimate_row_count,
    CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)
    FROM sys.dm_exec_query_profiles
    WHERE session_id=63 –if the thread is 63
    GROUP BY node_id,physical_operator_name
    ORDER BY node_id;

    In order to have an idea of the progress of the query.
    No more blindness!!

    Reply
  • Stephen Morris
    August 30, 2018 3:22 am

    select @@version
    GO 5
    — runs everything in the batch 5 times

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}