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:
|
1 2 3 4 5 6 7 8 9 |
-- /* First line. Removing the two dashes activates the block comment SELECT patientname, Patientid, Language FROM whatevertable Where name = 'Whatever' -- */ Last line. When the block comment is on, this terminates it |
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.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
[…] What Were Your Game-Changing Discoveries in SQL? […]
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))
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;
Well *that* fell on its face.
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!!
select @@version
GO 5
— runs everything in the batch 5 times