Do Queries Support Regular Expressions?

If I had the following string in a table how could I find all records that
contained the term 'Business' for example? Would Regular Expressions be
supported in this context? Your comments regarding performance of a category
schema such as this?
// Example
PrivateSector_Business_Services
<%= Clinton GallagherClinton,
You could use the LIKE operator with the % wildcard character as:
WHERE COLUMN LIKE '%Business%'
Optionally you could use a Full-Text index as well.
HTH
Jerry
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
> If I had the following string in a table how could I find all records that
> contained the term 'Business' for example? Would Regular Expressions be
> supported in this context? Your comments regarding performance of a
> category schema such as this?
> // Example
> PrivateSector_Business_Services
> <%= Clinton Gallagher
>
>
>
>|||Thank you Jerry. That was simple :-)
<%= Clinton Gallagher
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OZqoPlczFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Clinton,
> You could use the LIKE operator with the % wildcard character as:
> WHERE COLUMN LIKE '%Business%'
> Optionally you could use a Full-Text index as well.
> HTH
> Jerry
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||For what it's worth, you can make real regular expression query searches via
calls to external COM libraries.
http://blogs.msdn.com/khen1234/arch.../11/416392.aspx
However, if using the native LIKE operator satisfies your requirement, then
use that instead.
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
> If I had the following string in a table how could I find all records that
> contained the term 'Business' for example? Would Regular Expressions be
> supported in this context? Your comments regarding performance of a
> category schema such as this?
> // Example
> PrivateSector_Business_Services
> <%= Clinton Gallagher
>
>
>
>

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inserted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to the OS/2 version of DB2,
and/or it might have been changed since.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the clustered index is the
>physical sort order of data in a table. And once i have this technique available to me, i can take
>advantage of it to group low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tuning option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be able to take advantage
> of that for query optimization?
>|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At
the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inser
ted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects
of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to
the OS/2 version of DB2,
and/or it might have been changed since.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the cl
ustered index is the
>physical sort order of data in a table. And once i have this technique avai
lable to me, i can take
>advantage of it to group low-cardinality rows together, where an index woul
d not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tunin
g option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be
able to take advantage
> of that for query optimization?
>|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Do other RDMS have clustered indexes?

i realize the term "clustered index" is a MS thing. i also know that the
clustered index is the physical sort order of data in a table. And once i
have this technique available to me, i can take advantage of it to group
low-cardinality rows together, where an index would not be selective enough.
So, with clustered indexes in SQL Server, i have another performance tuning
option. Does Oracle, MySQL, DB2 let the user change the physical sort order
of a table - and be able to take advantage of that for query optimization?
I recall a long long time ago working with the OS/2 version of DB2 (DB2/2):
You did a rebuild of the table to cluster it. This was a once operation. At the end of the table
(imagine pages and extents), you had overflow pages. New rows were not inserted in place, but were
added to these overflow pages. So, you didn't get the fragmentation aspects of SQL Server, but there
was some extra cost for looking up these overflow pages.
All above is from memory, from around 1991. This might have been special to the OS/2 version of DB2,
and/or it might have been changed since.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the clustered index is the
>physical sort order of data in a table. And once i have this technique available to me, i can take
>advantage of it to group low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance tuning option. Does Oracle,
> MySQL, DB2 let the user change the physical sort order of a table - and be able to take advantage
> of that for query optimization?
>
|||With DB2 you can create a single clustered index on a table and specify
whether or not you want it ASC or DESC. You can also specify to either
allow or disallow Reverse Scans.
In addition DB2 v. 8.2 has added functionality to eliminate the limit
of one clustered index per table. They have what is call an MDC (Multi
Dimensional Cluster) which allows you to effectively create more than
one clustered index on a single table. They do this by arranging the
data into blocks rather than index pages. The are extremely effective
in performance tuning where you have a large data set.
|||Oracle's Index Organized tables aka IOT are quite similar to MS SQL
Servers clustered index.
DB2's clustered index and its table are separate objects. The docs used
to say that DB2 will try to maintain physical order, but there was no
guarantee - you needed to reorganize the table periodically once the
clustering factor dropped too low. ("clustering factor" is statistics
present in DB2 and Oracle, but not relevant to MS SQL Server, because
MS SQL Server uses bookmarks, not Row identifiers to locate a row from
a non-clustered index)
|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235qm3VU5FHA.3532@.TK2MSFTNGP10.phx.gbl...
>i realize the term "clustered index" is a MS thing. i also know that the
>clustered index is the physical sort order of data in a table. And once i
>have this technique available to me, i can take advantage of it to group
>low-cardinality rows together, where an index would not be selective
>enough.
> So, with clustered indexes in SQL Server, i have another performance
> tuning option. Does Oracle, MySQL, DB2 let the user change the physical
> sort order of a table - and be able to take advantage of that for query
> optimization?
In Oracle an "Index-Organized Table" is the equivalent as a SQL Server
clustered index. There are some minor differences in implementation, for
instance, the requirement that the "clustered index" must be the primary
key. But they are pretty much the same thing.
David
|||Alexander Kuznetsov wrote:
> Oracle's Index Organized tables aka IOT are quite similar to MS SQL
> Servers clustered index.
> DB2's clustered index and its table are separate objects. The docs
> used to say that DB2 will try to maintain physical order, but there
> was no guarantee - you needed to reorganize the table periodically
> once the clustering factor dropped too low. ("clustering factor" is
> statistics present in DB2 and Oracle, but not relevant to MS SQL
> Server, because MS SQL Server uses bookmarks, not Row identifiers to
> locate a row from a non-clustered index)
MaxDB has a similar feature: *all* tables are stored the way MS SQL Server
tables with a clustered index are stored. The index used is the PK of the
table and if there is no PK then a hidden column with a synthetic value is
added and used for the PK.
Kind regards
robert

