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]

Filegroups

Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second filegroup than the "default" one and put here one of the 2 most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table that cause most problems and the Raid 1 i would put the other table. Any opinions? Any suggestions?
Thanxs
Cl=E1udiaYou should always keep the log on a different drive than the data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Cláudia" <Alphaseusa@.hotmail.com> wrote in message
news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise
performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second
filegroup than the "default" one and put here one of the 2
most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table
that cause most problems and the Raid 1 i would put the
other table. Any opinions? Any suggestions?
Thanxs
Cláudia|||Claudia,
I'd recommend keeping all your data files off of the log files drive.
Putting data files on the same drive as your log files will greatly slow
down access to the log files.
--
Denny Cherry
DBA
GameSpy Industries
"Cláudia" <Alphaseusa@.hotmail.com> wrote in message
news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise
performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second
filegroup than the "default" one and put here one of the 2
most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table
that cause most problems and the Raid 1 i would put the
other table. Any opinions? Any suggestions?
Thanxs
Cláudia|||Claudia
The guys are right about log files, for OLTP, but as you said your's is OLAP which can be different. If you are not updating the database or you are doing it at a time when no-users are on the system and there is a large time window, the impact of the transaction logs being on the same drives as the data is reduced to a level where if you can increase performance, during the time users are on the system by spreading the data more, then don't worry about the log location.
You say you have 3 hard drives, I assume that is at the logical level, bit hard to have a one drive raid 5. The main thing that worries me about your proposed use is you say one drive is raid 1 and one is raid 5 that you propose to put the data on. It is not a good idea to mix the raid levels your data files are on.
If you resolve your raid levels, I see no harm in trying to spread the load.
Regards
John
>--Original Message--
>Claudia,
>I'd recommend keeping all your data files off of the log files drive.
>Putting data files on the same drive as your log files will greatly slow
>down access to the log files.
>-- >Denny Cherry
>DBA
>GameSpy Industries
>"Cl=E1udia" <Alphaseusa@.hotmail.com> wrote in message
>news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
>Question:
>I have 3 hard disks: C, D and E
>C contains OS information (Raid 1).
>I was thinking of doing this ( in order to optimise
>performace of my SQL Server):
>P.S Its an OLAP server
>D: (Raid 1) put all the log files here + create a second
>filegroup than the "default" one and put here one of the 2
>most heavy queried tables
>E: put all the data files in one filegroup (Raid 5)
>I was thinking that in the Raid 5 i would put the table
>that cause most problems and the Raid 1 i would put the
>other table. Any opinions? Any suggestions?
>Thanxs
>Cl=E1udia
>
>.
>|||Claudia
Would be easier if you had 8, but lets see what we can do. I don't know the size of your disks or your database, so this might not work.
I am assuming you are a 'classic' olap data warehousing system and not some kind of hybrid.
Leave the operating system as it is, two disks RAID 1. Configure 4 disks as RAID 0 + 1 and put the databases on there. Despite saying you can put the transaction logs on the same disk, put it on the remaining disk with no RAID. (If you had an extra disk, I would have said 6 disk RAID 0+1 and put the lot on it.
Before anyone says, what no RAID for the logs, I was assuming 'classic' olap where the logs are not used, usually the database is set to simple recovery (or trauncate log on checkpoint for 6.5 or 7). For what I see as 'classic' olap it usually quicker and easier to rebuild the database from source than have a backup/DR plan, so no backups and no logs.
Not enough info to know if this would be suitable, what do you think?
Regards
John
>--Original Message--
>I have 7 hard disks:
>2 -- raid 1
>2 -- raid 1
>3 -- raid 5
>any ideas on how to do it best to optimize performance?
>>--Original Message--
>>Claudia
>>The guys are right about log files, for OLTP, but as you >>said your's is OLAP which can be different. If you are >not >>updating the database or you are doing it at a time when >>no-users are on the system and there is a large time >>window, the impact of the transaction logs being on the >>same drives as the data is reduced to a level where if >you >>can increase performance, during the time users are on >the >>system by spreading the data more, then don't worry about >>the log location.
>>You say you have 3 hard drives, I assume that is at the >>logical level, bit hard to have a one drive raid 5. The >>main thing that worries me about your proposed use is you >>say one drive is raid 1 and one is raid 5 that you >propose >>to put the data on. It is not a good idea to mix the raid >>levels your data files are on.
>>If you resolve your raid levels, I see no harm in trying >>to spread the load.
>>Regards
>>John
>>
>>--Original Message--
>>Claudia,
>>I'd recommend keeping all your data files off of the log >>files drive.
>>Putting data files on the same drive as your log files >>will greatly slow
>>down access to the log files.
>>-- >>Denny Cherry
>>DBA
>>GameSpy Industries
>>"Cl=E1udia" <Alphaseusa@.hotmail.com> wrote in message
>>news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
>>Question:
>>I have 3 hard disks: C, D and E
>>C contains OS information (Raid 1).
>>I was thinking of doing this ( in order to optimise
>>performace of my SQL Server):
>>P.S Its an OLAP server
>>D: (Raid 1) put all the log files here + create a second
>>filegroup than the "default" one and put here one of the >2
>>most heavy queried tables
>>E: put all the data files in one filegroup (Raid 5)
>>I was thinking that in the Raid 5 i would put the table
>>that cause most problems and the Raid 1 i would put the
>>other table. Any opinions? Any suggestions?
>>Thanxs
>>Cl=E1udia
>>
>>.
>>.
>.
>

File numbers in backup files

When i backup, everyday, A db to the same file(using noinit option), how can
I know the nmber of each file in order to be able to restore the the one I
want using with file = 1 or 5 or 6. Is there a sp or a sus table
Thanks
RESTORE HEADERONLY
Also see the backup history tables in the msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:00E117EF-9023-444D-8B3A-2DC8FB4F2F4B@.microsoft.com...
> When i backup, everyday, A db to the same file(using noinit option), how can
> I know the nmber of each file in order to be able to restore the the one I
> want using with file = 1 or 5 or 6. Is there a sp or a sus table
> Thanks
|||Hi,
This is Charles from Microsoft Online Community Support. I'm responsible
for checking the issue status.
Please feel free to let us know if you need further research. It's always
our pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support

File numbers in backup files

When i backup, everyday, A db to the same file(using noinit option), how can
I know the nmber of each file in order to be able to restore the the one I
want using with file = 1 or 5 or 6. Is there a sp or a sus table
ThanksRESTORE HEADERONLY
Also see the backup history tables in the msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:00E117EF-9023-444D-8B3A-2DC8FB4F2F4B@.microsoft.com...
> When i backup, everyday, A db to the same file(using noinit option), how c
an
> I know the nmber of each file in order to be able to restore the the one
I
> want using with file = 1 or 5 or 6. Is there a sp or a sus table
> Thanks|||Hi,
This is Charles from Microsoft Online Community Support. I'm responsible
for checking the issue status.
Please feel free to let us know if you need further research. It's always
our pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support

File numbers in backup files

When i backup, everyday, A db to the same file(using noinit option), how can
I know the nmber of each file in order to be able to restore the the one I
want using with file = 1 or 5 or 6. Is there a sp or a sus table
ThanksRESTORE HEADERONLY
Also see the backup history tables in the msdb database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:00E117EF-9023-444D-8B3A-2DC8FB4F2F4B@.microsoft.com...
> When i backup, everyday, A db to the same file(using noinit option), how can
> I know the nmber of each file in order to be able to restore the the one I
> want using with file = 1 or 5 or 6. Is there a sp or a sus table
> Thanks|||Hi,
This is Charles from Microsoft Online Community Support. I'm responsible
for checking the issue status.
Please feel free to let us know if you need further research. It's always
our pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support

File Groups

Hi everyone,
While creating our database in only one disc(C or D), suppose that we create more than one file group in order to group our data files. However, in this situation; I wonder that whether it brings any benefit or advantage to us.

Also, I wonder that why we always have to put our data file into separate file group if we use separate discs for data files. Is not it allowed to use only one file group even if we use separate dics ?

Would you explain these to me ?

Thanks

If youre on one disk, there's not much difference. If you have 2 disks, putting log on separate disk is the most common and efficient solution in most cases.
For a typical database application, the best configuration of hardware and an average one are not likely to differ dramaticaly in terms of performance. Performance tuning is almost 90% done at SQL and application layers.

But putting log on separate disk always helps ;)

