Equals in sql (null vs empty string(''))

declare @test varchar(12)
set @test = null --CHAR(13)+CHAR(10)
print len(@test)
if @test > '' 
print 'yes'
else 
print 'no'

set @test = null
if @test = null  --is null returns the correct result
print 'yes, it is null'
else 
print 'not null'

SQL Server: Null VS Empty String

Be careful with nulls and checking for inequality in sql server.

For example

select * from foo where bla <> 'something' 

will NOT return records where bla is null. Even though logically it should.

So the right way to check would be

select * from foo where isnull(bla,'') <> 'something' 

Which of course people often forget and then get weird bugs.

Parsing datetime as special string format

At my current work, my team has a very knowledgable dicussion about parsing datetime as a special string. 

declare @tens char(31) = '0000000001111111111222222222233';
declare @ones char(31) = '1234567890123456789012345678901';
declare @days char(62) = '01020304050607080910111213141516171819202122232425262728293031';

select top 100 m.dob, right(convert(char(8), m.dob, 112), 4) + left(convert(char(8), m.dob, 112), 4) as right_left_4,
              substring(@tens, month(m.dob), 1) + substring(@ones, month(m.dob), 1) + substring(@tens, day(m.dob), 1) + substring(@ones, day(m.dob), 1) + cast(year(m.dob) as char(4)) as tens_and_ones,
              substring(@days, 2*month(m.dob) - 1, 2) + substring(@days, 2*day(m.dob) -1,2) + cast(year(m.dob) as char(4)) as days
from member m
right(convert(char(9), 100000000 + month(m.dob) * 1000000 + day(m.dob) * 10000 + year(m.dob)), 8)

//fast -> slow 2, 3 ,1
1.	format(m.dob,'MMddyyyy') as [Birth Date],
2.	RIGHT('0'+CAST(MONTH(m.dob) AS VARCHAR(2)),2) + RIGHT('0'+CAST(DAY(m.dob) AS VARCHAR(2)),2) + CAST(YEAR(m.dob) AS VARCHAR(4)) as [Birth Date],
3.	replace(convert(varchar(10),m.dob,101),'/','') as [Birth Date],

Results for various proposed alternative methods.  The baseline is 1194 seconds.  The first two are close enough to the baseline that they are essentially equivalent to it.

right(convert(char(8),m.dob,112),4)+convert(char(4),m.dob,112)

(1204 seconds)

right(convert(char(9), 100000000 + month(m.dob) * 1000000 + day(m.dob) * 10000 + year(m.dob)), 8)

(1213 seconds; using cast instead of convert yielded 1221 seconds)

substring(@days, 2*month(m.dob) - 1, 2) + substring(@days, 2*day(m.dob) -1,2) + cast(year(m.dob) as char(4))

(1317 seconds)

substring(@tens, month(m.dob), 1) + substring(@ones, month(m.dob), 1) + substring(@tens, day(m.dob), 1) + substring(@ones, day(m.dob), 1) + cast(year(m.dob) as char(4))

(1402 seconds)

Wrapping the baseline version in a scalar function

(2563 seconds)

For my select of about 22.5 million records with 20 columns, Method #2 is fastest (1194 seconds), followed by #3 (1448 seconds, 21% slower), followed by #1 (1645 seconds, 38% slower).

Style 112 returns a date in yyyymmdd format, not the required mmddyyyy; T-SQL doesn’t supply a style that will do so directly, which is why I tried #3.

Using CHAR does make a difference: we don’t get the correct results.  CHAR always contains the specified number of characters and the CAST omits the leading zero.  So any single digit value is the digit followed by a blank and prepending the zero gives us a 3 character string with a blank at the end and we extract the two rightmost characters; using VARCHAR, the CAST gives us just the digit.  Run the following to see what I mean (example will work until October 1):

select '['+RIGHT('0'+CAST(MONTH(getdate()) AS CHAR(2)),2)+']','['+RIGHT('0'+CAST(MONTH(getdate()) AS VARCHAR(2)),2)+']'

A useful stored procedure template + data clean up using dynamic sql

This is very useful stored procedure template and an example with cleaning up data with muptiple relational tables using dynamic sql.

if object_id(N'dbo.sp') is null 
begin
	-- create stub
	exec( 'create procedure dbo.sp as begin raiserror(''NOT IMPLEMENTED. STUB ONLY.'', 16, 1) end' )
end
go


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[sp] (
                 @id int
				 , @begin_proc_id int --smaller integer
				 , @end_proc_id int --bigger interger
				 , @audit_id varchar(20) 
)
AS


declare @proc_id varchar(50)
		, @table_name varchar(128)
		, @column_name varchar(128)
		, @proc_id_column_name varchar(128) 
		, @backup_table_name varchar(128)
		, @sql nvarchar(max) 
		, @servername VARCHAR(225) = @@SERVERNAME
		, @trancode VARCHAR(1000) = ''


--get all proc_id for the table
if object_id('tempdb..#proc_ids') is not null drop table #proc_ids
create table #proc_ids
(
	proc_id int
)
insert into #proc_ids
select proc_id 
from proc_table
where id = @id and proc_id between @begin_proc_id and @end_proc_id


--perform cleanup if the table is not empty
if exists(select 1 from #proc_ids)
begin


	--the process can be possibly gotten blocked. Off-work time is recommended. 
	BEGIN TRAN T1
	BEGIN TRY		
		--list all possible tables for clean-up
		declare @all_tables table
			(
				table_name varchar(128)
				, proc_id_name varchar(128)
				, is_backup_needed char(1)
				, backup_table_name varchar(128)
				, sql_statement nvarchar(max)
			)
		insert into @all_tables 
		values('table1', 'proc_id', 
		iif(exists(select 1 from table1 tn join #proc_ids cpi on cpi.proc_id = tn.proc_id), '1', '0'), 
		'table1_audit_' + @audit_id, null)
			, ('table2', 'proc_id', 
		iif(exists(select 1 from table2 tn join #proc_ids cpi on cpi.proc_id = tn.proc_id), '1', '0'), 
		'table2_audit_' + @audit_id, null)
			, ('table3', 'proc_id', 
		iif(exists(select 1 from table3 tn join #proc_ids cpi on cpi.proc_id = tn.proc_id), '1', '0'), 
		'table3_audit_' + @audit_id, null)
			, ('table4', 'proc_id', 
		iif(exists(select 1 from table4 tn join #proc_ids cpi on cpi.proc_id = tn.proc_id), '1', '0'), 
		'table4_audit_' + @audit_id, null)
			, ('table5', 'proc_id', 
		iif(exists(select 1 from table5 tn join #proc_ids cpi on cpi.proc_id = tn.proc_id), '1', '0'), 
		'table5_audit_' + @audit_id, null)
			, ('table6', 'proc_id', 
		iif(exists(select 1 from table6 tn join #proc_ids cpi on cpi.proc_id = tn.proc_id), '1', '0'), 
		'table6_audit_' + @audit_id, null)
			, ('table7', 'proc_id', 
		iif(exists(select 1 from table7 tn join #proc_ids cpi on cpi.proc_id = tn.proc_id), '1', '0'), 
		'table7_audit_' + @audit_id, null)
			, ('table8', 'tableProcedureID', 
		iif(exists(select 1 from table8 tn join #proc_ids cpi on cpi.proc_id = tn.tableID), '1', '0'), 
		'table8_audit_' + @audit_id, null)
			, ('proc_table', 'proc_id', 
		iif(exists(select 1 from proc_table tn join #proc_ids cpi on cpi.proc_id = tn.proc_id), '1', '0'), 
		'proc_table_audit_' + @audit_id, null)

		--populate the sql_statement field for @all_tables
		update t
		--if starts
		set t.sql_statement ='if ' + t.is_backup_needed  + ' = 1 begin ' + 'if object_id(''' + t.backup_table_name +''') is null CREATE TABLE ' + t.backup_table_name + ' (' + 
								stuff((select c.column_name + ' ' + 
								CASE WHEN c.data_type = 'timestamp' THEN 'varbinary(8)' 
									when c.data_type in ('char','nchar','binary','varchar', 'nvarchar','varbinary') then c.data_type + '(' + iif(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(20))) + ')'
									WHEN c.data_type in ('numeric', 'decimal') THEN c.data_type + '(' + cast(c.NUMERIC_PRECISION as varchar(10)) + ',' + cast(c.NUMERIC_SCALE as varchar(10)) + ')'
									ELSE c.data_type 
								END + ', '  -- we don't need (or really want) any other column attributes like NOT NULL...
								FROM INFORMATION_SCHEMA.COLUMNS c where  t.table_name = c.TABLE_NAME ORDER BY c.ORDINAL_POSITION for xml path('')), 1, 0, '') 
		from @all_tables t 
	
		--the second update is for using len()
		update  t
		set t.sql_statement = left(t.sql_statement, len(t.sql_statement) - 1) + ', run_date datetime) ' +
		--delete data from the source table and insert the deleted data into the backup table
		'delete from ' + t.table_name + ' output deleted.*, getdate() into ' + t.backup_table_name + 
		' where ' + t.proc_id_name + ' in (select proc_id from #proc_ids) ' +
		--if ends
		'end'							
		from @all_tables t

		
		--get dynamic sql ready
		set @sql = stuff((select sql_statement + '; ' from @all_tables for xml path('')), 1, 0, '')

		exec(@sql)			

		COMMIT TRAN T1
		END TRY

		BEGIN CATCH 
			ROLLBACK TRAN T1
			SET @trancode = 'T1 Broken, cannot backup or delete records using sp on '+ @servername
			SELECT @trancode as TranCode,  ERROR_NUMBER() AS ErrorNumber  
			,ERROR_MESSAGE() AS ErrorMessage			
		END CATCH 

		if @trancode = ''
			print 'The script runs successfully.'		
		else 
			print 'Please check ErrorMessage. Most of cases, we just need to run the script at another off-work time.'
end
else 
begin
	print 'There is no proc_id retured. Please check the input paramters like @begin_proc_id, @end_proc_id etc.'
end

GO


-- Install or update extended properties for this SP.
declare @install_ts nvarchar(23);
set @install_ts = convert(nvarchar(23), getdate(), 121);

IF EXISTS (
     SELECT 1 FROM sys.extended_properties
     WHERE [major_id] = OBJECT_ID(N'dbo.sp')
     AND [name] = N'Installed'
)
BEGIN
     EXEC sys.sp_updateextendedproperty 
     @name = N'Installed', 
     @value = @install_ts, 
     @level0type = N'SCHEMA', @level0name = 'dbo',
     @level1type = N'PROCEDURE',  @level1name = 'sp';
END
ELSE
BEGIN
     EXEC sys.sp_addextendedproperty 
     @name = N'Installed', 
     @value = @install_ts, 
     @level0type = N'SCHEMA', @level0name = 'dbo',
     @level1type = N'PROCEDURE',  @level1name = 'sp';
END