One of the Jr. DBA at in my Team Member asked me question the other day when he was replacing TEXT field with VARCHAR(MAX) : How can I limit the VARCHAR(MAX) field with maximum length of 12500 characters only. His Question was valid as our application was allowing 12500 characters.
Traditionally thinking we only create the field as long as we need. SQL Server 2005 does support VARCHAR(MAX) but does not support VARCHAR(12500). If we try to create database field with VARCHAR(12500) it gives following error.
Server: Msg 131, Level 15, State 3, Line 1
The size (12500) given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000).
The solution we came up was to put constraint on the VARCHAR(MAX). SQL SERVER 2005 supports VARCHAR(MAX) and Constraint.
CREATE TABLE [dbo].[MyTestTable] ( [VarChar12500] VARCHAR(MAX) )
GO
ALTER TABLE [dbo].[MyTestTable]
ADD CONSTRAINT [MaxLengthConstraint]
CHECK (DATALENGTH([VarChar12500]) <= 12500)
GO
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
Hi,
I was trying to use this query and I’m getting an error stating incorrect syntax near MAX. Please help me out with this as I’m trying to pass some text into the database with more than 10000 chars.
Thanks and Regards,
Turya
how can i change the table field order ???
example for I have one table name XYZ and field are Code,Name,Address,Pin how I change the sequence like Name,Code,Address,Pin …. Please Help Me
Shanku
Hi Shanku,
You can not do that. You will have to create new table with your desired column order and insert values from old table to new table. Drop the old table and rename the new table with the name of old table.
However, the order of column does not matter to database.
Regards,
Pinal Dave(SQLAuthority.com)
Hello Sir ,
how can change Primary key from SQL prompt .. Please help me…
im having problem in sending query online to create a table with varchar(max)
they say that the server is sql 2k5 and it seems like its 2005.
but i have replaced max with 300.. cuz i dont really need too much details but what was wrong there?
Sir,
How I can wrote syntax to find the “Maximum of 10 values from a table” like Salary Column using SQL Server 2005
hello sir,
i want to use MAX function in insert query, how can i do that?
insert into xyz(id,name) values(max(id),’nirav dave’)
i want to use max(id) in insert query coz i don’t want to fire special query to find max number of id,
plz. help me.
Hi !
Can you help me please ?
I had to change our database (not empty) collation from French_CI_AS to SQL_Latin1_General_CP850_CI_AI.
: Database\Properties\Options\Collation. Change it !
After doing this, i noticed that only data on fields with “varchar(max) ” type were truncated.
Like, even if i put 100 characters by the interface, no problem but only 12 characters are really saved in the database. Troncation happens only on these types of fiels et always the same number of characters 12
Can anyone knows why and how avoid this ?
Thank you very much and sorry, i do not speak english very well :(
Hi,
I was trying to use this query and I’m getting an error stating incorrect syntax near MAX. Please help me out with this as I’m trying to pass some text into the database with more than 12000 chars.
please send right qeury
on my email id i need it very ugently
Thanks and Regards,
ajay
Even I declare
local Variable with VARCHAR(MAX)
its Given only 8000 As Output..
please Suggest.
Hi All,
i am in doubt whether the constraints are stored in sys_objects or information_schema. table_constraints.
please help me out.