Dear all,
I want to know all the possible values for the status field bring up for
sysprocesses table. Values such 'running' or 'sleeping' seems very
evident but there is one so-called 'DEF-WK...' or something like that which
I haven't idea.
In this occasion I am not be able to find it inside the BOL
Does anyone know how do I figure out such values?
Thanks in advance,
EnricHi
Look at the code from the system SP sp_who2
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:C9B83081-19A4-4A33-AD4C-F75AE450B394@.microsoft.com...
> Dear all,
> I want to know all the possible values for the status field bring up for
> sysprocesses table. Values such 'running' or 'sleeping' seems very
> evident but there is one so-called 'DEF-WK...' or something like that
> which
> I haven't idea.
> In this occasion I am not be able to find it inside the BOL
> Does anyone know how do I figure out such values?
> Thanks in advance,
> Enric
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Indexing with null or zero
I am adding an int field to a table that will be used to link it to another
table when it has a number in it. I plan to index it for faster access but
wondered if my default for that table should be NULL or zero or if it makes
a difference. Thanks.
DavidTo an index, NULL is just a value (as is 0). So use whatever makes most sens
e to you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.252@.TK2MSFTNGP12.ph
x.gbl...
>I am adding an int field to a table that will be used to link it to another
table when it has a
>number in it. I plan to index it for faster access but wondered if my defa
ult for that table
>should be NULL or zero or if it makes a difference. Thanks.
> David
>|||I have to say that it would be nice if there was a built-in means to create
a
unique constraint on a column such that nulls are ignored. Going the route o
f
indexed views or triggers is a colossal pain.
Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse
> to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uy%238XzdSFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||Tibor,
I don't think this is quite true. A column's nullability (assuming
that is part of the choice here) affects what query plans can be
considered. Also, indexes aside, there can be different and
unexpected results depending on whether NULL or 0 is used.
An example we often see in the newsgroups is
select this, that
from T
where T.parent not in (
select parent
from T_children
)
which returns no rows even when there are T.parent values
not present in the T_children table.
There are other differences less likely to be an issue, like
calculating percentages:
select
T.parent,
sum(case when parent = T_children.parent then 1.00 else 0.00 end) /
count(T_children.parent) as percentage
from T join T_children
on T.parent = T_children.parent
group by T.parent
Unfortunately, while using NULL is probably truer to the principles
of good modeling, using 0 may have some practical advantages, if
the referring column can then be declared as NOT NULL. To maintain
referential integrity via a foreign key constraint, NULL is a better choice,
since 0 requires putting a dummy entry into the referenced table.
Steve Kass
Drew University
Tibor Karaszi wrote:
>To an index, NULL is just a value (as is 0). So use whatever makes most sen
se to you.
>
>|||
Thomas wrote:
>I have to say that it would be nice if there was a built-in means to create
a
>unique constraint on a column such that nulls are ignored. Going the route
of
>indexed views or triggers is a colossal pain.
>
>
There is at least one solution using an indexed column instead of
an indexed view. Functionally, it is much the same, but you might
find it easier to maintain.
http://groups.google.co.uk/groups?q...B8-B7567063D1CC
SK
>Thomas
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>
>|||Hmm..That's an clever solution. For the purposes of other people reading thi
s
thread, the idea is to create a computed column that either equals the colum
n on
which you really want a unique index or the primary key when that value is n
ull.
You would then put the unique index on this computed column.
Granted, it does mean dealing with computed columns which can be a persnicke
ty
but it does get around the issue.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
> Thomas wrote:
>
> There is at least one solution using an indexed column instead of
> an indexed view. Functionally, it is much the same, but you might
> find it easier to maintain.
> http://groups.google.co.uk/groups?q...B8-B7567063D1CC
> SK
>|||If a Foreign Key column allows null, then this is definitely NOT the same as
putting a zero in the column. A value of zero MUST exist in the reference
table, a null value does NOT have to (indeed it cannot exist) as PK in the
reference table. The distinction is critical, it is the dfference between a
[one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
relationship.
Example, In a [one]-to-[zero or many] Employees have zero o many timecard
punches, but for each time card punch there must be one employee - and only
one an employee.
In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
have zero or many books checked out to them, and each book can be checked
out to zero or one library patron...
The only way to model this distinction is by allowing, (and using) null
values in the Foreign Key column on the many side of the DRI constraint.
"Tibor Karaszi" wrote:
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.25
2@.TK2MSFTNGP12.phx.gbl...
>
>|||When I want a Foreign Key Column to be unique, but Allow any number of Nulls
,
(that's a [One]-to-[Zero or One] Relationship, I use an extra table
CREATE TABLE [TabA] (
[AID] [int] NOT NULL ,
[Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_TabA] PRIMARY KEY CLUSTERED
([AID]) ON [PRIMARY]
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabB] (
[BID] [int] NOT NULL ,
[AID] [int] NULL ,
CONSTRAINT [PK_TabB] PRIMARY KEY CLUSTERED
( [BID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabB_TabA] FOREIGN KEY
([AID]) REFERENCES [TabA] ([AID])
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabC] (
[BID] [int] NOT NULL ,
[AID] [int] NOT NULL ,
CONSTRAINT [PK_TabC] PRIMARY KEY CLUSTERED
([BID]) ON [PRIMARY] ,
CONSTRAINT [IX_TabCUniqueAID] UNIQUE NONCLUSTERED
([AID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabC_TabB] FOREIGN KEY
([BID]) REFERENCES [TabB] ([BID])
) ON [PRIMARY]
GO
-- Then I add ALL the child records to TabB, Both those with null values of
AID, and Non-Null values of AID, but only add the Non-Null AID Records to
TabC... TabB is teh real table, and has all the other attriobutes in it,
TabC is only there to enforce uniqueness on the Non-Null Values of AID in Ta
bB
"Thomas" wrote:
> Hmm..That's an clever solution. For the purposes of other people reading t
his
> thread, the idea is to create a computed column that either equals the col
umn on
> which you really want a unique index or the primary key when that value is
null.
> You would then put the unique index on this computed column.
> Granted, it does mean dealing with computed columns which can be a persnic
kety
> but it does get around the issue.
>
> Thomas
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
>|||Perhaps I was in a bit too much hurry when responding. I was looking at it p
urely from the index'
perspective, how the data is stored in the index. You definitely want to thi
nk through semantics
carefully. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
> If a Foreign Key column allows null, then this is definitely NOT the same
as
> putting a zero in the column. A value of zero MUST exist in the referenc
e
> table, a null value does NOT have to (indeed it cannot exist) as PK in the
> reference table. The distinction is critical, it is the dfference between
a
> [one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
> relationship.
>
> Example, In a [one]-to-[zero or many] Employees have zero o many timecard
> punches, but for each time card punch there must be one employee - and on
ly
> one an employee.
> In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
> have zero or many books checked out to them, and each book can be checked
> out to zero or one library patron...
> The only way to model this distinction is by allowing, (and using) null
> values in the Foreign Key column on the many side of the DRI constraint.
> "Tibor Karaszi" wrote:
>|||Tibor,
Neglected to make clear that details in my post,were, of course, not
directed at you... figured you just overlooked that. I slip into
pedanticLand way too easily...
Respectfully, Charly
"Tibor Karaszi" wrote:
> Perhaps I was in a bit too much hurry when responding. I was looking at it
purely from the index'
> perspective, how the data is stored in the index. You definitely want to t
hink through semantics
> carefully. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
>
>
table when it has a number in it. I plan to index it for faster access but
wondered if my default for that table should be NULL or zero or if it makes
a difference. Thanks.
DavidTo an index, NULL is just a value (as is 0). So use whatever makes most sens
e to you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.252@.TK2MSFTNGP12.ph
x.gbl...
>I am adding an int field to a table that will be used to link it to another
table when it has a
>number in it. I plan to index it for faster access but wondered if my defa
ult for that table
>should be NULL or zero or if it makes a difference. Thanks.
> David
>|||I have to say that it would be nice if there was a built-in means to create
a
unique constraint on a column such that nulls are ignored. Going the route o
f
indexed views or triggers is a colossal pain.
Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse
> to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:uy%238XzdSFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||Tibor,
I don't think this is quite true. A column's nullability (assuming
that is part of the choice here) affects what query plans can be
considered. Also, indexes aside, there can be different and
unexpected results depending on whether NULL or 0 is used.
An example we often see in the newsgroups is
select this, that
from T
where T.parent not in (
select parent
from T_children
)
which returns no rows even when there are T.parent values
not present in the T_children table.
There are other differences less likely to be an issue, like
calculating percentages:
select
T.parent,
sum(case when parent = T_children.parent then 1.00 else 0.00 end) /
count(T_children.parent) as percentage
from T join T_children
on T.parent = T_children.parent
group by T.parent
Unfortunately, while using NULL is probably truer to the principles
of good modeling, using 0 may have some practical advantages, if
the referring column can then be declared as NOT NULL. To maintain
referential integrity via a foreign key constraint, NULL is a better choice,
since 0 requires putting a dummy entry into the referenced table.
Steve Kass
Drew University
Tibor Karaszi wrote:
>To an index, NULL is just a value (as is 0). So use whatever makes most sen
se to you.
>
>|||
Thomas wrote:
>I have to say that it would be nice if there was a built-in means to create
a
>unique constraint on a column such that nulls are ignored. Going the route
of
>indexed views or triggers is a colossal pain.
>
>
There is at least one solution using an indexed column instead of
an indexed view. Functionally, it is much the same, but you might
find it easier to maintain.
http://groups.google.co.uk/groups?q...B8-B7567063D1CC
SK
>Thomas
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OmrRbKeSFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>
>|||Hmm..That's an clever solution. For the purposes of other people reading thi
s
thread, the idea is to create a computed column that either equals the colum
n on
which you really want a unique index or the primary key when that value is n
ull.
You would then put the unique index on this computed column.
Granted, it does mean dealing with computed columns which can be a persnicke
ty
but it does get around the issue.
Thomas
"Steve Kass" <skass@.drew.edu> wrote in message
news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
> Thomas wrote:
>
> There is at least one solution using an indexed column instead of
> an indexed view. Functionally, it is much the same, but you might
> find it easier to maintain.
> http://groups.google.co.uk/groups?q...B8-B7567063D1CC
> SK
>|||If a Foreign Key column allows null, then this is definitely NOT the same as
putting a zero in the column. A value of zero MUST exist in the reference
table, a null value does NOT have to (indeed it cannot exist) as PK in the
reference table. The distinction is critical, it is the dfference between a
[one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
relationship.
Example, In a [one]-to-[zero or many] Employees have zero o many timecard
punches, but for each time card punch there must be one employee - and only
one an employee.
In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
have zero or many books checked out to them, and each book can be checked
out to zero or one library patron...
The only way to model this distinction is by allowing, (and using) null
values in the Foreign Key column on the many side of the DRI constraint.
"Tibor Karaszi" wrote:
> To an index, NULL is just a value (as is 0). So use whatever makes most se
nse to you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message news:uy%238XzdSFHA.25
2@.TK2MSFTNGP12.phx.gbl...
>
>|||When I want a Foreign Key Column to be unique, but Allow any number of Nulls
,
(that's a [One]-to-[Zero or One] Relationship, I use an extra table
CREATE TABLE [TabA] (
[AID] [int] NOT NULL ,
[Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_TabA] PRIMARY KEY CLUSTERED
([AID]) ON [PRIMARY]
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabB] (
[BID] [int] NOT NULL ,
[AID] [int] NULL ,
CONSTRAINT [PK_TabB] PRIMARY KEY CLUSTERED
( [BID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabB_TabA] FOREIGN KEY
([AID]) REFERENCES [TabA] ([AID])
) ON [PRIMARY]
GO
-- ****************************************
***
CREATE TABLE [TabC] (
[BID] [int] NOT NULL ,
[AID] [int] NOT NULL ,
CONSTRAINT [PK_TabC] PRIMARY KEY CLUSTERED
([BID]) ON [PRIMARY] ,
CONSTRAINT [IX_TabCUniqueAID] UNIQUE NONCLUSTERED
([AID]) ON [PRIMARY] ,
CONSTRAINT [FK_TabC_TabB] FOREIGN KEY
([BID]) REFERENCES [TabB] ([BID])
) ON [PRIMARY]
GO
-- Then I add ALL the child records to TabB, Both those with null values of
AID, and Non-Null values of AID, but only add the Non-Null AID Records to
TabC... TabB is teh real table, and has all the other attriobutes in it,
TabC is only there to enforce uniqueness on the Non-Null Values of AID in Ta
bB
"Thomas" wrote:
> Hmm..That's an clever solution. For the purposes of other people reading t
his
> thread, the idea is to create a computed column that either equals the col
umn on
> which you really want a unique index or the primary key when that value is
null.
> You would then put the unique index on this computed column.
> Granted, it does mean dealing with computed columns which can be a persnic
kety
> but it does get around the issue.
>
> Thomas
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uSJlWXeSFHA.3788@.tk2msftngp13.phx.gbl...
>
>|||Perhaps I was in a bit too much hurry when responding. I was looking at it p
urely from the index'
perspective, how the data is stored in the index. You definitely want to thi
nk through semantics
carefully. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
> If a Foreign Key column allows null, then this is definitely NOT the same
as
> putting a zero in the column. A value of zero MUST exist in the referenc
e
> table, a null value does NOT have to (indeed it cannot exist) as PK in the
> reference table. The distinction is critical, it is the dfference between
a
> [one]-to-[zero or many] relationship, and a [zero or one]-to-[zero or many]
> relationship.
>
> Example, In a [one]-to-[zero or many] Employees have zero o many timecard
> punches, but for each time card punch there must be one employee - and on
ly
> one an employee.
> In a [zero or one]-to-[zero or many] relatonship, Each Library patron can
> have zero or many books checked out to them, and each book can be checked
> out to zero or one library patron...
> The only way to model this distinction is by allowing, (and using) null
> values in the Foreign Key column on the many side of the DRI constraint.
> "Tibor Karaszi" wrote:
>|||Tibor,
Neglected to make clear that details in my post,were, of course, not
directed at you... figured you just overlooked that. I slip into
pedanticLand way too easily...
Respectfully, Charly
"Tibor Karaszi" wrote:
> Perhaps I was in a bit too much hurry when responding. I was looking at it
purely from the index'
> perspective, how the data is stored in the index. You definitely want to t
hink through semantics
> carefully. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:EC2328B5-9A67-4E1A-A096-9B071AE179EB@.microsoft.com...
>
>
Monday, March 19, 2012
Indexing on calculated fields
I would like to index on a calculated field. I need to index records by
w
and want to store the W Number. Below is a test schema that
should work but the index will not create.
drop table calcdate;
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,[basedate],0)
);
create index calcdate_idx ON calcdate(calcsun);
I get the error " 37000(1933)[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot create index because the key column 'calcsun' is
non-deterministic or imprecise."
The page
http://msdn.microsoft.com/library/d...>
_08_95v7.asp
says that DATEDIFF is deterministic so I cannot see why the above index
will not create.
All help much appreciated.
GJHello, GJ
I was somehow surprised of this behaviour, too. It turns out that
DATEDIFF is indeed deterministic, but of it's parameters was not: 0 as
a datetime is non-deterministic! The page you quoted says that CONVERT
is deterministic with a datetime only when the style parameter is
specified (and it's not 0, 9, 100 or 109). Therefore, this works (and I
think that you will get the same results):
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,basedate,convert(datetime,'1
9000101',112))
);
create index calcdate_idx ON calcdate(calcsun);
Razvan
w
and want to store the W Number. Below is a test schema thatshould work but the index will not create.
drop table calcdate;
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,[basedate],0)
);
create index calcdate_idx ON calcdate(calcsun);
I get the error " 37000(1933)[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot create index because the key column 'calcsun' is
non-deterministic or imprecise."
The page
http://msdn.microsoft.com/library/d...>
_08_95v7.asp
says that DATEDIFF is deterministic so I cannot see why the above index
will not create.
All help much appreciated.
GJHello, GJ
I was somehow surprised of this behaviour, too. It turns out that
DATEDIFF is indeed deterministic, but of it's parameters was not: 0 as
a datetime is non-deterministic! The page you quoted says that CONVERT
is deterministic with a datetime only when the style parameter is
specified (and it's not 0, 9, 100 or 109). Therefore, this works (and I
think that you will get the same results):
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,basedate,convert(datetime,'1
9000101',112))
);
create index calcdate_idx ON calcdate(calcsun);
Razvan
Monday, March 12, 2012
Indexing datetime field for selecting dates ranges
Hi,
I have a table with a smalldatetime field. Some of the queries in my
application are using range searches over that smalldatetime field, such as
selecting all the records within a date range. None of these queries are
selecting records from a specific date/time. They all work on ranges (e.g.
using BETWEEN or operators like >=).
Is there any reason for indexing the smalldatetime field? Could that make
the queries run faster?
Regards,
Amir.Yes, indexes on those columns can be beneficial, just as indexes on any colu
mn. You need to make
sure that your query is written in a way so that those indexes can be used
(http://www.karaszi.com/SQLServer/info_datetime.asp), of course. And whether
the indexes then *will*
be used is dependent on a lot of factors (the query, the data, selectivity e
tc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amir" <agamy@.actcom.co.il> wrote in message news:OCbLcq9FGHA.1032@.TK2MSFTNGP15.phx.gbl...[
color=darkred]
> Hi,
> I have a table with a smalldatetime field. Some of the queries in my appli
cation are using range
> searches over that smalldatetime field, such as selecting all the records
within a date range.
> None of these queries are selecting records from a specific date/time. The
y all work on ranges
> (e.g. using BETWEEN or operators like >=).
> Is there any reason for indexing the smalldatetime field? Could that make
the queries run faster?
> Regards,
> Amir.
>[/color]|||Thanks for the explanation!
Kind Regards,
Amir.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOlH7mDGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Yes, indexes on those columns can be beneficial, just as indexes on any
> column. You need to make sure that your query is written in a way so that
> those indexes can be used
> (http://www.karaszi.com/SQLServer/info_datetime.asp), of course. And
> whether the indexes then *will* be used is dependent on a lot of factors
> (the query, the data, selectivity etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Amir" <agamy@.actcom.co.il> wrote in message
> news:OCbLcq9FGHA.1032@.TK2MSFTNGP15.phx.gbl...
>
I have a table with a smalldatetime field. Some of the queries in my
application are using range searches over that smalldatetime field, such as
selecting all the records within a date range. None of these queries are
selecting records from a specific date/time. They all work on ranges (e.g.
using BETWEEN or operators like >=).
Is there any reason for indexing the smalldatetime field? Could that make
the queries run faster?
Regards,
Amir.Yes, indexes on those columns can be beneficial, just as indexes on any colu
mn. You need to make
sure that your query is written in a way so that those indexes can be used
(http://www.karaszi.com/SQLServer/info_datetime.asp), of course. And whether
the indexes then *will*
be used is dependent on a lot of factors (the query, the data, selectivity e
tc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amir" <agamy@.actcom.co.il> wrote in message news:OCbLcq9FGHA.1032@.TK2MSFTNGP15.phx.gbl...[
color=darkred]
> Hi,
> I have a table with a smalldatetime field. Some of the queries in my appli
cation are using range
> searches over that smalldatetime field, such as selecting all the records
within a date range.
> None of these queries are selecting records from a specific date/time. The
y all work on ranges
> (e.g. using BETWEEN or operators like >=).
> Is there any reason for indexing the smalldatetime field? Could that make
the queries run faster?
> Regards,
> Amir.
>[/color]|||Thanks for the explanation!
Kind Regards,
Amir.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOlH7mDGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Yes, indexes on those columns can be beneficial, just as indexes on any
> column. You need to make sure that your query is written in a way so that
> those indexes can be used
> (http://www.karaszi.com/SQLServer/info_datetime.asp), of course. And
> whether the indexes then *will* be used is dependent on a lot of factors
> (the query, the data, selectivity etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Amir" <agamy@.actcom.co.il> wrote in message
> news:OCbLcq9FGHA.1032@.TK2MSFTNGP15.phx.gbl...
>
Indexing custom BLOB Field with SQL Server 2000!
Hi,
I want define a full text indexing in a BLOB field that store custom binary
file. As I know in such cases should write an IFilter for extracting plain
text from this binary file.
How is is possible with SQL Server 2000? Is there a guide about writing an
IFilter?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mamouri" <m@.m.com> wrote in message
news:eYphDbuHHHA.420@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want define a full text indexing in a BLOB field that store custom
> binary
> file. As I know in such cases should write an IFilter for extracting plain
> text from this binary file.
> How is is possible with SQL Server 2000? Is there a guide about writing an
> IFilter?
>
I want define a full text indexing in a BLOB field that store custom binary
file. As I know in such cases should write an IFilter for extracting plain
text from this binary file.
How is is possible with SQL Server 2000? Is there a guide about writing an
IFilter?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mamouri" <m@.m.com> wrote in message
news:eYphDbuHHHA.420@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want define a full text indexing in a BLOB field that store custom
> binary
> file. As I know in such cases should write an IFilter for extracting plain
> text from this binary file.
> How is is possible with SQL Server 2000? Is there a guide about writing an
> IFilter?
>
INDEXING A VIEW in MSSQL2K5x64.de Standard w.SP1 ... ERROR 1939
I'm experiencing a problem while trying to index a view ... I'm using a single field and everything else are default entry's. I'm getting the Error Message 1939 .
The code is a follows:
USE [CS_WGO]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX [IDX_ALG_ORG_BEZ_V_1] ON [dbo].[ALG_ORG_BEZ_V]
(
[NDL] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
And the Error is:
Msg 1939, Level 16, State 1, Line 1
Index kann für die 'ALG_ORG_BEZ_V'-Sicht nicht erstellt werden, da die Sicht nicht schemagebunden ist.
For those of you who do not read German ... it means something like ..." The Index kann not be created because the view is not bound to a schema.
I don't understand what that means for me ... can someone explain that to me a little bit more in depth ... ?
Thanx ... wvg
I m facing the same problem when tried with interface of sqlserver 2005 and the database was sql server 2000, how to get rid of it as i wish to decrease the exectuion time of the view. and so tried to index it.
|||can anyone please help me with this please?
thanks
|||can u post the schema (script) of the view. to create index on a view (indexed view) there are lot of rules to be followed. One of the requirement is to have the view Schema bound. ie the View script should have a SCHEMABINDING clause. Read about this in BOL Create View section.
Madhu
INDEXING A VIEW in MSSQL2K5x64.de Standard w.SP1 ... ERROR 1939
I'm experiencing a problem while trying to index a view ... I'm using a single field and everything else are default entry's. I'm getting the Error Message 1939 .
The code is a follows:
USE [CS_WGO]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX [IDX_ALG_ORG_BEZ_V_1] ON [dbo].[ALG_ORG_BEZ_V]
(
[NDL] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
And the Error is:
Msg 1939, Level 16, State 1, Line 1
Index kann für die 'ALG_ORG_BEZ_V'-Sicht nicht erstellt werden, da die Sicht nicht schemagebunden ist.
For those of you who do not read German ... it means something like ..." The Index kann not be created because the view is not bound to a schema.
I don't understand what that means for me ... can someone explain that to me a little bit more in depth ... ?
Thanx ... wvg
I m facing the same problem when tried with interface of sqlserver 2005 and the database was sql server 2000, how to get rid of it as i wish to decrease the exectuion time of the view. and so tried to index it.
|||can anyone please help me with this please?
thanks
|||can u post the schema (script) of the view. to create index on a view (indexed view) there are lot of rules to be followed. One of the requirement is to have the view Schema bound. ie the View script should have a SCHEMABINDING clause. Read about this in BOL Create View section.
Madhu
INDEXING A VIEW in MSSQL2K5x64.de Standard w.SP1 ... ERROR 1939
I'm experiencing a problem while trying to index a view ... I'm using a single field and everything else are default entry's. I'm getting the Error Message 1939 .
The code is a follows:
USE [CS_WGO]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX [IDX_ALG_ORG_BEZ_V_1] ON [dbo].[ALG_ORG_BEZ_V]
(
[NDL] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO
And the Error is:
Msg 1939, Level 16, State 1, Line 1
Index kann für die 'ALG_ORG_BEZ_V'-Sicht nicht erstellt werden, da die Sicht nicht schemagebunden ist.
For those of you who do not read German ... it means something like ..." The Index kann not be created because the view is not bound to a schema.
I don't understand what that means for me ... can someone explain that to me a little bit more in depth ... ?
Thanx ... wvg
I m facing the same problem when tried with interface of sqlserver 2005 and the database was sql server 2000, how to get rid of it as i wish to decrease the exectuion time of the view. and so tried to index it.
|||can anyone please help me with this please?
thanks
|||can u post the schema (script) of the view. to create index on a view (indexed view) there are lot of rules to be followed. One of the requirement is to have the view Schema bound. ie the View script should have a SCHEMABINDING clause. Read about this in BOL Create View section.
Madhu
Indexing - What is fastest? Numeric or Alpha fields?
If I create an index on a field in SQL Server, what will be the most efficient (fastest) field type to index a field? (This field will be a "Pointer" to a child table that will contain a list of codes, and their description.)
Would a Numeric field be quicker than a VarChar field?
VarChar would make it easier for a Human to decipher the raw records. (For example, if I used a numeric the code would be 42 or 47, while the VarChar could be'savings' or'checking'.)
Basically I will have the following "Master" table:
FieldType---IDIntNameVarCharStatusInt -or- VarCharCustomer_TypeInt -or- VarChar
If Customer_Type is a code that can be looked up in another table, and I index that field, would I want the "Code" to be an Int or VarChar?
SQL:
Select *From MasterWhere Customer_Type = <42> or <'savings'>
MyWhereclause would depend on the field type.
Thank you,
Bryan
Friday, March 9, 2012
indexes question
Assume we have a sales table, which contains a OrderID(char(8)) and a OrderDate(smalldatetime) field. Each day, hundreds of thousands of records needs to be inserted into this table. We need a daily sales report, and the OrderID is not necessarilly sequentially entered (every data operator has a range of OrderID, so for each data operator, it is sequential, but globally, it is not).
So, I would like to create a clustered index on OrderDate, and the Primary Key is on OrderID. I think it is good for generating the daily sales report. However, when an order is entered, we need to quickly check if the OrderID already exits, because the unique index on OrderID is built on the clustered index (on OrderDate), which means the Database Engine will search all the records on that day. This could be a slow process.
Is it possible to create indexes so that I can generate daily report and search for the OrderID quickly at the same time?
Thanks.
Create the clustered index on the orderDate and a second, unique non clustered index on orderId. When you check that the order id already exists, if you do a "if exists (select orderId from sales where orderId = @.orderId)", then this will not need to do a clustered index lookup as the index is covered i.e. : there is no need to go back to the base table, as the only column you are asking for (orderId) is already in the non clustered index.
The daily sales report would continue to work with the clustered index on the orderDate.
Hope this helps.
Friday, February 24, 2012
Indexes & "OR"
Hi,
i have 2 tables:
table 1 - called Feed. contains the following fields:
productName
PartNumber
ManufacturerName
PartNumberManufacturerName (this field concatenates the part number field
and the manufacturer name field).
Table 2 - called Products. contains the following fields:
productID
ProductName
PartNumber1
PartNumber2
PartNumber3
ManufacturerName
Mapkey1 - (this field concatenates the PartNumber1 field and the
manufacturername field)
Mapkey2 - (this field concatenates the PartNumber2 field and the
manufacturername field)
Mapkey3 - (this field concatenates the PartNumber3 field and the
manufacturername field)
I have a query that finds the productID for the records in the feed table.
query is as follows
select a.productName, a.partnumber,
from feed a (NOLOCK),
products b (NOLOCK)
where a.partnumberManufacturerName = b.mapKey1 OR
a.partnumberManufacturerName = b.mapKey2 OR
a.partnumberManufacturerName = b.mapKey3
Indexes:
on the feed table, i have an index on the partnumberManufacturerName and on
the products table, i have multiple indexes:
Mapkey1 ,Mapkey2, and Mapkey3
and then on each individual field
Mapkey1
Mapkey2
Mapkey3
My question is which index should i keep and which index should i drop. i'm
not sure which index the query is going to use since i'm using the "OR".
thanks
rafaelwhy the concatentated columns? they don't help performance [and may
hinder it]
why the multiple part numbers in separate columns - is it possible to
have more than 3?
do you have a manufacturers table as well?
i'd seriously consider changing this un-normalized schema to have a
linking table between partnumbers and productIDs (and normalize to have
a manufacturers table)
how does the Feed table get populated?
if you fix the schema, with proper primary and foreign keys, you
probably won't need extra indexes [at least for this query].
Rafael Chemtob wrote:
> Hi,
> i have 2 tables:
> table 1 - called Feed. contains the following fields:
> productName
> PartNumber
> ManufacturerName
> PartNumberManufacturerName (this field concatenates the part number field
> and the manufacturer name field).
> Table 2 - called Products. contains the following fields:
> productID
> ProductName
> PartNumber1
> PartNumber2
> PartNumber3
> ManufacturerName
> Mapkey1 - (this field concatenates the PartNumber1 field and the
> manufacturername field)
> Mapkey2 - (this field concatenates the PartNumber2 field and the
> manufacturername field)
> Mapkey3 - (this field concatenates the PartNumber3 field and the
> manufacturername field)
> I have a query that finds the productID for the records in the feed table.
> query is as follows
> select a.productName, a.partnumber,
> from feed a (NOLOCK),
> products b (NOLOCK)
> where a.partnumberManufacturerName = b.mapKey1 OR
> a.partnumberManufacturerName = b.mapKey2 OR
> a.partnumberManufacturerName = b.mapKey3
> Indexes:
> on the feed table, i have an index on the partnumberManufacturerName and o
n
> the products table, i have multiple indexes:
> Mapkey1 ,Mapkey2, and Mapkey3
> and then on each individual field
> Mapkey1
> Mapkey2
> Mapkey3
> My question is which index should i keep and which index should i drop. i
'm
> not sure which index the query is going to use since i'm using the "OR".
> thanks
> rafael
>
>
i have 2 tables:
table 1 - called Feed. contains the following fields:
productName
PartNumber
ManufacturerName
PartNumberManufacturerName (this field concatenates the part number field
and the manufacturer name field).
Table 2 - called Products. contains the following fields:
productID
ProductName
PartNumber1
PartNumber2
PartNumber3
ManufacturerName
Mapkey1 - (this field concatenates the PartNumber1 field and the
manufacturername field)
Mapkey2 - (this field concatenates the PartNumber2 field and the
manufacturername field)
Mapkey3 - (this field concatenates the PartNumber3 field and the
manufacturername field)
I have a query that finds the productID for the records in the feed table.
query is as follows
select a.productName, a.partnumber,
from feed a (NOLOCK),
products b (NOLOCK)
where a.partnumberManufacturerName = b.mapKey1 OR
a.partnumberManufacturerName = b.mapKey2 OR
a.partnumberManufacturerName = b.mapKey3
Indexes:
on the feed table, i have an index on the partnumberManufacturerName and on
the products table, i have multiple indexes:
Mapkey1 ,Mapkey2, and Mapkey3
and then on each individual field
Mapkey1
Mapkey2
Mapkey3
My question is which index should i keep and which index should i drop. i'm
not sure which index the query is going to use since i'm using the "OR".
thanks
rafaelwhy the concatentated columns? they don't help performance [and may
hinder it]
why the multiple part numbers in separate columns - is it possible to
have more than 3?
do you have a manufacturers table as well?
i'd seriously consider changing this un-normalized schema to have a
linking table between partnumbers and productIDs (and normalize to have
a manufacturers table)
how does the Feed table get populated?
if you fix the schema, with proper primary and foreign keys, you
probably won't need extra indexes [at least for this query].
Rafael Chemtob wrote:
> Hi,
> i have 2 tables:
> table 1 - called Feed. contains the following fields:
> productName
> PartNumber
> ManufacturerName
> PartNumberManufacturerName (this field concatenates the part number field
> and the manufacturer name field).
> Table 2 - called Products. contains the following fields:
> productID
> ProductName
> PartNumber1
> PartNumber2
> PartNumber3
> ManufacturerName
> Mapkey1 - (this field concatenates the PartNumber1 field and the
> manufacturername field)
> Mapkey2 - (this field concatenates the PartNumber2 field and the
> manufacturername field)
> Mapkey3 - (this field concatenates the PartNumber3 field and the
> manufacturername field)
> I have a query that finds the productID for the records in the feed table.
> query is as follows
> select a.productName, a.partnumber,
> from feed a (NOLOCK),
> products b (NOLOCK)
> where a.partnumberManufacturerName = b.mapKey1 OR
> a.partnumberManufacturerName = b.mapKey2 OR
> a.partnumberManufacturerName = b.mapKey3
> Indexes:
> on the feed table, i have an index on the partnumberManufacturerName and o
n
> the products table, i have multiple indexes:
> Mapkey1 ,Mapkey2, and Mapkey3
> and then on each individual field
> Mapkey1
> Mapkey2
> Mapkey3
> My question is which index should i keep and which index should i drop. i
'm
> not sure which index the query is going to use since i'm using the "OR".
> thanks
> rafael
>
>
Indexes
I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to crea
te a
> non-clustered index on acct_key.
... unless you do it to cover queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is n
o reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered ind
ex.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I no
w see was the case,
re-reading the OP). I was thrown off a bit by this:
I have difficulties understanding what "extract single values from this fiel
d" means. My thinking
was that the NC index could cover queries where the restriction is for some
other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to crea
te a
> non-clustered index on acct_key.
... unless you do it to cover queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is n
o reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered ind
ex.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I no
w see was the case,
re-reading the OP). I was thrown off a bit by this:
I have difficulties understanding what "extract single values from this fiel
d" means. My thinking
was that the NC index could cover queries where the restriction is for some
other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>
Indexes
I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
... unless you do it to cover queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered index.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>> I have cluster index created on a composite primary key (acct_key,period).
>> I would like to create a non-cluster index on the acct_key field, since
>> there are numerous sql statements that extract single value from this field.
>> Please let me know if cluster index has a composite primary key neither
>> field should be in a non-cluster index?
>>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is
>> no reason to create a non-clustered index on acct_key.
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is
>> no reason to create a non-clustered index on acct_key.
>> ... unless you do it to cover queries.
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I now see was the case,
re-reading the OP). I was thrown off a bit by this:
>> I would like to create a non-cluster index on the acct_key field, since
>> there are numerous sql statements that extract single value from this field.
I have difficulties understanding what "extract single values from this field" means. My thinking
was that the NC index could cover queries where the restriction is for some other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
>> non-clustered index on acct_key.
>> ... unless you do it to cover queries.
>> You'd have to have a pretty wide table for that to make a difference -- the clustered index
>> already covers every possible query...
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
... unless you do it to cover queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered index.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>> I have cluster index created on a composite primary key (acct_key,period).
>> I would like to create a non-cluster index on the acct_key field, since
>> there are numerous sql statements that extract single value from this field.
>> Please let me know if cluster index has a composite primary key neither
>> field should be in a non-cluster index?
>>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is
>> no reason to create a non-clustered index on acct_key.
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is
>> no reason to create a non-clustered index on acct_key.
>> ... unless you do it to cover queries.
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I now see was the case,
re-reading the OP). I was thrown off a bit by this:
>> I would like to create a non-cluster index on the acct_key field, since
>> there are numerous sql statements that extract single value from this field.
I have difficulties understanding what "extract single values from this field" means. My thinking
was that the NC index could cover queries where the restriction is for some other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
>> non-clustered index on acct_key.
>> ... unless you do it to cover queries.
>> You'd have to have a pretty wide table for that to make a difference -- the clustered index
>> already covers every possible query...
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>
Indexes
Say you have a table with 10 fields, 50,000-100,000 records, and 1 primary key field.
Is there any performance difference between creating a "covered" index and versus creating 9 individual indexes (not 10 b/c i'm assuming the PK field will already have an index created for it), one for each non key field.What is a "covered" index?|||A covered Index is an index which includes many columns in it. For example if you have an application which can search on 5 out of 10 fields you could create an index on those 5 fields and the result is supposed to be quicker searches on those 5 fields.|||Then I guess it might depend on how often rows are inserted, and how often these columns are updated, since that is when index rows would be inserted or updated.
It sounded like you were suggesting putting all the columns of the table in one index -- this would not accomplish anything, would it?
I see what you are saying about combining some columns in an index, particularly if your search would be filtering on more than one of the columns.
It's difficult to make a suggestion without more info on activity, column size, and filtering methods.|||Assume that you'll be reading most of the time. Given that is there a difference between the two methods.. If any?|||Do we speak about read or write operations here?
If we speak read, then it's all coming about what queries are going to be used.
If there's gonna be a SELECT statement that will be utilizing a scope that will use a WHERE with 5 fields, the optimum is to have an index that will cover those 5 fields instead of having those 9 seperate indexes.
As for the write operation I am not that sure that there'll be any differences anyways.
I think it all comes to the query optimizer really and the execution plan.|||I've always thought you should cover the columns in a popular where clause.
In a few cases where the data is inserted or updated more than read, then fewer indexes is better.|||I think it depends on your query.
Also, the index tuning wizard may helpful in your case.
Is there any performance difference between creating a "covered" index and versus creating 9 individual indexes (not 10 b/c i'm assuming the PK field will already have an index created for it), one for each non key field.What is a "covered" index?|||A covered Index is an index which includes many columns in it. For example if you have an application which can search on 5 out of 10 fields you could create an index on those 5 fields and the result is supposed to be quicker searches on those 5 fields.|||Then I guess it might depend on how often rows are inserted, and how often these columns are updated, since that is when index rows would be inserted or updated.
It sounded like you were suggesting putting all the columns of the table in one index -- this would not accomplish anything, would it?
I see what you are saying about combining some columns in an index, particularly if your search would be filtering on more than one of the columns.
It's difficult to make a suggestion without more info on activity, column size, and filtering methods.|||Assume that you'll be reading most of the time. Given that is there a difference between the two methods.. If any?|||Do we speak about read or write operations here?
If we speak read, then it's all coming about what queries are going to be used.
If there's gonna be a SELECT statement that will be utilizing a scope that will use a WHERE with 5 fields, the optimum is to have an index that will cover those 5 fields instead of having those 9 seperate indexes.
As for the write operation I am not that sure that there'll be any differences anyways.
I think it all comes to the query optimizer really and the execution plan.|||I've always thought you should cover the columns in a popular where clause.
In a few cases where the data is inserted or updated more than read, then fewer indexes is better.|||I think it depends on your query.
Also, the index tuning wizard may helpful in your case.
Sunday, February 19, 2012
Indexes
I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?
A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>
|||> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
... unless you do it to cover queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered index.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
|||Hmm, yes, assuming this is the only column to be in the nc index (which I now see was the case,
re-reading the OP). I was thrown off a bit by this:
[vbcol=seagreen]
I have difficulties understanding what "extract single values from this field" means. My thinking
was that the NC index could cover queries where the restriction is for some other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?
A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>
|||> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
... unless you do it to cover queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is no reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered index.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
|||Hmm, yes, assuming this is the only column to be in the nc index (which I now see was the case,
re-reading the OP). I was thrown off a bit by this:
[vbcol=seagreen]
I have difficulties understanding what "extract single values from this field" means. My thinking
was that the NC index could cover queries where the restriction is for some other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>
indexed views / performance / encryption
I think this is an interesting question for anyone who
encrypts data in their db, but wants the performance of
an unencrypted field. This question was posted yesterday
in microsoft.public.sqlserver.security with no response.
I have a table that contains an encrypted field, I will
call the table encryptTable.
The only way that a user can view the data is by using a
view. For example
select col001 from encryptTable --returns
#&$@.!?`#*&$#
select col001 from encryptTableView --returns
mysqql_sucks
Because the field is encrypted, this query takes a lot of
time.
select * from encryptTableView where col001
= 'mysqql_sucks'
This takes a lot of time because every row in the million
row table has to be decrypted, to be compared to the
where clause.
If I were to create an indexed view then col001 would be
stored in a clear text format
and allow queries like:
select * from encryptTableView where col001
= 'mysqql_sucks'
to run in record time.
The question is...
Is is possible to access (read) the data in the indexed
view? Since the data is clear text, that would not be
good.
Thanks for you help
Bob
www.cake&eat2.comThanks for you reply, perhaps I was not clear enough. At
this point, I am not concerned if the query optimizer
chooses to use the index or not. I am concerned that the
clear text data in the index is readable. For example,
if select * from myindex returned the data stored in the
index, this would be bad. Is there any way to view the
data inside an index?
>--Original Message--
>I think yes, by using a query hint and specifying the
index.
>Yovan
>"Bob" <bob@.nospam.com> wrote in message
>news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
>> I think this is an interesting question for anyone who
>> encrypts data in their db, but wants the performance of
>> an unencrypted field. This question was posted
yesterday
>> in microsoft.public.sqlserver.security with no
response.
>> I have a table that contains an encrypted field, I will
>> call the table encryptTable.
>> The only way that a user can view the data is by using
a
>> view. For example
>> select col001 from encryptTable --returns
>> #&$@.!?`#*&$#
>> select col001 from encryptTableView --returns
>> mysqql_sucks
>> Because the field is encrypted, this query takes a lot
of
>> time.
>> select * from encryptTableView where col001
>> = 'mysqql_sucks'
>> This takes a lot of time because every row in the
million
>> row table has to be decrypted, to be compared to the
>> where clause.
>> If I were to create an indexed view then col001 would
be
>> stored in a clear text format
>> and allow queries like:
>> select * from encryptTableView where col001
>> = 'mysqql_sucks'
>> to run in record time.
>> The question is...
>> Is is possible to access (read) the data in the indexed
>> view? Since the data is clear text, that would not be
>> good.
>>
>> Thanks for you help
>> Bob
>> www.cake&eat2.com
>
>.
>|||To my knowlegde there is not, but i am sure there is way at the data level.
Yovan
"bob" <bob@.nospam.com> wrote in message
news:03a601c37ed3$5bdb8c20$a301280a@.phx.gbl...
> Thanks for you reply, perhaps I was not clear enough. At
> this point, I am not concerned if the query optimizer
> chooses to use the index or not. I am concerned that the
> clear text data in the index is readable. For example,
> if select * from myindex returned the data stored in the
> index, this would be bad. Is there any way to view the
> data inside an index?
> >--Original Message--
> >I think yes, by using a query hint and specifying the
> index.
> >
> >Yovan
> >"Bob" <bob@.nospam.com> wrote in message
> >news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
> >> I think this is an interesting question for anyone who
> >> encrypts data in their db, but wants the performance of
> >> an unencrypted field. This question was posted
> yesterday
> >> in microsoft.public.sqlserver.security with no
> response.
> >>
> >> I have a table that contains an encrypted field, I will
> >> call the table encryptTable.
> >> The only way that a user can view the data is by using
> a
> >> view. For example
> >>
> >> select col001 from encryptTable --returns
> >> #&$@.!?`#*&$#
> >>
> >> select col001 from encryptTableView --returns
> >> mysqql_sucks
> >>
> >> Because the field is encrypted, this query takes a lot
> of
> >> time.
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >>
> >> This takes a lot of time because every row in the
> million
> >> row table has to be decrypted, to be compared to the
> >> where clause.
> >>
> >> If I were to create an indexed view then col001 would
> be
> >> stored in a clear text format
> >> and allow queries like:
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >> to run in record time.
> >>
> >> The question is...
> >> Is is possible to access (read) the data in the indexed
> >> view? Since the data is clear text, that would not be
> >> good.
> >>
> >>
> >> Thanks for you help
> >> Bob
> >> www.cake&eat2.com
> >>
> >
> >
> >.
> >|||I think your confusing what an Indexed view is. You don't read from an
Index you read from the table but sql server may choose to use an index if
available to determine what rows to retrieve. You don't have the option of
selecting from an Index itself. In the case of an Indexed view you are
essentially creating a new table with the contents of the clustered index
expression of the view. So the answer is essentially Yes. If the user has
read permissions on that indexed view then they can indeed read the values
of that column. Do you really need to encrypt the columns you use to search
on?
In any case you might want to read up in BOL under "Indexes - Creating an
Indexed view" for more details.
--
Andrew J. Kelly
SQL Server MVP
"bob" <bob@.nospam.com> wrote in message
news:03a601c37ed3$5bdb8c20$a301280a@.phx.gbl...
> Thanks for you reply, perhaps I was not clear enough. At
> this point, I am not concerned if the query optimizer
> chooses to use the index or not. I am concerned that the
> clear text data in the index is readable. For example,
> if select * from myindex returned the data stored in the
> index, this would be bad. Is there any way to view the
> data inside an index?
> >--Original Message--
> >I think yes, by using a query hint and specifying the
> index.
> >
> >Yovan
> >"Bob" <bob@.nospam.com> wrote in message
> >news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
> >> I think this is an interesting question for anyone who
> >> encrypts data in their db, but wants the performance of
> >> an unencrypted field. This question was posted
> yesterday
> >> in microsoft.public.sqlserver.security with no
> response.
> >>
> >> I have a table that contains an encrypted field, I will
> >> call the table encryptTable.
> >> The only way that a user can view the data is by using
> a
> >> view. For example
> >>
> >> select col001 from encryptTable --returns
> >> #&$@.!?`#*&$#
> >>
> >> select col001 from encryptTableView --returns
> >> mysqql_sucks
> >>
> >> Because the field is encrypted, this query takes a lot
> of
> >> time.
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >>
> >> This takes a lot of time because every row in the
> million
> >> row table has to be decrypted, to be compared to the
> >> where clause.
> >>
> >> If I were to create an indexed view then col001 would
> be
> >> stored in a clear text format
> >> and allow queries like:
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >> to run in record time.
> >>
> >> The question is...
> >> Is is possible to access (read) the data in the indexed
> >> view? Since the data is clear text, that would not be
> >> good.
> >>
> >>
> >> Thanks for you help
> >> Bob
> >> www.cake&eat2.com
> >>
> >
> >
> >.
> >|||Bob,
I'm not sure what tool you are using for the encryption, but here is another
option. If you are using ECB cipher mode your cipher text will always be the
same. So when you want to search for a value just encypt the value and use the
cipher text for that value as the search criteria.
To the best of my knowledge this only works with ECB. The downside is that ECB
is not terribly safe compared to other cipher modes. You may want to first
encrypt using CBC, OFB, CFB and then encrypt the data again using ECB.
Just a suggestion
Tom O
"Bob" :
> I think this is an interesting question for anyone who
> encrypts data in their db, but wants the performance of
> an unencrypted field. This question was posted yesterday
> in microsoft.public.sqlserver.security with no response.
> I have a table that contains an encrypted field, I will
> call the table encryptTable.
> The only way that a user can view the data is by using a
> view. For example
> select col001 from encryptTable --returns
> #&$@.!?`#*&$#
> select col001 from encryptTableView --returns
> mysqql_sucks
> Because the field is encrypted, this query takes a lot of
> time.
> select * from encryptTableView where col001
> = 'mysqql_sucks'
> This takes a lot of time because every row in the million
> row table has to be decrypted, to be compared to the
> where clause.
> If I were to create an indexed view then col001 would be
> stored in a clear text format
> and allow queries like:
> select * from encryptTableView where col001
> = 'mysqql_sucks'
> to run in record time.
> The question is...
> Is is possible to access (read) the data in the indexed
> view? Since the data is clear text, that would not be
> good.
>
> Thanks for you help
> Bob
> www.cake&eat2.com
>
encrypts data in their db, but wants the performance of
an unencrypted field. This question was posted yesterday
in microsoft.public.sqlserver.security with no response.
I have a table that contains an encrypted field, I will
call the table encryptTable.
The only way that a user can view the data is by using a
view. For example
select col001 from encryptTable --returns
#&$@.!?`#*&$#
select col001 from encryptTableView --returns
mysqql_sucks
Because the field is encrypted, this query takes a lot of
time.
select * from encryptTableView where col001
= 'mysqql_sucks'
This takes a lot of time because every row in the million
row table has to be decrypted, to be compared to the
where clause.
If I were to create an indexed view then col001 would be
stored in a clear text format
and allow queries like:
select * from encryptTableView where col001
= 'mysqql_sucks'
to run in record time.
The question is...
Is is possible to access (read) the data in the indexed
view? Since the data is clear text, that would not be
good.
Thanks for you help
Bob
www.cake&eat2.comThanks for you reply, perhaps I was not clear enough. At
this point, I am not concerned if the query optimizer
chooses to use the index or not. I am concerned that the
clear text data in the index is readable. For example,
if select * from myindex returned the data stored in the
index, this would be bad. Is there any way to view the
data inside an index?
>--Original Message--
>I think yes, by using a query hint and specifying the
index.
>Yovan
>"Bob" <bob@.nospam.com> wrote in message
>news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
>> I think this is an interesting question for anyone who
>> encrypts data in their db, but wants the performance of
>> an unencrypted field. This question was posted
yesterday
>> in microsoft.public.sqlserver.security with no
response.
>> I have a table that contains an encrypted field, I will
>> call the table encryptTable.
>> The only way that a user can view the data is by using
a
>> view. For example
>> select col001 from encryptTable --returns
>> #&$@.!?`#*&$#
>> select col001 from encryptTableView --returns
>> mysqql_sucks
>> Because the field is encrypted, this query takes a lot
of
>> time.
>> select * from encryptTableView where col001
>> = 'mysqql_sucks'
>> This takes a lot of time because every row in the
million
>> row table has to be decrypted, to be compared to the
>> where clause.
>> If I were to create an indexed view then col001 would
be
>> stored in a clear text format
>> and allow queries like:
>> select * from encryptTableView where col001
>> = 'mysqql_sucks'
>> to run in record time.
>> The question is...
>> Is is possible to access (read) the data in the indexed
>> view? Since the data is clear text, that would not be
>> good.
>>
>> Thanks for you help
>> Bob
>> www.cake&eat2.com
>
>.
>|||To my knowlegde there is not, but i am sure there is way at the data level.
Yovan
"bob" <bob@.nospam.com> wrote in message
news:03a601c37ed3$5bdb8c20$a301280a@.phx.gbl...
> Thanks for you reply, perhaps I was not clear enough. At
> this point, I am not concerned if the query optimizer
> chooses to use the index or not. I am concerned that the
> clear text data in the index is readable. For example,
> if select * from myindex returned the data stored in the
> index, this would be bad. Is there any way to view the
> data inside an index?
> >--Original Message--
> >I think yes, by using a query hint and specifying the
> index.
> >
> >Yovan
> >"Bob" <bob@.nospam.com> wrote in message
> >news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
> >> I think this is an interesting question for anyone who
> >> encrypts data in their db, but wants the performance of
> >> an unencrypted field. This question was posted
> yesterday
> >> in microsoft.public.sqlserver.security with no
> response.
> >>
> >> I have a table that contains an encrypted field, I will
> >> call the table encryptTable.
> >> The only way that a user can view the data is by using
> a
> >> view. For example
> >>
> >> select col001 from encryptTable --returns
> >> #&$@.!?`#*&$#
> >>
> >> select col001 from encryptTableView --returns
> >> mysqql_sucks
> >>
> >> Because the field is encrypted, this query takes a lot
> of
> >> time.
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >>
> >> This takes a lot of time because every row in the
> million
> >> row table has to be decrypted, to be compared to the
> >> where clause.
> >>
> >> If I were to create an indexed view then col001 would
> be
> >> stored in a clear text format
> >> and allow queries like:
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >> to run in record time.
> >>
> >> The question is...
> >> Is is possible to access (read) the data in the indexed
> >> view? Since the data is clear text, that would not be
> >> good.
> >>
> >>
> >> Thanks for you help
> >> Bob
> >> www.cake&eat2.com
> >>
> >
> >
> >.
> >|||I think your confusing what an Indexed view is. You don't read from an
Index you read from the table but sql server may choose to use an index if
available to determine what rows to retrieve. You don't have the option of
selecting from an Index itself. In the case of an Indexed view you are
essentially creating a new table with the contents of the clustered index
expression of the view. So the answer is essentially Yes. If the user has
read permissions on that indexed view then they can indeed read the values
of that column. Do you really need to encrypt the columns you use to search
on?
In any case you might want to read up in BOL under "Indexes - Creating an
Indexed view" for more details.
--
Andrew J. Kelly
SQL Server MVP
"bob" <bob@.nospam.com> wrote in message
news:03a601c37ed3$5bdb8c20$a301280a@.phx.gbl...
> Thanks for you reply, perhaps I was not clear enough. At
> this point, I am not concerned if the query optimizer
> chooses to use the index or not. I am concerned that the
> clear text data in the index is readable. For example,
> if select * from myindex returned the data stored in the
> index, this would be bad. Is there any way to view the
> data inside an index?
> >--Original Message--
> >I think yes, by using a query hint and specifying the
> index.
> >
> >Yovan
> >"Bob" <bob@.nospam.com> wrote in message
> >news:026901c37ecb$9fdb0de0$a301280a@.phx.gbl...
> >> I think this is an interesting question for anyone who
> >> encrypts data in their db, but wants the performance of
> >> an unencrypted field. This question was posted
> yesterday
> >> in microsoft.public.sqlserver.security with no
> response.
> >>
> >> I have a table that contains an encrypted field, I will
> >> call the table encryptTable.
> >> The only way that a user can view the data is by using
> a
> >> view. For example
> >>
> >> select col001 from encryptTable --returns
> >> #&$@.!?`#*&$#
> >>
> >> select col001 from encryptTableView --returns
> >> mysqql_sucks
> >>
> >> Because the field is encrypted, this query takes a lot
> of
> >> time.
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >>
> >> This takes a lot of time because every row in the
> million
> >> row table has to be decrypted, to be compared to the
> >> where clause.
> >>
> >> If I were to create an indexed view then col001 would
> be
> >> stored in a clear text format
> >> and allow queries like:
> >> select * from encryptTableView where col001
> >> = 'mysqql_sucks'
> >> to run in record time.
> >>
> >> The question is...
> >> Is is possible to access (read) the data in the indexed
> >> view? Since the data is clear text, that would not be
> >> good.
> >>
> >>
> >> Thanks for you help
> >> Bob
> >> www.cake&eat2.com
> >>
> >
> >
> >.
> >|||Bob,
I'm not sure what tool you are using for the encryption, but here is another
option. If you are using ECB cipher mode your cipher text will always be the
same. So when you want to search for a value just encypt the value and use the
cipher text for that value as the search criteria.
To the best of my knowledge this only works with ECB. The downside is that ECB
is not terribly safe compared to other cipher modes. You may want to first
encrypt using CBC, OFB, CFB and then encrypt the data again using ECB.
Just a suggestion
Tom O
"Bob" :
> I think this is an interesting question for anyone who
> encrypts data in their db, but wants the performance of
> an unencrypted field. This question was posted yesterday
> in microsoft.public.sqlserver.security with no response.
> I have a table that contains an encrypted field, I will
> call the table encryptTable.
> The only way that a user can view the data is by using a
> view. For example
> select col001 from encryptTable --returns
> #&$@.!?`#*&$#
> select col001 from encryptTableView --returns
> mysqql_sucks
> Because the field is encrypted, this query takes a lot of
> time.
> select * from encryptTableView where col001
> = 'mysqql_sucks'
> This takes a lot of time because every row in the million
> row table has to be decrypted, to be compared to the
> where clause.
> If I were to create an indexed view then col001 would be
> stored in a clear text format
> and allow queries like:
> select * from encryptTableView where col001
> = 'mysqql_sucks'
> to run in record time.
> The question is...
> Is is possible to access (read) the data in the indexed
> view? Since the data is clear text, that would not be
> good.
>
> Thanks for you help
> Bob
> www.cake&eat2.com
>
Subscribe to:
Posts (Atom)