NULL in JOIN

Problem: Join 2 tables, but there are some fit cases with NULL value – and they are not return.

Fast fix: ISNULL in the JOIN.

But, let's take a look to pay attention on this.

create table #aaa (id int, thedate datetime)

create table #bbb (id int, thedate datetime)

insert into #aaa (id, thedate) values (1, '2010-01-01'), (2, NULL), (3, getdate())

insert into #bbb (id, thedate) values (1, '2005-07-01'), (2, NULL), (3, getdate())

-- "equel" nulls will not be return

select *

from #aaa a

join #bbb b   on     a.id = b.id

                     and b.thedate = a.thedate

-- only "equel" nulls will be return

select *

from #aaa a

join #bbb b   on     a.id = b.id

where  b.thedate is null

and           a.thedate is null

-- all joins will be return

select *

from #aaa a

join #bbb b   on     a.id = b.id

                     and isnull(b.thedate, '1900-01-01') = isnull(a.thedate, '1900-01-01')

-- PAY ATTENTION: if the "isnull data" is a possible value in your tables - it can be a problem:

insert into #aaa (id, thedate) values (4, '1900-01-01')

insert into #bbb (id, thedate) values (4, NULL)

select *

from #aaa a

join #bbb b   on     a.id = b.id

                     and isnull(b.thedate, '1900-01-01') = isnull(a.thedate, '1900-01-01')

 

drop table #aaa

drop table #bbb

So:

ISNULL(AAA, GETDATE) can't be a 100% solution to join on datetime,

ISNULL on numeric columns MUST take into consideration which value will be in the ISNULL (common mistake is to put zero – it's a valid numeric value!!!),

ISNULL(AAA, N'') can't be a 100% solution to join on strings (empty string it's a valid numeric value!!!),

And so on...

How long did a job run? Or: cast HHMMSS format to seconds

How long did a job run??

It should be simple:

select run_duration from msdb.dbo.sysjobhistory

BUT - NO!

run_duration is Elapsed time in the execution of the job or step in HHMMSS format (int) (from technet.microsoft).

Pay attention, that it is not always has all the 6 digits: 00:10:46 is 1046.

So, we need to cast it:

Cast to seconds:

146 = one minute and 46 seconds = 106 seconds:

select

(      (run_duration % 100) -- Seconds

       + 60 * ((run_duration /100) % 100) -- + minutes to seconds

       + 3600 * ((run_duration / 10000) % 100) -- + Hours to seconds

) as Duration_SEC

from msdb.dbo.sysjobhistory

Cast to 'HH:MM:SS' format:

146 = 00:01:46

select

(CASE len(run_duration)

       WHEN 1 THEN cast('00:00:0'

       + cast(run_duration as char) as char (8))

       WHEN 2 THEN cast('00:00:'

       + cast(run_duration as char) as char (8))

       WHEN 3 THEN cast('00:0'

       + Left(right(run_duration,3),1)

       +':' + right(run_duration,2) as char (8))

       WHEN 4 THEN cast('00:'

       + Left(right(run_duration,4),2)

       +':' + right(run_duration,2) as char (8))

       WHEN 5 THEN cast('0'

       + Left(right(run_duration,5),1)

       +':' + Left(right(run_duration,4),2)

       +':' + right(run_duration,2) as char (8))

       WHEN 6 THEN cast(Left(right(run_duration,6),2)

       +':' + Left(right(run_duration,4),2)

       +':' + right(run_duration,2) as char (8))

END) as Duration

from msdb.dbo.sysjobhistory

ISNUMERIC issue: Conversion failed when converting the nvarchar value to data type int

SELECT ... ,CONVERT(int, CAST(MyColumn AS float))   [Points] 

FROM ...

WHERE ISNUMERIC(MyColumn) = 1

Error:

cast failed: Conversion failed when converting the nvarchar value to data type int

But..... we check that ISNUMERIC(MyColumn) = 1, so why it is failed?

Cause:

Some charactes that are not numbers are return from ISNUMERIC as numeric, for example:

select

       ISNUMERIC('-'),

       ISNUMERIC('+'),

       ISNUMERIC('$'),

       ISNUMERIC('.'),

       ISNUMERIC(','),

       ISNUMERIC('\')

will return 1 to all!

Solution: TRY_PARSE

in the conditions, check WHERE TRY_PARSE (MyColumn AS float) IS  NOT NULL

SELECT ... ,CONVERT(int, CAST(MyColumn AS float))   [Points] 

FROM ...

WHERE TRY_PARSE (MyColumn AS float) IS  NOT NULL

TRY_PARSE tries to parse:

• if it can parse - it return the pared value,
• if not - it return NULL, and not fail the query!!!

so, if we check thet TRY_PARSE IS  NOT NULL, we can do the casting in the select.

Remote table-valued function calls are not allowed

Error message:

Remote table-valued function calls are not allowed

The reason:

When selects from remote tables (linked servers) - the table must be called with an alias.

--error:

SELECT ... FROM LinkedServer.DBNAME.dbo.Tablename

-- ok:

SELECT ... FROM LinkedServer.DBNAME.dbo.Tablename t

Explanation:

I don't know, and I also don't know if anyone knows.

DATALENGTH is not like LEN !

Two main differences between DATALENGTH and LEN:
DATALENGTH returns the number of bytes used by an expression.

LEN returns the number of characters contained in an expression.

DATALENGTH supports expressions of any type.

LEN supports only string expressions.

declare @TestLength TABLE 
(
  char1 CHAR(10),
  nchar2 NCHAR(10),
  varchar3 VARCHAR(10),
  nvarchar4 NVARCHAR(10)
) 
INSERT INTO @TestLength VALUES('test', 'test', 'test', 'test')
INSERT INTO @TestLength VALUES('test   ', 'test   ', 'test   ', 'test   ')

SELECT
  LEN(char1) AS len1, DATALENGTH(char1) AS datalength1,
  LEN(nchar2) AS len2, DATALENGTH(nchar2) AS datalength2,
  LEN(varchar3) AS len3, DATALENGTH(varchar3) AS datalength3,
  LEN(nvarchar4) AS len4, DATALENGTH(nvarchar4) AS datalength4
FROM @TestLength

Different place of casting --> different results

declare @intA int = 2, @intB int = 5

select @intA / @intB * 100.00 -- = ?

select @intA * 100.00 / @intB -- = ?

Why are the results different?

In the first select - the cast to decimal number is done after the first operation - that it int:

2/5 = 0.4 = 0 (int!) * 100.00 = 0.00 (cast the zero to decimal)

In the first select - the cast to decimal is in the first operation, so the other operations will be decimal too:

2 * 100.00 = 200.00 (decimal!) / 5 = 40.00

Get aggregations of few columns for each record - using 'Values' operator

SELECT ValuesTestID, ValuesTestName, 

IntCol1, IntCol2, IntCol3, IntCol4, IntCol5, IntCol6,

( Select MAX(ValuesTestName)

From    (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6)) 

UniqueColumn(ValuesTestName)

) MaxIntCol,

( Select SUM(ValuesTestName)

From    (Values (IntCol1), (IntCol2), (IntCol3), (IntCol4), (IntCol5), (IntCol6)) 

UniqueColumn(ValuesTestName)

) SumIntCol

FROM ValuesTest

SSRS Format

Date format :

format(Fields!Timestmp.Value,"dd/MM/yyyy HH:mm")

Number format:

FormatNumber(First(Fields!CastingActualAvgSlabWeight.Value),3) 

Check if an expression is numeric

IsNumeric:

select IsNumeric('0.2') -- return 1

select IsNumeric('asasa') -- return 0

http://msdn.microsoft.com/en-us/library/aa933213(v=sql.80).aspx

Note: parameter with value of NULL is not considered numeric, but parameter with value of empty string ('') will be converted to zero:

DECLARE @P int
SET @P = NULL
SELECT IsNumeric(@P) -- return 0
SET @P = ''
SELECT IsNumeric(@P) -- return 1
SELECT IsNumeric(NULL) -- return 0
SELECT IsNumeric('') -- return 0