Microsoft – SQL Server – Column DataTypes \ Definitions {the weary kind}

Microsoft – SQL Server – Column DataTypes \ Definitions  {the weary kind}

The local BlockBuster Video Rental Store is Closing down.

Just like a kid remembers the local burger place or McDonald’s, I think adults remember the local music stores, the local BlockBuster \ Hollywood Store.

And, I think some of us remember the local  “Barnes & Noble”, Walden Books.  Just to be a able to go in to a book store and see all the books, you will never get to read, but would love to.

Many years ago it was the Client Server {OS\2} books by Robert Orfali and Dan Harkey; San Jose State U lecturers.  Programming Windows Books by Charles Petzold – http://www.charlespetzold.com/books.html.

A few years ago it was the stack of Java Books – Enterprise Java (EJB).  Today it might be Enterprise Service Bus (ESB) or HTML 5.

But, back to our Weary Kind fable.

A good cover of “Weary Song”  is available @
http://www.youtube.com/watch?v=ECaTozt_8uo&feature=relmfu

In Database World, There are some data types or column definitions that are a bit harder to optimize.

In MS SQL Server, they include:

  1. DataTypes – text
  2. Un-persisted  Computed Columns

There are multiple reasons why one needs to review and have peer review of Database tables structural definitions:

The reasons include:

1) Some datatypes are not indexable

2) Some datatypes preclude “Online” Database Operations.  In SQL Enterprise, one can perform online operations such as Index Rebuild, etc.  But, one is unable to do see when a table includes text columns

3) Un-persisted Computed Columns also have to be re-computed each time they are accessed

4) Un-persisted Columns can not be the main key of a partition table

5) Unicode columns (nchar, nvarchar, ntext) help to support Internalization.  But, they also use occupy double the size of their corresponding pair
To identify those columns, one can use a Query that resembles the one below:

select 

	  tblObject.name
	, tblColumn.name
	, tblColumnType.name
	,
	  case

       	    when (tblColumn.max_length = -1) 

               then 'Column Length is MaxLength'

	    when (
		  (tblColumnType.name in ('text', 'ntext'))
		 )
		then 'DataType (Suspect)'

	     when (
		   (tblColumn.is_computed = 1)
		)
		  then 'Computed Column (Unpersisted)'

	     else null 

          end as [reason]

	from   sys.objects tblObject

           inner join sys.columns tblColumn

	     on tblObject.object_id = tblColumn.object_id

	   left outer join sys.computed_columns tblColumnComputed

	    on tblColumn.object_id = tblColumnComputed.object_id
	    and  tblColumn.column_id = tblColumnComputed.column_id	

	   left outer join sys.types tblColumnType

	    on tblColumn.system_type_id
                     = tblColumnType.system_type_id

	where  tblObject.type in ('U')

	and    (

		   -- max data types
		   (tblColumn.max_length = -1) -- Max Data Type

		or

	    	  (

                    --text data types
 		    (
			(tblColumnType.name in ('text', 'ntext')		    )

		)   

		 --un-persisted computed columns
		or (
			 (tblColumn.is_computed = 1)				     and (tblColumnComputed.is_persisted = 0)
		   )	

	)
	order by
              tblObject.name
            , tblColumn.name

References:

1) Are there any disavantages to using Nvarchar(max)
http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax

2) PERFORMANCE COMPARISON OF VARCHAR(MAX) VS. VARCHAR(N)
http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/

3) VARCHAR(MAX) Performance in SQL Server 2008 R2
http://richardlees.blogspot.com/2010/07/varcharmax-performance-in-sql-server.html

4) ONLINE REINDEX=ON
http://weblogs.sqlteam.com/geoffh/archive/2007/11/01/Online-Reindex–ON.aspx

5) NTEXT vs NVARCHAR(4000) in MS SQL Server v 2005
http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

6) Performance Memo/NText Fields
http://blogs.msdn.com/b/emeadaxsupport/archive/2008/11/03/performance-memo-ntext-fields.aspx

7) IN-ROW Data
 http://msdn.microsoft.com/en-us/library/ms189087(v=sql.105).aspx

8)NVarchar(max) columns can not be “Unicode” compressed
http://msdn.microsoft.com/en-us/library/ee240835.aspx

9) Use Unicode Native Format to Export/Import Data
http://msdn.microsoft.com/en-us/library/ms189941(v=sql.110).aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s