Creating Primary Key And Indexes

Aug 19, 2002

Thanks to help from Ray Maio I have been able to create copies of various tables using the Select Into command.
Ray also responded that this command does does not transfer Primary Key and Index information.

I have attempted to create an index by using the following statement:

create nonclustered index SchoolIndx on wrestlerstest (School)

I did not get an error, but can't tell ift the index was created. The question is, how do I find out if
there is an index for the column School? Is there a command that will tell me. I'm just sending commands
to the server via a short .asp program and don't really know how to obtain a message back. Could I find the information
by browsing the

Thanks again in advance,

Greg Zafros

View 1 Replies

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

Jul 16, 2014

what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:

ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])

What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

View 4 Replies View Related

Indexes In Primary Keys

May 6, 2005

I have a 3rd party app which had a primary key with about 5 fields. The last field of this was a trantype. This app had a posting process which uses this in it's sql. Ran rather slow. We added an individual index to this field and cut processing down 90%. It almost seemded like sl server was ignoring this index. Is this because it was the last field in the primary key index?

View 1 Replies View Related

What Is Diffecence Indexes, Primary Key And Identity.

Mar 12, 2007

What is diffecence indexes, primary key and identity.

View 1 Replies View Related

Clustered Indexes On Primary Keys

Aug 18, 2006

All of the 3 books I've read say it is not a good idea to create a clustered index on the primary key but it is created as the default. My question is has this changed in 2005? My understanding is to create the clustered index on columns used first in join clauses and then in where clauses, what is the answer?

View 14 Replies View Related

Unique Colm Indexes And Primary Keys

Jan 25, 2006

I have a deal table, each of these investments must be unique. I created a int pk : idDeal. Does that make sense or should i just use the deal colm being it has a unique constraint,
Reguarding indexes, should i make the auto # colm my pk and make that the clustered index? and put another index on the Deal Colmn? Any suggestions welcomed

Thank you

View 4 Replies View Related

Creating Indexes

Feb 26, 2001

Just a quick question, Is it better to build an Indexindicies after creating a new table or after populating the table with the data. Which is quicker? What about statistics? This table is dropped/and then recreated on a weekly basis. It is a reporting database.

Thanks.

View 1 Replies View Related

Creating Indexes

Nov 6, 2000

I ran the index tuning wizard and it suggested 4 new indexes w/ a 20% improvement to be gained..

I saved the index creations to a script file and I'm curious if I can implement them at any time or is it better to do that after most of the users are done w/ the system for the day..

Also, any other performance suggestions to help speed up queries? Thanks in advanced..

View 2 Replies View Related

DB Design :: Need To Recreate All Indexes From Primary To Secondary File Group

Jul 14, 2015

I have a database around 500 GB. right now the database have only one data file and one log, it has only one filegroup also.all the indexes and table are placed in Primary Filegroup . we are going to separate them. the planing is to move all the indexes to Secondary filegroup and all the table will be in Primary filegroup.But there will be a problem while implementing it because there are around 600 tables and each table have at least 2 non-clustered index , so is there any way to move all the index to Secondary Filegroup.

View 3 Replies View Related

Transact SQL :: Find Column ADRSCODE Used In Indexes Or Primary Keys?

Oct 12, 2015

I wanted to find all occurrences of ADRSCODE in a Database where ADRSCODE is in either an Index or a Primary Key.

I know how to get all of the occurences of ADRSCODE in a database and the table associated with it, I just want to tack on the Index and/or primary key.

SELECTOBJECT_NAME(object_id)FROMsys.columns
WHEREname
='foo'

How can I get the other bit of information ?

View 2 Replies View Related

Creating Indexes On View

Nov 9, 2000

Hi

I want to know if we can create indexes on Views in SQL 7.0. If yes, then how

Thanks

alsi

View 1 Replies View Related

Creating Clustered Indexes

May 6, 2008

Greetings all,

