Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002

    Question Unanswered: Two questions about SQL Server vs Access

    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.

  2. #2
    Join Date
    Feb 2002
    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.

  3. #3
    Join Date
    Dec 2002
    Czech Republic

    Re: Two questions about SQL Server vs Access

    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
    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)
    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,,, ...

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts