Database

Mustafa Ismail Mustafa wrote:

My baby son is teething

Mika Wendelius wrote:

what's the biggest pain in this?

Arun Abraham Jose wrote:

Then how do we know what is the value for this flag

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

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'

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?

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