Delete Duplicate Record

I have Changed my database schema and transform Data with new keys using
this Query :
SELECT Case_No, Case_Date, Ma7akem_ID, COUNT(*) AS Expr1
FROM Master_Ahkam
GROUP BY Case_No, Case_Date, Ma7akem_ID
HAVING (COUNT(*) > 1)
Due to a Design error in the old one there was a "ModifyDate" in the
primarykey. so the whole record are the same except the Modifydate.. I need
to delete these rows..
Is there anyway to do so'Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. If you had a proper relational
design in the first place, you could not have dups in the first place.
Can you kill the guy that did this? That will improve the overall
quality of your software.|||Hiiiiiiii
The one who designed this is my boss so if I Kill him I 'll Have to Find new
Job, Can you help -;)
Here is the DDL
----
--
CREATE TABLE [AH_Tasneef] (
[ID] [PKInt] NOT NULL ,
[Parent_ID] [PKInt] NOT NULL CONSTRAINT [DF__Tasneef__Parent___0519C6AF]
DEFAULT (0),
[Ma7kama_ID] [PKInt] NOT NULL ,
[Text] [varchar] (200) COLLATE Arabic_BIN NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_Tasneef] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_AH_Tasneef_AH_Ma7akem] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_TasnFakaraat] (
[Tasneef_ID] [PKInt] NOT NULL ,
[Master_ID] [PKInt] NOT NULL ,
[Fakra_No] [int] NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_TasnFakaraat] PRIMARY KEY NONCLUSTERED
(
[Tasneef_ID],
[Master_ID],
[Fakra_No]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_SubMaster_AH_Master] FOREIGN KEY
(
[Master_ID]
) REFERENCES [AH_Master] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_SUBMA foreign key (Master_ID,
Fakra_No)
references dbo.AH_SubMaster (Master_ID, Fakra_No)
go
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_TASNE foreign key (Tasneef_ID)
references dbo.AH_Tasneef (ID)
go
----
--
I have duplicate records in table AH_TasnFakaraat which i need to clean...
how could i do so'
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133664266.448284.115290@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. If you had a proper relational
> design in the first place, you could not have dups in the first place.
> Can you kill the guy that did this? That will improve the overall
> quality of your software.
>|||On Sun, 4 Dec 2005 11:38:16 +0200, Islamegy wrote:

>Hiiiiiiii
>The one who designed this is my boss so if I Kill him I 'll Have to Find ne
w
>Job, Can you help -;)
Hi Islamegy,
I can't help you killing your boss or finding you a new job, but I might
be able to help with the duplicates.
I'm not sure exactly which columns are duplicated and which are not, nor
how to decide which rows to retain and which to remove, so I'll post a
generic example for you to modify.
CREATE TABLE Test
(Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
CONSTRAINT TestKey PRIMARY KEY (Col1, Col2, Col3)
)
INSERT INTO Test (Col1, Col2, Col3)
SELECT 1, 2, 3
UNION ALL
SELECT 1, 2, 4
go
The primary key should have been (Col1, Col2). This means that the data
above is incorrect. The lowest value for Col3 should be retained, all
others removed. In the end, only the (1, 2, 3) row should survive.
-- Remove unwanted data
DELETE FROM Test
WHERE EXISTS
(SELECT *
FROM Test AS t2
WHERE t2.Col1 = Test.Col1
AND t2.Col2 = Test.Col2
AND t2.Col3 < Test.Col3)
-- Change key
ALTER TABLE Test
DROP CONSTRAINT TestKey
ALTER TABLE Test
ADD CONSTRAINT TestKey PRIMARY KEY (Col1, Col2)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Delete - Exists - problem

Can anyone tell me why the following deletes all the records instead of
simply the ones returned in the "Exists" clause?
The select in the exists by itself returns 131 records, however when run in
the following context it deletes all 4474 that are in the Shades table.'
Delete FROM #TMP_SHADE WHERE EXISTS
(select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
where OLD_SHADE_ID NOT in (select SHADE_ID
from SHADE_SUC))
The idea is to delete any shade records in #tmp_shade where they do not
exist in shade_SUC
ThanksDan,

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
Because you are not correlating the tables. If at least one row in table
#TMP_CHANGEDSHADES meet the condition, all rows from table #TMP_SHADE will b
e
deleted. It should be something like:
Delete
FROM #TMP_SHADE
WHERE EXISTS
(
select DISTINCT(OLD_SHADE_ID)
from #TMP_CHANGEDSHADES
where #TMP_CHANGEDSHADES.col1 = #TMP_SHADE.col1
and OLD_SHADE_ID NOT in (select SHADE_ID from SHADE_SUC)
)
AMB
"Dan" wrote:

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
> The select in the exists by itself returns 131 records, however when run i
n
> the following context it deletes all 4474 that are in the Shades table.?
?
> Delete FROM #TMP_SHADE WHERE EXISTS
> (select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
> where OLD_SHADE_ID NOT in (select SHADE_ID
> from SHADE_SUC))
> The idea is to delete any shade records in #tmp_shade where they do not
> exist in shade_SUC
> Thanks
>|||Delete from X where Exists (Y)
will delete all rows in X if Y is true and nothing if Y is false.
try something like
Delete from X where Y_id in (select distinct(Y_id) from Y)
I hope you get the idea.
Regards,
Nishant
"Dan" wrote:

> Can anyone tell me why the following deletes all the records instead of
> simply the ones returned in the "Exists" clause?
> The select in the exists by itself returns 131 records, however when run i
n
> the following context it deletes all 4474 that are in the Shades table.?
?
> Delete FROM #TMP_SHADE WHERE EXISTS
> (select DISTINCT(OLD_SHADE_ID) from #TMP_CHANGEDSHADES
> where OLD_SHADE_ID NOT in (select SHADE_ID
> from SHADE_SUC))
> The idea is to delete any shade records in #tmp_shade where they do not
> exist in shade_SUC
> Thanks
>|||Great Alejandro! - Thank you for the pointer
Dan
"Alejandro Mesa" wrote:
> Dan,
>
> Because you are not correlating the tables. If at least one row in table
> #TMP_CHANGEDSHADES meet the condition, all rows from table #TMP_SHADE will
be
> deleted. It should be something like:
> Delete
> FROM #TMP_SHADE
> WHERE EXISTS
> (
> select DISTINCT(OLD_SHADE_ID)
> from #TMP_CHANGEDSHADES
> where #TMP_CHANGEDSHADES.col1 = #TMP_SHADE.col1
> and OLD_SHADE_ID NOT in (select SHADE_ID from SHADE_SUC)
> )
>
> AMB
>
>
> "Dan" wrote:
>sql

degree of parallelism in sql server 2000

Hi,

I would like to know how can I see the degree of
parallelism for select,insert,delete statements. I am on
SQL 2000 , I check SQL profiler , Performance event
class , It has got 1,2,3,4 degree of parallelism to
monitor degree but it says (7.0 delete, insert, select,
update)

what is the way to monitor Degree of Parallelism for SQL
server 2000. like to see if query is running in Parallel or not .

Please let me know.

Thanks,
Sac.YOu can use PROFILER to monitor degree of parallelism. For information refer to SQL Performance (http://www.sql-server-performance.com) website for articles and tips.

defining command,commandtype and connectionstring for SELECT command is not similar to INS

i am using visual web developer 2005 and SQL 2005 with VB as the code behind

i am usingINSERT command like this

Dim test As New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()
test.InsertCommandType = SqlDataSourceCommandType.Text
test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@.roll,@.name, @.age, @.email) "
test.InsertParameters.Add("roll", TextBox1.Text)
test.InsertParameters.Add("name", TextBox2.Text)
test.InsertParameters.Add("age", TextBox3.Text)
test.InsertParameters.Add("email", TextBox4.Text)
test.Insert()

i am usingUPDATE command like this

Dim test As New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
test.UpdateCommandType = SqlDataSourceCommandType.Text
test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 "
test.Update()

but i have to use theSELECT command like this which is completely different from INSERT and UPDATE commands

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()

TextBox4.Text = tblData.Rows(1).Item("name").ToString()
TextBox5.Text = tblData.Rows(1).Item("age").ToString()
TextBox6.Text = tblData.Rows(1).Item("email").ToString()

for INSERT and UPDATE commands defining the command,commandtype and connectionstring is same

but for the SELECT command it is completely different. why ?

can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?

if its possible how to do ?

please help me

Hi you can use:

Insert:

Dim

conAsNew SqlConnection("server=.;database=test;uid=sa")Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="insert into t1(name,birth) values (@.n,@.b)"

cmd.Parameters.Add(

New SqlParameter("@.n", Data.SqlDbType.NVarChar)).Value =TextBox1.Text

cmd.Parameters.Add(

New SqlParameter("@.b", Data.SqlDbType.DateTime)).Value =TextBox2.Text

cmd.ExecuteNonQuery()

con.Close()

Update:

Dim conAsNew SqlConnection("server=.;database=test;uid=sa")

Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="updae t1 setname= @.n , birth= @.b)"

cmd.Parameters.Add(

New SqlParameter("@.n", Data.SqlDbType.NVarChar)).Value =TextBox1.Text

cmd.Parameters.Add(

New SqlParameter("@.b", Data.SqlDbType.DateTime)).Value =TextBox2.Text

cmd.ExecuteNonQuery()

con.Close()

Select:

Dim conAsNew SqlConnection("server=.;database=test;uid=sa")

Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="updae t1 setname= @.n , birth= @.b)"

Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)

con.Close()

|||Sorry the third query is "select .........." not "update ...."

Defining a Select Command

I'm trying to populate a DropDownList from my SQL database. I'm using C# 2005 and when I compile my code I get an error.Compiler Error Message:CS0103: The name 'myConnection' does not exist in the current context

using

System;

using

System.Data;

using

System.Data.SqlClient;

using

System.Configuration;

using

System.Collections;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

public

partialclassDefault3 : System.Web.UI.Page

