It’s Meme Monday time! Our topic is SQL Horoscopes, or more catchily, SQLStrology.

We’re going to have to figure out what astrological sign our SQL Server instance has – when it was “born” (installed) – before we can apply any kind of horoscope predictions.  I’ll start with the base query to find the installation date:

SELECT  createdate as Sql_Server_Install_Date
FROM    sys.syslogins
where   sid = 0x010100000000000512000000 -- language neutral
        -- loginname = 'NT AUTHORITY\SYSTEM' -- only English language installations

(Thanks to Mladen P for the SQL Server install date query.)

Now let’s turn this into a function we can use on any SQL Server instance.  There are several different ways to code this function; I chose to go with a table variable return, and a table variable to hold the Zodiac fields; I could just have easily accomplished this with a CASE statement to populate the return table, but I think readability would suffer:

CREATE FUNCTION dbo.GetServerHoroscope()
RETURNS @ServerHoroscope TABLE (
    ServerName NVARCHAR(200),
    SqlServerInstallDate DATE,
    ZodiacSign NVARCHAR(20),
    QuickCharacteristics  NVARCHAR(400))
AS
/*=============================================
 Author:  Jennifer McCown Jen@MidnightDBA.com  http://www.MidnightDBA.com/Jen/ 

 Create date: 7/11/2011

 Copyright:
    This work by Jennifer McCown is licensed under a Creative Commons
    Attribution-Noncommercial-No Derivative Works 3.0 United States License.

http://creativecommons.org/licenses/by-nc-nd/3.0/us/

    (Which means, use it all you want, break it up, whatever...as long as you
    attribute me.)

 -------------------------
 Description:
    This function uses the SQL Server installation date to determine the installation's
    zodiac sign, and also returns quick astrological characteristics.

 -------------------------
 Example execution:
    SELECT * FROM dbo.GetServerHoroscope();

=============================================*/BEGIN
    -----------------------
    -- DECLARE VARIABLES --
    -----------------------
    DECLARE @SqlServerInstallDate DATE;

    DECLARE @SQLStrology TABLE (
        ZodiacSign NVARCHAR(20),
        START  NVARCHAR(20),
        [END]  NVARCHAR(20),
        StartDate DATE,
        EndDate DATE,
        QuickCharacteristics  NVARCHAR(400));

    ------------------------
    -- POPULATE VARIABLES --
    ------------------------

    /*  Thanks to Mladen Pradjic for this SQLServerInstallDate query. See
        http://weblogs.sqlteam.com/mladenp/archive/2009/07/16/How-to-check-when-was-SQL-Server-installed-with-a.aspx
     */    SELECT @SqlServerInstallDate = createdate
    FROM    sys.syslogins
    WHERE   sid = 0x010100000000000512000000 -- language neutral
            -- loginname ='NT AUTHORITY\SYSTEM' -- only English language installations

    /* Zodiac dates and characteristics pulled from http://www.whats-your-sign.com/zodiac-sign-dates.html  */    INSERT INTO     @SQLStrology (ZodiacSign,START, [END], QuickCharacteristics)
    VALUES    ('Aries', '03/21', '04/9', 'Active, Demanding, Determined, Effective, Ambitious'),
            ('Taurus', '04/20', '05/20', 'Security, Subtle strength, Appreciation, Instruction, Patience'),
            ('Gemini', '05/21', '06/20', 'Communication, Indecision, Inquisitive, Intelligent, Changeable'),
            ('Cancer', '06/21', '07/22', 'Emotion, Diplomatic, Intensity, Impulsive, Selective'),
            ('Leo', '07/23', '08/22', 'Ruling, Warmth, Generosity, Faithful, Initiative'),
            ('Virgo', '08/23', '09/22', 'Analyzing, Practical, Reflective, Observation, Thoughtful'),
            ('Libra', '09/23', '10/22', 'Balance, Justice, Truth, Beauty, Perfection'),
            ('Scorpio', '10/23', '11/21', 'Transient, Self-Willed, Purposeful, Unyielding'),
            ('Sagittarius', '11/22', '12/21', 'Philosophical, Motion, Experimentation, Optimism'),
            ('Capricorn', '12/22', '01/19', 'Determination, Dominance, Perservering, Practical, Willful'),
            ('Aquarius', '01/20', '02/18', 'Knowledge, Humanitarian, Serious, Insightful, Duplicitous'),
            ('Pisces', '02/19', '03/20', 'Fluctuation, Depth, Imagination, Reactive, Indecisive');

    -- Use the NVARCHAR start and end date strings and the SQL Server install date to build actual zodiac dates:
    UPDATE @SQLStrology SET StartDate = CAST(START + '/'+ CAST(YEAR(@SqlServerInstallDate) AS CHAR(4)) AS DATE),
        EndDate = CAST([END] + '/'+ CAST(YEAR(@SqlServerInstallDate) AS CHAR(4)) AS DATE);

    -- Insert the one applicable row into the output table:
    INSERT INTO @ServerHoroscope
    SELECT @@ServerName     as ServerName
        , @SqlServerInstallDate  AS SqlServerInstallDate
        , ZodiacSign
        , QuickCharacteristics
    FROM @SQLStrology
    WHERE @SqlServerInstallDate BETWEEN StartDate AND EndDate;

    ------------
    -- RETURN --
    ------------

    RETURN;
END

This was a fun exercise. For more on multi-statement table-valued functions, see the SQL Server Books Online article CREATE FUNCTION, especially example C.

I look forward to updating the function with your SQL-specific SQLstrology characteristics.

Happy days,

Jen McCown

http://www.MidnightDBA.com/Jen