Filter Expression

Hi

I have some data that need to be filtered based on a SET of Id's.
If it's about a single ID, then i would pass it as a parameter in a
stored procedure and use it within the the WHERE Clause, but here those
ID's are determined in run time and I can't simply create a a stored
procedure for an unknown amount of ID's.
I looked into the SQL Server 8.0 Manual but had no examples how to use
the Function Filter.
Generaly, how can filter some records based on a set of ID's?

Best regardscoosa (coosa76@.gmail.com) writes:
> I have some data that need to be filtered based on a SET of Id's.
> If it's about a single ID, then i would pass it as a parameter in a
> stored procedure and use it within the the WHERE Clause, but here those
> ID's are determined in run time and I can't simply create a a stored
> procedure for an unknown amount of ID's.
> I looked into the SQL Server 8.0 Manual but had no examples how to use
> the Function Filter.
> Generaly, how can filter some records based on a set of ID's?

FROM tbl t
JOIN iter_intlist_to_tbl(@.list_of_ids, DEFAULT) f ON t.id = f.number

iter_intlist_to_tbl is table-valued function that unpacks a space-
separared list of integers into table which you then join with.

Code at http://www.sommarskog.se/arrays-in-...ist-of-integers.
The rest of the article provides more discussion about this kind of problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Filling out missing data in subsequent records?

Hi

I receive several datafiles from another system that are more or less in a Excel pivot table like format.
That is the first row representing the current data is fully filled, while subsequent rows, representing historic data are left partly empty.
Current rows and historic rows have different identifiers, e.g. rectype=0 or 1
Filling out the missing data on the historic record should be simple, if only all current rows would be filled.
Some current rows aren't filled, so the stuff like the following doesn't work:
update t1
set t1.colA =
(
select top 1 t2.colA
from mytable AS t2
where t2.rowid <= t1.rowid
and t2.cola <> 0
order by t2.rowid desc
)
from mytable AS t1

Somehow I need to check for the rectype, so I don't fill out rows with data from a previous entity

Any suggestions before I revert to using a cursor?

And while we are at it: I am in for an easy way to do this for all (about 60) colums in one move?

Before you ask:
After filling everything out we process the file to arrive at a few handy fromto tables, so we can use the correct data about the entity's status at a particular point in time elsewhere

I am using MS SQL Server 2005, and solutions are allowd to use any specific trickery that MSSQL allows.

Many thanks for any constructive thoughts

Cheers

DrioWould this do the job? My changes are highlighted

UPDATE t1
SET t1.colA =
(
SELECT TOP 1 t2.colA
FROM MyTable AS t2
WHERE t2.rowid < t1.rowid
AND t2.colA <> 0
ORDER BY t2.rowid DESC
)
FROM mytable AS t1
WHERE rectype = 0

