Skip to main content

Primary keys without Clustered Index ...

As everyone of us know, by default if we create a Primary Key (PK) field in a table it would create Clustered Index automatically. I have been frequently asked by some of blog readers on "Is there a way to create PK fields without clustered Index?". Actually there are three methods by which we can achieve this. Let me give you the sample for both the methods below:

Method 1:

Using this method we can specify it while creating the table schema itself.

Create Table tblTest
(
Field1 int Identity not null primary key nonclustered,
Field2 varchar(30),
Field 3 int null
)
Go

Method 2:

Using this method also we could specify it while creating the table schema. It just depends on your preference.

Create Table tblTest
(
Field1 int Identity not null,
Field2 varchar(30),
Field 3 int null
Constraint pk_parent primary key nonclustered (Field1)
)
Go

Method 3:

If at all you already have a table which have a clustered index on a PK field then you might want to opt for this method.

Step 1: Find the contraint name

sp_helpconstraint tblTest

/*
This way we could find out the constraint name. Lets assume that our constraint name is PK_tblTest_74794A92
*/

Step 2: First drop the existing constaint

Alter table tblTest drop constraint PK_tblTest_74794A92

Step 3: Add the new nonclustered index to that field now

Alter table tblTest add constraint PK_parent1 primary key nonclustered (Field1)

Comments

Popular posts from this blog

Timezone conversion UTC to CST with Daylight Savings

Converting UTC format date to Standard time of CST or EST is straight forward. DECLARE @UTC_Date DATETIME SET @UTC_Date = GETUTCDATE() SELECT @UTC_Date AS [UTC], DATEADD(hh, -6, @UTC_Date) AS [CST - Standard Time], DATEADD(hh, -5, @UTC_Date) AS [EST - Standard Time] But if the given date falls under daylight saving then the above calculation won't work. So how is Daylight saving calculated? 1. If the year <= 2006 then daylight saving is between: 2 am on First Sunday in April till 2 am on Last Sunday in October 2. If the year >= 2007 then daylight saving is between: 2 am on Second Sunday in March till 2 am on First Sunday in November 3. UTC to CST (Standard Time) = -6 4. UTC to CDT (Daylight Time) = -5 5. UTC to EST (Standard Time) = -5 6. UTC to EDT (DayLight Time) = -4 Solution - 1: DECLARE @UTC_Date DATETIME SET @UTC_Date = GETUTCDATE() SELECT @UTC_Date AS [UTC], DATEADD(hh, -6, @UTC_Date) AS [CST - Standard Time], DATEADD(hh, CA

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script". Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it woul

AWS fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request

While using AWS and trying to copy a file from a S3 bucket to my EC2 instance ended up with this error message. Command Used: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ Error: fatal error: An error occurred (400) when calling the HeadObject operation: Bad Request The error goes off if we add the region information to the command statement. I am using Asia Pacific (Mumbai) so used ap-south-1 as the region name. Modified Command: aws s3 cp s3://mybucketname/myfilename.html /var/www/html/ --region ap-south-1