I've just converted my site over from Access to SQL Server (only on my computer, I haven't uploaded it to my site). Anyway, I have a few questions about running SQL Commands on an SQL Server.

1) I used to be able to run this portion of an SQL Statement ..."ORDER BY [Networth] DESC, [TotalLand] DESC, [Country Name]", however it seems SQL Server cannot order by a text field... how do I fix this?

2) I used to have an Autonumber field in Access. The Upsizing wizard correctly converted it to a int field with increment. However, sometimes I want to delete all the records and start the incrementing over. I used to copy and paste a "Structure Only" version of the table in Access to fix this, but how do I do it with an SQL Database?

Thanks in advance.

You are correct about the order by - what is the longest value that is stored in the text field ?

For your 2nd question, you can use the truncate table statement. This will delete all entries in the table as well as reseting the identity column.

1)SQL Server cannot order by a text field...
You were not too much specific about text presentation in MSSQL database, so main problems are:

i) Collations
MS SQL Server 2000 uses collations for text columns.
Every collation has specified name and order id.
Default collation for new databases is selected
during SQL Server setup and selected collation is used in system databases (master,tempdb,...).
Default collation for existing database can be changed by ALTER DATABASE statement.
Collation of selected column can be changed by COLLATE statement.
WARNING: Tables cannot be joined on columns with different collations
without recollating in statement (no "default" conversion).

ii) "select-driven" order
MS SQL Server 2000 uses special "select-driven" order,
so you must be carefull when using
SELECT Name=Name COLLATE ...
FROM ...
ORDER BY Name
SELECT Name=CONVERT(...,Name)
FROM ...
ORDER BY Name
because of ordering by collated or converted value.
Will not help (bug?):
SELECT Name=Name COLLATE ...
FROM [Table]
ORDER BY [Table].Name
Try:
SELECT XName=Name COLLATE ...
FROM ...
ORDER BY Name
Datetime values are also affected by this feature.

iii) Order length limit
Limited to 8,060 B.

iv)The text, ntext, and image data types cannot be used in an ORDER BY clause
(Msg 420, Level 16, State 1)
Use:
ORDER BY CONVERT(CHAR(8000),SUBSTRING([ValueText],1,8000))
(4000 for nchar)

iv+)Order can be collated.

iv++)When you need more than 8000 chars, you can try
select into ordered subordering temporary tables with identity(int,1,1) function
(limit 2G unique values-> advance to bigint) and inner joining these tables
on substrings of orginal text values. Then we can use
ORDER BY #SubOrderTab8000.id, #SubOrderTab16000.id, #SubOrderTab24000.id, ...

iv+++)Full 2 GB text needs (2000000000/8000*4)=1,000,000 B per row
to order by the first level subordering so we must use the second level subordering tables

2) I used to have an Autonumber ...
Try for fast reseeding without truncate table :
DBCC CHECKIDENT ('table_name',RESEED)


For more information see MS SQL OnLine Books.