Do not SELECT

Hi,
Is there any way how to formulate SQL query to select all columns except
column D and E?
So if the table has the columns A,B,C,D the result woud contain only columns
A,B,C
SELECT " * except D,E"
FROM myTable
Thanks,
Lubomir
SELECT A, B, C
FROM myTable
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||Hello,
If you want it permamanent then you can create a view and then query the
view
CREATE VIEW A1
as
Select A,B,C from myTable
After the creation query the view
Select * from A1
THanks
Hari
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||The problem is, the tables are created on the fly, so I don't know what
columns the particular table will have. I know however, every table has two
columns ("help" columns) that will not be displayed, as they are used for
another purposes.
From that reason, it would be very convenient to make query like SELECT *
and to specify those 2 columns to be exclusive.
Thanks,
Lubomir
"Hari Prasad" wrote:

> Hello,
> If you want it permamanent then you can create a view and then query the
> view
> CREATE VIEW A1
> as
> Select A,B,C from myTable
> After the creation query the view
> Select * from A1
> THanks
> Hari
>
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
>
>
|||On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:

>The problem is, the tables are created on the fly, so I don't know what
>columns the particular table will have.
Hi Lubomir,
That is indeed a problem. And it's also a sign that you are using the
database in a way that it's not intended to be used - applications that
need to create tables on the fly are almost always the result of some
bad design decisions.
Could you explain in some more detail WHY your application does not have
a fixed data model?

>From that reason, it would be very convenient to make query like SELECT *
>and to specify those 2 columns to be exclusive.
There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
vote against it - personally, I'd rather remove the SELECT * than to
extend it!)
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||How do you manage, populate, update, etc., tables that you don't know the
structure of? How do you know you are getting the right number of columns,
with the correct names, in your UI application? How do you know that your
datasets will not break the front end? And if you're just not "displaying"
the help columns, then just don't "display" them. What you do or don't
display in the UI doesn't have to be the same as what you retrieve from the
database.
In all seriousness though, get rid of the SELECT * and start naming your
columns. It will save you a bunch of headaches down the road.
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:8587187A-53B8-4C53-A2DD-2F300FADFF3A@.microsoft.com...[vbcol=seagreen]
> The problem is, the tables are created on the fly, so I don't know what
> columns the particular table will have. I know however, every table has
> two
> columns ("help" columns) that will not be displayed, as they are used for
> another purposes.
> From that reason, it would be very convenient to make query like SELECT *
> and to specify those 2 columns to be exclusive.
> Thanks,
> Lubomir
> "Hari Prasad" wrote:
|||Hi Hugo,
The reason is, that that this application serves more application (like
plugins) with their own tables.
Lubomir
"Hugo Kornelis" wrote:

> On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:
>
> Hi Lubomir,
> That is indeed a problem. And it's also a sign that you are using the
> database in a way that it's not intended to be used - applications that
> need to create tables on the fly are almost always the result of some
> bad design decisions.
> Could you explain in some more detail WHY your application does not have
> a fixed data model?
>
> There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
> vote against it - personally, I'd rather remove the SELECT * than to
> extend it!)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Mon, 7 May 2007 09:38:00 -0700, Lubomir wrote:

>Hi Hugo,
>The reason is, that that this application serves more application (like
>plugins) with their own tables.
Hi Lubomir,
As long as the data requirements for all these plugin applications are
relatively stable, they can each have their own set of tables that
you'll have to design and deploy once (and possibly more often, if and
when requirements change).
If the data requirements can change on a daily basis, you're probably
best advised to move away from relational databases, since they're
really designed to be used with a fixed datamodel.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||If you are talking about SQL 2005, then you can write DDL trigger and
programically create view or procedure wich select all columns from table
exept those 2.
Ramunas
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...[vbcol=seagreen]
> Hi Hugo,
> The reason is, that that this application serves more application (like
> plugins) with their own tables.
> Lubomir
>
> "Hugo Kornelis" wrote:
*[vbcol=seagreen]
|||Yes, that could work.
Thanks,
Lubomir
"Ramunas Balukonis" wrote:

> If you are talking about SQL 2005, then you can write DDL trigger and
> programically create view or procedure wich select all columns from table
> exept those 2.
> Ramunas
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...
> *
>
>

Do not lock table...

sql2k sp3
Whats good/ bad to checking/ not checking the "Do not
lock table during snapshot..." button?
TIA, ChrisR
when you select the concurrent snapshot option it takes longer to generate
the snapshot and there is less locking.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:10ab01c4a736$fe990410$a301280a@.phx.gbl...
> sql2k sp3
>
> Whats good/ bad to checking/ not checking the "Do not
> lock table during snapshot..." button?
> TIA, ChrisR

Do Inserts into Temptable enforce order of select?

I have a temp table which I am trying to do a select INTO. The temptable has an identity field which increments by one. In the select query, I am doing an order by. It does not appear that the temp table retains the physical ordering of the records from the order by. When I do a select * FROM temptable without an order by, the first record that is displayed as an id of 113. Then about the 42nd record in the temp table is the ID=1 record. In addition, the table records are not ordered by the original order of the select that inserted into the temp table.

From this I am led to believe that SQL does not enforce the orders of records on INSERT into statesments. Is this a correct assumption, or might there be something else going on?Please take a look at the blog post below:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

Ordering is not guaranteed for SELECT queries unless you include an explicit ORDER BY in the outer-most query. Similarly, the order of insertion of rows is not guranteed either. The identity values are however generated based on the order specified in the ORDER BY in INSERT...SELECT statement.

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?
A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?
sql

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

do indexed views slow down inserts

sql2l sp3
Im starting to do a bit of research on indexed views. On a
normal table, a clustered index slows down inserts. Is the
same true for a clustered indexed view? Will it slow down
inserts for the underlying table?A clustered index does not have to slow down inserts. If you understand how
clustered indexes work and choose the appropriate column(s) for the index
expression it can actually speed it up and certainly can make a difference
on selects. Since an Indexed view uses a clustered index it can have the
same properties. That said an indexed view is always going to be slower on
inserts than a straight table simply because of the extra data your dealing
with. Each time you do an insert, update or delete on the underlying table
it potentially has to populate and recalculate the indexed views data. How
much depends on several factors such as the table size and definition and
the hardware setup etc. However these losses may be offset by the gains
that you can achieve on the selects against an indexed view. Again it
depends but in general they are not ideal for situations where you have lots
of inserts.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:428701c47326$2badabd0$a601280a@.phx.gbl...
> sql2l sp3
> Im starting to do a bit of research on indexed views. On a
> normal table, a clustered index slows down inserts. Is the
> same true for a clustered indexed view? Will it slow down
> inserts for the underlying table?

Do I want to use a join?

I have two tables, both containing memberID fields. One table is a membership table and the other is a list of members in a committee. So one or more memberIDs from the first table are in the second table, while every memberID in the second table is in the first table.

I need to get all the memberIDs in the first table that are NOT in the second table.

The point of this is to have two lists, one of members not on the committee and one of member that are on the committee. Add a couple of arrow buttons and the user can add or delete members for the committee.

Diane

You can get all Members not on the committee by

SELECT * FROM TableA WHERE MemberId NOT IN (SELECT MemberId FROM TableB)

|||

That was fast, thank you.

Diane

|||As soon as you validated the soultion, please mark the question as answered.|||I did, when I posted my thank you.

Do I really need a cursor?

I've built an application to import transactions into the database. Bad transactions go in a separate table and dupe transactions get updated. Currently, it takes about 2 hours to import ~40K records using the code below. Obviously I'd like this to run as fast as possible and since cursors are a real drag I was wondering if there was a more efficient way to accomplish this.

DECLARE
@.contact_id int,
@.product_code char(9),
@.status_date datetime,
@.business_code char(4),
@.expire_date datetime,
@.prod_status char(4),
@.transaction_id int,
@.emailAddress varchar(50),
@.journal_id int

BEGIN TRAN
DECLARE transaction_import_cursor CURSOR
FOR SELECT transaction_id, product_code, emailAddress, status_date, business_code, expire_date, prod_status from transactions_batch_tmp
OPEN transaction_import_cursor
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT top 1 contacts.contact_id AS contact_id, transactions_batch_tmp.status_date AS status_date, transactions_batch_tmp.product_code AS product_code,
transactions_batch_tmp.business_code AS business_code, transactions_batch_tmp.expire_date AS expire_date,
transactions_batch_tmp.prod_status AS product_status
FROM transactions_batch_tmp INNER JOIN
journal INNER JOIN
contacts ON journal.contact_id = contacts.contact_id ON transactions_batch_tmp.emailAddress = contacts.emailAddress AND
transactions_batch_tmp.product_code = journal.product_code INNER JOIN
products ON transactions_batch_tmp.product_code = products.product_code
WHERE rtrim(ltrim(contacts.emailAddress)) = @.emailAddress AND journal.product_code = @.product_code
ORDER BY transactions_batch_tmp.status_date desc
IF @.@.ROWCOUNT = 0
BEGIN
print 'NEW transaction! ' + @.product_code + @.emailAddress
insert into journal (contact_id, product_code, status_date, business_code, expire_date, entryTypeID, product_status, date_entered)
SELECT distinct rtrim(ltrim(contacts.contact_id)) as cid, rtrim(ltrim(products.product_code)), transactions_batch_tmp.status_date,
rtrim(ltrim(transactions_batch_tmp.business_code)) , transactions_batch_tmp.expire_date, 21, rtrim(ltrim(transactions_batch_tmp.prod_status)), getDate()
FROM contacts INNER JOIN (transactions_batch_tmp INNER JOIN products ON transactions_batch_tmp.product_code=products.produ ct_code) ON contacts.emailAddress=transactions_batch_tmp.email Address
WHERE transactions_batch_tmp.transaction_id=@.transaction _id
END
ELSE
BEGIN
--print 'UPDATE transaction! ' + @.product_code + @.emailAddress
UPDATE journal
SET status_date =
(SELECT max(tmp.status_date)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code)
FROM transactions_batch_tmp tmp, contacts c, products p, journal j
WHERE tmp.emailaddress = @.emailAddress
AND tmp.emailaddress = rtrim(c.emailaddress)
AND c.contact_id = j.contact_id
AND j.product_code = @.product_code
AND j.product_code = tmp.product_code
END
FETCH NEXT FROM transaction_import_cursor INTO @.transaction_id, @.product_code, @.emailAddress, @.status_date, @.business_code, @.expire_date, @.prod_status
END
CLOSE transaction_import_cursor
DEALLOCATE transaction_import_cursor
COMMIT TRAN

/** purge data from temp error table before writing bad records for this batch **/
truncate table tran_import_error;

/** write bad records (missing product code or email address) to temp_error table **/
insert into tran_import_error (transaction_id, product_code, emailAddress, date_entered)
SELECT DISTINCT transactions_batch_tmp.transaction_id, transactions_batch_tmp.product_code, transactions_batch_tmp.emailAddress, getDate()
FROM transactions_batch_tmp
where transactions_batch_tmp.emailaddress not in (select emailaddress from contacts)
OR
transactions_batch_tmp.product_code not in (select product_code from products)

TIAI don't see anything in your code that requires a cursor. It would run much faster as set-based INSERT and UPDATE statements.|||Well, how would I handle the update part without a cursor? I need to make sure that *only* unique contact_id-product_code values exist in the journal table.

