define composite primary key with more than 900 bytes

Question

define composite primary key with more than 900 bytes

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply

anbillava Ten Centuries Points: 1146 More actions · Answer 1

But i am getting below error for one of the column which has 82 columns

Cannot create index or statistics 'IX_LABREPORT' on table 'LABREPORT' because the computed column 'cs_LABREPORT' is imprecise and not persisted. Consider removing column from index o

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

You will need to provide DDL, and index create statements. Otherwise we are just taking a shot in the dark.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

anbillava Ten Centuries Points: 1146 More actions · Answer 3

The table look like this

TableName : Test

Here are the Columns

idTest - Unique column

idPatNum - Patient Id number

idVisit - Visit ID

col1 - Questionnaire 1 which will be asked by patient and the value stored here

col2 - Questionnaire 2 which will be asked by patient and the value stored here

col3 - Questionnaire 3 which will be asked by patient and the value stored here

,........

There could be many more columns as i said in one table there are more than 80 columns

The data here is entered by an application. Condition here is that For a patient id and visit id all the columns should have unique values. If user enters same data it should throw a error.

I hope this is clear for you.

Ben Teraberry SSChampion Points: 11815 More actions · Answer 4

So you want the same patient to be able to take the same test on the same visit multiple times so long as they provide different answers? That doesn't really make sense, but I maybe I don't understand something.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

anbillava Ten Centuries Points: 1146 More actions · Answer 5

It should be possible. Its a requirement. This feature is called as double data entry and through this if another user enters same data then it should tell him that this data already exisits. Initially the data will be entred in a paper later some data entry person will enter the data.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

anbillava (4/15/2010)
It should be possible. Its a requirement. This feature is called as double data entry and through this if another user enters same data then it should tell him that this data already exisits. Initially the data will be entred in a paper later some data entry person will enter the data.

If the data is being entered manually, you still have the problem of potentially entering the same data for that patient more than once.

A better solution would be to have a constraint to uniquely identify the patient. Once the patient is identified, you have a questionnaire that can also be uniquely identified and then tied to the patient. Your application would then screen pop the data entry person and notify them that the patient exists and that a survey is present. The data entry person validates a few fields - not every single field, and then either creates a new entry or moves on to the next patient.

80 fields as a constraint is trouble waiting.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

anbillava Ten Centuries Points: 1146 More actions · Answer 7

Thanks for the suggestion.

But i need to create a feature to the user through which user should not be doing any manual validation of the fields. There wil two differet users. They will be entereing data seperately. There should be a reconcilliation feature through which user can select which is the suitable data.

Any idea how to achieve this ?

Ben Teraberry SSChampion Points: 11815 More actions · Answer 8

So there could be a situation where the same person answers the same questions for the same visit in two different ways and you want to enter both sets of results for that same patient for that same visit in your database?

Also, even if this makes sense, I would be concerned with typos given the use of manual entry. With what you are describing you want, if the data entry people have any typos, your system will not prevent the unintended duplication of data. Manual entry is always a problem, but by extending your constraint over such a large number of fields, the chances of entry errors invalidating the constraint are greatly increased.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

anbillava Ten Centuries Points: 1146 More actions · Answer 9

Is there any way to improve this.

I was thinking of creating two different tables and consolidating the data and make sure that which is the right data ?

SQLRNNR SSC Guru Points: 281324 More actions · Answer 10

Without knowing your structure, no.

With users manually entering data, you are introducing an error level that would nearly negate the need for an automated mechanism. How can you guarantee that a human will enter the exact data for each and every field from a survey? Furthermore, how can there be two paper copies of the same questionnaire unless a human made copies of the questionnaire?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Trey Staker SSCarpal Tunnel Points: 4766 More actions · Answer 11

anbillava (4/15/2010)
It should be possible. Its a requirement. This feature is called as double data entry and through this if another user enters same data then it should tell him that this data already exisits. Initially the data will be entred in a paper later some data entry person will enter the data.

I don't think this would even work if you were trying to compare 2 datafields that were test answers.

Lets say you have a question like "Please comment on your experience today" this gets put into a column col1 when the patient enters it into the computer as:

"My experience today was lovely Dr. SomeDoctor was Helpful" and a patientid 12345

Human factor of entering the data comes into play when the paper is manually entered the data processor may key in:

"My Experience today was lovely het Dr. was helpfull" with the same patientid 12345

both of those would be entered into the database and would be essentially duplicates, correct?

What you're trying to do is put 80 fields of this type of stuff together. You really need a better way to build a unique key and not do it on text data.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

anbillava Ten Centuries Points: 1146 More actions · Answer 12

Just to clear it again

There will be only one paper.

First this data will be entered by one user (First pass). He might do some mistakes in entering.

Later the same data will be entered by another user (Second pass)

At the end with reconcilliation feature these data will be listed in the application and the discrepancies will be resolved.

Ben Teraberry SSChampion Points: 11815 More actions · Answer 13

IMO opinion then your primary key should be a composite of:

ID of person entering +

patientID +

visitID

You can just run SQL queries to generate reports on where the same patient/visit had different results entered by different data entry people. Furthermore, if this double-checking of data is really what you're after you can report on patient/visits that were only entered once (and therefore not double-checked.)

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

WayneS SSC Guru Points: 95405 More actions · Answer 14

bteraberry (4/15/2010)
Manual entry is always a problem, but by extending your constraint over such a large number of fields, the chances of entry errors invalidating the constraint are greatly increased.

Do you mean decreased?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281324 More actions · Answer 15

If you must re-enter the data, then load the data into the application and have the application do a checksum on each field and immediately alert to differences or not permit the second person to enter something different. That is a lot easier to do than the method you are pursuing.

Same principle as when you have to retype your password in an online application to confirm. You could even just leave that to one person - just type everything twice. It wouldn't make a lot of difference to have one person do it over having two people doing it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events