TRY_PARSE to check valid date

DECLARE @s VARCHAR(6) = '022212'

SELECT IIF(TRY_PARSE((Left(@s,2)+'-'+ substring(@s,3,2)+'-20'+right(@s,2)) AS date) IS not NULL, 'True', 'False') AS 'Validdate';

SQL Server 2012 or 2014
TRY_PARSE ( string_value AS data_type [ USING culture ] )


Date Range (Gap and Island) T-SQL Question

CREATE TABLE #temp(key_column VARCHAR(30),start_dt DATETIME,end_dt DATETIME)

('A', '2014-05-01 00:00:00.000','2014-05-31 00:00:00.000'),
('A', '2014-06-01 00:00:00.000','2014-06-20 00:00:00.000'),
('A', '2014-06-20 00:00:00.000','2014-07-10 00:00:00.000'),
('B', '2014-05-01 00:00:00.000','2014-05-31 00:00:00.000'),
('B', '2014-06-10 00:00:00.000','2014-06-20 00:00:00.000')

DECLARE @minstart_dt DATETIME, @maxend_dt DATETIME;
SELECT @minstart_dt = MIN(start_dt),  @maxend_dt = MAX(end_dt)  FROM #temp;

--**** you can create a calendar table
;WITH Num1 (n) AS (
SELECT 1 as n
FROM Num1 Where n<101),

,myDateCTE as
select dateadd(day,n-1,(select min(start_dt) from #temp)) dt 
from Nums


, mycte as (
select dt, key_column, row_number() Over(Partition by key_column order by dt) rn1 from 
(select distinct key_column from #temp) t1, myDateCTE  

,mycte1 as (
SELECT key_column, dt, rn1, row_number() Over(Partition by key_column order by dt) rn2
   FROM mycte t1
     (SELECT * FROM #temp t2 
       WHERE t1.dt BETWEEN t2.start_dt AND t2.end_dt and t1.key_column=t2.key_column)


Select key_column, Min(m.dt) As start_dt, Max(m.dt) As end_dt
From mycte1 m
Group By key_column, m.rn1 - m.rn2


drop table #temp

Modify Computed Column Data Type Using T-SQL

You need to drop the column first and add the column back with explicit data type CAST/CONVERT.
An example:

ALTER TABLE [dbo].[myTable] 
   DROP COLUMN [fullname];

ALTER TABLE [dbo].[myTable] 
ADD [fullname]  AS (CONVERT(varchar(250)
,case when gid=1 
then isnull([fname],'')+' '+isnull([lname],'')
else [busname] end));



If your computed column has mixed column data types in its formula, you should align (fix) these columns to appropriate data type. The last option is to use a cast/convert to control the final data type for the computed column.

If you have the computed column in the table already when you want to modify one of the column data type used in the formula, you need to drop the computed column first and recreate it when you finish the data type change for the column involved.
An example:

ALTER TABLE [dbo].[myTable] 
   DROP COLUMN [fullname];

ALTER TABLE [dbo].[myTable] 
  ALTER COLUMN [theOtheColumn] varchar(55) null

ALTER TABLE [dbo].[myTable] 
ADD [fullname]  AS (CONVERT(varchar(250)
,case when gid=1 
then isnull([fname],'')+' '+isnull([lname],'')
else [busname] end));



Count Distinct –Alternate Solution in T-SQL

  mukey nvarchar (30) NULL,
  cokey nvarchar (30) NULL,
  ncomp int NULL);
INSERT INTO mytable(mukey, cokey, ncomp)
  ('1017271', '9302647', NULL),
  ('1017271', '9302647', NULL),
  ('1017271', '9302647', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL)

;with PartialSums as (
SELECT mukey, cokey,COUNT(*) AS countstarpartialcount FROM mytable  
GROUP BY mukey, cokey
,mycte as (SELECT mukey,
SUM(countstarpartialcount) AS countstar,
SUM(1) AS countproductkeys FROM partialsums 
Group by mukey)

Merge mytable tgt 
using mycte  as src on tgt.mukey=src.mukey
When matched Then 
set ncomp=src.countproductkeys;

select * from mytable

drop table mytable

Register -t3226 to SQL Server Startup Parameter to suppress backup operation success messages in logs

SQLServer configuration manager >>SQL Server Services>>Right click on the service to get the Properties menu.
>>tabbed dialog window >>start up parameters tab
>>Type -t3226 in Specify a startup parameter
>>Click Add

–will take effect after restart service

–Check status

Get data from a date range: Construct the start and end date – T-SQL

declare @StartMonth NVARCHAR(10),
@StartYear NVARCHAR(4),
@EndMonth NVARCHAR(10),
@EndYear NVARCHAR(4)

Select @StartMonth = N'January',
		@StartYear = N'2014',
		@EndMonth = N'February',
		--@EndMonth = N'December',
		@EndYear = N'2014'

Select Cast(@StartMonth + '1 ' + @StartYear as date) as beginningofstartdt
,Cast(@EndMonth + '1 ' + @EndYear as date) as beginningofenddt
,Dateadd(month,1,Cast(@EndMonth + '1 ' + @EndYear as date)) as beginningofNextMonth,
DateAdd(ms, -3, DateAdd(month, DateDiff(month, 0, Cast(@EndMonth + '1 ' + @EndYear as date))+1, 0) ) endofenddt

--Solutions for your question

WHERE ta.TimeByDay >= Cast(@StartMonth + '1 ' + @StartYear as date)
AND ta.TimeByDay <Dateadd(month,1,Cast(@EndMonth + '1 ' + @EndYear as date))


WHERE ta.TimeByDay >= Cast(@StartMonth + '1 ' + @StartYear as date)
AND ta.TimeByDay <=DateAdd(ms, -3, DateAdd(month, DateDiff(month, 0, Cast(@EndMonth + '1 ' + @EndYear as date))+1, 0) )

--new functions since SQL Server 2012 Datefromparts, Eomonth ...

Split Time Period into Equal Time Slots

declare @date datetime = '2014-07-10 00:00:00.000'
      , @start int = 820
	  , @end int = 1000
	  -- , @start varchar(5) = '8:20'
	  --, @end  varchar(5) = '10:00'
	  , @duration  int = 20 ; --minute

	  declare @start2 varchar(5)=Left(Right('00'+Cast(@start as varchar(5)),4),2)+':'+Right(Cast(@start as varchar(5)),2)
	  declare @end2 varchar(5)=Left(Right('00'+Cast(@end as varchar(5)),4),2)+':'+Right(Cast(@end as varchar(5)),2)

	 ;with mycte as ( 
	 select *, dateadd(minute, timeslot*n,cast(convert(varchar(10),dt,101)+' '+ stime as datetime)) dt2
	  ,cast(convert(varchar(10),dt,101)+' '+ etime as datetime) dt_end
	   from (Select @date as dt, @start2 stime, @end2 etime, @duration timeslot) t
	  cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n)


	  select dt2 from mycte where dt2<dt_end
2014-07-10 08:20:00.000
2014-07-10 08:40:00.000
2014-07-10 09:00:00.000
2014-07-10 09:20:00.000
2014-07-10 09:40:00.000


Remove Extra Comma in String Concatenation

declare @one char(1)='a'
declare @two char(1)=''
declare @three char(1)='c'
declare @four char(1)='d'

declare @s varchar(50)
select @s=ISNULL(nullif(@one,'')+',','')+ISNULL(nullif(@two,'')+',','')+ISNULL(nullif(@three,'')+',','')+ISNULL(nullif(@four,''),'')           

         WHEN Patindex('%,', @s) > 0 THEN Substring(@s, 1, Len(@s) - 1)
         ELSE @s


Dynamic Pivot with Column in a Sorted Order

--create table [dbo].[Report] (id int identity(1,1), [Type] char(1), ABC int, T_Date date,[Sum] int)
--Insert into [dbo].[Report] values
--('A',1,dateadd(week,19,'1/1/2014'), 10),
--('A',2,dateadd(week,18,'1/1/2014'), 10),
--('A',2,dateadd(week,21,'1/1/2014'), 10),
--('A',1,dateadd(week,20,'1/1/2014'), 10),
--('A',3,dateadd(week,22,'1/1/2014'), 10)

----drop table  [dbo].[Report]

DECLARE @columns NVARCHAR(MAX),@columns_pivot NVARCHAR(MAX), @sql NVARCHAR(MAX);

    SELECT @columns_pivot = COALESCE(@columns_pivot + ', ', '') + QUOTENAME(Week_No)
      ,@columns = COALESCE(@columns + ', ', '') + 'ISNULL(' + QUOTENAME(Week_No) + ',0) AS ' + QUOTENAME(Week_No) + ''
    FROM (SELECT DISTINCT DATEPART(wk,T_Date) As Week_No FROM [dbo].[Report]
	   WHERE DATEPART(m,T_Date) = 5) x
	   ORDER BY Week_No DESC; 

--print @columns_pivot
--print @columns
    SET @sql = '
    SELECT ABC, ' + @columns + '
    FROM   (Select  ABC ,SUM(CASE WHEN Type = ''A'' THEN Sum ELSE 0 END) AS Revenue
    ,DATEPART(wk,T_Date) As Week_No
     FROM [dbo].[Report]
     ) As j 
     PIVOT(   max(Revenue)    FOR Week_No in (' + @columns_pivot + ')) As p '

	 print @sql
	-- exec(@sql)

ASPState Database Related

I did a quick read about ASPState for developer implementation. I found a blog from Darren Kopp to share his changes to improve performance for this database.
I read through the blog and here is the link for future reference.