LAG/LEAD Demo for SQL Server

-- This demo can only be run on SQL SERVER 2012 or higher because of the LAG/LEAD function calls
USE <database_name>;
    Value INT NOT NULL

INSERT INTO #LagLeadSource
( Value )
VALUES ( 123 ),( 234 ),( 345 ),( 456 ),( 567 ),( 678 ),( 789 ),( 890 ),( 901 );

       LAG(Value, 4, 0) OVER ( ORDER BY ID ASC) AS Next_x_4_LowestValue,
       LAG(Value, 3, 0) OVER ( ORDER BY ID ASC) AS Next_x_3_LowestValue,
       LAG(Value, 2, 0) OVER ( ORDER BY ID ASC) AS Next_x_2_LowestValue,
       LAG(Value, 1, 0) OVER ( ORDER BY ID ASC) AS NextLowestValue,
       Value AS 'ACTUAL_VALUE',
       LEAD(Value, 1, 0) OVER ( ORDER BY ID ASC) AS NextHighestValue,
       LEAD(Value, 2, 0) OVER ( ORDER BY ID ASC) AS Next_x_2_HighestValue,
       LEAD(Value, 3, 0) OVER ( ORDER BY ID ASC) AS Next_x_3_HighestValue,
       LEAD(Value, 4, 0) OVER ( ORDER BY ID ASC) AS Next_x_4_HighestValue
FROM #LagLeadSource
-- using a WHERE clause here will "break" this code as the dataset will have a row-restriction applied before the LAG/LEAD functions are ever called, rendering them useless

IF OBJECT_ID('tempdb..#LagLeadSource') IS NOT NULL
DROP TABLE #LagLeadSource;

Get metadata for all tables and columns in a SQL Server database

USE <database_name>;
SELECT + '.' + AS 'Schema.Table Name' ,
    CAST(td.value AS VARCHAR(5000)) 'Table Description' , AS 'Column Name' ,
    CAST(cd.value AS VARCHAR(5000)) AS 'Column Description'
FROM sysobjects AS t
INNER JOIN sysusers AS u ON u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties AS td ON td.major_id =
    AND td.minor_id = 0
    AND = 'MS_Description'
INNER JOIN syscolumns AS c ON =
LEFT OUTER JOIN sys.extended_properties AS cd ON cd.major_id =
    AND cd.minor_id = c.colid
    AND = 'MS_Description'
WHERE t.type = 'u'
    AND NOT cd.value IS NULL
ORDER BY, c.colorder

Find all triggers in a SQL Server database

Use the following small script to find all hidden triggers in a SQL Server database.  Pretty much all competent developers using SQL agree that using triggers is a bad practice, but few know how to find them before they have wasted a lot of development and testing time not understanding why things aren’t working the way they want them to (because of triggers doing behind the scenes work, undesirably).

Trigger the horse, with Roy Rogers.
-- Finds all "hidden" triggers in a database. Using triggers is not a best practice.
USE <database_name>;
SELECT  name AS [TriggerName]
FROM    sys.objects
WHERE   OBJECTPROPERTY(object_id, 'IsTrigger') <> 0;

Coffee – not Java

Coffee beans in the shape of a coffee cup. Stock Photo

When I talk about “coffee” I mean REAL coffee, the dark, rich stuff, and not just “hot brown water”, my term of “dis-endearment” for office-coffee, church-coffee, or your average low-grade convenience store coffee.  Yuck.

I generally prefer to use a normal drip coffee-maker, and I never make coffee with anything other than distilled or purified water (better) anymore to get away from the chlorine and fluoride, which influence the taste of the brew. I recently acquired a basic Keurig model (used; first generation), about five years after everyone else seems to have bought one.  I like them – maybe even a lot – but they won’t replace my drip coffee-maker for the purity and quality of brew. Keurigs are convenient, but you pay a penalty in the quality of the brewed coffee in my opinion.

That having been said, the Keurig is very convenient for my morning coffee especially, when I am under more of a time crunch.  Good coffee for me via K-cups these days comes from:

  • Gevalia – Dark Royal Roast:  Like liquid dessert, but not quite as good as Seattle’s Best, Blend No. 4
  • Folgers – Black Silk:  My daily standby in absence of the Gevalia
  • GreenMountain – Dark Magic:  Wow, that was strong…Wow!
  • Cameron’s Coffee – Highlanger Grogg:   Too light a roast, but delicious anyway
  • Cameron’s Coffee – Cinnamon Sugar Cookie:  Same; delicious
  • McCafe – Premium Roast:  As good as the drive-thru coffee; the other McCafe brews have a strange aftertaste, IMHO

That was in order of preference if I have all of them at the ready in my supply drawer. However, if making drip coffee, my hands-down preferences are:

  • Seattle’s Best – Blend No. 4:  Seriously, like drinking liquid dessert; *not* available in a K-cup as of early 2017
  • Folger’s – Black Silk:  Good stuff at a great price

Thought you might like to know my java preferences.

Agree / disagree?  What do you think makes for good coffee?