Any problem for using a single data file?

Hi there,
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
Alex
That was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>
|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>
|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with
>
|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with
>
|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Any problem for using a single data file?

Hi there,
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
AlexThat was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with[vbcol=seagreen]
>|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with[vbcol=seagreen]
>|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Any problem for using a single data file?

Hi there,
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
AlexThat was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > Is there any problem using 1 data file with restricted file
growth
> > set to 20GB? I've heard that it's better to have multiple data files
with
> > 2GB each. Is that true?
> >
> > Thanks!
> >
> > Alex
> >
> >
>|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > Is there any problem using 1 data file with restricted file
growth
> > set to 20GB? I've heard that it's better to have multiple data files
with
> > 2GB each. Is that true?
> >
> > Thanks!
> >
> > Alex
> >
> >
>|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Any alternative way to retreive data

Hi: Guys
Given the table below I want a select query that returns the AccountRepID with the largest single sale for each RegionID. In the event of a tie choose any single top AccountRepID to return.

CREATE TABLE [Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
RegionID] [int],
[AccountRepID] [int],
[SalesAmount] [money]
)

If the data were
salesid,regionid,accountrepid,salesamount
1,101,31,$50
2,101,32,$25
3,102,31,$25
4,102,32,$25
5,102,31,$15

The query should return
regionid,accountrepid
101,31
102,31 or 102,32

Is there another way to get the data other than the following query:

select regionID,accountrepid FROM Sales
where salesamount in
(select max(salesamount) FROM Sales group by regionid)

ThanksI don't think that will actually get you what you need. What if one region has the exact same salesamount as another region, but it's not the max for that region. You've then returned duplicate rows for that region.

Try this:

SELECT sa1.regionID, MAX(sa1.accountrepid)
FROM
Sales sa1
INNER JOIN (
SELECT regionID, MAX(salesamount) AS salesamount
FROM Sales) sa2 ON sa1.regionID = sa2.regionID
AND sa1.salesamount = sa2.salesamount|||select a.RegionId,a.AccountRepId,a.SalesAmount From Sales a
Inner join
(select RegionId,Max(salesAmount) as SalesAmount from Sales group by RegionId) b
on a.RegionId = b.RegionId and a.SalesAmount = b.SalesAmount