T-SQL (SS2K8) :: Convert Non-cluster Primary Key To Cluster Unique
Nov 6, 2014
I have following script which i am planning to run to drop all non-clustered primary keys on a database and then created as clustered. I am using someone else's script so don't know how to modify this. Some of primary key columns are used in references in other tables.
is there anyway i can drop the existing primary keys and using their original script then create again as clustered including restoring all foreign and reference keys and unique or no unique.
DECLARE @table NVARCHAR(512), @tablename NVARCHAR(512),
@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX), @sql3 NVARCHAR(MAX),
@column NVARCHAR(MAX);
DECLARE @indexname NVARCHAR(512);
SELECT name As 'Table'
I'm probably going to get shot down with thousands of reasons forthis, but I've never really heard or read a convincing explanation, sohere goes ...Clustered indexes are more efficient at returning large numbers ofrecords than non-clustered indexes. Agreed? (Assuming the NC indexdoesn't cover the query, of course)Since it's only possible to have one clustered index, why is thisalmost always used for the primary key, when by definition a primarykey will always return 1 record?Isn't it generally better to specify a non-clustered index for theprimary key, and reserve the clustered index for a column which willmost likely be used for queries that return multi-row data sets (e.g.date columns)?Also, if you are using a sequential key, clustering this will cause aninsert hotspot on the last page of the table, which can causeconcurrency problems if you aren't using row-level locking. If you'reusing a random clustered key then inserts will generally be improved,assuming you're using a sensible fillfactor, but you still lose theadvantage of using the clustered index for multi-record retrieval.I'd be very interested to hear other peoples' views on this.Phil
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
During the installation of Adding node to a SQL Server failover cluster(On passive node) getting error like.. The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.We run the below commands but didn’t get any resolution & got the same above error .
1<sup>st</sup> Method…
1. Open console command (Run->CMD with administrator privileges).
2. net stop winmgmt
3. Rename folder %windir%System32WbemRepository to other one, for backup purposes (for example _Repository).
I am in the process of moving databases from a SQL 2005 Standard version to a 2-node 2014 cluster.All of my 2005 databases back up successfully.They all restore without issue except for one database that has a full text catalog. I get this message
Msg 7610, Level 16, State 1, Line 2 Access is denied to "fileStoragedataMSSQLSERVERFullTextCatalog", or the path is invalid. Msg 3156, Level 16, State 50, Line 2 File 'sysft_FTCatalog' cannot be restored to 'fileStoragedataMSSQLSERVERFullTextCatalog'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 2 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
[code]....
I went as far as giving the folder full access to everyone temporarily and received the same error.
Can I build a cluster by adding the cluster service, then the SQL instances, then add the other nodes and their passive SQL instances?I would lean to building the cluster first, the add the SQL instances.
With the last table being created below, it has a clustered primary key.One of the fields it is referencing on the previous table, courseId, can NOT be unique.But without it being unique, the cluster primary key won't work.Is there another way to achieve what I am trying to do here?CREATE TABLE dbo.courseScores ( courseId varchar(20) NOT NULL CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId) REFERENCES courseStructure (courseId), studentId varchar(20) NOT NULL CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId) REFERENCES students (studentId), CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId) )
The two SQL Server 2k (SP4) are running on W2K(SP4) in clustered A/A mode. The seconary server took over successfuly as the primary server crached. How to recover the primary sevrer?
We are planning to change all IPs of PRODUCTION Failover Cluster Setup. In my cluster setup ... we have 2 Physical Nodes with windows-2008, Roles are MSDTC and SQL-2008R2.
IP change for:
1. Both Nodes(Physical) 2. MSDTC 3. SQL Server 4. windows Cluster
So Almost... All IPs are going to change.
Im DBA here, I need to take care of SQL cluster and MSDTC. But I haven't performed this activity before.So I'm worrying about Impacts and consequences of this change. steps how should I perform this activity.
We have many tables which have cluster index on column with datatype 'Char(200)'. Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
I am trying to upgrade a SQL Server 6.5(Cluster) to SQL Serevr 7.0 (Cluster)..I already have an intsllation of 7.0(On a Cluster),so this means that 6.5 and sql 7 are on seperate cluster's ,if i try to upgrade from 6.5 Cluster to 7.0 Cluster is asks me to uncluster 6.5 and 7.0 is this correct ,assume i cannot break the cluster then what???.. what is the best way i can achieve this functinality.....
I am wanting to get the job name based on sys.sysProcesses.[Program_name] column. Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?
SELECTCASE WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name ELSE RTRIM([program_name]) END ProgramName , Val1.UqID , Val1.UqIDStr
We are planning to upgrade the SQL Server in our production environment from SQL Server 2000 to SQL Server 2005. This is a 4 Node cluster environment with 3 Databases on 3 Virtual instances. The main requirement is to achieve this with no/minimal downtime.
Could you please suggest or direct me to any documentation for the best practices used to upgrade such an environment?
We have (had) an active/active cluster. 2 physical machines, each running their own instance, clustered together. Node1/Ins1 and Node2/Ins2.
Node2 failed and Ins2 failed over to Node1 as it should. Node2 required that we rebuild the server (rebuild = reinstall O/S). Now we need to get Node2 back into the cluster and get Ins2 failed back over to Node2.
Does anyone know, for certain, the correct steps to accomplish this? Obviously, we could backup everything and completely destroy Ins2 and recreate it on Node2 then rejoin the cluster. But I'm looking for something less destructive.
Is it possible to reinstall SQL, then rejoin the cluster, and then fail Node2 over? Or will there be registry conflictions?
Any help would be appreciated. Also, if you have any links to some official documentation, that would be great too.
We're upgrading a SQL Server 2000 cluster (Active/Passive) running on Windows 2000 Server to a SQL Server 2005 Cluster running on Windows Server 2003. We can't purchase new hardware and we have no spare hardware. We also need to move from Windows 2000 Server to Windows 2003 Server at the same time. We want to keep downtime to a bare minimum.
What we were thinking was the following steps... Anyone try this?
1. Break the link between the servers.
2. Install a fresh copy of windows 2003 server on one side along with SQL Server 2005. While this step is running, the active node would still be live on Windows 2000 Server and SQL Server 2000 serving our customers.
3. Restore a copy of a backup from the active production side to the node we're upgrading and at that point we would bring the active node down, switching the active node to be the newly upgraded server.
4. As a final step, the old active node would now have the link to it broken, we would install a fresh copy of windows 2003 server on it and sql server 2005. At this point we would bring it back into the cluster and the cluster would be complete again.
Need your help and guidence for doing upgrading SQL Server 2000 Cluster to SQL Server 2005 Cluster.
Let me explain my current environment.
1. Currently SQL Server 2000 Cluster environment is running on Windows 2000 Server we need to upgrade this to SQL Server 2005 on Windows 2003 Server. >>> Production environment.
My Plans:
1. On Testing Environment Install SQL Server 2000 cluster on Windows 2003 Server and do a restore of databases from the produciton environment.
2. Upgrade In-Place from SQL Server 2000 Cluster to SQL Server 2005 Cluster.
My doubts
1. Can i install SQL Server 2000 Cluster on Windows 2003 Server. Is it possible or not.
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
I have two Windows 2000 servers (Advance Edition) to form a Windows Cluster. I also install MS SQL 2000 Enterprise Edition on the cluster to form a MS SQL cluster. Now, I want to upgrade the hardware and OS (but keep on using SQL 2000), so I install Windows 2003 server Enterprise Edition on two new servers to form a new Windows Cluster. I am planing to install MS SQL 2000 Enterprise Edition on the new cluster, so the old SQL cluster and new SQL cluster are side by side. I would like to know how to setup a new SQL cluster (I know it has problem to rename SQL Cluster name, so how to fix this problem)? And how to transfer everything (such as system databases, users database, sql user account, password and maintenance plan jobs etc) from old SQL cluster to new SQL cluster? And how to switch over from old SQL cluster to new SQL cluster?
According to microsoft, we can cluster SSIS service but it is NOT RECOMMENDED. http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Now this is the situation that I have where I need to understand how SSIS works?
Enviornment: Active Active cluster enviornment for SQL server with SSIS server installed as stand alone as default on both node.
Name: Node 1 Node 2 --------- -------------- --------------------- Server name: Nd1 Nd2 SQL server name: cs-nd1in01 cs-nd2in02 SSIS server name: Nd1 Nd2
BTW, this is cosolidated enviornment so there are more than one application expected and resides on each instance of SQL server.
The question is around SSIS, what would be the best practice to develop SSIS package that can work with above envoinrment.
Secnario: What if my Nd1 fails. SQL server cs-nd1IN01 will be failover to Nd2 and it will be available. But How about SSIS packages? How that understands to use Nd2 SSIS as Nd1 SSIS is not available. Is anyone has similar experience to setup SSIS in cluster envionrment but as non-cluster service?
Uma writes "Hi Dear, I have A Table , Which Primary key consists of 6 columns. total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
I am trying to configure a 2 node SQL cluster. MSCS is configured and is failing over correctly, however when I start the installation of SQL Server, and specify the IP address of the virtual server and hit next, the installation hangs, and stops responding. I have ran comclust.exe on both nodes and verified that MSDTC shows up in the cluster administrator. I have searched high and low to no avail---
I installed MS SQl 7.0 on MS Cluster 1.0 as Active/Active I have NT Ent with sp6 and Sql 7.0 with Sp1. After two days from the installation one of the SQl Failed to get online we checked in the log I found that the master Database get corrupted. after the investigation I found that the Heart Beat cable was not their.
My question is : can the heart beat cable cause this corruption.
I realize this is like arguing politics or religion, but I have some wide tables that have frequent inserts on. SOme of these tables have millions of rows. I have seen pros and cons of having clustered indexes on tables like these. The one curve ball is that this is a vendor supplied app that I have little control over (ie how they call data is all dynamic sql). I thought the rule was not to cluster index on large tables with frequent indexes, but happy to hear other opinions
i'm trying to rearrange the file locations of the mdf, ndf, ldf in a clustered environment (Active/Passive mode). If i changed the file location on the active mode would it propogate to the passive cluster automatically