January 7, 2016 at 10:37 pm
Comments posted to this topic are about the item Shortening a column
January 7, 2016 at 10:38 pm
Nice, easy one to end the week on, thanks, Steve
January 7, 2016 at 10:39 pm
This was removed by the editor as SPAM
January 7, 2016 at 11:26 pm
Nice question Steve. Thanks for sharing
January 7, 2016 at 11:57 pm
Thank you for a nice easy Friday question.
...
January 8, 2016 at 2:55 am
Easy - tell the 44% who have got it wrong so far.
I take it as a well earned point!
January 8, 2016 at 2:58 am
There are circumstances in which option 4 is the correct answer. The question does not state that the table is empty, nor does it say anything about the data in the column to be altered. For example, if you execute this:
INSERT INTO MySales
VALUES (
1
,1
,CURRENT_TIMESTAMP
,CURRENT_TIMESTAMP
,1
,10
,'0123456789'
,1
,'Test'
)
GO
DROP INDEX IX_MySales_CustomerReport ON MySales
ALTER TABLE MySales ALTER COLUMN OrderStatus varchar(5)
CREATE INDEX IX_MySales_CustomerReport ON MySales (CustomerID, SaleDate) INCLUDE (ShipDate, SalesRepID, orderstatus)
you get this error:
Msg 8152, Level 16, State 14, Line 15
String or binary data would be truncated.
John
January 8, 2016 at 5:17 am
A nice and simple question to end the week. Thanks and have a great weekend.
January 8, 2016 at 9:31 am
John Mitchell-245523 (1/8/2016)
There are circumstances in which option 4 is the correct answer. The question does not state that the table is empty, nor does it say anything about the data in the column to be altered. For example, if you execute this:
INSERT INTO MySalesVALUES (
1
,1
,CURRENT_TIMESTAMP
,CURRENT_TIMESTAMP
,1
,10
,'0123456789'
,1
,'Test'
)
GO
DROP INDEX IX_MySales_CustomerReport ON MySales
ALTER TABLE MySales ALTER COLUMN OrderStatus varchar(5)
CREATE INDEX IX_MySales_CustomerReport ON MySales (CustomerID, SaleDate) INCLUDE (ShipDate, SalesRepID, orderstatus)
you get this error:
Msg 8152, Level 16, State 14, Line 15String or binary data would be truncated.
John
In that case, answer four is still not correct. The correct answer would be
Drop the index
UPDATE MySales SET OrderStatus = LEFT(OrderStatus,5) -- or whatever truncation function you wish to use -- note: do the update after dropping the index so SQL Server doesn't have to update the index as well as the table.
Alter the column
Recreate the index
which is not listed. You can change the maximum length of a varchar column without dropping and recreating the table. You will have to drop and re-create any indexes, and you will have to update any data which won't fit into the smaller size, but it can be done.
January 8, 2016 at 10:35 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 8, 2016 at 10:46 am
paul s-306273 (1/8/2016)
Easy - tell the 44% who have got it wrong so far.I take it as a well earned point!
I consistently get answers wrong, like this one, I figured shortening the column would truncate data so I selected "can't do it", but I'm not going to be that bothered by being wrong, just as long as I'm not wrong in production 😀
January 11, 2016 at 2:13 am
sknox (1/8/2016)
In that case, answer four is still not correct.
It is if you don't want to lose any data. Yes, if you were doing this at all then perhaps you wouldn't mind losing the data. But where the information in a question isn't complete, you have to make assumptions. I made the wrong ones. I'm not beating myself up about it, though - it'll certainly help me remember this thing about indexes!
John
January 12, 2016 at 12:53 am
Good question. And if you want to lengthen the column you can do it directly without dropping the index.
Igor Micev,My blog: www.igormicev.com
February 17, 2016 at 4:34 pm
Thanks for the question.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply