CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
alejandro carnero
alejandro carnero

Reputation: 1784

Best SQL type for 2000 characters

I have a table with a column named X509 , i have used this function for know how many characters have the stored values :

select distinct  LEN (x509certificate)   from ctrc

this statment have return me these values :

  2112
  2732
  2748
  2800

I need to save in this column values from 2112(min) to 2800(max) characters , the table uses NVARCHAR(max) or nvarchar lenght -1. Whats the best sql type for this kind of data???

Thanks Alejandro

Upvotes: 1

Views: 1010

Answers (3)

garvit gupta
garvit gupta

Reputation: 303

I think varchar(max) is the best datatype for the strings whose length is unknown.

For more details you can go through this link : http://www.dotnetspider.com/forum/158173-Difference-between-Varchar-nvarchar.aspx

Upvotes: 0

Mithrandir
Mithrandir

Reputation: 25397

You could do this:

ALTER TABLE ctrc ADD [cert_length] as LEN (x509certificate);

SQL Server will use a suitable size for the column.

If you talk about the certificate, then NVARCHAR(MAX) is fine. SQL Server will store even (N)VARCHAR(max) columns in row, if you didn't change any table options and there is still suffient space in the page.

Upvotes: 1

Justin Wignall
Justin Wignall

Reputation: 3510

If you mean what is the best column type for storing the certs themselves, there is no fixed length for an X509 cert, using nvarchar is exactly the right thing to go for and nvarchar(MAX) or e.g. nvarchar(maxlength * 2) will have essentially zero performance impact on your database compared to trying to get the exact length requirement right.

If you mean what is best for storing the results of the LEN calculation then a regular int column will do just fine. tinyint works ok too but again if you're thinking about performance or storage issues the difference unless you're storing billions of rows will be less than your mental cost of maintaining/remembering.

In summary, 'Keep It Simple' :)

Upvotes: 4

Related Questions