Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automatic truncate of varchar string


Automatic truncate of varchar string

Author
Message
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
GilaMonster (1/31/2012)
jcb (1/31/2012)
Can You rely on a trigger to safe truncate the data to the column?

No, because types are checked before triggers fire.

Yes, if you use a view ;-) ...


CREATE TABLE dbo.Test
(
String varchar(245) NOT NULL
);
GO
CREATE VIEW dbo.TestView
WITH SCHEMABINDING AS
SELECT
String = CONVERT(varchar(8000), String)
FROM dbo.Test AS t;
GO
CREATE TRIGGER [trg TestView IOI]
ON dbo.TestView
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.Test (String)
SELECT
LEFT(String, 245)
FROM INSERTED;
END


-- Success
INSERT dbo.TestView (String) VALUES(REPLICATE('X', 250));
GO
SELECT
t.String,
DataSize = DATALENGTH(t.String)
FROM dbo.Test AS t
GO
DROP VIEW dbo.TestView;
DROP TABLE dbo.Test;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
jcb
jcb
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2742 Visits: 969
I understand the team worry about extending the size of the column.
It can broke some procedure expecting a 245 size string.

I dont know you DB or app, but I know changing from sysbase can be a pain.
and I guessing the worst: the app is old, no layers and no fancy SPs (or you can easily change only a single line in the app/SP to truncate it), maybe even no OO.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
jcb (1/31/2012)
I need to ask...
Changing the column from varchar(245) to varchar(max) is an option?

VARCHAR(something <= 8000) would be better. MAX types prevent online index builds (before SQL Server 2012 is released anyway) and typing the column as a potential LOB has all sorts of other query plan side-effects.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47137 Visits: 44346
SQL Kiwi (1/31/2012)
GilaMonster (1/31/2012)
jcb (1/31/2012)
Can You rely on a trigger to safe truncate the data to the column?

No, because types are checked before triggers fire.

Yes, if you use a view ;-) ...


True, but probably not an option in this case seeing as they're looking for a quick fix and locating all the places that it can happen for a fix isn't apparently allowed.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2796 Visits: 3681
Personally... I would tell them no. We don't truncate data at the database level with SQL Server. Tell them it is not possible and that they simply have to truncate it at the application layer. Tell them SQL Server has these safeguards in place to ensure data integrity.

Somehow you have to force them to do things the right way. That's my 2 cents anyway...

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Siva Ramasamy
Siva Ramasamy
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 326
yes...O.K to accept the loss of data if it is greater than 245 chars
Siva Ramasamy
Siva Ramasamy
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 326
Thanks everyone for your response..I told the team it is not possible to do it from the DB end.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
GilaMonster (1/31/2012)
SQL Kiwi (1/31/2012)
GilaMonster (1/31/2012)
jcb (1/31/2012)
Can You rely on a trigger to safe truncate the data to the column?

No, because types are checked before triggers fire.

Yes, if you use a view ;-) ...

True, but probably not an option in this case seeing as they're looking for a quick fix and locating all the places that it can happen for a fix isn't apparently allowed.

I have seen cases where the base table is renamed, as the view named as the base table...well you know the pattern. That said, my reply was intended for interest and completeness, rather than as a practical suggestion.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
SQLKnowItAll (1/31/2012)
Personally... I would tell them no. We don't truncate data at the database level with SQL Server. Tell them it is not possible and that they simply have to truncate it at the application layer.

Ah! The famous DBA "can't do" attitude Laugh



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2796 Visits: 3681
SQL Kiwi (1/31/2012)
SQLKnowItAll (1/31/2012)
Personally... I would tell them no. We don't truncate data at the database level with SQL Server. Tell them it is not possible and that they simply have to truncate it at the application layer.

Ah! The famous DBA "can't do" attitude Laugh


Cool As long as you say it with a positive attitude and maybe a little "I really wish I could, but I just can't figure out how to allow bad data into the database." That will usually get the stakeholders to rethink the request.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search