Peek Definition (Disable)

Like everything in live, one can like or dislike something. One of new feature in Visual Studio 2013 is Peak Definition. This one is something I dislike to be honest. Someone will say, then don’t use it when you don’t like it. That is true, but one of the visual studio extensions “Productivity Power Tools” adds one more way to see the Peek Definition Window. Default shortcut from Visual Studio to access it is Alt + F12, ReSharper adds a shortcut but luckily harder to hit it by mistake Ctrl + Shift + Click, but this extension adds one more which is bothering. The shortcut is Ctrl + Click. That means when you want to open the file where some type is defined, instead of opening, you get the Peek window.
For ones that this shortcut is bothering here is the way how to disable it:
- From Visual Studio open Tools/Options and then navigate to Productivity Power Tools

20 Tests that your database design should pass

Recently i discovered a small application named SqlCop that integrates many database tests. I tried the tool and it was pretty much ok, although I’ve discovered some application bugs but also some of the queries(tests) were not working in my environment. So i decided to completely rewrite all those queries, without worrying about older Sql Server versions, and stick only with views from sys schema. Queries are tested on Sql Server 2005 and 2008.

--    Procedures or functions with prefix sp_
SELECT  Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM    sys.objects o
WHERE   o.is_ms_shipped = 0
        AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')
        AND LOWER(o.name) LIKE 'sp[_]%'
        AND o.name NOT IN ('sp_alterdiagram', 'sp_creatediagram',
                               'sp_dropdiagram', 'sp_helpdiagramdefinition',
                               'sp_helpdiagrams', 'sp_renamediagram',
                               'sp_upgraddiagrams')
ORDER BY Found

--    Tables with prefix tbl
SELECT  Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name
      , t.type_desc
FROM    sys.tables t
WHERE   t.type = 'U'
        AND LOWER(t.name) LIKE 'tbl%'
ORDER BY Found

--    VarChar missing size
SELECT  Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM    sys.objects o
WHERE   o.is_ms_shipped = 0
        AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')
        AND REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), '    ', ''), ']', '') LIKE '%varchar[^(]%'
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found

--    Decimal missing size
SELECT  Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM    sys.objects o
WHERE   o.is_ms_shipped = 0
        AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')
        AND (REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), '    ', ''), ']', '') LIKE '%decimal[^(]%'
            OR
            REPLACE(REPLACE(REPLACE(LOWER(OBJECT_DEFINITION(o.object_id)), ' ', ''), '    ', ''), ']', '') LIKE '%numeric[^(]%'
            )
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found

--    Using undocumented procedures
DECLARE @Temp TABLE(ProcedureName VARCHAR(50))
INSERT INTO @Temp VALUES('sp_MStablespace')
INSERT INTO @Temp VALUES('sp_who2')
INSERT INTO @Temp VALUES('sp_tempdbspace')
INSERT INTO @Temp VALUES('sp_MSkilldb')
INSERT INTO @Temp VALUES('sp_MSindexspace')
INSERT INTO @Temp VALUES('sp_MShelptype')
INSERT INTO @Temp VALUES('sp_MShelpindex')
INSERT INTO @Temp VALUES('sp_MShelpcolumns')
INSERT INTO @Temp VALUES('sp_MSforeachtable')
INSERT INTO @Temp VALUES('sp_MSforeachdb')
INSERT INTO @Temp VALUES('sp_fixindex')
INSERT INTO @Temp VALUES('sp_columns_rowset')
INSERT INTO @Temp VALUES('sp_MScheck_uid_owns_anything')
INSERT INTO @Temp VALUES('sp_MSgettools_path')
INSERT INTO @Temp VALUES('sp_gettypestring')
INSERT INTO @Temp VALUES('sp_MSdrop_object')
INSERT INTO @Temp VALUES('sp_MSget_qualified_name')
INSERT INTO @Temp VALUES('sp_MSgetversion')
INSERT INTO @Temp VALUES('xp_dirtree')
INSERT INTO @Temp VALUES('xp_subdirs')
INSERT INTO @Temp VALUES('xp_enum_oledb_providers')
INSERT INTO @Temp VALUES('xp_enumcodepages')
INSERT INTO @Temp VALUES('xp_enumdsn')
INSERT INTO @Temp VALUES('xp_enumerrorlogs')
INSERT INTO @Temp VALUES('xp_enumgroups')
INSERT INTO @Temp VALUES('xp_fileexist')
INSERT INTO @Temp VALUES('xp_fixeddrives')
INSERT INTO @Temp VALUES('xp_getnetname')
INSERT INTO @Temp VALUES('xp_readerrorlog')
INSERT INTO @Temp VALUES('sp_msdependencies')
INSERT INTO @Temp VALUES('xp_qv')
INSERT INTO @Temp VALUES('xp_delete_file')
INSERT INTO @Temp VALUES('sp_checknames')
INSERT INTO @Temp VALUES('sp_enumoledbdatasources')
INSERT INTO @Temp VALUES('sp_MS_marksystemobject')
INSERT INTO @Temp VALUES('sp_MSaddguidcolumn')
INSERT INTO @Temp VALUES('sp_MSaddguidindex')
INSERT INTO @Temp VALUES('sp_MSaddlogin_implicit_ntlogin')
INSERT INTO @Temp VALUES('sp_MSadduser_implicit_ntlogin')
INSERT INTO @Temp VALUES('sp_MSdbuseraccess')
INSERT INTO @Temp VALUES('sp_MSdbuserpriv')
INSERT INTO @Temp VALUES('sp_MSloginmappings')
INSERT INTO @Temp VALUES('sp_MStablekeys')
INSERT INTO @Temp VALUES('sp_MStablerefs')
INSERT INTO @Temp VALUES('sp_MSuniquetempname')
INSERT INTO @Temp VALUES('sp_MSuniqueobjectname')
INSERT INTO @Temp VALUES('sp_MSuniquecolname')
INSERT INTO @Temp VALUES('sp_MSuniquename')
INSERT INTO @Temp VALUES('sp_MSunc_to_drive')
INSERT INTO @Temp VALUES('sp_MSis_pk_col')
INSERT INTO @Temp VALUES('xp_get_MAPI_default_profile')
INSERT INTO @Temp VALUES('xp_get_MAPI_profiles')
INSERT INTO @Temp VALUES('xp_regdeletekey')
INSERT INTO @Temp VALUES('xp_regdeletevalue')
INSERT INTO @Temp VALUES('xp_regread')
INSERT INTO @Temp VALUES('xp_regenumvalues')
INSERT INTO @Temp VALUES('xp_regaddmultistring')
INSERT INTO @Temp VALUES('xp_regremovemultistring')
INSERT INTO @Temp VALUES('xp_regwrite')
INSERT INTO @Temp VALUES('xp_varbintohexstr')
INSERT INTO @Temp VALUES('sp_MSguidtostr')
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM   sys.objects o
        INNER Join @Temp T ON LOWER(OBJECT_DEFINITION(o.object_id)) Like '%' + LOWER(T.ProcedureName) + '%'     
WHERE  o.is_ms_shipped = 0
        AND o.type IN ('P', 'FN', 'TF', 'IF', 'AF', 'FT', 'IS', 'PC', 'FS')
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found

--    Procedures without SET NOCOUNT ON
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM   sys.objects o
WHERE  o.is_ms_shipped = 0
        AND o.type = 'P'
        AND UPPER(OBJECT_DEFINITION(o.object_id)) NOT LIKE '%SET NOCOUNT ON%'
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found

--    Procedures with SET ROWCOUNT
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM   sys.objects o
WHERE  o.is_ms_shipped = 0
        AND o.type = 'P'
        AND UPPER(OBJECT_DEFINITION(o.object_id)) LIKE '%SET ROWCOUNT%'
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found

--    Procedures with @@identity
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM   sys.objects o
WHERE  o.is_ms_shipped = 0
        AND o.type = 'P'
        AND o.name <> 'sp_creatediagram'
        AND LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%@@identity%'
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
ORDER BY Found

--    Invalid characters in column or object names
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM   sys.objects o
WHERE  o.is_ms_shipped = 0
        AND LOWER(o.name) LIKE '%[^a-z0-9_.-]%'
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name
      , type_desc = 'COLUMN'
FROM   sys.columns c
        INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE  o.is_ms_shipped = 0
        AND LOWER(c.name) LIKE '%[^a-z0-9_.-]%'
ORDER BY Found

--    Using of float or real data types
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM   sys.objects o
WHERE  o.is_ms_shipped = 0
        AND o.name <> 'sp_creatediagram'
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
        AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%float%'
            OR
            LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%real%')
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name
      , type_desc = 'COLUMN'
