DATE CALENDAR

Over the years I have had some of the strangest / most complex requirements for data querying / reporting that you could imagine. The most difficult often involves some type of strange business rule or requirement surrounding a date parameter, logic, range, etc. I've put this code together in order to pre-calculate many elements of a date's characteristics and be able to quickly determine various date characteristics / values without the need to break my head every time things get hairy.

To run it, simply choose the database in which you want the table to reside, populate the @Date_Start and @Date_End variables with the date range you want populated in the calendar, and execute.

Below is a listing of the output fields and their description using a date of 03/19/2014 (MM/DD/YYYY) as the reference example (the code should compensate for how any SQL Server instance is set up to handle the internal settings for start / end of week, weekdays, etc.). All values after the calendar_date field are specific to the date value found in each individual record.

• calendar_date: calendar date value (2014-03-06 00:00:00.000)
• calendar_year: year portion of the date (2014)
• calendar_month: month portion of the date (03)
• calendar_day: day portion of the date (19)
• calendar_quarter: quarter in which the date value falls under (4)
• first_day_in_week: first day of the week in which the date value is found
• last_day_in_week: last day of the week in which the date value is is_week_in_same_month: is the first_day_in_week and last_day_in_week value contained within the same month - Boolean (1)
• first_day_in_month: first day of the month (2014-03-01 00:00:00.000)
• last_day_in_month: last day of the month (2014-03-31 00:00:00.000)
• is_last_day_in_month: is the date value the last day of the month (0)
• first_day_in_quarter: first day of the quarter (2014-03-01 00:00:00.000)
• last_day_in_quarter: last day of the quarter (2014-12-31 00:00:00.000)
• is_last_day_in_quarter: is the date value the last day of the quarter (0)
• day_of_week: day of the week (3)
• week_of_month: week of the month (2)
• week_of_quarter: week of the quarter (2)
• week_of_year: week of the year (41)
• days_in_month: total days in the month (31)
• month_days_remaining: number of days remaining in the month (25)
• weekdays_in_month: number of weekdays in the the month (22)
• month_weekdays_remaining: number of weekdays remaining in the month (18)
• month_weekdays_completed: number of weekdays completed in the month (4)
• days_in_quarter: total days in the quarter (92)
• quarter_days_remaining: number of days remaining in the quarter (86)
• quarter_days_completed: number of days completed in the quarter (6)
• weekdays_in_quarter: number of weekdays in the the quarter (66)
• quarter_weekdays_remaining: number of weekdays remaining in the quarter (62)
• quarter_days_completed: number of days completed in the quarter (6)
• day_of_year: number of days completed in the year (279)
• year_days_remaining: number of days remaining in the year (86)
• is_weekday: is the date a weekday - Boolean (1)
• is_leap_year: is the date contained within a leap year - Boolean (0)
• day_name: full name of the day (Tuesday)
• month_day_name_instance: number of occurrences of the day_name within the month up until and including the specified date (1)
• quarter_day_name_instance: number of occurrences of the day_name within the quarter up until and including the specified date (1)
• year_day_name_instance: number of occurrences of the day_name within the year up until and including the specified date (40)
• month_name: full name of the month (October)
• year_week: calendar_year and week_of_year (left padded with zeros) values concatenated (201441)
• year_month: calendar_year and calendar_month (left padded with zeros) values concatenated (201403)
• year_quarter: calendar_year and calendar_quarter (prefixed with a "Q") values concatenated (2014Q4)

 SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON
GO




DECLARE @Date_Start AS DATETIME
DECLARE @Date_End AS DATETIME


SET @Date_Start = '20140319'
SET @Date_End = '20301231'


----------------------------------------------------------------------------------------------------------------------
--    Error Trapping: Check If Permanent Table(s) Already Exist(s) And Drop If Applicable
----------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'date_calendar', N'U') IS NOT NULL
BEGIN

    DROP TABLE date_calendar

END


----------------------------------------------------------------------------------------------------------------------
--    Permanent Table: Create Date Xref Table
----------------------------------------------------------------------------------------------------------------------

