Does input parameter type matter?

Question

Does input parameter type matter?

Naomi Nosonovsky 8,906

We have a very simple stored procedure that is executed very often as it writes to a simple audit table. One of the columns in that table defined as tinyint. However, parameter to the stored procedure defined as int. I'm wondering if there is any conversion done during the INSERT command and does this even matter that the parameter type doesn't match the column's type on the INSERT command?

Thanks in advance.

0 comments

Answer accepted by question author

David Browne - msft 3,856

On INSERT this won't matter, as the INT will be implicitly converted to TINYINT. On INSERT the parameter type is always converted to the column type, and the implicit conversion itself is very, very inexpensive.

On SELECT when passing the parameter in the WHERE clause, this matters much more as INT has a higher data type precedence than TINYINT, and will cause the column values to be converted to the parameter type, instead of vice-versa, requiring a conversion for each row and preventing index use.

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2020-11-24T22:37:08.273+00:00
That can happen, depending on the exact types involved. The optimizer is able to handle some of these wrong-way conversions without resorting to scans. For instance:

CREATE TABLE bussig (id tinyint NOT NULL PRIMARY KEY NONCLUSTERED, name sysname NOT NULL) INSERT bussig(id, name) SELECT TOP (255) row_number() OVER(ORDER BY object_id), name FROM sys.objects go CREATE PROCEDURE bussig_sp @id int AS SELECT * FROM bussig WHERE id = @id go EXEC bussig_sp 9

I get an Index Seek when running the procedure. There does not seem to be conversion.

That said, using the correct data type is always a good idea.

3 additional answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2020-11-24T22:37:08.273+00:00

That can happen, depending on the exact types involved. The optimizer is able to handle some of these wrong-way conversions without resorting to scans. For instance:

CREATE TABLE bussig (id tinyint NOT NULL PRIMARY KEY NONCLUSTERED, name sysname NOT NULL) INSERT bussig(id, name) SELECT TOP (255) row_number() OVER(ORDER BY object_id), name FROM sys.objects go CREATE PROCEDURE bussig_sp @id int AS SELECT * FROM bussig WHERE id = @id go EXEC bussig_sp 9

I get an Index Seek when running the procedure. There does not seem to be conversion.

That said, using the correct data type is always a good idea.

Answer 1

Guoxiong 8,221

Converting int to tinyint may not affect the performance. But I would suggest to change the data type of the parameter to tinyint. This way would avoid a run time error if that parameter value is greater than 255.

0 comments

Answer 2

Naomi Nosonovsky 8,906

My question is more performance wise oriented. Does the conversion happen behind the scenes and would it make sense to change parameter type (since this procedure is one of the most executed procedures) or it should not matter?

Anonymous

2020-11-24T18:34:09.237+00:00

Technically it could produce an overflow so I'd probably fix it. Performance-wise it shouldn't matter as the parameter should be passed in by value.

--please don't forget to Accept as answer if the reply is helpful--

Answer 3

Anonymous

Shouldn't really be a problem until / unless the input value exceeded the bounds of the tinyint column

--please don't forget to Accept as answer if the reply is helpful--

0 comments

Share via

Does input parameter type matter?

3 additional answers

Your answer