FROM   sys.columns c
        INNER JOIN sys.objects o ON c.object_id = o.object_id
        INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE  o.is_ms_shipped = 0
        AND t.name IN ('float', 'real')
ORDER BY FOUND

--    Using of text, ntext or image data types
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
      , o.type_desc
FROM   sys.objects o
WHERE  o.is_ms_shipped = 0
        AND o.name <> 'sp_creatediagram'
        AND o.name NOT LIKE 'SqlQueryNotificationStoredProcedure-%'
        AND (LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%text%'
            OR
            LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%ntext%'
            OR
            LOWER(OBJECT_DEFINITION(o.object_id)) LIKE '%image%')
UNION ALL
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name
      , type_desc = 'COLUMN'
FROM   sys.columns c
        INNER JOIN sys.objects o ON c.object_id = o.object_id
        INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE  o.is_ms_shipped = 0
        AND t.name IN ('text', 'ntext', 'image')
ORDER BY FOUND

--    Collation problem. Column's collation is different from database collation
SELECT Found = OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name + '.' + c.name
      , type_desc = 'COLUMN'
FROM   sys.columns c
        INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE  o.is_ms_shipped = 0
        AND CAST(c.collation_name AS VARCHAR(50)) <> CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS VARCHAR(50))

--    Empty tables
SET NOCOUNT ON ;

CREATE TABLE #EmptyTables (Table_Name VARCHAR(100)) 

EXEC sp_msforeachtable 'If Not Exists(Select * From ?)
    Insert Into #EmptyTables Values(''?'')'

SELECT  Table_Name
FROM    #EmptyTables
ORDER BY Table_Name

DROP TABLE #EmptyTables

--    Tables without primary key
SELECT  Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name
      , type_desc = 'PRIMARY_KEY'
FROM    sys.tables t
        LEFT OUTER JOIN (SELECT object_id FROM sys.indexes WHERE is_primary_key = 1) i ON t.object_id = i.object_id
WHERE i.object_id IS NULL

--    Tables without clustered index
SELECT  Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name
      , type_desc = 'TABLE'
FROM sys.tables t
    LEFT OUTER JOIN (SELECT object_id FROM sys.indexes WHERE type = 1) i ON t.object_id = i.object_id
WHERE i.object_id IS NULL
ORDER BY Found

--    Missing foreign Keys
--      If a column ends with id but is not a foreign key, it should be renamed
SELECT  Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + '.' + c.name
      , type_desc = 'FOREIGN_KEY_CONSTRAINT'
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        LEFT OUTER JOIN (SELECT fk.parent_object_id, fkc.parent_column_id
                        FROM    sys.foreign_keys fk
                                INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
                        ) fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
        LEFT OUTER JOIN (SELECT ic.object_id, ic.column_id
                        FROM sys.index_columns ic
                            INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                            INNER JOIN (SELECT object_id
                                        FROM sys.index_columns
                                        GROUP BY object_id, index_id
                                        HAVING COUNT(*) = 1) icu ON ic.object_id = icu.object_id
                        WHERE i.is_primary_key = 1
                        ) ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE   fkc.parent_column_id IS Null
        AND ic.object_id IS NULL
        AND t.name <> 'sysdiagrams'
        AND t.name NOT LIKE '%Log'
        AND t.name NOT LIKE 'Log%'
        AND c.name Like '%Id'
ORDER BY FOUND

--    Uniqueidentifier column used in clustered index as first column and have default value newid()
--        check for default value is commented to show all clustered indexes with first column with uniqueidentifier type
--        it is posible that having such first column and that to be fine if that is a foreign key column.
--        But if you set a default values on all such columns to newid(), even so you set their values in insert procedure,
--        then you can uncomment the last check, and with that remove the ones that are fine as described.
SELECT  Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + ': ' + i.name + ' (' + c.name + ')'
      , type_desc = 'CLUSTERED_INDEX'
FROM sys.index_columns ic
    INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    INNER JOIN sys.tables t ON i.object_id = t.object_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id AND ic.column_id = c.column_id
    INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
WHERE i.type = 1
        AND ic.column_id = 1
        AND tp.name = 'uniqueidentifier'
        --AND c.default_object_id <> 0
ORDER BY Found

--    Missing index for foreign key columns
--  It is posible that creating indexes for all foreign keys is not required. So you should decide for which ones to create indexes.
SET NOCOUNT ON ;
CREATE TABLE #SmallTables (object_id int) 
EXEC sp_msforeachtable 'If Not Exists(SELECT * FROM (Select Rows = Count(*) From ?) R WHERE Rows < 100)
    Insert Into #SmallTables Values(OBJECT_ID(''?''))'

SELECT  Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + t.name + '.' + fkc.name + ' (' + c.name + ')'
      , type_desc = 'INDEX'
      , Resolution = 'CREATE NONCLUSTERED INDEX [IX_' + t.name + '_' + c.name + '] ON [' + OBJECT_SCHEMA_NAME(t.object_id) + '].[' + t.name + ']([' + c.name + '])'
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN (SELECT fk.parent_object_id, fkc.parent_column_id, fk.name
                        FROM    sys.foreign_keys fk
                                INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
                        ) fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
        LEFT OUTER JOIN (SELECT ic.object_id, ic.column_id
                        FROM sys.index_columns ic
                            INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                            INNER JOIN sys.index_columns icu ON ic.object_id = icu.object_id
                        WHERE ic.index_column_id = 1
                        ) ic ON fkc.parent_object_id = ic.object_id AND fkc.parent_column_id = ic.column_id
--        INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
        INNER JOIN #SmallTables st ON t.object_id = st.object_id
WHERE   ic.object_id IS NULL
        AND t.name <> 'sysdiagrams'
        AND t.name NOT LIKE '%Log'
        AND t.name NOT LIKE 'Log%'
        --AND tp.name <> 'uniqueidentifier'
ORDER BY FOUND

DROP TABLE #SmallTables

--    Wide tables
SELECT  Found = OBJECT_SCHEMA_NAME(t.object_id) + '.' + OBJECT_NAME(t.object_id)
      , type_desc = 'TABLE'
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.types tp ON c.system_type_id = tp.system_type_id
GROUP BY t.object_id
HAVING  SUM(ISNULL(NULLIF(CONVERT(BIGINT, tp.max_length), 8000), 0) + CASE WHEN c.collation_name IS NULL THEN 0 ELSE c.max_length END) > 8060

--    Missing index
;WITH    missing_index_impact AS (
SELECT   dm_db_missing_index_groups.index_handle,
        SUM(( dm_db_missing_index_group_stats.avg_total_user_cost
              * dm_db_missing_index_group_stats.avg_user_impact
              * ( dm_db_missing_index_group_stats.user_seeks
                  + dm_db_missing_index_group_stats.user_scans ) )) AS "total_impact",
        ( 100.00
          * SUM(dm_db_missing_index_group_stats.avg_total_user_cost
                * dm_db_missing_index_group_stats.avg_user_impact
                * ( dm_db_missing_index_group_stats.user_seeks
                    + dm_db_missing_index_group_stats.user_scans ))
          / SUM(SUM(dm_db_missing_index_group_stats.avg_total_user_cost
                    * dm_db_missing_index_group_stats.avg_user_impact
                    * ( dm_db_missing_index_group_stats.user_seeks
                        + dm_db_missing_index_group_stats.user_scans ))) OVER ( ) ) AS "percent_impact",
        ROW_NUMBER() OVER ( ORDER BY SUM(avg_total_user_cost
                                         * avg_user_impact
                                         * ( user_seeks
                                             + user_scans )) DESC ) AS rn
    FROM     sys.dm_db_missing_index_groups AS dm_db_missing_index_groups
            JOIN sys.dm_db_missing_index_group_stats AS dm_db_missing_index_group_stats ON dm_db_missing_index_groups.index_group_handle = dm_db_missing_index_group_stats.group_handle
    GROUP BY    dm_db_missing_index_groups.index_handle
),
agg_missing_index_impact AS (
    SELECT   missing_index_impact_1.index_handle,
            missing_index_impact_1.total_impact,
            SUM(missing_index_impact_2.total_impact) AS running_total_impact,
            missing_index_impact_1.percent_impact,
            SUM(missing_index_impact_2.percent_impact) AS running_total_percent,
            missing_index_impact_1.rn
    FROM     missing_index_impact AS missing_index_impact_1
            JOIN missing_index_impact AS missing_index_impact_2 ON missing_index_impact_1.rn <= missing_index_impact_2.rn
    GROUP BY    missing_index_impact_1.index_handle,
            missing_index_impact_1.total_impact,
            missing_index_impact_1.percent_impact,
            missing_index_impact_1.rn
),
missing_index_details AS (
    SELECT   dm_db_missing_index_details.database_id,
            dm_db_missing_index_details."object_id",
            dm_db_missing_index_details.index_handle,
            dm_db_missing_index_details."statement",
            dm_db_missing_index_details.equality_columns,
            dm_db_missing_index_details.inequality_columns,
            dm_db_missing_index_details.included_columns
    FROM    sys.dm_db_missing_index_details AS dm_db_missing_index_details
)
SELECT  agg_missing_index_impact.rn,
    missing_index_details."statement",
    missing_index_details.equality_columns,
    missing_index_details.inequality_columns,
    missing_index_details.included_columns,
    agg_missing_index_impact.running_total_impact,
    agg_missing_index_impact.total_impact,
    agg_missing_index_impact.running_total_percent,
    agg_missing_index_impact.percent_impact,
    "key".index_key_column_count,
    "key".index_key_column_bytes,
    "all".index_all_column_count,
    "all".index_all_column_bytes