What's best practice for creating clustered indexes?! Should they be added to a table AFTER it has been populated or should the clustered index be created BEFORE?

Thanks for your advice in advance.

View 4 Replies View Related

Doubt In Creating Indexes-Urgent

Jun 4, 2001

Hi,
I have a question do I have to increase the Transaction Log size to create new indexes after dropping existing one on a table which has 18108360 rows. I need to recreate 5 indexes on that table. As this table is corrupt. Let me know. The corruption was on one of the indexes of this table.
Thanks,
Cynthia

View 1 Replies View Related

Creating Indexes On Physically Separate Drives

Apr 25, 2000

I'll be running SQL v7.0 with raid level 0. The DB will have approx. 250 tables and 1200 indexes.
Can you have the indexes located on a physically separate drive with SQL v7.0 and would this be the best practice?

View 1 Replies View Related

Creating Indexes On Tables From The Database Explorer

Jan 16, 2006

I would like to create an index on a table from the Database Explorer.

I am using Microsoft Visual C# 2005 Express Edition.

I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.

Thanks!

View 1 Replies View Related

SSIS 2000 To 2005 Database Transfer Lost All Primary Keys, Identities And Indexes

Mar 25, 2008

Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.

I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.

What am I missing?

What other ways are there to upgrade a SS2000 DB to 2005?

Thank you.

View 4 Replies View Related

Creating Indexes On Large Table To Increase Performance

Mar 5, 2008

Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I��m using 5 conditions, I can��t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:

CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))

CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))

INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC

INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType

FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID

WHERE RecordedCalls.ID <= '9369907'

AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))

AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)

AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')

AND RecordedCalls.ServerID NOT IN('2')

AND RecordedCalls.AgentID NOT IN('1000010000')

AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))

AND RecordedCalls.IsDeleted='false'

GROUP BY RecordedCalls.CallType

SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')

WHEN 'Arabic' THEN #tempLookups.NameA

ELSE #tempLookups.NameE

END AS CallsType FROM

#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code

DROP TABLE #tempLookups

DROP TABLE #tempTable

Thanks all,
Tayseer

Any suggestions how to create efficient indexes??!!

View 2 Replies View Related

Creating Full-text Indexes On A Table In A Publication

Sep 13, 2006

I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:

Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.

Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!

-mike

View 12 Replies View Related

Creating A Primary Key

Jun 3, 2008

Completely new to SQL so wondered if anyone could help with an issue I'm sure there is a simple answer too.

Need to create a Primary Key for an existing table. Now I have managed to create a new column called _ID, which set the contents to NULL. So populated the column with the number 1 so that I could set it to no null.

Now when I run the query:

ALTER TABLE tablename ADD PRIMARY KEY (_ID);

I get the error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo._DATACAPTURE' and the index name 'PK___DATACAPTURE__145C0A3F'. The duplicate key value is (1).

Which is obviously due to the repeated 1 in _ID.

So I am thinking I just need to run a query that fills the _ID with a unique number.

Any ideas how I do that?

Thanks for your help in advance.

View 5 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 12, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):

1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.

Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???

2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???

View 1 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 16, 2007

View 3 Replies View Related

Creating Composite Primary Key

Feb 2, 2004

Hi

I'm trying to create a composite Primary Key on a table. This is the SQL I've written:

CREATE TABLE BookingItems
(
BookingID INT NOT NULL
REFERENCES Bookings(BookingID),
EquipmentTypeID INT NOT NULL
REFERENCES EquipmentType(EquipmentTypeID),
CONSTRAINT PK_BookingItems_id PRIMARY KEY
(BookingID, EquipmentTypeID)
)

Is this right? I'm trying to define a Primary Key made up of BookingID and EquipmentTypesID, which are both Foreign Keys as defined in the column definition.

Thanks
Jon

View 1 Replies View Related

Creating Table With A Primary Key

Jun 29, 2006

Hi,

I need to create a new table in our database.
This table is not linked into the existing schema in anyway, so i'm not sure if I need a primary key or not.
either way, coudl anyone tell me how to create a primary key ni the CREATE TABLE statement.
I have tried searching but cannot find the answer.

many thanks,
Matt

View 7 Replies View Related

Creating Primary Key With ODBC

Oct 17, 2005

Hi all,I've spent hours trying to find the error in the following SQL 2000command:ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEYCLUSTERED ([PhoneNo]) On [PRIMARY]Every time I try to "Execute" this from my (VB5) ODBC connection I get:Runtime error 3289;Syntax error in CONSTRAINT clauseFor the life of me I can see nothing wrong. I used Enterprise managerto create this statement, and I can create the primary key fine fromthere. The PhoneNo field does not allow NULLs.Everything is service-packed up to date. I have tried using[databasename].[dbo].[ClientList] and suchlike.I've tried to find a relevant manual, but my SQL Server developersguide suggests this should be ok and I can't see anything wrong in thebooks online.Can anybody please help?TIA

View 7 Replies View Related

Creating A Primary Key In A Trigger

Sep 17, 2007

I'd like to create a primary key value (incremental) within a trigger and set it in a primary key column.

Any idea anyone? Do I define my trigger as a On INSERT, Instead of INSERT? I tried both but it doesn't seem I'm doing things right.

View 3 Replies View Related

Creating A Table With A Dual Primary Key

Jul 12, 2004

This question may be a little complicated.

I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:

OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1

Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?

Thanks!!!!!

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

SQL 2005: Creating Custom Primary Key

Mar 27, 2008

Hello everyone!

Even though I am not "new to SQL Server", my experience in working with it has ebbed and flowed, and working with SQL Server is not something that happens for me even on a monthly basis.

I work as an in-house programmer for a financial investment company and I have been asked to work on a database that is to hold financial data.

One of the requirements for the tables has been that the Primary Key should be [name_of_the_table] + [Identity].

So, for example, for table "tblEquities", the values of the Primary Key would be:

PK
-------
tblEquities1
tblEquities2
tblEquities3
tblEquities4
tblEquities5

My questions are:
1) Is it possible to achieve this by using just one column, as in defining a formula for the "Computed Column Specification" , something that would look like "'tblEquities' + Identity()"?
2) If the above is not possible, I assume it's much easier to just define a column as being Identity column and then create a 2nd computed column that would aggregate the name of the table with the value in the Identity column, so that I end up with a column holding the desired values? (I guess it would look something like
"'tblEquities' + [Identity]")

Also, I have been asked to find out what SQL 2005 have/offers, in term of guaranteeing referential integrity. Are the "Database Diagram", as well as the "Relationships" dialog box, the tools that would meet that criteria or does SQL 2005 contain other tools that would help in this regard?

Thank you!

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

Creating Primary Key On Nullable Column

Feb 29, 2008

Hello,

I am trying to recreate a primary key that I dropped in a table....

I'm using a statement like

ALTER TABLE temp WITH NOCHECK add CONSTRAINT PK__tempkey PRIMARY KEY CLUSTERED

(

num,
store
)

But apparently the "store" column was created without a "not null" and it let it create the PK during the table creation but now it won't let me add the PK with that nullable column..

Does anyone know how to get it to use that column to create a primary key after the initial table creation?

Here is the error i get

Cannot define PRIMARY KEY constraint on nullable column in table

Thanks a lot

View 5 Replies View Related

Creating A Primary Key Column In SQL Server 2005

Feb 1, 2008

I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?

One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.

With these information, can some body help me in deciding this?

View 2 Replies View Related

Creating A New Numbered Column As Primary Key In A Table

Jul 23, 2005

This is a fairly simple question, but what is the easiest way to:create a new numbered column (where value is simply the row number) inan existing table and setting it as a primary key?

View 1 Replies View Related

Creating Custom Primary Key In SQL Server 2005

Mar 11, 2008

I currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.

View 3 Replies View Related