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?
Hi all,
My question is about Indexs on partition where I have a table with say 5 partitions and I want to create index on partitions and not on the whole table. The objective is that if i create a table level index on a partition table and eventually if I drop one of the partition or add another partition, what will happen to the index?
1) Do I need to re-create the index for the partion which are left after deleting one partition?
2) If a partition is added do I need re-create the index for the whole table or just create the index for that particular new partition?
Let me know if there is any white paper or code available. I have gone through the white paper published "SQL Server 2005" Partitioned Tables and Indexes Author: Kimberly L. Tripp, Founder, SQLskills.com
Hi!
using table partition;
is it possible to build index only on the latest partition?
Best regards
Is there anyway to drop an index at the partition level in SQL 2005 ?There is a way to rebuild at the partition level (assuming this creates)i.e.: alter index ALL on dbo.LP_CQ rebuild partition = 229 ;I want to:1. Drop index(es) at partition level (partitioned by period value (1..400)).2. bulk copy data in (can be 1 or more periods per day).3. Rebuild at Partition level.Thx
View 1 Replies View Related I have one partition table "tablea" with partition key dateentry on yearly basis and table have four partition with name y2013,y2014,y2013,y2015 with one partition schema . How I can create partition index on tablea that first time create partition  index  and next time I want to rebuild index only on y2015 partition .Â
<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>.
I have a table partitioned by month. There are no primary key. There is another table with the same structure that I use to load data for the current month. I can perform partition switch with no problem.
I created non unique index on integer field in both tables. After this change partition switch does not work anymore. Here is an error that I am getting:
'ALTER TABLE SWITCH' statement failed. The table 'dbo.test' is
partitioned while index 'IX_test' is not partitioned."
Any help?
Hi ,
I have question regard data partition view .
Please see below sample from BOL + sample of execution plane .
I would like to ask what is the way to avoid the optimizer scan tables out of the scope (I would expect that the only table for this query will be SUPPLY1)
Thanks,
Eyal
--This example uses tables named SUPPLY1, SUPPLY2, SUPPLY3, and SUPPLY4, which correspond to the supplier tables from four offices, located in different countries/regions.
USE tempdb
GO
--create the tables and insert the values
CREATE TABLE SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
)
CREATE TABLE SUPPLY2 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
supplier CHAR(50)
)
CREATE TABLE SUPPLY3 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
supplier CHAR(50)
)
CREATE TABLE SUPPLY4 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
supplier CHAR(50)
)
GO
--create the view that combines all supplier tables
CREATE VIEW all_supplier_view
AS
SELECT *
FROM SUPPLY1
UNION ALL
SELECT *
FROM SUPPLY2
UNION ALL
SELECT *
FROM SUPPLY3
UNION ALL
SELECT *
FROM SUPPLY4
GO
INSERT all_supplier_view VALUES ('1', 'CaliforniaCorp')
INSERT all_supplier_view VALUES ('5', 'BraziliaLtd')
INSERT all_supplier_view VALUES ('231', 'FarEast')
INSERT all_supplier_view VALUES ('280', 'NZ')
INSERT all_supplier_view VALUES ('321', 'EuroGroup')
INSERT all_supplier_view VALUES ('442', 'UKArchip')
INSERT all_supplier_view VALUES ('475', 'India')
INSERT all_supplier_view VALUES ('521', 'Afrique')
GO
/* */
SELECT * FROM all_supplier_view WHERE supplyID BETWEEN 1 and 150
I have a partition view named StudentRequest with underlying tables StudentRequest_T1 and StudentRequest_T2.
Primary Key is on NumericSchoolDBN, SchoolYear, TermId, StudentID, CourseCode
Partition View
Create View dbo.StudentRequestPartView with SchemaBinding
as
Select
NumericSchoolDBN, SchoolYear, TermId, StudentID, CourseCode, AssignedCourseCode, AssignedSectionId,
LockAssignedRequest, UpdatedByPID, UpdatedDate, CreatedByPID, CreatedDate, AuditCommentId, DebugTrace
from dbo.[StudentRequest_T1]
union all
Select
NumericSchoolDBN, SchoolYear, TermId, StudentID, CourseCode, AssignedCourseCode, AssignedSectionId,
LockAssignedRequest, UpdatedByPID, UpdatedDate, CreatedByPID, CreatedDate, AuditCommentId, DebugTrace
from dbo.[StudentRequest_T2]
go
Partition Tables
CREATE TABLE [dbo].[StudentRequest_T1](
[NumericSchoolDBN] [int] NOT NULL,
[SchoolYear] [smallint] NOT NULL,
[TermId] [tinyint] NOT NULL Check ([TermID] = 1),
[StudentID] [int] NOT NULL,
[CourseCode] [varchar](10) NOT NULL,
:
)
CREATE TABLE [dbo].[StudentRequest_T2](
[NumericSchoolDBN] [int] NOT NULL,
[SchoolYear] [smallint] NOT NULL,
[TermId] [tinyint] NOT NULL Check ([TermID] = 2),
[StudentID] [int] NOT NULL,
[CourseCode] [varchar](10) NOT NULL,
:
)
I am able to insert, update and delete records in the StudentRequest view using simple DML SQL statements i.e.
e.g.
Insert into Student Request (NumericSchoolDBN, SchoolYear, TermId,€¦)
Values(12345, 2006, 1,€¦)
Delete from StudentRequest Where NumericSchoolDBN = 12345 and TermId = 1
But when I use complex SQL statements using self-joins€¦
Insert into Student Request (NumericSchoolDBN, SchoolYear, TermId,€¦)
Select NumericSchoolDBN, SchoolYear, TermID,€¦.
From Student Request sr1 left outer join Student Request sr2 on
Sr1.NumericSchoolDBN = Sr1.NumericSchoolDBN and
Sr1.SchoolYear = Sr2.SchoolYear and
Sr1.TermId = Sr2.TermId and
Sr1.StudentID = Sr2.StudentID and
Sr1.CourseCode = Sr2.CourseCode
I get the following error and I can€™t seem to find any documentation that this is a limitation!!!...
Msg 4439, Level 16, State 6, Procedure Course_UpdateCoursePromotion, Line 232
Partitioned view 'STARS.dbo.StudentRequest' is not updatable because the source query contains references to partition table '[STARS].[dbo].[StudentRequest_T1]'.
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 everyone,
I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?
Can you please give some suggestions? It will be very helpful if you can post some references or examples.
Thank you!
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:
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 RelatedI 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!
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)
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 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]