|||

Thanks for your reply but it seems to me a little bit confusing.
Would you mind giving some examples about the efficiency and high level performance of File Groups ?

Thanks

|||

Database transaction typically write some data to the disk, and some data to the log. This means constant log file access for writing. Meanwhile, when there are no failures, this writes can be performed sequentially in large chunks. This dramatically decreases disk access time, because significant part of time that disks need to perform an operation(read or write) consumes "seeking" disk head to the desired position, because this is mechanical operation. Sequential writes avoid seeks.
This is the main thing I could think of given no details. In a rare case if your application is very read-intensive (OLAP), then you might consider partitioning data files among disks to fasten reads.

In a mixed (OLTP) application, consider doing the following, depending on how many disks you have:

A separate disk for all log files, maybe with redundancy(mirroring).
Then, consider separating secondary (non-clustered) indexes from data files.
Then consider separating data files.

|||

Hi,
First of all, thanks for your all replies and thanks for spnding your time for my questions.
I think that I finally kept the idea about the efficiency and high level performance which I want to demonstrate.
For instance, suppose that we recall some records from two different table in one databases and we do this by using inner join. And if we put this two table into different file groups which are located in different discs, then it is really more efficient to call the column of these tables than other situation, is not it ?

Briefly, we do this application in order to share the whole power to the to the discs so that at one time, we can do many things faster than before.

Am I right or wrong ?

Thanks

|||

merit,
Your scheme of splitting data among disks works perfectly with you example provided, that is, when overhelming part of transactions are read-only. Otherwise, if you have read-write (OLTP) application and only 2 disks, just put the data in one disk and log to the other.

|||

Hi,

Again thanks for your reply.

While I was reading a tutorial abou this topic, I heard that The CREATE DATABASE statement is a multithreaded process that creates one process thread for each logical device.

What does this mean ?

Would you please explain it to me ?

|||

Hi,

Another good and significant example about the efficiency of using separate file group in different discs is SELECT INTO statement. Because, while performing this , you both read data from a table and then write this data to another table. Thus, if we try to use different discs for both reading data and writing data, we will have increased the performance and speed of the program.

Menawhile, the another reason for this is that while performing some operations, every disc are used by the system at the same time, is not it ?

Thanks,

Mert

|||

I guess not.

The main point is that, as I mentioned earlier, when you write into some table, you simultaneously write into log too. In this case, it's much better to put data files to one disks and put log to separate disk. Never forget that log is intensively used during write transactions. In your example, you completely do not consider log writes.

|||

Hi,
You suggest me to put log to different discs which I also appreciate since it is obvious that this brings performance benefit to the dics.
But at the following link , it supports what I said about SELECT INTO.
Reads from one discs and write to another table which is located in different file group in different disc, so do you really think that this does not increase the performance ?
Suppose that you performs both operations from the same discs. So while doing this, it is likely that your disc drive spends more power to do it and more time to do it.
So I would like to learn the main reason why you think that only, putting log files into different disc can bring high level performance.

http://www.dell.com/content/topics/global.aspx/power/en/ps2q03se_rosetta?c=us&cs=555&l=en&s=biz

Thanks,
Mert

|||

That's all correct.
In the link you provided those guys featured the following disk configurations:

<Quote>Storage consisted of two EMC^? CLARiiON^? FC4700 storage arrays and two EMC ^? 8530 storage arrays. Each of the EMC CLARiiON FC4700 storage arrays had a 4 GB write-read cache, while each EMC Symmetrix 8530 storage array had a 16 GB write-read cache. Each of the four storage arrays included 96 disks capable of 73 GB and 10,000 rotations per minute (RPM), for a total of 28 TB of raw disk space (12 TB after RAID-1+0 configuration).<Quote>

It is obvious that they had put log into other drive too, see it here:

<Quote>The Rosetta Genomics application database files totaled 10.35 TB for data files and 135 GB for the log file. Administrators defined both a primary, or default, file group and a user-defined file group. The default file group had one data file containing database system objects. Located on the default drive C, the default file group shared drive space with SQL Server binaries and the operating system. The user-defined file group was configured with 40 fixed-size 131 GB data files on CLARiiON arrays and 40 fixed-size 135 GB data files on Symmetrix arrays, for a total of 80 data files?one for each LUN designated for database file storage. Mount points were configured at 132 GB on CLARiiON arrays and 136 GB on Symmetrix arrays.<Quote>

Indeed, if you have SUCH storage capacitites, you can put everything on different drives. In my earler post I wrote that if you have ENOUGH disks you can split data files too. Anyhow, given TWO disks and a WRITE-INTENSIVE application, it is almost always the best practice to separate log from data files.
Once again, if you have such opportunity, indeed, split data files too! I was all about priorities, and the wonderful article you provided says nothing about putting log into one disk and distributing data.
I'd reccomend you the wonderful book of D. Shasha and P. Bonnet, "Database Tuning" for very thorough discussion of the topic.
http://www.amazon.com/gp/product/1558607536/sr=8-1/qid=1151403308/ref=pd_bbs_1/103-2557871-6722269?ie=UTF8

P.S.
Applications with extremely large data storage requirements very often mean that the application is OLAP one, that is, a read-intensive application, when everything I said should be reversed. In those applications, log file is no more a hot spot.

P.P.S. merit, I am just too curious not to ask, is your disk configuraton something (even remotely) close to the configuration in the article?

|||

Thanks for your interest to this thread,

Andranik Khachatryan

For your question, I ask everything since I am also curious about them, not for application on my computers or any problem that I encounter.

Meanwhile, I am studying to the MCAD exam that's why I ask lots of questions recently.

Best wishes,

Mert

|||

Hi,
There something make me still confused about this topic.
For example, when we call some columns from a table with Select query statement, do the SQL server do this by first finding the file group that this table located in and then finding the corresopnding table ?

Thanks