July 30, 2019 at 1:55 pm
One benefit I see is to do with vertical alignment.
SELECT col1
,LongCol2
,LongerCol3
,RidiculousCol4
FROM SomeTable;
In this example, both the commas and the column names are vertically aligned, which is not the case for trailing commas (unless some padding is added).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 30, 2019 at 10:39 pm
In case anyone is interested, here's a typical example of my code at work. It's just a small example but you'll be able to derive the casing I use, other formatting habits I've developed, and the "River Format".
Generally speaking, the "River Format" has key words or parts of key phrases to the left of column 8, column 8 is generally kept blank except for section and flower box headers, and everything else is to the right. It works real well for me. I also right justify the key words on the left. I'm so used to typing in that format that it takes little time for me to follow as I'm typing. It also makes the code very readable for me.
There are some exceptions to the general rule as you can see in the RAISERROR code that is included in the code. For things like CROSS APPLY, the "river" occurs between the two words.
The "river" is column 8. It's a vertical "river" of blanks.
You might also find the following code useful for other reasons. 😀 I have a job that runs this code once per hour because of all the damned "every minute" jobs that people just had to have.
CREATE OR ALTER PROCEDURE [dbo].[PurgeFrequentJobHistory]
/**********************************************************************************************************************
Purpose:
Purge job history for frequently executed jobs that run at least once every @pFreqMins minutes based on hours to keep.
----------------------------------------------------------------------------------------------------------------------
Usage Examples:
--===== Basic Syntax
EXEC dbo.PurgeFrequentJobHistory @pKeepForHours, @pFreqMins
;
--===== Simple Syntax uses defaults (see notes in "Presets" section of code)
EXEC dbo.PurgeFrequentJobHistory
;
--===== Purge job history prior to 48 hours ago for jobs that run every 10 minutes or less.
-- (Positional notation)
EXEC dbo.PurgeFrequentJobHistory 48, 10
;
----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 26 Jun 2015 - Jeff Moden
- Initial Creation and test.
**********************************************************************************************************************/
--===== Declare the parameters and defaults for this proc
@pKeepForHours INT = 48 --Number of hours to keep job history for frequently executed jobs.
,@pFreqMins INT = 10 --Jobs that rerun in this number of minutes or less are considered "frequently run".
AS
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Environment (standard)
SET XACT_ABORT ON;
SET NOCOUNT ON;
--===== Standard variables and constants
DECLARE @DisplayBar NCHAR(119) = REPLICATE('=',119)
,@InfoMsg NVARCHAR(2000)
,@ProcName SYSNAME = QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.'
+ QUOTENAME(OBJECT_NAME(@@PROCID))
,@RunStartDT NCHAR(24) = CONVERT(NCHAR(24),GETDATE(),113)
;
--===== Local obviously named variables.
DECLARE @OldestDT NVARCHAR(30)
,@SQL NVARCHAR(MAX)
;
--===== Identify the run
RAISERROR('%s',0,0,@DisplayBar) WITH NOWAIT;
SELECT @InfoMsg = N'Run Start: '+@RunStartDT;
RAISERROR('INFO: %s: %s',0,0,@ProcName,@InfoMsg) WITH NOWAIT
;
--=====================================================================================================================
-- Purge frequent job history
--=====================================================================================================================
--===== Identify the section
SELECT @InfoMsg = N'Creating the dynamic SQL to purge the job history...';
RAISERROR('INFO: %s: %s',0,0,@ProcName,@InfoMsg) WITH NOWAIT
;
--===== Determine the oldest purge date based on the @@pKeepForHours parameter provided
SELECT @OldestDT = CONVERT(NVARCHAR(30),DATEADD(hh,-ABS(@pKeepForHours),DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0)),120)
;
--===== Create the purge job history code for every job that reruns in less than @pFreqMins minutes.
SELECT @SQL = ISNULL(@SQL+CHAR(10),N'')
+ REPLACE(REPLACE(REPLACE(
N'EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = "<<@OldestDT>>", @job_name = N"<<j.name>>";'
,'"' ,'''')
,'<<j.name>>' ,j.name)
,'<<@OldestDT>>',@OldestDT)
FROM msdb.dbo.sysschedules ss
JOIN msdb.dbo.sysjobschedules js ON js.schedule_id = ss.schedule_id
JOIN msdb.dbo.sysjobs J ON j.job_id = js.job_id
WHERE (freq_subday_type = 4 AND freq_subday_interval <= @pFreqMins) --Per-minute jobs that rerun at least every @pFreqMins minutes.
OR (freq_subday_type = 2 AND freq_subday_interval <= @pFreqMins*60) --Per-second jobs that rerun at least every @pFreqMins minutes.
;
--===== Display the dynamic SQL (you can comment this out if you don't have something similar)
SELECT DynamicSQL = LongString FROM util.ShowLongString(@SQL)
;
-----------------------------------------------------------------------------------------------------------------------
--===== Identify the section
SELECT @InfoMsg = N'Purging the job history...';
RAISERROR('INFO: %s: %s',0,0,@ProcName,@InfoMsg) WITH NOWAIT
;
EXEC (@SQL)
;
--=====================================================================================================================
-- Housekeeping and Exit
--=====================================================================================================================
--===== Identify the section
SELECT @InfoMsg = N'RUN COMPLETE: Duration = ' + CONVERT(NCHAR(12),GETDATE()-@RunStartDT,114);
RAISERROR('INFO: %s: %s',0,0,@ProcName,@InfoMsg) WITH NOWAIT;
RAISERROR('%s',0,0,@DisplayBar)
;
Here's what the output looks like in the run logs... helps a whole lot for troubleshooting if an error does occur. I also prefer to let SQL Server do its job instead of creating a TRY/CATCH environment, in most cases. As with all else, "It Depends". 😀
=======================================================================================================================
INFO: [dbo].[PurgeFrequentJobHistory]: Run Start: 30 Jul 2019 18:34:48:257
INFO: [dbo].[PurgeFrequentJobHistory]: Creating the dynamic SQL to purge the job history...
DynamicSQL
-----------------------------------------------------------------------------------------------------------------------
<?LongString --
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = '2019-07-28 18:00:00', @job_name = N'Log_Cached_Modules_For_Usage';
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = '2019-07-28 18:00:00', @job_name = N'TransferCallDataFromPhoneSystem';
?>
INFO: [dbo].[PurgeFrequentJobHistory]: Purging the job history...
0 history entries purged.
0 history entries purged.
INFO: [dbo].[PurgeFrequentJobHistory]: RUN COMPLETE: Duration = 00:00:00:013
=======================================================================================================================
As a bit of a sidebar, I absolutely hate the extra space this site puts between lines of code. It makes the code look a hell of a lot longer than it really is.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 46 through 46 (of 46 total)
You must be logged in to reply to this topic. Login to reply