Changing SQL Server Database Sorting

Changing SQL Server Database sorting

You'll need to remove WITH SCHEMABINDING from your views and table-valued functions. To identify them you can query the INFORMATION_SCHEMA views:

SELECT TABLE_SCHEMA, TABLE_NAME AS VIEW_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%SCHEMABINDING%'

SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SCHEMABINDING%'

1. First backup the database.
2. Generate an ALTER script of all schema-bound views and functions.
3. Delete the words "WITH SCHEMABINDING" from the script.
4. Run the script a few times, until all reference errors are resolved.
5. Change the collation on your database.
6. Script and drop all constraints (keys, checks and defaults).
7. Change the collation of each column using the script below.
8. Recreate constraints.
9. Finally, run the original script a few times to enable schema-binding.

You can change the collation of all columns with this script:

DECLARE @collation nvarchar(128)
DECLARE @commands table ([SQL] nvarchar(max))
DECLARE @cursor cursor
DECLARE @sql nvarchar(max)

SET @collation = 'SQL_Latin1_General_CP1_CI_AS'

INSERT @commands ([SQL])
SELECT 'ALTER TABLE ' + QUOTENAME(c.TABLE_SCHEMA) +'.'+ QUOTENAME(c.TABLE_NAME)
    + ' ALTER COLUMN ' + QUOTENAME(c.COLUMN_NAME)
    + ' ' + c.DATA_TYPE
    + ISNULL('(' + LTRIM(STR(c.CHARACTER_MAXIMUM_LENGTH)) + ')', '')
    + ISNULL(' COLLATE ' + @collation, '')
    + ' ' + CASE c.IS_NULLABLE WHEN 'NO' THEN 'NOT ' ELSE '' END + 'NULL'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND c.COLLATION_NAME <> @collation

SET @cursor = CURSOR FOR SELECT [SQL] FROM @commands
OPEN @cursor
FETCH NEXT FROM @cursor INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN 
    PRINT @sql
    EXEC (@sql)

    FETCH NEXT FROM @cursor INTO @sql
END

What's the best way to store sort order in SQL?

None of the answers so far have touched on the real problem with custom sort order and that is what happens when two different people want the same records sorted differently.

If you need a custom sort order, you need a related table to store it in, not an additional field. The table would have the userid, the recordId of the data and the sort order for the record. That way Joe Smith can have one order and Sally Jones another for the same data. Now you have the problem of new records being added to the data set. Do you put them at the beginning of the sort order or the end or do you require the person to set an order for them before they can be added to the set. This is in actuality a very complex problem that is generally not worth the amount of time it takes to implement because almost no one ever uses that system once it's in place (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated in terms of saving the order of all the records (which will of course require changes the next time the query is run since there will be new records.) This is very painful process of limited untility.

I did this once in a proposal writing application because we needed to be able to sort the parts and tasks on the proposal in the order we thought would be most impressive to the customer. Even then, we had to institute a default order, so that they only need to move around the two or three things they really wanted to show up first instead of ordering 10,000 individual parts.

A better choice if you can get them to buy off on it, is to allow them to sort the data by columns (desc or asc). Usually the user interface can be designed so that if you click on a column header, it will resort the data by that column. This is relatively straightforward to do and meets most needs for custom ordering.

You really need to discuss this requirement with management and get details of how they want it to work beyond, I want custom ordering. This is often one of those things people think they want, but don't really use.

How to sort values in columns and update table?

You would have to use a second table

1. create a new table games2 with the same structure as your games table, making sure the ID is auto-incrementing

   CREATE TABLE `games2` LIKE `games`;
   

2. copy the data, sorted, into games2

   INSERT INTO `games2` (`Name`, `SomeDescription`) SELECT `Name`, `SomeDescription` FROM `games` ORDER BY `Name`
   

3. drop or move the old table

   -- DROP TABLE `games`;
   -- or
   RENAME TABLE `games` TO `games1`;
   

4. rename new table to old name

   RENAME TABLE `games2` TO `games`;
   

These steps will result in what you want.

How to update SQL Server Table after query sort by datetime

I want to keep or the data sort by datetime.

You just can't. SQL tables represent unordered sets of rows, so you cannot actually order the stored data. There is no inherent or default data ordering that you could modify.

You might notice that rows appear to be returned in the same order when you run the same query again, but database engines do not whatsoever guarantee that, and the results you see today may change in the future.

Whenever you need to retrieve the data in a given order, you do need to use an order by clause. Otherwise, the ordering is undefined, meaning that the database is free to return the rows in any order it likes.

How does sql server sort your data?

Although it's good to wonder about how it might be explained that you often see the same order, I'd like to point out that it never a good idea to rely on implicit order caused by the particular implementation of the underlying database engine. In other words, its nice to know why, but you should never ever rely on it. For MS SQL, the only thing that reliably delivers the rows in a certain order, is an explicit ORDER BY clause.

Not only do different RDMBS-es behave differently, one particular instance may behave differently due to an update (patch). Not only that, even the state of the RDBMS software may have an impact: a "warm" database behaves differently than a "cold" one, a small table behaves differently than a large one.

