Filter date for ToDay, some problem with time.

I all.

In a talbe I've a datatime field. for example it contain '16-4-2007 10:45'.

I like to write a SQL that return all record with the date field equals '16-4-2007' (it's not important the time). how to?

thank you.

Try this query to retrive data

select

*from clientswhere clientAddressbetween'2007-04-12 00:00:00.000'and'2007-04-12 23:59:59.999'

The alternative query is

select

*from clientswhere clientAddress>='2007-04-12'and clientAddress<'2007-04-13'

The time format is yyyy-MM-dd and time. Since you are saving time in DB the query should be framed as above

Hope this will help you

|||

try this syntax

print

convert(varchar(20),convert(datetime,'16-04-2006 16:45',105),105)

how it works: convert string to date time with correct format for date string and next convert date time back to string with format you need.

See help for CONVERT in T-SQL help for more format info.

filter data by row number

SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2

This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).Can you give me sample data to work on?|||Patient IdentifierPatient InitialsDateTimeOperator Index
0517_00003GHV18-Oct-0611:4891
0517_00003GHV18-Oct-0611:50100
0517_00004JMH17-Oct-0611:4189
0517_00004JMH17-Oct-0611:5093
0517_00004JMH17-Oct-0611:5291
0517_00004JMH17-Oct-0612:0093
0534_00003JS21-Nov-0612:35100
0534_00003JS21-Nov-0612:46100
0534_00004ChM20-Nov-0610:49100
0534_00004ChM20-Nov-0610:51100
0534_00006JK4-Dec-069:38100
0534_00006JK4-Dec-069:4784
0534_00006JK4-Dec-069:5093
0534_00007TL29-Nov-069:2298
0534_00007TL29-Nov-069:34100
0539_00001PGL9-Oct-069:39100
0539_00001PGL9-Oct-069:4395
0539_00002DWR27-Oct-0610:0491
0539_00002DWR31-Oct-0611:4092
0539_00002DWR31-Oct-0611:4196
0539_00002DWR31-Oct-0611:4292
0539_00003JmL30-Nov-069:1496
0539_00003JmL30-Nov-069:1897|||I figured it out! Thanks!|||Here is the code I wrote and it is not correct although it appears to be correct at first. I was validating my data and discovered on several instances a value of 80 (something) is there instead of 90 (something).

SELECT [Patient Identifier], Date, [Operator Index], Time

FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] BETWEEN 80 AND 89
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
[First Step] AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]

Filling up ListControl from a SQL query

Hello,

I use MFC ListControl. Is there any way to fill it up using SQL query without adding one row at a time?

All I could find was m_List.InsertItem(...) and m_List.SteItemText(...)

Are there any other ways?

Thanks

One possible way to do this is using virtual listctrl:

Fill(insert) the list items with bound data addresses first, then do SQL fetch for the result.

|||

Thanks a lot for the reply.

Could you give me an example of how to do that or point me to where I can read about it. Thanks again

|||

Following link maybe helpful

http://www.codeproject.com/listctrl/virtuallist.asp

fill factor question

Ive always had a hard time getting my head into this topic so please bear
with me. Say Ive got a huge table that gets lots of Inserts. But to do these
Inserts, it needs to do lots of Selects.
#Table3 is the table in question here:
create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
select t1.T1C1,T1C2,t2.T2C2, 'test'
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
So, would a table like this call for a lower Fill Factor as it will have
lots of new Inserts. Or would a table like this call for a higher Fill
Factor to increase the Select speed to do those Inserts?
TIA, ChrisR.
What column(s) is the clustered index on?
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
> Ive always had a hard time getting my head into this topic so please bear
> with me. Say Ive got a huge table that gets lots of Inserts. But to do
> these Inserts, it needs to do lots of Selects.
> #Table3 is the table in question here:
> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
> create table #Table2(T2C1 int, T2C2 char(10))
> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
> char(10))
> insert into #Table1 (T1C2) values ('T1C2')
> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
> select t1.T1C1,T1C2,t2.T2C2, 'test'
> from #Table1 t1
> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>
> So, would a table like this call for a lower Fill Factor as it will have
> lots of new Inserts. Or would a table like this call for a higher Fill
> Factor to increase the Select speed to do those Inserts?
> TIA, ChrisR.
>
|||Lets say C1 for all 3 tables.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
> What column(s) is the clustered index on?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>
|||If the inserts come in ordered, a higher fillfactor would be ideal. If
not, a high one will still be good as the index will balance out very
quickly. Maximizing the rows per page will greatly help the selection
of rows of data and be fewer I/Os.
|||If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>
|||Since this was a question regarding the appropriate fill factor, which you
answered quite nicely, I won't beat up on you too badly.
First of all, IDENTITIES are NOT good candidates for the Clustered Index as
is espoused so often.
Second, Identity Clustered Indexes do create page splits, quite often, at
the node levels of the indexes.
Third, Identities are usually only a surrogate key. All tables, wich
represent enities, must at least have a business key in order to use a
surrogate as a proxy; otherwise, you are just making things up with no clear
definition. That key should have a Unique Constraint defined whenever the
Primary Key is placed on the Identity.
Finally, for you select statement that you are using for your Insert
statement, all attributes used within a Join Condition and any highly
selectable columns that participate in a Where Clause should have and index
defined on them.
Sincerely,
Anthony Thomas

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJ53NT9aFHA.2900@.TK2MSFTNGP15.phx.gbl...
If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>
sql

