This is my first sql server app so I'm pretty ignorant.
In my production environment I have a server with 4
processors and 4 drives and is part of a SANS network. 2
drives are mirrorred to be c: and two drives are mirrorred
to be d:, both RAID1. I also have another drive (T:)
available for my app. I have my datafiles for this app on
the t: drive which is RAID5 and I have my log files on
drive d:. Is this the recommended practice. I know that
I read that I need to separate my data files and log
files. Please advise.
Thanks,
EdieThat seems like a reasonable setup. To be able to give more precise
suggestions, we'd need I/O metrics for the current config. I suggest you
keep it like it is unless you have performance problems and are willing to
spend some time on the issue.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Edie Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
> This is my first sql server app so I'm pretty ignorant.
> In my production environment I have a server with 4
> processors and 4 drives and is part of a SANS network. 2
> drives are mirrorred to be c: and two drives are mirrorred
> to be d:, both RAID1. I also have another drive (T:)
> available for my app. I have my datafiles for this app on
> the t: drive which is RAID5 and I have my log files on
> drive d:. Is this the recommended practice. I know that
> I read that I need to separate my data files and log
> files. Please advise.
> Thanks,
> Edie|||Thanks. If I were to add more databases to that instances
of sql server, would it be a good practice to put all my
log files on that one D Drive?
>--Original Message--
>That seems like a reasonable setup. To be able to give
more precise
>suggestions, we'd need I/O metrics for the current
config. I suggest you
>keep it like it is unless you have performance problems
and are willing to
>spend some time on the issue.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Edie Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
>> This is my first sql server app so I'm pretty ignorant.
>> In my production environment I have a server with 4
>> processors and 4 drives and is part of a SANS network.
2
>> drives are mirrorred to be c: and two drives are
mirrorred
>> to be d:, both RAID1. I also have another drive (T:)
>> available for my app. I have my datafiles for this app
on
>> the t: drive which is RAID5 and I have my log files on
>> drive d:. Is this the recommended practice. I know
that
>> I read that I need to separate my data files and log
>> files. Please advise.
>> Thanks,
>> Edie
>
>.
>|||Yes, I think so. Ideally, each database should have it's on drive for the
log, but sometimes that is not practical. Isolating other stuff from the
drive where you have a number of databases log files is still better than
mix 'n match, IMO.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:000301c3c011$770047f0$a501280a@.phx.gbl...
> Thanks. If I were to add more databases to that instances
> of sql server, would it be a good practice to put all my
> log files on that one D Drive?
> >--Original Message--
> >That seems like a reasonable setup. To be able to give
> more precise
> >suggestions, we'd need I/O metrics for the current
> config. I suggest you
> >keep it like it is unless you have performance problems
> and are willing to
> >spend some time on the issue.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Edie Richardson" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:034e01c3be9f$6c680620$a401280a@.phx.gbl...
> >> This is my first sql server app so I'm pretty ignorant.
> >> In my production environment I have a server with 4
> >> processors and 4 drives and is part of a SANS network.
> 2
> >> drives are mirrorred to be c: and two drives are
> mirrorred
> >> to be d:, both RAID1. I also have another drive (T:)
> >> available for my app. I have my datafiles for this app
> on
> >> the t: drive which is RAID5 and I have my log files on
> >> drive d:. Is this the recommended practice. I know
> that
> >> I read that I need to separate my data files and log
> >> files. Please advise.
> >>
> >> Thanks,
> >> Edie
> >
> >
> >.
> >
Showing posts with label pretty. Show all posts
Showing posts with label pretty. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
PL/SQL Problem
I'm having problems with the one homework problem, i'm a beginner and i'm pretty much trying to teach myself PL/SQL...can someone please help me out?...
scrore is a column of table students and its datatype is NUMBER. Assume that all values in this column score are distinct and that there are more than 3 values in this column. Write a piece of PL/SQL code that will find out the third largest score and the third smallest score. (You can print out the values or return the values.)
O~Originally posted by oflowers
I'm having problems with the one homework problem, i'm a beginner and i'm pretty much trying to teach myself PL/SQL...can someone please help me out?...
scrore is a column of table students and its datatype is NUMBER. Assume that all values in this column score are distinct and that there are more than 3 values in this column. Write a piece of PL/SQL code that will find out the third largest score and the third smallest score. (You can print out the values or return the values.)
O~
You could consider using RANK or DENSE_RANK:
SQL> select ename, sal,
2 rank() over (order by sal) rank_asc,
3 rank() over (order by sal desc) rank_desc,
4 dense_rank() over (order by sal) dense_rank_asc,
5 dense_rank() over (order by sal desc) dense_rank_desc
6 from emp
7* order by sal;
ENAME SAL RANK_ASC RANK_DESC DENSE_RANK_ASC DENSE_RANK_DESC
---- ---- ---- ---- ----- -----
SMITH 800 1 14 1 12
JAMES 950 2 13 2 11
ADAMS 1100 3 12 3 10
WARD 1250 4 10 4 9
MARTIN 1250 4 10 4 9
MILLER 1300 6 9 5 8
TURNER 1500 7 8 6 7
ALLEN 1600 8 7 7 6
CLARK 2450 9 6 8 5
BLAKE 2850 10 5 9 4
JONES 2975 11 4 10 3
SCOTT 3000 12 2 11 2
FORD 3000 12 2 11 2
KING 5000 14 1 12 1
14 rows selected.
Note there is no RANK_DESC=3, so probably DENSE_RANK suits you better:
SQL> select ename, sal
2 from
3 (
4 select ename, sal,
5 dense_rank() over (order by sal) dense_rank_asc,
6 dense_rank() over (order by sal desc) dense_rank_desc
7 from emp
9 )
10 where dense_rank_asc=3
11* or dense_rank_desc=3;
ENAME SAL
---- ----
JONES 2975
ADAMS 1100
Either print out or return those values as you prefer.|||Hi,
It is a good solution using the dense_rank, but i presume if you need to have more sorting on the same, you could do some sorting in the embedded query, like sorting on the ename itself.
I wish i saw this posting two weeks back, I could have saved quite a bit of time in a query, which wanted to look for the second orderid for a person out of around 10000 records.
:)
scrore is a column of table students and its datatype is NUMBER. Assume that all values in this column score are distinct and that there are more than 3 values in this column. Write a piece of PL/SQL code that will find out the third largest score and the third smallest score. (You can print out the values or return the values.)
O~Originally posted by oflowers
I'm having problems with the one homework problem, i'm a beginner and i'm pretty much trying to teach myself PL/SQL...can someone please help me out?...
scrore is a column of table students and its datatype is NUMBER. Assume that all values in this column score are distinct and that there are more than 3 values in this column. Write a piece of PL/SQL code that will find out the third largest score and the third smallest score. (You can print out the values or return the values.)
O~
You could consider using RANK or DENSE_RANK:
SQL> select ename, sal,
2 rank() over (order by sal) rank_asc,
3 rank() over (order by sal desc) rank_desc,
4 dense_rank() over (order by sal) dense_rank_asc,
5 dense_rank() over (order by sal desc) dense_rank_desc
6 from emp
7* order by sal;
ENAME SAL RANK_ASC RANK_DESC DENSE_RANK_ASC DENSE_RANK_DESC
---- ---- ---- ---- ----- -----
SMITH 800 1 14 1 12
JAMES 950 2 13 2 11
ADAMS 1100 3 12 3 10
WARD 1250 4 10 4 9
MARTIN 1250 4 10 4 9
MILLER 1300 6 9 5 8
TURNER 1500 7 8 6 7
ALLEN 1600 8 7 7 6
CLARK 2450 9 6 8 5
BLAKE 2850 10 5 9 4
JONES 2975 11 4 10 3
SCOTT 3000 12 2 11 2
FORD 3000 12 2 11 2
KING 5000 14 1 12 1
14 rows selected.
Note there is no RANK_DESC=3, so probably DENSE_RANK suits you better:
SQL> select ename, sal
2 from
3 (
4 select ename, sal,
5 dense_rank() over (order by sal) dense_rank_asc,
6 dense_rank() over (order by sal desc) dense_rank_desc
7 from emp
9 )
10 where dense_rank_asc=3
11* or dense_rank_desc=3;
ENAME SAL
---- ----
JONES 2975
ADAMS 1100
Either print out or return those values as you prefer.|||Hi,
It is a good solution using the dense_rank, but i presume if you need to have more sorting on the same, you could do some sorting in the embedded query, like sorting on the ename itself.
I wish i saw this posting two weeks back, I could have saved quite a bit of time in a query, which wanted to look for the second orderid for a person out of around 10000 records.
:)
Saturday, February 25, 2012
PIVOT TABLE QUERY
Hey guys, hopefully you can help me with my request... I am pretty new to SQL, so please bear with me..
I have a table on the system as follows
Item | Year | Month | ucivqa01 |AVG PRICE
ABC 2007 1 10 2.00
ABC 2007 2 10 2.10
ABC 2007 3 10 2.05
ABC 2007 4 10 2.30
ABC 2007 5 10 5.00
ABC 2007 6 10 1.95
XYZ 2007 1 10 100
XYZ 2007 2 10 112
XYZ 2007 3 10 111
XYZ 2007 4 10 100
XYZ 2007 5 10 105
XYZ 2007 6 10 104
I am trying to return the data in the following format:
ITEM MNTH1 MNTH2 MNTH3 MNTH4 MNTH5 MNTH 6 AVG1 AVG2 AVG3 AVG4 AVG5 AVG6
ABC 10 10 10 10 10 10 2.00 2.10 2.05 2.30 5.00 1.95
XYZ 10 10 10 10 10 10 100 112 111 100 105 104
This is only a small sample of the data, and i will be using the resulting SQL over a larger number or records. Also, the month and year will change. . I have a requirement to populate this data based on 41 months prior to the current month,
Anyway, i would appricate any help.
thanks
scotty
Here you go...
Code Snippet
Create Table #data (
Item Varchar(100) ,
Year Varchar(100) ,
Month int ,
ucivqa01 int ,
AVGPRICE float
);
Insert Into #data Values('ABC','2007','1','10','2.00');
Insert Into #data Values('ABC','2007','2','10','2.10');
Insert Into #data Values('ABC','2007','3','10','2.05');
Insert Into #data Values('ABC','2007','4','10','2.30');
Insert Into #data Values('ABC','2007','5','10','5.00');
Insert Into #data Values('ABC','2007','6','10','1.95');
Insert Into #data Values('XYZ','2007','1','10','100');
Insert Into #data Values('XYZ','2007','2','10','112');
Insert Into #data Values('XYZ','2007','3','10','111');
Insert Into #data Values('XYZ','2007','4','10','100');
Insert Into #data Values('XYZ','2007','5','10','105');
Insert Into #data Values('XYZ','2007','6','10','104');
Select
Item
,Max(Case When Year=2007 And Month=1 Then ucivqa01 End) Month1
,Max(Case When Year=2007 And Month=2 Then ucivqa01 End) Month2
,Max(Case When Year=2007 And Month=3 Then ucivqa01 End) Month3
,Max(Case When Year=2007 And Month=4 Then ucivqa01 End) Month4
,Max(Case When Year=2007 And Month=5 Then ucivqa01 End) Month5
,Max(Case When Year=2007 And Month=6 Then ucivqa01 End) Month6
,Avg(Case When Year=2007 And Month=1 Then AVGPRICE End) Avg1
,Avg(Case When Year=2007 And Month=2 Then AVGPRICE End) Avg2
,Avg(Case When Year=2007 And Month=3 Then AVGPRICE End) Avg3
,Avg(Case When Year=2007 And Month=4 Then AVGPRICE End) Avg4
,Avg(Case When Year=2007 And Month=5 Then AVGPRICE End) Avg5
,Avg(Case When Year=2007 And Month=6 Then AVGPRICE End) Avg6
From
#data
Group By
Item
|||Thanks Manivannan, that is a great help.
I also heard from someone else about using the pivot command instead. From that i was able to build the following up:
(column names are different as this is live data)
SELECT*
FROM(SELECT ITEM, CUSTOMER, InvMONTH, QTY, [Unit Price], PrcMonth
FROM mvxreport.dmsexportstage1)
SOURCEQUERY PIVOT(max(QTY)FOR [InvMONTH] IN([INV1], [INV2], [INV3], [INV4], [INV5], [INV6]))
AS PIVOTTABLE PIVOT(
max([Unit Price])FOR [PrcMONTH] IN([PRC1], [PRC2], [PRC3], [PRC4], [PRC5], [PRC6]))AS PIVOTTABLE1
But when i do this, i cannot group by Item, Customer.. I get the results below:
I would like to have one line per item and Customer Combiniation..
Do you have any suggestions on this?
Thanks
Scotty
Yes.. PIVOT is one of the nice operator. But your case it become hard. PIVOT operator is logically perfect if you want to pivot single column.
I tried to convert my previous query using PIVOT.
Create Table #data (
Item Varchar(100) ,
Year Varchar(100) ,
Month int ,
ucivqa01 int ,
AVGPRICE float
);
Insert Into #data Values('ABC','2007','1','10','2.00');
Insert Into #data Values('ABC','2007','2','10','2.10');
Insert Into #data Values('ABC','2007','3','10','2.05');
Insert Into #data Values('ABC','2007','4','10','2.30');
Insert Into #data Values('ABC','2007','5','10','5.00');
Insert Into #data Values('ABC','2007','6','10','1.95');
Insert Into #data Values('XYZ','2007','1','10','100');
Insert Into #data Values('XYZ','2007','2','10','112');
Insert Into #data Values('XYZ','2007','3','10','111');
Insert Into #data Values('XYZ','2007','4','10','100');
Insert Into #data Values('XYZ','2007','5','10','105');
Insert Into #data Values('XYZ','2007','6','10','104');
Code Snippet
Select * From
(
Select * From
(Select Item,Cast(Year as varchar) + '-' + Cast(Month as varchar) YearMonth,ucivqa01 From #data) as Data1
PIVOT
(
Max(ucivqa01) For YearMonth in ([2007-1], [2007-2], [2007-3], [2007-4], [2007-5], [2007-6])
) Pvt1
) as MaxData
Join
(
Select * From
(Select Item,Cast(Year as varchar) + '-' + Cast(Month as varchar) YearMonth,AVGPRICE From #data) as Data2
PIVOT
(
Avg(AVGPRICE) For YearMonth in ([2007-1], [2007-2], [2007-3], [2007-4], [2007-5], [2007-6])
) Pvt2
) as AVGData
on MaxData.Item=AVGData.Item
Subscribe to:
Posts (Atom)