I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:
Cannot alter the table '' because it is being published for replication.
How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?
I'm changing the collation sequence of a field which is a primary, clustered key field via:
ALTER TABLE [dbo].[clusterAlgorithm] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [ClusterAlgorithmClassName] ) ON [PRIMARY] GO
Is there a way to drop the primary key designation before doing an alter table/alter column statement and then recreating the key, or must I drop and recreate the table?
I am having trouble dropping constraints(Primary and Foreign Keys). I would like to do so so I can truncate the tables and repopulate them. Any time I use the DROP CONSTRAINT #### on one table, I get an error message saying this is referenced in another table. Any help in how to drop the keys so I can truncate the tables in a database would be appreciated. I must be overlooking something simple. Thanks for the help.
The table I am using have a column called Key which is the primary key of the table and a auto number. This primary key is not a foreign key in any other table.
I need to write SQL to drop the current primary key and add a new one Say "RecordId"
as the new primary key and which should be a autonumber too.
I have a copy of this database table and the first thing I noticed was that the Primary Key was pretty much useless and there were no sensible indexes. Every query hitting this table ended up table scanning.So I thought I would try dropping the existing Primary Key constraint and then creating a more natural key that would make data retrieval quicker (hopefully). I understand that creating a clustered index on this table is going to take a long time as ALL the data will need to be reorganised (I estimate this will take at least 1 hour). However, just dropping the existing Primary Key constraint is taking forever.I can see that the server is doing a lot of disk reading/ writing and the wait type in Activity Monitor is PAGEIOLATCH_EX.I would have thought that just dropping a primary key would not change the data in the table, just delete the associated index.
I get a new payroll data file every two weeks. I have a DTS package that drops the table, creates a new table and then adds an index. The SQL task that checks for the exsistence of an index and then drops and creates a new one is failing. Here is the the SQL for the Execute SQL task
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?
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?
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.
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
'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?
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).
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?
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
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.
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.
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.
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")
just i see a database and a table 'tbl_OutBox_MT' where there is now primary key and have index (non unique, non cluster). and it store almost 3000000 data per everyday. and wipe out data from their and archive all data to other location and broadcast this table 'tbl_OutBox_MT' by mobile operator everyday from morning to evening. but when it perform broadcast it to mobile operator it takes huge time. because this table gather data from different sources (tables) by using complex query and INSER INTO statement and insert into this table.
I need to perform first, my observation is there is no primary key. when i run any complex query into this table it takes huge time and sometimes shows transaction deadlock error.
CREATE TABLE [dbo].[tbl_OutBox_MT]( [TRAN_ID] [varchar](36) NOT NULL, [OUT_MSG_ID] [int] IDENTITY(1,1) NOT NULL, [OUT_MSG_ID_TELCO] AS (CONVERT([bigint],((((CONVERT([varchar](4),datepart(year,[PROCESS_TIME]),(0))+case len(CONVERT([varchar](2),datepart(month,[PROCESS_TIME]),(0))) when (1) then '0' else '' end)
