Search

Sqlserver - COALESCE

COALESCE

Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )


Limitations of COALESCE():
In COALESCE() while passing arguments,it does not support arguments with different data types in few cases.

For example: 

SELECT COALESCE('A',GETDATE())
GO


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

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


But ISNULL() can handle this

SELECT ISNULL('A',GETDATE())
GO



----
A
(1 row(s) affected)

Tricky Example:
DECLARE @T VARCHAR(100)
SELECT @T = COALESCE(@T+', ','' )+NAME FROM SYS.TABLES SELECT @T

Identity - Strange Behaviour

 CREATE TABLE test (A INT IDENTITY ,B INT)

 INSERT INTO test (B) VALUES ('sathya') 
 SELECT * FROM test 
 INSERT INTO test (B) VALUES (1) 
 SELECT * FROM test

Identity column is getting incremented,though the first insert doesnt happen.

SSIS - Truncation error while exporting from excel to database

The reason it fails is because it considers the excel sheet to be until 255 characters in length.

The engine that is responsible for importing excel ONLY SCANS THE FIRST 8 ROWS of the sheet .

If your first 8 rows have less than 255 characters and you have other rows more than 255 characters, then you are in trouble.
The solution is actually changing the registry of server that hosts the SQL. 
Change TypeGuessRows value to 0.

This will scan the entire excel sheet to determine the best data size & datatype for your excel sheet.

For 32 bit OS
HKEY_LOCAL_MACHINE\SOFTWARE\Mi­crosoft\Jet\4.0\Engines\Excel\TypeGuessRows

For 64 bit OS
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Mi­crosoft\Jet\4.0\Engines\Excel\TypeGuessRows

See Also: