SQL Data type, Varchar without N

When we declare variable as varchar, we will :
Declare part as varhcar(20)

What if we declare varchar without indicate the length?
Declare part as varchar


See the below example :

DECLARE @myVariable AS varchar
DECLARE @myNextVariable AS varchar(10)
SET @myVariable = 'abc'
SET @myNextVariable = 'abc'
SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);
--result    1           3
SELECT @myVariable, @myNextVariable;
--result a    abc
GO


Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1.
When n is not specified when using the CAST and CONVERT functions, the default length is 30.


Examples:
SPROC_TEST 'FINAL'

Create  PROCEDURE [dbo].[SPROC_TEST]
@submission_type VARCHAR -- START, FINAL
AS
print @submission_type
BEGIN
     IF  @submission_type='FINAL'
        PRINT 'A'
      ELSE
        PRINT 'B'
END


We pass parameter as ‘FINAL’ and expected “A’ will be printed,
We will get result result ‘B’ (not as expected), because value in @submission_type been truncate to “F”

Reference
http://msdn.microsoft.com/en-us/library/ms176089.aspx

SQL Data Type - Bit

We have SQL 2000 and SQL 2005 server
we run this in SQL 2000 server and SQL 2005 Server

DECLARE @bit bit
SET @bit = 'TRUE'
IF @bit = 1
PRINT 'Yes, this is true!'
ELSE
PRINT 'No, this is FALSE!'


SQL 2000 result :
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'TRUE' to a column of data type bit.

SQL 2005 result :
Yes, this is true!


SQL 2000 bit data type won't accept 'True'!

thanks for my colleague for this finding.


SQL 2000 - Bit
Integer data type 1, 0, or NULL.

SQL 2005 - Bit
An integer data type that can take a value of 1, 0, or NULL.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.