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

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)

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)


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

SQLCMD -- Part VIII (:r and about concatenating string with spaces)

Theory: :r -- parses additional T-SQL statements and sqlcmd commands from the file specified by into the statement cache. In this article we would see the usage of :r as well as handling spaces in SQLCMD. In SQL Mgmt Studio: Step 1: 01VariableInitialization.sql :setvar filepath "C:\Vadivel\SQL Related\Scripts\sqlcmd" :r $(filePath)\02TableCreation.sql Step 2: 02TableCreation.sql Create table tblTest ( Sno int identity, FName varchar(20) ) Go :r $(filePath)\03InsertScripts.sql Step 3: 03InsertScripts.sql Insert into tblTest (Fname) values ('alpha') Explanation of each file: 01VariableInitialization.sql -- In this file we create a scripting variable 'filePath' which will hold the path of the .sql files which we use for this demo. Then it executes 02TableCreation.sql. 02TableCreation.sql -- In this, we create tables of our choice. Also we make use of the scripting variable created in the previous file here to call another .sql file to inse