Even if you have background information about the implementation (ex: "there is a clustered index, thus it is likely the data will be returned by order of the clustered index"), there is always a possibility that there is another mechanism you don't know about that causes the rows to be returned in a different order (ex1: "if another session just did a full table scan with an explicit ORDER BY the resultset may have been cached; a subsequent full scan will attempt to return the rows from the cache"; ex2: "a GROUP BY may be implemented by sorting the data, thus impacting the order the rows are returned"; ex3: "If the selected columns are all in a secondary index that is already cached in memory, the engine may scan the secondary index instead of the table, most likely returning the rows by order of the secondary index").

Here's a very simple test that illustrates some of my points.

First, startup SQL server (I'm using 2008). Create this table:

create table test_order (
    id int not null identity(1,1) primary key
,   name varchar(10) not null 
)

Examine the table and witness that a clusted index was created to support the primary key on the id column. For example, in sql server management studio, you can use the tree view and navigate to the indexes folder beneath your table. There you should see one index, with a name like: PK__test_ord__3213E83F03317E3D (Clustered)

Insert the first row with this statement:

insert into test_order(name)
select RAND()

Insert more rows by repeating this statement 16 times:

insert into test_order(name)
select RAND()
from   test_order

You should now have 65536 rows:

select COUNT(*) 
from   test_order

Now, select all rows without using an order by:

select *
from   test_order

Most likely, the results will be returned by order of the primary key (although there is no guarantee). Here's the result I got (which is indeed by order of primary key):

#      id    name
1      1     0.605831
2      2     0.517251
3      3     0.52326
.      .     .......
65536  65536 0.902214

(the # is not a column but the ordinal position of the row in the result)

Now, create a secondary index on the name column:

create index idx_name on test_order(name)

Select all rows, but retrieve only the name column:

select name
from   test_order

Most likely the results will be returned by order of the secondary index idx_name, since the query can be resolved by only scanning the index (i.o.w. idx_name is a covering index). Here's the result I got, which is indeed by order of name.

#      name
1      0.0185732
2      0.0185732
.      .........
65536  0.981894

Now, select all columns and all rows again:

select * 
from test_order

Here's the result I got:

#      id    name
1      17    0.0185732
2      18    0.0185732
3      19    0.0185732
...    ..    .........

as you can see, quite different from the first time we ran this query. (It looks like the rows are ordered by the secondary index, but I don't have an explanation why that should be so).

Anyway, the bottom line is - don't rely on implicit order. You can think of explanations why a particular order can be observed, but even then you can't always predict it (like in the latter case) without having intimate knowledge of implementation and runtime state.

How do I order my SQL Server database from SQL Server Management Studio?

You are misunderstanding a fundamental concept about SQL: tables represent unordered sets. They have no ordering. So, there is simply no such thing as an "ordered table".

If you want the results in a particular order, you need to use an ORDER BY clause on the query. Period. That is how SQL works.

There is one capability in SQL Server that is suspiciously close to an ordered table -- clustered indexes. When a table has a clustered index, the data is sorted on the data pages by the index keys. However, this does not guarantee that a SELECT will actually return the rows in that order.

changing SQL Server database collation

I had this issue a while ago when we changed the underlying database to support unicode and needed to change character sets on a legacy database to support hungarian.

This script will get you a long way to changing the collation; you will need to change the main DB collation manually. I haven't had to run it in a long while it doesn't fix any calculated columns but there may be other issues. Don't run it in a live database without testing it first - you might want to select out the changes it makes so you can audit or figure out the ones missed later.

Declare
    @NewCollation varchar(255), @DBName sysname
Select  @NewCollation = 'Latin_1_CI_AI', -- change this to the collation that you need
        @DBName = DB_NAME()

Declare
    @CName varchar(255), @TbleName sysname, @objOwner sysname, @Sql varchar(8000), @Size int, @Status tinyint, @Colorder int

Declare CurWhileLoop cursor read_only forward_only local
for Select
       QUOTENAME(C.Name)
      ,T.Name
      ,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)
      ,C.Prec
      ,C.isnullable
      ,C.colorder
    From syscolumns C
      inner join systypes T on C.xtype=T.xtype
      inner join sysobjects O on C.ID=O.ID
      inner join sysusers u on O.uid = u.uid
    where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
      and O.xtype in ('U')
      and C.collation != @NewCollation
    and objectProperty(O.ID, 'ismsshipped')=0
    order by 3, 1

open CurWhileLoop
SET XACT_ABORT ON
begin tran
fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder
while @@FETCH_STATUS =0
begin
  set @Sql='ALTER TABLE '+@objOwner+' ALTER COLUMN '+@CName+' '+@TbleName+ isnull ('('
+convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation
+' '+case when @Status=1 then 'NULL' else 'NOT NULL' end
  exec(@Sql) -- change this to print if you need only the script, not the action
  fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder
end
close CurWhileLoop
deallocate CurWhileLoop
commit tran

SQL best practice to deal with default sort order

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

---

Related Topics