|
I created a temp table with an order by clause. If later on I query it, would my result set still follow the order by sort or is it going to be indeterminate like querying an actual table? This is hard to test because 999 of 1000 times I might get an ordered result set.
Thanks in advance.
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
data in temp table will remain in the state and order as its been inserted untill you make any operation(sorting,shorting etc) on it
|
|
|
|
|
are you sure? someone answered differently from another (dbforums) forum.
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
The somone else doesn't know what they are talking about.
Simple to test, create a table with 1 field, insert 2 records and then select from the records. They will ALWAYS be returned in the order they were inserted unless you order them.
Here you go
DECLARE @tbl TABLE (sField VARCHAR(20))
INSERT @tbl (sField) VALUES ('Z')
INSERT @tbl (sField) VALUES ('A')
SELECT * FROM @tbl
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The first thing to clear up is that you cannot control the insertion order of rows using ORDER BY, so the assumption on which the question is based is inaccurate. This is well documented in Books Online and elsewhere (see refs below).
If you insert rows one at a time then it is true that the insertion order determines the allocation order represented by IAM pages. It will not determine the eventual ordering of data pages however, or even necessarily the logical ordering of rows within a page, especially if page splits subsequently occur.
In a query, there is no way to specify how rows should be sorted other than by using ORDER BY. This is an important feature because it allows the optimiser to choose the most efficient path to the data based on requirements. By leaving out ORDER BY you signal to the optimiser "I don't care about order" and the optimiser chooses an execution plan accordingly. Check this for yourself by looking at the execution plan of a query without ORDER BY. You will typically see a scan showing "Ordered=False". Depending on various factors the optimiser can choose different strategies to retrieve the data for an unordered scan, which may or may not mimic the order in which rows were first inserted. Some relevant factors that influence the actual plan and sorting are: fragmentation, covering indexes and whether an "Advanced" scan is used (Enterprise Edition only).
Of course it is easy to contrive examples where the query order matches the insertion order and just as easy to make up examples where it doesn't. Just as surely, those nice safe examples could return different results if the conditions under which they ran were changed in some subtle way.
The correct and documented behaviour is that the sort order of a query without ORDER BY is undefined. If you ever assume otherwise then you are in unsupported territory and you must decide for yourself whether it is worth the possible risk of leaving out ORDER BY.
Hope this helps.
References
Insertion order not guaranteed for SELECT INTO with ORDER BY:
http://msdn.microsoft.com/en-us/library/ms188385.aspx
Insertion order not guaranteed for INSERT with ORDER BY (SQL Engine Team Blog):
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
Advanced Scan:
http://msdn.microsoft.com/en-us/library/ms191475.aspx
Ordered and Unordered Scans:
Inside Microsoft SQL Server 2005 T-SQL Querying, Chapter 3
http://www.sql.co.il/books/insidetsql2005/
Queries without ORDER BY are unordered:
http://technet.microsoft.com/en-us/library/cc917540.aspx
http://msdn.microsoft.com/en-us/library/ms187956.aspx
|
|
|
|
|
Thanks for that info - I have never actually dug under the hood of the way a db treats the rows as I also am of the opinion that I don't really care, if I need a sort order I will impose it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Microsoft do not guarantee the order any records are returned unless you put an order by on the select. The default is to return the data in the prmary key order, but this cannot be relied on.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Greetings All,
I have a particular problem that I am hoping someone with some TSQL know-how can assist me with.
Here's the issue:
I have a table that contains 40 some-odd columns. the first 2 columns are essentially identifiers. The following columns are components of the identifier. To give a clear picture, lets' say the first row has CHCK in the 1st column and Chocolate Cake in the 2nd column. The rest of the columns contains the ingredients of the Chocolate Cake (let's say 7 ingredients). The next row is for Grilled Cheese. This row would only have 3 ingredients.
Here is what I would like to achieve:
I want to run through each row and read every column to see if there is a value in it. If there is a value, I would like to put the identifier and the ingredient in another table with an insert so that this new table (with only 2 columns) looks something like this
CHCK eggs
CHCK milk
GCHEES bread
GCHEESE cheese
Is there a way that I could do this in a stored procedure? I know that I could use a cursor to the rows but each row could have a variable number of *ingredients* and that is where I am lost.
Thank you in advance for any suggestions you may offer.
|
|
|
|
|
please don't explain table data in words. Please show us that in tabular format so that we can have a clear steer view
|
|
|
|
|
STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like:
Recipie
Ingredients
Where ingredients has a 1 to many relationship with the recipie field. If you are just starting then do some reading on data structures, database design before you continue. This is a typical clusterf**k created by someone who has no idea what they are doing. Do some research and get it right, otherwise you will waste a lot of your time attempting to undo these stupid structures.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like:
Recipie
Ingredients
I think that is what he wants to do.
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
select column1,convert(varchar(20),isnull(column2,''))+''+convert(varchar(20),isnull(column3'')) as coluumn2 from tablename
use the above query and write like above for all the ingredent column .
lets me know if any things is needed.
|
|
|
|
|
sorry for the above reply
use this
select coumun1,column2 from table1
where column2 is not null
union all
select coumun1,column3 from table1
where column3 is not null
union all
select coumun1,column4 from table1
where column4 is not null
here for three column u have to write for all column
|
|
|
|
|
for sqlserver 2005
use unpivot method
|
|
|
|
|
Whoa, Whoa guys. I may not have explained myself clearly from the responses I have received.
The database tables are in the correct format as you have suggested. I need a query to pull the data in the format I mentioned to another party. I am not certain how they will be utilizing the data in this manner but that what has been spec'd out to me.
Thank you Anup for your assistance and I will further explore the command you have suggested.
|
|
|
|
|
Hi,
I'm looking for a SQL-based reporting system with the following features for running against a SQL Server 2005 database.
- SQL for each report is to be hand coded (i.e. NOT 'ad-hoc/drag-n-drop end user reporting')
- Users will select parameters at run time
- Output to browser (don't care about controlling hard-copy formatting)
- Must be able to include complete T-SQL.
- Ideally include support for transactional operations
I wrote something very much like this in my last job, but would rather buy this time.
Anyone used or even know of such a beast? I've scoured Google with no result. Everything I've found is either 'Build reports without knowing SQL' or 'use this toolkit to integrate a specific report into your application.
Thanks,
Max.
|
|
|
|
|
It's called SQL Server Reporting Services. DevExpress also do an excellent reporting tool.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi There
I need to create report that needs to group by "[B]status[/B]= Funded and Not funded" , "[B]Institute[/B]" body is title
I need to find the [B]Total amount [/B] that Institute had earned for different Research Proposals. In One Proposal there could be Group of people involved
I am creating Report using this Query
SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.Amount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
WHERE (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));
My result is some thing like this
Title Inst Type Person Role SID Amount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000
c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000
I want the total By Inst = Del = 14,00,000
and Inst= comp = 1,200,000
I have created the group by Institute, added text box at the footer of Institute and assigned = Sum([Amount]) to the control source of that text box. I am not sure Why I am not getting the result
Thank you
Bijaya
|
|
|
|
|
You are not Using Group By method.For your knowledge U should Use Group By Query.
Like : SELECT A.CustomerName, A.SUM(OrderPrice) FROM Sales as A GROUP BY A.CustomerName
Best Regard
Anubhava Dimri
mailto: anubhava.prodata@gmail.com
|
|
|
|
|
Thank you Anubhava,
This is my Group By Query :
SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Sum(Proposal.Amount) AS SumOfAmount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
GROUP BY Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.DateSubmitted
HAVING (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));
Still result remains the same
Title Inst Type Person Role SID SumOfAmount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000
c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000
At Institute footer cntains Text feild =sum(SumOfAmount) for Del= ($500,000 *3) + ($900,000 * 3) = $ 4200,000
for comp= $2400,000
Where As I want this
By Inst = Del = 14,00,000
and Inst= comp = 1,200,000
Thank you
Bijaya
|
|
|
|
|
Hi Everybody,
I am working on the proble With the first query. I am not sure why I am not getting the Sum Amount by Institute(Inst)
Just to get Total Amount by Institute. I created another Query that is:
SELECT Institutions.IName,
Sum(Proposal.Amount) AS SumOfAmount,
Proposal.sid
FROM Proposal INNER JOIN (Institutions INNER JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID
GROUP BY Institutions.IName, Proposal.sid
HAVING (((Proposal.sid)=1 Or (Proposal.sid)=2));
Datasetview of the above Query is:
Inst SumAmount SID(status)
Del 14,00,000 funded
comp 12,00,000 not-funded
But when I assign this to Combo box at the Footer of Institute Group. I am not getting the accepcted result. I am getting this one.
Del = 500,000
Comp = 1200,000
Could onyone help me why this is happening?
I am counting on you, my project is due comming Monday. help Needed!
thank you
Bijaya 
|
|
|
|
|
I'm new to crystal report and currently is using CR Ver. 10 to develop my project. I would like to know how can i make my report to display data from db in horizontal with maximum of 3 rows of data per page.
My sql command look something as below:
select personid, personname, personfname, personlname, personmname
from person_tab
It should display as template below:
PERSON REPORT
ID | ID 1 | ID 2 | ID 3
NAME | NAME 1 | Name 2 | Name 3
FIRST NAME | First Name 1 | First Name 2 | First Name 3
LAST NAME | Last Name 1 | Last Name 2 | Last Name 3
MID NAME | Mid Name 1 | Mid Name 2 | Mid Name 3
|
|
|
|
|
you should used cross tab report type . it will available when u will add the report in own solution explorer.
lets me know if any things is needed.
|
|
|
|
|
Hi Experts
I facing a great Problem Pls Help.
I Have Database in SQL Server 2005.i giving it to My Client end.
i Want To Protect My Database Not To Open or Modified By Another User.
How To Make Certificate of Database and Use It at Client End So Another Person Not To Open My Database
|
|
|
|
|
I don't think that you can sign a SQL-database the way you sign a Word-document. A database is meant to be changed, if you want to restrict access then dive into permissions, users, passwords, policies and logons 
I are troll
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
