SQL 2012 :: Clustered Indexing On View

Jun 15, 2015

I've created a table which will hold staffing data (name, grade, etc.) and any shifts that are going to be entered. I've got some test data in the table.

The base table looks like this:-

CREATE TABLE [dbo].[tbl_ForecastShifts_New](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ward] [nvarchar](255) NULL,
[StaffBand] [real] NULL,
[StaffMemberName] [nvarchar](255) NULL,

[Code] ....

I've attached a spreadsheet with the test data from the table and the required layout on the second tab.

I've created a view which displays the data as I want it and I need to attach this to an MS Access front-end for the users to input/edit the data in the table with the use of a Access form.

The view I've created looks like this:-

CREATE view [dbo].[vw_Forecast_Staffing] with schemabinding

[Code] ....

It displays all the staff members and any shifts they've been given for a 7-day period with day 1 begin supplied by the user.

The user will also supply the ward they are interested in viewing.

When I attached the view to Access it becomes read-only as it doesn't have any indexes on it.

I can't create a clustered index on the view as there are derived tables.

View 3 Replies


SQL Server 2012 :: Clustered Index For Materialized View?

Aug 8, 2015

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:

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?

View 1 Replies View Related

Adding Full-Text Indexing To Clustered SQL7?

Jun 17, 2002

I've got MS SQL7 running on an MSCS cluster in an active/passive configuration (SQL runs on only 1 node out of the two nodes in the cluster).

I need to add Full-Text Indexing to SQL, and cannot find anything in the BOL on adding Full-Text Indexing to an existing SQL isntallation, let alone anything about adding it to a clustered intallation.

Does anybody out there have any experience with adding Full-Text Indexing to an existing SQL install, or better yet, to a clustered SQL install?

View 3 Replies View Related

SQL 2012 :: Remember Definitions Of Clustered And Non Clustered Indexes?

Nov 24, 2014

What is the easiest way to remember the definitions of clustered and non clustered indexes.

View 9 Replies View Related

Indexing A View

Dec 14, 2005

Hi All,I'm hoping someone can help me. I think I'm missing something verybasic. I'm trying to put a clustered index on a view that I havecreated. I keep getting the error:Server: Msg 8668, Level 16, State 1, Line 1An index cannot be created on the view 'cew_avwage_uscnty' because theselect list of the view contains a non-aggregate expression.Here is the create view statement:CREATE view dbo.cew_avwage_uscntywith schemabindingasselect statefips, countyfips, naics_code, sum(disc * 0) as disc,data_year, sum(qtr_payroll)/((sum(emp1+emp2+emp3))/12) as avwagefrom dbo.qcew_own_n where year= '2004'and (ownership = '0'and (naics_code like '__' or naics_code like '__-__')or (ownership = '5' and naics_code = '10'))and countyfips <> '999'and naics_code <> '99'and ((disc = '0') or (disc <> '0' and (emp1 <> 0 or emp2 <> 0 or emp3 <>0)))group by statefips, countyfips, naics_code, data_yearHere is the create index statement I am using:create unique clustered index main_cdxon dbo.cew_avwage_uscnty (statefips, countyfips, naics_code, year)Each field that I have listed in the select statement MUST appear in theview. The disc field needs to be set = 0 and the data_year field needsto be set = 2004. Initially I had select statement for disc anddata_year set as, disc = '0', year = '2004' .. but thought this wasgiving me my problem. I changed it and am still getting the same error?Is my problem because of the fields that I am grouping by? Any advicewould be very much appreciated. I'm trying to avoid creating an extracttable, as these data are updated regularly - so I would like to onlyupdate the base table and have the view refresh itself.Thanks again!Bethany*** Sent via Developersdex http://www.developersdex.com ***

View 6 Replies View Related

Indexing View Definition Table Columns

Sep 29, 2007

Hi experts,

Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns

CREATE View hrinu.Parity as
T1.Matcle as CorpID,
T2.Nmpres as Name,
T4.DATDEB as LeaveFrom,
T4.DATFIN as LeaveTo,
T10.LIBLON as LeaveType,
T8.LIBLON as Location,
T12.LIBLON as ParentOrg

from HRINU.zy00 T1,
HRINU.zy3y T2,
HRINU.zy39 T3,
HRINU.zyag T4,
HRINU.zy38 T5,
HRINU.zy1s T6,
HRINU.zd00 T7,
HRINU.zd01 T8,
HRINU.zd00 T9,
HRINU.zd01 T10,
HRINU.zd00 T11,
HRINU.zd01 T12
where T4.Nudoss = T3.nudoss
and T4.Nudoss = T1.Nudoss
and T1.Nudoss = T2.nudoss
and T3.nudoss = T5.nudoss
and T6.nudoss = T1.nudoss
and T6.stempl = 'A'
and t7.cdstco = 'z04'
and t9.cdstco = 'DSJ'
and t11.cdstco= 'DRE'
and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00
and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00
and T6.dtef1s <= getdate() and T6.datxxx > getdate()

Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables.
Also throw some light on how to analyse the execution plan for further enhancements.

Thanks in advance

Arvind L

View 3 Replies View Related

INDEXING A VIEW In MSSQL2K5x64.de Standard W.SP1 ... ERROR 1939

Aug 2, 2006

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:






















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

View 7 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

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.

View 2 Replies View Related

How To Create Non-unique Clustered Indexed View?

May 30, 2008

I'm not able to create indexed views,
which are clustered-indexed on only 1st field.

I can't even INCLUDED other fields.

I need the entire view to exist as a physical table. (for performance)

Please let me know the work around.


View 1 Replies View Related

SQL 2012 :: Importing Data And Indexing

Jul 22, 2014

I have a CSV File, I am importing this into SQL Server using SSIS package through Flat File source Task.

Few Points about data & its handling
1) inside the Procedure they are dropping the index, then populating the table, then again creating the same index.
2) Data is huge in figure (say, in millions)

My doubt:
which is the best way to import the data
1) Just inserting the data without dropping the Index
2) Drop index, populate table, re-create index (the way they do right now)

View 3 Replies View Related

SQL 2012 :: Disable File Indexing On Drives

Feb 25, 2014

Is it a best practice to disable "Allow files on this drive to have contents indexed" on NTFS drives used by SQL for its data, log, tempdb, etc?

In what I've read it seems to be a best practice for Filestream objects and Flash storage drives. We don't currently use Filestream objects or have Flash drives.

Are there any benefits or drawbacks to disabling this feature on an NTFS drive connected to SAN LUNs under mount points?

View 2 Replies View Related

SQL Server 2014 :: Indexed View Not Being Used For Partitioned Clustered Column-store Index?

Oct 9, 2015

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
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?

View 1 Replies View Related

Creating Clustered Index On View With Table Containing XML Data Types Takes Forever And Causes Timeouts

Apr 21, 2007

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,






The view definition is displayed below.



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



declare @pidstr varchar(100)

SET @pidstr = LOWER(CONVERT(varchar(100), @PID))

return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')


The definition for UDF f_GetIfModificationExist

ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )

returns Bit

with schemabinding



declare @pidstr varchar(100)

SET @pidstr = LOWER(CONVERT(varchar(100), @PID))

return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')


The Statement to create the index is below.




[RecordID] ASC


View 1 Replies View Related

SQL 2012 :: AlwaysOn With Clustered Disks

Mar 13, 2014

I am trying to build the 2 node 2 clusters with the AlwaysOn.

Here isthe landscape.

2 nodes PROD failover cluster (running once instance)
2 nodes DR failover cluster (running 2 instances - DR and PRE-PROD)

Both clusters are in different geographies.

PRE-PROD can be editable. So out of scope of Always On.

One instance on PROD -> DR of the other box. [Want to achive thru AlwaysON]

Now my Question:

1) Do i need to have all the 4 nodes in same failover cluster group? If yes, then this would become MultiSubnet cluster Or Is there any way those 2 diffrerent failover clusters (one DR and one PROD) can be part of AlwaysOn.

2) Can i use the clustered disks as in the above landscape for always on?

View 1 Replies View Related

SQL 2012 :: Clustered Index Delete

Mar 28, 2014

I want to know more details about the Clustered Index Delete. Is that Clustered Index Delete in the execution plan is good or bad or we can neglect that cost. Is there any way to avoid that clustered Index delete operator from the execution plan.

View 4 Replies View Related

SQL 2012 :: Monitoring Clustered Server

May 7, 2014

What are the events to be monitored for a Active/Passive Cluster?

View 4 Replies View Related

SQL 2012 :: Selecting A Clustered Index?

Jul 29, 2014

- What are your thoughts on adding clustered index on datetime (createdDate , native GUID) column. The data will be be physically organized in the clustered index allowing range operations to perform its duties. But will the GUID column make any impact ( drawbacks) should it be made part of the clustered key ?

The GUID column will provide the lookup with the required indexes to support.

View 3 Replies View Related

SQL 2012 :: Clustered Instances And Security

Jan 5, 2015

Is it possible for a clustered instance of SQL2012 to have 2 network names ?

Reason: I need to segregate admin access to a clustered instance so that the admins and SSMS connect via a different IP address than the application. I know I can block SSMS access via application-level firewalls, but ideally the application would connect to CLUSTER1INSTANCE01 on , say,, and the admins would connect to CLUSTER1ADMININSTANCE01 on, and they'd be the same instance, just using different names and IPs

View 3 Replies View Related

SQL 2012 :: Is Non Clustered Index Getting Always Scanned Useful

Mar 16, 2015

I created a NC index as suggested by missing index DMV(of course I don't create them blindly). This one seemed to be a useful index but I now see from index usage stats that it only got scanned 50 times in 4 days.No seeks, no lookups. So is it a good idea keeping such index.The table on which this index is created is used more for reads and less for writes.

View 6 Replies View Related

SQL 2012 :: Column For Clustered Index

Apr 16, 2015

Is it always the best practice to have the partition column also as the column for clustered index?

View 2 Replies View Related

SQL 2012 :: Restoring A Database On Clustered Server?

Mar 5, 2014

I have clustered SQL server machines(SSserver1 and SSserver2). I need to restore a database from another standalone server to this clustered environment. In this case do I need to restore the DB on both the nodes that are part of the clustering(SSserver1 and SSserver2) or Just can I do it on one of them?

View 5 Replies View Related

SQL 2012 :: Included Columns In Non-clustered Index

May 8, 2014

I am trying to tune a process that is running slowly. I analyzed the process using the Database Engine Tuning Advisor, and it recommended the creation of 3 indexes, all non-clustered:

1) ColA, include ColB
2) ColA, include ColC
3) ColA, include ColD

So... I created a single non-clustered index on:

4) ColA, include ColB, ColC, ColD

That should do the same thing, right? A look at my execution plan shows that the index I created is being scanned -- 3 times. What is puzzling me, though, is that the Database Engine Tuning Advisor is still recommending I create these 3 separate indexes, even with the index (4) that I created in existence.

If it matters, ColA, ColB, ColC and ColD are all int FKs.

View 2 Replies View Related

SQL 2012 :: Server Clustered Availability Group

Jul 30, 2014

I have 2, 2 node clusters

PROD1(cluster 1) Clustered SQL instance1
PROD2(cluster 1)
DR 1 (cluster 2) Clustered SQL insatace 2
DR 2 (cluster 2)

I have set an availability group up from the PROD instance to the DR instance.How does the AG behave if a SQL instance fails at PROD? Does it try to fail over to Node 2 on Prod before going over to DR? or bring the Replica at DR online straight away? Can we only use Manual Failover of the AG in this scenario to make use of the High Availability of the Windows cluster?

View 3 Replies View Related

SQL 2012 :: Cost Of Clustered Index Insert Is 100%?

Sep 7, 2014

how to reduce the cost of clustered index insert ?

View 8 Replies View Related

SQL Server 2012 :: 42% Of 100% Taken In Clustered Index Insert

Dec 1, 2014

I have a query which is primarily a victim of blocking and a blocker itself for other queries. I studied the plan for this and it shows a 42% cost on CI insert operation. The insert is happening on a table (Table A) that has a PK. This PK is not a running number. It is also a business key (primary key) in another master table (Table B).

My understanding is that the cost is heavy because -

1, this PK is not an incremental number. It could be any number not in a sequence.
2. while inserting into CI, there must be a scan happening to find out the location where the index will be inserted.

How can I reduce the cost?

1. Should I go for partitioning of this table Table A? I am trying to do this but I am not able to find any suitable partition key looking at the JOINS and filter clauses where this table is being used in the applicaiton.
2. Should I introduce a surrogate key (running number) as a primary key so that CI is faster ?

View 9 Replies View Related

SQL 2012 :: Possible Sources Of Fragmentation On Clustered Index

Jan 23, 2015

I have a table that has a clustered index that is only the identity column on the table. The table is somewhere around 200K rows and has 3800 pages in the index. We run our index maintenance every other day on this database using Ola's scripts and this index is rebuilt because it is 40-60% fragmented after 2 days. Overall, this isn't really too much of a problem since the index rebuild doesn't take too long, but I am puzzled as to how this index is getting fragmented since the only column in it is the identity.

