Difference between STDEV & STDEVP

Can somebody please compare these 2 functions for me and explain with an example?

Thank You

You can find detailed description here (the second article has an example):

http://office.microsoft.com/en-us/assistance/HP010322691033.aspx

http://www.beyondtechnology.com/tips016.shtml

Quote:

STDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased."
STDEVP is used when the group of numbers being evaluated is complete - it's the entire population of values. In this case, the 1 is NOT subtracted and the denominator for dividing the sum of squared deviations is simply N itself, the number of observations ( a count of items in the data set ). Technically, this is referred to as "biased." Remembering that the P in STDEVP stands for "population" may be helpful. Since the data set is not a mere sample, but constituted of ALL the actual values, this standard deviation function can return a more precise result.

Difference between SP and function

Hi All,

It may be sound weird. I want to find the difference between SQL Stored procedure and functions. I knew a couple one is for function, parameter is must where as in SP its not, second is function would return a value whereas procedure wont.

Is there anythign else?That was it! You are on the right track :)|||>> second is function would return a value whereas procedure wont.

not necessarily. even a stored proc can return values ( see OUTPUT PArameters).

Function can return only ONE value where as a stored proc can return multiple values.
performance wise there isnt any diff.

I remember having googled about this once and did come across a couple of articles that xtensively described the differences..so I would say google and you can find more definitive answers.

hth|||I guess "parameter is must where as in SP " he ment for OUTPUT param!|||one use of a function is to use it to return a table object that can be used in another sql statement|||Hi

Thanks you all for your answers. But in the recent inteview which i attended they asked for one more difference between these two apart from those i specified earlier.|||I'm sure you could Google and find all of the information you need.

The main thing about UDFs is that they need to be deterministic -- that is, the same input parameters will always return the same result. So therefore you cannot, for example, directly use GETDATE() in your UDF. Another biggie is that you cannot use either @.@.ERROR or RAISERROR. And another biggie is that dynamic SQL cannot be executed.

I've gotten burnt by all of the above, and others. Some have workarounds, some do not.

Terri

Difference b/w SPs & Functions

1. cannot hv tran in functions
2. cannot include SPs in select queries or in from clause whereas functions
can be included. (exception using OPENQUERY for including SP in from clause)
What other differences?Hi Rakesh
Difference between Stored Procedure and UDF:
Returning Parameters:
UDF Should return a parameter
Stored Procedure need not return any value
UDF Returns only one parameter
Stored Procedures can return more than one parameters
UDF can return a table
Stored Procedure returns a resultset
Usage
UDFs can be used a inline functions and can also be executed explicitly
eg: inline function: SELECT getdate()
Stored Procedures cannot be used as inline functions
Hope this helps you.
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rakesh" wrote:

> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas function
s
> can be included. (exception using OPENQUERY for including SP in from claus
e)
>
> What other differences?|||You cannot use INSERT/UPDATE/DELETE on base tables.
You cannot Use NonDeterministic functions inside UDF
A scalar valued user defined function can be used Anywhere a value is
expected, as in WHERE clause, check contraint etc.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||Rakesh
Visit at http://www.sql-server-performance.com/
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DDCFBA3-C85D-47A4-A2CE-CDC22EEA364F@.microsoft.com...
> 1. cannot hv tran in functions
> 2. cannot include SPs in select queries or in from clause whereas
> functions
> can be included. (exception using OPENQUERY for including SP in from
> clause)
>
> What other differences?|||"Chandra" wrote in message

> Difference between Stored Procedure and UDF:
> Returning Parameters:
> UDF Should return a parameter
> Stored Procedure need not return any value
Stored Procedures RETURN a value, even if you dont explicitly specify (0 by
default)

> UDF Returns only one parameter
> Stored Procedures can return more than one parameters
Stored Procedure can RETURN one one value and that must be integer.
You can use Output Parameters to get other datatypes back from the SP

> UDF can return a table
> Stored Procedure returns a resultset
Stored Procs can return multiple recordsets, UDF can return only one (table
valued udfs)

> UDFs can be used a inline functions and can also be executed explicitly
> eg: inline function: SELECT getdate()
What you mean by inline?

> Stored Procedures cannot be used as inline functions
EXEC yourvar= ProcName <ParamList>
Just plain Nitpicking :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

Diff between rank & desnse_rank()

Hi ,
Just confused between the Yukon ranking functions
rank & dense_rank
Thanks
ARRSay you have following
Anderson
Anderson
Smith
Xing
Rank over these will give you 1, 1, 3, 4
Dense_rank will give you 1,1,2,3
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Aju" <ajuonline@.yahoo.com> wrote in message news:%23bE3v$$FFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Hi ,
> Just confused between the Yukon ranking functions
> rank & dense_rank
> Thanks
> ARR
>

Diagnose SQL Procedures.

Hi there,
Is there any way I can create one user defined stored procedure that will
let me know all the stored procedure and functions in current database have
compiled successfully, there is no compilation error in the stored
procedures ?
Basically, I have a database and there are around 90 stored procedure and I
want to check all the stored procedures compiled successfully or still they
need to compiled so I need any query or stored procedure through which I can
diagnose this...
Thanks in advance
What exactly do you mean by compile? Sp's only get compiled the first time
they are run. They get parsed and checked when you create them. There really
isn't anything such as an unsuccessful compilation.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:%23VA1H30MGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Is there any way I can create one user defined stored procedure that will
> let me know all the stored procedure and functions in current database
> have compiled successfully, there is no compilation error in the stored
> procedures ?
> Basically, I have a database and there are around 90 stored procedure and
> I want to check all the stored procedures compiled successfully or still
> they need to compiled so I need any query or stored procedure through
> which I can diagnose this...
> Thanks in advance
>
|||Andrew J. Kelly wrote:
> What exactly do you mean by compile? Sp's only get compiled the
> first time they are run. They get parsed and checked when you create
> them. There really isn't anything such as an unsuccessful compilation.
Maybe he wants to know how to verify existing procedures against a
schema that has changed. If that's the case, there's some information
here that might help:
http://groups.google.com/group/micro...eb685e696368c1
David Gugick - SQL Server MVP
Quest Software

Developer Environment vs. Live Server

I am experiencing a situation where certain functions work perfectly when I run it on my local machine under MVS, but when I upload it to the server, then it does not?!

Here is an example:

I am using a drop down box (in a FormView) that is databound in an online submission form. When I run the application in MVS, then I can edit the records by opening the form, and selection the new value in the drop down list. The new value is then also saved to the database when I hit the update button. When I upload the code to the server, then the drop down list shows the correct information (so the databinding to the control seem to work correctly), but the new value is not saved to the database.

Here is the code for the drop down list:

"DropDownList1" runat="server" DataSourceID="SqlDataSource3"
DataTextField="UserName" DataValueField="UserId" SelectedValue='<%# Bind("UserId") %>'
CssClass="text">
"SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"
SelectCommand="SELECT [UserName], [UserId] FROM [vw_aspnet_Users] ORDER BY [UserName]">

Here is the code updating the database with the record (I have removed some records as well as the Insert and Delete parts):

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tourism_connect1 %>"
UpdateCommand="UPDATE Resorts SET typeid = @.typeid, destrictid = @.destrictid, UserId = @.UserId, WHERE (resortid = @.resortid)"
OnInserted="SqlDataSource1_Inserted">

<UpdateParameters>
<asp:Parameter Name="typeid" />
<asp:Parameter Name="destrictid" />
<asp:Parameter Name="UserId" />
<asp:Parameter Name="resortid" />
</UpdateParameters>
</asp:SqlDataSource>

What I can mention further, is that if I connect to the database that is on the live server (so the application runs in MVS on my local machine, but it then retrieves the info from the online database), then it also works fine. It is just giving this issue when the online application is trying to update the values.

There is also no errors during the process.

I will appreciate any advise on how to overcome this, as I really do not know where to look anymore...

Thanks in advance!

Regards

Jan

Have you checked logs? My first guess is a permission error, but it's a guess at this point with the given information.

Jeff

|||

Hi Jeff,

I had a look at all possible permission settings, and I just could not find anything! Unfortunately my service provider can not give me access to the logs at this time, so since everything else failed, I decided to redo the whole lot. The good news is that I got it working, and the problem seem to have been due to date formatting.

I have a Text Box that I update in the Page_Load event with the current date when the record is edited. The code looked like this:

TextBox varLastRevised2 = ((TextBox)(FormView1.FindControl("LastRevisedTextBox")));
varLastRevised2.Text = DateTime.Now.ToLongDateString();

For some reason, when this value needed to be placed back into the database, then the format of the date was not recognised. So I have changed the code to the following:

TextBox varLastRevised2 = ((TextBox)(FormView1.FindControl("LastRevisedTextBox")));
varLastRevised2.Text = DateTime.Now.ToString();

With this, it seem to be working.

The strange part is that I would think that there would be some sort of error if the date format was giving an error, but instead it just did not do anything. I am sure that the logs might have contained some info about this, but this is something I'll have to clear up.

Thanks for your advise in any case.

Best Regards
Jan

|||

This is maybe beacuse the database objects (e.g. tables, stored procedures,..etc) is created on the MVS with a user (say UserA), and now in the live server you are using another user (say UserB) who has the right permission to SELECT data (for example), but he can not see the table. Why? because it might be located in a schema that "UserB" can not access to it.

Make sure that the database user in the live server can see tables in the schema where you put the tables you got from development.

Good luck.