Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?
I take it you mean that the 4 column together comprises the PK (i.e., a composite PK, you can only
have one PK but it can be over several columns).
Technically, it doesn't matter. However, when humans read database schemas, it seems natural to have
the PK as the left-most columns. I.e., it is an esthetic issue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in message
news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> Let say I have 10 columns and 4 of them is a primary keys.
> The question is where should I place these 4 columns (with
> primary keys)? Is it at the most left?
|||Hi,Tibor
Did the OP mean also sorting columns within PK? I mean if you have primary
key on A,B,C,D ,does it matter a place of the column?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I take it you mean that the 4 column together comprises the PK (i.e., a
composite PK, you can only
> have one PK but it can be over several columns).
> Technically, it doesn't matter. However, when humans read database
schemas, it seems natural to have
> the PK as the left-most columns. I.e., it is an esthetic issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message
> news:084e01c48362$1c886130$3a01280a@.phx.gbl...
>
|||The first column in the composite index (key) should be the one, you most
often query on.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message news:084e01c48362$1c886130$3a01280a@.phx.gbl...
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?
|||Good point Uri, I read the question as "between all columns in the table". Dan posted a good reply
if the question is "order within the PK columns".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u$ThY62gEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,Tibor
> Did the OP mean also sorting columns within PK? I mean if you have primary
> key on A,B,C,D ,does it matter a place of the column?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> composite PK, you can only
> schemas, it seems natural to have
> message
>
Showing posts with label keys. Show all posts
Showing posts with label keys. Show all posts
Friday, March 23, 2012
question on the primary key
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?I take it you mean that the 4 column together comprises the PK (i.e., a comp
osite PK, you can only
have one PK but it can be over several columns).
Technically, it doesn't matter. However, when humans read database schemas,
it seems natural to have
the PK as the left-most columns. I.e., it is an esthetic issue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in mess
age
news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> Let say I have 10 columns and 4 of them is a primary keys.
> The question is where should I place these 4 columns (with
> primary keys)? Is it at the most left?|||Hi,Tibor
Did the OP mean also sorting columns within PK? I mean if you have primary
key on A,B,C,D ,does it matter a place of the column?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I take it you mean that the 4 column together comprises the PK (i.e., a
composite PK, you can only
> have one PK but it can be over several columns).
> Technically, it doesn't matter. However, when humans read database
schemas, it seems natural to have
> the PK as the left-most columns. I.e., it is an esthetic issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message
> news:084e01c48362$1c886130$3a01280a@.phx.gbl...
>|||The first column in the composite index (key) should be the one, you most
often query on.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message news:084e01c48362$1c886130$3a01280a@.phx.gbl...
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?|||Good point Uri, I read the question as "between all columns in the table". D
an posted a good reply
if the question is "order within the PK columns".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u$ThY62gEHA.636@.TK2MSFTNGP12.phx.gbl..
.
> Hi,Tibor
> Did the OP mean also sorting columns within PK? I mean if you have primar
y
> key on A,B,C,D ,does it matter a place of the column?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> composite PK, you can only
> schemas, it seems natural to have
> message
>
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?I take it you mean that the 4 column together comprises the PK (i.e., a comp
osite PK, you can only
have one PK but it can be over several columns).
Technically, it doesn't matter. However, when humans read database schemas,
it seems natural to have
the PK as the left-most columns. I.e., it is an esthetic issue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in mess
age
news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> Let say I have 10 columns and 4 of them is a primary keys.
> The question is where should I place these 4 columns (with
> primary keys)? Is it at the most left?|||Hi,Tibor
Did the OP mean also sorting columns within PK? I mean if you have primary
key on A,B,C,D ,does it matter a place of the column?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I take it you mean that the 4 column together comprises the PK (i.e., a
composite PK, you can only
> have one PK but it can be over several columns).
> Technically, it doesn't matter. However, when humans read database
schemas, it seems natural to have
> the PK as the left-most columns. I.e., it is an esthetic issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message
> news:084e01c48362$1c886130$3a01280a@.phx.gbl...
>|||The first column in the composite index (key) should be the one, you most
often query on.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message news:084e01c48362$1c886130$3a01280a@.phx.gbl...
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?|||Good point Uri, I read the question as "between all columns in the table". D
an posted a good reply
if the question is "order within the PK columns".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u$ThY62gEHA.636@.TK2MSFTNGP12.phx.gbl..
.
> Hi,Tibor
> Did the OP mean also sorting columns within PK? I mean if you have primar
y
> key on A,B,C,D ,does it matter a place of the column?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> composite PK, you can only
> schemas, it seems natural to have
> message
>
question on the primary key
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?I take it you mean that the 4 column together comprises the PK (i.e., a composite PK, you can only
have one PK but it can be over several columns).
Technically, it doesn't matter. However, when humans read database schemas, it seems natural to have
the PK as the left-most columns. I.e., it is an esthetic issue.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in message
news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> Let say I have 10 columns and 4 of them is a primary keys.
> The question is where should I place these 4 columns (with
> primary keys)? Is it at the most left?|||Hi,Tibor
Did the OP mean also sorting columns within PK? I mean if you have primary
key on A,B,C,D ,does it matter a place of the column?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I take it you mean that the 4 column together comprises the PK (i.e., a
composite PK, you can only
> have one PK but it can be over several columns).
> Technically, it doesn't matter. However, when humans read database
schemas, it seems natural to have
> the PK as the left-most columns. I.e., it is an esthetic issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message
> news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> > Let say I have 10 columns and 4 of them is a primary keys.
> > The question is where should I place these 4 columns (with
> > primary keys)? Is it at the most left?
>|||The first column in the composite index (key) should be the one, you most
often query on.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message news:084e01c48362$1c886130$3a01280a@.phx.gbl...
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?|||Good point Uri, I read the question as "between all columns in the table". Dan posted a good reply
if the question is "order within the PK columns".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u$ThY62gEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,Tibor
> Did the OP mean also sorting columns within PK? I mean if you have primary
> key on A,B,C,D ,does it matter a place of the column?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > I take it you mean that the 4 column together comprises the PK (i.e., a
> composite PK, you can only
> > have one PK but it can be over several columns).
> >
> > Technically, it doesn't matter. However, when humans read database
> schemas, it seems natural to have
> > the PK as the left-most columns. I.e., it is an esthetic issue.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
> message
> > news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> > > Let say I have 10 columns and 4 of them is a primary keys.
> > > The question is where should I place these 4 columns (with
> > > primary keys)? Is it at the most left?
> >
> >
>
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?I take it you mean that the 4 column together comprises the PK (i.e., a composite PK, you can only
have one PK but it can be over several columns).
Technically, it doesn't matter. However, when humans read database schemas, it seems natural to have
the PK as the left-most columns. I.e., it is an esthetic issue.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in message
news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> Let say I have 10 columns and 4 of them is a primary keys.
> The question is where should I place these 4 columns (with
> primary keys)? Is it at the most left?|||Hi,Tibor
Did the OP mean also sorting columns within PK? I mean if you have primary
key on A,B,C,D ,does it matter a place of the column?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I take it you mean that the 4 column together comprises the PK (i.e., a
composite PK, you can only
> have one PK but it can be over several columns).
> Technically, it doesn't matter. However, when humans read database
schemas, it seems natural to have
> the PK as the left-most columns. I.e., it is an esthetic issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message
> news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> > Let say I have 10 columns and 4 of them is a primary keys.
> > The question is where should I place these 4 columns (with
> > primary keys)? Is it at the most left?
>|||The first column in the composite index (key) should be the one, you most
often query on.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message news:084e01c48362$1c886130$3a01280a@.phx.gbl...
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?|||Good point Uri, I read the question as "between all columns in the table". Dan posted a good reply
if the question is "order within the PK columns".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u$ThY62gEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,Tibor
> Did the OP mean also sorting columns within PK? I mean if you have primary
> key on A,B,C,D ,does it matter a place of the column?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > I take it you mean that the 4 column together comprises the PK (i.e., a
> composite PK, you can only
> > have one PK but it can be over several columns).
> >
> > Technically, it doesn't matter. However, when humans read database
> schemas, it seems natural to have
> > the PK as the left-most columns. I.e., it is an esthetic issue.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
> message
> > news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> > > Let say I have 10 columns and 4 of them is a primary keys.
> > > The question is where should I place these 4 columns (with
> > > primary keys)? Is it at the most left?
> >
> >
>
Tuesday, March 20, 2012
question on recompiles
On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
TIA,
ChrisR
ChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
TIA,
ChrisR
ChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
question on recompiles
On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
>> On Monday I added a whole bunch of indexes. They were pretty much just
>> indexes on foreign keys. Should I have recomiled all the sp's? Since I
>> didn't, should I bother now? According to BOL:
>> But if a new index is added from which the stored procedure might
>> benefit, optimization does not automatically happen (until the next
>> time the stored procedure is run after SQL Server is restarted).
>> Wouldnt they also get recompiled the first time they were run,
>> whether SQL was restarted or not?
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
>> On Monday I added a whole bunch of indexes. They were pretty much just
>> indexes on foreign keys. Should I have recomiled all the sp's? Since I
>> didn't, should I bother now? According to BOL:
>> But if a new index is added from which the stored procedure might
>> benefit, optimization does not automatically happen (until the next
>> time the stored procedure is run after SQL Server is restarted).
>> Wouldnt they also get recompiled the first time they were run,
>> whether SQL was restarted or not?
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
question on recompiles
On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
Wednesday, March 7, 2012
Question on encryption/ keys/ certificates/ etc.
SQL2K5
SP1
I was able to sucessfully create a Master Key, create a Certificate, create
a Symmetric Key and assign it to the Certificate, insert encrypted data, and
then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
topic and Im under the impression that I should also have needed too:
Set Force Encryption to Yes.
Configure the DB engine to use a Cert.
Reboot the box.
But I didn't have to do any of that. I just went and verified the settings
and thats not how SQL is configured. Im obviosuly missing something pretty
big here, can someone please assist?
TIA, ChrisRThat's 2 different things, what you've been doing is encrypting data stored
in the database. The other stuff you are talking about is encrypting traffic
from clients to SQL Server and vice versa. If you don't have that
requirement then don't worry about it. In SQL 2005, standard SQL logins are
encrypted anyway by a self generated certificate however general traffic
to/from the server (batches/results etc) are not. They are 2 completely
different and separate things.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"ChrisR" <NotAChance@.ms.com> wrote in message
news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> SQL2K5
> SP1
> I was able to sucessfully create a Master Key, create a Certificate,
> create
> a Symmetric Key and assign it to the Certificate, insert encrypted data,
> and
> then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
> topic and Im under the impression that I should also have needed too:
> Set Force Encryption to Yes.
> Configure the DB engine to use a Cert.
> Reboot the box.
> But I didn't have to do any of that. I just went and verified the settings
> and thats not how SQL is configured. Im obviosuly missing something pretty
> big here, can someone please assist?
> TIA, ChrisR
>|||As mentioned, I was missing something pretty big. ;-)
Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaL$0QCtGHA.4784@.TK2MSFTNGP04.phx.gbl...
> That's 2 different things, what you've been doing is encrypting data
stored
> in the database. The other stuff you are talking about is encrypting
traffic
> from clients to SQL Server and vice versa. If you don't have that
> requirement then don't worry about it. In SQL 2005, standard SQL logins
are
> encrypted anyway by a self generated certificate however general traffic
> to/from the server (batches/results etc) are not. They are 2 completely
> different and separate things.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
the[vbcol=seagreen]
settings[vbcol=seagreen]
pretty[vbcol=seagreen]
>
SP1
I was able to sucessfully create a Master Key, create a Certificate, create
a Symmetric Key and assign it to the Certificate, insert encrypted data, and
then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
topic and Im under the impression that I should also have needed too:
Set Force Encryption to Yes.
Configure the DB engine to use a Cert.
Reboot the box.
But I didn't have to do any of that. I just went and verified the settings
and thats not how SQL is configured. Im obviosuly missing something pretty
big here, can someone please assist?
TIA, ChrisRThat's 2 different things, what you've been doing is encrypting data stored
in the database. The other stuff you are talking about is encrypting traffic
from clients to SQL Server and vice versa. If you don't have that
requirement then don't worry about it. In SQL 2005, standard SQL logins are
encrypted anyway by a self generated certificate however general traffic
to/from the server (batches/results etc) are not. They are 2 completely
different and separate things.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"ChrisR" <NotAChance@.ms.com> wrote in message
news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> SQL2K5
> SP1
> I was able to sucessfully create a Master Key, create a Certificate,
> create
> a Symmetric Key and assign it to the Certificate, insert encrypted data,
> and
> then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
> topic and Im under the impression that I should also have needed too:
> Set Force Encryption to Yes.
> Configure the DB engine to use a Cert.
> Reboot the box.
> But I didn't have to do any of that. I just went and verified the settings
> and thats not how SQL is configured. Im obviosuly missing something pretty
> big here, can someone please assist?
> TIA, ChrisR
>|||As mentioned, I was missing something pretty big. ;-)
Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaL$0QCtGHA.4784@.TK2MSFTNGP04.phx.gbl...
> That's 2 different things, what you've been doing is encrypting data
stored
> in the database. The other stuff you are talking about is encrypting
traffic
> from clients to SQL Server and vice versa. If you don't have that
> requirement then don't worry about it. In SQL 2005, standard SQL logins
are
> encrypted anyway by a self generated certificate however general traffic
> to/from the server (batches/results etc) are not. They are 2 completely
> different and separate things.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
the[vbcol=seagreen]
settings[vbcol=seagreen]
pretty[vbcol=seagreen]
>
Question on encryption/ keys/ certificates/ etc.
SQL2K5
SP1
I was able to sucessfully create a Master Key, create a Certificate, create
a Symmetric Key and assign it to the Certificate, insert encrypted data, and
then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
topic and Im under the impression that I should also have needed too:
Set Force Encryption to Yes.
Configure the DB engine to use a Cert.
Reboot the box.
But I didn't have to do any of that. I just went and verified the settings
and thats not how SQL is configured. Im obviosuly missing something pretty
big here, can someone please assist?
TIA, ChrisRThat's 2 different things, what you've been doing is encrypting data stored
in the database. The other stuff you are talking about is encrypting traffic
from clients to SQL Server and vice versa. If you don't have that
requirement then don't worry about it. In SQL 2005, standard SQL logins are
encrypted anyway by a self generated certificate however general traffic
to/from the server (batches/results etc) are not. They are 2 completely
different and separate things.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"ChrisR" <NotAChance@.ms.com> wrote in message
news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> SQL2K5
> SP1
> I was able to sucessfully create a Master Key, create a Certificate,
> create
> a Symmetric Key and assign it to the Certificate, insert encrypted data,
> and
> then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
> topic and Im under the impression that I should also have needed too:
> Set Force Encryption to Yes.
> Configure the DB engine to use a Cert.
> Reboot the box.
> But I didn't have to do any of that. I just went and verified the settings
> and thats not how SQL is configured. Im obviosuly missing something pretty
> big here, can someone please assist?
> TIA, ChrisR
>|||As mentioned, I was missing something pretty big. ;-)
Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaL$0QCtGHA.4784@.TK2MSFTNGP04.phx.gbl...
> That's 2 different things, what you've been doing is encrypting data
stored
> in the database. The other stuff you are talking about is encrypting
traffic
> from clients to SQL Server and vice versa. If you don't have that
> requirement then don't worry about it. In SQL 2005, standard SQL logins
are
> encrypted anyway by a self generated certificate however general traffic
> to/from the server (batches/results etc) are not. They are 2 completely
> different and separate things.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> > SQL2K5
> > SP1
> >
> > I was able to sucessfully create a Master Key, create a Certificate,
> > create
> > a Symmetric Key and assign it to the Certificate, insert encrypted data,
> > and
> > then decrypt/ read that data. Pretty cool stuff. But Im reading up on
the
> > topic and Im under the impression that I should also have needed too:
> >
> > Set Force Encryption to Yes.
> > Configure the DB engine to use a Cert.
> > Reboot the box.
> >
> > But I didn't have to do any of that. I just went and verified the
settings
> > and thats not how SQL is configured. Im obviosuly missing something
pretty
> > big here, can someone please assist?
> >
> > TIA, ChrisR
> >
> >
>
SP1
I was able to sucessfully create a Master Key, create a Certificate, create
a Symmetric Key and assign it to the Certificate, insert encrypted data, and
then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
topic and Im under the impression that I should also have needed too:
Set Force Encryption to Yes.
Configure the DB engine to use a Cert.
Reboot the box.
But I didn't have to do any of that. I just went and verified the settings
and thats not how SQL is configured. Im obviosuly missing something pretty
big here, can someone please assist?
TIA, ChrisRThat's 2 different things, what you've been doing is encrypting data stored
in the database. The other stuff you are talking about is encrypting traffic
from clients to SQL Server and vice versa. If you don't have that
requirement then don't worry about it. In SQL 2005, standard SQL logins are
encrypted anyway by a self generated certificate however general traffic
to/from the server (batches/results etc) are not. They are 2 completely
different and separate things.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"ChrisR" <NotAChance@.ms.com> wrote in message
news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> SQL2K5
> SP1
> I was able to sucessfully create a Master Key, create a Certificate,
> create
> a Symmetric Key and assign it to the Certificate, insert encrypted data,
> and
> then decrypt/ read that data. Pretty cool stuff. But Im reading up on the
> topic and Im under the impression that I should also have needed too:
> Set Force Encryption to Yes.
> Configure the DB engine to use a Cert.
> Reboot the box.
> But I didn't have to do any of that. I just went and verified the settings
> and thats not how SQL is configured. Im obviosuly missing something pretty
> big here, can someone please assist?
> TIA, ChrisR
>|||As mentioned, I was missing something pretty big. ;-)
Thanks Jasper.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaL$0QCtGHA.4784@.TK2MSFTNGP04.phx.gbl...
> That's 2 different things, what you've been doing is encrypting data
stored
> in the database. The other stuff you are talking about is encrypting
traffic
> from clients to SQL Server and vice versa. If you don't have that
> requirement then don't worry about it. In SQL 2005, standard SQL logins
are
> encrypted anyway by a self generated certificate however general traffic
> to/from the server (batches/results etc) are not. They are 2 completely
> different and separate things.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:esEA%23%23AtGHA.1876@.TK2MSFTNGP06.phx.gbl...
> > SQL2K5
> > SP1
> >
> > I was able to sucessfully create a Master Key, create a Certificate,
> > create
> > a Symmetric Key and assign it to the Certificate, insert encrypted data,
> > and
> > then decrypt/ read that data. Pretty cool stuff. But Im reading up on
the
> > topic and Im under the impression that I should also have needed too:
> >
> > Set Force Encryption to Yes.
> > Configure the DB engine to use a Cert.
> > Reboot the box.
> >
> > But I didn't have to do any of that. I just went and verified the
settings
> > and thats not how SQL is configured. Im obviosuly missing something
pretty
> > big here, can someone please assist?
> >
> > TIA, ChrisR
> >
> >
>
Monday, February 20, 2012
Question on attribute keys of measure group
Hi, dear friends,
I encountered a problem processing my cube with the error message telling: processing measure_group_name (which only has a count as its only measure)failed as attribute keys can not be found? Why is that? I have no idea why is that? Does it mean I have to include all the keys of the fact table where the measure group from in the measure groups as measures? Otherwise the system failed to identify the distinct count of the system? Or whatever measures the measure group contains, it alwys have to include all the keys including the dimension keys of dimensions which the measure group is related to?
Thanks in advance for your help and advices and I am always looking forward to hearing from you shortly from you.
With best regards,
Yours sincerely,
This message means that a column used to join the measure group to a dimension contains a value not found in the dimension. This is usually caused by a referential integrity error - the FK does not existing in the PK of the lookup table. However, it can also be caused by the presence of a null in the FK as by default this null will be converted to zero or blank which probably does not exist as a key in the dimension. You can handle the situation of nulls in FK columns within the fact table by setting the NullProcessing option in the advanced tab of the measure group relationship dialog from the Dimension Usage tab of the cube editor.|||Hi, Matt,
Thanks a lot. Yes, the problem is the matching of the PK and the FK between the tables.
However, the data source view logical key and the relationships between tables are really annoying then, as it is not able to check the integrity of the tables?
With best regards,
Yours sincerely,
|||So it sounds like you would like to have an option on the Data Source View to validate logical keys and relationships. I'd recommend you file a feature request on http://connect.microsoft.com/SQLServer/Feedback. Such feature requests will be automatically added to our issue tracking system for consideration in future releases and those that originate from a customer such as yourself get extra consideration since we know it's a real world scenario. If you can describe the scenario it will also help as it will allow us to better identify which requests will help the most people and how to implement them so that they actually provide the help intended.
Thanks, Matt
|||Hi, Matt,
Thanks a lot and this sounds really a good idea of it.
With best regards,
Yours sincerely,
Subscribe to:
Posts (Atom)