How To Create Index In The View On MS SQL 2000?--URGENT!!!

Sep 14, 2001

Hi ALL,

How to create index in the view on MS SQL 2000?

Thank you very much!

View 4 Replies


ADVERTISEMENT

Can't Create An Index On My View

Dec 1, 2004

G'day all.

I am trying to create an index on a view that joins two tables.

I get the classic error of course:
'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'

Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.

Code is below and any help greatly appreciated.

CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS
SELECTt_number
FROM dbo.ticket_cancellations RIGHT OUTER JOIN
dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number
WHERE dbo.tickets.t_cancelled <> - 1 OR
-- Add all cancellation codes that are to be excluded from the NET view below
(dbo.ticket_cancellations.tc_cancellation_code <> 83943
AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)

GO
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)

View 1 Replies View Related

CREATE INDEX ON VIEW

May 28, 2008

Cannot create index on
view 'View name ' because the view is not schema bound." What is the
Schema bound ? How can i create the view Schema bound ?

Pls help me out Sir


Yaman

View 1 Replies View Related

How Do I Create A Fulltext Index On A View?

Oct 30, 2007

Hello all,I'm wanting to do a CONTAINS query on fields that belong to two seperate tables.  So, for example, this is what I'd *like* to do :SELECT ci.itemNameFROM Content_Items ciINNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemIdINNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table.  I would then create a fulltext index on this view, and do fulltext queries against it.It seems like this should work.  However, when I try to create a fulltext index on my newly-created view, I get this error :A unique column must be defined on this table/view.How do I create a unique column within a view?Also, will my approach work, or will it have unintended consequences?  I'm using this as part of a search component in my software, so it has to perform reasonably well.

View 1 Replies View Related

Create Clustered Index On A View

Mar 16, 2001

I have a view named select id,name,state from customer where state ='va'
can I create clustered index on a view (name) if so please provide me with the sql statement.
Thanks
Al

View 2 Replies View Related

How Do I Create A Fulltext Index On A View?

Oct 30, 2007

Hello all,

I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :

SELECT ci.itemName
FROM Content_Items ci
INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId
INNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]

However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.

So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.

It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :
A unique column must be defined on this table/view.

How do I create a unique column within a view?

Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.

View 1 Replies View Related

How Do I Create A Fulltext Index On A View?

Oct 30, 2007

Hello all,


I'm wanting to do a CONTAINS query on fields that belong to two seperate tables. So, for example, this is what I'd *like* to do :


SELECT ci.itemName
FROM Content_Items ci
INNER JOIN Content_Pages AS cp ON ci.contentItemId = cp.contentItemId
INNER JOIN FREETEXTTABLE(Content_Items, (ci.title, cp.pageText), @searchString) AS ft ON ci.contentItemId = ft.[KEY]


However, this will not work, since SQL Server won't let you do fulltext queries against more then one table.


So my idea is to create a view that combines my Content_Items table with the desired column from my Content_Pages table. I would then create a fulltext index on this view, and do fulltext queries against it.


It seems like this should work. However, when I try to create a fulltext index on my newly-created view, I get this error :
A unique column must be defined on this table/view.


How do I create a unique column within a view?


Also, will my approach work, or will it have unintended consequences? I'm using this as part of a search component in my software, so it has to perform reasonably well.

View 7 Replies View Related

Create A View In MSSQL 2000

Oct 12, 2005

I have created a view in MS SQL2000 as followed:

Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;

my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.

Any HELP would be GREAT.
Thank You!!

View 3 Replies View Related

Unable To Create Indexable View On SQL 2000.

May 1, 2008

Hello,

I have a query that seems to take a while to execute and I'm looking into using an indexed view to see if this helps. I use the script below to create the view but when I query it's indexability using:

(select ObjectProperty(object_id('GetMessageQueueDetails'), 'IsIndexable'))

it always return '0'.

Here is a very cut down version of the view, only selects the uid! from a single table