FROM    agg_missing_index_impact AS agg_missing_index_impact
    JOIN missing_index_details AS missing_index_details ON agg_missing_index_impact.index_handle = missing_index_details.index_handle
    JOIN ( SELECT   missing_index_details1.index_handle,
                    COUNT(*) AS index_key_column_count,
                    SUM(COL_LENGTH(missing_index_details1."statement",
                                   dm_db_missing_index_columns1.column_name)) AS index_key_column_bytes
           FROM     missing_index_details AS missing_index_details1
                    CROSS APPLY sys.dm_db_missing_index_columns(missing_index_details1.index_handle) AS dm_db_missing_index_columns1
           WHERE    dm_db_missing_index_columns1.column_usage = 'EQUALITY'
                    OR dm_db_missing_index_columns1.column_usage = 'INEQUALITY'
           GROUP BY    missing_index_details1.index_handle
         ) AS "key" ON missing_index_details.index_handle = "key".index_handle
    JOIN ( SELECT   missing_index_details2.index_handle,
                    COUNT(*) AS index_all_column_count,
                    SUM(COL_LENGTH(missing_index_details2."statement",
                                   dm_db_missing_index_columns2.column_name)) AS index_all_column_bytes
           FROM     missing_index_details AS missing_index_details2
                CROSS APPLY sys.dm_db_missing_index_columns(missing_index_details2.index_handle) AS dm_db_missing_index_columns2
           GROUP BY    missing_index_details2.index_handle
         ) AS "all" ON missing_index_details.index_handle = "all".index_handle
--WHERE missing_index_details.database_id = ?
ORDER BY    agg_missing_index_impact.rn ASC ;

Calculate DateTime difference in SQL Server with ITVF

If you work with Date types in SQL Server, calculating date difference is not simple because of the way DateDiff work. You need to write functions for calculating years difference between two dates, months difference, etc. You can do that creating like always scalar function that will do some calculations and return a value. If you use such a scalar function in a SELECT clause and that select returns large number of rows, you will soon realize that calculating scalar value will be significant part of the total execution time. Not just that, but also if you open profiler to see SQL batches, you will see all that calls to scalar functions for every row in the query as a row in the profiler, and with such a large number of rows in the profiler, you can miss the important ones. SQL Server since version 2000 have introduced a great feature called Inline Table Value Functions (iTVF). iTVF's are such an important feature because using them in combination with CROSS APPLY you can really have code reusability which is hard to achieve in any SQL language. If you do some complex columns calculations for example calculating some distributions, and you do that same calculations in many procedures, then creating ITVF and put the complex calculations can be your boost. After that changing the distribution and fixing bugs will be on one place only.
To return to the title and write functions that are very useful in any database. I will present two just to give you idea, from which you can create some others. First the function for calculating Years difference between two dates:

CREATE FUNCTION dbo.DateDiffYears
(
     @DateFrom AS DATETIME
     , @DateTo AS DATETIME
)
RETURNS TABLE
AS
     RETURN SELECT Years = DATEDIFF(yy, @DateFrom, @DateTo)
- CASE WHEN DATEPART(dy, @DateFrom) > DATEPART(dy, @DateTo) 
THEN 1 ELSE 0 END

Now you can use with CROSS APPLY in your queries. Imagine a table Persons that have a column BirthDate. You need to return their age in the result set. You will have something like this:
SELECT PersonId
       , Age = DY.Years
FROM   Persons
       CROSS APPLY dbo.DateDiffYears(BirthDate, GETDATE())

The second one is called DateSpan, not like .NET TimeSpan but if you need the same one, you can easely create one, which will return Years, Months and Days difference between two days:
CREATE FUNCTION dbo.DateSpan
(
       @DateFrom AS DATETIME
       , @DateTo AS DATETIME
)
RETURNS TABLE
AS
       RETURN SELECT Years, TotalMonths, Months = TotalMonths - Years * 12, TotalDays, [Days]
       FROM (
              Years = DATEDIFF(yy, @DateFrom, @DateTo) - CASE WHEN DATEPART(dy, @DateFrom) > DATEPART(dy, @DateTo) THEN 1 ELSE 0 END
              , TotalMonths = DATEDIFF(mm, @DateFrom, @DateTo) - CASE WHEN DATEPART(d, @DateFrom) > DATEPART(d, @DateTo) THEN 1 ELSE 0 END
              , TotalDays = DATEDIFF(dd, @DateFrom, @DateTo)
              , [Days] = ABS(DAY(@DateTo) - DAY(@DateFrom) + DAY(DATEADD(d, -DAY(@DateTo), @DateTo)))
       ) D
The performance difference between ITVF and scalar functions is big and lineary grows on number of rows, and you will not see any additional row in the profiler.

Calculate DateTime Difference in SQL Server

I have no idea, why still SQL Server have no support for TimeSpan type. Also in 2008 version will be so many new DateTime types but still functions for manipulating DateTime values are few. If you need to calculate difference between two dates, but that difference to be represented as years:months: days, you will realize that that is not an easy task. There is DateDiff function, but when you try to use it you will see that this function will not return desired result. At least to get the result in a easy way.

I was in a need for such a function so I start looking for on the net. I found one which was pretty much what i needed. However I decide to create my own, much simpler and just for what I need. So here it is:

CREATE FUNCTION [dbo].[TotalDateDiff]
(
        @DateFrom AS SMALLDATETIME,
        @DateTo AS SMALLDATETIME
)
RETURNS CHAR(8)
AS
BEGIN
    DECLARE @Result CHAR(8)
    DECLARE @Years SMALLINT, @Months SMALLINT, @Days SMALLINT

    SELECT    @Years = DATEDIFF(YEAR, @DateFrom, @DateTo),
            @Months = DATEDIFF(MONTH, @DateFrom, @DateTo)

    SET @Months = @Months - @Years * 12

    SET @Days = DAY(@DateTo) - DAY(@DateFrom)
    IF @Days < 0
        BEGIN
            SET @Months = @Months - 1
            SET @Days = DAY(DATEADD(DAY, -DAY(@DateTo), @DateTo)) + @Days
        END

    IF @Months < 0
        BEGIN
            SET @Years = @Years - 1
            SET @Months = 12 + @Months
        END

    SET @Result = RIGHT('0' + CAST(@Years AS VARCHAR(2)), 2) + ':' +
                  RIGHT('0' + CAST(@Months AS VARCHAR(2)), 2) + ':' +
                  RIGHT('0' + CAST(@Days AS VARCHAR(2)), 2)
    RETURN @Result
END

Visual C# MVP Award

Today I receive mail from Microsoft:

Congratulations! We are pleased to present you with the 2008 Microsoft® MVP Award! The MVP Award is our way to say thank you for promoting the spirit of community and improving people’s lives and the industry’s success every day. We appreciate your extraordinary efforts in Visual C# technical communities during the past year.

Thanks Microsoft, I really appreciate this award.

My efforts in past couple of years on msdn forums, in particular C#, ADO, SQL forums, for helping other developers is the main reason. My nickname is boban.s, so you can find some very useful posts from ones about base class libraries in .NET though posts related with windows application type of problems to threading, localization, application update etc.

You probably know that winning MVP award is a result of many activities in public community, writing books, blogs, managing user groups, etc. I would like to make this blog active in next year in order to get award for next year too. I will post mainly about C#, but also about T-SQL and SharePoint. I already have in mind what will be the next post.

SMA Technical Indicator

I saw a question on MSDN Forums about having real-time SMA indicator. So even if I never used this indicator in real systems, I know it's simplest one and decide to develop it. So here it is:

