|
I have a survey table where people were asked up 7 question and I'm trying to figure out the best way to assign a primary key in sql. here how it looks like.
key surveyid questionid answid questions answer
ed45894 1 3 1 when was the last time you bought soda 1 week ago
ed45894 1 7 2 How many live in your house 4
ed45894 1 4 5 do you children in your house Yes
ed45894 1 2 7 do you use coupon while shopping No
fs45895 1 3 4 when was the last time you bought soda yesterday
fs45895 1 7 2 How many live in your house 1
fs45895 1 4 5 do you children in your house Yes
fs45895 1 2 7 do you use coupon while shopping No
|
|
|
|
|
You're highly unlikely to overrun an int for surveys.
"Never attribute to malice that which can be explained by stupidity."
- Hanlon's Razor
|
|
|
|
|
learning_new wrote: trying to figure out the best way to assign a primary key in sql
There is only one way to do so; you find the column or columns that uniquely identify a single row in the collection.
If you are having trouble with it, you're not normalizing. Your table should be in 3NF; or in short, you want the key, the whole key, and nothing but the key. Codd's law, look it up.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Can you explain what these columns are ?
Looks like you have enough data to make a primary key, but understanding the data would lead to a suggestion.
![Java | [Coffee]](https://www.codeproject.com/script/Forums/Images/coffee.gif)
|
|
|
|
|
Thanks David. I was able to figure out.
|
|
|
|
|
Say I have this TSQL table
create table Table (
TableId int identity(1, 1) not null,
value nvarchar(max) null,
id0 int null,
id1 int null,
id2 int null
)
and say I make a query like (getting the biggest id2 for a given id0 and id1)
declare @arg0 int = 2
declare @arg1 int = 3
select top 1 id2
from Table
where id0 = @arg0 and id1 = @arg1
order by id2 desc
What would be the better index Index1, or Index2?
create index Index1 on Table (id2) include (id0, id1)
create index index2 on Table (id0, id1, id2)
modified 5-Feb-20 20:52pm.
|
|
|
|
|
Index3. Didn't actually try it, just based it on documentation.
Super Lloyd wrote: create index index2 on Table (id0, id1, id2)
Creates a composite index, based on those three columns, sorted first by id0, and within that group on id1, and within that group on id2.
Super Lloyd wrote: create index Index1 on Table (id2) include (id0, id1)
Creates a index on id2, but adds the (values of) columns id0 and id1 to the index for quick access.
Super Lloyd wrote: What would be the better index Index1, or Index2?
You mean "faster"? Do you mean faster read, or faster write? From what I see, they'd both need to write three fields when writing, since the index needs be updated. They'd both supply the three fields from the index (without accessing the table) when reading. The bigger difference is in the first field of the index, id2 in one, id0 in the other. Both queries seem to cover the query, but the docs point to making a composite of all three fields;
Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient. Which field comes first in that list, is also on MSDN;
List the columns to be included in the composite index, in sort-priority order Soo.. option 2? Maybe option 3, with (id0, id1, id2); you are first looking up on the first two fields, than ordering on id2. That'd be my guess, given the example query and the docs. You could of course create a table and actually test that; MSSQLMS would give you an execution plan with timings.
Why is there an identity field? Wouldn't id0 and id1 simply be your primary key? That's what you are using to locate a unique value in the set; if you're not actively using the identity-field, then inserts would benefit from removing it. If you make id0 and id1 your primary key, then the table will have a clustered index on those fields, meaning the table is physically sorted on those fields. For large tables, that would be actually preffered;
The preferred way to build indexes on large tables is to start with the clustered index and then build any nonclustered indexes So docs are hinting that (for large sets) a clustered index is preferred and autmatically created when defining a PK; but then the ids' can't be NULL. If those ids' represent categories, you may even want to go for a filtered index.
I hope for you that someone posts an answer that simply says the first or the second, without all these details 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Mm.. you seem to recommend Index2 so I am confused by your "Index3" on the first line...
basically this is a table that aggregate a summary from multiple data sources. id0 and id1 identify the datasource and are small number (between 1 to 9 each) and id2 is the original record id in the foreign datasource.
I am interested to speed up read query...
But.. mmm... I realize this particular query is not too important.. and querying by id2 will be used elsewhere... mmmmm.....
Thanks for your feedback and MSDN quotes though! 
|
|
|
|
|
Super Lloyd wrote: Mm.. you seem to recommend Index2 so I am confused by your "Index3" on the first line...
Same as Index2, but with another order of the fields.
Super Lloyd wrote: basically this is a table that aggregate a summary from multiple data sources. id0 and id1 identify the datasource and are small number (between 1 to 9 each) and id2 is the original record id in the foreign datasource.
That makes those three fields a compound primary key.
Super Lloyd wrote: Thanks for your feedback and MSDN quotes though!
My pleasure, and seems they know most about Sql Server 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Index2 is the faster one because index1 will not be used at all except maybe for a full scan.
You might want to create it as;
create index index2 on Table (id0, id1, id2 DESC) But the difference is academical at best. SQL Server can scan in reverse direction after all.
|
|
|
|
|
For that query, definitely index 2.
Think of it like searching a massive address book. You're trying to find the highest house number for anyone called "John Smith".
- Index 1 sorts the addresses by house number, and includes the first and last name. You have to start at the end and scan backwards until you find an entry for "John Smith".
- Index 2 sorts the addresses by first name, then last name, then house number. You can jump straight to the end of the "John Smith" records, and see the highest house number.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
The quote below is from An Introduction to Database Systems, 8th edition, C J Date.
Is that possible systems crash while transferring updates from the physical device to the database?
If yes, since transactions are the unit of recovery, those updates that are written to the database should be undone?
"It is quite possible, for instance, that the system might crash after the COMMIT has been honored but before the updates have been physically written to the database--they might still be waiting in a main-memory buffer and so lost at the time of the crash. Even if that happens, the system's restart procedure will still record those updates in the database: it is able to discover the values to be written by examining the relevant records in the log."
modified 2-Feb-20 13:48pm.
|
|
|
|
|
Not sure if I understand the question correctly, but are you looking for Rollforward? . With rollforward, when the database is recovered, all the logs are reapplied and in the end, everything that is not committed is rolled back. This guarantees a consisted situation even after crash.
If the recovery is interrupted because of a new crash, the recovery is simply started from the beginning next time.
|
|
|
|
|
Many thanks!
Sorry can you please explain this:
If the system crash after the COMMIT has been honored but just part of the updates have been physically written to the database other parts are still waiting in a main-memory buffer, then what will happen? How they know that just part of the data is written?
|
|
|
|
|
It's explained in the text you quoted:
"... the system's restart procedure will still record those updates in the database: it is able to discover the values to be written by examining the relevant records in the log."
|
|
|
|
|
The quote below is from *An Introduction to Database Systems, 8th edition, C J Date*.
Is that possible systems crash while transferring the updates from physical device to database?
"It is quite possible, for instance, that the system might crash after the COMMIT has been honored but before the updates have been physically written to the database--they might still be waiting in a main-memory buffer and so lost at the time of the crash. Even if that happens, the system's restart procedure will still record those updates in the database: it is able to discover the values to be written by examining the relevant records in the log."
modified 2-Feb-20 13:48pm.
|
|
|
|
|
In theory, yes. I would expect modern databases to wait for verification of the write before returning from the commit, which implies reading to verify after the data has been written.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I want to copy the structure of all the tables of one database from one server to another server by using Stored Procedure ( due to automation ) there should be no index, no trigger , no fkr etc
|
|
|
|
|
Nice requirement. You have permission to proceed
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
|
|
|
|
|
Easiest way to copy the structure is to do a backup, initiated from the sproc. You can restore that on whatever server you want.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
For example to transfer the sum of $100 from account 123 to account 456. we want the total number of dollars in accounts 123 and 456 taken together not to change.
Why it would be unreasonable to declare an integrity constraint to that effect?
|
|
|
|
|
That is what transactions are for, wrap the transfer in a transaction if there is an error roll back the transaction. An after process integrity check is not unreasonable but the correct way is with a transaction.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
But the book says it's unreasonable.
|
|
|
|
|
The book should say it is incorrect or redundant and should recommend using a transaction. All books are not 100% correct all the time.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Tara11 wrote: Why it would be unreasonable to declare an integrity constraint to that effect?
Mainly because integrity constraint operate on row-basis. Transactions then again ensure that the operation is completed as a whole even when multiple commands are affected.
But neither of these guarantee that the total sum is unaffected when money is transferred. If this would be a single table operation, a trigger could be utilized to do that check.
modified 2-Feb-20 15:32pm.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