fill factor question

Ive always had a hard time getting my head into this topic so please bear
with me. Say Ive got a huge table that gets lots of Inserts. But to do these
Inserts, it needs to do lots of Selects.
#Table3 is the table in question here:
create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
select t1.T1C1,T1C2,t2.T2C2, 'test'
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
So, would a table like this call for a lower Fill Factor as it will have
lots of new Inserts. Or would a table like this call for a higher Fill
Factor to increase the Select speed to do those Inserts?
TIA, ChrisR.What column(s) is the clustered index on?
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
> Ive always had a hard time getting my head into this topic so please bear
> with me. Say Ive got a huge table that gets lots of Inserts. But to do
> these Inserts, it needs to do lots of Selects.
> #Table3 is the table in question here:
> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
> create table #Table2(T2C1 int, T2C2 char(10))
> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
> char(10))
> insert into #Table1 (T1C2) values ('T1C2')
> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
> select t1.T1C1,T1C2,t2.T2C2, 'test'
> from #Table1 t1
> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>
> So, would a table like this call for a lower Fill Factor as it will have
> lots of new Inserts. Or would a table like this call for a higher Fill
> Factor to increase the Select speed to do those Inserts?
> TIA, ChrisR.
>|||Lets say C1 for all 3 tables.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
> What column(s) is the clustered index on?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>|||If the inserts come in ordered, a higher fillfactor would be ideal. If
not, a high one will still be good as the index will balance out very
quickly. Maximizing the rows per page will greatly help the selection
of rows of data and be fewer I/Os.|||If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>|||Since this was a question regarding the appropriate fill factor, which you
answered quite nicely, I won't beat up on you too badly.
First of all, IDENTITIES are NOT good candidates for the Clustered Index as
is espoused so often.
Second, Identity Clustered Indexes do create page splits, quite often, at
the node levels of the indexes.
Third, Identities are usually only a surrogate key. All tables, wich
represent enities, must at least have a business key in order to use a
surrogate as a proxy; otherwise, you are just making things up with no clear
definition. That key should have a Unique Constraint defined whenever the
Primary Key is placed on the Identity.
Finally, for you select statement that you are using for your Insert
statement, all attributes used within a Join Condition and any highly
selectable columns that participate in a Where Clause should have and index
defined on them.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJ53NT9aFHA.2900@.TK2MSFTNGP15.phx.gbl...
If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>

fill factor question

