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!
I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
Hello all.
I have the following table
Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.
Is this a bug?
Thanks in advance,
TipoMan
I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.
And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?
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 RelatedHi
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:
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!!
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!
Hi,
Can we define Index on view?
Thanks,
Rahul Jha
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
Is it possible in the existing views to create index
View 1 Replies View RelatedG'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)
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
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?
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
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
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
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
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 RelatedI 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 RelatedI 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
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.
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.
Hi ALL,
How to create index in the view on MS SQL 2000?
Thank you very much!
I have a view that joins a dozen tables with a million rows added per year by an application. I want to materialize it. The view is always filtered by date first on reports, then there are a few key transaction keys, but then many other fields required to make each row unique. I don't want to add these columns since they are large, many, not used for sorting or filtering, and may not define uniqueness in a future application design. I need a uniqueifier that is application agnostic. I prefer a bigint. So to store the materialized view ideally for reporting, I want to add the following clustered index to materialize the view:
CREATE unique CLUSTERED INDEX idx1
ON [dbo].[myview](myDate, key1, key2, key3, id bigint identity(1,1) NOT NULL)
And I get this error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'bigint'.
Can I do what I want? If so, how?
Consider following code:
SELECT e1.EntityIdentity as CompanyID
FROM dbo.Entitye1 --company
JOIN dbo.EntityAssociationea
ON e1.EntityID = ea.EntityID1
JOIN dbo.Entitye2 --user
ON ea.EntityID2 = e2.EntityID
This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).
Since dbo.Entity and dbo.EntityAssociation change infrequently I thought that an indexed view would really improve performance. But I've found one of the seemingly undocumented Microsoft features when trying to create the clustered index and get the following error msg:
Msg 1947, Level 16, State 1, Line 1
Cannot create index on view "ROICore.dbo.vEntityEntityAssociation_CompanyUser". The view contains a self join on "ROICore.dbo.Entity".
I really need to improve performance on this subquery. Entity currently has over 20m rows and EntityAssociation over 35m rows and both are growing.
How to improve performance? Indexes on both tables for the most part give index seeks, but I thought my saviour might be the index view. Obviously this will not work.
I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.
I have created a view with the following code:
ALTER view dbo.FactView
with schemabinding
as
select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]
, sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]
, sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]
, sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]
, sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]
, sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]
from dbo.FactConsumptionPart
group by local_date_key, read_type_key, meter_key, unit_key
I then created an index on the view as follows:
create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)
I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Hi
I need to detach a Database, but with "EXEC sp_detach_db 'test','false'" I can't do this because it is in use.
Is there a Possibility to make a "force detach" or something?
Thanks for Help
Greets
Id like to know how I force the transaction log to shrink in v2000?
I have sp_force_shrink_log but this only works in v7.
... Also, how do I set the log to truncate on checkpoint?
Many thanks,
Lauryn
How to force the primary key for a table to begin at a set Primary Id and not standard count (1,2,3,4,5..10,11,12) etc.
Pretty much how to force it being on number I would like to begin.