Core data type question (bigint)

We are trying to work with our developers to upgrade to SQL 2000 from SQL 7 for a critical applicaion and all looks good in testing for the most part. The concern that our developers have is that in order for the application to work on the test SQL 2000 server they had to delete a core data type (bigint) for the application to work. It doesn't appear to have any negative affects and we know for sure that the application database does not need that data type at all.

Can someone verify that there are no requirements for SQL 2000 needing to have this data type? They are worried that something within SQL may rely on it and we would find out the hard way in production possibly.

Edit

Bigint is used when the value you are storing is more than normal int value and it will not create problem. I would create the new database with SQL7.0 compatibilty on the code page for the unknown issues. The following is from the BOL(books online)

"Unless explicitly stated, functions, statements, and system stored procedures that accept int expressions for their parameters have not been changed to support implicit conversion of bigint expressions to those parameters. Thus, SQL Server only implicitly converts bigint to int when the bigint value is within the range supported by the int data type. A conversion error occurs at run time if the bigint expression contains a value outside the range supported by the int data type."

Hope this helps.

|||

Thanks for the quick reply!

Just so I'm sure... We can safely remove the BigInt data type without causing any reprecusions to SQL 2000? Our user databases don't utilize BigInt at all but my concern is in how this may effect the system databases.

We've run this in our test environment for a few weeks but not under heavy load and haven't seen any negative effects but I don't know about long term effect on the core functionality of SQL 2000.

|||

gbollman wrote:

Our user databases don't utilize BigInt at all but my concern is in how this may effect the system databases.

Good to be concerned if your developers used SQL DMO(SQL Server Data Management Object) for your tables. That is the reason I said choose SQL7.0 compatibility in the code page selection in my original post. BTW service pack 4 was released last Friday. In SQL 7.0 upgrade I move the database three or four times in SQL Server 2000 through backup and restore use the restore from device option in the backup wizard. Then do stress testing with your employees. Hope this helps.