I am in a Process of designing database deployment layout for a system.
There are 4 major subsystem in the application each uses its own database.
Database :
1) Primary DB
2) Processing A DB
3) Reporting DB
4) Processing B DB
The Purpose of Primary is to hold all the Input data in unmodified state.
Processing A DB is a Exact copy of Primary DB.
In Processing A DB the the computation & Validation happens .The Results (
More than one Table )of Processing A is updated in Primary DB.
This Updates needs to be transfered to Reporting DB , Processing B DB.
In Processing B DB some status column is updated and this Updates needs
to be propogated back to the Primary
I am Looking at transactional Replication as a Option for this . Let me
know your views on this
You are trying to do work flow. Replication is not an easy fit with this.
Basically with a replication solution you need a server which will act as
the clearing house figuring out what goes where. With sql2000 you do not
have this - with SQL 2005 you sort of do with the peer to peer replication
model with transactional replication.
It seems that your data flow is this
Primary -> Processing A -> Reporting
-> Processing B -> Primary
Primary <- (with filtering) Processing A
By what criteria does data flow from Processing A back to Primary and how
are you going to prevent data from Processing B flowing back to Processing
A?
If you can filter this data, you could use transactional replication from
one node to another. If you can't I don't think transactional replication
will be a good fit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:DF063537-EE93-4742-AFBB-8BBAF557CEE4@.microsoft.com...
> I am in a Process of designing database deployment layout for a system.
> There are 4 major subsystem in the application each uses its own
database.
> Database :
> 1) Primary DB
> 2) Processing A DB
> 3) Reporting DB
> 4) Processing B DB
>
> The Purpose of Primary is to hold all the Input data in unmodified
state.
> Processing A DB is a Exact copy of Primary DB.
> In Processing A DB the the computation & Validation happens .The Results (
> More than one Table )of Processing A is updated in Primary DB.
> This Updates needs to be transfered to Reporting DB , Processing B DB.
> In Processing B DB some status column is updated and this Updates needs
> to be propogated back to the Primary
> I am Looking at transactional Replication as a Option for this . Let me
> know your views on this
>
|||Thanks for your response.
Well I have a Status Column on the Primary field based on which i Push the
(Set of tables) data to the Processing A DB .
Process A DB does some computation and Updates Data & the staus field on th
Tables .
My Question is whats the Best way of Pushing this data back(from Processing
A) to the Primary server .
There are around 30 table which gets updated .
I am currently looking at DTS/ Transactional Replication to move the
tables back to the Primay DB. I feel there would be Cyclic Updates ?
any advice on this
Best Reagrds
"Hilary Cotter" wrote:
> You are trying to do work flow. Replication is not an easy fit with this.
> Basically with a replication solution you need a server which will act as
> the clearing house figuring out what goes where. With sql2000 you do not
> have this - with SQL 2005 you sort of do with the peer to peer replication
> model with transactional replication.
> It seems that your data flow is this
> Primary -> Processing A -> Reporting
> -> Processing B -> Primary
> Primary <- (with filtering) Processing A
> By what criteria does data flow from Processing A back to Primary and how
> are you going to prevent data from Processing B flowing back to Processing
> A?
> If you can filter this data, you could use transactional replication from
> one node to another. If you can't I don't think transactional replication
> will be a good fit.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:DF063537-EE93-4742-AFBB-8BBAF557CEE4@.microsoft.com...
> database.
> state.
>
>
|||I think you want to replicate these tables back, but ensure that the
replication stored procedures have logic built in them to only execute is
the status column is an update.
For instance going from primary to processing a the status column might have
a value of 0, when processed on A it is updated to 1.
In your update replication procs have a wrapper which looks like this going
back to Primary.
if StatusColumn=1 then
do work
else
return 0
In your update replication procs have a wrapper which looks like this going
to PRocessing A.
if StatusColumn=0 then
do work
else
return 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:2242A1DF-2047-4C2B-A8F6-A63835767DF5@.microsoft.com...
> Thanks for your response.
> Well I have a Status Column on the Primary field based on which i Push the
> (Set of tables) data to the Processing A DB .
> Process A DB does some computation and Updates Data & the staus field on
th
> Tables .
> My Question is whats the Best way of Pushing this data back(from
Processing[vbcol=seagreen]
> A) to the Primary server .
> There are around 30 table which gets updated .
> I am currently looking at DTS/ Transactional Replication to move the
> tables back to the Primay DB. I feel there would be Cyclic Updates ?
> any advice on this
> Best Reagrds
>
> "Hilary Cotter" wrote:
this.[vbcol=seagreen]
as[vbcol=seagreen]
replication[vbcol=seagreen]
how[vbcol=seagreen]
Processing[vbcol=seagreen]
from[vbcol=seagreen]
replication[vbcol=seagreen]
system.[vbcol=seagreen]
Results ([vbcol=seagreen]
DB.[vbcol=seagreen]
needs[vbcol=seagreen]
me[vbcol=seagreen]
|||Thanks Hilary
Well I have not yet decided on the mechanism to updating the data back to
primary
Server .
I am considering to create a Publication on the Processing A server and
let Primary server subscribe this datas ( seems to me a cyclic and bit over
head) .
Note : there will be more that 30 table updates which needs to be send
back-forth
do you have any suggesstion
"Hilary Cotter" wrote:
> I think you want to replicate these tables back, but ensure that the
> replication stored procedures have logic built in them to only execute is
> the status column is an update.
> For instance going from primary to processing a the status column might have
> a value of 0, when processed on A it is updated to 1.
> In your update replication procs have a wrapper which looks like this going
> back to Primary.
> if StatusColumn=1 then
> do work
> else
> return 0
> In your update replication procs have a wrapper which looks like this going
> to PRocessing A.
> if StatusColumn=0 then
> do work
> else
> return 0
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:2242A1DF-2047-4C2B-A8F6-A63835767DF5@.microsoft.com...
> th
> Processing
> this.
> as
> replication
> how
> Processing
> from
> replication
> system.
> Results (
> DB.
> needs
> me
>
>
|||it will be cyclic unless you can figure out some criteria to use as a basis
for not applying the transaction. Use this criteria as a basis as to whether
the proc applies the data on the subscriber/publisher or not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:127378BB-BBAB-4F77-9A10-817170920414@.microsoft.com...
> Thanks Hilary
> Well I have not yet decided on the mechanism to updating the data back to
> primary
> Server .
> I am considering to create a Publication on the Processing A server and
> let Primary server subscribe this datas ( seems to me a cyclic and bit
over[vbcol=seagreen]
> head) .
> Note : there will be more that 30 table updates which needs to be send
> back-forth
> do you have any suggesstion
>
> "Hilary Cotter" wrote:
is[vbcol=seagreen]
have[vbcol=seagreen]
going[vbcol=seagreen]
going[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
?[vbcol=seagreen]
act[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
unmodified[vbcol=seagreen]
B[vbcol=seagreen]
Updates[vbcol=seagreen]
Let[vbcol=seagreen]
|||Thanks for the info .
Can you please suggest me a mechanism for updatating the data back to the
primary server from the Processing A DB .
I am planning to use replication from Processing A back to the primary
server ... any suggestion here
Raj
"Hilary Cotter" wrote:
> it will be cyclic unless you can figure out some criteria to use as a basis
> for not applying the transaction. Use this criteria as a basis as to whether
> the proc applies the data on the subscriber/publisher or not.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:127378BB-BBAB-4F77-9A10-817170920414@.microsoft.com...
> over
> is
> have
> going
> going
> the
> on
> ?
> act
> not
> and
> unmodified
> B
> Updates
> Let
>
>
|||replication with custom stored procedures.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:501BF2AB-FF83-4EE4-9927-F80606684F85@.microsoft.com...[vbcol=seagreen]
> Thanks for the info .
> Can you please suggest me a mechanism for updatating the data back to the
> primary server from the Processing A DB .
> I am planning to use replication from Processing A back to the primary
> server ... any suggestion here
> Raj
> "Hilary Cotter" wrote:
basis[vbcol=seagreen]
whether[vbcol=seagreen]
back to[vbcol=seagreen]
and[vbcol=seagreen]
bit[vbcol=seagreen]
execute[vbcol=seagreen]
might[vbcol=seagreen]
this[vbcol=seagreen]
this[vbcol=seagreen]
Push[vbcol=seagreen]
field[vbcol=seagreen]
the[vbcol=seagreen]
Updates[vbcol=seagreen]
with[vbcol=seagreen]
will[vbcol=seagreen]
you do[vbcol=seagreen]
Primary[vbcol=seagreen]
Primary[vbcol=seagreen]
replication[vbcol=seagreen]
for a[vbcol=seagreen]
own[vbcol=seagreen]
..The[vbcol=seagreen]
DB.[vbcol=seagreen]
Processing[vbcol=seagreen]
this .[vbcol=seagreen]
Showing posts with label design. Show all posts
Showing posts with label design. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Indexing in SQL Server Star Scheme Data Warehouse
Hi all,
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
VThe indexing looks fine, but as to whether this is a good design or not you only need to check my sig below to get my opinion...|||Thanks Blindman.
The one issue that we are encountering is, we didnt create a separate time dimension (a mistake in design). We have a smalldatetime field (72 distinct values only, one for each month, so 6 years in total) in the fact table.
We are not able to query this smalldate time field efficiently because we didnt index it (as it was not part of the dimension). We would like to change the design now.
We would like to create a time dimension using the following:
1. Create Time Dimension Table
2. Create new column Time_Key in Fact Table
3. Create Non clustered Index on smalldatetime field in fact table.
4. Join on smalldatetime fields in Time Dimension and Fact table to populate time_key in 2 from Time Dimension table.
5. Drop the index and column of smalldatetime field in fact and reassign non clustered index to Time_Key (FK)
Let me know how the above approach sounds to you guys.
V|||My gut feeling is that creating and dropping the temporary index on the smalldatetime column will take as long as doing a non-indexed join. Generally, indexes are only valuable because they are used more than once, so the investment involved in creating them is saved over each subsequent operation.
An index on a set of 72 discreet values may not even give you much performance boost across millions of records.|||I suppose the question I would pose to you, more than your design, would be, have you chosen the right granularity for your fact table? I haven't seen too many fact tables that stop at a monthly level unless they are being used for forecasting or budgeting purposes and even then they align to pre-existing warehouses, like a sales warehouse. My best recommendation would be to take some time and study warehousing and ensure you are providing a solution that isn't going to have to be reworked a couple of months down the road when the end users want to be able to drill down into details.
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
VThe indexing looks fine, but as to whether this is a good design or not you only need to check my sig below to get my opinion...|||Thanks Blindman.
The one issue that we are encountering is, we didnt create a separate time dimension (a mistake in design). We have a smalldatetime field (72 distinct values only, one for each month, so 6 years in total) in the fact table.
We are not able to query this smalldate time field efficiently because we didnt index it (as it was not part of the dimension). We would like to change the design now.
We would like to create a time dimension using the following:
1. Create Time Dimension Table
2. Create new column Time_Key in Fact Table
3. Create Non clustered Index on smalldatetime field in fact table.
4. Join on smalldatetime fields in Time Dimension and Fact table to populate time_key in 2 from Time Dimension table.
5. Drop the index and column of smalldatetime field in fact and reassign non clustered index to Time_Key (FK)
Let me know how the above approach sounds to you guys.
V|||My gut feeling is that creating and dropping the temporary index on the smalldatetime column will take as long as doing a non-indexed join. Generally, indexes are only valuable because they are used more than once, so the investment involved in creating them is saved over each subsequent operation.
An index on a set of 72 discreet values may not even give you much performance boost across millions of records.|||I suppose the question I would pose to you, more than your design, would be, have you chosen the right granularity for your fact table? I haven't seen too many fact tables that stop at a monthly level unless they are being used for forecasting or budgeting purposes and even then they align to pre-existing warehouses, like a sales warehouse. My best recommendation would be to take some time and study warehousing and ensure you are providing a solution that isn't going to have to be reworked a couple of months down the road when the end users want to be able to drill down into details.
Friday, March 9, 2012
Indexes versus views
I am currently taking a class, SQL SErver 2000 Database Design and
Implementation (70-229) and have just finished reading about indexes. In
terms of their primary use, these seem closely related to 'views'. I am
unable to find any research on how to know when to use a 'view' versus an
'index'. I was disappointed that the text did not compare the two for
contrast. Can someone explain or direct me to information regarding their
differences?Indexes and views don't really have anything in common.
A view is a stored query that can be accessed like a table.
An index is sorted list of columns from your table (or indexed view, but
thats a different topic). This sorted list allows the database to quickly
locate a particular value in the indexed columns, and includes a pointer to
the actual row in the table. Indexes are used to speed up searches.
Unique indexes not only speed up searches but prevent duplicate values from
being inserted into the table.
A search on Google (or any good database book) will turn up much more in
depth explanations.
"a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
> I am currently taking a class, SQL SErver 2000 Database Design and
> Implementation (70-229) and have just finished reading about indexes. In
> terms of their primary use, these seem closely related to 'views'. I am
> unable to find any research on how to know when to use a 'view' versus an
> 'index'. I was disappointed that the text did not compare the two for
> contrast. Can someone explain or direct me to information regarding their
> differences?|||Did the instructor explain these two concepts? They are nothing at all
alike, and their use is quite different, so I would be surprised to see a
section comparing the two. It would be like having a section explaining the
difference between tables and SELECT statements.
An index is a physical structure that contains pointers to the data and
helps find the data you are looking for more quickly. In general, you don't
'use' an index. You create the indexes you need, and SQL Server chooses
whether or not to use them to help get to the data you are looking for.
A view is a way to save a SELECT statement so you don't have to retype it
every time, and can use the data returned by the view as if it were a table.
If there are indexes on the table that your view is based on, they can be
used exactly as if you were using the full underlying SELECT instead of
using the view.
You can also build indexes on views, but that is a whole separate big topic.
Have you asked the instructor this question?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
>I am currently taking a class, SQL SErver 2000 Database Design and
> Implementation (70-229) and have just finished reading about indexes. In
> terms of their primary use, these seem closely related to 'views'. I am
> unable to find any research on how to know when to use a 'view' versus an
> 'index'. I was disappointed that the text did not compare the two for
> contrast. Can someone explain or direct me to information regarding their
> differences?|||There are indexes, there are views, and there are indexed views, so
lets take them one by one.
An index is used for accessing a table efficiently. It may also serve
to enforce a UNIQUE constraint. It is a physical, rather than logical
concept. Dropping an index may result in a query running slower, but
it should never change the results a query returns.
A view is, in contrast, strictly logical. The result set from any
SELECT command is (effectively) a table; a view simply provides a
convenient way to reference the results of a SELECT as if it were a
physical table.
As you can see, there is nothing about indexes and views that is
closely related. But, I suspect the source of your confusion is that
odd creation, the indexed view.
An indexed view is a trick. While a view is strictly a logical
construct, and indexed view is a sneaky way to force the view to
become a physical table internally. Like a real view, there is never
any question about it being kept in sync with the table(s) from which
it is derived.
The indexed view is a specialized tool used to optimize retrieval.
Indexed views can sometimes cause updates to the underlying table(s)
to become horribly slow, so they are not a tool one chooses to
implement lightly.
There are also some issues about how the optimizer treats them,
depending on the edition (Standard or Enterprise) running, but to get
started just try to understand the basics.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:13:01 -0700, a_pridgen
<apridgen@.discussions.microsoft.com> wrote:
>I am currently taking a class, SQL SErver 2000 Database Design and
>Implementation (70-229) and have just finished reading about indexes. In
>terms of their primary use, these seem closely related to 'views'. I am
>unable to find any research on how to know when to use a 'view' versus an
>'index'. I was disappointed that the text did not compare the two for
>contrast. Can someone explain or direct me to information regarding their
>differences?|||> I was disappointed that the text did not compare the two for
> contrast.
Well, this is kind of like expecting a car manual to compare anti-freeze and
carburetors, or seat belts and radios, for contrast. They are completely
different things, even though they are part of a bigger entity, as others
have and will explain(ed).
A|||Well, you see, that's the 'rub', I have no instructor...only a book and
online tutorial....sometimes concepts are hard to grasp with no one to
'bounce' questions off of.
I was
about the 'views'. I incorrectly thought they were some
manipulation of the data (such as a logical file arrangement).
Thank you for the explanation...it really did help.
Some of these 'concepts' are really hard for me, as I have been using RPG400
FOREVER!!!
"Kalen Delaney" wrote:
> Did the instructor explain these two concepts? They are nothing at all
> alike, and their use is quite different, so I would be surprised to see a
> section comparing the two. It would be like having a section explaining th
e
> difference between tables and SELECT statements.
> An index is a physical structure that contains pointers to the data and
> helps find the data you are looking for more quickly. In general, you don'
t
> 'use' an index. You create the indexes you need, and SQL Server chooses
> whether or not to use them to help get to the data you are looking for.
> A view is a way to save a SELECT statement so you don't have to retype it
> every time, and can use the data returned by the view as if it were a tabl
e.
> If there are indexes on the table that your view is based on, they can be
> used exactly as if you were using the full underlying SELECT instead of
> using the view.
> You can also build indexes on views, but that is a whole separate big topi
c.
> Have you asked the instructor this question?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
> news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
>
>|||Well, thanks so much for boosting my confidence.
I now remember why I hestitated before posting for help!
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, this is kind of like expecting a car manual to compare anti-freeze a
nd
> carburetors, or seat belts and radios, for contrast. They are completely
> different things, even though they are part of a bigger entity, as others
> have and will explain(ed).
> A
>
>|||Thanks for the help...when looking for 'help' I always hope for people
willing to share knowledge without being condescending.
"Roy Harvey" wrote:
> There are indexes, there are views, and there are indexed views, so
> lets take them one by one.
> An index is used for accessing a table efficiently. It may also serve
> to enforce a UNIQUE constraint. It is a physical, rather than logical
> concept. Dropping an index may result in a query running slower, but
> it should never change the results a query returns.
> A view is, in contrast, strictly logical. The result set from any
> SELECT command is (effectively) a table; a view simply provides a
> convenient way to reference the results of a SELECT as if it were a
> physical table.
> As you can see, there is nothing about indexes and views that is
> closely related. But, I suspect the source of your confusion is that
> odd creation, the indexed view.
> An indexed view is a trick. While a view is strictly a logical
> construct, and indexed view is a sneaky way to force the view to
> become a physical table internally. Like a real view, there is never
> any question about it being kept in sync with the table(s) from which
> it is derived.
> The indexed view is a specialized tool used to optimize retrieval.
> Indexed views can sometimes cause updates to the underlying table(s)
> to become horribly slow, so they are not a tool one chooses to
> implement lightly.
> There are also some issues about how the optimizer treats them,
> depending on the edition (Standard or Enterprise) running, but to get
> started just try to understand the basics.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 09:13:01 -0700, a_pridgen
> <apridgen@.discussions.microsoft.com> wrote:
>
>|||> Well, thanks so much for boosting my confidence.
> I now remember why I hestitated before posting for help!
Hey, you get what you pay for. Since other people had already explained the
concepts directly, I just thought I'd show an analogy of your expectations.
Sorry I bothered, because it seems my point was lost on you.
Implementation (70-229) and have just finished reading about indexes. In
terms of their primary use, these seem closely related to 'views'. I am
unable to find any research on how to know when to use a 'view' versus an
'index'. I was disappointed that the text did not compare the two for
contrast. Can someone explain or direct me to information regarding their
differences?Indexes and views don't really have anything in common.
A view is a stored query that can be accessed like a table.
An index is sorted list of columns from your table (or indexed view, but
thats a different topic). This sorted list allows the database to quickly
locate a particular value in the indexed columns, and includes a pointer to
the actual row in the table. Indexes are used to speed up searches.
Unique indexes not only speed up searches but prevent duplicate values from
being inserted into the table.
A search on Google (or any good database book) will turn up much more in
depth explanations.
"a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
> I am currently taking a class, SQL SErver 2000 Database Design and
> Implementation (70-229) and have just finished reading about indexes. In
> terms of their primary use, these seem closely related to 'views'. I am
> unable to find any research on how to know when to use a 'view' versus an
> 'index'. I was disappointed that the text did not compare the two for
> contrast. Can someone explain or direct me to information regarding their
> differences?|||Did the instructor explain these two concepts? They are nothing at all
alike, and their use is quite different, so I would be surprised to see a
section comparing the two. It would be like having a section explaining the
difference between tables and SELECT statements.
An index is a physical structure that contains pointers to the data and
helps find the data you are looking for more quickly. In general, you don't
'use' an index. You create the indexes you need, and SQL Server chooses
whether or not to use them to help get to the data you are looking for.
A view is a way to save a SELECT statement so you don't have to retype it
every time, and can use the data returned by the view as if it were a table.
If there are indexes on the table that your view is based on, they can be
used exactly as if you were using the full underlying SELECT instead of
using the view.
You can also build indexes on views, but that is a whole separate big topic.
Have you asked the instructor this question?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
>I am currently taking a class, SQL SErver 2000 Database Design and
> Implementation (70-229) and have just finished reading about indexes. In
> terms of their primary use, these seem closely related to 'views'. I am
> unable to find any research on how to know when to use a 'view' versus an
> 'index'. I was disappointed that the text did not compare the two for
> contrast. Can someone explain or direct me to information regarding their
> differences?|||There are indexes, there are views, and there are indexed views, so
lets take them one by one.
An index is used for accessing a table efficiently. It may also serve
to enforce a UNIQUE constraint. It is a physical, rather than logical
concept. Dropping an index may result in a query running slower, but
it should never change the results a query returns.
A view is, in contrast, strictly logical. The result set from any
SELECT command is (effectively) a table; a view simply provides a
convenient way to reference the results of a SELECT as if it were a
physical table.
As you can see, there is nothing about indexes and views that is
closely related. But, I suspect the source of your confusion is that
odd creation, the indexed view.
An indexed view is a trick. While a view is strictly a logical
construct, and indexed view is a sneaky way to force the view to
become a physical table internally. Like a real view, there is never
any question about it being kept in sync with the table(s) from which
it is derived.
The indexed view is a specialized tool used to optimize retrieval.
Indexed views can sometimes cause updates to the underlying table(s)
to become horribly slow, so they are not a tool one chooses to
implement lightly.
There are also some issues about how the optimizer treats them,
depending on the edition (Standard or Enterprise) running, but to get
started just try to understand the basics.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:13:01 -0700, a_pridgen
<apridgen@.discussions.microsoft.com> wrote:
>I am currently taking a class, SQL SErver 2000 Database Design and
>Implementation (70-229) and have just finished reading about indexes. In
>terms of their primary use, these seem closely related to 'views'. I am
>unable to find any research on how to know when to use a 'view' versus an
>'index'. I was disappointed that the text did not compare the two for
>contrast. Can someone explain or direct me to information regarding their
>differences?|||> I was disappointed that the text did not compare the two for
> contrast.
Well, this is kind of like expecting a car manual to compare anti-freeze and
carburetors, or seat belts and radios, for contrast. They are completely
different things, even though they are part of a bigger entity, as others
have and will explain(ed).
A|||Well, you see, that's the 'rub', I have no instructor...only a book and
online tutorial....sometimes concepts are hard to grasp with no one to
'bounce' questions off of.
I was
about the 'views'. I incorrectly thought they were somemanipulation of the data (such as a logical file arrangement).
Thank you for the explanation...it really did help.
Some of these 'concepts' are really hard for me, as I have been using RPG400
FOREVER!!!
"Kalen Delaney" wrote:
> Did the instructor explain these two concepts? They are nothing at all
> alike, and their use is quite different, so I would be surprised to see a
> section comparing the two. It would be like having a section explaining th
e
> difference between tables and SELECT statements.
> An index is a physical structure that contains pointers to the data and
> helps find the data you are looking for more quickly. In general, you don'
t
> 'use' an index. You create the indexes you need, and SQL Server chooses
> whether or not to use them to help get to the data you are looking for.
> A view is a way to save a SELECT statement so you don't have to retype it
> every time, and can use the data returned by the view as if it were a tabl
e.
> If there are indexes on the table that your view is based on, they can be
> used exactly as if you were using the full underlying SELECT instead of
> using the view.
> You can also build indexes on views, but that is a whole separate big topi
c.
> Have you asked the instructor this question?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
> news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
>
>|||Well, thanks so much for boosting my confidence.
I now remember why I hestitated before posting for help!
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, this is kind of like expecting a car manual to compare anti-freeze a
nd
> carburetors, or seat belts and radios, for contrast. They are completely
> different things, even though they are part of a bigger entity, as others
> have and will explain(ed).
> A
>
>|||Thanks for the help...when looking for 'help' I always hope for people
willing to share knowledge without being condescending.
"Roy Harvey" wrote:
> There are indexes, there are views, and there are indexed views, so
> lets take them one by one.
> An index is used for accessing a table efficiently. It may also serve
> to enforce a UNIQUE constraint. It is a physical, rather than logical
> concept. Dropping an index may result in a query running slower, but
> it should never change the results a query returns.
> A view is, in contrast, strictly logical. The result set from any
> SELECT command is (effectively) a table; a view simply provides a
> convenient way to reference the results of a SELECT as if it were a
> physical table.
> As you can see, there is nothing about indexes and views that is
> closely related. But, I suspect the source of your confusion is that
> odd creation, the indexed view.
> An indexed view is a trick. While a view is strictly a logical
> construct, and indexed view is a sneaky way to force the view to
> become a physical table internally. Like a real view, there is never
> any question about it being kept in sync with the table(s) from which
> it is derived.
> The indexed view is a specialized tool used to optimize retrieval.
> Indexed views can sometimes cause updates to the underlying table(s)
> to become horribly slow, so they are not a tool one chooses to
> implement lightly.
> There are also some issues about how the optimizer treats them,
> depending on the edition (Standard or Enterprise) running, but to get
> started just try to understand the basics.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 09:13:01 -0700, a_pridgen
> <apridgen@.discussions.microsoft.com> wrote:
>
>|||> Well, thanks so much for boosting my confidence.
> I now remember why I hestitated before posting for help!
Hey, you get what you pay for. Since other people had already explained the
concepts directly, I just thought I'd show an analogy of your expectations.
Sorry I bothered, because it seems my point was lost on you.
Wednesday, March 7, 2012
Indexes design
If I have a table with Col1 + Col2 as PK. But most of the queries use Col1, Col2, Col3 and Col4 in the queries, should I create another unique index on this combination? If not, my queries do an index scan. Just wondering is there a general rule for this kind of situation as I have a lot of such cases in my database.depends. most of the time multi column indexes are not a good idea. if i remember correctly for multi column indexes to work at all, the query has to search the columns in the table in the order that you defined the columns in the index.
index scans are ok, it is table scans you have to worry about.
are you experiencing a performance issue?|||Thrasymachus- I agree, we have to include all the columns in the index in the where clause to avoid a index scan. But the table is huge and yes, I am experiencing performance issues. I know I could make them run better with a seek.|||what's huge? tens of millions of records?
what's the ddl like? lots of fields? big fields?
And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition.
What's the query look like?|||3 million records. Table has 9 columns with 2 varchar(512) and a UniqueIdentifier.
Not sure what you mean by "And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition."|||3 million is not that big.
If your index is created like so
CREATE INDEX MyIndex On Table1 (col1,col2,col3)
Then your query should go like
SELECT *
FROM Table1
Where col1 = @.param1
AND col2 = @.param2
AND col3 = @.param3
same order, see.
ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values.|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.|||I develop in sql 2k now, but I just looked at my old design book for sql 7 I have laying around to make sure and this part I did confirm. things might have changed. The other thing I remeber from an ex-colleague of mine who was the sql guru I ever knew.
"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.
The notion that the WHERE clause search arguments have to be in the "same order " as the columns defined in the key of the index is rubbish.
Also, the term "index scan" to me means examining EVERY row of an index because to find a match on a search criteria. In your example, "where clause is on col1 and col3 while your index is on (col1, col2, col3)", given that you don't have any datatype mismatch issues, and given that the index is chosen as the access path, and given that the search argument operator is "=", and given that the index is large enough, then you will KEY POSITION on col1, and scan the rest of the index for col3 matches. So, every row of the index containing col1 value is "scanned", but no more than that.|||KSherlock- I agree with you. I have the same understanding as yours. We got side tracked into this discussion about column order. My original question is a little different. Still looking for some comments/suggestions on it.|||As for this part:
"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."
I will refer you to Database Design on SQL Server 7 by certification insider press page 173.
As for the other thing. This kind of backs me up (taken from http://www.sql-server-performance.com/composite_indexes.asp). I have forgot a little of it:
"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"
So heres mud in your eye Sherlock.|||Check me if I have this straight:
Col1 and Col2 make up the PK
And most queries use all 4 Col1 Col2 Col3 and Col4? Or is that "or"? After all, if you specify col1 and col2, specifying col3 and col4 would be a bit redundant, eh?
If you specify values for Col1 and Col2, you should be seeking on the PK. Are you using functions on the columns? Like
where upper(col1) = upper(some search argument)
The above will cause a table scan every time. Since the PK is usually clustered, you may be seeing this just represented as a clustered index scan. It is really the same thing.|||And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition
...
"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"
So heres mud in your eye Sherlock.
Uhhh, yea. Whatever you say...|||...most of the time multi column indexes are not a good idea...Huh? Please present your theories as such, not as a "proven fact", man. You want the guy to loose his job just because you couldn't resist making a statement?
...ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values...Good point, but ... how should I say it? It's orthodox concept for a table that is NEVER a part of an action query. Introduction of indexes based on "best practices for SELECT queries" ONLY, - is a recepie for your action queries as a potential bottleneck.
index scans are ok, it is table scans you have to worry about.
are you experiencing a performance issue?|||Thrasymachus- I agree, we have to include all the columns in the index in the where clause to avoid a index scan. But the table is huge and yes, I am experiencing performance issues. I know I could make them run better with a seek.|||what's huge? tens of millions of records?
what's the ddl like? lots of fields? big fields?
And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition.
What's the query look like?|||3 million records. Table has 9 columns with 2 varchar(512) and a UniqueIdentifier.
Not sure what you mean by "And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition."|||3 million is not that big.
If your index is created like so
CREATE INDEX MyIndex On Table1 (col1,col2,col3)
Then your query should go like
SELECT *
FROM Table1
Where col1 = @.param1
AND col2 = @.param2
AND col3 = @.param3
same order, see.
ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values.|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.|||I develop in sql 2k now, but I just looked at my old design book for sql 7 I have laying around to make sure and this part I did confirm. things might have changed. The other thing I remeber from an ex-colleague of mine who was the sql guru I ever knew.
"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."|||I am not sure if the order of the columns in the index and the query really matters. QO is smart enough to re-phrase your query to use the index. But if your query's where clause is on col1 and col3 while your index is on (col1, col2 and col3) then there will be a scan versus a seek.
The notion that the WHERE clause search arguments have to be in the "same order " as the columns defined in the key of the index is rubbish.
Also, the term "index scan" to me means examining EVERY row of an index because to find a match on a search criteria. In your example, "where clause is on col1 and col3 while your index is on (col1, col2, col3)", given that you don't have any datatype mismatch issues, and given that the index is chosen as the access path, and given that the search argument operator is "=", and given that the index is large enough, then you will KEY POSITION on col1, and scan the rest of the index for col3 matches. So, every row of the index containing col1 value is "scanned", but no more than that.|||KSherlock- I agree with you. I have the same understanding as yours. We got side tracked into this discussion about column order. My original question is a little different. Still looking for some comments/suggestions on it.|||As for this part:
"ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values."
I will refer you to Database Design on SQL Server 7 by certification insider press page 173.
As for the other thing. This kind of backs me up (taken from http://www.sql-server-performance.com/composite_indexes.asp). I have forgot a little of it:
"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"
So heres mud in your eye Sherlock.|||Check me if I have this straight:
Col1 and Col2 make up the PK
And most queries use all 4 Col1 Col2 Col3 and Col4? Or is that "or"? After all, if you specify col1 and col2, specifying col3 and col4 would be a bit redundant, eh?
If you specify values for Col1 and Col2, you should be seeking on the PK. Are you using functions on the columns? Like
where upper(col1) = upper(some search argument)
The above will cause a table scan every time. Since the PK is usually clustered, you may be seeing this just represented as a clustered index scan. It is really the same thing.|||And if you go ahead with this index, remember the order inwhich you search has to match the order of index definition
...
"A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000] Updated 11-15-2004"
So heres mud in your eye Sherlock.
Uhhh, yea. Whatever you say...|||...most of the time multi column indexes are not a good idea...Huh? Please present your theories as such, not as a "proven fact", man. You want the guy to loose his job just because you couldn't resist making a statement?
...ALSO I JUST REMEMBERED, the most selective column in the index definition should come first. That is col1 in the index definintion should have the highest number of distinct values...Good point, but ... how should I say it? It's orthodox concept for a table that is NEVER a part of an action query. Introduction of indexes based on "best practices for SELECT queries" ONLY, - is a recepie for your action queries as a potential bottleneck.
Sunday, February 19, 2012
Indexed Views & Constraints
Is using indexed views to create constraints a good idea? If not, what
database design flaws usually lead to this sort of construct?Can you give an example of how you would use an indexed view to create a
constraint? I've heard of indexed views, but never heard of using an
indexed view to create constraints.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||For a constraint on a view to be meaningful, the view would have to be
updatable. The restrictions on updatable views are that
-the columns in the view must be directly related to the columns in the base
tables (no aggregate functions)
-any update to the view must affect one one of the base tables involved at a
time.
So, if you wanted to create a primary key constraint using two columns in a
view that orginated it two different base tables, it would not act as a
constraint per se. Any insert or update you tried to perform on the view tha
t
modified both columns simultaneously would fail because it would violate the
second rule above. In other word, you would have the constaint, but you
wouldn't be able to use it in the way that you would like.
The index (as a result of the primary key constraint) would help with
SELECTs though.
A unique clustered index on a view also might not provide the constraint
enforcement you are looking for. Consider the following:
create table testa (aid int, aval char(1), PRIMARY KEY (aid))
go
create table testb (bid int, bval char(1), PRIMARY KEY (bid))
go
INSERT INTO testa VALUES (1, 'M')
INSERT INTO testa VALUES (2, 'A')
INSERT INTO testa VALUES (3, 'R')
INSERT INTO testa VALUES (4, 'K')
INSERT INTO testa VALUES (5, 'O')
INSERT INTO testb VALUES (1, 'W')
INSERT INTO testb VALUES (2, 'I')
INSERT INTO testb VALUES (3, 'L')
INSERT INTO testb VALUES (4, 'L')
INSERT INTO testb VALUES (5, 'Y')
CREATE VIEW dbo.testc WITH SCHEMABINDING AS
(SELECT a.aid, a.aval, b.bval from dbo.testa a inner join dbo.testb b on
a.aid=b.bid)
CREATE UNIQUE CLUSTERED INDEX PK_testc_aval_bval ON dbo.testc (aval,bval)
INSERT INTO testa VALUES (6,'Z')
--You would think this would fail
INSERT INTO testb VALUES (6,'Z')
--But it doesn't.
select * from testc
--last row will be (6,'Z','Z'), something that should have violated the uniq
ue
--constraint.
"Wes" wrote:
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||the only thing I can think of right now is to create a unique index on
an indexed view. I cannot think of a real life situation when it would
be feasible to do so.
Note that for Oracle you can also create check constraints on
materialized views, which lets you enforse business rules like 'no more
than 15 employees report to a manager'. Yet in Oracle world that is a
very complex way, recommended to use only as the very last resort.|||also this might be relevant:
http://www.dbazine.com/oracle/or-articles/tropashko8|||> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
To require all non-null values in a particular column to be unique but to
allow any number of nulls.|||I assume this would mean creating a unique index on view.Column1
and the view would be:
select XYZ from table
where Column1 is not null
The next question would be what is the alternative way to do this without a
view? I can think of a use defined function that checks for the existence
of the value in the table, or for null.
i.e. @.Column1 is null or not exists(select 1 from table where column1 =
@.column1)
Would such an aproach be worse for performance?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OXYynSGHGHA.3624@.TK2MSFTNGP09.phx.gbl...
> To require all non-null values in a particular column to be unique but to
> allow any number of nulls.
>|||>I assume this would mean creating a unique index on view.Column1
Correct.
> The next question would be what is the alternative way to do this without
> a
> view? I can think of a use defined function that checks for the existence
> of the value in the table, or for null.
You are assuming a single alternative. A trigger is another. I'm sure
there are others - perhaps involving some overly clever and obscure
technique.
> Would such an aproach be worse for performance?
Judging whether one approach is superior to another requires an evaluation
of the pros and cons of all alternatives and an evaluation of the impact of
each on the system in general. As usual, it is impossible to say that any
approach is superior to any other without delving into the specifics of the
system and its usage.|||Scott,
Thanks for the clarification.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ONQCLyGHGHA.3100@.tk2msftngp13.phx.gbl...
> Correct.
>
without
existence
> You are assuming a single alternative. A trigger is another. I'm sure
> there are others - perhaps involving some overly clever and obscure
> technique.
>
> Judging whether one approach is superior to another requires an evaluation
> of the pros and cons of all alternatives and an evaluation of the impact
of
> each on the system in general. As usual, it is impossible to say that any
> approach is superior to any other without delving into the specifics of
the
> system and its usage.
>|||We have a table that list a personid, relationtypeid (~CEO,CFO,…Employee),
and companyid.
These 3 items are unique per row but the relationtypeid and companyid should
also be unique when the relationtypeid is of a certain type. The thought i
s
to create a schema bound view with a unique clustered index on it. The view
would have the applicable filter based on the relationtypeid.
"Jim Underwood" wrote:
> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
> indexed view to create constraints.
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
>
>
database design flaws usually lead to this sort of construct?Can you give an example of how you would use an indexed view to create a
constraint? I've heard of indexed views, but never heard of using an
indexed view to create constraints.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||For a constraint on a view to be meaningful, the view would have to be
updatable. The restrictions on updatable views are that
-the columns in the view must be directly related to the columns in the base
tables (no aggregate functions)
-any update to the view must affect one one of the base tables involved at a
time.
So, if you wanted to create a primary key constraint using two columns in a
view that orginated it two different base tables, it would not act as a
constraint per se. Any insert or update you tried to perform on the view tha
t
modified both columns simultaneously would fail because it would violate the
second rule above. In other word, you would have the constaint, but you
wouldn't be able to use it in the way that you would like.
The index (as a result of the primary key constraint) would help with
SELECTs though.
A unique clustered index on a view also might not provide the constraint
enforcement you are looking for. Consider the following:
create table testa (aid int, aval char(1), PRIMARY KEY (aid))
go
create table testb (bid int, bval char(1), PRIMARY KEY (bid))
go
INSERT INTO testa VALUES (1, 'M')
INSERT INTO testa VALUES (2, 'A')
INSERT INTO testa VALUES (3, 'R')
INSERT INTO testa VALUES (4, 'K')
INSERT INTO testa VALUES (5, 'O')
INSERT INTO testb VALUES (1, 'W')
INSERT INTO testb VALUES (2, 'I')
INSERT INTO testb VALUES (3, 'L')
INSERT INTO testb VALUES (4, 'L')
INSERT INTO testb VALUES (5, 'Y')
CREATE VIEW dbo.testc WITH SCHEMABINDING AS
(SELECT a.aid, a.aval, b.bval from dbo.testa a inner join dbo.testb b on
a.aid=b.bid)
CREATE UNIQUE CLUSTERED INDEX PK_testc_aval_bval ON dbo.testc (aval,bval)
INSERT INTO testa VALUES (6,'Z')
--You would think this would fail
INSERT INTO testb VALUES (6,'Z')
--But it doesn't.
select * from testc
--last row will be (6,'Z','Z'), something that should have violated the uniq
ue
--constraint.
"Wes" wrote:
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||the only thing I can think of right now is to create a unique index on
an indexed view. I cannot think of a real life situation when it would
be feasible to do so.
Note that for Oracle you can also create check constraints on
materialized views, which lets you enforse business rules like 'no more
than 15 employees report to a manager'. Yet in Oracle world that is a
very complex way, recommended to use only as the very last resort.|||also this might be relevant:
http://www.dbazine.com/oracle/or-articles/tropashko8|||> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
To require all non-null values in a particular column to be unique but to
allow any number of nulls.|||I assume this would mean creating a unique index on view.Column1
and the view would be:
select XYZ from table
where Column1 is not null
The next question would be what is the alternative way to do this without a
view? I can think of a use defined function that checks for the existence
of the value in the table, or for null.
i.e. @.Column1 is null or not exists(select 1 from table where column1 =
@.column1)
Would such an aproach be worse for performance?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OXYynSGHGHA.3624@.TK2MSFTNGP09.phx.gbl...
> To require all non-null values in a particular column to be unique but to
> allow any number of nulls.
>|||>I assume this would mean creating a unique index on view.Column1
Correct.
> The next question would be what is the alternative way to do this without
> a
> view? I can think of a use defined function that checks for the existence
> of the value in the table, or for null.
You are assuming a single alternative. A trigger is another. I'm sure
there are others - perhaps involving some overly clever and obscure
technique.
> Would such an aproach be worse for performance?
Judging whether one approach is superior to another requires an evaluation
of the pros and cons of all alternatives and an evaluation of the impact of
each on the system in general. As usual, it is impossible to say that any
approach is superior to any other without delving into the specifics of the
system and its usage.|||Scott,
Thanks for the clarification.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ONQCLyGHGHA.3100@.tk2msftngp13.phx.gbl...
> Correct.
>
without
existence
> You are assuming a single alternative. A trigger is another. I'm sure
> there are others - perhaps involving some overly clever and obscure
> technique.
>
> Judging whether one approach is superior to another requires an evaluation
> of the pros and cons of all alternatives and an evaluation of the impact
of
> each on the system in general. As usual, it is impossible to say that any
> approach is superior to any other without delving into the specifics of
the
> system and its usage.
>|||We have a table that list a personid, relationtypeid (~CEO,CFO,…Employee),
and companyid.
These 3 items are unique per row but the relationtypeid and companyid should
also be unique when the relationtypeid is of a certain type. The thought i
s
to create a schema bound view with a unique clustered index on it. The view
would have the applicable filter based on the relationtypeid.
"Jim Underwood" wrote:
> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
> indexed view to create constraints.
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)