Default value for int columns

• kensai

  Old Hand

  Points: 352

  More actions

  September 12, 2002 at 7:02 am

  #79120

  I have a few columns with datatype int on a table and somehow they get the value '0' whenever I enter a new record with empty values for that columns and I don't have any Default Value or Constraint for these columns. Is this a designed behaviour of SQL Server? Do every column with int datatype get the default value 0 everytime they left empty? How can I prevent this?

• Nicholas Wang

  Default port

  Points: 1463

  More actions

  September 12, 2002 at 12:50 pm

  #436414

  Are those columns NULLABLE? I think if it's NULLABLE then when you insert nothing into it it gets a NULL, otherwise you can always specify a DEFAULT value.

  ALTER TABLE sales

  ALTER COLUMN qty INT NULL

  DEFAULT NULL WITH VALUES

  Will that work?

• bp

  SSCarpal Tunnel

  Points: 4656

  More actions

  September 12, 2002 at 1:54 pm

  #436421

  Did you check for any triggers on that table?

  I don't think what you say is design behavior of SS. See below:

  SET NOCOUNT ON

  GO

  USE TEMPDB

  GO

  PRINT 'Creating a table with no defaults on any columns...'

  CREATE TABLE TABLE_TEST_OUT_INT(A_TEXT_FIELD VARCHAR(5), AN_INT_FIELD INT);

  GO

  PRINT 'Inserting values into the table...'

  INSERT INTO TABLE_TEST_OUT_INT(AN_INT_FIELD) VALUES(1);

  INSERT INTO TABLE_TEST_OUT_INT(A_TEXT_FIELD) VALUES('Hello');

  GO

  PRINT 'Displaying all records in the table...'

  PRINT ''

  SELECT * FROM TABLE_TEST_OUT_INT;

  PRINT '(note: there are no zeroes in the int field)'

  GO

  PRINT 'Dropping the table...'

  DROP TABLE TABLE_TEST_OUT_INT

  GO

  SET NOCOUNT OFF

• kensai

  Old Hand

  Points: 352

  More actions

  September 15, 2002 at 4:09 am

  #436557

  nickel01:

  yes they're nullable

  bp:

  no i don't have any triggers.

  I found out something today. After changing the datatype of the column to varchar I was still getting 0 value inserted. The stored proc I use to enter data to this column had @zipcode int=null parameter for it. After changing it to @zipcode varchar(50)=null it stopped enter 0 for default. I couldn't figured it out what to do other than using varchar datatype, I wonder what I should do for this.