CREATE TABLE date_calendar

    (
         calendar_date DATETIME NOT NULL
        ,calendar_year SMALLINT
        ,calendar_month TINYINT
        ,calendar_day TINYINT
        ,calendar_quarter TINYINT
        ,first_day_in_week DATETIME
        ,last_day_in_week DATETIME
        ,is_week_in_same_month INT
        ,first_day_in_month DATETIME
        ,last_day_in_month DATETIME
        ,is_last_day_in_month INT
        ,first_day_in_quarter DATETIME
        ,last_day_in_quarter DATETIME
        ,is_last_day_in_quarter INT
        ,day_of_week TINYINT
        ,week_of_month TINYINT
        ,week_of_quarter TINYINT
        ,week_of_year TINYINT
        ,days_in_month TINYINT
        ,month_days_remaining TINYINT
        ,weekdays_in_month TINYINT
        ,month_weekdays_remaining TINYINT
        ,month_weekdays_completed TINYINT
        ,days_in_quarter TINYINT
        ,quarter_days_remaining TINYINT
        ,quarter_days_completed TINYINT
        ,weekdays_in_quarter TINYINT
        ,quarter_weekdays_remaining TINYINT
        ,quarter_weekdays_completed TINYINT
        ,day_of_year SMALLINT
        ,year_days_remaining SMALLINT
        ,is_weekday INT
        ,is_leap_year INT
        ,day_name VARCHAR (10)
        ,month_day_name_instance TINYINT
        ,quarter_day_name_instance TINYINT
        ,year_day_name_instance TINYINT
        ,month_name VARCHAR (10)
        ,year_week CHAR (6)
        ,year_month CHAR (6)
        ,year_quarter CHAR (6)
    );


----------------------------------------------------------------------------------------------------------------------
--    Table Insert: Populate Base Date Values Into Permanent Table Using Common Table Expression (CTE)
----------------------------------------------------------------------------------------------------------------------

WITH cte_date_base_table AS

    (
        SELECT
            @Date_Start AS calendar_date

        UNION ALL

        SELECT
            DATEADD (DAY, 1, CTE.calendar_date)
        FROM
            cte_date_base_table CTE
        WHERE
            DATEADD (DAY, 1, CTE.calendar_date) <= @Date_End
    )

INSERT INTO date_calendar

    (
        calendar_date
    )

SELECT
    CTE.calendar_date
FROM
    cte_date_base_table CTE
OPTION
    (MAXRECURSION 0)


----------------------------------------------------------------------------------------------------------------------
--    Table Update I: Populate Additional Date Xref Table Fields (Pass I)
----------------------------------------------------------------------------------------------------------------------

UPDATE
    date_calendar
SET
     calendar_year = DATEPART (YEAR, calendar_date)
    ,calendar_month = DATEPART (MONTH, calendar_date)
    ,calendar_day = DATEPART (DAY, calendar_date)
    ,calendar_quarter = DATEPART (QUARTER, calendar_date)
    ,first_day_in_week = DATEADD (DAY, -DATEPART (WEEKDAY, calendar_date) + 1, calendar_date)
    ,first_day_in_month = CONVERT (VARCHAR (6), calendar_date, 112) + '01'
    ,day_of_week = DATEPART (WEEKDAY, calendar_date)
    ,week_of_year = DATEPART (WEEK, calendar_date)
    ,day_of_year = DATEPART (DAYOFYEAR, calendar_date)
    ,is_weekday = ISNULL ((CASE
                                WHEN ((@@DATEFIRST - 1) + (DATEPART (WEEKDAY, calendar_date) - 1)) % 7 NOT IN (5, 6) THEN 1
                                END), 0)
    ,day_name = DATENAME (WEEKDAY, calendar_date)
    ,month_name = DATENAME (MONTH, calendar_date)


ALTER TABLE date_calendar ALTER COLUMN calendar_year INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN calendar_month INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN calendar_day INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN calendar_quarter INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN first_day_in_week DATETIME NOT NULL


ALTER TABLE date_calendar ALTER COLUMN first_day_in_month DATETIME NOT NULL


ALTER TABLE date_calendar ALTER COLUMN day_of_week INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN week_of_year INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN day_of_year INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN is_weekday INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN day_name VARCHAR (10) NOT NULL


ALTER TABLE date_calendar ALTER COLUMN month_name VARCHAR (10) NOT NULL


CREATE NONCLUSTERED INDEX IX_date_calendar_calendar_year ON date_calendar (calendar_year)


CREATE NONCLUSTERED INDEX IX_date_calendar_calendar_month ON date_calendar (calendar_month)


CREATE NONCLUSTERED INDEX IX_date_calendar_calendar_quarter ON date_calendar (calendar_quarter)


CREATE NONCLUSTERED INDEX IX_date_calendar_first_day_in_week ON date_calendar (first_day_in_week)


CREATE NONCLUSTERED INDEX IX_date_calendar_day_of_week ON date_calendar (day_of_week)


CREATE NONCLUSTERED INDEX IX_date_calendar_is_weekday ON date_calendar (is_weekday)


----------------------------------------------------------------------------------------------------------------------
--    Table Update II: Populate Additional Date Xref Table Fields (Pass II)
----------------------------------------------------------------------------------------------------------------------

UPDATE
    date_calendar
SET
     last_day_in_week = first_day_in_week + 6
    ,last_day_in_month = DATEADD (MONTH, 1, first_day_in_month) - 1
    ,first_day_in_quarter = A.first_day_in_quarter
    ,last_day_in_quarter = A.last_day_in_quarter
    ,week_of_month = DATEDIFF (WEEK, first_day_in_month, calendar_date) + 1
    ,week_of_quarter = (week_of_year - A.min_week_of_year_in_quarter) + 1
    ,is_leap_year = ISNULL ((CASE
                                WHEN calendar_year % 400 = 0 THEN 1
                                WHEN calendar_year % 100 = 0 THEN 0
                                WHEN calendar_year % 4 = 0 THEN 1
                                END),0)
    ,year_week = CONVERT (VARCHAR (4), calendar_year) + RIGHT ('0' + CONVERT (VARCHAR (2), week_of_year), 2)
    ,year_month = CONVERT (VARCHAR (4), calendar_year) + RIGHT ('0' + CONVERT (VARCHAR (2), calendar_month), 2)
    ,year_quarter = CONVERT (VARCHAR (4), calendar_year) + 'Q' + CONVERT (VARCHAR (1), calendar_quarter)
FROM

    (
        SELECT
             X.calendar_year AS subquery_calendar_year
            ,X.calendar_quarter AS subquery_calendar_quarter
            ,MIN (X.calendar_date) AS first_day_in_quarter
            ,MAX (X.calendar_date) AS last_day_in_quarter
            ,MIN (X.week_of_year) AS min_week_of_year_in_quarter
        FROM
            date_calendar X
        GROUP BY
             X.calendar_year
            ,X.calendar_quarter
    ) A

WHERE
    A.subquery_calendar_year = calendar_year
    AND A.subquery_calendar_quarter = calendar_quarter


ALTER TABLE date_calendar ALTER COLUMN last_day_in_week DATETIME NOT NULL


ALTER TABLE date_calendar ALTER COLUMN last_day_in_month DATETIME NOT NULL


ALTER TABLE date_calendar ALTER COLUMN first_day_in_quarter DATETIME NOT NULL


ALTER TABLE date_calendar ALTER COLUMN last_day_in_quarter DATETIME NOT NULL


ALTER TABLE date_calendar ALTER COLUMN week_of_month INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN week_of_quarter INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN is_leap_year INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN year_week VARCHAR (6) NOT NULL


ALTER TABLE date_calendar ALTER COLUMN year_month VARCHAR (6) NOT NULL


ALTER TABLE date_calendar ALTER COLUMN year_quarter VARCHAR (6) NOT NULL


CREATE NONCLUSTERED INDEX IX_date_calendar_last_day_in_week ON date_calendar (last_day_in_week)


CREATE NONCLUSTERED INDEX IX_date_calendar_year_month ON date_calendar (year_month)


CREATE NONCLUSTERED INDEX IX_date_calendar_year_quarter ON date_calendar (year_quarter)


----------------------------------------------------------------------------------------------------------------------
--    Table Update III: Populate Additional Date Xref Table Fields (Pass III)
----------------------------------------------------------------------------------------------------------------------

UPDATE
    date_calendar
SET
     is_last_day_in_month = (CASE
                                WHEN last_day_in_month = calendar_date THEN 1
                                ELSE 0
                                END)
    ,is_last_day_in_quarter = (CASE
                                    WHEN last_day_in_quarter = calendar_date THEN 1
                                    ELSE 0
                                    END)
    ,days_in_month = DATEPART (DAY, last_day_in_month)
    ,weekdays_in_month = A.weekdays_in_month
    ,days_in_quarter = DATEDIFF (DAY, first_day_in_quarter, last_day_in_quarter) + 1
    ,quarter_days_remaining = DATEDIFF (DAY, calendar_date, last_day_in_quarter)
    ,weekdays_in_quarter = B.weekdays_in_quarter
    ,year_days_remaining = (365 + is_leap_year) - day_of_year
FROM

    (
        SELECT
             X.year_month AS subquery_year_month
            ,SUM (X.is_weekday) AS weekdays_in_month
        FROM
            date_calendar X
        GROUP BY
            X.year_month
    ) A

    ,(
        SELECT
             X.year_quarter AS subquery_year_quarter
            ,SUM (X.is_weekday) AS weekdays_in_quarter
        FROM
            date_calendar X
        GROUP BY
            X.year_quarter
     ) B

WHERE
    A.subquery_year_month = year_month
    AND B.subquery_year_quarter = year_quarter


ALTER TABLE date_calendar ALTER COLUMN is_last_day_in_month INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN is_last_day_in_quarter INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN days_in_month INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN weekdays_in_month INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN days_in_quarter INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN quarter_days_remaining INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN weekdays_in_quarter INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN year_days_remaining INT NOT NULL


----------------------------------------------------------------------------------------------------------------------
--    Table Update IV: Populate Additional Date Xref Table Fields (Pass IV)
----------------------------------------------------------------------------------------------------------------------

UPDATE
    date_calendar
SET
     month_weekdays_remaining = weekdays_in_month - A.month_weekdays_remaining_subtraction
    ,quarter_weekdays_remaining = weekdays_in_quarter - A.quarter_weekdays_remaining_subtraction
FROM

    (
        SELECT
             X.calendar_date AS subquery_calendar_date
            ,ROW_NUMBER () OVER
                                (
                                    PARTITION BY
                                        X.year_month
                                    ORDER BY
                                        X.calendar_date
                                ) AS month_weekdays_remaining_subtraction
            ,ROW_NUMBER () OVER
                                (
                                    PARTITION BY
                                        X.year_quarter
                                    ORDER BY
                                        X.calendar_date
                                ) AS quarter_weekdays_remaining_subtraction
        FROM
            date_calendar X
        WHERE
            X.is_weekday = 1
    ) A

WHERE
    A.subquery_calendar_date = calendar_date


----------------------------------------------------------------------------------------------------------------------
--    Table Update V: Populate Additional Date Xref Table Fields (Pass V)
----------------------------------------------------------------------------------------------------------------------

UPDATE
    date_calendar
SET
     month_weekdays_remaining = A.month_weekdays_remaining
    ,quarter_weekdays_remaining = A.quarter_weekdays_remaining
FROM

    (
        SELECT
             X.calendar_date AS subquery_calendar_date
            ,COALESCE (Y.month_weekdays_remaining, Z.month_weekdays_remaining, X.weekdays_in_month) AS month_weekdays_remaining
            ,COALESCE (Y.quarter_weekdays_remaining, Z.quarter_weekdays_remaining, X.weekdays_in_quarter) AS quarter_weekdays_remaining
        FROM
            date_calendar X
            LEFT JOIN date_calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
                AND Y.year_month = X.year_month
            LEFT JOIN date_calendar Z ON DATEADD (DAY, 2, Z.calendar_date) = X.calendar_date
                AND Z.year_month = X.year_month
        WHERE
            X.month_weekdays_remaining IS NULL
    ) A

WHERE
    A.subquery_calendar_date = calendar_date


ALTER TABLE date_calendar ALTER COLUMN month_weekdays_remaining INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN quarter_weekdays_remaining INT NOT NULL


----------------------------------------------------------------------------------------------------------------------
--    Table Update VI: Populate Additional Date Xref Table Fields (Pass VI)
----------------------------------------------------------------------------------------------------------------------

UPDATE
    date_calendar
SET
     is_week_in_same_month = A.is_week_in_same_month
    ,month_days_remaining = days_in_month - calendar_day
    ,month_weekdays_completed = weekdays_in_month - month_weekdays_remaining
    ,quarter_days_completed = days_in_quarter - quarter_days_remaining
    ,quarter_weekdays_completed = weekdays_in_quarter - quarter_weekdays_remaining
    ,month_day_name_instance = A.month_day_name_instance
    ,quarter_day_name_instance = A.quarter_day_name_instance
    ,year_day_name_instance = A.year_day_name_instance
FROM

    (
        SELECT
             X.calendar_date AS subquery_calendar_date
            ,ISNULL ((CASE
                        WHEN DATEDIFF (MONTH, X.first_day_in_week, X.last_day_in_week) = 0 THEN 1
                        END), 0) AS is_week_in_same_month
            ,ROW_NUMBER () OVER
                                (
                                    PARTITION BY
                                         X.year_month
                                        ,X.day_name
                                    ORDER BY
                                        X.calendar_date
                                ) AS month_day_name_instance
            ,ROW_NUMBER () OVER
                                (
                                    PARTITION BY
                                         X.year_quarter
                                        ,X.day_name
                                    ORDER BY
                                        X.calendar_date
                                ) AS quarter_day_name_instance
            ,ROW_NUMBER () OVER
                                (
                                    PARTITION BY
                                         X.calendar_year
                                        ,X.day_name
                                    ORDER BY
                                        X.calendar_date
                                ) AS year_day_name_instance
        FROM
            date_calendar X
    ) A

WHERE
    A.subquery_calendar_date = calendar_date


ALTER TABLE date_calendar ALTER COLUMN is_week_in_same_month INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN month_days_remaining INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN month_weekdays_completed INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN quarter_days_completed INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN quarter_weekdays_completed INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN month_day_name_instance INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN quarter_day_name_instance INT NOT NULL


ALTER TABLE date_calendar ALTER COLUMN year_day_name_instance INT NOT NULL


----------------------------------------------------------------------------------------------------------------------
--    Main Query: Final Display / Output
----------------------------------------------------------------------------------------------------------------------

SELECT
    *
FROM
    date_calendar 

ORDER BY
    calendar_date

thanks

venkat

Data Encryption and Decryption

Encryption of data is a huge multi-facted and complex topic that fills entire books. 

In my opinion, encryption of data, or any other data security measure, is not a one-size-fits-all technology, nor should any organisation rely on just one form of encryption or data security.  Security of any kind is very much subject to a vicious cycle of  invention-circumvention, and should therefore be regularly reviewed and updated.  This has been the case with physical security for as long as it has existed (locks, safes etc) and will likely continue to be the case with software and data security

 

Security of data is never going to be absolute, and all any organisation can do is protect against the majority of criminals or criminal intent.  The dedicated hacker is a hard animal to defeat, but the casual thief is much easier to thwart.

 

I have worked for organisations at all ends of the security spectrum, from financial to production line.  Not all of them use the most leading edge methods for every piece of data as that would be prohibitively expensive and complex, but all of them used data security technology.

 

RC4 encryption is an older technology, whose algorithms are public knowledge, but to the casual or mildy experiences hacker, it can still be a locked door.

 

It is relatively simple to implement, and if the appropriate measures are taken to secure all (or part) of the encryption key, it can be effective enough.

 

The following code provides a function-based implementation of RC4 encryption.  Key management is not covered as that is usually site-specific.

 

 

Inner Function:

USE [yourdbname]
GO
/****** Object:  UserDefinedFunction [dbo].[fnInitRc4]    Script Date: 3/12/2014 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[fnInitRc4]
(
    @Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS

BEGIN
    DECLARE    @Key TABLE (i TINYINT, v TINYINT)

    DECLARE    @Index SMALLINT,
        @PwdLen TINYINT

    SELECT    @Index = 0,
        @PwdLen = LEN(@Pwd)

    WHILE @Index <= 255
        BEGIN
            INSERT    @Key
                (
                    i,
                    v
                )
            VALUES    (
                    @Index,
                     ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
                )

            INSERT    @Box
                (
                    i,
                    v
                )
            VALUES    (
                    @Index,
                    @Index
                )

            SELECT    @Index = @Index + 1
        END


    DECLARE    @t TINYINT,
        @b SMALLINT

    SELECT    @Index = 0,
        @b = 0

    WHILE @Index <= 255
        BEGIN
            SELECT        @b = (@b + b.v + k.v) % 256
            FROM        @Box AS b
            INNER JOIN    @Key AS k ON k.i = b.i
            WHERE        b.i = @Index

            SELECT    @t = v
            FROM    @Box
            WHERE    i = @Index

            UPDATE    b1
            SET    b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
            FROM    @Box b1
            WHERE    b1.i = @Index

            UPDATE    @Box
            SET    v = @t
            WHERE    i = @b

            SELECT    @Index = @Index + 1
        END

    RETURN
END

 Encryption and Decryption related funcation:

USE [yourdbname]
GO
/****** Object:  UserDefinedFunction [dbo].[fnEncDecRc4]    Script Date: 3/12/2014 12:20:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnEncDecRc4]
(
    @Pwd VARCHAR(256),
    @Text VARCHAR(8000)
)
RETURNS    VARCHAR(8000)
AS

BEGIN
    DECLARE    @Box TABLE (i TINYINT, v TINYINT)

    INSERT    @Box
        (
            i,
            v
        )
    SELECT    i,
        v
    FROM    dbo.fnInitRc4(@Pwd)

    DECLARE    @Index SMALLINT,
        @i SMALLINT,
        @j SMALLINT,
        @t TINYINT,
        @k SMALLINT,
              @CipherBy TINYINT,
              @Cipher VARCHAR(8000)

    SELECT    @Index = 1,
        @i = 0,
        @j = 0,
        @Cipher = ''

    WHILE @Index <= DATALENGTH(@Text)
        BEGIN
            SELECT    @i = (@i + 1) % 256

            SELECT    @j = (@j + b.v) % 256
            FROM    @Box b
            WHERE    b.i = @i

            SELECT    @t = v
            FROM    @Box
            WHERE    i = @i

            UPDATE    b
            SET    b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
            FROM    @Box b
            WHERE    b.i = @i

            UPDATE    @Box
            SET    v = @t
            WHERE    i = @j

            SELECT    @k = v
            FROM    @Box
            WHERE    i = @i

            SELECT    @k = (@k + v) % 256
            FROM    @Box
            WHERE    i = @j

            SELECT    @k = v
            FROM    @Box
            WHERE    i = @k

            SELECT    @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
                @Cipher = @Cipher + CHAR(@CipherBy)

            SELECT    @Index = @Index  +1
              END

    RETURN    @Cipher
END

 

 

thanks

venkatesh

 

Missing Index

I was trying to identify the missing indexes in my SQL Server 2012 database and I used the Display Estimated Execution Plan in the SQL Server Management Studio. Even after I created the suggested index, succeeding attempts to display the execution plan still showed the index that I just created as missing.

SQL Server keeps data about possible missing indexes and the script below helped me to show all the missing indexes that SQL Server was tracking as well as the actual scripts to create these missing indexes. I got it from this link:

http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

SELECT

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +

CASE

WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

GO

In my case, I saw multiple suggestions for missing indexes for the same table I was having trouble on. When I created all the suggested indexes, the problem with the Display Estimated Execution Plan query did not appear anymore and the performance of the query was great.