I havn't tested this code - it's only in my head (/on the screen) so don't use it on your live data ;)|||Thnak you georgev:
for the tagline (I won't do it again; a real eye-opener)
for the small correction in my code and for the direction.

It goes wrong where there are history record after the current reccord with no data.Obvioulsy they get filled from the previous current record that hadd data.

My interim solution
1. update all current records wh data with a dummy value
2. use the fill out query
We then have to check the marked records and see if we can find a pattern that allows us the handle them in code (otherwise someone has to go through them manually; only .25% of total)

Thanks for you swift response

Cheers

Drio

Fill factor in dbcc dbreindex

Hi
I am on sqlserver 2000 and my question is on dbcc dbreindex.
If I say dbcc dbreindex('TABLE_NAME')
what is the fillfactor used on index rebuilds.
Documents does not say anywhere about it.
Tks
MangeshHi
The fill factor that was used when the index was originally created. 90% by
default.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
> Hi
> I am on sqlserver 2000 and my question is on dbcc dbreindex.
> If I say dbcc dbreindex('TABLE_NAME')
> what is the fillfactor used on index rebuilds.
> Documents does not say anywhere about it.
> Tks
> Mangesh
>|||I believe that default is 100%, unless you change it with sp_configure...?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> The fill factor that was used when the index was originally created. 90% b
y default.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message
> news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
>|||I have just tried this on SP3 and the default is 80
"Tibor Karaszi" wrote:

> I believe that default is 100%, unless you change it with sp_configure...?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>|||Hi Marc
What exactly did you try?
I just ran some tests, and the orig_fillfactor value was 0% in sysindexes,
shown as completely full pages with DBCC SHOWCONTIG.
This seems to support what Tibor said.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:89126794-1194-4595-8820-FBB14C23A7AE@.microsoft.com...[vbcol=seagreen]
>I have just tried this on SP3 and the default is 80
> "Tibor Karaszi" wrote:
>|||I agree with Kalen and Tibor. The default should be 0 which is 100% full.
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ucTTcPFPFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Hi Marc
> What exactly did you try?
> I just ran some tests, and the orig_fillfactor value was 0% in sysindexes,
> shown as completely full pages with DBCC SHOWCONTIG.
> This seems to support what Tibor said.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:89126794-1194-4595-8820-FBB14C23A7AE@.microsoft.com...
>

Fill factor in dbcc dbreindex

Hi
I am on sqlserver 2000 and my question is on dbcc dbreindex.
If I say dbcc dbreindex('TABLE_NAME')
what is the fillfactor used on index rebuilds.
Documents does not say anywhere about it.
Tks
Mangesh
Hi
The fill factor that was used when the index was originally created. 90% by
default.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
> Hi
> I am on sqlserver 2000 and my question is on dbcc dbreindex.
> If I say dbcc dbreindex('TABLE_NAME')
> what is the fillfactor used on index rebuilds.
> Documents does not say anywhere about it.
> Tks
> Mangesh
>
|||I believe that default is 100%, unless you change it with sp_configure...?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> The fill factor that was used when the index was originally created. 90% by default.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
> news:B88E1A87-BED9-4155-BDE6-B8F261CCF46C@.microsoft.com...
>
|||I have just tried this on SP3 and the default is 80
"Tibor Karaszi" wrote:

> I believe that default is 100%, unless you change it with sp_configure...?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eDqjC2$OFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
|||Hi Marc
What exactly did you try?
I just ran some tests, and the orig_fillfactor value was 0% in sysindexes,
shown as completely full pages with DBCC SHOWCONTIG.
This seems to support what Tibor said.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:89126794-1194-4595-8820-FBB14C23A7AE@.microsoft.com...[vbcol=seagreen]
>I have just tried this on SP3 and the default is 80
> "Tibor Karaszi" wrote:
|||I agree with Kalen and Tibor. The default should be 0 which is 100% full.
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ucTTcPFPFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Hi Marc
> What exactly did you try?
> I just ran some tests, and the orig_fillfactor value was 0% in sysindexes,
> shown as completely full pages with DBCC SHOWCONTIG.
> This seems to support what Tibor said.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:89126794-1194-4595-8820-FBB14C23A7AE@.microsoft.com...
>
sql

filegroups table

Hi!!!
I would like to know what filegroup is in the table or what tables are in the filegroup, using SELECT.
Excume my english.
Thank you.May check DBCC CHECKFILEGROUP which returns part of information required.|||It is found in sysindexes.
The data pages will be id = 0 if no clustered index or 1 if there is a clustered index.

groupid will give the filegroup id.

You can find the filegroup corresponding to the groupid in sysfiles.

Filegroup question

Hi
I have database with multiples files (.ndf) but it resides in one filegrp
which is PRIMARY.
If i wanted to restore, do i just perform restore from full backup. Any
additional steps since there are multiple files in that filegrp?
TIA
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:DE6A17FE-9030-44B1-987C-392D73B11C8B@.microsoft.com...
> Hi
> I have database with multiples files (.ndf) but it resides in one filegrp
> which is PRIMARY.
> If i wanted to restore, do i just perform restore from full backup. Any
> additional steps since there are multiple files in that filegrp?
> TIA
It depends on how you performed your backup. If you did a database backup,
then you simply need to perform a restore from that backup.
If you did a filegroup backup, (which you probably did not do as you only
have the default PRIMARY filegroup), then you could restore the filegroup
backup and rerun all logs since the filegroup backup occurred.
Rick Sawtell
MCT, MCSD, MCDBA

Filegroup question

Hi
I have database with multiples files (.ndf) but it resides in one filegrp
which is PRIMARY.
If i wanted to restore, do i just perform restore from full backup. Any
additional steps since there are multiple files in that filegrp?
TIA"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:DE6A17FE-9030-44B1-987C-392D73B11C8B@.microsoft.com...
> Hi
> I have database with multiples files (.ndf) but it resides in one filegrp
> which is PRIMARY.
> If i wanted to restore, do i just perform restore from full backup. Any
> additional steps since there are multiple files in that filegrp?
> TIA
It depends on how you performed your backup. If you did a database backup,
then you simply need to perform a restore from that backup.
If you did a filegroup backup, (which you probably did not do as you only
have the default PRIMARY filegroup), then you could restore the filegroup
backup and rerun all logs since the filegroup backup occurred.
Rick Sawtell
MCT, MCSD, MCDBA

file size of restore

Hi
I did an initial file size of a db to 100GB .
The backups reflect the true size of the DB (about 3GB). However if you
try and restore the DB it wants 500GB of disk space. Is there anyway to
restore so it only restores the size of the data?I don't know why it wants 500GB but you do need the entire size of the data
and log files not just the data itself. It is a good idea to have extra
space but you may be pushing it a little too far if you only have 3GB.
Andrew J. Kelly SQL MVP
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:VvGdna6Hf7i_DF3bnZ2dnUVZ8rOdnZ2d@.bt
.com...
> Hi
> I did an initial file size of a db to 100GB .
> The backups reflect the true size of the DB (about 3GB). However if you
> try and restore the DB it wants 500GB of disk space. Is there anyway to
> restore so it only restores the size of the data?
>
>
>
>
>|||>I don't know why it wants 500GB
Perhaps the initial size was 100GB and it has now grown to 500GB? Doesn't ch
ange what you are
saying, though... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMhxzCd3HHA.2312@.TK2MSFTNGP06.phx.gbl...
>I don't know why it wants 500GB but you do need the entire size of the data
and log files not just
>the data itself. It is a good idea to have extra space but you may be pushi
ng it a little too far
>if you only have 3GB.
> --
> Andrew J. Kelly SQL MVP
> "Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
> news:VvGdna6Hf7i_DF3bnZ2dnUVZ8rOdnZ2d@.bt
.com...
>|||Only thing that makes sense to me is that 1) the data file has grown to
500GB or more likely the LOG file has grown unchecked to 400GB because you
had Full Recovery Mode set but never backed up the log file.
TheSQLGuru
President
Indicium Resources, Inc.
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:VvGdna6Hf7i_DF3bnZ2dnUVZ8rOdnZ2d@.bt
.com...
> Hi
> I did an initial file size of a db to 100GB .
> The backups reflect the true size of the DB (about 3GB). However if you
> try and restore the DB it wants 500GB of disk space. Is there anyway to
> restore so it only restores the size of the data?
>
>
>
>
>

file group question

Hi
I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
They are 4 different disks and different disk controllers.
I do not have a backup of the database. Now I have a situation where I lost
one of the disk containing the index filegroup. I am running in FULL MODE.
I do not care if I loose index file group as it will only slow down the
database.
Is there a way to start the database without the index filegroup?
Can I take it offline or drop it?
Thanks
Mangesh
Mangesh Deshpande wrote:
> Hi
> I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
> They are 4 different disks and different disk controllers.
> I do not have a backup of the database. Now I have a situation where
> I lost one of the disk containing the index filegroup. I am running
> in FULL MODE.
> I do not care if I loose index file group as it will only slow down
> the database.
> Is there a way to start the database without the index filegroup?
> Can I take it offline or drop it?
> Thanks
> Mangesh
Do you think there were any clustered indexes in the database. If so,
where did you put those tables? On one of the table filegroups or one of
the index file groups? The table goes with the clustered index, so if
you built a clustered index on a table and put it on the index drive you
lost, you lost the table as well.
Many here are going to wonder:
Where is your redundancy on your drive subsystem?
Why no backups?
It's pretty risky leaving both out of the equation as you now know.
Going forward I might suggest using two mirrored sets. One for data and
one for temp db and log files.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David. This is just a hypothetical case. For production we are using
RAIDa 5.
I am keeping clustered index on the my table file group and it is not broken.
My non clustered indexes are on separate file group and it is broken.
So I want to have a mechanism by which even if the index file group disk
crashes my system should be up. In oracle you can take index file system
offline and allow the database to work fine.
I wanted to check if we have a mechanism in SQL Server 2000.
Thanks always
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> Do you think there were any clustered indexes in the database. If so,
> where did you put those tables? On one of the table filegroups or one of
> the index file groups? The table goes with the clustered index, so if
> you built a clustered index on a table and put it on the index drive you
> lost, you lost the table as well.
> Many here are going to wonder:
> Where is your redundancy on your drive subsystem?
> Why no backups?
> It's pretty risky leaving both out of the equation as you now know.
> Going forward I might suggest using two mirrored sets. One for data and
> one for temp db and log files.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Mangesh Deshpande wrote:
> Thanks David. This is just a hypothetical case. For production we are
> using RAIDa 5.
> I am keeping clustered index on the my table file group and it is not
> broken. My non clustered indexes are on separate file group and it is
> broken.
> So I want to have a mechanism by which even if the index file group
> disk crashes my system should be up. In oracle you can take index
> file system offline and allow the database to work fine.
> I wanted to check if we have a mechanism in SQL Server 2000.
> Thanks always
> Mangesh
But in production you are using RAID 5 which won't apply to your
hypothetical. I personally have no idea whether SQL Server can recover
from a lost drive with a lost filegroup. I assume there is a way.
Whether that's with the help of MS PSS or some technique I don;t know
about I'm not sure. My only question here is why is this hypothetical
even worth considering if your production environment does not resemble
what you describe?
David Gugick
Imceda Software
www.imceda.com
|||Thanks David for sharing the knowledge. I was thinking of adding the
filegroups on one of our other production NON CRITICAL database which has no
RAID.
But I was checking to see if I can achieve any benefit and JUST Curious as
it is a standard practice in Oracle to Create indexes on separate Tablespaces.
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> But in production you are using RAID 5 which won't apply to your
> hypothetical. I personally have no idea whether SQL Server can recover
> from a lost drive with a lost filegroup. I assume there is a way.
> Whether that's with the help of MS PSS or some technique I don;t know
> about I'm not sure. My only question here is why is this hypothetical
> even worth considering if your production environment does not resemble
> what you describe?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

file group question

Hi
I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
They are 4 different disks and different disk controllers.
I do not have a backup of the database. Now I have a situation where I lost
one of the disk containing the index filegroup. I am running in FULL MODE.
I do not care if I loose index file group as it will only slow down the
database.
Is there a way to start the database without the index filegroup?
Can I take it offline or drop it'
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
> They are 4 different disks and different disk controllers.
> I do not have a backup of the database. Now I have a situation where
> I lost one of the disk containing the index filegroup. I am running
> in FULL MODE.
> I do not care if I loose index file group as it will only slow down
> the database.
> Is there a way to start the database without the index filegroup?
> Can I take it offline or drop it'
> Thanks
> Mangesh
Do you think there were any clustered indexes in the database. If so,
where did you put those tables? On one of the table filegroups or one of
the index file groups? The table goes with the clustered index, so if
you built a clustered index on a table and put it on the index drive you
lost, you lost the table as well.
Many here are going to wonder:
Where is your redundancy on your drive subsystem?
Why no backups?
It's pretty risky leaving both out of the equation as you now know.
Going forward I might suggest using two mirrored sets. One for data and
one for temp db and log files.
David Gugick
Imceda Software
www.imceda.com|||Thanks David. This is just a hypothetical case. For production we are using
RAIDa 5.
I am keeping clustered index on the my table file group and it is not broken
.
My non clustered indexes are on separate file group and it is broken.
So I want to have a mechanism by which even if the index file group disk
crashes my system should be up. In oracle you can take index file system
offline and allow the database to work fine.
I wanted to check if we have a mechanism in SQL Server 2000.
Thanks always
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> Do you think there were any clustered indexes in the database. If so,
> where did you put those tables? On one of the table filegroups or one of
> the index file groups? The table goes with the clustered index, so if
> you built a clustered index on a table and put it on the index drive you
> lost, you lost the table as well.
> Many here are going to wonder:
> Where is your redundancy on your drive subsystem?
> Why no backups?
> It's pretty risky leaving both out of the equation as you now know.
> Going forward I might suggest using two mirrored sets. One for data and
> one for temp db and log files.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks David. This is just a hypothetical case. For production we are
> using RAIDa 5.
> I am keeping clustered index on the my table file group and it is not
> broken. My non clustered indexes are on separate file group and it is
> broken.
> So I want to have a mechanism by which even if the index file group
> disk crashes my system should be up. In oracle you can take index
> file system offline and allow the database to work fine.
> I wanted to check if we have a mechanism in SQL Server 2000.
> Thanks always
> Mangesh
But in production you are using RAID 5 which won't apply to your
hypothetical. I personally have no idea whether SQL Server can recover
from a lost drive with a lost filegroup. I assume there is a way.
Whether that's with the help of MS PSS or some technique I don;t know
about I'm not sure. My only question here is why is this hypothetical
even worth considering if your production environment does not resemble
what you describe?
David Gugick
Imceda Software
www.imceda.com|||Thanks David for sharing the knowledge. I was thinking of adding the
filegroups on one of our other production NON CRITICAL database which has no
RAID.
But I was checking to see if I can achieve any benefit and JUST Curious as
it is a standard practice in Oracle to Create indexes on separate Tablespace
s.
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> But in production you are using RAID 5 which won't apply to your
> hypothetical. I personally have no idea whether SQL Server can recover
> from a lost drive with a lost filegroup. I assume there is a way.
> Whether that's with the help of MS PSS or some technique I don;t know
> about I'm not sure. My only question here is why is this hypothetical
> even worth considering if your production environment does not resemble
> what you describe?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>