right candidates of clustered key/index and unique key/index.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

"I like to separate the development tasks or role from the DBA role. "

sure this is our world in here, infrastructure DBA don't do and know how to tune query as they usually don't know how to add/design an index.

"Create a correct data model. Normalize it (1NF-4NF, BCNF, DKNF). Especially: Don't denormalize tables. Performance optimization is prime DBA task."

why don't denormalize table ? if we see too many join then we need to denormalize it, right?
data warehouse solution can have 2nd normal form, right ?

"Unique constraints are part of the model and must be used as required. Every candidate key should be marked by creating a unique constraint for it.
In a normalized model you will not have many tables with more than one candidate key. Thus picking the primary key is obvious."

so primary key is one of the unique key, right ? that's what I am wondering in my other post: https://www.experts-exchange.com/questions/28714594/analyze-query-plan-with-profiler.html?anchorAnswerId=40981333#a40981333

so do you mean creating Unique constraints is better then unique index to make each row unique.

"Use the defaults. Optimize it later.
"

defaults means ?

"So from the development/application designer view point: Just create a correct, complete model.
"

yeah, it make the query later on easier.

"Then you need a clustered index which supports the main usage pattern"

so it is not a heap any more and it can be faster as it has an ordered index anyway ?

"The rest is query tuning by rewriting them and using proper indexing. "

yeah, non-clustered later on, right? but I heard that no matter what non-clustered index is created, it is no used BEFORE clustered index create, right?

as the non-clustered index will make SQL server look back to the cluster index anyway as it is the data page itself?

so if the clustered index not helping much we can ADD (not dropping the primary key) unique index to make it faster, right? so clustered index and unique can be create on the same table ?

"But: In most of this cases it is a modelling problem: Relational modelling (OLTP) vs. dimensional modelling (OLAP). "

yeah, I heard about that, it is the root of everything, right?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

ste5an,

"The task optimizing by denormalization is often an indicator for the fact, that the developer has chosen the wrong model in this place."

so you saw that happen before, right? I am not sure why we can't denormalize it.

"A UNIQUE CONSTRAINT is a logical construct and part of the model, a UNIQUE INDEX is the concrete (physical) implementation used for a UNIQUE CONSTRAINT. It's model vs. implementation detail. As a developer I don't care (not that much) about implementation detail, when I create the model."

I like that, but it doesn't means that if we define the unique constraint on it the unique index will also be create automatically, right? I am wondering on this .

"Whether you need a clustered or heap table depends on the number of row, on the insert,delete and select patterns.
A clustered index may be used instead of an non-clustered index, when for example other values must be read. Here a clustered index seek can be faster than a non-clustered index seek and a key-lookup."

you mean is number of row is small, then heap is good as SQL server can read the whole table faster than using index, right? and we save diskspace too.

so if number of row is small, like 1000-2000 rows, cluster index seek will be faster as SQL server just read the whole table into the buffer pool, right?

"failed at step 2 (make it right). "

what is step 2 I am sorry ?

ScottPletcher,

"This can be confusing if you don't do logical modeling first, before you ever consider physical modeling. That's very common, to skip/ignore logical modeling, even though it's a huge mistake.
"

in here logic modelling is the data modellling, and physical model is the index definition, right?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Additional to what @Scott wrote:

A logical data model has under normal circumstances exactly one physical model. The physical model is a derivation of the logical model.

The reverse process does not exist. You cannot create the logical model from the physical model.

ASKER

ste5an,

"Thus, instead of just implementing this change, start with reviewing the requirements first. This step is necessary for different reasons, like documentation, communication and impact analysis."

tks, very clear and I agree,

I can get most out of this topic by reading this: http://www.amazon.com/gp/product/1449316409?psc=1&redirect=true&ref_=ox_sc_act_title_6&smid=ATVPDKIKX0DER, right?

any another video and link you can suggest which tell me all that jazz so that I can learn the right thing all together ?

" SQL Server itself will create this constraint, by creating an unique index. In reverse creating unique indices as optimization step only happens when there exists an UNIQUE CONSTRAINT on the table and the UNIQUE INDEX is just a different, but equivalent way of expressing it. In other words: I don't create UNIQUE INDEXes often while optimizing (model change!).
"

very clear too, so this means when creating the unique constraint for the model, the unique index will be create automatically FOR THAT PURPOSE, right?

so in what case you experienced before the unique index is not the one you want which create trouble and need query tuning ? but if expressing it different and the SAME also means no need to touch/recreate them, right?

"you mean is number of row is small, then heap is good as SQL server can read the whole table faster than using index, right? and we save diskspace too.

so if number of row is small, like 1000-2000 rows, cluster index seek will be faster as SQL server just read the whole table into the buffer pool, right?"

may I know your options here?

"The real problem is that programmers have spent far too much time worrying about efficiency in the wrong places and at the wrong times"

that's what I am doing now, ask you guys on the right options instead of spending too much time on sth wrong, sorry if I ask too much.

As I keep saying, you can give me a briefing and then show me some GOOD LINK for me to read. this save the time of both of us.

"The reverse process does not exist. You cannot create the logical model from the physical model. "

so must from logical model to physical model .

ScottPletcher,

this books : http://www.amazon.com/gp/product/1449316409?psc=1&redirect=true&ref_=ox_sc_act_title_6&smid=ATVPDKIKX0DER, can give a correct guide on what is logical model and physical model, right?

or other suggestion ?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

"The book is one of the basic ones. I reommend it often."

ok then any intermedia and expert one ? I am sorry I'd like to ask this.

"btw, there is an fourth role when we have incomplete or inconsistent requirements: Project manangment (requirements management). This where we need to get also in conntact with the customer (product owner).":

agree !

"This is all about software development processes"

that's what I am going to look at , but later.

tks for your help.

ASKER

ste5an and ScottPletcher,

any more suggestion on data modeling learning ? video and books ?

ASKER

sorry, any update for me ?