public class SMA
{
    private readonly int _Length;
    private readonly bool _StoreData;
    private decimal _Value;
    private decimal _Price;
    private bool _Primed;
    private readonly string _Name;
    private readonly DecimalCollection _PriceArray = new DecimalCollection();
    private readonly DecimalCollection _ValueArray = new DecimalCollection();

    public SMA(int length) : this(length, false)
    {
    }

    /// <summary>
    /// Class for calculating Simple Moving Average
    /// </summary>
    /// <param name="length">Lenght SMA calculation formula</param>
    /// <param name="storeData"></param>
    public SMA(int length, bool storeData)
    {
        _Length = length;
        _StoreData = storeData;
        _Name = GetType().Name + Length;
    }

    public int Length
    {
        get { return _Length; }
    }

    public bool StoreData
    {
        get { return _StoreData; }
    }

    public decimal Value
    {
        get { return _Value; }
    }

    public bool Primed
    {
        get { return _Primed; }
    }

    public string Name
    {
        get { return _Name; }
    }

    public DecimalCollection ValueArray
    {
        get { return _ValueArray; }
    }

    public void PriceTick(decimal price, bool add)
    {
        if (add)
            AddPrice(price);
        else
            EditPrice(price);
    }

    public void PriceTicks(DecimalCollection prices)
    {
        if (prices == null || prices.Count == 0)
            return;
        for (int i = 0; i < prices.Count; i++)
        {
            AddPrice(prices[i]);
        }
    }

    private void AddPrice(decimal price)
    {
        _Price = price;
        if (!_Primed)
        {
            _PriceArray.Add(_Price);
            if (_PriceArray.Count == Length)
            {
                _Primed = true;
                _Value = _PriceArray.Average();
            }
        }
        else
        {
            _PriceArray.RemoveAt(0);
            _PriceArray.Add(price);

            _Value = _PriceArray.Average();

            if (_StoreData)
            {
                ValueArray.Add(_Value);
            }
        }

    }

    private void EditPrice(decimal price)
    {
        if (price != _Price)
        {
            _Price = price;

            _PriceArray[_PriceArray.Count - 1] = _Price;

            if (_Primed)
            {
                if (_PriceArray.Count == _Length)
                {
                    _Value = _PriceArray.Average();
                    if (_StoreData)
                    {
                        ValueArray[ValueArray.Count - 1] = _Value;
                    }
                }
                else
                {
                    _Value = _PriceArray.Average();
                    if (_StoreData)
                    {
                        ValueArray[ValueArray.Count - 1] = _Value;
                    }
                }
            }
        }
    }
}

This source uses DecimalCollection class that is already published on my blog.

Password Generator

You have probably got in situations when you need a password generator, somethimes for generating keys like for generating ticket for thin client using webservices, or for your personal use when you need to create a key for user account, but want that to be randomly. Here is a good piece of code for generating simple or strong passwords.

public static string GeneratePassword(int passwordLen, bool includeSmallLetters, bool includeBigLetters,
                            bool includeNumbers, bool includeSpecialCharacters)
{
   char[] returnValue = new char[passwordLen];
   char[] Choises;
   string smallLetters = string.Empty;
   string bigLetters = string.Empty;
   string numbers = string.Empty;
   string specialcharacters = string.Empty;

   int choisesLen = 0;

   if (includeSmallLetters)
   {
      smallLetters = "abcdefghijklmnopqrstuvwxyz";
      choisesLen += smallLetters.Length;
   }
   if (includeBigLetters)
   {
      bigLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
      choisesLen += bigLetters.Length;
   }
   if (includeNumbers)
   {
      numbers = "012345678901234567890123456789";
      choisesLen += numbers.Length;
   }
   if (includeSpecialCharacters)
   {
      specialcharacters = "~`!@#$%^&*()-_+=\|<,>.?/ {[}]";
      choisesLen += specialcharacters.Length;
   }
   if (choisesLen == 0)
      throw new ArgumentOutOfRangeException("includeSmallLetters",
                                  "At least one type of characters must be included!");

   Choises = (smallLetters + bigLetters + numbers + specialcharacters).ToCharArray();
   Random rnd = new Random();

   for (int i = 0; i < passwordLen; i++)
   {
      returnValue[i] = Choises[rnd.Next(choisesLen - 1)];
   }
   return new string(returnValue);
}