[Stored Procedures] How do I calculate 3^1.2?

Hello,

I am working with SQL Server 2005 stored procedures.

How do I do this:
SET @.MyAnswer = 3^1.2
--i.e. 3 to the 1.2 power

Currently it will not allow it because"The data types int and numeric are incompatible in the boolean XOR operator."

~Le

I think you must use FLOAT to use all or most of the mathematical functions and cast the result to Numeric. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms177516.aspx

|||

Caddre:

I think you must use FLOAT to use all or most of the mathematical function and cast the result to Numeric. Hope this helps.

http://msdn2.microsoft.com/en-US/library/ms177516.aspx

Excellent! That put me on the right path.

The correct function is:POWER(x,y)

and the key wasx.

Power (1.0000, 1) will return 1.0000
Power (1.00, 1) will return 1.00

Thanks!

`Le

[SSIS] : STDEV in Derived Columns

Hello,

Does anyone have already tried to calculate a standard deviation (STDEV) in a derived column ?

Any help is welcome ;-)

Cheers,

Bertrand

Bertrandr wrote:

Hello,

Does anyone have already tried to calculate a standard deviation (STDEV) in a derived column ?

Any help is welcome ;-)

Cheers,

Bertrand

Have you looked into the documentation?

http://msdn2.microsoft.com/en-us/library/ms141671.aspx

it looks to me thta function is not available...you may want to do that in the DB or create a script component.

|||

Haven't calculated standard deviation in a derived column, but rather in an Async script component (i.e. Synchronous Input Id = 0 on the first output). The data being passed in to this transform is DT_CY data type (currency) called TotalDue. The data passed out is a DT_R8 on the output called StdDev.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private rowCount As Integer = 0

Private columnSum As Double = 0

Private columnSumOfSquares As Double = 0

Public Overrides Sub InputMain_ProcessInput(ByVal Buffer As InputMainBuffer)

While Buffer.NextRow()

InputMain_ProcessInputRow(Buffer)

End While

If Buffer.EndOfRowset Then

With OutputMainBuffer

.AddRow()

.StdDev = StdDev(rowCount, columnSum, columnSumOfSquares)

End With

OutputMainBuffer.SetEndOfRowset()

End If

End Sub

Public Overrides Sub InputMain_ProcessInputRow(ByVal Row As InputMainBuffer)

rowCount = rowCount + 1

columnSum = columnSum + Row.TotalDue

columnSumOfSquares = columnSumOfSquares + Math.Pow(Row.TotalDue, 2)

End Sub

Private Function StdDev(ByVal Rows As Integer, ByVal sum As Double, ByVal sumOfSquares As Double) As Double

Dim topSumVal As Double = (rowCount * columnSumOfSquares) - (Math.Pow(columnSum, 2))

Return Math.Sqrt(topSumVal / (rowCount * (rowCount - 1)))

End Function

End Class

|||Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253840

Thanks,
Phil|||

Phil Brammer wrote:

Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=253840

Thanks,
Phil

Phil,

Are you sure that's the right link? it takes me to 'Page Not Found'.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Bertrand,
There has been a feature suggestion posted for SSIS with respect to statistics. Please visit the link and vote. Please add your comments as well.

https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=253840

Thanks,
Phil

Phil,

Are you sure that's the right link? it takes me to 'Page Not Found'.

-Jamie

Good catch... When I visited the link, it redirected to a URL without "SQLServer" in the path... The link has been updated in my original post...

@table VS #table

WHIch one is the best to use in my SP's.I have lots SPs which are meant to calculate huge data,so in those SPs right now i am using lots of #tables and i found that my server performance is effected by those #tables. So i wanted to use @.tables henceforth.
Please guide me in this issue to choose the best one...

Thanks.How much data are you storing in the temp tables?

Temp tables will use tempdb

table variables will use memory...

both use up resources...

I believe BOL recommends table variables...

What are you doing?

Would it make sense to use physical staging tables?|||maximum of 300 rows and each row having 10 columns and I run most of the SPs for every ten mins as a job.

Thats the whole Picture.

Thanks.|||With a 300 by 10 chunk of data, you aren't talking enough to get excited about either way. Table variables (@.table) will give you faster scans, and temp tables (#table) will give you faster seeks. Most boxes I've seen have more disk than RAM, but either way your load isn't big enough to matter.

I'm just curious, what makes you think that the #tables are affecting your server performance? Even if there were a couple dozen of them, they shouldn't be noticable. A few thousand would have some impact, but I doubt that you've got anywhere near that volume.

-PatP|||Another consideration is that you can put as many indexes as you want on a temporary table to increase JOIN efficiency, but on a table variable you can only have one index.

I generally use table variables unless the dataset is going to be accessed multiple times on a non-indexed column.|||another day, learnt another thing about sql server feature.
thank you guys.

:)

ps: i am always excited about new things that i learn.|||Originally posted by bruce_Reid
WHIch one is the best to use in my SP's.I have lots SPs which are meant to calculate huge data,so in those SPs right now i am using lots of #tables and i found that my server performance is effected by those #tables. So i wanted to use @.tables henceforth.
Please guide me in this issue to choose the best one...

Thanks.

It will vary by the system also.

In our current system (we have our RAM max'd out though), we cut it off at 10k records as a general rule. If we join the table to a lot of tables later on in the procedure though, we will use temp tables to take advantage of the indexes. You need to find out what's good for your system.

BTW, if you do a trace, you will find that table variables create a space in tempdb and use that. It will be something like #12@.1aadfa0.

The difference is how the memory space is used vs. the ability to use indexes.|||With a table variable (never used one, But was about to use it to implement some sorta virtual queue for each user of a multi user environment),

1. is it possible to define an identity column? (tried it without success)

2. is it possible to select a subset of a table into the table variable? i.e. with out using an insert statement, but rather something more like
SET @.table_name = (SELECT col1, col2, col3 FROM TABLE1)
I tried this without success.

James :)|||Originally posted by nano_electronix
With a table variable (never used one, But was about to use it to implement some sorta virtual queue for each user of a multi user environment),

1. is it possible to define an identity column? (tried it without success)

2. is it possible to select a subset of a table into the table variable? i.e. with out using an insert statement, but rather something more like
SET @.table_name = (SELECT col1, col2, col3 FROM TABLE1)
I tried this without success.

James :) 1. Yes, you can, as in:DECLARE @.t TABLE (
id INT IDENTITY
, v VARCHAR(20) NOT NULL
)

INSERT INTO @.t (v) VALUES ('One')
INSERT INTO @.t (v) VALUES ('Two')
INSERT INTO @.t (v) VALUES ('Three')

SELECT * FROM @.t

2. Not as you've shown it, but you can sneak up on it using the INSERT INTO syntax.

-PatP|||Indexing temp tables? It'll be a wash because of the possibility of recompiles and locking of sysobjects, sysindexes, and syscolumns in tempdb. I've never indexed a temp table because I've never had them big enough to bother. If it turns out to be of significant size, maybe using temp table was not the best choice? Percentage of table scan on it should be minimal compared to overall cost of the query, and THIS should be the main determinant whether to use temp table or not. And as far as a difference between # vs. @., - I'd use # only if I want to store a result of an " insert #tmp execute sp", otherwise - it's definitely @..|||I've gotten performance boosts from indexing table variables and temp tables, but only if they are referenced multiple times in a procedure. I normally use table variables now, but will revert to temp tables when I need additional indexes that can't be added to a table variable.|||We're the same way blindman. We do have some batch processes that run at night though where we have received huge performance gains from indexed temp tables. Usually they have 75k-700k records though and are joined several times.

The performance increase on anything over several thousand can be mind-boggling by just building an index on the temp table.|||Originally posted by Pat Phelan
1. Yes, you can, as in:DECLARE @.t TABLE (
id INT IDENTITY
, v VARCHAR(20) NOT NULL
)

INSERT INTO @.t (v) VALUES ('One')
INSERT INTO @.t (v) VALUES ('Two')
INSERT INTO @.t (v) VALUES ('Three')

SELECT * FROM @.t

2. Not as you've shown it, but you can sneak up on it using the INSERT INTO syntax.

-PatP

Cheers

For answer to question 2, did u mean something like ...

INSERT INTO myTable99(Col1)
SELECT '1~2~3~4~5' UNION ALL
SELECT '1~2~3~4~5' UNION ALL
SELECT '1,2~3,4,5'

I pulled it above off another thread. hehehe.

James :)|||I think the question was, can you use SELECT INTO to create a table variable the same way you can create other tables on the fly, and the answer is no. Table variables must be explicitly defined, though I'm not sure what Pat meant by "sneaking up on it". Pat?|||Is this possible?

DECLARE @.t TABLE (
col1 VARCHAR(20),
col2 VARCHAR(20),
col3 VARCHAR(20)
)

INSERT INTO @.t
(col1, col2, col3)
values
(SELECT col1, col2, col3 FROM src_table)

sorry i have to ask instead of trying it out myself, main reason is because i had a fresh install which i want to ghost before i do anything to it and i don't want to import any sample database into the freshly installed database.

I guess the question i am asking about is -
"Is it possible to do multiple insert like how i wrote the insert query above?"

cheers

James :)|||I don't think so. You can write it like this instead:

INSERT INTO @.t
(col1, col2, col3)
SELECT col1, col2, col3
FROM src_table

and that should run just fine.|||Thank you that's the answer i needed.

I would imagine that's not standard ANSI either?

James :)|||ummmm, that's about as standard as it gets.|||Originally posted by derrickleggett
ummmm, that's about as standard as it gets. The tabel variable is 100% Microsoft extension, the INSERT INTO...SELECT syntax is ISO/ANSI standard.

In response to blindman's question, that is exactly what I meant by "sneaking up" on a table assignment, using the INSERT INTO syntax.

-PatP|||Originally posted by Pat Phelan
The tabel variable is 100% Microsoft extension, the INSERT INTO...SELECT syntax is ISO/ANSI standard.

In response to blindman's question, that is exactly what I meant by "sneaking up" on a table assignment, using the INSERT INTO syntax.

-PatP

The SQL ANSI-92 standard has to do with usage and syntax. The statement is as standard as it gets.