Well, this is about as far as it gets. Personally I would remove you from a position as SQL responsible ddeveloper for this.
See, yes, you DO get this sort order. NOW. But once you add ONE condition, things may change. SQL states very clealry that the order of data returned is NOT defined unless you define it. Relying on an implementation detail is as bad as it can get as a developer. Especially relying on something as a clustered index which then a DBA may decide to change. The rules stand, as defined by the SQL specs, that the output of a select statement has no intrinsic order. Your answering posts actually relies on the SQL Server deciding to make a table scan - which it may do, or it may not do, depending on a lot of factory. In your simplistic case all of this factors are not present, though. Thomas > -----Original Message----- > From: Unmoderated discussion of advanced .NET topics. > [mailto:[EMAIL PROTECTED] On Behalf Of > Potter, Mark S. > Sent: Montag, 26. Juli 2004 16:27 > To: [EMAIL PROTECTED] > Subject: Re: [ADVANCED-DOTNET] Common Sort Order for .NET and > SQL Server > > Actually if a table has a clustered index then the data is > physically stored in ascending or descending order of that > clustered index in tha data pages. For example the following > table is ordered by ID as the primary clustered index of > InventoryState. > > CREATE TABLE [InventoryState] ( > [Id] [int] IDENTITY (1, 1) NOT NULL , > [State] [varchar] (50) COLLATE > SQL_Latin1_General_CP1_CI_AS NOT NULL , > [Active] [bit] NOT NULL CONSTRAINT > [DF_InventoryState_Active] DEFAULT (1), > [UserName] [varchar] (50) COLLATE > SQL_Latin1_General_CP1_CI_AS NOT NULL , > [LastUpdated] [smalldatetime] NOT NULL CONSTRAINT > [DF_InventoryState_LastUpdated] DEFAULT (getdate()), > [LastChanged] [timestamp] NOT NULL , > CONSTRAINT [PK_InventoryState] PRIMARY KEY CLUSTERED > ( > [Id] > ) ON [PRIMARY] , > CONSTRAINT [IX_InventoryState] UNIQUE NONCLUSTERED > ( > [State] > ) ON [PRIMARY] > ) ON [PRIMARY] > GO > > So if I say "select [id], state, active from inventorystate" > for example. I would get the the recordset in ASCENDING ID > order from SQL even though I did not specify an order by clause. > > ID state active > ----------- -------------------------------------------------- ------ > 1 InActive 1 > 2 Ready 1 > 3 ReservedForShipment 1 > 4 ShippedToCustomer 1 > 5 ReceivedAtCustomer 1 > 6 InstalledAtCustomer 1 > 7 NeedsRepairAtCustomer 1 > 8 ShippedToPlant 1 > 9 ReceivedAtPlant 1 > 10 NeedsRepairAtPlant 1 > 11 Retired 1 > > (11 row(s) affected) > > If we show the query plan we would see the following. > > StmtText > ----------------------------------------------- > select ID, state, active from inventorystate > > (1 row(s) affected) > > StmtText > > -------------------------------------------------------------- > -------------------------------- > |--Clustered Index > Scan(OBJECT:(..[dbo].[InventoryState].[PK_InventoryState])) > > (1 row(s) affected) > > StmtText > ------------------------------- > > > SET STATISTICS PROFILE OFF > > (1 row(s) affected) > > > My 2 cents. > > -----Original Message----- > From: Thomas Tomiczek [mailto:[EMAIL PROTECTED] > Sent: Monday, July 26, 2004 10:10 AM > To: [EMAIL PROTECTED] > Subject: Re: [ADVANCED-DOTNET] Common Sort Order for .NET and > SQL Server > > > Now, this is a totally different topic, obviously. > > Ok, so you basically found out that that there are issues > with the sort order. This is actually a problem I stubmeld > into some time ago - the SQL sort orders are not per > definition "correct" when you compare them to the .net > locales. I think this is more that .NET does not sort > correctly / langauge dependant, but I have not fully verified this. > > Now, from what you posted originally I take you do not > actually care about the order being correct for any locale > (like when you present it to the user and the user would call > you an idiot because your program presents a list in the > wrong sort order). If this is the case, then the topic is > basically finding ONE sort order that works in common between > SQL Server and .NET. As long, as it seems, as they both > match, the answers are correct for you (again, comparing this > to a presentatation to the end user, where this may be still wrong). > > As you said, Latin1_General_CI_AS seems to be about the best > you can get your hands on. Be happy you have not to present > anything to the user at the same time. > > Thomas Tomiczek > THONA Software & Consulting Ltd. > (Microsoft MVP C#/.NET) > (CTO PowerNodes Ltd.) > > > -----Original Message----- > > From: Unmoderated discussion of advanced .NET topics. > > [mailto:[EMAIL PROTECTED] On Behalf Of Jon > > Stonecash > > Sent: Montag, 26. Juli 2004 15:55 > > To: [EMAIL PROTECTED] > > Subject: Re: [ADVANCED-DOTNET] Common Sort Order for .NET and SQL > > Server > > > > My apologies to you Thomas. it has been a long weekend and I could > > have been more complete in the question. I am sorting the > data (ORDER > > BY) in SQL, but .NET does not see the data coming in from > the stream > > as ordered. > > It is mostly sorted but there are some items "out of order". > > I decided to create a test program to see which collations > worked and > > which did not: > > > > Option Strict On > > Option Explicit On > > > > Imports System > > Imports System.data.sqlclient > > Imports System.Globalization > > > > Module Module1 > > > > Private _Connection As SqlConnection > > > > Sub Main() > > > > Try > > _Connection = New SqlConnection _ > > > > ("server=(local);uid=sa;pwd=pass4jcs;database=ISI;Max Pool > > Size=200") > > _Connection.Open() > > TryCollation("SQL_Latin1_General_Cp1_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1_CI_AS") > > TryCollation("Latin1_General_CS_AS") > > TryCollation("Latin1_General_CI_AS") > > TryCollation("SQL_Latin1_General_Cp1250_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1250_CI_AS") > > TryCollation("SQL_Latin1_General_Cp1251_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1251_CI_AS") > > TryCollation("SQL_Latin1_General_Cp1253_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1253_CI_AS") > > TryCollation("SQL_MixDiction_Cp1253_CS_AS") > > TryCollation("SQL_AltDiction_Cp1253_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1253_CI_AI") > > TryCollation("SQL_Latin1_General_Cp1254_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1254_CI_AS") > > TryCollation("SQL_Latin1_General_Cp1255_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1255_CI_AS") > > TryCollation("SQL_Latin1_General_Cp1256_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1256_CI_AS") > > TryCollation("SQL_Latin1_General_Cp1257_CS_AS") > > TryCollation("SQL_Latin1_General_Cp1257_CI_AS") > > Catch myException As Exception > > Console.WriteLine(myException.ToString) > > Finally > > _Connection.Close() > > End Try > > Console.Read() > > > > End Sub > > Private Function TryCollation(ByVal theCollation As > > String) As Int32 > > > > Dim myCompare As Int32 > > Dim myErrorCount As Int32 = 0 > > Dim myDataReader As SqlDataReader > > > > Try > > Dim mySQL As String = "SELECT CONVERT(varchar(255), > > CT.[Name]) " > > _ > > & " COLLATE {0} AS AddRemoveName" _ > > & " FROM [Application List] AL" _ > > & " INNER JOIN [Count of Installed > Applications] CT ON > > CT.AppKeyID = AL.AppKeyID" _ > > & " WHERE(CT.[Name] Is Not NULL)" _ > > & " ORDER BY AddRemoveName" > > Dim myCommand As New > > SqlCommand(String.Format(mySQL, theCollation), _Connection) > > myDataReader = myCommand.ExecuteReader() > > Dim myLastName As String = "" > > Dim myCurrentName As String = "" > > While myDataReader.Read > > myCurrentName = myDataReader.GetString(0).Trim > > myCompare = String.Compare(myLastName, > myCurrentName, > > True, > > CultureInfo.InvariantCulture) > > Select Case myCompare > > Case Is < 0, Is = 0 > > ' this is goodness > > Case Else > > myErrorCount += 1 > > 'Console.WriteLine(String.Format("{0} > > should be lower than {1}", _ > > ' myLastName, myCurrentName)) > > End Select > > myLastName = myCurrentName > > End While > > myDataReader.Close() > > Console.WriteLine("Collation = " & theCollation > & " Error > > count = " & myErrorCount.ToString) > > Catch myException As Exception > > Console.WriteLine(myException.ToString) > > Finally > > myDataReader.Close() > > End Try > > End Function > > > > End Module > > > > This gives the following results: > > > > Collation = SQL_Latin1_General_Cp1_CS_AS Error count = 4 > Collation = > > SQL_Latin1_General_Cp1_CI_AS Error count = 4 Collation = > > Latin1_General_CS_AS Error count = 0 Collation = > Latin1_General_CI_AS > > Error count = 0 Collation = SQL_Latin1_General_Cp1250_CS_AS Error > > count = 13 Collation = SQL_Latin1_General_Cp1250_CI_AS > Error count = > > 13 Collation = SQL_Latin1_General_Cp1251_CS_AS Error count = 13 > > Collation = SQL_Latin1_General_Cp1251_CI_AS Error count = > 13 Collation > > = SQL_Latin1_General_Cp1253_CS_AS Error count = 13 Collation = > > SQL_Latin1_General_Cp1253_CI_AS Error count = 13 Collation = > > SQL_MixDiction_Cp1253_CS_AS Error count = 175 Collation = > > SQL_AltDiction_Cp1253_CS_AS Error count = 4 Collation = > > SQL_Latin1_General_Cp1253_CI_AI Error count = 4 Collation = > > SQL_Latin1_General_Cp1254_CS_AS Error count = 13 Collation = > > SQL_Latin1_General_Cp1254_CI_AS Error count = 15 Collation = > > SQL_Latin1_General_Cp1255_CS_AS Error count = 13 Collation = > > SQL_Latin1_General_Cp1255_CI_AS Error count = 13 Collation = > > SQL_Latin1_General_Cp1256_CS_AS Error count = 13 Collation = > > SQL_Latin1_General_Cp1256_CI_AS Error count = 13 Collation = > > SQL_Latin1_General_Cp1257_CS_AS Error count = 13 Collation = > > SQL_Latin1_General_Cp1257_CI_AS Error count = 13 > > > > What this suggests is that I should sort using collation > > "Latin1_General_CI_AS" to get .NET to agree with the sort > done by SQL > > Server. > > > > > > >From: Thomas Tomiczek <[EMAIL PROTECTED]> > > >Reply-To: "Unmoderated discussion of advanced .NET topics." > > ><[EMAIL PROTECTED]> > > >To: [EMAIL PROTECTED] > > >Subject: Re: [ADVANCED-DOTNET] Common Sort Order for .NET and SQL > > >Server > > >Date: Mon, 26 Jul 2004 15:30:20 +0200 > > > > > >Ok, let's go. > > > > > > > -----Original Message----- > > > > From: Unmoderated discussion of advanced .NET topics. > > > > [mailto:[EMAIL PROTECTED] On Behalf Of Jon > > > > Stonecash > > > > Sent: Montag, 26. Juli 2004 14:13 > > > > To: [EMAIL PROTECTED] > > > > Subject: [ADVANCED-DOTNET] Common Sort Order for .NET and > > SQL Server > > > > > > > > I am having a problem getting SQL Server and .NET to > agree on the > > > > sort order for some data. > > > > > >Really? > > > > > > > My sort column in SQL Server is character data ([varchar] > > > > (255) COLLATE SQL_Latin1_General_CP1_CI_AS). As I > > understand this, > > > > the data sorts in dictionary order. Thus, a value of > > "F-SA" sorts > > > > between "FR" and "FT". > > > > > >Tip: you seem to haves kipped reading the beginner section > > of your SQL > > >documentation. Your understanding is totally unsubstantiated. > > > > > > > The problem is that .NET seems to think that the data is not > > > > in order. I > > > > > >And guess what, .NET is right with this. > > > > > >Beginner SQL: unless you explicitly sort the data, it is > unsorted in > > >the returned data set. > > > > > > > have tried all of the CultureInfo settings that I can > think of to > > > > convince .NET to see this as ordered. I have a need to > > > > > >Why not fix your bugs regarding any basick nowledge how SQL works, > > >instead of trying to get .NET agreeing? > > > > > > > process these rows and compare them to another sorted > > stream of data > > > > and the comparison is going nuts when it gets to these "special > > > > cases". > > > > > >Then why do you not sort the data? > > > > > > > Is there a way to get SQL Server and .NET on the same > (code) page > > > > without sorting the data internally after reading it from SQL > > > > Server? > > > > > >No. SQL 101. Unless the SQL statement specifies a sort order, sort > > >order is random. > > > > > >That said - why do you not just specify a sort order in > your query? > > >This is pretty simple, and you do not have to resort on the client > > >then. It just is not automatic. > > > > > >Note: SQL is SET ORIENTED. Sets do not have an intrinsic > sort order. > > > > > >Thomas Tomiczek > > >THONA Software & Consulting Ltd. > > >(Microsoft MVP C#/.NET) > > >(CTO PowerNodes Ltd.) > > > > > > > > > > > Jon Stonecash > > > > > > > > =================================== > > > > This list is hosted by DevelopMentor(r) > > http://www.develop.com Some > > > > .NET courses you may be interested in: > > > > > > > > NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles > > > > http://www.develop.com/courses/gaspdotnetls > > > > > > > > View archives and manage your subscription(s) at > > > > http://discuss.develop.com > > > > > > > > > >=================================== > > >This list is hosted by DevelopMentor(r) > > http://www.develop.com Some .NET > > >courses you may be interested in: > > > > > >NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles > > >http://www.develop.com/courses/gaspdotnetls > > > > > >View archives and manage your subscription(s) at > > >http://discuss.develop.com > > > > =================================== > > This list is hosted by DevelopMentor(r) > http://www.develop.com Some > > .NET courses you may be interested in: > > > > NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles > > http://www.develop.com/courses/gaspdotnetls > > > > View archives and manage your subscription(s) at > > http://discuss.develop.com > > > > =================================== > This list is hosted by DevelopMentor(r) http://www.develop.com > Some .NET courses you may be interested in: > > NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles > http://www.develop.com/courses/gaspdotnetls > > View archives and manage your subscription(s) at > http://discuss.develop.com > > =================================== > This list is hosted by DevelopMentor(r) http://www.develop.com > Some .NET courses you may be interested in: > > NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles > http://www.develop.com/courses/gaspdotnetls > > View archives and manage your subscription(s) at > http://discuss.develop.com > =================================== This list is hosted by DevelopMentorŪ http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com