Thanks.|||Add some bit flag and notes columns to your import table. Then you can run data checks against the records prior to importing them. Flag any duplicates or bad records and add a note as to why they were flagged. Then import only the non-flagged records. Delete the non-flagged records when you are done, and you are left with a list of bad records that you can review or discard.|||blindman - Thanks for your help.

I'm almost there (I hope), but was wondering if there was a more efficient way to delete the dupe records than having to write two separate queries. I need to keep the most recent product_code-status_date transaction for *each* person. This runs after I insert ALL the records in the journal table.

--delete dupe trans with status_date as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[status_date] < G.[max_status_date]
AND G.[product_code] = journal.[product_code];

--delete dupe trans with journal_id as the flag
DELETE journal FROM journal, contacts
JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON G.[contact_id] = contacts.[contact_id]
WHERE journal.[journal_id] < G.[MaxID]
AND G.[product_code] = journal.[product_code];

Thanks again.|||The contact table has nothing to do with your delete, except to limit the deleted records to those that have a contact_id. I assume that contact_id is part of journal's natural key and that all records have a valid contact_id, so drop if from both your queries. (If you do need it for filtering, join it in the subquery.)

--delete dupe trans with status_date as the flag
DELETE
FROM journal
INNER JOIN
(select product_code, contact_id, max(status_date) as max_status_date
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[status_date] < G.[max_status_date]

--delete dupe trans with journal_id as the flag
DELETE journal
FROM journal
INNER JOIN
(select product_code, contact_id, max(journal_id) as maxID
from journal
group by product_code, contact_id) AS G
ON journal.[product_code] = G.[product_code]
and journal.[contact_id] = G.[contact_id]
and journal.[journal_id] < G.[MaxID]

It also appears that the first query should handle all product_code/contact_id duplicates except those with that share exactly the same status_date. If status_date stores only whole-date values, then I guess I see the point of the second delete statement, but otherwise I wouldn't expect you to get a high rowcount from it.

Now to your question; can this be done as a single SQL statement? Yes, but it would essentially require two nested subqueries, so I don't think you would get a big performance boost from it, and you would certainly have to sacrifice code clarity. I recommend that you leave it as two separate deletes.

Do I need to Reindex after truncate table?

Hi All,
A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
truncate table although the index(es) is still there but the data page
is 0.
Thank you for your help.
BobAfter truncating a table there is nothing to reindex. The index definitions
are still there and will apply to the new records.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"dengcharles@.hotmail.com" wrote:
> Hi All,
> A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
> statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
> truncate table although the index(es) is still there but the data page
> is 0.
> Thank you for your help.
> Bob
>|||Regarding your statement that "index(es) is still there but the data page is
0" you can use sp_spaceused 'your_table' to verify that everything is 0
(rows, data, index, etc).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
> After truncating a table there is nothing to reindex. The index definitions
> are still there and will apply to the new records.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "dengcharles@.hotmail.com" wrote:
> > Hi All,
> >
> > A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
> > statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
> > truncate table although the index(es) is still there but the data page
> > is 0.
> >
> > Thank you for your help.
> >
> > Bob
> >sql

Do I need to Reindex after truncate table?

Hi All,
A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
truncate table although the index(es) is still there but the data page
is 0.
Thank you for your help.
BobAfter truncating a table there is nothing to reindex. The index definitions
are still there and will apply to the new records.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"dengcharles@.hotmail.com" wrote:

> Hi All,
> A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
> statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
> truncate table although the index(es) is still there but the data page
> is 0.
> Thank you for your help.
> Bob
>|||Regarding your statement that "index(es) is still there but the data page is
0" you can use sp_spaceused 'your_table' to verify that everything is 0
(rows, data, index, etc).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> After truncating a table there is nothing to reindex. The index definition
s
> are still there and will apply to the new records.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "dengcharles@.hotmail.com" wrote:
>

Do I need to Reindex after truncate table?

Hi All,
A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
truncate table although the index(es) is still there but the data page
is 0.
Thank you for your help.
Bob
After truncating a table there is nothing to reindex. The index definitions
are still there and will apply to the new records.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"dengcharles@.hotmail.com" wrote:

> Hi All,
> A quick question: Do I have to Reindex a table (using "DBCC DBREINDEX"
> statement) AFTER I truncate a table? In SQL 2005 BOL it seems after
> truncate table although the index(es) is still there but the data page
> is 0.
> Thank you for your help.
> Bob
>
|||Regarding your statement that "index(es) is still there but the data page is
0" you can use sp_spaceused 'your_table' to verify that everything is 0
(rows, data, index, etc).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> After truncating a table there is nothing to reindex. The index definitions
> are still there and will apply to the new records.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "dengcharles@.hotmail.com" wrote:

Do i need to Fully process the CUBE, if Structural change to fact table happens

I have a requirement.
I have a CUBE in SQL 2000. I need to change the structure of Fact Table and i need to add one more dimension to my CUBE.
What are the problems will arise if i do this. i need to Fully process the CUBE?
PLS help meSQl 2000 - OLAP CUBE

Do i need primary key?

Here is the scenario
I have a table (let's call it tblTest) with 3 fields, UserID, Note,
NoteEnterDate (simple one to many relationship. user can have many notes)
Query would be to join it with user table and get the first and last name of
the user (based on UserID) and show the Note and NoteEnterDate (at this
point, I cannot think of any other query that uses this tblTest other than
this).
As far as index is concerned, I think I just need to create clustered index
on UserID.
I know it is good to declare a primary key for every table. Is there a need
for primary key for tblTest perhaps by adding another field called NoteID?
Thanks"Justin" <jus820@.hotmail.com> wrote in message
news:e4OGN5TbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> Here is the scenario
> I have a table (let's call it tblTest) with 3 fields, UserID, Note,
> NoteEnterDate (simple one to many relationship. user can have many notes)
> Query would be to join it with user table and get the first and last name
> of the user (based on UserID) and show the Note and NoteEnterDate (at this
> point, I cannot think of any other query that uses this tblTest other than
> this).
> As far as index is concerned, I think I just need to create clustered
> index on UserID.
> I know it is good to declare a primary key for every table. Is there a
> need for primary key for tblTest perhaps by adding another field called
> NoteID?
>
Would you allow the same (UserID,Note,NoteEnterDate) to be entered twice?
If so, add a synthetic NoteID to tell them apart. If not, create the PK on
(UserID, NoteEnterDate,Note).
David|||you don 't need to waste the space on another column, if the noteid is not
having significance. and if you are going to use an identity for it then its
of no use at all.
u can use user ID and NoteEnterDate as the primary key.
Hope this helps.
--
"Justin" wrote:

> Here is the scenario
> I have a table (let's call it tblTest) with 3 fields, UserID, Note,
> NoteEnterDate (simple one to many relationship. user can have many notes)
> Query would be to join it with user table and get the first and last name
of
> the user (based on UserID) and show the Note and NoteEnterDate (at this
> point, I cannot think of any other query that uses this tblTest other than
> this).
> As far as index is concerned, I think I just need to create clustered inde
x
> on UserID.
> I know it is good to declare a primary key for every table. Is there a ne
ed
> for primary key for tblTest perhaps by adding another field called NoteID?
> Thanks
>
>|||NoteEnterDate is smalldatetime datatype (since we don't basically care about
the seconds). User can type more than one note in a minute. If I create
primary key on UserID and NoteEnterDate, this is not allowed.
Even even if this was not allowed (creating more than one notes in a
minute), what would be the point of creating primary key on UserID,
NoteEnterDate?
Thanks
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0F05A821-5FFD-4A20-97E2-301565FFA187@.microsoft.com...
> you don 't need to waste the space on another column, if the noteid is not
> having significance. and if you are going to use an identity for it then
> its
> of no use at all.
> u can use user ID and NoteEnterDate as the primary key.
> Hope this helps.
> --
>
>
> "Justin" wrote:
>|||> Even even if this was not allowed (creating more than one notes in a
> minute), what would be the point of creating primary key on UserID,
> NoteEnterDate?
Maybe to prevent someone hitting refresh on your web page, 80 times in a
minute, and populating your table with redundant data.
A|||> you don 't need to waste the space on another column, if the noteid is not
> having significance. and if you are going to use an identity for it then
> its
> of no use at all.
I don't think I particularly agree. What harm does an IDENTITY column do?
I don't think it's as horrible as you make it out to be. The OP doesn't
know all future requirements now, so we can't really gauge its significance,
but let's say you wanted to track notes for some reason (e.g. show who has
viewed them and when). You might create another table called:
CREATE TABLE dbo.NoteTracking
(
? FOREIGN KEY REFERENCES dbo.tblTest(?),
ViewDate SMALLDATETIME
)
Surely you don't suggest it would be better to make up this primary key
UserID,NoteEnterDate and use that as your reference in the secondary table?
Like the OP, I'm not clear on what business sense a primary key on
UserID,NoteEnterDate would make, other than to fulfill the mantra "every
table must have a primary key." If it really is possible (and even
desirable) for a single user to enter two notes in one minute, then we're
back at square one.
A|||All tables must have at least one set of columns that can invariably
identify every row in the table. And there are obvious practical reasons to
explicitly declare one of those sets of columns as the primary key.
Anithsql

Do I need more integrity on my DB?

I am not sure I need more integrity on my DB. My DDL are down below.
thanks

************************************************** *****

create table person(
personId int identity(1,1) primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null
);

create table student(
studentId char(4) not null primary key,
personId int not null
);

alter table student
add constraint fk_person_student
foreign key (personId)
references person (personId)
;

create table instructor(
instructorId char(4) not null primary key,
instructorQual varchar(100) not null,
personId int not null
);

alter table instructor
add constraint fk_person_instructor
foreign key (personId)
references person (personId)
;

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

create table contact(
contactId int not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
street varchar(50) not null,
city varchar(25) not null,
state char(2) not null,
zip char(5) not null,
relationship varchar(25) not null,
phNum char(12) not null,
emailAdd varchar(50) null,
);

create table class(
classNum char(4) not null primary key,
className varchar(25) not null,
classDay char(3) not null,
classTime char(8) not null,
testNum char(5) not null
);

alter table class
add constraint fk_class_testnum
foreign key (testNum)
references test (testNum)
;

create table discount(
discountNum char(3) primary key,
discountDesc varchar(100) not null,
discountPer decimal(3,2) not null
);

create table test(
testNum char(5) primary key,
testName varchar(50) not null,
testDate smalldatetime not null,
testFee money not null,
);

create table studentClass(
studentId char(4) not null,
classNum char(4) not null,
pass char(1) not null
);

alter table studentClass
add constraint pk_studentclass primary key clustered (studentId, classNum)
;

alter table studentClass
add constraint fk_studentclass_studenttid
foreign key (studentId)
references student(studentId)
;

alter table studentClass
add constraint fk_studentclass_classnum
foreign key (classNum)
references class(classNum)
;

create table contractDiscount(
contractNum int not null,
discountNum char(3) not null
);

alter table contractDiscount
add constraint pk_contractdiscount primary key clustered (contractNum, discountNum)
;

alter table contractDiscount
add constraint fk_contractdiscount_contractnum
foreign key (contractNum)
references contract(contractNum)
;

alter table contractDiscount
add constraint fk_contractdiscount_discountnum
foreign key (discountNum)
references discount(discountNum)
;

create table instructorClass(
instructorId char(4) not null,
classNum char(4) not null,
);

alter table instructorClass
add constraint pk_instructorclass primary key clustered (instructorId, classNum)
;

alter table instructorClass
add constraint fk_instructorclass_instructorid
foreign key (instructorId)
references instructor(instructorId)
;

alter table instructorClass
add constraint fk_instructorclass_classnum
foreign key (classnum)
references class(classnum)
;Without knowing more about your business and the applications using the database, it's very hard to say if you need more. It's possible. This looks good though just from looking at it.|||as a specific example.

I have a contract table which is connected to student, contact and contractDiscount table with studentId and contactId as a FK. contractDiscount table is a intersection table.

IF I try to delete a contract record it won't let me, unlease I delete the discount relation on contractDiscount table. when I delete the relation with that certain row in discount on contractDiscount table then I am able to delete the contract.
However in that case, my student table and contact table record will be there without the existing contract.

My first question is do i even need to delete those records in student and contact table for intefrity of DB, when I delete the contract table?

second question is If I need to delete the record how I do that because without deleting contractDiscout record, I am not even able to delete the contract record.

Do I have to reinitialize snapshot if a new table added?

Hello,

After creating a publication for Merge Replication, if I need to add or drop a simple table, do I have to reinitialize the snapshot all over again?

Thanks

Ekrem ?nsoy

Hello again,

I'm sorry for taking your time but I've found the answer from the Books Online (http://msdn2.microsoft.com/en-us/library/ms152493.aspx)

Thanks

Ekrem ?nsoy

Do foreign keys generate implicit indexes?

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

Heres an example of how the foreign key constraint is being added:

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Bobbobdurie@.gmail.com (bobdurie@.gmail.com) writes:

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID?

In SQL Server, no.

Quote:

Originally Posted by

I've been told this is done in some databases, but i need to know for
sure if SQL Server does it. Has anyone heard of this before, on any
other databses perhaps?

I seem to recall having heard this about Sybase Anywhere.

Quote:

Originally Posted by

Heres an example of how the foreign key constraint is being added:
>
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

Indeed, it is often a good idea to add indexes on foreign keys, as it
can speed up deletions considerably. And it is not uncommon to search
for data in a table on a foreign key. However, as always, you should
think twice, and not add indexes blindly. For instance, if you have a
country-code column in a address table, there is little reason to add
an index on that column, since you don't delete countries very often.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I've been told this is done in some databases, .. <<

Yes, but better. Sybase SQL Anywhere (nee Watcom SQL) builds links
from all the FK references to the single PRIMARY KET/UNIQUE occurence
in the referenced table. Saves space, pre-joins tables for speed and
makes DRI actions both easy and fast.

SQL Server is still thinking in terms of "table = file" instead of
"table is part of a whole schema" and that "record =row" instead of
"row is made up of columns". Stonebreaker had a recent blog on column-
oriented design over contigous storage model.|||On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@.gmail.com"
<bobdurie@.gmail.comwrote:

Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.

-Tom.

Quote:

Originally Posted by

>If i create a simple table with a foreign key constraint, does it
>create an implicit index on that given ID? I've been told this is
>done in some databases, but i need to know for sure if SQL Server does
>it. Has anyone heard of this before, on any other databses perhaps?
>
>Heres an example of how the foreign key constraint is being added:
>
>ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
>[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
>REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
>My initial testing seems to indicate adding an index on the foreign
>key column helps, but i need to know for sure. Any insight would be
>greatly appreciated!
>
>Bob

|||Check with sysindexes to see if SQL Server does this too.

As Erland mentioned, SQL Server does not automatically index foreign key
columns. That task is left to the discretion of the DBA, who might choose
not to index the foreign column(s) due to low cardinality and static data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tom van Stiphout" <no.spam.tom7744@.cox.netwrote in message
news:8qqbf3190hk4cis52502s9th2ebsjjfpd5@.4ax.com...

Quote:

Originally Posted by

On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@.gmail.com"
<bobdurie@.gmail.comwrote:
>
Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.
>
-Tom.
>
>

Quote:

Originally Posted by

>>If i create a simple table with a foreign key constraint, does it
>>create an implicit index on that given ID? I've been told this is
>>done in some databases, but i need to know for sure if SQL Server does
>>it. Has anyone heard of this before, on any other databses perhaps?
>>
>>Heres an example of how the foreign key constraint is being added:
>>
>>ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
>>[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
>>REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>>
>>My initial testing seems to indicate adding an index on the foreign
>>key column helps, but i need to know for sure. Any insight would be
>>greatly appreciated!
>>
>>Bob

|||Also, analyse your query requirements then apply an Indexing Strategy

--

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
<bobdurie@.gmail.comwrote in message
news:1190404274.471197.197240@.n39g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?
>
Heres an example of how the foreign key constraint is being added:
>
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
>
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!
>
Bob
>

|||On Sep 24, 3:25 am, "Jack Vamvas" <DEL_TO_RE...@.del.comwrote:

Quote:

Originally Posted by

Also, analyse your query requirements then apply an Indexing Strategy
>
--
>
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
>
<bobdu...@.gmail.comwrote in message
>
news:1190404274.471197.197240@.n39g2000hsh.googlegr oups.com...
>

Quote:

Originally Posted by

If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?

>

Quote:

Originally Posted by

Heres an example of how the foreign key constraint is being added:

>

Quote:

Originally Posted by

ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])

>

Quote:

Originally Posted by

My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!

>

Quote:

Originally Posted by

Bob

Thanks for all the responses on this, its much appreciated!!! I also
found this article which makes me realize other people have had the
same misconceptions as me :)
http://www.sqlskills.com/blogs/kimb...yColu mns.aspx