May 28, 2008 at 3:42 pm
Just curious - is there a cost associated with using the CAST function in a query that returns 500 rows? This particular query does this on 4 different columns. They are casting from ntext to nvarchar(50). I looked at the data and none of the values in any of the fields being "casted" are greater than 24 characters. Would there be some benefit, query-wise, in just storing the values as nvarchar(50) in the first place rather than ntext and using the cast function? Please let me know your thoughts.
Thanks!
Anne
May 28, 2008 at 10:07 pm
When we use text data type in a table, the table only saved the (pointer) address of the data. When you retrieve data, the system needs to find the (pointer) address, then find the data. On the contrary, nchar, nvarchar, ... save data on data pages. I believe using nchar, nvarchar, ... are better.
May 28, 2008 at 11:46 pm
I agree with the previous poster, the issue isn't the CAST function, which is very fast, the issue is the NText datatype itself, which is slow, cumbersome and inconvenient.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 11:54 pm
Hi
The main issue here lies with the choice of data type , but cast does have some impact on performace specially if the number of rows are large.
"Keep Trying"
May 29, 2008 at 5:56 am
Everyone else has nailed it. The one other thing you might consider is using NVARCHAR(MAX) if you are potentially storing more than 50 characters. Well, more than 4000 in this case. That gives you the best of both worlds. While the data fits on a page, it's stored with the row in the database just like any other CHAR or VARCHAR field. Once it gets big enough it stores it off to disk with a pointer just like a TEXT field. Actually, the TEXT data type is no longer necessary and I'm using MAX fields exclusively for this type of data.
All that said, if you only need 24 or 50 characters worth of data, make your life easier and define the field to store that much.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 29, 2008 at 12:58 pm
I'd add that if you *know* your data will fit in a nvarchar(50) - and future data as well - it's good to keep it that small if you want to index on the field.
(but you can cover it if it needs to stay big, though)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply