Click here to Skip to main content
15,076,740 members
Home / Discussions / Database


AnswerRe: How to calculate the daily and weekly averages using sql server or alternate sql softwares Pin
Mycroft Holmes31-Mar-20 12:50
professionalMycroft Holmes31-Mar-20 12:50 
QuestionCreate a new Primary Key on survey table Pin
learning_new25-Feb-20 12:01
Memberlearning_new25-Feb-20 12:01 
AnswerRe: Create a new Primary Key on survey table Pin
Nathan Minier28-Feb-20 6:38
professionalNathan Minier28-Feb-20 6:38 
AnswerRe: Create a new Primary Key on survey table Pin
Eddy Vluggen28-Feb-20 9:48
professionalEddy Vluggen28-Feb-20 9:48 
AnswerRe: Create a new Primary Key on survey table Pin
David Mujica2-Mar-20 3:56
MemberDavid Mujica2-Mar-20 3:56 
GeneralRe: Create a new Primary Key on survey table Pin
learning_new3-Mar-20 4:28
Memberlearning_new3-Mar-20 4:28 
QuestionIndexing question Pin
Super Lloyd5-Feb-20 13:35
MemberSuper Lloyd5-Feb-20 13:35 
AnswerRe: Indexing question Pin
Eddy Vluggen5-Feb-20 15:09
professionalEddy Vluggen5-Feb-20 15:09 
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;

MSDN[^] wrote:
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;

MSDN[^] wrote:
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;

MSDN[^] wrote:
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 Big Grin | :-D
Bastard Programmer from Hell Suspicious | :suss:
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.

GeneralRe: Indexing question Pin
Super Lloyd5-Feb-20 17:38
MemberSuper Lloyd5-Feb-20 17:38 
GeneralRe: Indexing question Pin
Eddy Vluggen6-Feb-20 2:50
professionalEddy Vluggen6-Feb-20 2:50 
AnswerRe: Indexing question Pin
Jörgen Andersson5-Feb-20 22:28
professionalJörgen Andersson5-Feb-20 22:28 
AnswerRe: Indexing question Pin
Richard Deeming6-Feb-20 0:17
mveRichard Deeming6-Feb-20 0:17 
Questionintegrity constraint Pin
Tara112-Feb-20 6:23
MemberTara112-Feb-20 6:23 
AnswerRe: integrity constraint Pin
Wendelius2-Feb-20 8:47
mveWendelius2-Feb-20 8:47 
GeneralRe: integrity constraint Pin
Tara112-Feb-20 9:07
MemberTara112-Feb-20 9:07 
GeneralRe: integrity constraint Pin
markkuk2-Feb-20 23:48
Membermarkkuk2-Feb-20 23:48 
Questiontransaction recovery Pin
Tara112-Feb-20 5:46
MemberTara112-Feb-20 5:46 
AnswerRe: transaction recovery Pin
Eddy Vluggen3-Feb-20 3:00
professionalEddy Vluggen3-Feb-20 3:00 
QuestionCopy structure of all the tables of one database to another server Pin
Member 1473192331-Jan-20 4:58
MemberMember 1473192331-Jan-20 4:58 
AnswerRe: Copy structure of all the tables of one database to another server Pin
phil.o31-Jan-20 5:12
mvephil.o31-Jan-20 5:12 
AnswerRe: Copy structure of all the tables of one database to another server Pin
Eddy Vluggen3-Feb-20 3:01
professionalEddy Vluggen3-Feb-20 3:01 
Questionintegrity constraint Pin
Tara1129-Jan-20 10:36
MemberTara1129-Jan-20 10:36 
AnswerRe: integrity constraint Pin
Mycroft Holmes29-Jan-20 10:56
professionalMycroft Holmes29-Jan-20 10:56 
GeneralRe: integrity constraint Pin
Tara1129-Jan-20 11:05
MemberTara1129-Jan-20 11:05 
GeneralRe: integrity constraint Pin
Mycroft Holmes29-Jan-20 11:21
professionalMycroft Holmes29-Jan-20 11:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.