• RELEVANCY SCORE 4.25

    DB:4.25:Non Clustered Unique Index Creation Failing Due To Duplicate Key jz




    I am a DBA and my rebuild index job has failed with the error that The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name and index name. The non clustered unique index is created on two columns and
    i see that these two columns have a duplicate value. My question is that how was the duplicate data inserted in to the table when it already had anon clustered unique index in place on these columns.
    Please help me to understand this concept.

    DB:4.25:Non Clustered Unique Index Creation Failing Due To Duplicate Key jz

    . So how will I resolve the issue. Is there any way other than deleting one of these rows?

    There is no other way. You need to delete the duplicates:
    http://www.sqlusa.com/bestpractices/eliminateduplicates/

    Kalman Toth Database OLAP Architect
    SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

  • RELEVANCY SCORE 4.05

    DB:4.05:Accessing Base Tables While Unique Clustered Index Creation On A Index View km




    While creating the unique clustered index on a index view, how will I be able to access the base tables in that view(During the process of index creation)??
    Tried firing a parallel query during the index creation,but it gets timedout and gets executed only after the creation of Unique Clustered Index on the view is finshed.
    Thanks.

  • RELEVANCY SCORE 3.65

    DB:3.65:How To Automate A Job Which Create A Unique Clustered Index On #Temp as




    Iwant to automate a job which create a UNIQUE CLUSTERED INDEX on #temp
    CREATE UNIQUE CLUSTERED INDEX temp_record_idx ON #temp (ID)
    - for example the #temp table has
    ID FName LName
    --------------------
    1 aaa bbb
    2 ccc ddd
    ... ... ...

    a - I want check if there is duplicate ID first
    b - If there is no duplicate ID then CREATE UNIQUE CLUSTERED INDEX temp_record_idx ON #temp (ID)
    c - else if there is duplicate ID column then Remove the records
    d - then, CREATE UNIQUE CLUSTERED INDEX temp_record_idx ON #temp (ID)

    Thanks,
    Alex

    DB:3.65:How To Automate A Job Which Create A Unique Clustered Index On #Temp as


    How about to update another table based on CTE duplicate records .

    I think I would need an example that illustrates what you have in mind. The best if you can post:
    o CREATE TABLE statements for your tables.
    o INSERT statements with sample data.
    o The desired result given the sample.
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • RELEVANCY SCORE 3.64

    DB:3.64:Indexes No-Clustered 11


    Pessoal,
    Estou querendo separar indexes no-clustered em filegroup diferentes.
    Algum site que falar disso?
    Posso colocar qualquer index no-clustered (unique, etc...) em outro filegroup?
     
    Abs.
     
     

    DB:3.64:Indexes No-Clustered 11

    da uma olhada em:
     
    http://www.forumweb.com.br/artigos/artigos.php?action=fileid=392
     
    tem alguns exemplos.;
     
    abs.

  • RELEVANCY SCORE 3.51

    DB:3.51:Code First Automatic Migrations In Sql Azure - Tables Without A Clustered Index Are Not Supported zx



    I can't seem to get my Code-First Migration to successfully create tables in my SQL Azure database.

    It keeps complaining about SQL Azure's lack of support for tables without clustered indexes.

    public partial class IUnityDbContext : DbContext
    {
    public IUnityDbContext()
    : base(Name=IUnityDbContext)
    {
    Database.SetInitializer(new MigrateDatabaseToLatestVersionIUnityDbContext, Configuration());

    }

    public DbSetUser Users { get; set; }

    public virtual void Commit()
    {
    base.SaveChanges();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    modelBuilder.Configurations.AddFromAssembly(typeof (IUnityDbContext).Assembly);

    base.OnModelCreating(modelBuilder);
    }
    }
    In Package Manager I Enable-Migrations -EnableAutomaticMigrations then Add-Migration Initial. If I test on non Azure database the database and tables are successfully created and/or migrated when my application instantiate my DbContext.
    However, when I change the connection string to my Azure db I get

    Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

    The weird part is my migration DOES create a Primary Key clustered Index. So when I try to add a second one:

    CreateTable(
    dbo.Strategies,
    c = new
    {
    StrategyId = c.Int(nullable: false, identity: true),
    StrategyName = c.String(maxLength: 450)
    })
    .PrimaryKey(s = s.StrategyId, clustered: true)
    .Index(s = s.StrategyId, unique: true, clustered: true);

    It says a table cannot have 2 clustered indexed. Then if I set the Primary Key to clustered: false (and leave the other clustered Index configuration), it again complains about the table not having one!

    Even more so, when I look at the created non-Azure database from my pre-test, the Index is indeed labeled (Clustered) in SSMS under the Index folder . So why is Azure complaining???

    Please advise

    DB:3.51:Code First Automatic Migrations In Sql Azure - Tables Without A Clustered Index Are Not Supported zx

    Ok I found the problem after checking out the generated Sql using Update-Database -Verbose. Turns out to be a bug in EF6- Alpha 3 where the __MigrationHistory table is created with a NONCLUSTERED primary key. I guess I shouldve mentioned I was using an
    alpha, it just slipped my mind.

    The workaround can be found here :

    http://stackoverflow.com/questions/15248037/error-deploying-to-sql-azure-using-ef-6-alpha3-code-first-and-migrations-creatin

  • RELEVANCY SCORE 3.40

    DB:3.40:How To View Indexes c8


    I am trying to run an insert query shown below. When I run it I'm getting this error: Cannot insert duplicate key row in object 'dbo.tbl_reg_BlindQCID' with unique index 'IX_tbl_data_BlindQCID'. Can anyone help me with this? Am
    I getting this error because I'm inserting into tbl_reg_BlindQCID and using the sample table in the select statement?
    There are 3 indexes sent for this table:
    IX_tbl_data_BlindQCID (Clustered)
    IX_tbl_data_BlindQCID_1 (Non-Unique, Non-Clustered)
    PK_tbl_data_BlindQCID (Unique, Non-Clustered)

    insert into tbl_reg_BlindQCID
    (bintBlindQCPoolID, vchStudyRefID, vchBlindQCID,
    chrInsertUserID, dtmInsertDatetime, chrUpdateUserID, dtmUpdateDatetime)
    select 2040, vchStudyRefID, vchBlindQCID,
    iqz9, GETDATE(), iqz9, GETDATE()
    from tbl_reg_BlindQCID
    where bintBlindQCPoolID = 1708

    DB:3.40:How To View Indexes c8

    Was this combination of fields unique for a reason? Before changing the index make sure you reviewed the business requirements that lead to the original index implementation.For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

  • RELEVANCY SCORE 3.37

    DB:3.37:Check Performance After Index Creation s7


    I've recreated several index on database: normal to bitmap or normal to unique. And now I need to check impact of this changes to my database. May I measure it somehow?

    DB:3.37:Check Performance After Index Creation s7

    Firstly read, then implement statspack : http://www.akadia.com/services/ora_statspack_survival_guide.html

    After this also read:

    ALTER INDEX my_index_i MONITORING USAGE;
    ...
    ALTER INDEX my_index_i NOMONITORING USAGE;

    http://www.oracle-base.com/articles/10g/IndexMonitoring.php

    http://www.remote-dba.net/oracle_10g_tuning/t_oracle_index_usage_tracking.htm

  • RELEVANCY SCORE 3.28

    DB:3.28:Fillfactor For Non-Clustered Index pz


    Hello all,
    Fillfactor determines how much of leaflevel of an index will be filled with data upon creation. Since non-clustered index does not have datapages in leaf level, how doesit work for such index?
    Thanks.

    DB:3.28:Fillfactor For Non-Clustered Index pz

    Hi,

    Non-clustered indexes don't have data pages at the leaf-level but they do store redundant data of the [indexed column Clustered Index or RowID in case of heap].
    You can look at this info using the system tables and Kimberly Tripp wrote a neat script to look what's stored at the leaf level of the indexes.
    http://www.sqlskills.com/BLOGS/KIMBERLY/category/sp_helpindex-rewrites.aspx
    Fillfactor is still applicable to NC indexes and you should choose the number wisely and monitor tweak appropriately.

    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/

  • RELEVANCY SCORE 3.27

    DB:3.27:Sql Server - Clustered Vs Non-Clustered x7


    Hi,

    When should I use clustered index versus non-clustered index?

    Thanks in advance!

  • RELEVANCY SCORE 3.18

    DB:3.18:Unique Clustered Index On Multiple Columns sa


    I am evaluating whether to create an Indexed View for a frequent query. As I was digging into Indexed Views, I noticed that they require a Unique Clustered Index.
    For my query, there is no single unique column. But a combination of 2 columns is unique for the query. So, is it possible to create a unique clustered index out of 2 columns for a indexed view??I always think tomorrow will have more time than today. And every today seems to pass-by faster than yesterday.

    Rahul Singla | http://www.rahulsingla.com

    DB:3.18:Unique Clustered Index On Multiple Columns sa

    Just note that an indexed view may or may not affect underlying tables while you are doing DML operationsBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • RELEVANCY SCORE 3.16

    DB:3.16:Unique Key kc


    Does the creation of a unique key on a column create a unique index or any index at all.
    Please help in solving the query as it urgent.

    Regards.

  • RELEVANCY SCORE 3.15

    DB:3.15:Alter Table Add Constraint s9


    The way I typically like to add PK, FK, and Index is to use the alter table add constraint statement.
    For example, to add a unique nonclustered index to a table I use this.
    alter table [some_table]
    add constraint [some_index]
    unique nonclustered ([some_column])

    Following this syntax, how would you create a clustered non unique index?

    DB:3.15:Alter Table Add Constraint s9


    Indexes and CONSTRAINTs are not the same thing.

    Yes, not all indexes are constraints. The only indexes that are contraints are the unique indexes. I think I see why I am not allowed to use the add contraint syntax for the non unique index.... Non uniqueness is not any kind of contraint.

  • RELEVANCY SCORE 3.15

    DB:3.15:Clustered Constraint V Clustered Index 78


    I inherited a database where the designer uses unique clustered constraints instead of indexes for  primary keys.
    Now as far as I can make out, there is no performance issue because it is clustered.  My question is, why only create a unique clustered constraint instead of a unique clustered index?
    My thinking is that a primary key should be ceated as an index rather than a constraint.  Does it matter?

    DB:3.15:Clustered Constraint V Clustered Index 78

    When you create a primary key, you have no option - you get a primary key constraint, period...behind the scenes, you end up with a unique index on the columns that make up the key and absolutely no null values are allowed.
    As for a unique constraint vs. unique index, it's not to say that one is any better than the other, they both provide very, very, very similar functionality - If you create a unique constraint, you end up with a unique index behind the scenes.
    Differences between constraints vs. indexes:
    The columns that make up the unique key can allow nulls but not for more than one complete key.
    A unique key can be referenced by a foreign key constraint and a column which has only a unique index cannot be referenced.
    Constraints are checked before indexes and this can lead to a large multi-row insert/select or update to fail before modification.
    There's probably some more as well...

  • RELEVANCY SCORE 3.14

    DB:3.14:Difference Between Primary Key With Clustered Index And Primary Key With Nonclustered Index. az


    Hi,
    I have created two tables. table one has the following fields,
    Id - unique clustered index.
    table two has the following fields,
    Tid - unique clustered index
    Id - foreign key of table one(id).
    Now i have created primary key for the table one column 'id'. It's created as nonclustered, unique, primary key located on PRIMARY. Primary key create clustered index default. since unique clustered index existed in table one, it has created
    Nonclustered primary key.
    My Question is, What is the difference between clustered, unique, primary key and nonclustered, unique, primary key? Is there any performance impact between these?
    Pleaseassist me to understand this. Thanks in advance.
    Thanks,
    SathyJay.

    DB:3.14:Difference Between Primary Key With Clustered Index And Primary Key With Nonclustered Index. az


    My Question is, What is the difference between clustered, unique, primary key and nonclustered, unique, primary key? Is there any performance impact between these?

    Both guarantee that primary key values are unique and not null. The difference is in the storage architecture. Like all clustered indexes, the leaf nodes of a clustered primary key b-tree index are the actual data pages.Once a row
    is located using the clustered index, other in-row non-key columns are readilyavailable in the same row.These other columnscan be returned ifrequested withoutreading the data row separately. Also, the clustered index
    key is automatically included in all non-clustered indexes as the row locator sonon-clustered indexeswill cover queries that need only the non-clustered index key and the clustered index key.

    With a non-clustered primary key index (assuming there is also a clustered index on the table), the leaf-nodes contain only the primary key andthe clustered index key (as a row locator). Once a row is located by the primary key index, a separate
    read of the data row will be required to return columns other than the primary key, clustered index key (and other included columns).
    Using your example, let's say these tables are OrderHeader (OrderID primary key)and OrderDetail (OrderDetailID primary key) and foreign key OrderID. If you queries often join these 2 tables on OrderID, it might be best to create a clustered index
    on OrderDetail OrderIdbecause this will physically store OrderDetail with the same OrderIDtogether. Alternatively, you could create a composite clustered primary key on OrderDetail with OrderID and OrderDetailID to serve the same purpose.
    See the AdventureWorks2012 sample database for an example of this approach.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

  • RELEVANCY SCORE 3.13

    DB:3.13:Indexes??? za


    --New
    ALTER TABLE dbo.EventTracking
    ADD CONSTRAINT XPKtblEventTracking PRIMARY KEY CLUSTERED ([Object_ID]);
    CREATE UNIQUE INDEX unci_tblEventTracking ON dbo.tblEventTracking ( eventDate, eventID, VLU_ID );
    CREATE NONCLUSTERED INDEX IE1tblEventTracking ON dbo.tblEventTracking ( VEHICLE_ID, Account_ID );
    CREATE NONCLUSTERED INDEX IE2tblEventTracking ON dbo.tblEventTracking ( VEHICLE_NAME, DEG_LONGITUDE, DEG_LATITUDE );
    CREATE NONCLUSTERED INDEX IE3tblEventTracking ON dbo.tblEventTracking (SQLSystemDate );
    GO

    -- Current indexes on a table EventTracking
    -------------------------------------------------- ------------------------------------------------------- --------------------------------------------------
    --1 AK1_Tracking_eventID_VluID_eventDate nonclustered, unique, unique key located on PRIMARY eventID, VLU_ID, eventDate
    --2 idx_SQLSystemDate nonclustered located on PRIMARY SQLSystemDate(-)
    --3 idx_tblEventTracking_ETLCovering nonclustered located on PRIMARY VEHICLE_ID, eventDate, Account_ID
    --4 D idx_Tracking_EventDate nonclustered located on PRIMARY eventDate
    --5 D idx_Tracking_EventID nonclustered located on PRIMARY eventID
    --6 D idx_Tracking_Vehicle_ID nonclustered located on PRIMARY VEHICLE_ID
    --7 idx_Tracking_Vehicle_Name nonclustered located on PRIMARY VEHICLE_NAME, eventDate, DEG_LONGITUDE, DEG_LATITUDE
    --8 PK__tblEventTracking__7211DF33 clustered, unique, primary key located on PRIMARY Object_ID
    --9 D XIE_eventDate_VehicleID_tblEventTracking nonclustered located on PRIMARY eventDate, VEHICLE_ID
    --10 D XIE7tblEventTracking nonclustered located on PRIMARY eventID, VLU_ID

    DB:3.13:Indexes??? za

    Whatare the reasons for the compound indexes?
    Optimization: http://www.sqlusa.com/articles/query-optimization/Kalman Toth Database OLAP Architect
    SQL Server 2014 Design Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

  • RELEVANCY SCORE 3.12

    DB:3.12:Does Non Clustered Index Help In Fast Retrieval Of Data mx


    Does Non unique NonClustered index on a column ( having duplicates) help in fast retrieval of data?

    DB:3.12:Does Non Clustered Index Help In Fast Retrieval Of Data mx

    Absotively!George that is an awesome word :)The short answer to the question is yes an non unique index may help in faster data retrieval. I say "may" because the index will not be of use if it is not used in the query predicate or the selectivity is too low. When the index selectivity is low, the optimizer may find it faster to scan than seek half the table. For example, say that you have an Employees table with a column called IsSalary, where a value of 1 is salary paidand a value of 0 is not salary. If you want to query for all employees who are paid a salary, the optimizer is likely to scan the table because the uniquness of the predicate column is very low (obviously dependent upon data distribution). Ultimately, you want to create indexes on columns with data that is relatively unique, it does not have to be completely unique, but it should be a unique enough that a small percentage of the table's rows will be returned.selectivity calculation:
    /*===========================================================
    DETERMINE SELECTIVITY FOR COMPOSITE INDEX
    ===========================================================*/
    DECLARE @UniqueValues INT

    SELECT DISTINCT State, County
    FROM MyTable;

    SET @UniqueValues = @@ROWCOUNT;

    SELECT (1.0* @UniqueValues) / count(*) AS [SELECTIVITY]
    FROM MyTable;

    /*===========================================================
    DETERMINE SELECTIVITY FOR NON-COMPOSITE INDEX
    ===========================================================*/
    SELECT (1.0 * COUNT(DISTINCT Primary_First_Name)) / count(*)
    FROM MyTable;

    http://jahaines.blogspot.com/

  • RELEVANCY SCORE 3.11

    DB:3.11:Index Problem d9


    Ive created an SSIS package and the very last task is to recreate the indexes on a table, but the task is failing with this error The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tblCompany' and the index name 'IX_tblCompany_CompanyName'. The duplicate key value is (NULL, 96260). The index 'IX_tblCompany_CompanyName' on company name is non-unique and non-clustered, but there are many rows where the company name is null, so could this be causing the problem ? I cant see how i can have a duplicate key (NULL, 96260) If an index is non-unique, would null values cause this ?

    DB:3.11:Index Problem d9

    the SSIS package executes a stored procedure that creates indexes on several tables, the first table is the company table ALTER INDEX ALL ON dbo.tblCompany REBUILD WITH (STATISTICS_NORECOMPUTE = OFF) There is only one index on this table that is built from 2 columns and that is set to be non unique so it shouldnt fail with the reported error, should it ?

  • RELEVANCY SCORE 3.07

    DB:3.07:Not Able To Drop Unique Non Clustered Index zp


    I am not able to drop Unique Non Clustered Index from table. Although I have tried after droping all constraint (Primary Key Foreign Key) also but again same below error is coming. I

    An explicit DROP INDEX is not allowed on index ''DBName'.TableName. It is being used for FOREIGN KEY constraint enforcement.

    DB:3.07:Not Able To Drop Unique Non Clustered Index zp

    Hi Chris, Thanks alot ...
    From your above foreign key script I could find out foreign key of that table after droping that foreign key I could drop that Index....

  • RELEVANCY SCORE 3.07

    DB:3.07:Heaps Of Things 7d


     
    Hi all,
    a question that has been in my mind for a while:
     
    A HEAP is a table that has no clustered index.
     
    Now consider a table that has a unique clustered index (primary key) on an identity column ?
     
    For example:
     
    create table Test
    (
        TestId int identity(1,1) not null,
        ...,
        constraint PK_Test primary key clustered (TestId)
    )
     
    Is there a recognised term for such a table ?
     
    Thanks
    Graham
     

    DB:3.07:Heaps Of Things 7d

    I don't think that there is something specific to that.  To me that is just a normal table in SQL Server.  It is how 98% of the tables I have ever seen are built.  If you find another answer post it back here though.
     

  • RELEVANCY SCORE 3.06

    DB:3.06:Indexes 9m


    Hello,
    I the following T-SQL to create a table and an index:

    create table dbo.Boxes
    (
    Id int identity not null
    constraint Boxes_Id_PK primary key clustered (Id),
    Name nvarchar(80) not null
    constraint Boxes_Name_U unique,
    );

    create index Boxes_Name_IDX on Boxes (Name)

    DB:3.06:Indexes 9m

    As noted above:
    1. PRIMARY Key constraint creates a clustered (default) UNIQUE index.
    2. You can only have one clustered index per table
    3. UNIQUE Key constraint creates a nonclustered UNIQUE index.
    There is no need to create additonal indexes.Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com

  • RELEVANCY SCORE 3.06

    DB:3.06:No Index On Primary Key Column a3


    In SQL Server 2000 creation of Primary Key does not create Clustered Index upon it automatically, but in 2005 it does.
    Is there any way I can create a Primary key with no Index while creating a table in SQL Server 2005?

  • RELEVANCY SCORE 3.05

    DB:3.05:Indexes Are Not Getting Used On Indexed View zf


    Hi,
    I havea veiw that is heavly used by the application with one unique cluster index and one non-clustered index.
    To increase performance i have created the non-clustered index as i mention above.
    But the actual problem is that my non-clustered indexis not geting used in index view,when i see the query plan it reference the actual table.
    Select * from ViewName -----It used the above unique clustered index.
    select columnName from ViewName --It start using the actual tables name.
    Can some one help me on it.
    This above condition accour in my Production database.

    I havetest database with same schema and itused the non-clustered index as expected.
    I have compared both the test and prod database,all schema,index etc are same...but differnet query plan.

    Regards Madan Agrawal Please vote if you find my post valuable for you.

    DB:3.05:Indexes Are Not Getting Used On Indexed View zf

    Madan,
    The two databases could have different execution plans if the optimizer sees differences between the data. If your development database doesn't have as much data, or the statistics have not been updated in one of the environments, then it's very reasonable
    that the execution plans would differ. If the data sets are the same and the schemas are the same, make sure the statistics are updated and recompile the objects and they should match.
    Eric IsaacsEric Isaacs

  • RELEVANCY SCORE 3.03

    DB:3.03:Adding Non-Clustered, Non-Unique Index To Heap 3j


    If I have a large(ish) heap table, and add a non-clustered and non-unique index to it, does it have to rewrite the entire heap or not? If it does, would it be advantageous to make it a clustered non-unique index?
    I know the non-unique clustered index introduces a uniquifier, but suddenly realized I was unsure how this works for heap tables with only non-clustered indexes.
    Thanks,
    Josh

    DB:3.03:Adding Non-Clustered, Non-Unique Index To Heap 3j


    I'm not sure I understand. If you create a clustered index, unique or not will change it from a heap to a table with a clustered index on it. If you create a non-clustered index on a heap, it doesn't change the heap. The heap continues to be a heap. Am I
    missing something in the question?

    Just making sure I'm not misunderstanding.
    I'm happy if the heap remains a heap and is not rewritten.
    But I was wondering how a non-clustered index could even work against an unalterered heap, and suddenly wondered if it actually had to rewrite the heap to add a uniquifier to each row, so it would remain a heap to me, but would have a little more structure
    hidden away. Especially since a non-unique clustered index seems to feel the need to actually add such a uniquifier.
    Thanks,
    Josh

  • RELEVANCY SCORE 3.03

    DB:3.03:Stop Sql Server Doing An Unneccessary Clustered Index Insert On A View fk


    HiI have a large table (between 74 and 88million rows) which is the middle table (Table B) of a many to many relationship. I have a view that builds a unified picture of the data contained in the these tables. The view has a clustered index defined against it. Table A on the left hand side of my large table is the core table in my database. Table C is the table that contains the normalised data items. When I insert a new record into table C the task takes a long time to complete (5mins on a very very good server). This is because SQL server rebuilds part of the clustered index on the view (I can see a clustered index insert in the actual execution plan). This is despite the new row in Table C not being referenced in Table B. Inserting rows into Table A and Table B takes milliseconds as I would expect. The database is not partitioned.
    This is an annoymised version of the tables creation script. I've left the columns that aren't used in so you can see the full structure of the tables.
    CREATE TABLE [dbo].[TableA] ([TableAId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableAIdGUID] [uniqueidentifier] NOT NULL,[ImportantId] [INT] NOT NULL ,[DateCreated] [datetime] NOT NULL ,[OtherId2] [int] NOT NULL ,[TableATypeId] [int] NOT NULL ,[Active] [bit] NOT NULL ,[AuditUser] [NVARCHAR] (20) NULL , [AuditTime] [datetime] NULL )GOCREATE TABLE [dbo].[TableB] ([TableBId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableBGUID] [uniqueidentifier] NOT NULL,[TableAId] [INT] NOT NULL ,[TableCId] [INT] NOT NULL ,[Tag] [NVARCHAR] (50) NULL , [Order] [tinyint] NOT NULL ,[DateCreated] [datetime] NOT NULL ,[Date1] [datetime] NULL ,[Date2] [datetime] NULL ,[LastUpdated] [datetime] NOT NULL ,[Active] [bit] NOT NULL ,[AuditUser] [NVARCHAR] (20) NULL , [AuditTime] [datetime] NULL)GOCREATE TABLE [dbo].[TableC] ([TableCId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableCIdGUID] [uniqueidentifier] NOT NULL,[TableCTypeId] [int] NOT NULL ,[TableCValue] [NVARCHAR] (255) NOT NULL , [Frequency] [float] NOT NULL DEFAULT 1,[TableCValue2] [NVARCHAR] (255) NULL ,[AuditUser] [NVARCHAR] (20) NULL ,[AuditTime] [datetime] NULL )GOCREATE VIEW [dbo].[vwTables]WITH SCHEMABINDING ,ENCRYPTIONASSELECT dbo.[TableB].TableBId, dbo.[TableB].TableAId, dbo.TableA.ImportantId,dbo.TableC.TableCValue,dbo.TableC.TableCValue2, dbo.TableC.TableCTypeId,dbo.TableA.TableATypeIdFROM dbo.[TableB] INNER JOINdbo.TableC ON dbo.[TableB].TableCId = dbo.TableC.TableCId INNER JOINdbo.TableA ON dbo.[TableB].TableAId = dbo.TableA.TableAIdWHERE (dbo.[TableB].Active = CAST(1 AS BIT)) AND (dbo.TableC.TableCValue'') and (dbo.TableC.TableCValue is not null)GOCREATE UNIQUE CLUSTERED INDEX [IX_vwTables] ON [dbo].[vwTables] ([TableCValue] ASC,[TableCTypeId] ASC,[TableBId] ASC,[ImportantId] ASC)GOALTER TABLE [dbo].[TableB] ADD CONSTRAINT [FK_TableB_TableC] FOREIGN KEY ([TableCId]) REFERENCES [dbo].[TableC] ([TableCId]),CONSTRAINT [FK_TableB_TableA] FOREIGN KEY ([TableAId]) REFERENCES [dbo].[TableA] ([TableAId])GOCREATE NONCLUSTERED INDEX [IX_TableA_Nonclustered] ON [dbo].[TableA] ([ImportantId] ASC,[TableAId] ASC,[TableATypeId] ASC,[Active] ASC)GOCREATE NONCLUSTERED INDEX [IX_TableA_OtherId2] ON [dbo].[TableA] ([AuditTime] ASC,[OtherId2] ASC)GOCREATE NONCLUSTERED INDEX [IX_TableB_NonClustered] ON [dbo].[TableB] ([TableAId] ASC,[TableBId] ASC,[Active] ASC)GOCREATE NONCLUSTERED INDEX [IX_EntityAttributes_NonClustered_2] ON [dbo].[TableB] ([TableBId] ASC,[TableAId] ASC,[TableCId] ASC,[Tag] ASC,[Active] ASC) GO
    Does anyone know why SQL Server decides it needs to rebuild part of the index when I insert into TableC and not TableA or TableB and how I can prevent it from doing so.Thanks in advanced.

    DB:3.03:Stop Sql Server Doing An Unneccessary Clustered Index Insert On A View fk

    HiI have a large table (between 74 and 88million rows) which is the middle table (Table B) of a many to many relationship. I have a view that builds a unified picture of the data contained in the these tables. The view has a clustered index defined against it. Table A on the left hand side of my large table is the core table in my database. Table C is the table that contains the normalised data items. When I insert a new record into table C the task takes a long time to complete (5mins on a very very good server). This is because SQL server rebuilds part of the clustered index on the view (I can see a clustered index insert in the actual execution plan). This is despite the new row in Table C not being referenced in Table B. Inserting rows into Table A and Table B takes milliseconds as I would expect. The database is not partitioned.
    This is an annoymised version of the tables creation script. I've left the columns that aren't used in so you can see the full structure of the tables.
    CREATE TABLE [dbo].[TableA] ([TableAId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableAIdGUID] [uniqueidentifier] NOT NULL,[ImportantId] [INT] NOT NULL ,[DateCreated] [datetime] NOT NULL ,[OtherId2] [int] NOT NULL ,[TableATypeId] [int] NOT NULL ,[Active] [bit] NOT NULL ,[AuditUser] [NVARCHAR] (20) NULL , [AuditTime] [datetime] NULL )GOCREATE TABLE [dbo].[TableB] ([TableBId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableBGUID] [uniqueidentifier] NOT NULL,[TableAId] [INT] NOT NULL ,[TableCId] [INT] NOT NULL ,[Tag] [NVARCHAR] (50) NULL , [Order] [tinyint] NOT NULL ,[DateCreated] [datetime] NOT NULL ,[Date1] [datetime] NULL ,[Date2] [datetime] NULL ,[LastUpdated] [datetime] NOT NULL ,[Active] [bit] NOT NULL ,[AuditUser] [NVARCHAR] (20) NULL , [AuditTime] [datetime] NULL)GOCREATE TABLE [dbo].[TableC] ([TableCId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableCIdGUID] [uniqueidentifier] NOT NULL,[TableCTypeId] [int] NOT NULL ,[TableCValue] [NVARCHAR] (255) NOT NULL , [Frequency] [float] NOT NULL DEFAULT 1,[TableCValue2] [NVARCHAR] (255) NULL ,[AuditUser] [NVARCHAR] (20) NULL ,[AuditTime] [datetime] NULL )GOCREATE VIEW [dbo].[vwTables]WITH SCHEMABINDING ,ENCRYPTIONASSELECT dbo.[TableB].TableBId, dbo.[TableB].TableAId, dbo.TableA.ImportantId,dbo.TableC.TableCValue,dbo.TableC.TableCValue2, dbo.TableC.TableCTypeId,dbo.TableA.TableATypeIdFROM dbo.[TableB] INNER JOINdbo.TableC ON dbo.[TableB].TableCId = dbo.TableC.TableCId INNER JOINdbo.TableA ON dbo.[TableB].TableAId = dbo.TableA.TableAIdWHERE (dbo.[TableB].Active = CAST(1 AS BIT)) AND (dbo.TableC.TableCValue'') and (dbo.TableC.TableCValue is not null)GOCREATE UNIQUE CLUSTERED INDEX [IX_vwTables] ON [dbo].[vwTables] ([TableCValue] ASC,[TableCTypeId] ASC,[TableBId] ASC,[ImportantId] ASC)GOALTER TABLE [dbo].[TableB] ADD CONSTRAINT [FK_TableB_TableC] FOREIGN KEY ([TableCId]) REFERENCES [dbo].[TableC] ([TableCId]),CONSTRAINT [FK_TableB_TableA] FOREIGN KEY ([TableAId]) REFERENCES [dbo].[TableA] ([TableAId])GOCREATE NONCLUSTERED INDEX [IX_TableA_Nonclustered] ON [dbo].[TableA] ([ImportantId] ASC,[TableAId] ASC,[TableATypeId] ASC,[Active] ASC)GOCREATE NONCLUSTERED INDEX [IX_TableA_OtherId2] ON [dbo].[TableA] ([AuditTime] ASC,[OtherId2] ASC)GOCREATE NONCLUSTERED INDEX [IX_TableB_NonClustered] ON [dbo].[TableB] ([TableAId] ASC,[TableBId] ASC,[Active] ASC)GOCREATE NONCLUSTERED INDEX [IX_EntityAttributes_NonClustered_2] ON [dbo].[TableB] ([TableBId] ASC,[TableAId] ASC,[TableCId] ASC,[Tag] ASC,[Active] ASC) GO
    Does anyone know why SQL Server decides it needs to rebuild part of the index when I insert into TableC and not TableA or TableB and how I can prevent it from doing so.Thanks in advanced.

  • RELEVANCY SCORE 3.02

    DB:3.02:Index 3d


    1)difference between unique clustered index and clustered index
    2)difference between composite index and non clustered index

    DB:3.02:Index 3d

    Hi,Check these links1. http://www.sql-server-performance.com/articles/per/index_data_structures_p2.aspx   http://blog.sqlauthority.com/2009/02/04/sql-server-observation-effect-of-clustered-index-over-nonclustered-index/Rajesh Jonnalagadda
    http://www.ggktech.com

  • RELEVANCY SCORE 3.02

    DB:3.02:Can You Please Differentiate Blw Unique Clustered Index And Non Unique Clustered Index. 8z


    Can you please differentiate b/w the following:

    Unique Clustered Index
    Non Unique Clustered Index
    and explain what are these?
    Thanks,
    Nagaraju K.

    DB:3.02:Can You Please Differentiate Blw Unique Clustered Index And Non Unique Clustered Index. 8z

    UNIQUE CLUSTERED INDEX is typically used for PRIMARY or UNIQUE KEYs. It is helpful with range searches among others.
    NON-UNIQUE CLUSTERED INDEX
    must have a very limited application role, I never used it.
    Related article:
    http://www.sqlusa.com/bestpractices2008/allindexes/
    Kimberly Tripp on the topic:
    Let's start with the key things that I look for in a clustering key:
    * Unique

    * Narrow

    * Static

    Why Unique? A clustering key should be unique because a clustering key (when one exists) is used as the lookup key from all non-clustered indexes. Take for
    example an index in the back of a book - if you need to find the data that an index entry points to - that entry (the index entry) must be unique otherwise, which index entry would be the one you're looking for? So, when you create the clustered index - it
    must be unique. But, SQL Server doesn't require that your clustering key is created on a unique column. You can create it on any column(s) you'd like. Internally, if the clustering key is not unique then SQL Server will “uniquify” it by adding a 4-byte integer
    to the data. So if the clustered index is created on something which is not unique then not only is there additional overhead at index creation, there's wasted disk space, additional costs on INSERTs and UPDATEs, and in SQL Server 2000, there's an added cost
    on a clustereD index rebuild (which because of the poor choice for the clustering key is now more likely).
    http://stackoverflow.com/questions/4332982/do-clustered-indexes-have-to-be-unique

    Kalman Toth SQL SERVER BI TRAINING

  • RELEVANCY SCORE 3.00

    DB:3.00:Basic Question About Clustered Indexes 91


    I assume that the modifier clustered basically means sort and group like values together. Is that right? If so, then why is it good to have a clustered index on a column that is constrained unique? If a colulmn is defined as a primary key, is it not already unique and sorted simply because it is a primary key? I dont understand why the docs recommend using a clustered index on a colulmn that is unique or mostly unique? Maybe I'm misunderstanding the use of the term clustered??

    DB:3.00:Basic Question About Clustered Indexes 91

    Wow... good question!  The answer is... it depends.  :) See, SQL Server will add a 4-byte key to your clustered key to make it unique.  But if your composite clustered index would create a wide clustered index, you may be better off taking the 4-byte hit.  This is because your new (possibly wide) composite clustered index will be included as a key in each of your non-clustered indexes, which could end up having a greater performance impact. Can you post a basic overview of your table's schema and your query?  And how many times will your query be run each day, and is it a time-sensitive query?  This information will allow us to better advise you.  :)Michelle Ufford | SQLFool.com | Please mark solved if I've answered your question :)

  • RELEVANCY SCORE 2.99

    DB:2.99:Trouble Creating Non Clustered Index k9


    Hi,

    I have a table MainTable on which I already have one CLUSTERED INDEX built upon when data is loaded into this table via a DTS package everytime the DTS is run.
    The DTS follows the below steps...
    - Drops table
    - Creats table again
    - Data gets loaded into table
    - Clustered Index is created on the table

    Now I want to create a NONCLUSTERED INDEX on this table, but everytime i create it using Query Analyzer, the Index gets dropped the next time DTS package is called.
    If I add the NONCLUSTERED INDEX in the DTS right after the CLUSTERED INDEX creation, will it harm the DTS package performance ??

    Thanks.

    DB:2.99:Trouble Creating Non Clustered Index k9

    Hello,
    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    COMMIT
    BEGIN TRANSACTION
    CREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable
    (
    Item,
    DateTime1
    ) ON [PRIMARY]
    GO
    COMMIT

    CREATE NONCLUSTERED INDEXIX_TestTable2
    ON dbo.TestTable
    (
    Id,
    Location,UniqueNum, DateTime2
    ) ON [PRIMARY]

    GO

    --Just a question, Why you are using begin Transaction twice?
    Maybe only one begin transaction is good in this case. If you are using two of them , then one commit is not going to close the other.
    Please check this.
    Thanks

  • RELEVANCY SCORE 2.98

    DB:2.98:Index And Clustered Index On The Primary Key 83


    Accidental DBA here - I've inherited a SQL database and app and I'm trying to go through and learn it and find possible problems...
    There are a lot of indexes created with the _dta prefix - so these were created by the tuning adviser I guess...
    anyway I find a bunch of duplicate indexes and also indexes that are also clustered and non clustered for the primary key for certain tables - so I look at one table for instance and it has an ID column, that's int and autoincrementing, there's a unique
    clustered index for it and also a non clustered index for it
    should I delete the non clustered index? (again this index is prefixed with the _dta so it was created by the performancetuning )

    Check out my CNC (and more) projects at http://www.backyard-workshop.com

    DB:2.98:Index And Clustered Index On The Primary Key 83

    Hello Uri,
    Why not an engineer created a non-clustered index with one column that used for a particular query to avoid the usage of CLUSTERED index which is huge for a huge table with multiple columns? May be a situation where non-clustered index has
    been created to avoid a clustered scan. At the time, it wont be a good idea to DROP the non clustered index just because its a DUPLICATE index. And I think its not a duplicate index also.
    Please share your thoughts.

  • RELEVANCY SCORE 2.98

    DB:2.98:Composite Clustered Index md


    SQL 2000. We've got a non-unique composite clustered index and I'm struggling to see the point of it:
    CREATE CLUSTERED INDEX cidx_one ON dbo.table_one (id ASC,col2 ASC,col3 ASC,col4 ASC) WITH FILLFACTOR = 90 ON [PRIMARY]
    id is an identity column so the index could be set as unique. col2-col4 are INT and VARCHAR columns. I realise the clustered index is made internally unique in the background with a uniquifier, but it is not declared unique.
    I cannot see the point of having composite clustered indexes like this. I can see this may help queries that sort on all four columns in that order, but that's it.
    Being as the clustered index is the data, I can't see any point in having additional columns in it, as the first column is already unique.
    Additionally, non-clustered indexes will be bloated as they contain primary key . Is that correct?
    I'm planning to refactor the clustered index on the id alone and provider non-clustered indexes for the other columns.
    We're shortly upgrading straight to SQL 2008 so will be looking at the INCLUDE clause.
    In summary, is there any point in a non-unique composite clustered index where the first column is unique?

    DB:2.98:Composite Clustered Index md

    It will help you a lot with the decision, if you look at the query patterns which are the ones that drive index selection. Also, the pattern for update / delete operations and how the key columns will be affected.AMB

  • RELEVANCY SCORE 2.97

    DB:2.97:Reverse Index For Primary Key 8c


    Hi,

    When we create a primary key, it automatically creates an unique index on it. It there any way we can give a clause to create a reverse unique index on it?

    We can obviously at later state alter an index to change it into reverse, but how to do at creation time?

    Regards!

  • RELEVANCY SCORE 2.97

    DB:2.97:Clustered Index Should Be Always Used? cm


    hi ,
    just i want to know if Column should have Clustered Index ( in case there isalready unique non-clustered Index include another Column)??

    DB:2.97:Clustered Index Should Be Always Used? cm

    Every table must have a clustered indexJavier Villegas |
    @javier_vill |http://sql-javier-villegas.blogspot.com/
    Please click Propose As Answer if a post solves your problem or Vote As Helpful if a post has been useful to you

  • RELEVANCY SCORE 2.97

    DB:2.97:Fragmentao De Indice - (Valor No Zera) ck


    Estou com problema na hora de recriaro meu indice...os comandos so executados com sucesso porem os valores no so modificados isso normal? Segue abaixo comandos executados.
    Obrigada
    SELECT object_id, index_type_desc, avg_fragment_size_in_pages,

    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent, avg_record_size_in_bytes
    FROM sys.dm_db_index_physical_stats (7, NULL, NULL , NULL,NULL)
    WHERE avg_fragmentation_in_percent 0 AND index_id 0;

    object_id

    index_type_desc

    avg_fragment_size_in_pages

    avg_fragmentation_in_percent

    avg_page_space_used_in_percent

    avg_record_size_in_bytes

    311672158

    CLUSTERED INDEX

    1

    50

    NULL

    NULL

    375672386

    CLUSTERED INDEX

    5,555555556

    6

    NULL

    NULL

    407672500

    CLUSTERED INDEX

    18,14285714

    3,937007874

    NULL

    NULL

    439672614

    CLUSTERED INDEX

    3,6

    16,66666667

    NULL

    NULL

    Pegando o Primeiro ID que esta Fragmentado 50%

    select
    * from
    sys.objects y,
    sys.indexes p
    where
    y.object_id
    = 311672158
    and
    y.object_id
    = p.object_id

    Recriando o Index

    CREATE
    UNIQUE CLUSTERED
    INDEX XPKhistorico_acesso
    ON dbo.historico_acesso(id_historico_acesso)
    WITH
    (DROP_EXISTING
    = ON);
    GO

    O Problema quando eu executo novamente o 1 select , nada foi alterado o ndice continua com 50% fragmentado....isso normal?

  • RELEVANCY SCORE 2.96

    DB:2.96:Re: Difference Between Primary Key And Unique Key With Not Null Constraint ca


    i believe clustered indexes are created for index organized tables

    DB:2.96:Re: Difference Between Primary Key And Unique Key With Not Null Constraint ca

    Thanks all for putting your efforts,
    I really got more insights of Cluster Index,
    it cleared my confusion, and I am sure for few others too... :)

    Thanks to you,
    Nimish Garg
    3360
    Sven W.
    jeneesh

  • RELEVANCY SCORE 2.94

    DB:2.94:When To Use Clustered Index And Non Clustered Index fc


     
     
    When to Use Clustered Index and Non Clustered Index in SQL Server 2005?
    Give the Scenario's and give examples?

    DB:2.94:When To Use Clustered Index And Non Clustered Index fc

    Hi Kumar,
    Check the guidelines When to Use Clustered Index
    andNonclustered Index .

    PS.Shakeer Hussain

  • RELEVANCY SCORE 2.94

    DB:2.94:Index On View ss


    Can a non-unique clustered index be created on View ? If not then why only unique clustered index will be created on View?

    DB:2.94:Index On View ss

    The clustered index on the view must be UNIQUE and we can not have Non UNIQUE clustered index on a view. And the first index on the view must be UNIQUE clustered because - The clustered index is used to store the records physically (Materiliaze the view)- The UNIQUE is used to improve the performance of the data modifications on View or on the base table i.e. by using UNIQUE index on view SQL Server will retrieve the records faster and updates the View data.Ranjith | My Blog

  • RELEVANCY SCORE 2.93

    DB:2.93:Unique Clustered Index? ja


    If I make a clusted unique index on a column which later starts to hold Non-unique data what would happen?Mr Shaw

    DB:2.93:Unique Clustered Index? ja

    No!!! what ever is the index type you select it will not effect the results row count for the matching key.
    If duplicate Key values are there in the index column SQL Server putsin some extra effort to get those duplicate records as well for the matching key.
    Ranjith | My Blog

  • RELEVANCY SCORE 2.93

    DB:2.93:Unique Filtered Index zz


    Hi Friends

    Here is my scenario
    Employee
    (
    EmployeeID int,
    EmployeeName varchar(50),
    EmployeeCode varchar(15),
    IsDeleted bit
    )
    I created a non clustered unique filtered index as below, but it doesn't work for me. That is to say, I cant create two rows with same EmployeeCode and different IsDeleted values. I want to maintain the unique filtered index on Employee code having IsDeleted
    = 0

    CREATE UNIQUE NONCLUSTERED INDEX [U_Nix_User_EmployeeCode] ON [dbo].[Employee]
    (
    [EmployeeCode] ASC
    )
    WHERE ([IsDeleted]=(0))
    GO

    DB:2.93:Unique Filtered Index zz

    Hi Uri Dimant
    It works in my end now. That was a mistake. I had created two index on the same column by mistake, one filtered index and the other was non filtered unique indexes.

    Thanks a lot
    Asharaf
    MCTS, http://asharafaliptb.wordpress.com

  • RELEVANCY SCORE 2.92

    DB:2.92:Clustered Index Or Nonclustered Unique Index On Primary Key? 79


    On a vendor table there is a Primary Key which is an IDENTITY column (static, ever increasing) however instead of a clustered index I notice it has a Unique NonClustered index. Is there any performance that could be gained by going with Clustered?
    Or vice versa? The table is essentially a record change log that records the date/time a record changes elsewhere and the change that was made.

    Thanks,
    MishMy Google-Fu is strong!

    DB:2.92:Clustered Index Or Nonclustered Unique Index On Primary Key? 79

    Thanks. I've sent the vendor a ticket asking but to be honest I have less faith in their answers after the last few debacles with them. My Google-Fu is strong!

  • RELEVANCY SCORE 2.92

    DB:2.92:Check Non-Clustered Index Creation Date 8a


    Hi all~~ are there any method we can check the creation date of the non-clustered index ??

  • RELEVANCY SCORE 2.91

    DB:2.91:Must All Federated Tables Have A Composite Primary Key? jz


    All unique and clustered indexes on the federated table must contain the federation column. Each table can only have 1 clustered index; that is the primary key. Does it follow therefore that the primary key on federated tables must be composite?

    DB:2.91:Must All Federated Tables Have A Composite Primary Key? jz

    Composite key adds complexity ... I think we can manage that.
    I expect there will be a performance hit also. We expect to take a big performance hit moving to Azure anyway and this just layers on top of that, so we'll just have to see how we go.
    Thank you for your help Dilkush.

  • RELEVANCY SCORE 2.90

    DB:2.90:How Does Index Creation Helps Improving Performance? z1



    I heard many times for for creating index for tables and fields to improve system performance. When I check in wiki it explains indexing a field set it to store unique values therefore avoid duplicate entries but not sure how does index creation helps in this regard.

    Can anyone please explain?

    Brgds, AM

    DB:2.90:How Does Index Creation Helps Improving Performance? z1


    Hi Pradeep and Travis,

    Thank you for your sharing and explanations. I am clear with this concept now and Index creation makes more sense to me.

    Brgds, AM

  • RELEVANCY SCORE 2.89

    DB:2.89:Making A Non-Clustered Index Unique 8x


    In what circumstances should we consider adding a composite column to a non-unique clustered index in order to make the index unique, rather than letting the SQL Server database engine add an uniquefier?
    Thanks in advance.
    ~Avnip

    DB:2.89:Making A Non-Clustered Index Unique 8x

    I'm not sure I know the answer to that one. You might try running it with and without a clustered index in Profiler or try it with SET STATISTICS IO ON
    You're actually referring to a heap in this situation. If I remember correctly, you would see advantages in BULK insert situations but disadvantages in most other situations. It's probably going to depend on your situation, though (it always falls back to It depends).
    Considering this is out of my area of expertise, I would recommend you do searches on Heap and Clustered Index as my recommendations would basically be on what I read on the same search.
    Anyone else have input on this one?

  • RELEVANCY SCORE 2.87

    DB:2.87:Convert Unique, Non-Clustered Index To Clustered Index 7c


    I have a unique, non-clustered index that I would like to make clustered. Is there a simple way to do this?
    Thanks!

    DB:2.87:Convert Unique, Non-Clustered Index To Clustered Index 7c

    Yes,
    You use the regular CREATE INDEX statement for it, and simply add WITH DROP_EXISTING to it. For example
    CREATE UNIQUE CLUSTERED INDEX IX_mytable
    ON dbo.mytable (indexed_column)
    WITH DROP_EXISTING
    Since you can only have one clustered index (per table), you must drop the existing clustered index first (when applicable).
    --
    Gert-Jan

  • RELEVANCY SCORE 2.86

    DB:2.86:Difference Primary Key Unique Key? a8


    Please tell difference between Primary key Unique key, Apart from the below difference?
    -- Primary key can't have null whereas unique key can have a single null value also.
    -- Be default clustered index comes on primary key.
    ?Thanks!! http://gurunguns.wordpress.com

    DB:2.86:Difference Primary Key Unique Key? a8

    check this thread
    http://www.dotnetspider.com/resources/15382-difference-between-primary-key-unique-key.aspx
    http://www.geekinterview.com/question_details/15915Everything about SQL Server | Experience inside SQL Server
    -Mohammad Nizamuddin

  • RELEVANCY SCORE 2.85

    DB:2.85:Clustered Index A Must For All The Tables? s9



    Gurus,
    I am working at a client site and all tables in their database do not have a clustered index. Instead of clustered index all the tables have a unique nonclustered index.
    So to explain in detail they have a table called company with only one field company_name varchar(100) and it has only one record inside it representing name of the company. company_name is the part of primary key for all the remaining tables inside
    the database. So for example contact table will have a unique nonclustered index on contact_id and company_name column.
    I believe this design is wrong and every table should have a clustered index. I am planning to change all the table design to put clustered index in it.

    I have three questions.
    (1) I want the confirmation that i'm on the right track and I should definitely change the table designs to put clustered indexes on all the tables.
    (2)Going ahead with the same example for the contact table I will create a clustered index on the contact_id column only. All the queries inside the database are always refering to company_name. Tempting up on this fact I could have created clustered index
    on composite columns contact_id and company_name but I am not doing it because company_name is always one value and as far as i believe you are supposed to create index only on columns which have enough selectivity. Please confirm that My approach is
    right and I should be creating a clustered index this way.
    (3) After creating the clustered index in order to reduce the overhead of maintaining the index I was planning to get rid of unique nonclustered index. I believe that we don't need unique nonclustered index because clustered index will be taking care of
    whaterver part it was doing and there will be more overhead to maintain the not needed nonclustered index.Am i doing this right?
    Pleaset let me know.
    Thanks!

    DB:2.85:Clustered Index A Must For All The Tables? s9

    3)
    If you have NCI on selective in tems of data column, and used that column in WHERE condition that instead of doing few reads SQL Server will go for CI scan which may be expencive.... You do need to have NCI to cover some queries which are based unique
    data abd return few rows .Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • RELEVANCY SCORE 2.85

    DB:2.85:Clustered Index pp


     
    can someone plz explain the diff between a clustered and non clustered index
     
    tx
     

    DB:2.85:Clustered Index pp

    Please read this analogy and then index related stuffs from here which will help you understand better. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. The pages in each level of the index, including the data pages in the leaf level, are linked in a doubly-linked list. The leaf page of contains the data itself in a clustered index. Each table can have only one clustered index as the key values in the data rows are unique and the index is built on the unique key column (generally primary key but not always). A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. The structure of the row locator is determined on the basis of the type of storage of the data pages. If the data page is stored as a heap, a row locator becomes a pointer to a row. If the data page is stored in a clustered table the row locator is a clustered index key. Refer here for more info.- Deepak

  • RELEVANCY SCORE 2.85

    DB:2.85:Index 9z


    clustered index Contains unique values ..when creating primary key on particular columnindex will be created automatically...every non clustered index creation row id will be generated ...then it will avoid full table scan.it will pick up only the index id and give it to the user...
     clustered index having unique values..how it will improve the performance of the query?
     

    DB:2.85:Index 9z

    Clustered index is helpful for certain type of queries. Check the following link.

    Consider using a clustered index for queries that do the following: Return a range of values by using operators such as BETWEEN, , =, , and =.....Use ORDER BY, or GROUP
    BY clauses.

    A general example:
    clustered index on OrderDate will speed up queries with filters like OrderDate = @StartDate and OrderDate = @EndDate.

    Kalman Toth Database OLAP Architect
    Paperback / Kindle: Database Pro

  • RELEVANCY SCORE 2.85

    DB:2.85:Error On Creating A View ka


    Getting this error when a tried to create index, does this mean I can not create index on this view. The column I am trying to create is a int type.

    Cannot create index on view 'products.dbo.view_test'. It contains text, ntext, image, FILESTREAM or xml columns.
    create unique
    clustered
    Index idx_viewtest1
    on view_test(Id)

    Gaby

    DB:2.85:Error On Creating A View ka

    You can create the index on the base table(s) if it don't already exists.
    Do you really need the BLOB column in the view? If not, remove it.Olaf Helper

    Blog
    Xing

  • RELEVANCY SCORE 2.85

    DB:2.85:Query Not Using Clustered Index m9


    Hello everybody,
    this is so weird.
    i have a table with 140,412,450 rows in it.
    there columns in it are indexed

    Group id -- has 2777 distinct values
    emp_id --- has 143,233 distinct values
    and a date column column service date which could be any date i could not see the distinct values in this.
    There is a clustered index on Groupid
    non clustered on emp_id and date column
    when iwanted to see the distinct values for all these column in the table
    i did
    select count(distinct group_id) from table 1(nolock)
    it uses the date index non clustered index scan in the execution plan
    when i run this

    select count(distinct emp_id) from table1 (nolock)
    it properly used the emp_id non clustered and did a non clustered index scan and did the calculations

    the data will be updated monthly. and expects to have about samenumber of rows in the table.
    its mostly read only
    is this correct to hve a clustered index on not a very unique column.. (group_id)

    there are queries which filteron group_id by other users.
    And i would be using another date column called paid date and i would be creating a non clustered index.

    but i am thinking of the clusteredindex..

    Please suggest

    DB:2.85:Query Not Using Clustered Index m9

    Ideally your clustered index will be on a unique value (if not sql will add a column to make it unique)
    Ideally it will also increment - to avoid fragemntation by insertign randomly into the data.
    You also don't want teh value to change - ie not effected
    by updates.
    There is a good video here on clustered indexes:
    http://technet.microsoft.com/en-us/sqlserver/gg508879.aspx

    Seth

    http://lqqsql.wordpress.com

  • RELEVANCY SCORE 2.84

    DB:2.84:Clustered Index Scan p8


    I have a large table, approx 20 million rows. That table has a clustered index on an ID column. There are no other indexes. I did a select * from table where col = something. The column in the where clause is not the ID column on which the
    clustered index is defined. So, I thought it would do a table scan, but I see that it did a clustered index scan. How does it use an index for a column that is not defined in the index definition?
    Here is a simple example I use to understand what an index does. Say you have a phone book. The phone book is organized by Last Name, First Name. The phone book is a (non unique) clustered index. In this example, if you search for Last Name = 'Smith', you
    do a clustered index seek. If you search for First Name = 'John', you do a clustered index scan. If you search by State = 'NY', what would that be? Still clustered index scan?

    DB:2.84:Clustered Index Scan p8

    Yes, the FirstName key cannot be used because it's not the first index column. However, in the case of a non-clustered index with LastName and First Name,a query that needs only those two columnscoulduse a non-clustered index scan
    to retrieve the data, which is less expensive thanthe clustered index scan. The optimal index for such a query wouldneed FirstName as the first column.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

  • RELEVANCY SCORE 2.84

    DB:2.84:How To Create Clustered Index In Oracle ? 81


    I need to Create unique clustered index idx_col1 on table1(col1) in Oracle 9i.
    Should I declare it at creation of table as index-organized ? My table is created with the code like Create table table1 as select ..... from ....
    Can anybody post an example ?

    Thanks

    DB:2.84:How To Create Clustered Index In Oracle ? 81

    Thanks a lot Justin.

    I already found the problem. This table consists of 87 fields, many of them have long size like varchar2(50). After reducing some the the largest fields to some lower number I finally got this table createion OK. Apparently, the combined size of all fields in one record should not exceed the size of data block.

  • RELEVANCY SCORE 2.82

    DB:2.82:Create Table Problem 77


    I'm trying to create a table - there will be others for a partitioned view. Column names only for test script.

    --if object_id(TestForM, U) is not null
    -- drop table TestForM

    create table TestForM
    (
    Ref1 nvarchar(10) not null,
    Ref2 nvarchar(10) not null,
    Ref_Timestamp smalldatetime not null,
    Ref_Value decimal(18, 4) not null
    )

    --primary key
    alter table TestForM add constraint PK_TestForM
    primary key nonclustered (Ref1, Ref2, Ref_Timestamp)

    --clustered index (not unique) - used later for partitioned view
    create clustered index IX_TestForM_Ref_Timestamp on
    TestForM (Ref_Timestamp)

    --re-create non-clustered index for primary key with include column
    drop index PK_TestForM on TestForM
    create unique nonclustered index PK_TestForM on
    TestForM (Ref1, Ref2, Ref_Timestamp) include (Ref_Value)

    --checks
    alter table TestForM add constraint CK_TestForM_Ref_Timestamp
    check (Ref_Timestamp 01/01/2009)

    DB:2.82:Create Table Problem 77

    Thanks Hugo, that'll explain it. Looked in BOL (2005) but didn't see that restriction on Include columns. A bit surprised as I thought that a PK constraint was no more than a unique index, so if you can have an Include column on a unique non-clustered index,
    why is making it the PK a problem.
    I'll have to take option 2.

  • RELEVANCY SCORE 2.82

    DB:2.82:Index On Normal View s1


    Hi
    As per my understanding, we can create index on normal view :
    1)First one should be Unique Clustered Index
    2)And then we can create n (n249) number of non clustered index on the same view.
    Correct?
    If yes, is the below syntax correct?
    CREATE UNIQUE CLUSTERED INDEX IDX_VW_TRADE
    ON VW_TRADE(LASTUPDATEDATE)

    Where VW_TRADE is the normal view and not materialized one.

    Please share your suggestions.

    Thanks.

  • RELEVANCY SCORE 2.82

    DB:2.82:Asc And Desc In Index Creation zj


    Hello All,

    I have oracle 10 g r 2

    I am creating the below index

    create index idx_col1 on TEST_INDEXES (col1 asc, col2 desc);

    My question is when I create a primary key by default a unique index is created.

    Can oblige this index to have descending ascending order , as the above index ?

    Regards,

    DB:2.82:Asc And Desc In Index Creation zj

    NB wrote:
    Why it did not work?

    Now i have two unique indexes, the one i manually created and the one created when i added the PK.

    Can i have only one index ?Thats because the one you created manually is a Function Based index. Oracle does not use it for Primary key.

  • RELEVANCY SCORE 2.82

    DB:2.82:How Do We Know That Clustered Index Is Created On Primary Key 3s


    hi All,

    I've read that clustered index is created when primary key is created
    And non-clustered index is created with unique key

    I'm confused how to check that clustered index is created with primary key

    Plese explain me

    DB:2.82:How Do We Know That Clustered Index Is Created On Primary Key 3s

    In that case, it seems you missed the forum ;)

    Regards

    Etbin

  • RELEVANCY SCORE 2.81

    DB:2.81:Sql 2008 Clustered Index Creation Changes Table Placement p9


    Hi everyone,could someone please explain why If I create a clustered index on table to a secondary file group, the table storage changes its location and moves from the primary to the secondary file group.Thanks

    DB:2.81:Sql 2008 Clustered Index Creation Changes Table Placement p9

    To add to what Phil said which is completely correct, you might take a quick read through the following BOL topics:Tables and Index Data Structure Architectureand more specifically:Clustered Index StructuresJonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!

  • RELEVANCY SCORE 2.81

    DB:2.81:Query Optimization Confused!!! ap


    I have a view with 30 tables joins. Non of them have any sort of indexes.When I ran select * from view with actual execution plan on it suggest me to create one non-clustered index..thats it..after i create that non clustered index one of the table scan
    gets changed into index seek..the columns it is suggesting to create non clustered index can also be used to create clustered index..
    Why it is suggesting to createnon clustered index instead of clusteredindex?
    For Example it is suggesting:

    CREATE

    NONCLUSTERED
    INDEX [NCIndes]

    ON

    table
    (a,b

    )

    INCLUDE
    (c,d)
    Here a,b,c,d can be used to create clustered index as they form unique values. But if I create clustered index with these columns nothing gets changed.
    Why it is not suggesting to create clustered index for other tables? Is it because creating clustered index wont help the query perf ?
    Any suggestion would be appreciated.
    Thanks,

    DB:2.81:Query Optimization Confused!!! ap

    Why it is not suggesting to create clustered index for other tables? Is it because creating clustered index wont help the query perf ?

    I don't think the advisors and wizards are really meant to start with all unindexed heaps.
    They are good for suggestions here and there, but for 30 tables you should be able to eyeball a lot more cases for indexes than just one.
    Josh

  • RELEVANCY SCORE 2.81

    DB:2.81:Clustered Index Or Not s8


    Hi all,
     
    I have a table with 25-30 million records. Presently the table has a clustered index on the unique code field which is a varchar(12) type. And a nonclustered index on a datetime field.
     
    Is it advisable to switch the clustered index to datetime field and non clustered to the varchar field, as i have read clustered index work good  over a range of values. Please comment
     
    Thanks

    DB:2.81:Clustered Index Or Not s8

    Hi Dev,You can do one another thing also..Take all queries related with that table and run index tuning wizard to find out required type of index. It will help you to find out which type of index you required on those columns, but sometimes it not give us correct advice or it may slow some another queries.RegardsDeepak Goyal

  • RELEVANCY SCORE 2.80

    DB:2.80:Table Partitions And Non Aligned Clustered Index jz


    Hi ThereI have been messing around with partitioning, one thing i have noticed is that you can create your table on your partitioning scheme and then a  non aligned clustered index.For exampleCREATE TABLE [fctStockMonthly] (   [TransactionDateKey]  int   NOT NULL,  [StoreKey]  int   NOT NULL,  [ProductKey]  int   NOT NULL) ON [PSchemeDate](TransactionDateKey)GOSo rows inserted with various date keys will end up in relevant partitions.But if i then create a clustered index on another filegroup or non aligned column eg:CREATE CLUSTERED INDEX CIX ON [fctStockMonthly] (StoreKey) ON [DEFAULT)ORCREATE UNIQUE CLUSTERED INDEX UIX ON [fctStockMonthly] (TransactionDateKey) ON [FG_Index]In both scenario's the data no longer will reside in the correct partitioned fiel groups but in the FG_index or default file group.In other words your partitioning in now completely useless.1. Is this just a stupid practice ? IE: should your partitioning column and clustered index always be aligned? What is the point of partitioning on one column and creating a clustered index on another , or creating a clustered index on the same column but in a different file group.I do not want to do this , i have just noticed you can do this but why would you ?Thanx

    DB:2.80:Table Partitions And Non Aligned Clustered Index jz

    In other words your partitioning in now completely useless.
    Why? If you query for particular partition then having CI is very efficient...Best Regards,Uri Dimant SQL Server MVP,
    http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog:
    Large scale of database and data cleansing
    MS SQL Consultants:
    Improves MS SQL Database Performance

  • RELEVANCY SCORE 2.80

    DB:2.80:Sql 2005 2008 Disable Index j3


    Hello,for SQL 2005 2008 is it possible to DISABLE either a Clustered and/or Non-clustered index but still have the unique constraint rule enabled ?

    Thanks in advance.

    DB:2.80:Sql 2005 2008 Disable Index j3

    Thanks for the speedy reply Kalman, much appreciated.

  • RELEVANCY SCORE 2.80

    DB:2.80:Reg: Index On Range In Where Clause 3p


    All,Please help me out on this scenario.I am creating a table where ID is an integer and the where clause will always be in range(for e.g between 200 and 400).for best performance which index can i create on table such that query runs faster ?(a)clustered(b)non-clustered(c)unique(d)both clustered and non clusteredThanks

    DB:2.80:Reg: Index On Range In Where Clause 3p

    All,Please help me out on this scenario.I am creating a table where ID is an integer and the where clause will always be in range(for e.g between 200 and 400).for best performance which index can i create on table such that query runs faster ?(a)clustered(b)non-clustered(c)unique(d)both clustered and non clusteredThanks

  • RELEVANCY SCORE 2.80

    DB:2.80:Is A Unique Constraint A Special Type Of Index? mf


    I have a compound clustered index across two columns.

    I have been asked to add a unique constraint across these two columns.

    When I added the constraint a new index appeared. Are unique constraint a special type of index?

    Should I just make the clustered index unique? Mr Shaw

    DB:2.80:Is A Unique Constraint A Special Type Of Index? mf

    Yes, if you create Unique constraint (logical component ) SQL Server creates Unique index (physical component) behind the scenes
    Yes you can CREATE CLUSTERED INDEX ... WITH DROP_EXISTING clause (see details in the BOL)Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • RELEVANCY SCORE 2.80

    DB:2.80:Database Indexing Question zx


    Cosider the following tableCREATE Table T1{        ID LONGINT(clustered index),        Name VARCHAR(50),        Status TINYINT,        GroupI INT,        GroupII INT}GroupII is a subset of GroupI Both however are found in the table since the creation of group 2 is based on the creation of entries in the table.If i have the following Indexes:ID (clustered)GroupI, Name (Unique)GroupIGroupII, NameGroupI, GroupII, Status, NameGroupII, Name, GroupIThe last two queries were added after a suggestion from the Index Tunning Wizard (SQL SERVER), they're however badly affecting other queries. Is there a way i can comromise those two in some way, maybe split them up?

    DB:2.80:Database Indexing Question zx

    The best information on this subject is in the Books Online:Tables and Index Data Structures ArchitectureYou can also get a real good information from Kalen Delaney's Inside SQL Server book:http://www.amazon.com/Microsoft%C2%AE-SQL-Server%C2%AE-2008-Internals/dp/0735626243Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!

  • RELEVANCY SCORE 2.80

    DB:2.80:Primary Key, Foreign Key, Unique Key Unique Index jk


    I have never seen more confusion on the web than the topic above. I prepared a simple chart which explains all. Let us know if helpful.

    DB Object
    How Many per Table
    FK referenceable

    Nulls in Column

    Underlying Object
    ANSI
    Modell

    PRIMARY KEY
    1
    yes
    none
    C (default)/NC unique index
    yes
    yes

    FOREIGN KEY
    many
    no
    none or many
    optional index
    yes
    yes

    UNIQUE KEY
    many
    yes
    0 or 1
    C / NC(default) unique index
    yes
    yes

    UNIQUE INDEX
    many
    yes
    0 or 1

    no
    no

    C - clustered NC - non-clustered
    UI is non-ANSI
    All can be single column or composite
    FK UI can be NULL or NOT NULL
    Nobody knows why 1 null is allowed in UI/UK

    Kalman Toth, SQL Server Business Intelligence Training;
    SQLUSA.com

    DB:2.80:Primary Key, Foreign Key, Unique Key Unique Index jk


    If the reason is not good enough,

    Precisely the point I am driving at: what is a good reason to FK reference a UNIQUE INDEX?Kalman Toth, SQL Server Business Intelligence Training;
    SQLUSA.com

  • RELEVANCY SCORE 2.80

    DB:2.80:Code Attribute : Does It Correspond Internally To Unique Clustered Index ? 1p


    All,
    I would like to know ifthe MDS required attribute 'Code' correspond internally to a Unique Clustered Index, or does MDS creates its own unique identifier behind the scene to be used as cluster index ?
    My question is related to scalability and index fragmentation. if Code is used as Unique Clustered Index then it impact the value we should assign to it. Having it as a dataType=Text seems to indicate that there is another indexing runing
    behind the scene, just want to confirm.
    Pascal.---- ----------- Pascal. ---------------

    DB:2.80:Code Attribute : Does It Correspond Internally To Unique Clustered Index ? 1p

    The Code column, along with the Version_ID column,is part of a unique, non-clustered index. The unique, clustered index is made up of the Version_ID column and an internal ID (integer) column.
    Thanks,
    Brian Barnett - MSFT
    SQL Server MDS Team

  • RELEVANCY SCORE 2.79

    DB:2.79:Non-Unique Clustered Index Performance jj


    I'm thinking of using GUIDs instead of int identity from now on. I've read that a GUID column is very bad to use as a clustered index though. I was wondering if, generally speaking, it would be an acceptable design to make the clustered index another, non-unique
    column, that will group the rows according to how they're most often accessed. Say, if I have an ItemDetail table that FK's on Item by ItemID, I would make ItemID the clustered index on itemDetail. Any gotchas there?

    DB:2.79:Non-Unique Clustered Index Performance jj

    Think about update of the row. With unique clustered index and visible uniquifier you can update it based on the clustered index value. For non-unique clustered index, you must use either non-clustered key or the combination of clustered index and
    primary key columns in the where. In both cases the plan would be less efficient.

    In the app I worked on, the important thing was to get those 50 rows efficiently. Once you're down to fifty, getting any one of them is trivial, but if we had to scan all over a 500gb table to find the 50 rows that would have been inefficient.
    It doesn't hurt to make the uniquifier visible, it just doesn't help much in cases like this, and maybe the design looks simpler if you do without it.
    Josh

  • RELEVANCY SCORE 2.78

    DB:2.78:Huge Tables With No Appropriate Column For Creating Clustered Index ! 9p


    Hello,
    I have come across a stored procedure which needs to be tuned. It is using some tables that are too huge (100 million records, datasize 5GB).
    The SELECT operations on these tables are taking most of the time. There is no unique column present in these tables that can be either made primary key or clustered index or can be considered as basis of horizontally partitioning the tables. There is one non-clustered
    index present for each of these tables on a non-unique integer column but this index is not helping much as the performance is real slow.
    Will adding a new unique column (identity column) will help to increase the performance as then these tables could be partitioned and I can create clustered index on that unique column? Looking for experts' advice on this.
    Regards.In persuit of learning SQL.

    DB:2.78:Huge Tables With No Appropriate Column For Creating Clustered Index ! 9p

    There is no right or wrong when it comes to indexing. The are aspects. I suggest you follow the suggestion given by Dan. I.e., it can be fine a cluster over something which isn't unique. A covering non-clustered index can be even more beneficial. But
    of course such addes overhead. That is the tradeoff. I also agree that returning 10 million rows is a lot, and for such there is only so much one can do...Tibor Karaszi, SQL Server MVP |
    web | blog

  • RELEVANCY SCORE 2.78

    DB:2.78:Can We Create Unique Clusterd Index On View Which Is Dynamic In Nature(Data Is Fetched Using Pivot In Sql Server) 19


    Hi ,
    I am trying to create to full text index on description column in view. The prerequiste for full text index on view is, it must have unique clustered index.

    My view is created using Pivot function in SQL server and joins. When i try to add Unique Clustered index to view it gives me error message Cannot create index on view 'vInDetails' because the view is not schema bound.
    WhenI try to add with schemabinding in create view statement it says table 'dynamic' is invalid for schema binding. My view uses CTE and PIVOT function.
    How can add full text index on View? is there any other alternative?

    Thanks,
    Dhanaji

    Dhanaji

    DB:2.78:Can We Create Unique Clusterd Index On View Which Is Dynamic In Nature(Data Is Fetched Using Pivot In Sql Server) 19

    Hello,
    You must create the view by using the WITH SCHEMABINDING option in order to indexed view:
    --Create view
    CREATE VIEW schema.view
    WITH SCHEMABINDING
    AS
    ....
    GO
    --Create an index on the view.
    CREATE UNIQUE CLUSTERED INDEX IDX_V1
    ON schema.vew (column);
    GO
    Reference:Create Indexed Views
    Regards,
    Fanny Liu Fanny Liu

    TechNet Community Support

  • RELEVANCY SCORE 2.78

    DB:2.78:Does Microsoft Plan To Add Primary Key And Unique Constraints To Clustered Columnstore Index In Next Releases Of Sql Server After 2014? jc


    Clustered columnstore index is a greate feature in SQL Server 2014, but currently cannot be used with primary key and unique constraints, which is important to achieve data integrity. I wonder if Microsoft plans to add primary key and unique constraints
    to clustered columnstore index in next releases of SQL Server. Thanks!

    DB:2.78:Does Microsoft Plan To Add Primary Key And Unique Constraints To Clustered Columnstore Index In Next Releases Of Sql Server After 2014? jc

    One way to achieve is by creating a materialize view as follows. I would like to thank Brent Ozar for this idea

    create view dbo.my_t_account with schemabinding
    as select accountkey
    from dbo.CCI table
    go
    create unique clustered index clix_my_t_account on my_t_account (accountkey)
    thanks
    Sunil

  • RELEVANCY SCORE 2.77

    DB:2.77:Clustered Index On A Varbinary Column 98


    Hi,
    I just wanted to ask if there are any consequences to using a varbinary(120) column on a table and having a clustered index on it. How does this affect inserts etc.
    This column is used in a checksum like manner, and the checksum values are unique.
    Many thanks.

    DB:2.77:Clustered Index On A Varbinary Column 98

    As mentioned above, this is a good performance practice to have key value this big.Please use Marked as Answer if my post solved your problem and use
    Vote As Helpful, if a post was useful to help other user's find a solution quicker.

  • RELEVANCY SCORE 2.77

    DB:2.77:Unique Clustered Index z8


    Can someone explain what is Unique Clustered Index and what is Unique NonClustered Index ?

    DB:2.77:Unique Clustered Index z8


    Thanks for your quick response. But i'm looking for Unique CLustered Index Unique NonClustered Index.

    Following is a demo. Primary key creates a unique index. It can be clustered or nonclustered. Default is clustered.

    CREATE TABLE Alpha (
    Col1 int PRIMARY KEY CLUSTERED, -- UNIQUE CLUSTERED INDEX
    Col2 nvarchar(32) UNIQUE, -- UNIQUE NONCLUSTERED INDEX
    ModifiedDate datetime default(CURRENT_TIMESTAMP)); -- no index

    INSERT Alpha(Col1, Col2) SELECT 1, 'New York City'
    -- (1 row(s) affected)

    INSERT Alpha(Col1, Col2) SELECT 1, 'Los Angeles'
    /*
    Msg 2627, Level 14, State 1, Line 1
    Violation of PRIMARY KEY constraint 'PK__Alpha__A259EE544119A21D'. Cannot insert duplicate key in object 'dbo.Alpha'. The duplicate key value is (1).
    The statement has been terminated.
    */

    INSERT Alpha(Col1, Col2) SELECT 2, 'New York City'

    /*
    Msg 2627, Level 14, State 1, Line 16
    Violation of UNIQUE KEY constraint 'UQ__Alpha__A259EE564C8B54C9'. Cannot insert duplicate key in object 'dbo.Alpha'. The duplicate key value is (New York City).
    The statement has been terminated.
    */
    INSERT Alpha(Col1, Col2) SELECT 2, 'Los Angeles'
    -- (1 row(s) affected)
    GO
    DROP TABLE dbo.Alpha
    GO

    Kalman Toth SQL SERVER 2012 BI TRAINING
    New Book: Beginner Database Design SQL Programming Using Microsoft SQL Server 2012

  • RELEVANCY SCORE 2.77

    DB:2.77:Index Creation: Unique Vs Nonunique jp


    Looking for a white paper on the timing for creation and for performance of nonunique vs unique indexes on columns that are indeed unique.

    Thanks
    Bill

    DB:2.77:Index Creation: Unique Vs Nonunique jp

    Well, Tom provided the answer.

    For more information see the "Ask Tom" response to this question.

    Regards
    Bill

  • RELEVANCY SCORE 2.76

    DB:2.76:Partitioned Index xa


    I have large table
    whom I want to partition. My partitioning column is a datetime column and primary key is a column of datatype char(13).
    As I am new to partitioning I have some queries

    First I want to ask is that is it possible to create a clustered index on primary key column using partition scheme.
    Secondly, should I create
    a clusterd index on primary key column before adding primary key constraint using
    partition scheme then create unique non-clustered index on primary key column and place it on the default filegroup or on primary filegroup.
    Third if I create few more non-clustered indexes on other columns, should it be a better to include the clustered index column in it.
    What if primary key is a composite primary key?
    I am using SQL Server 2008 SP2.

    Regards, Muhammad Bilal.

    DB:2.76:Partitioned Index xa

    Hi Muhammad Bilal
    I am trying to gain performance by doing partitioning.
    That is very important statement, in my opinion partition is not about about performance but about manageability... Sure if your user serach ONLY for specific partiton that is perfomance gain but if WHERE condition spreads out to almost the entire table
    that is NOT...
    Another great advantage of partitions is quckly moving 'old' data to archive... I have been using it all time
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • RELEVANCY SCORE 2.76

    DB:2.76:Question On Index Creation For Large Table pf


    Hi all--Given a table with the current setup and structure, I would like to create a clustered index for a table that gets updated once per week values I would like to keep unique:
    CREATE TABLE [dbo].[DBA_AUDIT_SESSION](
    [INSTANCE_NAME] [varchar](16) NULL,
    [HOST_NAME] [varchar](64) NULL,
    [OS_USERNAME] [varchar](255) NULL,
    [USERNAME] [varchar](30) NULL,
    [USERHOST] [varchar](128) NULL,
    [TERMINAL] [varchar](255) NULL,
    [TIMESTAMP] [datetime] NOT NULL,
    [ACTION_NAME] [varchar](28) NULL,
    [LOGOFF_TIME] [datetime] NULL,
    [LOGOFF_LREAD] [float] NULL,
    [LOGOFF_PREAD] [float] NULL,
    [LOGOFF_LWRITE] [float] NULL,
    [LOGOFF_DLOCK] [varchar](40) NULL,
    [SESSIONID] [float] NOT NULL,
    [RETURNCODE] [float] NOT NULL,
    [CLIENT_ID] [varchar](64) NULL,
    [SESSION_CPU] [float] NULL,
    [Import_Date] [datetime] NULL
    )
    This is is for an existing table within a SQL Server 2005 SP1 database. 
     
    I was thinking about creating an IDENTITY column and creating that as a composite primary key with TIMESTAMP, but the following posting seems to suggest that's not a good idea for performance and not the best design:
     
    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9messageid=152846
     
    Average size for this table is 1.75-2.5 million rows, and the TIMESTAMP field doesn't carry completely unique values.  The data is sourced from an Oracle 9i and/or 10gR2 database, populated by an SSIS package.  Suggestions on creating a good clustered index for this table?  Would I gain value by using the NEWSEQUENTIALID function in SQL Server 2005 on a uniqueidentifier datatype column?
     
    Thanks,
    Jonathan

  • RELEVANCY SCORE 2.75

    DB:2.75:Create Clustered Index On Table cp


    hello ,
    in our database on one table we don't have clustered index ,for requirement of application I created clustered on table after creation of clustered index application is very slow what is reason behind that .
    please explain .........

    mastanvali shaik

    DB:2.75:Create Clustered Index On Table cp

    Have a look at execution plan and see the step costsPlease Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

  • RELEVANCY SCORE 2.75

    DB:2.75:Finding Duplicate Keys 3x


    Hi

    Im little bit confused
    The status of my index is unusable and I cannot rebuild it.
    How can I find and delete the duplicate values that violate my index creation?

    Here is the ddl of my index:

    CREATE UNIQUE INDEX MOTOR_CONT_PK ON MOTOR (CONT_BRANCH_ID, PRODUCT_CODE, CONT_POLICY_NO, CONT_NO, CONT_COMPANY_ID, EFFECTIVE_DATE)

    SQL alter index MOTOR_CONT_PK rebuild;
    alter index MOTOR_CONT_PK rebuild
    *
    ERROR at line 1:
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

    Thanks in advance

    DB:2.75:Finding Duplicate Keys 3x

    DELETE FROM MOTOR
    WHERE ROWID IN (SELECT ROWID FROM
    (SELECT ROWID,ROW_NUMBER()OVER(PARTITION BY CONT_BRANCH_ID,
    PRODUCT_CODE ,
    CONT_POLICY_NO ,
    CONT_NO ,
    CONT_COMPANY_ID ,
    EFFECTIVE_DATE ORDER BY NULL) ID FROM MOTOR)
    WHERE ID1);Try using analytic function, If the other way is taking the time still.

    Ravi Kumar

  • RELEVANCY SCORE 2.75

    DB:2.75:If Clustered Index Updates Then Non-Clustered Index Also? mx


    if clustered index updates then non-clustered index also?

    Thanks

    DB:2.75:If Clustered Index Updates Then Non-Clustered Index Also? mx

    Thanks all,
    Actually I was reading the deadlock case study in which its mentioned that an update statement trying to update the cluster index and if cluster index updates then non cluster index also.
    Check: http://support2.microsoft.com/kb/832524
    Thanks

  • RELEVANCY SCORE 2.75

    DB:2.75:Partitioning On A Table - Few Questions, Confusions 9x


    Hello All,
    I've a table with around 300 Million Records. This Table has one Key Column (Auto Incremented by 1 ) and a Unique ID Field.
    Primary Key (Clustered Primary Key) was created on Key Column of this Table.
    Now If I want to partition this table based on UniqueID Field, SQL Server is not allowing me to do it! It throws the error something like Partitioned Column should present in Primary Key (Clustered Index)
    Is it mandatory that the partitioned column should definitely be present in the clustered index or primary key of the table if exists.

    Could someone tell me what are the prerequisites to partition a table that already have a primary key on FieldA and Unique Non Clustered Index on FieldB and I'd like to partition the table based on FieldC?

    Thanks in Advance...

    Unknown

    DB:2.75:Partitioning On A Table - Few Questions, Confusions 9x

    Yes, the partitioned table should have primary key.
    I completely agree with Dan's point. Refer the below link
    http://www.mssqltips.com/sqlservertip/1914/sql-server-database-partitioning-myths-and-truths/
    --Prashanth

  • RELEVANCY SCORE 2.75

    DB:2.75:Primary Key/Unique Index sp


    I'd generally thought it was a really good idea to make sure every table has a primary key and (probably good practice that it is clustered. (first normal and all that). However I've come across a table which has no PK, but does have a unique clustered index defined where you'd expect the PK to be. From a constraint/data integrity perspective the unique index will quite happliy perform the job of the PK; my question therefore is there any compelling reasons for this table to have a primary key added (and thus the superfluous CI removed).

    DB:2.75:Primary Key/Unique Index sp

    You cannot use replication on tables without a primary key. That's a compelling reason enough for me!

  • RELEVANCY SCORE 2.75

    DB:2.75:Odi - Create Unique Index On Flow Table 3c


    I am getting an error in step 19 which is "Create Unique Index on flow table"
    The statement is:
    create unique index IT_OWNER."I$_TEMP_ODI_Priority"_idx
    onIT_OWNER."I$_TEMP_ODI_Priority" (ID)
    NOLOGGING

    Why ODI is not generating index name correctly? The correct statement is
    create unique index IT_OWNER."I$_TEMP_ODI_Priority_idx"
    onIT_OWNER."I$_TEMP_ODI_Priority" (ID)
    NOLOGGING

    The issue here is that suffix _IDX is added at the end of statement, outside of double quotes. My process is failing and how can I force ODI to generate index name correctly?

    DB:2.75:Odi - Create Unique Index On Flow Table 3c

    Thank you very much. I tried first option (All upper case for table name) and it worked.

  • RELEVANCY SCORE 2.74

    DB:2.74:How Did Primary Key Constraint Get Dropped Without Dropping The Clustered Index? j8


    I have a table called Departments with no primary key. But there is a clustered Unique index on this table named PK_Departments. We have a practice of naming our Primary Keys with a prefix of PK_. How could the primary key have gotten dropped without alsodropping
    theclustered index? I have tried creating a table with a primary key, then altering the table to drop the primary key constraint, but it always drops the clustered index along with the Primary Key Constraint. Is there any way to drop the primary key
    and leave the clustered index?
    In case it matters, I am using this version : Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 X64 (Build
    6002: Service Pack 2)

    DB:2.74:How Did Primary Key Constraint Get Dropped Without Dropping The Clustered Index? j8

    This doesn't happen by itself. There is no command to achieve it. One can speculate about some strange bug in the db engine, but I would be very very surprised.
    Most likely, either the cl index was added by itself originally (not through a PK constraint definition); or you used to have a PK with its index as cl, and somebody removed the PK constraint and added this cl index by itself (essentially the first scenario,
    byt you had a PK which was dropped).
    So, short answer is no. :-)Tibor Karaszi, SQL Server MVP |
    web | blog

  • RELEVANCY SCORE 2.74

    DB:2.74:Can We Create Unique Clustered Index On Columns Having Null Values j8



    Hello,
    A quick question, Can we create Unique Clustered Index(UCI) on table having some of the column values are Null.
    Actually we had a Job process in which unfortunately some null values came inside table 'A'for the column 'X'. And when trying to create UCI on table 'A' for the same column'B' its failed givving below error.
    The Unique clustered Index statement terminated because a duplicate key was found

    for object name dbo.A, The duplicate key value is NULL

    DB:2.74:Can We Create Unique Clustered Index On Columns Having Null Values j8

    Chintak has a good point about the filtered indices. If you are not going to filter, you can create unique index that includes a column that is nullable. However, the values -- including nulls -- must be unique. So if you try to build a
    unique index of any kind -- whether clustered or not -- on a single column, that column must contain no more than one null value or the index create will fail. For example:
    --drop table dbo.test
    create table dbo.test( aColumn int );
    insert into dbo.test
    select 1 union all select 2 union all select null;
    create unique clustered index what
    on dbo.test (aColumn);
    /* -------- Output: --------
    Command(s) completed successfully.
    */
    drop index dbo.test.what;
    insert into dbo.test values(null);
    select * from dbo.test;
    /* -------- Output: --------
    aColumn
    -----------
    NULL
    1
    2
    NULL
    NULL
    (5 row(s) affected)
    */
    create unique clustered index what
    on dbo.test(aColumn);
    /* -------- Output: --------
    Msg 1505, Level 16, State 1, Line 1
    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.test' and the index name 'what'. The duplicate key value is (NULL).
    The statement has been terminated.
    */
    * To get these results, the commands must be run one command at a time.

  • RELEVANCY SCORE 2.74

    DB:2.74:Is Primary Key Really Needed. pj


    I have a database where many of the tables have a unique clustered index on the Table_ID (identity column) BUT there is no Primary Key defined.

    Is there a good reason(s) why I should build primary keys for those tables. Note, the Primary Key would be same Table_Id as the clustered index.

    DB:2.74:Is Primary Key Really Needed. pj

    Ideally when designing a table it should have a PK , in datawarehousing and some other cases you will see that a PK is not a must. e.g the fact table tables with many to many realtionships

    Also even if you do not enfore the PK, you can simulate it by using the inner join within the stored procedure and by using triggers etc
    http://msdn.microsoft.com/en-us/library/aa902684%28v=sql.80%29.aspx
    I have seen both implmentations and if you want a bug free and well designed system use the PK.Jayanth Kurup - www.enabledbusiness.com

  • RELEVANCY SCORE 2.74

    DB:2.74:Stop Sql Server Doing An Unneccessary Clustered Index Insert On A View 7x


    Hi
    I have a large table (between 74 and 88million rows) which is the middle table (Table B) of a many to many relationship. I have a view that builds a unified picture of the data contained in the these tables. The view has a clustered index defined against it.
    Table A on the left hand side of my large table is the core table in my database. Table C is the table that contains the normalised data items. When I insert a new record into table C the task takes a long time to complete (5mins on a very very good server). This is because SQL server rebuilds part of the clustered index on the view (I can see a clustered index insert in the actual execution plan). This is despite the new row in Table C not being referenced in Table B. Inserting rows into Table A and Table B takes milliseconds as I would expect. The database is not partitioned.
    This is an annoymised version of the tables creation script. I've left the columns that aren't used in so you can see the full structure of the tables.
    CREATE TABLE [dbo].[TableA] ([TableAId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableAIdGUID] [uniqueidentifier] NOT NULL,[ImportantId] [INT] NOT NULL ,[DateCreated] [datetime] NOT NULL ,[OtherId2] [int] NOT NULL ,[TableATypeId] [int] NOT NULL ,[Active] [bit] NOT NULL ,[AuditUser] [NVARCHAR] (20) NULL , [AuditTime] [datetime] NULL )GO
    CREATE TABLE [dbo].[TableB] ([TableBId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableBGUID] [uniqueidentifier] NOT NULL,[TableAId] [INT] NOT NULL ,[TableCId] [INT] NOT NULL ,[Tag] [NVARCHAR] (50) NULL , [Order] [tinyint] NOT NULL ,[DateCreated] [datetime] NOT NULL ,[Date1] [datetime] NULL ,[Date2] [datetime] NULL ,[LastUpdated] [datetime] NOT NULL ,[Active] [bit] NOT NULL ,[AuditUser] [NVARCHAR] (20) NULL , [AuditTime] [datetime] NULL)GO
    CREATE TABLE [dbo].[TableC] ([TableCId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,[TableCIdGUID] [uniqueidentifier] NOT NULL,[TableCTypeId] [int] NOT NULL ,[TableCValue] [NVARCHAR] (255) NOT NULL , [Frequency] [float] NOT NULL DEFAULT 1,[TableCValue2] [NVARCHAR] (255) NULL ,[AuditUser] [NVARCHAR] (20) NULL ,[AuditTime] [datetime] NULL )GO
    CREATE VIEW [dbo].[vwTables]WITH SCHEMABINDING ,ENCRYPTIONAS
    SELECT dbo.[TableB].TableBId, dbo.[TableB].TableAId, dbo.TableA.ImportantId,dbo.TableC.TableCValue,dbo.TableC.TableCValue2, dbo.TableC.TableCTypeId,dbo.TableA.TableATypeIdFROM dbo.[TableB] INNER JOINdbo.TableC ON dbo.[TableB].TableCId = dbo.TableC.TableCId INNER JOINdbo.TableA ON dbo.[TableB].TableAId = dbo.TableA.TableAIdWHERE (dbo.[TableB].Active = CAST(1 AS BIT)) AND (dbo.TableC.TableCValue'') and (dbo.TableC.TableCValue is not null)GO
    CREATE UNIQUE CLUSTERED INDEX [IX_vwTables] ON [dbo].[vwTables] ([TableCValue] ASC,[TableCTypeId] ASC,[TableBId] ASC,[ImportantId] ASC)GO
    ALTER TABLE [dbo].[TableB] ADD CONSTRAINT [FK_TableB_TableC] FOREIGN KEY ([TableCId]) REFERENCES [dbo].[TableC] ([TableCId]),CONSTRAINT [FK_TableB_TableA] FOREIGN KEY ([TableAId]) REFERENCES [dbo].[TableA] ([TableAId])GO
    CREATE NONCLUSTERED INDEX [IX_TableA_Nonclustered] ON [dbo].[TableA] ([ImportantId] ASC,[TableAId] ASC,[TableATypeId] ASC,[Active] ASC)GO
    CREATE NONCLUSTERED INDEX [IX_TableA_OtherId2] ON [dbo].[TableA] ([AuditTime] ASC,[OtherId2] ASC)GO
    CREATE NONCLUSTERED INDEX [IX_TableB_NonClustered] ON [dbo].[TableB] ([TableAId] ASC,[TableBId] ASC,[Active] ASC)GO
    CREATE NONCLUSTERED INDEX [IX_EntityAttributes_NonClustered_2] ON [dbo].[TableB] ([TableBId] ASC,[TableAId] ASC,[TableCId] ASC,[Tag] ASC,[Active] ASC) GO
    Does anyone know why SQL Server decides it needs to rebuild part of the index when I insert into TableC and not TableA or TableB and how I can prevent it from doing so.
    Thanks in advanced.P.S. I also posted this at on the http://social.msdn.microsoft.com/Forums/en-US/searchserverdevelopersandcustomization/threads forum as I found it first but reposted it here as this is a better forum for it I think.

    DB:2.74:Stop Sql Server Doing An Unneccessary Clustered Index Insert On A View 7x

    Just to add to the previous posts, creating indexed views is beneficial when data is frequently read but rarely updated, and this is because of the overhead of index maintenance. The index materializes the view and the indexes will need to be maintained on the inserts.Abdallah, PMP, ITIL, MCTS

  • RELEVANCY SCORE 2.73

    DB:2.73:Create Index 1d


    Hello guys,

    I'm new in oracle, Im used to creating clustered and non-clustered indexes in SQL server, however that concept doesnt seem to exist in oracle.

    Im using Oracle 9i, I have a table that has a primary key, the cardid field and a unique key, the serialnb field. I want to be able to create an index on the serialnb field. If this was in SQL server ideally i would have created a clustered index on the serialnb field.

    How can i accomplish this in Oracle.

    Thanks in advance.

    Mike

    DB:2.73:Create Index 1d

    And a [SQL Server] clusterd index will reorder the way records are phisycally [sic] stored See Oracle IOT referenced earlier.

    All three topics I mentioned are covered in the Application Developers Guide and in the Concepts manual.

    -- Mark D Powell --

  • RELEVANCY SCORE 2.73

    DB:2.73:Multiple Primary Key dp


    Why can not we create multiple primary key(with non clustered unique index) on a table?~Technology Analyst~

    DB:2.73:Multiple Primary Key dp

    You can create many different alternate keys by declaring unique constraints on any given table. Please give the ddl that you are trying to execute so that we can help you correct whatever trouble you might be having. Also, give a look at unique
    constraints in books online or try searching this forum for unique constraints.

  • RELEVANCY SCORE 2.73

    DB:2.73:Constraint Ordering cf


    How does Constraint PK ASC/DESC or Unique ASC/DESC or clustered index ASC/DESC impact PERFORMANCE? Are there any guidelines?

    DB:2.73:Constraint Ordering cf

    A CLUSTERED INDEX defines the order of records in the TABLE, and thus has an impact on performance.
    If the INDEX is NONCLUSTERED, i do not know. In Oracle, it will not impact performance, as the INDEX can be read forward or backward with no performance penalty. I would *guess* SQL Server is the same, but i do not know.

  • RELEVANCY SCORE 2.72

    DB:2.72:Reg: Index On Range In Where Clause m7


    All,Please help me out on this scenario. I am creating a table where ID is an integer and the where clause will always be in range(for e.g between 200 and 400).for best performance which index can i create on table such that query runs faster ?(a)clustered(b)non-clustered(c)unique(d)both clustered and non clustered Thanks

    DB:2.72:Reg: Index On Range In Where Clause m7

    All,Please help me out on this scenario. I am creating a table where ID is an integer and the where clause will always be in range(for e.g between 200 and 400).for best performance which index can i create on table such that query runs faster ?(a)clustered(b)non-clustered(c)unique(d)both clustered and non clustered Thanks

  • RELEVANCY SCORE 2.71

    DB:2.71:Creating Index On A Xml Generated From Dataset 99


    HI mark,

    I have an XMLTYPE table that contains data of the following format

    ROWSET
    ROW
    column11/column1
    column22/column2
    /ROW
    ROW
    column11/column1
    column22/column2
    /ROW
    ROW
    column11/column1
    column22/column2
    /ROW
    /ROWSET

    the size of the XML is around 6.6 GB
    I have to index it on column 1..

    Is it possible?
    If so how should be my schema?
    and table creation procedure?

    Can I create a clustered index on column 1 (it contains unique value)

    I am using an XMLTYPE table

    thanks
    Kathir

    DB:2.71:Creating Index On A Xml Generated From Dataset 99

    You will have to enable query re-write and update your statistics (dbms_stats package) on the objects your dealing with (table/index)

  • RELEVANCY SCORE 2.71

    DB:2.71:Query To Find The List Of Tables With Clustered Index a3


    In our database we found a table that was having clustered index on it , but not unique clustered index.Because of this , duplicates were inserted into that table .
    Now, we want to get the list of all the tables in that databse that have clustered index on that (not unique clustered index).
    I have using few queries but that does not work .
    Can someone help me with this?

    DB:2.71:Query To Find The List Of Tables With Clustered Index a3

    I've used the following.
    SELECT
    t.name AS table_name,
    i.type_desc AS index_type,
    i.is_unique AS is_unique_index
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
    INNER JOIN sys.indexes AS i
    ON t.object_id = i.object_id
    WHERE i.type_desc = 'CLUSTERED';

  • RELEVANCY SCORE 2.71

    DB:2.71:Partitioned Index Creation In Sql 2005 3f


    I have created a partition function PF1 and a partition scheme PS1 with multiple FGs using the function PF1.
     
    I created a partitioned table on the PS1;
    CREATE TABLE [dbo].[T1]( [Col1] [int] NOT NULL, [Col2] [smalldatetime] NOT NULL, [Col3] [smalldatetime] NOT NULL, [Col4] [datetime] NOT NULL CONSTRAINT [DF1]  DEFAULT (getdate()), [Col5] [float] NOT NULL CONSTRAINT [DF2]  DEFAULT ((0)), [Col6] [binary](16) NOT NULL CONSTRAINT [DF3]  DEFAULT (CONVERT([binary],'0',0)), [Col7] [tinyint] NOT NULL, [Col8] [tinyint] NOT NULL, [Col9] [bigint] NULL)
     ON PS1(Col3)
     
    I inserted data into this table and the database size is about 5.5TB. Each partition is about 480 GB.
     
    Now, I am trying to build a clustered index on this table using the same partition scheme PS1 so that I have a partitioned aligned index.
    ALTER TABLE [dbo].[T1] ADD CONSTRAINT [PK1] PRIMARY KEY CLUSTERED ([Col1] ASC, [Col3] ASC, [Col4] ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,       SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PS1]([Col3])
     
    I am specifying SORT_IN_TEMPDB as ON and I hav 2 TB of space for the tempdb. In this case, the sort table should be crated for one parition at time. So, 2 TB of tempdb space should be sufficient to sort one partitione at a time. But, I see that multiple paritions are being sorted concurrently and the index creation is failing sue to low space in tempdb.
     
    Can anyone suggest as to why it is hppening. Any suggstions on creation of my index?

    DB:2.71:Partitioned Index Creation In Sql 2005 3f

    I have created a partition function PF1 and a partition scheme PS1 with multiple FGs using the function PF1.
     
    I created a partitioned table on the PS1;
    CREATE TABLE [dbo].[T1]( [Col1] [int] NOT NULL, [Col2] [smalldatetime] NOT NULL, [Col3] [smalldatetime] NOT NULL, [Col4] [datetime] NOT NULL CONSTRAINT [DF1]  DEFAULT (getdate()), [Col5] [float] NOT NULL CONSTRAINT [DF2]  DEFAULT ((0)), [Col6] [binary](16) NOT NULL CONSTRAINT [DF3]  DEFAULT (CONVERT([binary],'0',0)), [Col7] [tinyint] NOT NULL, [Col8] [tinyint] NOT NULL, [Col9] [bigint] NULL)
     ON PS1(Col3)
     
    I inserted data into this table and the database size is about 5.5TB. Each partition is about 480 GB.
     
    Now, I am trying to build a clustered index on this table using the same partition scheme PS1 so that I have a partitioned aligned index.
    ALTER TABLE [dbo].[T1] ADD CONSTRAINT [PK1] PRIMARY KEY CLUSTERED ([Col1] ASC, [Col3] ASC, [Col4] ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,       SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PS1]([Col3])
     
    I am specifying SORT_IN_TEMPDB as ON and I hav 2 TB of space for the tempdb. In this case, the sort table should be crated for one parition at time. So, 2 TB of tempdb space should be sufficient to sort one partitione at a time. But, I see that multiple paritions are being sorted concurrently and the index creation is failing sue to low space in tempdb.
     
    Can anyone suggest as to why it is hppening. Any suggstions on creation of my index?

  • RELEVANCY SCORE 2.71

    DB:2.71:Adding Indexs In Merge Replication 7d


    I have add index and change PK's (dropold and creon one of my articles in merge replication. PK changes are replicated but new indexs not. For article options are set to
    @schema_option

    = 0x000001494DFF7FD1
    options to relicate all indexs is includeed. (

    0x10 -
    Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and UNIQUE constraints are generated if they are already defined on a published table.,
    0x40 -
    Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and UNIQUE constraints are generated if they are already defined on a published table.,
    0x80 -
    Replicates PRIMARY KEY constraints. Any indexes related to the constraint are also replicated, even if options
    0x10 and 0x40 are not enabled.
    ).
    I have expected that creating of new index will replicate too. Is that normal or is there some solution for make creation of index to replicate too.
    Thanks in advanced.

    DB:2.71:Adding Indexs In Merge Replication 7d

    Hi,
    After you create new index on the table, you may need to regenerate the snapshot and apply to the subscriptions to make schema change to take effect.
    Thanks,
    ChunsongPlease remember to click Mark as Answer on the post that helps you, and to click Unmark as Answer if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

  • RELEVANCY SCORE 2.71

    DB:2.71:Clustered Index Sort a8


    I have table like :

    CREATE TABLE [dbo].[myTable](id [int] IDENTITY(1,1) NOT NULL, col1 [int] NOT NULL, col2 [smallint] NOT NULL, col3 [tinyint] NOT NULL,.....)
    There is unique clustered index on column id.
    Than I have filtered nonclustered index on 3 columns, col1, col2 and col3.
    When query is executed it uses this index (index scan) as expected.

    But after that it sorts the result set by clustered column id(because of later merge join). It is very expensive operation.
    Since clustered column id is already part of every non-clustered index(as row locator id), is there any option to get the result set of index scan already sorted by id?

    DB:2.71:Clustered Index Sort a8

    8 Well, the code doesnt matter here. In SQL2012 windowsBase.dll is blocked by design. Since this is one of the basics .NET dlls, I guess, many unsafe assemblies wont work in SQL2012.

    Without seeing the code, it will not be possible for anyone to say whether you can solve the problem without that DLL or not.
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • RELEVANCY SCORE 2.71

    DB:2.71:How Creating Clustered Index On Foreign Key Help? 97


    Hi,
    how clustered index creation on foreign key help while joining.
    Please let me know the reasons.

    Regards
    Gokul

    DB:2.71:How Creating Clustered Index On Foreign Key Help? 97


    Thanks naom,
    i feel it provides good reason to test this things on huge tables :) and seeing the execution plan might provide more valid reasons.

    Regards
    Gokul

  • RELEVANCY SCORE 2.70

    DB:2.70:Clustered Index In Varchar Column xj


    Can I create a clustered index in a VARCHAR column which is NOT UNIQUE and HAS NULL VALUES? But creating one really improves perfomance of an important query that is executed often. I just want to know if it is right to createa clustered index on a
    VARCHAR column.
    Please advice.

    DB:2.70:Clustered Index In Varchar Column xj

    In addition , if it is often updated , it could lead you to heavy fragmentation and as result performance degradation. Let it go and look at fragmentation to see how often it get fragmented??Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • RELEVANCY SCORE 2.70

    DB:2.70:Heap Table Vs Clustered With Added Column mm


    I have heap table with Primary Key composed of 2 columns.
    First column of PK is INT and second column is varchar(50).
    Currently, this PK is non-clustered index even if by default, sql creates PK as clustered.

    Since this PK is 54 byte(or 29 bytes in average), this would enormously enlarge all other non-clustered indexes. So, I agree it is better to have this PK as non-clustered.
    Only when this PK would be part of the most of non-clustered index anyway, than it is better to have it as clustered, i guess? I know it depends, but anyway, what is your opinion for some common case?
    Since table doesn't have clustered index, sql adds rowID to every non-clustered index row. Row id is 8 byte.
    Wouldn't it be better to add some identity column to this table(even if I don't need it anywhere) and mark it as clustered unique index? Than this clustered key, which is only the half of the size of rowID(4 byte), would be added to every non-clustered index
    row and every row of non-clustered index this way would have 4 bytes less?

    And another benefit is that the table is more likely to be less fragmented if clustered than non-clustered.

    And whenretrieve data from heap table sql server has to read IAM page first to get the records location and then read the actual data, i.e 2 read to get the data. But in the case of cl index the leaf node contain the row locator and the data, so, only
    1 read (when table scan is in progress). So, table scan is usually faster than clustered scan.

    So, i guess it is always better to add some extra identity column only to get table clustered than have a heap table.
    (if I neglect that special cases when heap is better than clustered, such as staging tables)
    But in some cases clustered index on wide PK would benefit more than adding additional identity column only for clustered purpose(or having a heap table). Which are this cases the mostly?

    One is probably when PK must be part of most of the non-clustered index anyway.

    DB:2.70:Heap Table Vs Clustered With Added Column mm


    Ok, I believe that clustered index is better than heap, since all MVP say so :)
    But what is than the main reason for this?

    I would say it is because SQL Server is geared towards clustered indexes, and heaps are treated left-handedly, like it was something the cat brought it.
    It may be different in different product. In Oracle, it appears that heaps are very much the norm. They have something called index-organised tables, which is a clustered index, but in Oracle circles this is considered an odd creature.
    In the end, in SQL Server, its alright to use a heap if you find that gives you better performance and you understand what you are doing. But if you dont, you should follow the flow and add a clustered index on your table.
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • RELEVANCY SCORE 2.70

    DB:2.70:Unique Constraint Or Unique Index......... p9


    From EXPERT 10G Administration by Sam Alapati

    "Although you can explicitly create unique indexes, Oracle recommends that you not do so. Oracle advises you to use unique constraints instead. When you place a unique constraint on a tables column, Oracle will automatically create unique indexes on those columns."

    Why does oracle advice you to create a UNIQUE CONSTRAINT rather than a UNIQUE INDEX (using CREATE UNIQUE INDEX statement).

    Apart from the automatic creation of an Index, are there any other advantages?

    DB:2.70:Unique Constraint Or Unique Index......... p9

    When you place a unique constraint on a tables column, Oracle will automatically create unique indexes on those columns.This is plain wrong too: Oracle will use an appropriate index for enforcing the constraint if it already exists:

    SQL create table t (a integer)
    /
    Table created.

    SQL alter table t add constraint t_uk unique (a)
    /
    Table altered.

    SQL select index_name,uniqueness from user_indexes where table_name = 'T'
    /
    INDEX_NAME UNIQUENES
    ------------------------------ ---------
    T_UK UNIQUE
    1 row selected.

    SQL drop table t
    /
    Table dropped.

    BUT

    SQL create table t (a integer)
    /
    Table created.

    SQL create index t_uk on t(a)
    /
    Index created.

    SQL alter table t add constraint t_uk unique (a)
    /
    Table altered.

    SQL select index_name,uniqueness from user_indexes where table_name = 'T'
    /
    INDEX_NAME UNIQUENES
    ------------------------------ ---------
    T_UK NONUNIQUE

  • RELEVANCY SCORE 2.70

    DB:2.70:Secondary Index Creation xk



    CAN YOU EXPLAIN ME NONUNIQUE INDEX, AND UNIQUE INDEX

    WHEN CREATION OF SECONDARYINDEX IN ODS.

    DB:2.70:Secondary Index Creation xk


    Hi,

    Check this link:

    http://help.sap.com/saphelp_bw31/helpdata/en/9b/c743f5b40711d194f900a0c929b3c3/frameset.htm

    With rgds,

    Anil Kumar Sharma .P

  • RELEVANCY SCORE 2.70

    DB:2.70:Clustered Index 8c


    I understand that if a column on which clustered index is created is not unique, sql server add uniqueness data to make it unique. How much extra space does the uniqueness data occupy? Will this unique data be added to every row of the column
    or only rows which have duplicate column values?
    Is the behavior how sql server handles this aspect of clustered index, the same in 2005, 2008, and 2012?

    Thanks in advance

    DB:2.70:Clustered Index 8c

    If you define a clustered index as non unique, SQL Server will add a uniquifier which is 4 bytes integer to the clustering key only for rows whereclustering key is a duplicate.
    http://www.mssqltips.com/sqlservertip/2082/understanding-and-examining-the-uniquifier-in-sql-server/

  • RELEVANCY SCORE 2.70

    DB:2.70:Clustered Unique Index 9c


    Create an Index on the fields cus_surname, and cus_firstname. Index Detail: Clustered unique on the combination

    CREATE UNIQUE CLUSTER INDEX index_name
    ON CUSTOMER(CUS_SURNAME, CUS_FIRSTNAME);Error report:
    SQL Error: ORA-00968: missing INDEX keyword
    00968. 00000 - "missing INDEX keyword"

    I dont know what the Syntax for creating a clustered unique index. I've searched google and all i could find was a Unique Index.

    e.g.

    CREATE [UNIQUE] INDEX index_name
    ON table_name (column1, column2, . column_n)
    [ COMPUTE STATISTICS ];

    http://www.techonthenet.com/oracle/indexes.php

    Thanks,
    mc

    DB:2.70:Clustered Unique Index 9c

    So Oracle doesn't have a clustered index?

    Thanks, ill ask the instructor about it

  • RELEVANCY SCORE 2.70

    DB:2.70:Pk Constraint Vs. Clustered/Unique Index Declaration zx


     
    I was wondering what the difference was between creating a primary key constraint (either within the CREATE TABLE or in a separate statement) and creating a clustered/unique index on the same table.  Does the PK have any other behavior?  Thanks!
     
    -Erik

    DB:2.70:Pk Constraint Vs. Clustered/Unique Index Declaration zx

     Daniel Kuppitz wrote:

    A primary key is a clustered index --