{

privatestring connectionString = WebConfigurationManager.ConnectionStrings["mewconsultingConnectionString"].ConnectionString;protectedvoid Page_Load(object sender,EventArgs e)

{

SqlCommand myCommand =newSqlCommand();

myCommand.Connection = myConnection;

myCommand.CommandText =

"SELECT * FROM tblPau";

myConnection.Open();

SqlDataReader myReader;

myReader = myCommand.ExecuteReader();

myReader.Read();

// The first row in the result set is now available.

lstPau.Items.Add(myReader[

"PauSiteName"]);

myReader.Close();

myConnection.Close();

}

}

Exactly as the error stated: in your code, you did not delcare myConnection.

You need something like this before you reference it:

SqlConnection

myConnection =newSqlConnection(connectionString);

define Select parameters

Hi

I have a DropDownlist (Drop1) and a GridView,the GridView is bount to an SqlDataSource1 that has 2 Select parameters CatId and SourceId

The dropdownlist has a selectedvalue of the following format 15-10(2 numbers seperated by -).I want to set CatId to 15 and SourceId to 10

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:Art %>"SelectCommand="Select * from Option WhereSourceId=@.SourceId AndCatId=@.CatId"><SelectParameters><asp:ControlParameterControlID="Drop1"Name="SourceId"/><asp:ControlParameterControlID="Drop1"Name="CatId"/></SelectParameters></asp:SqlDataSource>

Can anyone help me to define the parameters?

thanks

Hi engnouna,

We can bind the select parameters to Label controls' Text property. And set the Text every time DropDownList select index changed. Here is the demo code:

<asp:GridViewID="GridView1"runat="server"DataSourceID="SqlDataSource1">

</asp:GridView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:testConnectionString%>"

SelectCommand="SELECT [SourceID], [CatID], [Name] FROM [ForDynamicGridView] WHERE (([SourceID] = @.SourceID) AND ([CatID] = @.CatID))">

<SelectParameters>

<asp:ControlParameterControlID="SourceID"DefaultValue="1"Name="SourceID"PropertyName="Text"

Type="Int32"/>

<asp:ControlParameterControlID="CatID"DefaultValue="2"Name="CatID"PropertyName="Text"

Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

</div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

<asp:ListItem>1-2</asp:ListItem>

<asp:ListItem>2-4</asp:ListItem>

<asp:ListItem>3-4</asp:ListItem>

</asp:DropDownList>

<asp:LabelID="SourceID"runat="server"Text="1"Visible="false"></asp:Label>

<asp:LabelID="CatID"runat="server"Text="2"Visible="false"></asp:Label>

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string[] value = DropDownList1.SelectedValue.Split('-');

SourceID.Text = value[0];

CatID.Text = value[1];

}

|||

Hi,

The ControlParameter itself cannot parse the text in your dropdownlist directly. So, I suggest you parse it in your select statement.

For example, if they are all 2 digits numbers, you can use

Select * from Option Where SourceId=LEFT(@.Input,2) And CatId=RIGHT(@.Input,2)

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Define a Key - "select * from "

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.

thanks for your time...On Jul 9, 10:39 am, Stagnight1 <stagnig...@.yahoo.comwrote:

Quote:

Originally Posted by

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.
>
thanks for your time...

You can assign it to Ctrl-V by putting it on the clipboard.|||Stagnight1 wrote:

Quote:

Originally Posted by

Is there a way to define a key that puts the text 'SELECT * FROM '
into the Query Analyzer window?
I must type this about 50 times a day but cannot see a simple way of
defining a key to write it for me..
(tools/customize) seems to execute everything you put in there rather
than leave it on the screen for me to add table names etc to.

I haven't tried it, but
http://www.regsoft.com/keys/
others may be available
http://www.google.com/search?hl=en&...G=Google+Search
Andrew

Default vs Named instances

I have a server computer named 'X' and I would like to install 2 instances of SQL Server 2005

1. SQLTEST

2. SQLDEV

During installation. if I select Default instance then it will name my sql server instance X . I don't want that. I want to have essentially 2 named instances to make it clear to the users that they are using a test and dev sql server. Whats the best way to do this? I am open to suggestions if there is a different way to do this.

Is it required to have a default instance? Can I have 2 named instances X\SQLDEV and X\SQLTEST?

Also, on the SQLTEST I will be installing reporting services. By not having a default instance will it cause issues for the SSRS installation.

Please help!

Thanks.

You certainly do not need a default instance on your machine. You are fine installing two named instances side-by-side. Essentially, the default instance is merely a named instance that we call "MSSQLSERVER" by default. As for specific RS questions, you may want to search/post in their forum for side-by-side recommendations.

Thanks,
Sam Lester (MSFT)

Default Value?

Hi, I'm creating a dynamic group of values using SELECT and UNION

Example:
(SELECT Description = 'Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber
WHERE @.PhoneNumber <> @.old_PhoneNumber UNION ALL

SELECT Description = 'Changed FaxNumber from ' + @.old_FaxNumber + ' to ' + @.FaxNumber
WHERE @.FaxNumber <> @.old_FaxNumber UNION ALL

SELECT Description = 'Changed EmailAddress from ' + @.old_EmailAddress + ' to ' + @.EmailAddress
WHERE @.EmailAddress <> @.old_EmailAddress)

The problem here is that SQL Server thinks "Description" is an int (by default probably) and gives me an error when I try to assign a string to it.

I'm taking that information and using it as a field in a INSERT INTO ... SELECT statement, so I don't think I am able to use a DECLARE statement or if that would even work.

Does anyone know how I can make it so that Description is always a varchar?

Maybe?

SELECT 'Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber AS Description

You could also do this:

SELECT CAST('Changed PhoneNumber from ' + @.old_PhoneNumber + ' to ' + @.PhoneNumber AS varchar) AS Description

OR:

SELECT 'Changed PhoneNumber from ' + CAST(@.old_PhoneNumber AS varchar) + ' to ' + cast(@.PhoneNumber AS varchar) AS Description

|||

The third option worked, but I only needed to do it with the Integers. Since there were integers in the string SQL Server tried to convert the entire string into an integer across every SELECT command in the union.

So since I had an integer many SELECTs down it was telling me "can't convert name to integer" even though there was no integer in sight of that particular SELECT statement. Pretty confusing if you ask me.

Default Value of Parameter using expression - ERROR!

I am trying to use the following expression to establish the default
value for a parameter:
="SELECT DefLib FROM Users WHERE (UserName = SUBSTRING('" & User!UserID
& "', 6, LEN('" & User!UserID & "') - 5))"
The User!UserID resolves as the domain\username or "AERO\username"
When I run the report with this expression in the default value of the
parameter, I get the error:
An unexpected error occurred in Report Processing.
Input string was in the wrong format.
I can use the following query in SQL Query Analyzer to successfully
return all records (or a specific record if I substitute an actual
username).
SELECT DefLib FROM Users WHERE (UserName = SUBSTRING('Aero\' +
UserName, 6, LEN('Aero\' + UserName) - 5))
The return value DefLib is type Int... The value field for the
parameter is also an integer. Why would I receive this error if the
datatypes are the same?
TIA,
FerdBump
Ferd Biffle wrote:
> I am trying to use the following expression to establish the default
> value for a parameter:
> ="SELECT DefLib FROM Users WHERE (UserName = SUBSTRING('" & User!UserID
> & "', 6, LEN('" & User!UserID & "') - 5))"
> The User!UserID resolves as the domain\username or "AERO\username"
> When I run the report with this expression in the default value of the
> parameter, I get the error:
> An unexpected error occurred in Report Processing.
> Input string was in the wrong format.
> I can use the following query in SQL Query Analyzer to successfully
> return all records (or a specific record if I substitute an actual
> username).
> SELECT DefLib FROM Users WHERE (UserName = SUBSTRING('Aero\' +
> UserName, 6, LEN('Aero\' + UserName) - 5))
> The return value DefLib is type Int... The value field for the
> parameter is also an integer. Why would I receive this error if the
> datatypes are the same?
> TIA,
> Ferd|||Hello Ferd,
have you found a solution for this problem?
Tom

Default value for multi select parameter

Hi

I am generating this report which has ten parameters. All parameters are multi value parameter.

Now is there any way to set all parameters to "Select All" by default? What I mean is that user dont have to go each an every parameter and click 'Select All' option to view report.

Regards

If you are using a query to select the possible values you can use the same query to do a select all. Otherwise, I believe you can specify the value a an array ['value1'],['value2'],...|||

Hi Lonnie

Thanks for your reply.

I am using a dataset to select the possible values for parameter.

When you use the same dataset for default value it does not select anything.

Amit

|||Are you using RS2005 or RS2000? I know that for RS2005 that works. For RS2000 you have to create an 'All' option, then do some coding in the SQL to get it to work.|||I am using RS2005 sp1 hotfix applied (which put back in the <Select All> option that sp1 took out ). I am using queries for my parameter lists. I do not see a way to pick a default of the <Select All> option that RS adds into the list. Under default you specify the dataset and the value field (which is a drop down of the field names - not values).

Default Value for a Queried Parameter

I have created a report parameter with a query for the list of available
values. This works well and you are able to see the values and select the
one you want and see the correct report lines. When I try to assign a
not-queried value to the default value the report preview acts like there is
nothing in the default value and you have to select the value from the drop
down list. I have made the default value entered is the correct case and
spelling. Any ideas?Have you tried using both the Lable and the value for the parameter?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pl" <pl@.discussions.microsoft.com> wrote in message
news:F849BD60-1588-49D2-8727-083DDA5F7A56@.microsoft.com...
>I have created a report parameter with a query for the list of available
> values. This works well and you are able to see the values and select the
> one you want and see the correct report lines. When I try to assign a
> not-queried value to the default value the report preview acts like there
> is
> nothing in the default value and you have to select the value from the
> drop
> down list. I have made the default value entered is the correct case and
> spelling. Any ideas?

default value for a parameter to select all

Hello,

I'm using SQLServer 2005 and I build a report in the visual studio.

I need a way to set as default value for a multi-value parameter

its option of 'Select All'.

Thanks, Talia.

Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.

The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.

-- Robert

|||

Thanks.

If somebody has another idea - I'll be happy to hear...

talia.

|||

Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.

-- Robert

|||

Hey, I think I found the way:

In the Default Values of the parameter, we must choose:

From Query

then, choose the same dataSet and the same ValueField of those

of the parameter - and it select all!

Talia.

|||

Talia,

I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.

It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.

The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":

Define a dataset named "ValidStatuses" as follows:

-- begin dataset definition

DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)

INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')

SELECT * FROM @.tblValidStatuses

-- end of dataset definition

Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.

I hope this helps.

-NFox

|||

In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.

I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.

|||

In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension

Thank you very much,

Viky V

default value for a parameter to select all

Hello,

I'm using SQLServer 2005 and I build a report in the visual studio.

I need a way to set as default value for a multi-value parameter

its option of 'Select All'.

Thanks, Talia.

Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.

The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.

-- Robert

|||

Thanks.

If somebody has another idea - I'll be happy to hear...

talia.

|||

Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.

-- Robert

|||

Hey, I think I found the way:

In the Default Values of the parameter, we must choose:

From Query

then, choose the same dataSet and the same ValueField of those

of the parameter - and it select all!

Talia.

|||

Talia,

I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.

It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.

The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":

Define a dataset named "ValidStatuses" as follows:

-- begin dataset definition

DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)

INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')

SELECT * FROM @.tblValidStatuses

-- end of dataset definition

Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.

I hope this helps.

-NFox

|||

In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.

I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.

|||

In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension

Thank you very much,

Viky V

default value for a parameter to select all

Hello,

I'm using SQLServer 2005 and I build a report in the visual studio.

I need a way to set as default value for a multi-value parameter

its option of 'Select All'.

Thanks, Talia.

Sorry, you cannot pre-select the actual "Select All" entry because it is a client only UI representation item.

The closest you can get is to define the same dataset field as valid value and default value. Then all valid values should be pre-selected.

-- Robert

|||

Thanks.

If somebody has another idea - I'll be happy to hear...

talia.

|||

Well, you could implement your own frontend application that handles the parameter visualization and selection and then use e.g. the new VS 2005 ReportViewer controls (www.gotreportviewer.com) to execute and display the report.

-- Robert

|||

Hey, I think I found the way:

In the Default Values of the parameter, we must choose:

From Query

then, choose the same dataSet and the same ValueField of those

of the parameter - and it select all!

Talia.

|||

Talia,

I'm not sure I understand completely, but I'll offer a workaround and hope that it helps.

It sounds like you have a multi-select parameter with values that do *not* come from a database table. Since the "out of the box" method of defaulting a multi-select parameter to "<Select All>" is to set the default parameter source to a dataset, and you don't have a dataset, you're seeking another solution.

The workaround uses a table variable that you create and populate as part of a dataset definition. You set the Default Values of the multi-select parameter (and the values list as well, if you wish) to that table. Here's how you would do this for a parameter named "StatusCriterion":

Define a dataset named "ValidStatuses" as follows:

-- begin dataset definition

DECLARE @.tblValidStatuses TABLE(
Status VARCHAR(20)
)

INSERT @.tblValidStatuses VALUES('Future Low')
INSERT @.tblValidStatuses VALUES('Future Medium')
INSERT @.tblValidStatuses VALUES('Future High')
INSERT @.tblValidStatuses VALUES('Future Critical')
INSERT @.tblValidStatuses VALUES('Working Medium')
INSERT @.tblValidStatuses VALUES('Working High')
INSERT @.tblValidStatuses VALUES('Working Critical')

SELECT * FROM @.tblValidStatuses

-- end of dataset definition

Then in the Default Values section of the parameter dialogue (bottom of the dialogue) select the "From Query" radio button and choose "ValidStatuses" for the Dataset and "Status" for the Field.

I hope this helps.

-NFox

|||

In SP1, microsoft ruined the select all functionality. I do not know why but they did. I would not invest any time into doing this if you are going to have to redo it all after applying SP1. It is the worst software change I have ever seen.

I digress. Since, you will have to manually create the ALL section. You can just set your default to ="All" or something like that.

|||

In a report based on a Cube (Analysis Services 2005), Is it possible set "All" as default value for a parameter based on dimension

Thank you very much,

Viky V

default user for object, sysadmin role, KB22067, invalid object

Trying to select rows from a table with a particular owner
which has unfortunately been set to the sysadmin role for
sqlserver by some predecessor.
'select * from mytable' gives error message 'invalid object'
This is an inconvenience
because we have that owner name hardcoded in an unknown
number of places in the code, and many other places queries are
coded in without any owner reference.
In reference to KB255067 it gives two unrealistic workarounds
* don't set the owner in that role
(Waiting for Microsoft TimeMachine(TM) to come out)
* create all the tables with DBO owner
My colleague has invented a workaround where we set the
sysusers.sid to the user we want it to be instead of to 0x01
but I am unsure of the consequences to that.
I would like to know what a truly useable workaround is
that will not have weird consequences later on so that
SqlServer will do what I want it to do.
For example a real workaround would be
* Mark up that user so the evidence of it having been in
the sysadmin role is removed
or in the wording of the KB article
* reconstruct or fix the ownership chain (since it was broken
when the sysadmin role was used)
DO NOT MODIFY SYSTEM TABLES DIRECTLY! Yes, a "qualified" dba could "get
away" with modifying system tables, but merely changing the owner sid is
insufficient as it will not modify the sysdepends table references.
Use sp_changeobjectowner and modify the owner of the object to dbo. If the
old owner was qualified and hard-coded, then map the application login, or
another login, to an aliased user to that old owner name.
A final consideration would be to script out all of those objects, export
the data, drop the objects, and then recreate the objects under a legitimate
database user (note, not login). Then, reimport the data.
Sincerely,
Anthony Thomas

"ElvinKee" <ElvinKee@.discussions.microsoft.com> wrote in message
news:D09D8C22-AD0D-4DBD-81DC-42EACF0CC07E@.microsoft.com...
> Trying to select rows from a table with a particular owner
> which has unfortunately been set to the sysadmin role for
> sqlserver by some predecessor.
> 'select * from mytable' gives error message 'invalid object'
> This is an inconvenience
> because we have that owner name hardcoded in an unknown
> number of places in the code, and many other places queries are
> coded in without any owner reference.
> In reference to KB255067 it gives two unrealistic workarounds
> * don't set the owner in that role
> (Waiting for Microsoft TimeMachine(TM) to come out)
> * create all the tables with DBO owner
> My colleague has invented a workaround where we set the
> sysusers.sid to the user we want it to be instead of to 0x01
> but I am unsure of the consequences to that.
> I would like to know what a truly useable workaround is
> that will not have weird consequences later on so that
> SqlServer will do what I want it to do.
> For example a real workaround would be
> * Mark up that user so the evidence of it having been in
> the sysadmin role is removed
> or in the wording of the KB article
> * reconstruct or fix the ownership chain (since it was broken
> when the sysadmin role was used)
>
>
> --

default user for object, sysadmin role, KB22067, invalid object

Trying to select rows from a table with a particular owner
which has unfortunately been set to the sysadmin role for
sqlserver by some predecessor.
'select * from mytable' gives error message 'invalid object'
This is an inconvenience
because we have that owner name hardcoded in an unknown
number of places in the code, and many other places queries are
coded in without any owner reference.
In reference to KB255067 it gives two unrealistic workarounds
* don't set the owner in that role
(Waiting for Microsoft TimeMachine(TM) to come out)
* create all the tables with DBO owner
My colleague has invented a workaround where we set the
sysusers.sid to the user we want it to be instead of to 0x01
but I am unsure of the consequences to that.
I would like to know what a truly useable workaround is
that will not have weird consequences later on so that
SqlServer will do what I want it to do.
For example a real workaround would be
* Mark up that user so the evidence of it having been in
the sysadmin role is removed
or in the wording of the KB article
* reconstruct or fix the ownership chain (since it was broken
when the sysadmin role was used)DO NOT MODIFY SYSTEM TABLES DIRECTLY! Yes, a "qualified" dba could "get
away" with modifying system tables, but merely changing the owner sid is
insufficient as it will not modify the sysdepends table references.
Use sp_changeobjectowner and modify the owner of the object to dbo. If the
old owner was qualified and hard-coded, then map the application login, or
another login, to an aliased user to that old owner name.
A final consideration would be to script out all of those objects, export
the data, drop the objects, and then recreate the objects under a legitimate
database user (note, not login). Then, reimport the data.
Sincerely,
Anthony Thomas
"ElvinKee" <ElvinKee@.discussions.microsoft.com> wrote in message
news:D09D8C22-AD0D-4DBD-81DC-42EACF0CC07E@.microsoft.com...
> Trying to select rows from a table with a particular owner
> which has unfortunately been set to the sysadmin role for
> sqlserver by some predecessor.
> 'select * from mytable' gives error message 'invalid object'
> This is an inconvenience
> because we have that owner name hardcoded in an unknown
> number of places in the code, and many other places queries are
> coded in without any owner reference.
> In reference to KB255067 it gives two unrealistic workarounds
> * don't set the owner in that role
> (Waiting for Microsoft TimeMachine(TM) to come out)
> * create all the tables with DBO owner
> My colleague has invented a workaround where we set the
> sysusers.sid to the user we want it to be instead of to 0x01
> but I am unsure of the consequences to that.
> I would like to know what a truly useable workaround is
> that will not have weird consequences later on so that
> SqlServer will do what I want it to do.
> For example a real workaround would be
> * Mark up that user so the evidence of it having been in
> the sysadmin role is removed
> or in the wording of the KB article
> * reconstruct or fix the ownership chain (since it was broken
> when the sysadmin role was used)
>
>
> --

Default sort order when using SELECT with no ORDER BY clause

For SQL Server 2000 and 7.0:

What is the default sort order of a dataset when you perform a SELECT * FROM [tablename] without using the ORDER BY clause?

I've been told it is ordered by the clustered index (my testing does not bear this out) and I've been told that it is completely unpredictable (closer to what I'm seeing). I believe the latter to be true but cannot find this outlined anywhere in Microsoft's BOL, TechNet, KnowledgeBase, MSDN, etc.

Any pointers on where to find the definitive answer is appreciated.

TIA. RickIf there is clustered index - sorting by clustered index, else sorting by inserting records.

BOL says:

SQL Server 7.0 tables use one of two methods to organize their data pages:

Clustered tables are tables that have a clustered index.
The data rows are stored in order based on the clustered index key. The data pages are linked in a doubly-linked list. The index is implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values.

Heaps are tables that have no clustered index.
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.

SQL Server also supports up to 249 nonclustered indexes on each table. The nonclustered indexes have a B-tree index structure similar to the one in clustered indexes. The difference is that nonclustered indexes have no effect on the order of the data rows. Clustered tables keep their data rows in order based on the clustered index key. The collection of data pages for a heap is not affected if nonclustered indexes are defined for the table. The data pages remain in a heap unless a clustered index is defined.|||Hi ,

If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted.

If you mention ORDER BY Column name , then the dafault sort order will be ASCending if SQL Server is installed by default collation.

Ex. Select * from authors order by authorname

FYI:
--
Use the locale identified by Setup, and then choose the desired binary, case, or other options.

For the release of SQL Server 2000, when Setup detects that the computer is running the U.S. English locale, Setup automatically selects the SQL collation: Dictionary order, case-insensitive, for use with 1252 character set.

To select the equivalent Windows collation, select Collation designator, choose the Latin1_General collation designator, do not select case-sensitive, and select accent-sensitive.

For more information , see BOL - Collation|||"If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted"

if you're lucky

records can get physically stored not in insertion sequence (e.g. a row happens to be too big for the current physical database page, but the next one inserted after that isn't)

i believe records are returned in physical sequence, all things being equal

but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first

i too have heard that the sort order is unpredictable, but i too have been unable to find this on microsoft's site

a "definitive" answer will be found, of course, only on microsoft's site

rudy
http://rudy.ca/|||Hi,

I dont agree with "R937". Sorry to say that.

To his Question" but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first"

This is absolutely wrong. Thats not the concept of caching or Query Optimization.

For Example, if I excute the query as below,

Use pubs
go
/* This will return rows where job_id > 5 */
Select * from jobs where job_id > 5
go

Select * from jobs
go
AS per your statement, this has to return the jobid>5 from cache first and then job_id<4 from disk.

Thats not true.

Concept:
----
As per SQL Server, both the query are entirely different.

First it will just look in cache whether the same SQL Query is available in cache not the data. If the SQL Query is not avaiable , then it will be excuted for optimization and return the rows from disk.

Hope you agree with me.

Any constructive criticism will be appreciated.

FYI: Concepts on Query Optimization will explain well.

Have Fun :)
Varad

Default Sort column....

My query looks like this......
SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
How the rest of the data will be displayed? How exactly SQL Server
determines the sorting order columns for rest of the columns?
I am using SQL2K.
Thanks,
Smith
The results will be ordered by COL2 only. If the values are not unique, the
ordering of the remaining data is undefined. You need to specify additional
columns in your ORDER BY clause if you want other data returned in a
particular sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> My query looks like this......
> SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> How the rest of the data will be displayed? How exactly SQL Server
> determines the sorting order columns for rest of the columns?
> I am using SQL2K.
> Thanks,
> Smith
>
|||Dan,
Thanks for the reply.
1. Is it documented anywhere that remaining sort order is undefined?
2. Is it consistent while displaying the data when the order is undefined?
Thanks,
Smith
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> The results will be ordered by COL2 only. If the values are not unique,
the
> ordering of the remaining data is undefined. You need to specify
additional
> columns in your ORDER BY clause if you want other data returned in a
> particular sequence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||> 1. Is it documented anywhere that remaining sort order is undefined?
Not as far as I know. As a rule, undefined behavior is seldom documented.
It is risky to rely on undefined/undocumented behavior because this can
change without notice between versions or service packs and break your code.

> 2. Is it consistent while displaying the data when the order is undefined?
No. Once your ordering criteria is satisfied, the order of the remaining
data depends on the details of the query plan. This may vary depending on
the indexes used, number of processors, concurrent scans, etc. SQL Server
certainly doesn't add the unnecessary overhead of sequencing data without an
explicit ORDER BY unless it is needed for internal query optimization
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional
>
|||"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
Yes. In theory: Sets have no order
In SQL 2000 selects return no specific order unless you use an Order BY.
Part of this is because it may be faster for the DB to return it in an order
different from what you want. (for example SOMETIMES you can expect it to
return in the order of the clustered index, but that's simply because it can
read it off the disk faster that way.)

> 2. Is it consistent while displaying the data when the order is undefined?
No. Or rather, it's not guaranteed to be. Now, in my experience, usually
it is, but don't count on it.

> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional
>
|||With an SQL query, you specify the resultset. It is up to the RDBMS to
generate the correct result, and it can obtain this result any which way
it likes, just as long as the result is correct.
Everything you don't specify (such as the ordering of two rows with the
same value in column COL2) is by definition unspecified. This is a
property of SQL.
What SQL-Server will do is create a query plan that ensures the ordering
on COL2. The order for duplicates COL2-rows will just be in whatever
order the rows happen to be at that point. Since (in most cases) there
are many ways to achieve the same result, you cannot rely on any
particular order that you did not specify.
Gert-Jan
Smith John wrote:[vbcol=seagreen]
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional

Default Sort column....

My query looks like this......
SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
How the rest of the data will be displayed? How exactly SQL Server
determines the sorting order columns for rest of the columns?
I am using SQL2K.
Thanks,
SmithThe results will be ordered by COL2 only. If the values are not unique, the
ordering of the remaining data is undefined. You need to specify additional
columns in your ORDER BY clause if you want other data returned in a
particular sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> My query looks like this......
> SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> How the rest of the data will be displayed? How exactly SQL Server
> determines the sorting order columns for rest of the columns?
> I am using SQL2K.
> Thanks,
> Smith
>|||Dan,
Thanks for the reply.
1. Is it documented anywhere that remaining sort order is undefined?
2. Is it consistent while displaying the data when the order is undefined?
Thanks,
Smith
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> The results will be ordered by COL2 only. If the values are not unique,
the
> ordering of the remaining data is undefined. You need to specify
additional
> columns in your ORDER BY clause if you want other data returned in a
> particular sequence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > My query looks like this......
> > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > How the rest of the data will be displayed? How exactly SQL Server
> > determines the sorting order columns for rest of the columns?
> > I am using SQL2K.
> > Thanks,
> > Smith
> >
> >
>|||> 1. Is it documented anywhere that remaining sort order is undefined?
Not as far as I know. As a rule, undefined behavior is seldom documented.
It is risky to rely on undefined/undocumented behavior because this can
change without notice between versions or service packs and break your code.
> 2. Is it consistent while displaying the data when the order is undefined?
No. Once your ordering criteria is satisfied, the order of the remaining
data depends on the details of the query plan. This may vary depending on
the indexes used, number of processors, concurrent scans, etc. SQL Server
certainly doesn't add the unnecessary overhead of sequencing data without an
explicit ORDER BY unless it is needed for internal query optimization
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> The results will be ordered by COL2 only. If the values are not unique,
> the
>> ordering of the remaining data is undefined. You need to specify
> additional
>> columns in your ORDER BY clause if you want other data returned in a
>> particular sequence.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
>> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>> > My query looks like this......
>> > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
>> > How the rest of the data will be displayed? How exactly SQL Server
>> > determines the sorting order columns for rest of the columns?
>> > I am using SQL2K.
>> > Thanks,
>> > Smith
>> >
>> >
>>
>|||"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
Yes. In theory: Sets have no order
In SQL 2000 selects return no specific order unless you use an Order BY.
Part of this is because it may be faster for the DB to return it in an order
different from what you want. (for example SOMETIMES you can expect it to
return in the order of the clustered index, but that's simply because it can
read it off the disk faster that way.)
> 2. Is it consistent while displaying the data when the order is undefined?
No. Or rather, it's not guaranteed to be. Now, in my experience, usually
it is, but don't count on it.
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > The results will be ordered by COL2 only. If the values are not unique,
> the
> > ordering of the remaining data is undefined. You need to specify
> additional
> > columns in your ORDER BY clause if you want other data returned in a
> > particular sequence.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> > news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > > My query looks like this......
> > > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > > How the rest of the data will be displayed? How exactly SQL Server
> > > determines the sorting order columns for rest of the columns?
> > > I am using SQL2K.
> > > Thanks,
> > > Smith
> > >
> > >
> >
> >
>|||With an SQL query, you specify the resultset. It is up to the RDBMS to
generate the correct result, and it can obtain this result any which way
it likes, just as long as the result is correct.
Everything you don't specify (such as the ordering of two rows with the
same value in column COL2) is by definition unspecified. This is a
property of SQL.
What SQL-Server will do is create a query plan that ensures the ordering
on COL2. The order for duplicates COL2-rows will just be in whatever
order the rows happen to be at that point. Since (in most cases) there
are many ways to achieve the same result, you cannot rely on any
particular order that you did not specify.
Gert-Jan
Smith John wrote:
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > The results will be ordered by COL2 only. If the values are not unique,
> the
> > ordering of the remaining data is undefined. You need to specify
> additional
> > columns in your ORDER BY clause if you want other data returned in a
> > particular sequence.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> > news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > > My query looks like this......
> > > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > > How the rest of the data will be displayed? How exactly SQL Server
> > > determines the sorting order columns for rest of the columns?
> > > I am using SQL2K.
> > > Thanks,
> > > Smith
> > >
> > >
> >
> >