CREATE TABLE [dbo].[Example](
[ExampleID] [int] IDENTITY(1,1) NOT NULL,
[ExampleCode] [varchar](10) NOT NULL,
[ForeignID] [int] NOT NULL,
[AnotherID] [int] NOT NULL,


) ON [PRIMARY]There is nothing strange like updates to the identity happening and while some records are deleted, there has only been about 20,000 in the life of the table (months). Not enough to account for the level of fragmentation that we're seeing on the index.About the only thing I can think of that would cause fragmentation on this index in this scenario are:

1. Page splits caused by starting with a small value in one of the VARCHARs and later inserting a larger value
2. Page splits caused by the NULLABLE column, ExampleDate, starting with NULLs and later updating them to a date.

For #1, I had development check the update scenarios for the varchar columns, especially the varchar(1000) one, and they didn't see it as a common thing where the values would go from small (or empty) to large.

For #2, I checked and found that the only value for that column in the table is NULL so while it always starts as NULL, it never gets updated to anything else.

I've tried looking at sys.dm_db_index_operational_stats and the leaf_update_count is around 300,000, but unless those updates are causing page splits, I don't see how they would contribute to fragmentation.

View 9 Replies View Related

SQL 2012 :: Clustered Index Defrag Not Working

Apr 15, 2015

I have a clustered index which shows as having a fragmentation level of 66% according to sys.dm_db_index_ physical_ stats.avg_ fragmentation_ in_ percent.

But no matter what I try the fragmentation level doesn't budge. And yes I'm updating the statistics after each attempt.Its not a huge issue the table only has 348 records. I'm testing a fixing fragmentation maintenance script. In Ironing out the syntax of my script I've fixed the fragmentation of indexes of over 65 % anyway..I've come across and index that I can't defragment. I've tried


I've tried


I've tried


The command complete successfully yet the avg_fragmentation_in_percent doesn't change. The table also has a nonclustered index. I've gleaned through all the statements of 'this will have no effect if' but so far I've not spotted a reason why this index won't defrag.The script has been modified to remove schema and database names for the forum.

dbcc showcontig('GRIDINFO','IDX_GRIDINFO1') with tableresults , all_levels
sp_helpindex 'GRIDINFO'


View 3 Replies View Related

SQL 2012 :: Registered Servers And Clustered Environment

Sep 10, 2015

Is it possible to use registered servers feature in clustered environment and with different versions of Sql server like 2012 & 2014.

View 0 Replies View Related

SQL 2012 :: 5 Columns In Table - Clustered Index Scan

Mar 28, 2014

I have a table with clustered index on that. I have only 5 columns in that table. Execution plan is showing that Index scan occurred. What are the cause of the Index scan how can we change that to index seek?

I am giving that kind of similar query below

SELECT @ProductID= ProductID FROM Product WITH (NOLOCK) WHERE SalesID= '@salesId' and Product = 'Clothes '

View 7 Replies View Related

SQL 2012 :: Create Clustered Index On A Very Large Table (500 GB)

May 7, 2014

I need to create a Clustered Index (CI) on a very large SQL Server 2012 database table. This table has about approximately 10 billion rows, 500 GB in size. The job ran for about 20 hours into it and then fails with error: "Out of disk space in tempdb". My tempDB size is 1.8TB, but yet it's still not enough.

Here is my script:

ON TableName(Column1,Column2)
ON sh_WeekDT(Day_DT)

View 9 Replies View Related

SQL 2012 :: Moving TempDB To Local Non-clustered Drive

Sep 11, 2014

We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.

Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.

The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.

View 4 Replies View Related

SQL 2012 :: Failover Clustered Instance With Availability Groups

Oct 22, 2014

Approach 1:

Prod - shared storage between server 1 and 2
Server1: clustered SQL instance with availability group as primary
Server2: Passive server for clustered instance of PROD

DR - shared storage between server 1 and 2
Server1: Clustered SQL instance with availability group as replica
Server2: Passive server for clustered instance of DR

Approach 2: Using replicated SAN
Prod -
Server 1: Standalone instance with availability group as Primary
Server 2:Standalone instance with availability group as replica

DR -
Server 1: Offline until Disk group 1 (Prod server 1) has been broken and brought online at DR
Server 2: Offline until Disk group 2 (Prod server 2) has been broken and brought online at DR

Both these approaches will work wont they? I have only built and played with normal availability groups across servers, not mixing it with clustered instance replicated SAN

View 4 Replies View Related

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