IF OBJECT_ID ('GetMessageQueueDetails', 'view') IS NOT NULL

DROP VIEW GetMessageQueueDetails ;

GO



IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON

IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON

IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON

IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON

IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON

IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON

IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF

GO



CREATE VIEW GetMessageQueueDetails

WITH SCHEMABINDING

AS

SELECT Uid

FROM dbo.MyTable

GO

I see from See http://msdn.microsoft.com/en-us/library/aa933148(SQL.80).aspx that the pre-requsites for indexed views are pretty strict, I have been through this list and think I have everything covered, except for :

"The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view."

Is there an easy way to find out if ANSI_NULLS was ON or OFF when the table was created. If it was OFF can I do an ALTER TABLE to turn it on and will that make the view indexable? If so how do I do this with out trashing the data in the table?

Or am I doing something else wrong?

Can anybody offer any help?

Thanks

View 2 Replies View Related

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

Jul 23, 2005

I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?

View 3 Replies View Related

Clustered Index On Client_ID+ORderNO+OrdersubNo, If I Create 3 Noncluster Index On Said Column Will It Imporve Performance

Dec 5, 2007



Dear All.

We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.

table is >> Order_Dtl

Columns are:-

Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)

Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.

The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.

Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)

My Question are:-


Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?

Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?

Q3. I want to check what indexes has been used? on what search?

Q4. How can i check what table was populated when, or last date of update (DML)?

My Limitation is i Dont Create a Partioned table. I dont have permission to do it.



In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.


I am thank u to all who read or reply.

Arshad

Manager Database
Esoulconsultancy.com

(Teradata Master)
10g OCP










View 3 Replies View Related

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

Problems On Create Proc Includes Granting Create Table Or View Perissinin SP

Aug 4, 2004

Hi All,

I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:

Create PROC dbo.GrantPermission
@user1 varchar(50)

as

Grant create table to @user1
go

Grant create view to @user1
go

Grant create Procedure to @user1
Go



Thanks Guys.

View 14 Replies View Related

Grant CREATE VIEW, CREATE PROCEDURE ...

Apr 12, 2006

Hi,

I have currently a problem with setting up the permissions for some developers. My configuration looks like this.

DB A is the productive database.

DB B is a kind of "development" database.

Now we have a couple of users call them BOB, DAVID, ...

who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.

Therefor I added them to the db role db_owner for db b.

For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message

'Msg 262, Level 14, State 1, Procedure Test, Line 3

CREATE VIEW permission denied in database 'b'.'

I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.

What's my mistake?

Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.

Regards,

Stefan

View 8 Replies View Related

Alter View / Create View

Aug 14, 2000

I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.

I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.

If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.

View 1 Replies View Related

Index On View

Jun 15, 2004

Hi

I have a problem creating an index on a view. The view should return the record corresponding to the Maximum Obje_ID. This seems to work.

CREATE VIEW dbo.D_Object_View
WITH SCHEMABINDING
AS
SELECT
Policy_ID,
Obj_ID,
Environment_Code,
CoB,
Sub_CoB,
Policy_No,
Version_No,
Object_Type,
Item_Seq,

FROM dbo.D_Object
WHERE
(Obj_ID IN
(SELECT MAX(Obj_ID)
FROM dbo.d_object
GROUP BY Environment_Code, COB, Policy_No, SUB_COB, Object_Type, Item_Seq))


I create the index with the following statement :
CREATE UNIQUE CLUSTERED INDEX [IX_Object_ID] ON [dbo].[D_Object_View]([Obj_ID]) ON [PRIMARY]

but get the following error :
Cannot index the view 'DB.dbo.D_Object_View'. It contains one or more disallowed constructs.

I think it is because of the MAX statement but don't know of any other way to do it. :confused:

View 4 Replies View Related

Index On View

Sep 30, 2004

Is it possilbe to create an index on a view using a linked server table? I am using openquery and I know I can't use rowset functions if I want to index a view.

I just didn't know if there was any other way to access the linked server data in order to create an index.

thanks!!

View 2 Replies View Related

About Index And View

Jul 8, 2006

hi,i am a beginner, i want to know
what is the different between index and view?
in my option,i think they do one job:let the select quickly!

View 2 Replies View Related

Index On View

Sep 12, 2007

Hi,
Can we define Index on view?




Thanks,
Rahul Jha

View 13 Replies View Related

Index On A View

Feb 17, 2004

Does anyone know of a trick that allows you to apply an index on the following view? I would like to index the license_id column. When I try to apply a unique clustered index I get the following error - Index on view 'dbo.v_lic_status' cannot be created because function 'getdate' yields nondeterministic results.

VIEW DDL:

SELECT license_id,
CASE
WHEN (term_date < GETDATE()) THEN 'Terminated'
WHEN (suspend_date < GETDATE()) THEN 'Suspended'
WHEN (expiration_date < GETDATE()) THEN 'Expired'
WHEN (effective_date < GETDATE()) THEN 'Active'
ELSE 'Pending'
END AS lic_status
FROM dbo.license

INDEX DDL:

create unique clustered index ux_v_lic_status_01 on dbo.v_lic_status (
license_id
)
with
fillfactor= 90
go

View 5 Replies View Related

View With Index.

Jun 17, 2008

Is it possible in the existing views to create index

View 1 Replies View Related

Create INDEX Within CREATE TABLE DDL

Jan 27, 2006

Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance

View 2 Replies View Related

Recreating Index After Restore--URGENT PLEASE

Sep 4, 2002

Hi all,
I have created a new database from a backup. When I run the application, it seems like it is not getting the right indexes but it is in Development environment. So, I ran a script to recreate the index by DBCC DBREINDEX(TABLENAME). Still not getting it right. mainly it is not using the primary key index. I believe that dbcc dbrenindex will also reindex primarykey indexes. Any one know, how to overcome this problem. It is a production issue and I would appreciate for your prompt advise.
Note: Does anyone has any script to drop the primarykey index and to recreate it.
Thanks,
Joe

View 1 Replies View Related

How To Force An Index On The View

Jan 21, 2004

I created a view from a table with out any where clause. All the rows from the table will be in the view with some extra info.

The table has a few indexes.

In my stored procedure I am storing two columns from the table ( IdHi , IdLo - primary key ) into a temp table and joining the temp to the view.
Here the query is taking too much time and not using the index. Can I force the primary key index on View?

Thanks!

View 1 Replies View Related

View Behaviour With Index.

May 12, 2006

Hi

If I have a view:

SELECT A,B
FROM tblTable
WHERE A = 1

And then I have a s-proc using that view:

SELECT *
FROM MyView
WHERE B > 6

My question: If I add an index to tblTable for the column B (not used in the view's WHERE clause, but used in the s-proc), will it have a performance improvement, because of the WHERE B > 6 on the view, assuming that this condition would benefit from the index if it were in the view itself.

I guess I could also put it this way: can an index on a column in a table improve the performance of a condition on a view using that table.

Thanks

Peter

View 2 Replies View Related

Partition View And Index

Jan 28, 2004

I have created a horizontal partition view from 4 physical tables.
just wondering how the index works in the partition view:
1) If I need to build an index on a column, do I need to build
it on all 4 physical tables? or I just build it on the view? or build
it on view and 4 physical tables?

2) If I build it on view, and if I add a table into view, do I need to
recreate all indices on the view?

View 6 Replies View Related

Index A View Or A Table

Apr 8, 2004

I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

The table is also involved in frequent non-simple select statements. It currently has about a million rows.

Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

Thanks

View 14 Replies View Related

Index For SQL Server View

Apr 25, 2008

Can someone show me how to create an index on the lrsn field in the below view. This view indexes are totally new to me but are needed due to slow performance.

Thanks
DG Hall

