DB Design :: Creating Appropriate Index For Table?

May 25, 2015

I have a table called as  A table :

CREATE TABLE [dbo].[A](
 [AutoID] [int] IDENTITY(1,1) NOT NULL,
 [ProID] [int] NOT NULL,
 [LID] [varchar](12) NOT NULL,
 [EventID] [varchar](12) NOT NULL,
 [HEventID] [varchar](12) NULL, 
) ON [PRIMARY]   

How I should creating the appropiate index for this table?A few option that I think ok.

Opt 1 : creating a primary key on the autoID with create index . create non clustered index on ProID and EventID
Opt 2 : create a primary key on the autoID with non clustered index . create clustred index on ProID and EventID .
opt 3: create primary key on the ProID and EventID with clustered index.

I have read thru the article on the primary key, clustered and non clustered indexing. However when I want to applyied the indexing..I feel a bit lost.But among the 3 option.... what is the different..

View 5 Replies


ADVERTISEMENT

Creating Index On Table...

Jan 22, 2008

Hi
i am new to using this sql server 2000....this is a very simple question to all u guys.....i am just in a learning stage...so any help from u guys is really appreciable....

i need to create a table customers with the following columns...
identity column to self-populate as the primary key,
joindate, leavedate, custcode, empID.

This is the one i tried:
create table customers (id int primary key identity (1,1) not null,
joindate smalldatetime null,
leavedate smalldatetime null,
custcode varchar (10) not null,
empid int not null
)
is tht code correct only???
and i also want the below one :
Create indexes on the leavedate, custcode and empid columns.

how to create these indexes???
and wht happens when i create them(like is thr any advantage of creating indexes???)

thanks......

View 2 Replies View Related

DB Design :: Re-indexing A Table Using Some Commands From ALTER INDEX REBUILD From Microsoft

May 20, 2015

I'm working on re-indexing a table using some commands from ALTER INDEX REBUILD from Microsoft. The indexes will be tested for threshold fragmentation. my plan is once the reindex is executed, a transaction backup will occur while controlling the size of the log file.  The query impose time limitations or stop reindexing after the specified amount of time has elapsed.

my question,
1. How can I integrate a query which checks if transaction log is getting full and which runs a Tlog backup if over 70%
2. How do I impose time limitation?

View 3 Replies View Related

T-SQL (SS2K8) :: Creating Index On Temp Table

Nov 12, 2014

In a Stored Proc I am creating the following temp table and index:

CREATE TABLE [dbo].[#ShipTo](
[Ship_to_Num] [int] NOT NULL,
[Country_key] [nvarchar](3) NULL,
CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED
(
[ship_to_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.

View 9 Replies View Related

Problem While Creating Index For Temporary Table...

Sep 27, 2007



Hi,

i have created index for a temporary table and this script should used by multiusers.So when second user connecting to it is giving index i mean object already exists.

So what i need is when the second user connected the script should create one more index on temporary table.Will sql server provide any random way of creating indexes if the index exists already with that name??

Thank You,

View 6 Replies View Related

Creating Table Trigger To Maintain FuzzyLookup Index

Apr 2, 2007

Hi,

I've created initial indexes for my table for the fuzzylookup process. I clicked on "Maintained index" but I don't see any triggers created on the reference table.

Do I create the triggers to maintain indexes myself?

Does anybody know how to create these triggers in terms of schema_name, Data_Modification_Statements etc.?

Would it be "Alter index <index name> REBUILD command?



Appreciate the help.

Gulden

View 6 Replies View Related

Data Warehousing :: Creating A Table With Column Store Index?

Sep 26, 2015

I am trying to create a sample table in the Azure SQL  Data warehouse but its giving me a syntax error Incorrect syntax near the keyword 'CLUSTERED'.

CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL

[Code] ....

what's the correct syntax

View 2 Replies View Related

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

Apr 21, 2007

I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?



The table definition is displayed below.



CREATE TABLE [dbo].[AuditLogDetails](

[ID] [int] IDENTITY(1,1) NOT NULL,

[RecordID] [int] NOT NULL,

[TableName] [varchar](64) NOT NULL,

[Modifications] [xml] NOT NULL,

CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]



The view definition is displayed below.



ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING

AS

SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications

FROM dbo.AuditLogParent P

INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1



The definition for UDF f_GetModification



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

returns xml

with schemabinding

as

begin

declare @pidstr varchar(100)

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

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

end





The definition for UDF f_GetIfModificationExist



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

returns Bit

with schemabinding

as

begin

declare @pidstr varchar(100)

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

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

end



The Statement to create the index is below.



CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]

(

[ID] ASC,

[RecordID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

View 1 Replies View Related

DB Design :: Script To Create Table With Primary Key Non-clustered And Clustered Index

Aug 28, 2015

I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...

I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.

CREATE TABLE [dbo].[tblNotificationMgr](
[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
[ContactKey] [int] NOT NULL,
[EventTypeEnum] [tinyint] NOT NULL,

[code]....

View 20 Replies View Related

Design Index Problem

Oct 22, 2007

Hi all,I have a very large table with many columns: dateTime type, nvarchartype and integer field type.A program exec many type of query with where clause.Data field is always in where clause, but some other field is presenttoo. Sometimes integer field, sometimes nvarchar field.Now I must create index for the query!For choose index field what i can do?I must create only index on a datetime field or every combination inevery type of query?In the second case I must create very much index! But this is verydispendious for update/insert/delete operation on the table!!!Some ideas?thnx

View 2 Replies View Related

Index Design Question

Apr 4, 2007

I want to create an index that will cause the cost of the query to be as low as possible and also
must minimize the space that is used by the index. What type of index/parameters I can associate when I create an Index. I already have a clustered index

View 5 Replies View Related

Index Design Question

Apr 25, 2008

When should a composite index be created in favor of multiple single colum indexes?

thanks

View 5 Replies View Related

DB Design :: PK And Clustering Index

Jul 6, 2015

I want to have table with 1 PK and 1 Clustered Index on Column2 and Id, this is my code:

CREATE TABLE [dbo].[Test] (
[Id] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
[Column2] INT NOT NULL,
[Column3] INT NOT NULL,
[Column4] INT NOT NULL,
);
CREATE CLUSTERED INDEX IX_Test ON dbo.Test (Column2, Id);

Error: "Cannot create more than one clustered index on table..."I know, that PRIMARY KEY automatically create Clustered index on Id.Then I manually delete existing Clustered index, and run this code part again:CREATE CLUSTERED INDEX IX_Test ON dbo.Test (Column2, Id);Everything is fine, except I lost my PK on Id...How can I leave PK on Id and create my custom Clustering Index?

View 7 Replies View Related

DB Design :: Nonclustered Index Create

Aug 27, 2015

I have table which having clustered index based on column (A,B,C,D,E,F).Now my query based on B,D,F.  e.g: where b='Test1' and D='test2' and F='test3' Now Execution plan ask to create non clustered index with (B,D,F) column.is it make any sense to create non clustered index where clustered already available.

View 4 Replies View Related

DB Design :: Adding Index Taking More Than 24 Hours

Oct 7, 2015

Here is the DDL of a table that I am trying to add an identity column and create a clustered index. I am using SQL 2012 SE

CREATE TABLE [dbo].[IPF](
[IPFId] [uniqueidentifier] NOT NULL,
[IPId] [uniqueidentifier] NOT NULL,
[FId] [uniqueidentifier] NULL,
[FName] [nvarchar](50) NULL,
[FItemId] [uniqueidentifier] NULL,

[code]....

The table currently has 220million rows. I am trying to add a new identity new column and create a clustered index on it. I ran the script and its been more than 26hrs and its still running. I ensured there is no blocking.

View 7 Replies View Related

DB Design :: Column Store Index And Constraints

Oct 20, 2015

can you have constraints as such [CreateBy] [nvarchar](30) NOT NULL DEFAULT (suser_sname()),on a table that has a column store index in SQL Server 2012,2014, or 2016?

View 3 Replies View Related

Index Design Recommendation - Examine Column Uniqueness

Nov 30, 2005

I am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you

View 1 Replies View Related

DB Design :: Unable To Create Index After Column Resizing

Oct 5, 2015

We have a vendor created database with 9000+ tables, one of which has about 6 billion rows.  The vendor redesigned the database recently and ever since we've had terrible performance.

What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.

Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.

Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.

The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).

There is no additional data, just wider columns. Why we are unable to create the index?

What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?

View 3 Replies View Related

DB Design :: Composite Clustered Index Key Based On Column

Nov 24, 2015

I created composite clustered index key based on Gender and Salary column 

The Query executed Successfully and <g class="gr_ gr_135 gr-alert gr_tiny gr_spell undefined ContextualSpelling multiReplace" data-gr-id="135" id="135">i</g>

got composite index key id Gender(-), Salary I <g class="gr_ gr_310 gr-alert gr_gramm undefined Grammar only-ins replaceWithoutSep" data-gr-id="310" id="310">want</g> know why Gender(-) display like this?

And Gender is <g class="gr_ gr_391 gr-alert gr_spell undefined ContextualSpelling ins-del multiReplace" data-gr-id="391" id="391">nvarchar</g> (20) 

View 2 Replies View Related

Creating An Index Timing Out

Oct 31, 2007

I am creating an index on a table wit 35 million records but I get the error
 'TT_ObjPerformance' table- Unable to create index 'IX_TT_ObjPerformance_CACode'.  Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
How can I get the index created?
ThanksSQL Server newbie

View 1 Replies View Related

Help On Creating A Covering Index????

Apr 2, 2007

Hi,

Trying to optimize a query but not sure what to do. I have this query on which I ran an exec plan,

SET NOCOUNT ON;
SELECT qaTestSuite.TestSuiteID, qaTestSuite.TestSuiteStart, qaTestSuite.TestInterface, qaTestSuite.TestVersion, qaTests.TestMachine, qaTestSuite.TestClientMachine, qaTests.TestLogin, qaTests.TestLabel,
qaTestSuite.TestCLPs, qaTestSuite.TestSuiteEnd, qaTests.TestID, qaTests.TestIDInternal, qaTests.TestStart,
qaTests.TestName, qaTests.TestTier, qaTests.TestNo, qaTests.TestWFBCalled, qaTests.TestWFBTime,
qaTests.TestSearches, qaTests.TestSearchesTime, qaTests.TestResult, qaTests.TestEnd, qaTestMssgs.TestMssgsID,
qaTestMssgs.TestMssgTime, qaTestMssgs.TestMssgType, qaTestMssgs.TestMessage, qaTestSuite.TestMode
FROM qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID INNER JOIN
qaTestMssgs with(NOLOCK) ON qaTests.TestID = qaTestMssgs.TestID
order by qaTestSuite.TestSuiteStart DESC

and it gives me the following results:

Use a Bookmark (RID or Clustering Key) to look up the corresponding row in the Table or Clustered Index.

Physical Op: Bookmark Lookup
Logical Op: Bookmark Lookup
Est. Row Count: 128
Est. Row Size: 4760
Est. I/O Cost: 0.368
Est. CPU Cost: 0.000141
Est. Execs: 1.0
Est. Cost: 0.368888(89%)
Est. Subtree Cost:.415

Argument:
BOOKMARK:([Bmk1004]), OBJECT:([QAMaster].[dbo].[qaTestMssgs]) WITH PREFETCH

I have no idea what to do with that. Anyone have any clues? What I found online was that I should make a Covering Index, but I didn't find
any patterns on how to do that. Any one have ideas of how to do this?

Thanks very much for your help!

--PhB

View 14 Replies View Related

Creating A Unique Index

Jul 20, 2005

HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan

View 3 Replies View Related

The Index Entry For Row ID Was Not Found In Index ID 3, Of Table 357576312

Jul 9, 2004

Hi,

I'm running a merge replication on a sql2k machine to 6 sql2k subscribers.
Since a few day's only one of the merge agents fail's with the following error:

The merge process could not retrieve generation information at the 'Subscriber'.
The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.

All DBCC CHECKDB command's return 0 errors :confused:
I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .

Any ideas as to what is causing this error?

View 3 Replies View Related

Advantages Of Using Nonclustered Index After Using Clustered Index On One Table

Jul 3, 2006

Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??

Thanks

View 4 Replies View Related

Creating Primary INDEX Problem

May 30, 2000

I created table and also I define the primary key its okay

but when I generate the SQL script for that table its not
creating the primary key

CREATE TABLE [dbo].[table1] (
[emp_id] [int] NOT NULL ,
[emp_name] [char] (25) NULL ,
[emp_address] [nvarchar] (50) NULL
) ON [PRIMARY]
GO


PS: I want to use emp_id as primary key but its not defined in the sql script

Thankx a lot

View 3 Replies View Related

Creating Index In DESC Order

Apr 15, 2003

Does anyone have a general rule or guide on when to use this SQL 2000 option when creating indexes? I was thinking generally on nonclustered indexes where the column would be unique and incremental and usually filtered on by range and often used in the order by clause. Such as columns of datetime or integers datatypes. Thanks.

View 1 Replies View Related

Creating And Moving Clustered Index

Dec 20, 2006

have a 3rd party app (can't change) which has some bad sql. I have a table that is used in the sql which if I put a clustered (I had an index on the fields in the sql but it would ignore and table scan) will use and stop doing table scan. this is a million row table that is growing. the data going in is pretty mich insert only. I have a separate array and file group which I have moved indexes to last year. 2 questions

1. If I would make a clustered index on the separate RAID and file group, doesn't the table need to go with it. I thought the clustered index and table had to be on same File Group

2. If I do this anyone see any issues with moving this table and index on this file group

View 2 Replies View Related

Performance Creating Clustered Index

Sep 29, 2005

Hi Guys,

I have a SQL 2000 sp3a server on Windows 2000 sp4. Running dual proc server with hyper threading enabled, 3gb memory attached to a HP EVA 5000 SAN.

One of the tables is 67gb and contains 140,000,000 rows. Recently someone dropped the clustered indexe so i`m trying to put it back (i've dropped the non clustered indexes as no point leaving them there whilst clustered builds).

The problem i am having is the rebuild is taking forever!! It ran for 23 hours before someone rebooted the server (!). The database is currently recovering from the reboot but i need to work out what is causing the appalling performance so i can get the index rebuilt. There are no reported hardware problems.....

There are multiple file groups involved and i found i was getting an extent allocation rate of 1.5 extents a second and same for deallocation.

Any advice on how to trouble shoot this?

View 12 Replies View Related

Creating A Primary Key As A Non Clustered Index

Jul 18, 2007

Hi,



I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO


CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO



The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?


Thanks

Nitin

View 3 Replies View Related

Access Violation Creating Clustered Index

Oct 26, 2000

Hi, Folks!

I'm receiving Access Violation Error when I'm trying to create a clustered index on a datetime field on a table that have around 4 million records, if I create the index nonclustered, no problem, but clustered the system raise this error!

Any help will be appreciate a lot!

Thanks in advance!

Armando Marrero
Cti. Miami

View 1 Replies View Related

Creating Non Clustered Index On DateTime Column

Dec 24, 2014

I have 5 million rows of table, and going to create Non Clustered Index for Datetime values column. Creating Non clustered Index on Datetime value column will affect performance or not.

View 4 Replies View Related

Creating An Index Whilst Extracting Data

Nov 28, 2007



Anyone know the syntax and function to create a self incrementing index whilst extracting data? I'm using the Derived Column tool. The extract will be a full extract everytime, not incremental.
Thanks.

View 4 Replies View Related

Problems Creating Full Text Index

May 17, 2008

Hi all,

Im new to sql and very interreseted in the Full text features, however when im trying to execute the following query:

USE Updater

CREATE FULLTEXT INDEX ON dbo.Servers (ServerName)

KEY INDEX ServerID

ON UpdaterCatalog

WITH CHANGE_TRACKING AUTO

GO

Where ServerID = Int NOT NULL IDENTITY and ServerName = VarChar(255) NOT NULL and UpdaterCatalog is just created

I get the following error:


Msg 7653, Level 16, State 1, Line 3

'ServerID' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, and has maximum size of 900 bytes. Choose another index for the full-text key.


I cant seem to figure out why this wont work since unless im mistaking, both fields are legal.

Note that creating an index on any other table doesn't work either.

Im running Sql server standard edition (32 bits) on VISTA Ultimate X64

Thanks in advance,

Koen

View 8 Replies View Related







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