Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Automatic truncate of varchar string Expand / Collapse
Author
Message
Posted Tuesday, January 31, 2012 11:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 5:20 AM
Points: 11,192, Visits: 11,086
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1244629
Posted Tuesday, January 31, 2012 11:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 10:29 AM
Points: 2,693, Visits: 895
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.
Post #1244632
Posted Tuesday, January 31, 2012 11:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 5:20 AM
Points: 11,192, Visits: 11,086
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1244633
Posted Tuesday, January 31, 2012 11:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 AM
Points: 42,412, Visits: 35,479
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 2008, MVP
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

Post #1244635
Posted Tuesday, January 31, 2012 11:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:28 PM
Points: 2,688, Visits: 3,367
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
Post #1244638
Posted Tuesday, January 31, 2012 11:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:39 AM
Points: 66, Visits: 186
yes...O.K to accept the loss of data if it is greater than 245 chars
Post #1244639
Posted Tuesday, January 31, 2012 11:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:39 AM
Points: 66, Visits: 186
Thanks everyone for your response..I told the team it is not possible to do it from the DB end.
Post #1244640
Posted Tuesday, January 31, 2012 11:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 5:20 AM
Points: 11,192, Visits: 11,086
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1244641
Posted Tuesday, January 31, 2012 11:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 5:20 AM
Points: 11,192, Visits: 11,086
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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1244644
Posted Tuesday, January 31, 2012 11:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:28 PM
Points: 2,688, Visits: 3,367
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


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
Post #1244653
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse