Strange Behavior In Insert

Question

Strange Behavior In Insert

chandan_jha18

SSChampion

Points: 11330
More actions
January 29, 2013 at 4:44 am

#273523

Friends,
One of my colleagues sent me the following code:
DECLARE @Tmp TABLE (RecID INT, Number INT)
INSERT INTO @Tmp (RecID, Number) VALUES (1, 1)
INSERT INTO @Tmp (RecID, Number) VALUES (2, 188)
INSERT INTO @Tmp (RecID, Number) VALUES (3, 165)
INSERT INTO @Tmp (RecID, Number) VALUES (4, 121)
INSERT INTO @Tmp (RecID, Number) VALUES (5, 1655)
DECLARE @TMP2 TABLE (Data VARCHAR(3))
INSERT INTO @TMP2 (Data)
SELECT MAX(Number)
FROM @Tmp
SELECT * FROM @TMP2
The output is '*'.
Though it may sound simple, I need to find out why. Please come up with your inputs.
Regards
Chandan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Expected behaviour of SQL

Read the following article http://msdn.microsoft.com/en-us/library/ms187928.aspx, especially the Truncating and Rounding Results section

Inserting a 4 digit INT into a 3 character length string, returns * detailing result length too short to display

chandan_jha18 SSChampion Points: 11330 More actions · Answer 2

chandan_jha18

SSChampion

Points: 11330

January 29, 2013 at 5:59 am

#1582068

You are the man! Thanks Anthony:-)

Chandan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

chandan_jha18 (1/29/2013)
...
Though it may sound simple, I need to find out why. Please come up with your inputs.

Take away the unnecessary tables and use variables in the exact same way:

DECLARE @data VARCHAR(3)

SET @data = 1655

SELECT @data

It should be clear now.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chandan_jha18 SSChampion Points: 11330 More actions · Answer 4

ChrisM@Work (1/29/2013)
chandan_jha18 (1/29/2013)
...
Though it may sound simple, I need to find out why. Please come up with your inputs.
Take away the unnecessary tables and use variables in the exact same way:
DECLARE @data VARCHAR(3)
SET @data = 1655
SELECT @data
It should be clear now.

Correct. It produced the same. so after going through the document, * means that size is too small to display the result when doing a conversion fron integer to char.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

chandan_jha18 (1/29/2013)
ChrisM@Work (1/29/2013)
chandan_jha18 (1/29/2013)
...
Though it may sound simple, I need to find out why. Please come up with your inputs.
Take away the unnecessary tables and use variables in the exact same way:
DECLARE @data VARCHAR(3)
SET @data = 1655
SELECT @data
It should be clear now.
Correct. It produced the same. so after going through the document, * means that size is too small to ~~display~~ store the result when doing a conversion fron integer to char.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden