NVARCHAR Limit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NVARCHAR Limit

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2008-12-15 : 17:12:46

Hello,

I am creating the following table:

create table aspnet_Articles
(
ArticleId uniqueidentifier not null
constraint PK_Article primary key clustered,
Body nvarchar(max) null,
CreatedAt datetime null,
IsPublished bit null,
Title nvarchar(800) null,
UpdatedAt datetime null
)

Is there any difference, for example in performance, if I use "Title nvarchar(max) null" instead of "Title nvarchar(800) null"?

The title will never be more than 800 characters. But if I set that limit in my SQL table column then I will need to validate it on my C# code ... but if I use max then I will not need to validate it ...

What is the best way to do this and what are the implications of using always "max"?

Thanks,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-15 : 18:29:14

You should use nvarchar(800). Do not use max just to avoid validation in C#.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 03:24:06

You will still need validation in C# if you use NVARCHAR(MAX).
Currently NVARCHAR(MAX) is 1048576 unicode characters.

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-16 : 09:55:34

If you do not need to store Unicode characters then using varchar instead of nvarchar will halve storage space used
So in your case varchar should have better performance over nvarchar.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1266201,00.html
Varchar(800) has advanges over varchar(max) which may or may not apply in this case:
- it enforces good design /business rules, and can be used as a key column in an index.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 10:12:25

VARCHAR(MAX) can be included in an index, even if there are more than 900 bytes allocated for a record.
See

CREATE TABLE	tstPeso
		(
			rowID INT IDENTITY(1, 1),
			i VARCHAR(MAX)
		)
GO

CREATE UNIQUE NONCLUSTERED INDEX ix_peso ON tstPeso (rowid) include(i)
GO

insert tstpeso select replicate('t', 800)
GO

insert tstpeso select replicate('t', 12000)
GO

select rowid, datalength(i), i 
from tstpeso
GO

DROP TABLE tstPeso
GO

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-16 : 11:54:31

Yes it can be included as a non-key column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources