Showing posts with label function. Show all posts
Showing posts with label function. Show all posts

Wednesday, March 28, 2012

Performance help

i have the foll id's (sample 2005053110073148) generated by a function. I
need to store the data in a table. What is
the best datatype should i give to the column to enhance performance and
storage.
ThanksIt looks like the return data is of a "datetime" datatype. (Close but not
exact.)
If you can use the "datetime" datatype, use it. if not, use Char(16). This
assumes the result is always 16 bytes. Stay away from "varchar" here if the
results are always a fixed length.
"Chris" wrote:

> i have the foll id's (sample 2005053110073148) generated by a function. I
> need to store the data in a table. What is
> the best datatype should i give to the column to enhance performance and
> storage.
> Thanks|||Chris wrote:
> i have the foll id's (sample 2005053110073148) generated by a
> function. I need to store the data in a table. What is
> the best datatype should i give to the column to enhance performance
> and storage.
> Thanks
CHAR or DECIMAL. A CHAR would require 16 bytes of storage. A DECIMAL
would require 9 bytes of storage. Can you provide more detail about what
this number is.
-
David Gugick
Quest Software
www.imceda.com
www.quest.com|||It depends on whether this id has any meaning, what
the possible values of this id are, and how it will be
used in the bigger picture. Few if any questions
about database design that can be answered without any
context, and this is not one of them.
If you need to interpret all or part of it as a date/time, store that
part as a datetime (or smalldatetime if one-minute precision
is enough). If this is "just an identifier" and you will never
need to interpret anything except perhaps the ordering,
decimal(16,0) may be a good choice (or decimal(bigger#,0),
if these can be longer). If some of the positions can contain
characters other thhan 0-9, you'll need a character type.
Steve Kass
Drew University
Chris wrote:

>i have the foll id's (sample 2005053110073148) generated by a function. I
>need to store the data in a table. What is
>the best datatype should i give to the column to enhance performance and
>storage.
>Thanks
>|||Hi,
Sorry about the details. The (2005053110073148) is just an identifier. I
won't need to interpret anything. It's just there to identify a group of
record. I have a function that takes the datetime and then concatenate a
randum number at the end. It's being returned from the function as a string
so the parameter in my sp is accepting it in a output variable as
declare @.id nchar(17)
Thanks
"Chris" wrote:

> i have the foll id's (sample 2005053110073148) generated by a function. I
> need to store the data in a table. What is
> the best datatype should i give to the column to enhance performance and
> storage.
> Thanks|||I used nchar(17) because I see in BOL nchar is 4000. Is that a good choice?
"Chris" wrote:

> i have the foll id's (sample 2005053110073148) generated by a function. I
> need to store the data in a table. What is
> the best datatype should i give to the column to enhance performance and
> storage.
> Thanks|||If it's all digits, and there are never any leading zeros,
nchar(17) is a bad choice. It takes 34 bytes per value,
which is about 4 times as much storage as you probably
need. If 17 digits is the maximum possible length (this one
is 16 digits) and it will always be a string of digits with no
leading zero, decimal(17,0) is a better choice. Or you could
use bigint, which would save one more byte per value.
SK
Chris wrote:
>I used nchar(17) because I see in BOL nchar is 4000. Is that a good choice?
>"Chris" wrote:
>
>

Saturday, February 25, 2012

perform aggregate function & group by

Hello, anyone can help?
below SQL works in sybase but fail in sql2000. sql2000 show error on the sum
of netweight (cannot perform aggregate function) and group by commodity_code
(invalid column name !!)
SELECT
(select HTS
from bur_inv_item_list
where item = material
and plantcode = plant) as commodity_code,
(select DESCRIPTION
from bur_inv_item_list
where item = material
and plantcode = plant) as description,
uom as uom,
(select Orig
from bur_inv_item_list
where item = material
and plantcode = plant) as coo,
netweight =
sum(case when plant = '0014' or plant = '0024' then
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant), 2)
else
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant) /2.20462, 2)
end),
sum(ext_cost) as cost
from bur_inv_cntr_list
group by
commodity_code,
description,
uom,
coo
order by
commodity_code,
description,
uom,
coo
;The logical evaluation order of a SELECT statement is (top to bottom):
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP
As you see, the SELECT hasn't happened yet when the GROUP BY is performed. T
his mean that you cannot
refer to any column alias name in the GROUP BY clause. Some product diverts
from the ANSI SQL
standard behavior, SQL Server does not (in this regard). So push the express
ions in a derived table
and work against that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Hello, anyone can help?
>
> below SQL works in sybase but fail in sql2000. sql2000 show error on the s
um of netweight (cannot
> perform aggregate function) and group by commodity_code (invalid column na
me !!)
>
> SELECT
> (select HTS
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as commodity_code,
> (select DESCRIPTION
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as description,
> uom as uom,
> (select Orig
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as coo,
> netweight =
> sum(case when plant = '0014' or plant = '0024' then
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant), 2)
> else
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant) /2.20462, 2)
> end),
> sum(ext_cost) as cost
> from bur_inv_cntr_list
> group by
> commodity_code,
> description,
> uom,
> coo
> order by
> commodity_code,
> description,
> uom,
> coo
> ;
>|||hi tibor,
thanks for your info. do you have any sample/case. I want to see the sample
to modify below script.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
> The logical evaluation order of a SELECT statement is (top to bottom):
> FROM
> WHERE
> GROUP BY
> HAVING
> SELECT
> ORDER BY
> TOP
> As you see, the SELECT hasn't happened yet when the GROUP BY is performed.
> This mean that you cannot refer to any column alias name in the GROUP BY
> clause. Some product diverts from the ANSI SQL standard behavior, SQL
> Server does not (in this regard). So push the expressions in a derived
> table and work against that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Here's a very simple example where a derived table is used so you don't have
to repeat the DATEPART
expression:
USE pubs
SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
FROM titles
GROUP BY DATEPART(mm, pubdate)
SELECT pub_month, COUNT(*) AS titles
FROM
(
SELECT DATEPART(mm, pubdate) AS pub_month
FROM titles
) AS i
GROUP BY pub_month
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> hi tibor,
> thanks for your info. do you have any sample/case. I want to see the sampl
e to modify below
> script.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Many Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebF1yHSaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Here's a very simple example where a derived table is used so you don't
> have to repeat the DATEPART expression:
> USE pubs
> SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
> FROM titles
> GROUP BY DATEPART(mm, pubdate)
> SELECT pub_month, COUNT(*) AS titles
> FROM
> (
> SELECT DATEPART(mm, pubdate) AS pub_month
> FROM titles
> ) AS i
> GROUP BY pub_month
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>

Perform aggregate against group value

Is there a way that I can perform my aggregate function agains the value in
group row instead of details row?
Because I do not show numeric values in the details but group row.
--
SevDer
http://www.sevder.com
A new .NET Source For .NET DevelopersDid you try the Previous aggregate function? Just place it into the group
header like =Previous(Fields!Country.Value) and it should work. Note: the
previous function has only one argument.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SevDer" <sevder@.newsgroup.nospam> wrote in message
news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Is there a way that I can perform my aggregate function agains the value
> in group row instead of details row?
> Because I do not show numeric values in the details but group row.
> --
> SevDer
> http://www.sevder.com
> A new .NET Source For .NET Developers
>
>|||Hi Robert,
I tried Previous as you suggested but this time I endup with empty
datacell..
However, please excuse me that I was not clear enough previously, I want to
perform this aggregate against the group in the footer. So I tried to use
the full previous function as described in the help "Previous(Expression,
AggFunction, PreviousScope, AggScope)" but it basically fails as you've
mentioned.
Anyway, do you have a solution for me?
I would like to sum my group values in the footer.
--
SevDer
http://www.sevder.com
A new .NET Source For .NET Developers
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uzeghIq2FHA.3244@.tk2msftngp13.phx.gbl...
> Did you try the Previous aggregate function? Just place it into the group
> header like =Previous(Fields!Country.Value) and it should work. Note: the
> previous function has only one argument.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "SevDer" <sevder@.newsgroup.nospam> wrote in message
> news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Is there a way that I can perform my aggregate function agains the value
>> in group row instead of details row?
>> Because I do not show numeric values in the details but group row.
>> --
>> SevDer
>> http://www.sevder.com
>> A new .NET Source For .NET Developers
>>
>|||> I would like to sum my group values in the footer.
Maybe I'm missing something, but assuming you group on
=Fields!FieldName.Value, just adding an expression like
=Sum(Fields!FieldName.Value) in the table footer should sum the group
values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SevDer" <sevder@.newsgroup.nospam> wrote in message
news:%23rqJeUw2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Hi Robert,
> I tried Previous as you suggested but this time I endup with empty
> datacell..
> However, please excuse me that I was not clear enough previously, I want
> to perform this aggregate against the group in the footer. So I tried to
> use the full previous function as described in the help
> "Previous(Expression, AggFunction, PreviousScope, AggScope)" but it
> basically fails as you've mentioned.
> Anyway, do you have a solution for me?
> I would like to sum my group values in the footer.
> --
> SevDer
> http://www.sevder.com
> A new .NET Source For .NET Developers
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:uzeghIq2FHA.3244@.tk2msftngp13.phx.gbl...
>> Did you try the Previous aggregate function? Just place it into the group
>> header like =Previous(Fields!Country.Value) and it should work. Note: the
>> previous function has only one argument.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "SevDer" <sevder@.newsgroup.nospam> wrote in message
>> news:uEOFqzm2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>> Is there a way that I can perform my aggregate function agains the value
>> in group row instead of details row?
>> Because I do not show numeric values in the details but group row.
>> --
>> SevDer
>> http://www.sevder.com
>> A new .NET Source For .NET Developers
>>
>>
>