Ive always had a hard time getting my head into this topic so please bear
with me. Say Ive got a huge table that gets lots of Inserts. But to do these
Inserts, it needs to do lots of Selects.
#Table3 is the table in question here:
create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
select t1.T1C1,T1C2,t2.T2C2, 'test'
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
So, would a table like this call for a lower Fill Factor as it will have
lots of new Inserts. Or would a table like this call for a higher Fill
Factor to increase the Select speed to do those Inserts?
TIA, ChrisR.What column(s) is the clustered index on?
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
> Ive always had a hard time getting my head into this topic so please bear
> with me. Say Ive got a huge table that gets lots of Inserts. But to do
> these Inserts, it needs to do lots of Selects.
> #Table3 is the table in question here:
> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
> create table #Table2(T2C1 int, T2C2 char(10))
> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
> char(10))
> insert into #Table1 (T1C2) values ('T1C2')
> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
> select t1.T1C1,T1C2,t2.T2C2, 'test'
> from #Table1 t1
> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>
> So, would a table like this call for a lower Fill Factor as it will have
> lots of new Inserts. Or would a table like this call for a higher Fill
> Factor to increase the Select speed to do those Inserts?
> TIA, ChrisR.
>|||Lets say C1 for all 3 tables.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
> What column(s) is the clustered index on?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please bear
>> with me. Say Ive got a huge table that gets lots of Inserts. But to do
>> these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>|||If the inserts come in ordered, a higher fillfactor would be ideal. If
not, a high one will still be good as the index will balance out very
quickly. Maximizing the rows per page will greatly help the selection
of rows of data and be fewer I/Os.|||If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> What column(s) is the clustered index on?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please
>> bear with me. Say Ive got a huge table that gets lots of Inserts. But to
>> do these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>>
>|||Since this was a question regarding the appropriate fill factor, which you
answered quite nicely, I won't beat up on you too badly.
First of all, IDENTITIES are NOT good candidates for the Clustered Index as
is espoused so often.
Second, Identity Clustered Indexes do create page splits, quite often, at
the node levels of the indexes.
Third, Identities are usually only a surrogate key. All tables, wich
represent enities, must at least have a business key in order to use a
surrogate as a proxy; otherwise, you are just making things up with no clear
definition. That key should have a Unique Constraint defined whenever the
Primary Key is placed on the Identity.
Finally, for you select statement that you are using for your Insert
statement, all attributes used within a Join Condition and any highly
selectable columns that participate in a Where Clause should have and index
defined on them.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJ53NT9aFHA.2900@.TK2MSFTNGP15.phx.gbl...
If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> What column(s) is the clustered index on?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>> Ive always had a hard time getting my head into this topic so please
>> bear with me. Say Ive got a huge table that gets lots of Inserts. But to
>> do these Inserts, it needs to do lots of Selects.
>> #Table3 is the table in question here:
>> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
>> create table #Table2(T2C1 int, T2C2 char(10))
>> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
>> char(10))
>> insert into #Table1 (T1C2) values ('T1C2')
>> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
>> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
>> select t1.T1C1,T1C2,t2.T2C2, 'test'
>> from #Table1 t1
>> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>>
>> So, would a table like this call for a lower Fill Factor as it will have
>> lots of new Inserts. Or would a table like this call for a higher Fill
>> Factor to increase the Select speed to do those Inserts?
>> TIA, ChrisR.
>>
>

FileTime

Is there a SQL function to get FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
You can use DATEDIFF(ms,startdate, GETDATE())
where ms stands for milliseconds, startdate is a your own starting date, and
getdate is the system function that returns the current date, up to
milliseconds.
Francesco Anti
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...[vbcol=seagreen]
> Is there a SQL function to get FileTime?
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
filetime
>
|||One thing to remember is that since SQL Server DateTime has less reolution,
you can do a direct comparison.
"Francesco Anti" <fanti @. sicosbt.it> wrote in message
news:ed2YskSBFHA.608@.TK2MSFTNGP15.phx.gbl...
> You can use DATEDIFF(ms,startdate, GETDATE())
> where ms stands for milliseconds, startdate is a your own starting date,
and[vbcol=seagreen]
> getdate is the system function that returns the current date, up to
> milliseconds.
> Francesco Anti
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
time
> filetime
>

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
No, not built-in; I guess the only way would be to create your own UDF.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...[vbcol=seagreen]
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
filetime
>

FileTime

Is there a SQL function to get FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the fileti
me
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>You can use DATEDIFF(ms,startdate, GETDATE())
where ms stands for milliseconds, startdate is a your own starting date, and
getdate is the system function that returns the current date, up to
milliseconds.
Francesco Anti
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
> Is there a SQL function to get FileTime?
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
>
filetime[vbcol=seagreen]
>|||One thing to remember is that since SQL Server DateTime has less reolution,
you can do a direct comparison.
"Francesco Anti" <fanti @. sicosbt.it> wrote in message
news:ed2YskSBFHA.608@.TK2MSFTNGP15.phx.gbl...
> You can use DATEDIFF(ms,startdate, GETDATE())
> where ms stands for milliseconds, startdate is a your own starting date,
and
> getdate is the system function that returns the current date, up to
> milliseconds.
> Francesco Anti
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:E0DD2F8B-F244-435D-99A5-105D368AC8D6@.microsoft.com...
time[vbcol=seagreen]
> filetime
>

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:

> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
>
> What do you mean by filetime? Can you give an example of what the fileti
me
> data looks like?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>No, not built-in; I guess the only way would be to create your own UDF.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
>
filetime[vbcol=seagreen]
>sql

FileTime

Is there a sql function for FileTime?
Filetime is a 64 bit number representing time(up to nano seconds) from
January 1, 1601 to what ever the time right now.
In C++/C# etc, you have functions to get his value or to convert file time
in system time. Ex;: Getfiletime()
FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
"Rick Sawtell" wrote:
>
> "uhway" <uhway@.discussions.microsoft.com> wrote in message
> news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
> > How can I convert filetime to datetime in sql server inside a sored
> > procedure.
> >
> > Thanks
> > BVR
>
>
> What do you mean by filetime? Can you give an example of what the filetime
> data looks like?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>No, not built-in; I guess the only way would be to create your own UDF.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"uhway" <uhway@.discussions.microsoft.com> wrote in message
news:755BB301-090D-4D92-94E1-30D91A112A10@.microsoft.com...
> Is there a sql function for FileTime?
>
> Filetime is a 64 bit number representing time(up to nano seconds) from
> January 1, 1601 to what ever the time right now.
> In C++/C# etc, you have functions to get his value or to convert file time
> in system time. Ex;: Getfiletime()
> FileTime 127512288251260000 is equivalent to '2005/01/26 16:00:25.126'
> "Rick Sawtell" wrote:
> >
> > "uhway" <uhway@.discussions.microsoft.com> wrote in message
> > news:B53A17CF-BFE1-4B37-8D0E-466859C445C8@.microsoft.com...
> > > How can I convert filetime to datetime in sql server inside a sored
> > > procedure.
> > >
> > > Thanks
> > > BVR
> >
> >
> > What do you mean by filetime? Can you give an example of what the
filetime
> > data looks like?
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
>

Files Needed to Distribute CR 8.5 at run time

I package my Project using Deployment Wizard to create a set up file. I'm using VB6 and Crystal Report 8.5 and Access 2000 as my database. After installing my application in other computer I notice that all my Crytal Report 8.5 is not running. What other Files should I need?

Thanks

Noel
www.roadcs.comHi,

Refer the help file "Runtime.hlp", it's inbuild in crystal report setup.

Thanks & Regards,
K.Babu

Originally posted by Noel Rico
I package my Project using Deployment Wizard to create a set up file. I'm using VB6 and Crystal Report 8.5 and Access 2000 as my database. After installing my application in other computer I notice that all my Crytal Report 8.5 is not running. What other Files should I need?

Thanks

Noel
www.roadcs.com

Filegroup restore problem.

Hi!

Last time I applied new filegroup to our database. I call it HISTORY, bacause it stores all moditications of tables on PRIMARY filegroup. I.e. if on PRIMARY filegroup is table User, then on HISTORY filegroup is table UserHistory which, stores changes apllied to User table. It's simple database changes monitoring. We use SQL SERVER 2000 Standard Edition.

One person of our team (Artur) needs copy tables from PRIMARY filegroup to his computer to perform some experiments. Previously there aren't problem. He make full backup of our database, copy it to DVD drive and restore it on his computer. Backup was size of 3GB.

When we added monitoring full backup is size of 20GB which is too large to DVD disk. So I thoght that we can backup only PRIMARY filegroup, because history of changes is useless for Artur.

Unfortunately it is not simple as we wish. I tried those steps:

Perform PRIMARY filegroup backup to file.

Copy this file to Artur's computer by DVD disk.

Perform our database's Trnsaction Log on Artur's computer.

Restore PRIMARY filegroup on Artur's computer (then database is "Loading" is Enterprise Manager)

Restore backup done in step 3. Step 5. yelds error (File 'TeleDB' has been rolled forward to LSN 51...01. This log terminates at LSN 50...01, which is too early to apply the WITH RECOVERY option. Reissue the RESTORE LOG statement WITH NORECOVERY.

What is wrong?
Is it possible to move data to Artur's computer this way, without full backup and serie of log backups?
Can I use other alternative solution to move data?

Best regards,
Walter

OK.

There are several issues going on here, but the most fundamental one is that SQL 2000 does not support partial database availability. The ability to restore only the primary filegroup and bring the database online is a new feature in SQL 2005.

I believe that the reason for the LSN message you're getting is that you haven't overwritten the HISTORY filegroup in the database you're restoring to, and it has a higher LSN than the primary filegroup that you're restoring. Even if that mismatch didn't exist (if you deleted the entire database before starting the restore), you wouldn't be able to bring the database online until you restored all of the filegroups.

About the best you'll get in SQL 2000 is to back up each filegroup separately and transfer it on its own DVD, but you can't omit a filegroup and still bring the DB online.

filegroup question

I apologize if this shows up twice as the first time I sent it said there was
an error. An article snippet:
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
Is this true form your experiences? If so, why not just split the whole db
into multiple files on the same filegroup?
There are other factors to consider.
How many physical disk drives do you have and disk controllers and how many
threads does each controller allow?
If you have too many reads occurring, then you may end up with some disk
thrashing as the switches occur.
Are you set up with a RAID array and if so, how is that RAID array handling
things.
From personal experience, I have found that the best of all worlds for small
to medium size databases (30GB or less) appears to be the following:
SQL Server and core files installed on the Root Drive.
Transaction logs on RAID 1 drives
Database on RAID 5 drives
Place very heavily used tables in their own filegroup.
I'm sure others have some great input to this question as well.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?
|||ChrisR wrote: <snip>
> But if the table were divided into three physical files (all part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster.
Well, multiple threads won't help much if the bottleneck is the I/O on a
particular disc. Remember that I/O is magnitudes slower than RAM or
context switching.
Since I primarily use OLTP type applications I have never bothered to
examine the potential 'gain'. In my experience the important part is to
activate as many physical discs as possible. This can be done with a
RAID setting, or by placing one or several files on each disc and
assigning them to individual or shared filegroups. In my experience the
simplest advice is to stripe and mirror everything. Unless you want to
do very specific database tuning every once in a while, this is probably
the best generic advice.
HTH,
Gert-Jan
|||"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
My understanding is that this was true under SQL 6.5, but is no longer true
in SQL 2000 (which I believe does support multiple treads per physical
file.)

>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?

Filegroup Question

I know that tables and indexes can belong to a certain filegroup, and that
this can be specified at creation time of the object, but can stored
procedures, views, or functions belong to a specific filegroup? Or are they
defaulted to the Primary filegroup since their information is stored in the
system tables for the database?
Also, is there a Information Schema view that will return a list of
filesgroups and the objects that belong to those filegroups?
Thanks!
Jason
No you can not specify where those objects live. Only the data and indexes.
I don't believe there is a view for what you want. You will most likely
have to do something custom to get that without using some third party tool.
Andrew J. Kelly SQL MVP
"Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
>I know that tables and indexes can belong to a certain filegroup, and that
> this can be specified at creation time of the object, but can stored
> procedures, views, or functions belong to a specific filegroup? Or are
> they
> defaulted to the Primary filegroup since their information is stored in
> the
> system tables for the database?
> Also, is there a Information Schema view that will return a list of
> filesgroups and the objects that belong to those filegroups?
> Thanks!
> Jason
|||Thanks for the quick response Andrew. That's what I thought, but I figured I
would ask the group to see if my thoughts were wrong.
Jason
"Andrew J. Kelly" wrote:

> No you can not specify where those objects live. Only the data and indexes.
> I don't believe there is a view for what you want. You will most likely
> have to do something custom to get that without using some third party tool.
> --
> Andrew J. Kelly SQL MVP
>
> "Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
> news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
>
>

filegroup question

I apologize if this shows up twice as the first time I sent it said there wa
s
an error. An article snippet:
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavil
y
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
Is this true form your experiences? If so, why not just split the whole db
into multiple files on the same filegroup?There are other factors to consider.
How many physical disk drives do you have and disk controllers and how many
threads does each controller allow?
If you have too many reads occurring, then you may end up with some disk
thrashing as the switches occur.
Are you set up with a RAID array and if so, how is that RAID array handling
things.
From personal experience, I have found that the best of all worlds for small
to medium size databases (30GB or less) appears to be the following:
SQL Server and core files installed on the Root Drive.
Transaction logs on RAID 1 drives
Database on RAID 5 drives
Place very heavily used tables in their own filegroup.
I'm sure others have some great input to this question as well.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?|||ChrisR wrote: <snip>
> But if the table were divided into three physical files (all part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster.
Well, multiple threads won't help much if the bottleneck is the I/O on a
particular disc. Remember that I/O is magnitudes slower than RAM or
context switching.
Since I primarily use OLTP type applications I have never bothered to
examine the potential 'gain'. In my experience the important part is to
activate as many physical discs as possible. This can be done with a
RAID setting, or by placing one or several files on each disc and
assigning them to individual or shared filegroups. In my experience the
simplest advice is to stripe and mirror everything. Unless you want to
do very specific database tuning every once in a while, this is probably
the best generic advice.
HTH,
Gert-Jan|||"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
My understanding is that this was true under SQL 6.5, but is no longer true
in SQL 2000 (which I believe does support multiple treads per physical
file.)

>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?

Filegroup Question

I know that tables and indexes can belong to a certain filegroup, and that
this can be specified at creation time of the object, but can stored
procedures, views, or functions belong to a specific filegroup? Or are they
defaulted to the Primary filegroup since their information is stored in the
system tables for the database?
Also, is there a Information Schema view that will return a list of
filesgroups and the objects that belong to those filegroups?
Thanks!
JasonNo you can not specify where those objects live. Only the data and indexes.
I don't believe there is a view for what you want. You will most likely
have to do something custom to get that without using some third party tool.
--
Andrew J. Kelly SQL MVP
"Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
>I know that tables and indexes can belong to a certain filegroup, and that
> this can be specified at creation time of the object, but can stored
> procedures, views, or functions belong to a specific filegroup? Or are
> they
> defaulted to the Primary filegroup since their information is stored in
> the
> system tables for the database?
> Also, is there a Information Schema view that will return a list of
> filesgroups and the objects that belong to those filegroups?
> Thanks!
> Jason|||Thanks for the quick response Andrew. That's what I thought, but I figured I
would ask the group to see if my thoughts were wrong.
Jason
"Andrew J. Kelly" wrote:
> No you can not specify where those objects live. Only the data and indexes.
> I don't believe there is a view for what you want. You will most likely
> have to do something custom to get that without using some third party tool.
> --
> Andrew J. Kelly SQL MVP
>
> "Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
> news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
> >I know that tables and indexes can belong to a certain filegroup, and that
> > this can be specified at creation time of the object, but can stored
> > procedures, views, or functions belong to a specific filegroup? Or are
> > they
> > defaulted to the Primary filegroup since their information is stored in
> > the
> > system tables for the database?
> >
> > Also, is there a Information Schema view that will return a list of
> > filesgroups and the objects that belong to those filegroups?
> >
> > Thanks!
> > Jason
>
>

filegroup question

I apologize if this shows up twice as the first time I sent it said there was
an error. An article snippet:
If your database is very large and very busy, multiple files can be used to
increase performance. Here is one example of how you might use multiple
files. Let's say you have a single table with 10 million rows that is heavily
queried. If the table is in a single file, such as a single database file,
then SQL Server would only use one thread to perform a read of the rows in
the table. But if the table were divided into three physical files (all part
of the same filegroup), then SQL Server would use three threads (one per
physical file) to read the table, which potentially could be faster. In
addition, if each file were on its own separate physical disk or disk array,
the performance gain would even be greater.
Is this true form your experiences? If so, why not just split the whole db
into multiple files on the same filegroup?There are other factors to consider.
How many physical disk drives do you have and disk controllers and how many
threads does each controller allow?
If you have too many reads occurring, then you may end up with some disk
thrashing as the switches occur.
Are you set up with a RAID array and if so, how is that RAID array handling
things.
From personal experience, I have found that the best of all worlds for small
to medium size databases (30GB or less) appears to be the following:
SQL Server and core files installed on the Root Drive.
Transaction logs on RAID 1 drives
Database on RAID 5 drives
Place very heavily used tables in their own filegroup.
I'm sure others have some great input to this question as well.
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?|||ChrisR wrote: <snip>
> But if the table were divided into three physical files (all part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster.
Well, multiple threads won't help much if the bottleneck is the I/O on a
particular disc. Remember that I/O is magnitudes slower than RAM or
context switching.
Since I primarily use OLTP type applications I have never bothered to
examine the potential 'gain'. In my experience the important part is to
activate as many physical discs as possible. This can be done with a
RAID setting, or by placing one or several files on each disc and
assigning them to individual or shared filegroups. In my experience the
simplest advice is to stripe and mirror everything. Unless you want to
do very specific database tuning every once in a while, this is probably
the best generic advice.
HTH,
Gert-Jan|||"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:169D42A6-339E-47CA-B0A4-23793C2AF884@.microsoft.com...
> I apologize if this shows up twice as the first time I sent it said there
was
> an error. An article snippet:
> If your database is very large and very busy, multiple files can be used
to
> increase performance. Here is one example of how you might use multiple
> files. Let's say you have a single table with 10 million rows that is
heavily
> queried. If the table is in a single file, such as a single database file,
> then SQL Server would only use one thread to perform a read of the rows in
> the table. But if the table were divided into three physical files (all
part
> of the same filegroup), then SQL Server would use three threads (one per
> physical file) to read the table, which potentially could be faster. In
> addition, if each file were on its own separate physical disk or disk
array,
> the performance gain would even be greater.
My understanding is that this was true under SQL 6.5, but is no longer true
in SQL 2000 (which I believe does support multiple treads per physical
file.)
>
> Is this true form your experiences? If so, why not just split the whole db
> into multiple files on the same filegroup?

Filegroup Bkp

Hi,

I have a VLDB database replicated to two different servers; one replication is a merge and the other is Transactional. It is a real time data which has a very high growth rate. At present we take full backup daily. The users during the pick time complain for the performance of this database. I am planning to implement a file group backups on this db and seperate tables and indexes on each file group. I would like expert opinion that will this implementation affect on my replication. I am worried for if i do this, my current replication will not be disturbed or stop working? Please advise? Also please suggest how frequent should i take backups?

Thanks

You are reconfiguring the database as well as the table structures. So, yes, it will impact replication when you reconfigure everything. Once moved, replication does not care. It issues insert, update, and delete statements and does not care what file the data is being written to.

The frequency of your backup is based on your data loss requirements. There isn't a specific answer on the frequency.

|||

First of all try to figure it out what is cuasing performance problem by running the trace for high duration, cpu and reads queries/procedures then proceed based on the analysis...

Are you running full backups during peak hours?

In the market there are some third party backup tools make your backups faster.

|||Yes you can test the filegroup backup method in thsi case, but ensure you have tested them in order to ensure it works in the case of an disaster recovery, fyi http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlwriter.mspx link.|||

There are no utilities to make backups "faster". Everything still calls the SQL Server backup engine and that engine runs as fast as the devices on the other side of it can take data. Because these utilities compress a backup, you are writing less to disk. Since you are writing less to disk, the backup runs in a shorter interval. It does not run any faster.

There is also a WIDE variance in times with these utilities. I've tested SQL Litespeed and Red Gate's SQL Backup. After testing more than 100 different databases containing a variety of data, Litespeed gets the job done in about 1/3 of the time that SQL Backup takes (default settings for both out of the box). The only case where I've gotten equivalent performance with the default settings was when I used a database that was almost entirely constructed of large text columns.

|||Does that mean, it will disturb the existing replication if i create a new file group on my db and i will have to rebuild replication?|||Does that mean, it will disturb the existing replication if i create a new file group on my db and i will have to rebuild replication?
|||No. You can add filegroups all you want without any issues. The issue comes when you want to move the tables to those filegroups. That is going to require dropping and recreating the clustered index which is normally the primary key. If you have enabled schema changes, then you will get an error when you try to alter the schema on the subscriber. I'd have to do some testing, I've never tried to move tables around to different filegroups on the subscriber while also keeping replication in place and running.

Filegroup backup and full recovery mode

Hi all,
My database in mssql2000 sp3a is about 40 GB in size. I
want to use filegroups and implement filegroup backup - at
the same time - i do not want to compromise on my 'FULL
recovery mode' - which means that i would like to have
point in time recovery as well.
Could any one let me know how to implement the same with
an example...?Hi
I think you will find in BOL a well explained examples about how to
accomlish it
"bharath" <barathsing@.hotmail.com> wrote in message
news:04d801c3b586$6db9bcc0$a501280a@.phx.gbl...
> Hi all,
> My database in mssql2000 sp3a is about 40 GB in size. I
> want to use filegroups and implement filegroup backup - at
> the same time - i do not want to compromise on my 'FULL
> recovery mode' - which means that i would like to have
> point in time recovery as well.
> Could any one let me know how to implement the same with
> an example...?