Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Meme Monday SQLstrology – Table Valued Zodiac Function

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.