CREATE VIEW dbo.V_Comm_Imp_Summary
AS
SELECT TOP 100 PERCENT cu.Comm_Use, cu.year_built, cu.lrsn, dbo.V_Comm_AG_Area.area AS AG_Area, dbo.V_Comm_Bsmt_Area.area AS BG_Area,
cu.extension, dbo.v_CommUse_Description.use_description
FROM dbo.V_Comm_Use cu INNER JOIN
dbo.v_CommUse_Description ON cu.Comm_Use = dbo.v_CommUse_Description.Comm_Use LEFT OUTER JOIN
dbo.V_Comm_Bsmt_Area ON cu.lrsn = dbo.V_Comm_Bsmt_Area.lrsn LEFT OUTER JOIN
dbo.V_Comm_AG_Area ON cu.lrsn = dbo.V_Comm_AG_Area.lrsn

View 2 Replies View Related

Index On A View Question

Feb 6, 2008

In the section on index tuning in chapter 3 of Microsoft Press manual Inside Microsoft SQL Server 2005: T-SQL Querying, the view dbo.VEmpOrders is created along with an index on the view. When a query is run against the view, the execution plan is supposed to use the index created in the view. When I run the example query, the execution plan uses an index in one of the base tables, dbo.Orders.

The execution plan in the book shows:
SELECT <--- Clustered Index Scan (using Performance.dbo.VEmpOrders.idx_ucl_eid_oy)

When I run the query as shown in the book, I get the execution plan:
SELECT <-- Hash Match <-- Compute Scalar <-- Index Scan (using Performance.dbo.Orders.idx_unc_od_oid_i_cid_eid)

Code to create the view and the subsequent query are:IF OBJECT_ID('dbo.VEmpOrders') IS NOT NULL
DROP VIEW dbo.VEmpOrders;
GO
CREATE VIEW dbo.VEmpOrders
WITH SCHEMABINDING
AS

SELECT empid, YEAR(orderdate) AS orderyear, COUNT_BIG(*) AS numorders
FROM dbo.Orders
GROUP BY empid, YEAR(orderdate);
GO

CREATE UNIQUE CLUSTERED INDEX idx_ucl_eid_oy
ON dbo.VEmpOrders(empid, orderyear);
SELECT empid, orderyear, numorders
FROM dbo.VEmpOrders;

How can I get the execution plan to use the view index. Any help will be appreciated.

Thanks, Bill

View 4 Replies View Related

Urgent - View Query

May 29, 2007

Dear Friends
I have 3 form with these names :

Clients <----- Projects <------- Files

I am using this query in my [ web ] page :

select clients.id ,
(select count(*) from projects where projects.clients_id = clients.id) as TotalProjects

from clients



I want to compute SUM for all relevant files for each client's projects.
how i can add it to select select statement ??


thank you.
m.o

View 5 Replies View Related

Clustered Index On Indexed View From ADO.NET

Oct 26, 2005

I have a clustered index on an indexed view in sql server 2000. When I do a simple select in query anaylser from this view I can see from the execution plan in profiler that the clustered index was used to return the data, hence improving performance of the underlying select(this is why I am using the indexed view). However, if I run the query from an asp.net page using the sql provider I can see the call in profiler but the clustered index is not used, hence reducing the performance of the call considerably.If anyone has experienced this please let me know.Cheers 

View 1 Replies View Related

URGENT: Reset Database Index Values SQLExpress

Jul 15, 2005

Hello! I like to reset my database (all tables in the database) to
default starting index values. I tried to use the command "TRUNCATE
TABLE" but it does't work becuase of the referenced foreign keys. I get
always the error message: Cannot truncate table 'T_MyTable' because it
is being referenced by a FOREIGN KEY constraint.
You know a solution for that problem?
Perhaps save all constraints, then delete them, truncate the tables and
then create the old constraints again.But how can I save all
constraints?

Thanks to help me in this urgent problem! FOX

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved