Sql-server – tablediff on two similar tables

migrationsql server

We're doing a SQL Server 2005 upgrade to 2008 and one of the things that we're also changing at the same time is we're changing all varchar fields to nvarchar (don't ask me why).

After importing the data to the new 2008 database, I want to compare and validate the data. So I tried using the tablediff program that comes with SQL Server but it can't compare the two schemas due to the varchar/nvarchar difference.

This is the error I get:

Table [db1].[dbo].[table1] on server1 and Table [db2].[dbo].[table2] on server2 have different schemas and cannot be compared.

Is there a quick workaround to do this? If not, anyone have any suggestions that are free?

As an aside, it would be great if instead of comparing the entire table, I can compare only the rows with a certain condition (ie. where dates > 2009)

Best Answer

If you want a utility to do this for you (and you can do it in a 14 day evaluation, or don't mind spending some money) Red Gate Software have excellent utilities SQL Compare (for schemas) and SQL Data Compare (for data).

(Thanks to squillman for pointing out I'd linked the wrong one!)

Related Solutions

Sql-server – Row size, indexes and varchar(max)

There is basically no difference between VARCHAR(2048) and VARCHAR(MAX). One is subject to overflow into the 'row overflow' allocation unit, the other is subject to overflow into the BLOB allocation unit, see Table and Index Organization. The default setting of the large value types out of row table option is 0 so, unless it was changed, the VARCHAR(MAX) will stay in row if possible, just like VARCHAR(2048) would.

I would recommend running sys.dm_db_index_physical_stats and getting the actual max, min and avg row size, as well as avg_page_space_used_in_percent. This will give a more accurate picture of the true row size(s), rather than the theoretical declared size.

Sql-server – sql server limitation of data size

To make things clear, we could take the first statement which is a very fundamental one:

A table can contain a maximum of 8,060 bytes per row.

In other words: each row must fit into one page (8 kB). And if possible, many rows can fit into one page - but never the other way round.

How about binary large objects (TEXT, NTEXT, BLOB)? Data in these columns is stored in a special place. The row just holds a pointer to the data, hence the fundamental statement above still holds: a table row must fit into one page.

Binary large data is stored outside of the table.
Each table row just holds a pointer in each BLOB field which actually points to the data.

Having this said, the new varchar(max) feature is special and behaves as follows:

varchar(n): a usual text fields stored in row, row data must fit into one page.
varchar(max), small data content: stored in row as long as all row data fits into one page.
varchar(max), big data content: as soon as the data does not fit into one page anymore, a pointer is stored in row and varchar(max) data is stored separately. In this case, varchar(max) behaves similar to the text data type.

Note the big data content depends on all the other columns of the table. Furthermore, varchar(max) can store text in compressed form such that more than 8,060 characters might fit into one data page.

In my opinion, the statement The length of each one of these columns must still fall within the limit of 8,000 bytes seems not to be precise, possibly even incorrect (a varchar(max) column can in fact hold 2 GB of data, though not being stored in row).

Best Answer

Related Solutions

Sql-server – Row size, indexes and varchar(max)

Sql-server – sql server limitation of data size

Related Topic