CODEWITHSUNDEEP
sql-serverfunctiontypes
SHR
SHR

Reputation: 8313

SQL Server: store INT in BIGINT column can be a problem?

I have mapping table in the following structure:

CREATE TABLE MappingTable 
(
    TableName SYSNAME,
    SrcId BIGINT,
    DstId BIGINT,
    PRIMARY KEY (Name, SrcId),
    UNIQUE (Name, DstId)
)

SrcId and DstId are referring to Identity columns in 2 identical databases, most of the columns type is INT but some are BIGINT, therefore I am storing all as BIGINT.

Now I want to write function to get DstId for TableName and SrcId.

So my question is simple: do I have any reason to implement 2 functions, one for INT and the 2nd for BIGINT?

To clarify: I read what I'd wrote: when an INT was written it reads as INT, and the same is for BIGINT.

Upvotes: 1

Views: 2289

Answers (1)

Ilyes
Ilyes

Reputation: 14928

Do I have any reason to implement 2 Functions, one for INT and the 2nd for BIGINT?

The simple answer to this No, you need just one function for BIGINT, eg:

DECLARE @BigInt BIGINT = 922337203685477580;
DECLARE @Int INT = 2147483647;

--SET @Int = @BigInt; this wont work, so your choice is BIGINT
SET @BigInt = @Int;

and here is a test with functions to make it very clear.

+-----------+--------------------------------------------------------------------------+---------+
| Data type |                                  Range                                   | Storage |
+-----------+--------------------------------------------------------------------------+---------+
| bigint    | -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) | 8 Bytes |
| int       | -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)                         | 4 Bytes |
+-----------+--------------------------------------------------------------------------+---------+

See int, bigint, smallint, and tinyint

Upvotes: 5

Related Questions