Recently we had a development server on WIN2K running SQL 2K. We noticed that the Information_Schema views disappeared. We have no idea how. It appeared to be about the time most recent patches were run. However, patches were applied to many servers, and the others all have Information_Schema
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and reinstalled SQL2K. All patches were installed on both the operating system and sql
Information_Schema was present
We have not reinstalled
We have not rebooted
Information_Schema is now not present
Last week's queries against it now fail
Any ideas?It appears that the views are still in Master, but have owner dbo
They can only be accessed in Master, and don't have data for other db's
What would cause this
What can be done to correct
Thanks!|||Any ideas on this
It is truly vexing, and we'd appreciate some input here!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.
Showing posts with label idea. Show all posts
Showing posts with label idea. Show all posts
Friday, March 30, 2012
Information_Schema disappears?
Recently we had a development server on WIN2K running SQL 2K. We noticed t
hat the Information_Schema views disappeared. We have no idea how. It appea
red to be about the time most recent patches were run. However, patches were
applied to many servers, a
nd the others all have Information_Schema.
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and
reinstalled SQL2K. All patches were installed on both the operating system
and sql.
Information_Schema was present.
We have not reinstalled.
We have not rebooted.
Information_Schema is now not present.
Last week's queries against it now fail.
Any ideas?It appears that the views are still in Master, but have owner dbo.
They can only be accessed in Master, and don't have data for other db's.
What would cause this?
What can be done to correct?
Thanks!|||Any ideas on this?
It is truly vexing, and we'd appreciate some input here!!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.
hat the Information_Schema views disappeared. We have no idea how. It appea
red to be about the time most recent patches were run. However, patches were
applied to many servers, a
nd the others all have Information_Schema.
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and
reinstalled SQL2K. All patches were installed on both the operating system
and sql.
Information_Schema was present.
We have not reinstalled.
We have not rebooted.
Information_Schema is now not present.
Last week's queries against it now fail.
Any ideas?It appears that the views are still in Master, but have owner dbo.
They can only be accessed in Master, and don't have data for other db's.
What would cause this?
What can be done to correct?
Thanks!|||Any ideas on this?
It is truly vexing, and we'd appreciate some input here!!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.
Monday, March 12, 2012
Indexing a TEXT Column
Hey
I want to index a TEXT column to accelerate my searchfunction,
does anybody have experience or good idea's ??
Greetz Da WitteHi,
Try index tuning wizard..
It will tell you if you should create an index and helps you do that.
Create a workload file and run it thru index tuning wizard..
Good luck
Tommy|||Text and image columns cannot be indexed. Use a long VARCHAR datatype instead.
blindman|||Good point Blindman !! :-)|||Originally posted by blindman
Text and image columns cannot be indexed. Use a long VARCHAR datatype instead.
blindman
Problem... I want to store >1000000 characters and also want to search in those records !!|||Look up Full-Text Indexes in Books Online. I think it will do what you are looking for.
blindman
I want to index a TEXT column to accelerate my searchfunction,
does anybody have experience or good idea's ??
Greetz Da WitteHi,
Try index tuning wizard..
It will tell you if you should create an index and helps you do that.
Create a workload file and run it thru index tuning wizard..
Good luck
Tommy|||Text and image columns cannot be indexed. Use a long VARCHAR datatype instead.
blindman|||Good point Blindman !! :-)|||Originally posted by blindman
Text and image columns cannot be indexed. Use a long VARCHAR datatype instead.
blindman
Problem... I want to store >1000000 characters and also want to search in those records !!|||Look up Full-Text Indexes in Books Online. I think it will do what you are looking for.
blindman
Sunday, February 19, 2012
Indexed Views & Constraints
Is using indexed views to create constraints a good idea? If not, what
database design flaws usually lead to this sort of construct?Can you give an example of how you would use an indexed view to create a
constraint? I've heard of indexed views, but never heard of using an
indexed view to create constraints.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||For a constraint on a view to be meaningful, the view would have to be
updatable. The restrictions on updatable views are that
-the columns in the view must be directly related to the columns in the base
tables (no aggregate functions)
-any update to the view must affect one one of the base tables involved at a
time.
So, if you wanted to create a primary key constraint using two columns in a
view that orginated it two different base tables, it would not act as a
constraint per se. Any insert or update you tried to perform on the view tha
t
modified both columns simultaneously would fail because it would violate the
second rule above. In other word, you would have the constaint, but you
wouldn't be able to use it in the way that you would like.
The index (as a result of the primary key constraint) would help with
SELECTs though.
A unique clustered index on a view also might not provide the constraint
enforcement you are looking for. Consider the following:
create table testa (aid int, aval char(1), PRIMARY KEY (aid))
go
create table testb (bid int, bval char(1), PRIMARY KEY (bid))
go
INSERT INTO testa VALUES (1, 'M')
INSERT INTO testa VALUES (2, 'A')
INSERT INTO testa VALUES (3, 'R')
INSERT INTO testa VALUES (4, 'K')
INSERT INTO testa VALUES (5, 'O')
INSERT INTO testb VALUES (1, 'W')
INSERT INTO testb VALUES (2, 'I')
INSERT INTO testb VALUES (3, 'L')
INSERT INTO testb VALUES (4, 'L')
INSERT INTO testb VALUES (5, 'Y')
CREATE VIEW dbo.testc WITH SCHEMABINDING AS
(SELECT a.aid, a.aval, b.bval from dbo.testa a inner join dbo.testb b on
a.aid=b.bid)
CREATE UNIQUE CLUSTERED INDEX PK_testc_aval_bval ON dbo.testc (aval,bval)
INSERT INTO testa VALUES (6,'Z')
--You would think this would fail
INSERT INTO testb VALUES (6,'Z')
--But it doesn't.
select * from testc
--last row will be (6,'Z','Z'), something that should have violated the uniq
ue
--constraint.
"Wes" wrote:
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||the only thing I can think of right now is to create a unique index on
an indexed view. I cannot think of a real life situation when it would
be feasible to do so.
Note that for Oracle you can also create check constraints on
materialized views, which lets you enforse business rules like 'no more
than 15 employees report to a manager'. Yet in Oracle world that is a
very complex way, recommended to use only as the very last resort.|||also this might be relevant:
http://www.dbazine.com/oracle/or-articles/tropashko8|||> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
To require all non-null values in a particular column to be unique but to
allow any number of nulls.|||I assume this would mean creating a unique index on view.Column1
and the view would be:
select XYZ from table
where Column1 is not null
The next question would be what is the alternative way to do this without a
view? I can think of a use defined function that checks for the existence
of the value in the table, or for null.
i.e. @.Column1 is null or not exists(select 1 from table where column1 =
@.column1)
Would such an aproach be worse for performance?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OXYynSGHGHA.3624@.TK2MSFTNGP09.phx.gbl...
> To require all non-null values in a particular column to be unique but to
> allow any number of nulls.
>|||>I assume this would mean creating a unique index on view.Column1
Correct.
> The next question would be what is the alternative way to do this without
> a
> view? I can think of a use defined function that checks for the existence
> of the value in the table, or for null.
You are assuming a single alternative. A trigger is another. I'm sure
there are others - perhaps involving some overly clever and obscure
technique.
> Would such an aproach be worse for performance?
Judging whether one approach is superior to another requires an evaluation
of the pros and cons of all alternatives and an evaluation of the impact of
each on the system in general. As usual, it is impossible to say that any
approach is superior to any other without delving into the specifics of the
system and its usage.|||Scott,
Thanks for the clarification.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ONQCLyGHGHA.3100@.tk2msftngp13.phx.gbl...
> Correct.
>
without
existence
> You are assuming a single alternative. A trigger is another. I'm sure
> there are others - perhaps involving some overly clever and obscure
> technique.
>
> Judging whether one approach is superior to another requires an evaluation
> of the pros and cons of all alternatives and an evaluation of the impact
of
> each on the system in general. As usual, it is impossible to say that any
> approach is superior to any other without delving into the specifics of
the
> system and its usage.
>|||We have a table that list a personid, relationtypeid (~CEO,CFO,…Employee),
and companyid.
These 3 items are unique per row but the relationtypeid and companyid should
also be unique when the relationtypeid is of a certain type. The thought i
s
to create a schema bound view with a unique clustered index on it. The view
would have the applicable filter based on the relationtypeid.
"Jim Underwood" wrote:
> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
> indexed view to create constraints.
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
>
>
database design flaws usually lead to this sort of construct?Can you give an example of how you would use an indexed view to create a
constraint? I've heard of indexed views, but never heard of using an
indexed view to create constraints.
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||For a constraint on a view to be meaningful, the view would have to be
updatable. The restrictions on updatable views are that
-the columns in the view must be directly related to the columns in the base
tables (no aggregate functions)
-any update to the view must affect one one of the base tables involved at a
time.
So, if you wanted to create a primary key constraint using two columns in a
view that orginated it two different base tables, it would not act as a
constraint per se. Any insert or update you tried to perform on the view tha
t
modified both columns simultaneously would fail because it would violate the
second rule above. In other word, you would have the constaint, but you
wouldn't be able to use it in the way that you would like.
The index (as a result of the primary key constraint) would help with
SELECTs though.
A unique clustered index on a view also might not provide the constraint
enforcement you are looking for. Consider the following:
create table testa (aid int, aval char(1), PRIMARY KEY (aid))
go
create table testb (bid int, bval char(1), PRIMARY KEY (bid))
go
INSERT INTO testa VALUES (1, 'M')
INSERT INTO testa VALUES (2, 'A')
INSERT INTO testa VALUES (3, 'R')
INSERT INTO testa VALUES (4, 'K')
INSERT INTO testa VALUES (5, 'O')
INSERT INTO testb VALUES (1, 'W')
INSERT INTO testb VALUES (2, 'I')
INSERT INTO testb VALUES (3, 'L')
INSERT INTO testb VALUES (4, 'L')
INSERT INTO testb VALUES (5, 'Y')
CREATE VIEW dbo.testc WITH SCHEMABINDING AS
(SELECT a.aid, a.aval, b.bval from dbo.testa a inner join dbo.testb b on
a.aid=b.bid)
CREATE UNIQUE CLUSTERED INDEX PK_testc_aval_bval ON dbo.testc (aval,bval)
INSERT INTO testa VALUES (6,'Z')
--You would think this would fail
INSERT INTO testb VALUES (6,'Z')
--But it doesn't.
select * from testc
--last row will be (6,'Z','Z'), something that should have violated the uniq
ue
--constraint.
"Wes" wrote:
> Is using indexed views to create constraints a good idea? If not, what
> database design flaws usually lead to this sort of construct?
>|||the only thing I can think of right now is to create a unique index on
an indexed view. I cannot think of a real life situation when it would
be feasible to do so.
Note that for Oracle you can also create check constraints on
materialized views, which lets you enforse business rules like 'no more
than 15 employees report to a manager'. Yet in Oracle world that is a
very complex way, recommended to use only as the very last resort.|||also this might be relevant:
http://www.dbazine.com/oracle/or-articles/tropashko8|||> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
To require all non-null values in a particular column to be unique but to
allow any number of nulls.|||I assume this would mean creating a unique index on view.Column1
and the view would be:
select XYZ from table
where Column1 is not null
The next question would be what is the alternative way to do this without a
view? I can think of a use defined function that checks for the existence
of the value in the table, or for null.
i.e. @.Column1 is null or not exists(select 1 from table where column1 =
@.column1)
Would such an aproach be worse for performance?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OXYynSGHGHA.3624@.TK2MSFTNGP09.phx.gbl...
> To require all non-null values in a particular column to be unique but to
> allow any number of nulls.
>|||>I assume this would mean creating a unique index on view.Column1
Correct.
> The next question would be what is the alternative way to do this without
> a
> view? I can think of a use defined function that checks for the existence
> of the value in the table, or for null.
You are assuming a single alternative. A trigger is another. I'm sure
there are others - perhaps involving some overly clever and obscure
technique.
> Would such an aproach be worse for performance?
Judging whether one approach is superior to another requires an evaluation
of the pros and cons of all alternatives and an evaluation of the impact of
each on the system in general. As usual, it is impossible to say that any
approach is superior to any other without delving into the specifics of the
system and its usage.|||Scott,
Thanks for the clarification.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:ONQCLyGHGHA.3100@.tk2msftngp13.phx.gbl...
> Correct.
>
without
existence
> You are assuming a single alternative. A trigger is another. I'm sure
> there are others - perhaps involving some overly clever and obscure
> technique.
>
> Judging whether one approach is superior to another requires an evaluation
> of the pros and cons of all alternatives and an evaluation of the impact
of
> each on the system in general. As usual, it is impossible to say that any
> approach is superior to any other without delving into the specifics of
the
> system and its usage.
>|||We have a table that list a personid, relationtypeid (~CEO,CFO,…Employee),
and companyid.
These 3 items are unique per row but the relationtypeid and companyid should
also be unique when the relationtypeid is of a certain type. The thought i
s
to create a schema bound view with a unique clustered index on it. The view
would have the applicable filter based on the relationtypeid.
"Jim Underwood" wrote:
> Can you give an example of how you would use an indexed view to create a
> constraint? I've heard of indexed views, but never heard of using an
> indexed view to create constraints.
>
> "Wes" <Wes@.discussions.microsoft.com> wrote in message
> news:EA28B504-0098-4314-8014-8082AD6662B7@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)