|
newid() doesn't actually generate random numbers, it generates GUIDs, which have a random part to them, and serve their purpose well for random ordering.
NEWID()
--------
- NEWID() generates GUID's instead of random numbers which is of type in a uniqueidentifier.
Try this:
SELECT NEWID() AS 'NEWID'
Output:
NEWID
-----
259ED1EE-AE35-41D2-9FCA-9FA9538AE5FF
- NEWID is guaranteed to be unique
RAND()
------
- Returns float value between 0 to 1.
Try this:
SELECT RAND() AS 'RANDOM NUMBER'
Output:
RANDOM NUMBER
------------
0.0431138996610296
- RAND() is not guaranteed to be unique
- SQL Server estimation plan says, sorting with NewId() is more time
consuming than Rand(). Rand() number generation is based on time factor
If we sort records with Rand(),the records will get same rand number and
result will be the same as previous.
e.g.
SELECT RAND() RANDOMCOLUMNS,COL1
FROM MYTABLE
ORDER BY RANDOMCOLUMNS
Output:
RANDOMCOLUMNS COL1
--------------------------------------------------------
0.227066653916067 1
0.227066653916067 2
0.227066653916067 3
0.227066653916067 4
As you can make out that the "RANDOMCOLUMNS" values are same through out.
Because random generation factor does not change during record selection.
Whereas, the same query with NEWID() will give the following
RANDOMCOLUMNS COL1
--------------------------------------------------------
B953F9A9-BE22-4651-8669-5CC6A5269911 4
50293D75-4139-4A75-883F-81DAA91ED4E6 1
0A9B1D56-68FD-4060-AA6B-89791DAF8924 3
4C3A2765-4EA2-493B-8AA9-EBA414D7AAC2 2
As you can make out that, unique GUID's has been created in this case.
So in a simple query, doing a simple "ORDER BY RAND()" will not randomly
order the results at all.In this context,NEWID() is much better, though
not more efficient.
For more information on "Generate random sets in SQL Server with NewID()", you can go through this tutorial
http://articles.techrepublic.com.com/5100-10878_11-6089823.html#[^] by by Arthur Fuller.
More on RAND()
http://msdn.microsoft.com/en-us/library/ms177610.aspx[^]
and NEWID()
1)
http://msdn.microsoft.com/en-us/library/aa276822(SQL.80).aspx[^]
2)http://www.sqlmag.com/Article/ArticleID/97032/sql_server_97032.html[^]
3)http://www.sqlmag.com/Article/ArticleID/99807/sql_server_99807.html[^]
N.B.~RAND() sorts on the constant (per SELECT)
NEWID() sorts on a dynamic (per row)
That is why if you look carefully, the COL1 values has been changed in
the 2nd case while it is same in the first.
For information about how are GUIDs sorted by SQL Server, look here
http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx[^]
Hope this helps.
Niladri Biswas
modified on Sunday, July 5, 2009 8:04 AM
|
|
|
|
|
I need to execute the stored procedure recursively..but i am getting an error there is limit of 32 to execute it repeatedly.... how to handle this problem...Any solution
|
|
|
|
|
It becomes a design issue, 32 is the depth of the proc stack, you need to redesign the way you are processing the data, possibly move it to the client or change the way the proc is called.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why in the world would you switch from an iterative version to a recursive one?
So not only are you incurring more memory overhead, but let's not forget you are running this *IN* SQL SERVER
Try not to use recursive function
Anyway, could you please send the stored proc so that we can have a look on that and tell you a suggestive approach!
Regards,
Niladri Biswas
Niladri Biswas
|
|
|
|
|
You replied to my reply - you may want to move this to the OP so as to get/give some feedback
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ello everyone.
I have an application that communicate with a database in sql server 2005, sometimes I have sql query that fails knowing that they are simple requests for updates. I give an example
I have a person table with a column name
I happen to change the names properly, may at one time given a specific name, a clear line in my table is blocked.
when looking around the net I have found that piece is a lock problem.
because once I restart my application everything works well with this line, but I still fall on another line that is blocked.
the query is:
[Code] SELECT
*
FROM
master.dbo.sysprocesses processlist
INNER JOIN master.dbo.sysdatabases DATABASELIST
ON PROCESSLIST.dbid = DATABASELIST.dbid
WHERE
(DATABASELIST.name = N'VISION_DB ')
[/ CODE]
lastwaitintype=LCK_M_X or LCK_M_X
I started with sql server and I do not know how to not have to lock every time
|
|
|
|
|
First of all I am not a database developer and therefore I may well have misunderstood your question.
However:
Morad SAJID wrote: I have a person table with a column name
what is the name of the database containing the person table?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Maybe try defining an explicit transaction before you do your updates and use a Try ... Catch block for error handling. If the update fails then you can execute a rollback, if the update succees, then execute a commit. By using explicit transactions, you should be more confident that locks are not staying around unwanted.
Database updates require locks to be performed, this is unavoidable. Give it a try. 
|
|
|
|
|
hello
i am using SQL server 2005 as my back end
now i want report of like from this date to this date...for that i wrote query like
format of my date is dd/MMM/yyyy
If optn1.Checked = True Or optn3.Checked = True Then
sqlstr="select * from des_mast"
sqlstr = sqlstr & "where pickup_date between '" & Me.dtp1.Value & "' and '" & Me.dtp2.Value & "'"
End If
but while executing this query shows an error message like
"incorrect syntax near the keyword 'between'"
please suggest i cant get where the problem is
thank you
|
|
|
|
|
Try to debug and follow debugger line and then you will find the problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
nazimghori wrote: format of my date is dd/MMM/yyyy
The format of your sql or the format of your datetimepicker?
nazimghori wrote: If optn1.Checked = True Or optn3.Checked = True Then
You dont need to compare a booleanvalue with true or false
Just do it like this:
If optn1.Checked Or optn3.Checked Then
Or even better:
If optn1.Checked OrElse optn3.Checked Then
nazimghori wrote: sqlstr="select * from des_mast"
sqlstr = sqlstr & "where pickup_date between '" & Me.dtp1.Value & "' and '" & Me.dtp2.Value & "'"
After concatenating this, i dont think youll get a space between des_mast and where, youll get des_mastwhere
Also, have you tryed using # for the date values: between #12/12/2009# and #12/12/2009#
If you still cant get it to work, you can test manually the query in sql server managment studio
Alexei Rodriguez
|
|
|
|
|
Hi to all,
Here is a problem I am facing in a table design in ms access.I am accessing the database through vb.net application.
All I need to get an efficient way to enter the marks for each students for each subject.Now, my number of subjects may increase or decrease.
Can I do this by adding or deleting the columns in the table(student which will contais all the details of the students with subjects as its column). If so please provide me the syntax for altering a table(adding /deleting columns)
Please advice me, if any other efficient method exist for the same.
Thanks in advance.
HimangshuS
-----------------------------
I am a beginner
|
|
|
|
|
Instead of altering the table that contains the student and his scores per class, create 2 tables. Students and StudentScores
The Student table could look something like this:
StudentID (primary key)<br />
Name<br />
GraduatingYear<br />
etc
The Course table would be:
CourseID (primary key)<br />
CourseName
The StudentScores table would look something like this:
StudentID (combination of StudentID,CourseID and CourseDate would be the primary key)<br />
CourseID<br />
CourseDate <br />
Score
By introducing a CourseDate, the student could take the course multiple times and have different grade for each time it was taken.
Also, this design allows for an unlimited number of Courses and each Student could take any number of courses offered.
This design is by no means complete, but it should give you something to think about.
Good luck with your project.
David
|
|
|
|
|
thanks a lot David,
I think thats the solution...Thanks a lot.
But what do you mean by
"This design is by no means complete"
Scared of that..
Please do reply for that. Since I need to create those table..
regards,
HimangshuS
-----------------------------
I am a beginner
|
|
|
|
|
You may want to inlcude more information in the tables than I have given, for example in the Student Table you could have the parent's name and contact numbers. In the StudentScores table you may want to store multiple scores, quizes, tests, mid-terms, final, etc. I don't know exactly what you need, but I'm sure you can think of ways to expand this basic design.
|
|
|
|
|
Hi,
I just wanted to know that is it possible to retrieve data without rollback or declaring any transaction point in sql 2005.
Rock Star
|
|
|
|
|
Yes.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
So how can I solve this issue.
It will be great if you can guide me for it.
Thank you!
Rock Star
|
|
|
|
|
The answer is to not declare any transaction, just do the select.
For example: select ID,FullName from Customer
This will retrieve the data and not create any implicit or explicit transactions. No need to commit or rollback anything.
|
|
|
|
|
Thanx for reply
My question here is that consider I excuted an update query on a table without defining any transaction point, but now I want to roll back this update query so I can recover my data. How can I do this?
Thank u!
Rock Star
|
|
|
|
|
You need to do some work on your question construction b/c thats not what anyone would have though the OP was about.
If you are talking about recovering from an error in the statement then a transaction is required, if there was not transaction you are gone.
If you want to recover from an incorrect update you may be able to restore the database from a backup or possibly by working the transaction logs. Restore the DB to another SQL instance and compare the data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have 3 tables.
1. A contact table
2. A company table
3. A company to contact association table.
If a company to contact association is created, a field, 'CompanyName', in the contact table needs to contain the name of that company (in order for our mailing lists to print correctly).
I've been trying to write a query which will fill in all of the contact table 'CompanyName' values, with all of the corresponding values in the company table.
The company to contact association table is the link between the two so:
1. I need to determine which contact's have an association with a company
2. I need to get the companyID from the association table, and get the company name
3. I need to set the contact company name to the retrieved value.
Only problem is... IM STUCK!!!
This is what i've done so far.
<br />
<br />
SELECT Contacts.CompanyName<br />
FROM MarkSandpit.dbo.Contacts<br />
INNER JOIN<br />
MarkSandpit.dbo.Associations_Companies_to_Contacts<br />
ON Contacts.mxContactID = Associations_Companies_to_Contacts.mxContactID<br />
INNER JOIN mxContact.dbo.Companies<br />
ON Associations_Companies_to_Contacts.mxCompanyID = Companies.mxCompanyID<br />
<br />
This just gives me a list of all the company names in the contact table, which have associations with a company.
Could some please help me out?
Cheers,
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
HI GUYS
I have installed sqlserver 2005 exp with advanced services and toolkit , but i cant see
report server ,
can any body tell me how to install a report server ?
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
|
Hi, thanks for looking
Im trying to make a query that will show me something like a report
I have a Homes table and a History table
Table Homes: Info about homes
Col: HomeID
Col: AddressID
Col: ...
Table History: A record for every status in wich a home has been
Col: HistoryID
Col: HomeID
Col: StatusID
Col: DateStarted
Col: DateFinished
Col: ...
A home can have up to 4 rows in the History (Status 1 - Status 4), what i need to do is a query that return something like this:
HomeID - Status1 - Status2 - Status3 - Status4
1 - 10/10/09 - 10/12/09 - 10/14/09 - 10/18/09
2 - ................
Here, im showing a home and the DateFinished column from the History table
In case that a home doesnt have statusX, null is ok
I was trying to create this view with the view designer, first i draged the Homes and History and made the query by filtering history with status1, so i got homeid and status1, then i had to redrag history with an alias and filter status2 to get status2 and so on
It seems really unefficient this way
Please let me know if you know another way to do it
Thanks in advance
Alexei Rodriguez
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
