#347500 Author:  Syed Shakeer Hussain Member Level: Gold Member Rank: 93 Date: 16/Feb/2009 Rating:  Points: 5 |
Hi
a couple of points to keep in mind with VARCHAR(MAX):
* The size range for VARCHAR is now 1 to 8,000 or MAX. Note there is nothing between 8,000 and MAX.
* VARCHAR(MAX) is more than a replacement from TEXT. It works just as well with short strings as well as long ones. Therefore, if there is any possibility that you'll exceed 8,000 bytes then use VARCHAR(MAX).
* The maximum size for a VARCHAR(MAX) is 2^31 - 1 bytes / characters.
* The [text in row] option for a table remains specific to TEXT / NTEXT / IMAGE data types. This option lets you tell SQL Server at which size to move TEXT / NTEXT / IMAGE data out of the table's data pages and into its own page allocation (assuming that the [text in row] option is ON). The new [large value types out of row] setting replaces the TEXT option for VARCHAR(MAX). However, this option is now binary. It is either ON or OFF. The size at which MAX types are moved out of a table's data pages and into its own data pages is now fixed at 8,000 bytes if [large value types out of row] is set to OFF.
Thanks & Regards!
Syed Shakeer Hussain
|
| #347511 Author: Sanjay Dawan Member Level: Silver Member Rank: 0 Date: 16/Feb/2009 Rating: Points: 2 |
Sorry i was unable to explore my question ... My question is
ABC table have cloumn varchar type column id
and values are P1, P2, ... , P9 and P10
then can i use below query ?
select MAX(id) from ABC
|
#347534 Author:  Shankar Member Level: Gold Member Rank: 229 Date: 16/Feb/2009 Rating: Points: 2 |
Hi
Yes u can use max in the varchar column,.
But will u able to explain u r requirement clearly ?
select max(name) from test
A S
If u need any clarifications pls mail me.
Thanks and Regards,
A S.
Note - Don't forget to rate this response [Poor / Fine / Good / Great / Excellent]
|
| #347538 Author: Madhavi Latha Member Level: Gold Member Rank: 1120 Date: 16/Feb/2009 Rating: Points: 2 |
ya you can use max function for the column whose datatype is varchar
syntax : select MAX(column_name) from table_name
|
| #347552 Author: kalpana Member Level: Silver Member Rank: 1507 Date: 16/Feb/2009 Rating: Points: 3 |
yes,you can use max with varchar column type.
If the column have the following values.
Example:
p1,p2,p3,p4,p5....p9,p10.It shows the result as p9 because it consider p10 as 'p','1','0' as an individual characters.so p9>p1(0).
|
| #347567 Author: Sanjay Dawan Member Level: Silver Member Rank: 0 Date: 16/Feb/2009 Rating: Points: 0 |
Is there any way to get P10 as max item..
|
| #347653 Author: kalpana Member Level: Silver Member Rank: 1507 Date: 16/Feb/2009 Rating: Points: 6 |
Sanjay,
We can get the p10 as max item by the foll query
--To find the max value of the varchar column type
1. DECLARE @Length INT
2. SET @Length=(SELECT MAX(LEN(ID)) from test)
3. SELECT TOP 1 ID FROM Test WHERE LEN(ID)=@Length ORDER BY ID DESC
In the second statement of the query we are getting the maximum length of ID.So as per our example @Length=3(Length of P10).
In the Third statement of the query we are fetching the ID Which is having the length as 3.So it throws the result as P10.
But Top 1 and order by ID is used for the foll reasons.
1. If there is another ID by P11.It should throw the result as P11 instead of P10.So I am ordering the result of the 3rd statement by id(Desc).So now the result of the select query is P11,P10.To get the result as P11.I am using Top 1
|
| #347726 Author: Sanjay Dawan Member Level: Silver Member Rank: 0 Date: 17/Feb/2009 Rating: Points: 1 |
Hmm I got it Kalpana..
Such a wonderful post...
Thanks a lot....
It is realy helpful ..
|