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


ADVERTISEMENT

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

Index With Primary Key

Jun 1, 2008

Hello there,I got a problem when I'm trying to Index a table with PrimaryKeyMy code so far:1    'Create my table2    nonqueryCommand.CommandText = "CREATE TABLE UpdateHistory (id integer IDENTITY(1,1) NOT NULL, version varchar(50) NOT NULL)"3    Console.WriteLine(nonqueryCommand.CommandText)4    Session("Tables") = Session("Tables") + "Number of Rows Affected with table UpdateHistory is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"5    6    'Set column id to Primary Key7    nonqueryCommand.CommandText = "CREATE INDEX idxid ON UpdateHistory (id) With PRIMARY"8    Console.WriteLine(nonqueryCommand.CommandText)9    Session("Tables") = Session("Tables") + "PrimaryKEY - Number of Rows Affected with table UpdateHistory is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"
I do get this error:System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'PRIMARY'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at updates_100_2.Page_Init(Object sender, EventArgs e) in http://server//admin/NewSystem/1.0.0/2.aspx.vb:line 163Note: Line 163 is 9 here..
Anyone who can see what I'm doing wrong?

View 1 Replies View Related

Primary Key Index?

May 17, 1999

Running SQL 6.5, using ActiveX controls(ADO and Datagrid),NT 4.0. All SP's
are current.

I lose the ability to get distinct values with a client-side cursor
after I set up a primary key. Select distinct works perfect before the
primary key creates an index. Any ideas?

View 1 Replies View Related

Index And Primary Key

Dec 9, 2004

By defining a numeric field in table as primary key, will the table be indexed on that particular field?

View 14 Replies View Related

Primary Key Index

Apr 1, 2004

Hi,

I have a table in my SQL 2000 database called utContact, this has a primary called ContactID (int, idenitity), by default this should have a clustered index on it, but when I go to view the indexes on the table it shows the primary key index as non clustered.

How can this be?

SQL server also will not let me change the index to clustered, but I need to as this is causing a lot of table scans in query execution plans.

Any help or advice on this matter would be greatly appreciated.

Cheers

View 7 Replies View Related

Change Primary Index Value

Oct 28, 2006

Is there any way to change the value of a primary key value?

View 10 Replies View Related

Primary Key With Cluster Index

Oct 9, 2007

Hi All,


One of my client having 1 million(nearly) records in a table.
I defined the table as below
1) Created table with one col(we can name it as "ID") having IDENTITY
2) Using "alter table", I created CLUSTERED PRIMARY KEY Constraint on Same field (ID)
3) The Primary key having 2 ref with another 2 tables


Now the issue is when we create or define a primary key (With Clustered Option) automatically cluster Index will be created on defined table

As such table having huge data whenever any updation or insertion against that particular table taking huge amount of time, because the cluster Index trying re-paging whole data. Because of re-paging each and every time "Transaction Log also growing in huge" (database is in full recovery mode and client wants in same mode only)
Data partitioning not posible because whole data related and current live data


I tried following options with vain

1) To Clear transaction log I suggested to take regular log backup's
2) I tried to drop cluster index and tried to implement non clustered index
Drop and re-create index is take taking huge amount of time
Even in this process I have to Re_Index remaining Index's also



Pls give me any other solution or suggestion in this regard

with Thanks & Regards
Bhaskara

View 8 Replies View Related

Help Me Please Error Index - Primary Key

Apr 9, 2008

'PXQ_2' table
- Unable to delete index 'PK_PXQ'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]I/O error (bad page ID) detected during read at offset 0x0000000102e000 in file 'D:MSSQL2000MSSQLxxx.mdf'.

I can't drop Primary Key. How to fix this problem?

Hardware(HDD) no error. I test oke?

Please help me.





-= MIB =-

View 7 Replies View Related

Primary Key Without Clustered Index

Jan 28, 2008

Dear All,
i've read one article that with some option, we can avoid creating clustered index on the primary key column. is ti possible?

how can we create a primary key without allowing sql server to create automaticaly a clustered index?

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 1 Replies View Related

Primary Key With Non-clustered Index

Dec 11, 2007

Dear all,


I want to keep certain archive data in certain tables. One such table is currently about 190 GB in size. It has a primary key with clustered index and three non-clustered indexes. The type of queries fired are strictly selects (daily) and inserts (only monthly).

Question: Is it advisable to have a non-clustered index on the primary key column?.....I am finding that the insert performance is getting hurt due to presence of clustered index on such a large table (190 GB).

Let me know your views.


Regards,

Chetan

View 3 Replies View Related

Rda Dropping Primary Key Index

May 2, 2007

I am using RDA to download a tables

I use TrackingOffWithIndexes



the issue is that when I download a small set of data the primary key index is there



when I download a larger set. the index gets dropped (no error) just slowness when querying the table



the max database size is set to 1024mb and the temp database size is set to 1024mb

the actual db size (with the missing index) is 262 mb... lot's of room left!



when I try to create the index manually after the download in sql server management studio I get this error:



Not enough storage is available to complete this operation

I am using 2GB sd cards and nothing else is on the card.



any ideas?



Regards,



eric [at] westgen com

View 14 Replies View Related

Index Statistics And A Primary Key

Sep 5, 2007

Hi

I have a question regarding updating statistics for a primary key.

Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.

Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?

Hope someone can help

Thanks
James

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

Sysindexes - How To Distinguish Primary Key Index

Apr 22, 2003

indid =1 works, is this the correct way

View 3 Replies View Related

Unique Index On X Columns But Not Primary Key

Feb 23, 2006

my table :

CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]

i must keep ID columns as primary key

ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]


but now A1+A2 must be unique

how can i do it ?

thank you

View 5 Replies View Related

Create Table + Index + Primary

Dec 17, 2006

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]


CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE


thank you

View 6 Replies View Related

SQL Server Takes Index Instead Of Primary Key

Aug 10, 2007

Hallo,

Following scenario:


Table1 with column: Year char(4), Quarter char(1), ID decimal(10,0), Hits int
PK_table1 on Year,Quarter,Hits

If i do a "Select Year From table1 group by Year", the executionplan looks like

select year from dbo.table1 group by year110NULLNULL1NULL4NULLNULLNULL86,21644NULLNULLSELECT0NULL
|--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))121Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL406E-061186,21644[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01
|--Parallelism(Gather Streams, ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))132ParallelismGather StreamsORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL800,028547491186,21643[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))143Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL803,6248431186,18788[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Clustered Index Scan(OBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD)154Clustered Index ScanClustered Index ScanOBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD[Testdb].[dbo].[Table1].[YEAR]1,449936E+0774,588317,9747291182,56304[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11


Now, with another index IX_Hits on hits and the same sql query, sql server now takes IX_Hits instead of PK_table1. And, it takes more time. Any idea why?


select Year from dbo.table1 group by Year110NULLNULL1NULL4NULLNULLNULL85,54985NULLNULLSELECT0NULL
|--Sort(DISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))121SortDistinct SortDISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL40,011261260,0001374511185,54985[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01
|--Parallelism(Gather Streams)132ParallelismGather StreamsNULLNULL800,028507491185,53845[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Hash Match(Partial Aggregate, HASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR]))143Hash MatchPartial AggregateHASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR])NULL8049,63581185,50995[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Index Scan(OBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits]))154Index ScanIndex ScanOBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits])[Testdb].[dbo].[Table1].[YEAR]1,449936E+0727,899427,9747291135,87415[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11



Thank You!!

View 6 Replies View Related

Is Index Created On Primary Or Secondary Key?

Apr 15, 2015

Is index created on only primary key or it can be created on secondary key ?

View 2 Replies View Related

Clustered Index On Composite Primary Key

Jul 23, 2005

Is that possible on SQL Server 2000 and onwards?

View 1 Replies View Related

Question On Primary Key, Unique Index

Oct 8, 2007



Question: I have a test table like this


CREATE TABLE [dbo].[Test](
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[addr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


But when I tried to input my data like
'abc', '123 abc'
'abc ','123 abc'

SQL server won't recognize 'abc' and 'abc ' is a different value if the last character is a space. Is there a way to make it as a different value? I tried to drop the primary and input the data. When I ran a group by the name column, 'abc' show 2 instead of 1. Seems SQL server is trying to ignore the space at the end too.

I also noticed unique index have the same problem too. Please help.

View 5 Replies View Related

Dropping Clustered Index Associated With Primary Key.

Aug 24, 2007

Hi all,


I have a huge table with million of rows, which has ONE Clustered index associated with the PRIMARY KEY, and there are some NON_Clustered indexes.


So,now i decided that, i dont need any more indexes ( not even one) on that table, but i need to maintain primary key on that table.


(a) So, how can i accomplish this (i.e.) having primay key but not having indexes on the table.


Thanks.

View 6 Replies View Related

Transact SQL :: Difference Between Index And Primary Key

Aug 10, 2015

What is the difference between the Index and the Primary Key?

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

How To Keep DataTable's Primary Index In Sync With Database?

May 12, 2008

I'm working with a legacy database whose structure I cannot change.  I'm building a web-based editor for one of the DB's tables.  This table has a Primary Key called "Master_Idx" that is an Identity (autonumber) field.To start, I query the DB and populate a data table which is cached.  This is used to "feed" the web form.  Any additions, changes, and deletions are reflected in this cached table.The user then has the flexibility to press Save at any time, which ensures that the database is modified accordingly.Here's my question:When new records are added, I assign them a Master_Idx value, the first of which was calculated to be 1 + the Max(Master_Idx) value when the editing session started.  But when I insert these records back into the database it's possible that another user might have also been doing similar editing (of different records from the same table), so there's no guarantee that the Master_Idx values in the data table will be identical to those in the DB.  When I insert a record from the cached table back into the DB, what technique can I use to check what value was assigned to the Master_Idx?  If it's not the same as in the cached table then I need to update it locally in a few places.I hope all of this makes sense but if it doesn't then let me know and I'll explain it in another way.Robert W. 

View 2 Replies View Related

How To Return Primary Unique Index Key On Insert

May 27, 2002

Hi,

I am making a program in Visual Basic .NET with SQL Server 2000.

I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...

I must do one of them, but don't know how either of them:

-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.

or

-get the command in Visual Basic that will send the Insert command with a return field ("Id")

Thanks in advance,
Sebastien Anselmo

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

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







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