To obtain SQL Server 2008R2 Product Key

To obtain SQL Server 2008R2 Product Key:
  USE MASTER

GO



EXEC XP_REGREAD 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\100\BIDS\Setup','ProductCode'

GO

Convert time to string - SQL

Scenario where a db field contains seconds, the user wanted a SSRS report displaying various aggregates of the seconds field as Hours - Minutes - Seconds.



IF OBJECT_ID('dbo.udf_SCC_TimeToString') IS NOT NULLDROP FUNCTION dbo.udf_SCC_TimeToString ;GO
CREATE FUNCTION dbo.udf_SCC_TimeToString(@timesec INT) --input in seconds as integerRETURNS VARCHAR(25)/*==========================================
Dom Horton
17/10/2012
==========================================*/AS BEGIN
DECLARE @return
AS VARCHAR(25),@a AS INT,@b AS INT,@hours AS INT, @mins
AS INT, @secs
AS INT;SET @a = @timesec%3600 --get no. of seconds over the hourSET @hours = (@timesec - @a)/3600 --get no. of seconds of completed hours, divide by hour in seconds to get completed hoursSET @b = @a --hold no. of secs over the hourSET @a = @b%60--get no. of secs over the minSET @mins = (@b - @a)/60 --get no. of seconds of completed mins, divide by min in secs to get completed minutesSET @secs = @aSET @return =CASE
WHEN @timesec >=3600
THENconvert(varchar(10),@hours) + ' hr ' +convert(varchar(10),@mins) + ' min ' +convert(varchar(10),@secs) + ' sec 'WHEN @timesec >=60
THENconvert(varchar(10),@mins) + ' min ' +convert(varchar(10),@secs) + ' sec 'ELSEconvert(varchar(10),@secs) + ' sec 'END;RETURN @returnEND


Example below showing the Stored Procedure in use:

and in use in a SSRS report:

Missing indexes - SQL

SELECT index_handle, database_id, object_id, equality_columns, inequality_columns, included_columns, statement

FROM sys.dm_db_missing_index_details

Index usage - SQL

SELECT OBJECT_NAME(I.object_id) AS TableName, I.name, I.index_id, I.type_desc, I.is_unique,


I.fill_factor, I.is_padded, I.is_disabled, I.is_hypothetical,

IUS.index_id , IUS.user_seeks, IUS.user_scans, IUS.user_lookups, IUS.user_updates, IUS.last_user_seek,

IUS.last_user_scan, IUS.last_user_lookup, IUS.last_user_update, IUS.system_seeks, IUS.system_scans, IUS.system_lookups, IUS.system_updates,

IUS.last_system_seek, IUS.last_system_scan, IUS.last_system_lookup, IUS.last_system_update

FROM sys.indexes AS I LEFT OUTER JOIN

sys.dm_db_index_usage_stats AS IUS

ON I.object_id = IUS.object_id AND

I.index_id = IUS.index_id


ORDER BY OBJECT_NAME(I.object_id)

Useful SSRS expression syntax

=IIF(Fields!a.value>100, True, False)

--------------------------------------------------------
=IIF(Fields!a.value >=10, "Green", IIF(Fields!a.value >=1, "Blue", "Red"))

values >=10 are green, between 1 & 9 are blue, less than 1 are red

--------------------------------------------------------

=SWITCH(
    Fields!a.value >=10, "green",
    Fields!a.value >=1, "blue",
    Fields!a.value = 1, "yellow",
    Fields!a.value <=0, "Red")

>=10 are green, between 1 & 9 blue, =1 yellow, <=0 red

--------------------------------------------------------

SWITCH function finds the first expression that's true. It will not catch errors further along.
IIF function evaluates all parts of the expression.

--------------------------------------------------------


=IIF(Rownumber("scope") Mod 2 = 0, "Khaki", "White")

set "scope" to reset row colours for every group

--------------------------------------------------------