What Were Your Game-Changing Discoveries in SQL?

6 Comments. Leave new

• MS.

  August 29, 2018 2:08 pm

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

• 

  Brett Ivery

  August 30, 2018 1:29 am

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

  • 

    Cat Skinner

    August 30, 2018 7:56 am

    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;

    • 

      Cat Skinner

      August 30, 2018 8:17 am

      Well *that* fell on its face.

• 

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

• 

  Stephen Morris

  August 30, 2018 3:22 am

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