January 6, 2011 at 1:32 am
Hello guys, Do any of you have document on best practices for using datatypes while creating tables ?
January 6, 2011 at 1:36 am
Not offhand, but in general use the most appropriate datatype and keep it as small as possible, but not too small.
Be aware that ntext, text and image are deprecated and should not be used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2011 at 2:01 am
When are ntext, text and image going to be depricated ? Hope they are in 2008.
January 6, 2011 at 2:04 am
Also what can be the best options to replace them in general ? Also I'm looking for some generalized tips on using datatypes while creating new systems (tables etc)
January 6, 2011 at 2:06 am
The replacement for text is varchar(max)
The replacement for ntext is nvarchar(max)
The replacement for image is varbinary(max)
As for general advice, what I said in the first post. In general use the most appropriate datatype for what you are storing and keep it as small as possible, but not too small.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2011 at 9:06 pm
Hello Gila, Thanks for the information. We are currently storing xml code in ntext columns. While migrating to SQL 2008, I want to change them all to different datatypes (since ntext is going to be depricated). Should I use xml datatype or nvarchar(max) datatype ? What do you suggest ?
January 7, 2011 at 12:36 am
iamanks (1/6/2011)
Hello Gila, Thanks for the information. We are currently storing xml code in ntext columns. While migrating to SQL 2008, I want to change them all to different datatypes (since ntext is going to be depricated). Should I use xml datatype or nvarchar(max) datatype ? What do you suggest ?
It depends. 😀
There are scenarios when it's best to shred the xml data and store the values in appropriate columns not keeping the xml data at all. If you need to keep the xml structure and you query the data directly, use XML data type, maybe along with XML indexing. And if you need to keep the xml data including header information and you're not intended to query the data directly and frequently, store it as NVARCHAR(MAX).
Those are just a few rules of thumb I use to determine the data type to store xml data. Nothing written in stone though.
@Gail: Excuse me for interrupting. ;-):-P
January 7, 2011 at 12:44 am
What do you do with the xml in the database?
If you put it in nvarchar, it's just a piece of text. If you put it in XML, you can add xml indexes which will help if you're shredding the XML and querying the attributes within
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2011 at 1:17 am
Kendal Van Dyke has a video over at sqlshare about what's involved in converting our existing data to the XML data type.
January 7, 2011 at 1:55 am
Another thing worth mentioning is that data stored in a column with the xml data type is converted to an internal format. This may or may not use more space than storing it in a varchar column, depending on the structure and size of the xml documents. My own tests shows that tiny xml documents takes up more space in a xml column compared to a varchar column, while large xml documents take up less space.
I cannot guarantee that this is always the case, so you should test it yourself.
I've also noticed similar patterns with CPU usage for the two types.
Here is a T-SQL script that you can use to check storage space and CPU usage. Try with different sizes for the xml string and see how it behaves, bot storage wize and CPU wize.
use tempdb
go
set statistics io off
set statistics time off
create table #xml(data xml)
create table #text(data varchar(max))
declare @xml xml, @varchar varchar(max)
set @xml='<master.sys.columns object_id="4" name="rowsetid" column_id="1" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="rowsetcolid" column_id="2" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="hobtcolid" column_id="3" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="status" column_id="4" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="rcmodified" column_id="5" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="maxinrowlen" column_id="6" system_type_id="52" user_type_id="52" max_length="2" precision="5" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="rowsetid" column_id="1" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="ownertype" column_id="2" system_type_id="48" user_type_id="48" max_length="1" precision="3" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="idmajor" column_id="3" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="idminor" column_id="4" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="numpart" column_id="5" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="status" column_id="6" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="fgidfs" column_id="7" system_type_id="52" user_type_id="52" max_length="2" precision="5" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="rcrows" column_id="8" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="auid" column_id="1" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="type" column_id="2" system_type_id="48" user_type_id="48" max_length="1" precision="3" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="ownerid" column_id="3" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="status" column_id="4" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="fgid" column_id="5" system_type_id="52" user_type_id="52" max_length="2" precision="5" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="pgfirst" column_id="6" system_type_id="173" user_type_id="173" max_length="6" precision="0" scale="0" is_nullable="0" is_ansi_padded="1" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />'
set @varchar='<master.sys.columns object_id="4" name="rowsetid" column_id="1" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="rowsetcolid" column_id="2" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="hobtcolid" column_id="3" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="status" column_id="4" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="rcmodified" column_id="5" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="4" name="maxinrowlen" column_id="6" system_type_id="52" user_type_id="52" max_length="2" precision="5" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="rowsetid" column_id="1" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="ownertype" column_id="2" system_type_id="48" user_type_id="48" max_length="1" precision="3" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="idmajor" column_id="3" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="idminor" column_id="4" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="numpart" column_id="5" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="status" column_id="6" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="fgidfs" column_id="7" system_type_id="52" user_type_id="52" max_length="2" precision="5" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="5" name="rcrows" column_id="8" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="auid" column_id="1" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="type" column_id="2" system_type_id="48" user_type_id="48" max_length="1" precision="3" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="ownerid" column_id="3" system_type_id="127" user_type_id="127" max_length="8" precision="19" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="status" column_id="4" system_type_id="56" user_type_id="56" max_length="4" precision="10" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="fgid" column_id="5" system_type_id="52" user_type_id="52" max_length="2" precision="5" scale="0" is_nullable="0" is_ansi_padded="0" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />
<master.sys.columns object_id="7" name="pgfirst" column_id="6" system_type_id="173" user_type_id="173" max_length="6" precision="0" scale="0" is_nullable="0" is_ansi_padded="1" is_rowguidcol="0" is_identity="0" is_computed="0" is_filestream="0" is_replicated="0" is_non_sql_subscribed="0" is_merge_published="0" is_dts_replicated="0" is_x005F_xml_document="0" xml_collection_id="0" default_object_id="0" rule_object_id="0" />'
set statistics io on
set statistics time on
insert into #text
select top 100000 @varchar
from master.sys.columns t1,master.sys.columns t2
insert into #xml
select top 100000 @xml
from master.sys.columns t1,master.sys.columns t2
set statistics io off
set statistics time off
exec sp_spaceused '#xml'
exec sp_spaceused '#text'
January 7, 2011 at 2:43 am
Thanks Gila for the clarification.
Tunnel, I will definitely check the video and will give you the feedback.
Ten, thanks for clarifying it in this way. Though it seems sp_spaceused doesnot work for temp tables, i'll check it with regular tables.
That helped guys. Thanks to all of you.
January 7, 2011 at 2:45 am
sp_spaceused works for temp tables if you are in tempdb. USE tempdb.
January 7, 2011 at 2:49 am
Thats cool. Thanks. Now I can see the difference.
January 7, 2011 at 2:51 am
I'm also looking for some hepl on my other post in this section only on Data purging.
January 10, 2011 at 2:06 am
Hello Nils, I checked as per your code this weekend and found that using xml datatype is good as it takes approx half space as compared to nvarchar(max).
What would you suggest on the basis of performance ?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply