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>;
GO
CREATE TABLE #LagLeadSource
(
    ID INT IDENTITY(1, 1) NOT NULL ,
    Value INT NOT NULL
);

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

SELECT ID,
       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;
Advertisements

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

USE <database_name>;
GO
SELECT
    u.name + '.' + t.name AS 'Schema.Table Name' ,
    CAST(td.value AS VARCHAR(5000)) 'Table Description' ,
    c.name 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 = t.id
    AND td.minor_id = 0
    AND td.name = 'MS_Description'
INNER JOIN syscolumns AS c ON c.id = t.id
LEFT OUTER JOIN sys.extended_properties AS cd ON cd.major_id = c.id
    AND cd.minor_id = c.colid
    AND cd.name = 'MS_Description'
WHERE t.type = 'u'
    AND NOT cd.value IS NULL
ORDER BY t.name, c.colorder
GO

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
Trigger the horse, with Roy Rogers.
-- Finds all "hidden" triggers in a database. Using triggers is not a best practice.
USE <database_name>;
GO
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?