How to optimize SQL Server table with 320 million + rows with only varchar(max) data types

Question

I have a table with 320 million+ rows and 34 columns, all of varchar(max) datatype, and with no indexing.

I am finding it extremely time consuming to summarize the whole table. Can anyone suggest best way to optimize this considering the following purpose I need to use it:

• simple select queries
• sum on a few column

Should I go for creating a non-clustered index on sequential row number column that I have added?

My table size on disk is 185 GB approx.

I am using

• Windows Server 2008 R2
• Xeon Processor 2.09 x 2
• 16 GB RAM
• SQL Server 2008 R2

Since data has no unique column so I have already added a sequenced with integer and it took 2 days to complete the operation.

Answer 1

1. Create new table with suitable data types. It's the most important part - to define type for your columns. nvarchar(max) is the most common type, so it can not be optimized. Use int or bigint for numbers, use nvarchar(N) where N - max length. Use bit for boolean, and so on
2. Create primary key, indexes for search
3. Copy data from the old table to new one with portions of 10000 or 100000 rows.