|
Kindly Let Me know the maximum Value which is recommended for the rows in a particular table.
Some of my tables have more than 60 lakhs rows..
Kindly Suggest??????????
Thanks in Advance...
|
|
|
|
|
There is no real limit, it is all dependant on disk space and hardware performance. You may want to look at partitioned tables for performance. Also when you get to these volumes of data indexing is crucial.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
As many as you need, and no more.
|
|
|
|
|
http://msdn.microsoft.com/en-us/library/ms143432.aspx
|
|
|
|
|
I have several multi-selection options and its becoming a pain to handle them in a n-n fashion, does SQL2K5 offer an alternative that I might use?
I'll give you an example. I have a patient and a patient can have several symptoms.
This would be the exact opposite of sending an array to the database, it'll be bringing back an array.
|
|
|
|
|
So you have tables like the following?
Patient
- PatientId (PK)
- Name
Symptom
- SymptomId (PK)
- Description
PatientSymptom
- PatientId (FK)
- SymptomId (FK)
If so, what's the biggest pain in this?
|
|
|
|
|
That is exactly how it is only you would have a "versioning" of it for the patient. So that for each admittance (the one you helped me out with this morning  ) the patient can and most likely will have different symptoms. I just find it painful that it has to go like this, it sorta like sawing against the grain. Implementation wise its not difficult and one can easily knock up a view to end up with a comma separated list of symptoms.
I don't know, I guess I'm just complaining.
|
|
|
|
|
Could you post any example what operation is painful or is it querying or something else? That would help to understand the problems and to give suggestions.
Based on our previous conversation I understood that you don't use surrogate keys (identity for example). If that's correct, perhaps the problems are related to that.
|
|
|
|
|
I'm just whining because I had little sleep last night. My baby son is teething.
|
|
|
|
|
Mustafa Ismail Mustafa wrote: My baby son is teething
I remember those nights. Luckily it was always one or two nights at a time with both of my kids. If it helps you in anyway, it goes over soon. And then again if you start counting the good sides and the bad sides of having children, there's no question which count is higher
I wish you strength and hopefully you get a good night sleep after this day. Perhaps we continue searching another data modeling solution for you another day
|
|
|
|
|
Yeah, he's a bundle of joy alright when the poor kid is not in pain.
|
|
|
|
|
Mika Wendelius wrote: what's the biggest pain in this?
(patient=OP, symptom=Headache)
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
It took me awhile to get this since I had completely forgotten the context... After reading the original post I got it. Think I'm 
|
|
|
|
|
I would like to rebuild the indexes of my database by using the query 'ALTER INDEX ALL'. With this I would like to set the option SORT_IN_TEMPDB to ON. But before that I want to know the value set for this particular flag in the table indexes. How can I get the current value of this flag?
I tried using the tables like sys.indexes, sys.stats, sys.schemas etc but miserably failed.
Please advice
With Regards,
Abraham Chethuvelil
|
|
|
|
|
AFAIK this information isn't stored when index is created or recreated.
|
|
|
|
|
Hi,
Then how do we know what is the value for this flag?
I want to set it on in our database now but I am not sure whether the people who created or maintained this database before were already set it or not?
Another doubt regarding setting this flag is that, will it cause any issues in the future w.r.t. Size or performance and w.r.t. Tempdb as well?
With Regards,
Abraham Chethuvelil
|
|
|
|
|
Arun Abraham Jose wrote: Then how do we know what is the value for this flag
I don't think it's possible to say afterwards if an index is created with SORT_IN_TEMPDB On or Off. Since this option affects only when the index is created, it isn't necessary information for SQL Server when index is used.
Arun Abraham Jose wrote: I want to set it on in our database now but I am not sure whether the people who created or maintained this database before were already set it or not
Since this isn't found in the system tables the only thing that comes in mind is that you create a standard that you add an extended property to your indexes. In this property you can document what option was used when the index was created.
Property can be set for example like this:
EXEC sys.sp_addextendedproperty
@name=N'SORT_IN_TEMPDB',
@value=N'True' ,
@level0type=N'SCHEMA',
@level0name=N'DatabaseName',
@level1type=N'TABLE',
@level1name=N'TableName',
@level2type=N'INDEX',
@level2name='IndexName'
After adding this property you can see it's value in system tables and in Management Studio.
Arun Abraham Jose wrote: Another doubt regarding setting this flag is that, will it cause any issues in the future w.r.t. Size or performance and w.r.t. Tempdb as well?
SORT_IN_TEMPDB affects only index creation. It does not affect how the index behaves after creation so it's creation time option only.
|
|
|
|
|
OK, I'm not a DB expert but I wanted to do the following and I'm not sure how to go about it. I googled but I think I'm using the wrong name for it.
I have a table that has a foreign key (PatientID) and there is an ID that is Patient relative called AdmittanceID. Now for the sake of clarity I'll give an example of what I want done.
Imagine two patiens, 10 & 20
{Table}
PatientID AdmittanceID
10 01
20 01
10 02
now, the AdmittanceID would autoincrement for each distinct PatientID. I'm stumped as to how to do this in SPs.
Suggestions?
|
|
|
|
|
You could do this with triggers. Depending on the business rules you would have a bit different implementations. For example if new record gets the maximum value for patient + 1, you would have something like:
CREATE TRIGGER SomeTriggerName
ON YourTableName
AFTER INSERT
BEGIN
DECLARE @keyValue int;
DECLARE @patientID int;
DECLARE newRows CURSOR
FOR SELECT PrimaryKeyColumnName, PatientId
FROM inserted;
--
OPEN newRows;
FETCH NEXT FROM newRows INTO @keyValue, @patientId;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE YourTableName
SET AdmittanceID = (SELECT MAX(AdmittanceID)
FROM YourTableName
WHERE PatientID = @patientId)
WHERE YourTableName.PrimaryKeyColumn = @keyValue;
--
FETCH NEXT FROM newRows INTO @keyValue, @patientId;
END
CLOSE newRows;
DEALLOCATE newRows;
END
And if you need that the patientid does not have gaps, you would create a delete trigger where you reorder numbering for records having the same patientid as deleted row(s).
|
|
|
|
|
Brilliant!
Thanks Mika, that is exactly what I needed!
|
|
|
|
|
You're welcome
|
|
|
|
|
Quick question.
PatientID is a key, but the primary key would be the combined PatientID and the AdmittanceID, this shouldn't affect the implementation correct? Also, why do I need the cursor? Shouldn't the quick select statement in addition to incrementing the value by 1 be sufficient?
There shouldn't be gaps because the values increment for each patient, and each patient's visit is mutually exclusive of the other unless my implementation is way wrong.
[edit]
The way I see it, it can't be done as such, it needs the SP that's doing the insert to find the last admittanceID, increment that and insert it with the rest of the data.
Last modified: 17mins after originally posted --
|
|
|
|
|
Mustafa Ismail Mustafa wrote: the primary key would be the combined PatientID and the AdmittanceID, this shouldn't affect the implementation
Actually it does. If it's defined as primary key, you cannot leave it null in the insert statement. Also you cannot insert duplicates. One way could be that you use negative patientid in the insert statement and this trigger modifies the values to correct ones. In that case the where clause would be something like:
...
WHERE (AdmittanceId, PatientId) = (@OldAdmittanceId, @PatientId);
Also it would be benefitial to have a surrogate key if none is present at the moment. That would make this operations easier. You could change the current primary key to unique key and define the surrogate key as primary key.
Mustafa Ismail Mustafa wrote: Also, why do I need the cursor
I didn't quickly have any other idea. The problem is that the trigger fires only once when rows are added regardless how many rows are added by the same statement. In other words the trigger isn't executed separately for each inserted row. Now if you add simultaneously two rows for the same patientid and you simply execute an update which sets the patientid to current max in db + 1 those two rows would have the same value for the patientid.
Mustafa Ismail Mustafa wrote: There shouldn't be gaps because the values increment for each patient
Ok, but what I meant is that is it ok to delete a record later? If it is, I think you would have a gap in patientid numbering. Don't know if it's any problem though.
|
|
|
|
|
I ended up doing this:
<br />
@PatientID int, <br />
@AdmittanceID int, <br />
@DateAdmitted datetime = GETDATE,<br />
@NurseNotes int,<br />
@DoctorNotes int,<br />
@MiscNotes int<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
<br />
SET @AdmittanceID = (SELECT MAX(EMR.PatientHistory.AdmittanceID) <br />
FROM EMR.PatientHistory <br />
WHERE EMR.PatientHistory.PatientID = @PatientID);<br />
<br />
SET @AdmittanceID = @AdmittanceID + 1;<br />
<br />
INSERT INTO EMR.PatientHistory<br />
(PatientID, AdmittanceID, DateAdmitted, NurseNotes, DoctorNotes, MiscNotes)<br />
VALUES<br />
(@PatientID, @AdmittanceID, @DateAdmitted, @NurseNotes, @DoctorNotes, @MiscNotes)<br />
END<br />
|
|
|
|
|
This one is also fine.
If you want, you can combine those statements to a single statement like:
INSERT INTO EMR.PatientHistory
(PatientID, AdmittanceID, DateAdmitted, NurseNotes, DoctorNotes, MiscNotes)
SELECT @PatientID,
MAX(EMR.PatientHistory.AdmittanceID) + 1
@DateAdmitted,
@NurseNotes,
@DoctorNotes,
@MiscNotes
FROM EMR.PatientHistory
WHERE EMR.PatientHistory.PatientID = @PatientID
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
