Creating Primary key without cluster index!!!

Is there a way to create Primary Key without Cluster Index

We all know, when we create a column with primary key, SQL Server automatically create cluster index on primary key field. I have been frequently asked by my friends as well as interviewer; Is it possible to create primary key field without cluster index?

Yes it is quite possible to create primary key field without cluster index in it. Let’s see

1. First we are creating a simple table without any constraint and will see whether any index exist

create table testIndex

(id int,

name varchar(20))

Command(s) completed successfully.

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex')

Table              IndexName   IndexType

------------------------------------------

testIndex           NULL       HEAP

(1 row(s) affected)

As we can see there is no index present in the above table

2. Now let’s try to create a table tableIndex2 with primary key and then will check for index present in the table.

create table testIndex2

(id int primary key,

name varchar(20))

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex2')

Table        IndexName                        IndexType

---------------------------------------------------------

testIndex2   PK__testInde__3213E83F07F6335A   CLUSTERED

(1 row(s) affected)

3. Now we will be trying to create a primary key field without clustered index.

create table testIndex3

(id int primary key nonclustered,

name varchar(20))

select OBJECT_NAME(object_id) as [Table], name as [IndexName],type_desc as [IndexType] from sys.indexes

where object_id = (select object_id from sys.objects where name = 'testIndex3')

Table        IndexName                       IndexType

----------------------------------------------------------

testIndex3   NULL                            HEAP

testIndex3   PK__testInde__3213E83E0BC6C43E  NONCLUSTERED

(2 row(s) affected)

Conclusion: So from this example we can see, that it is quite possible to create table with Primary Key without cluster index.

Put out of your mind and do code- it’s very effortless 

Post Reference: Vikram Aristocratic Elfin Share

Intersect, Inner Join sounds similar…but technicallyyy, is it.. hmm??? let’s find; (Series-I Intersect n Innerjoin)

Intersect operator: it retrieves the common record among the tables, most of time you will see the result of intersect operation is same as inner join query, but we are going to explore where it differs from inner join.

Note: while using Intersect operator the number and order of column must be same in all query as well as the datatype of both right and left query of intersect operator must be compatible.

Lets take an example to see the difference between intersect and inner join.

Here we will be creating two table ServiceProviderContact and ServiceProviderAddress and populate with some dummy data..

create table ServiceProviderContact

(serviceProviderId int identity(1,1) primary key,

serverProviderName varchar(10),

serverProviderCompany varchar(10),

modifiedDate datetime)

create table ServiceProviderAddress

(serviceProviderAddressId int identity(1,1) primary key,

serviceProviderId int references ServiceProviderContact(serviceProviderId),

serviceProviderAddress varchar(15),

modifiedDate datetime)

Now our table is ready, lets populate it with some data..

insert into ServiceProviderContact

values('akshita','ABC Ltd',getdate())

insert into ServiceProviderContact

values('Riddhi','ABC Ltd',getdate())

insert into ServiceProviderContact0

values('Samprada','ABC Ltd',getdate())

insert into ServiceProviderAddress

values(1,'ADI-Paldi', getdate())

insert into ServiceProviderAddress

values(1,'ADI-Sahibaug', getdate())

insert into ServiceProviderAddress

values(2,'ADI-Navragpura', getdate())

select * from ServiceProviderContacts

serviceProviderId serverProviderName serverProviderCompany modifiedDate

----------------- ------------------ --------------------- -----------------------

1                 akshita            ABC Ltd               2012-12-22 12:15:30.843

2                 Riddhi             ABC Ltd               2012-12-22 12:15:30.860

3                 Samprada           ABC Ltd               2012-12-22 12:15:30.860

(3 row(s) affected)

select * from ServiceProviderAddress

serviceProviderAddressId serviceProviderId serviceProviderAddress modifiedDate

------------------------ ----------------- ---------------------- -----------------------

4                        1                 ADI-Paldi              2012-12-22 12:21:10.513

5                        1                 ADI-Sahibaug           2012-12-22 12:21:10.513

6                        2                 ADI-Navragpura         2012-12-22 12:21:10.590

(3 row(s) affected)

Now lets fire intersect operation

select serviceProviderId, convert(varchar(12),modifiedDate,106) from  ServiceProviderAddress

intersect

select serviceProviderId, convert(varchar(12),modifiedDate,106) from  ServiceProviderContact

serviceProviderId

----------------- ------------

1                 22 Dec 2012

2                 22 Dec 2012

(2 row(s) affected)

The output shows the records that are common in both tables. It show only two in the above example.

Now lets try using Inner Join

select c.serviceProviderId, convert(varchar(12),c.modifiedDate,106)

from  ServiceProviderAddress a,ServiceProviderContact c

where  c.serviceProviderId = a.serviceProviderId and convert(varchar(12),c.modifiedDate,106) = convert(varchar(12),a.modifiedDate,106)

serviceProviderId

----------------- ------------

1                 22 Dec 2012

1                 22 Dec 2012

2                 22 Dec 2012

(3 row(s) affected)

The result are different as compared to Intersect Operator, since inner join first perform cartisen product then filter the data according to join condition.

Conclusion : So what we found inner join is similar to interest except it result in duplicate data where as intersect give the common record among left and right table of intersect operator.

Next Article would be: Stimulate Intersect operator using Inner join

Play through code and enjoy…

Post Reference: Vikram Aristocratic Elfin Share

Is it possible to create INDEX on computed column? Let’s try out!!!

It is always good to have computed column to improve the performance in specific scenarios. But in order to improve performance one need to implement INDEX on computed, but creating index on computed column is not as simple as on regular column. There are certain requirements for creating INDEX on computed column.

Let’s table a practical scenario to explain the way to create index on computed column:

The below is the UDF which the computed column will take in use while table creation.

create function sp_calulatePay(@basicPay int, @hra int)

returns int

with schemabinding

as

begin

      return (@basicPay + @hra)

end

Let’s create table with two computed column, the totalPay column will be using UDF sp_calulatePay.

create table ComputedIndexTesting

(name varchar(10),

dob datetime,

dobPlusOneYear as (dateadd(year,1,dob)) persisted,

basicPay int,

hra int,

totalPay as dbo.sp_calulatePay(basicPay,hra)

)

Command(s) completed successfully.

Let’s have some sample data inserted to this table

insert into ComputedIndexTesting (name,dob,basicPay,hra)

select 'Radhika','05/02/1986',1000,500 union all

select 'Vedika','05/03/1986',1000,500 union all

select 'Shyam','05/04/1986',1000,500

(3 row(s) affected)

Lets find out the values of computed column by firing the below select query

select * from ComputedIndexTesting

name       dob                     dobPlusOneYear          basicPay    hra         totalPay

---------- ----------------------- ----------------------- ----------- ----------- -----------

Radhika    1986-05-02 00:00:00.000 1987-05-02 00:00:00.000 1000        500         1500

Vedika     1986-05-03 00:00:00.000 1987-05-03 00:00:00.000 1000        500         1500

Shyam      1986-05-04 00:00:00.000 1987-05-04 00:00:00.000 1000        500         1500

(3 row(s) affected)

Now let’s check our computed columns for index creation using COLUMNPROPERTY ISINDEXABLE

select 

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsIndexable')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'dobPlusOneYear is Indexable ?',

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsIndexable')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'totalPay is Indexable ?'

//OUTPUT

dobPlusOneYear is Indexable ? totalPay is Indexable ?

----------------------------- -----------------------

Yes                           Yes

(1 row(s) affected)

The result shows both the column are indexable.

Now here our task starts, there are two important requirements that may need planning and analysis while creating indexes on computed columns in SQL Server

• ·         Determinism requirements
• ·         Precision requirements

Determinism : we have to make sure that the expression of our computed column is always the same for specific inputs. This can be checked by COLUMNPROPERTY function and IsDeterministic

select 

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsDeterministic')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'dobPlusOneYear is Deterministic ?',

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsDeterministic')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'totalPay is Deterministic ?'

dobPlusOneYear is Deterministic ? totalPay is Deterministic ?

--------------------------------- ---------------------------

Yes                               Yes

(1 row(s) affected)

This show both the computed columns are deterministic.

We can also check our UDF for deterministic by:

SELECT objectproperty(object_id('dbo.sp_calulatePay'),

'IsDeterministic') IsUDFDeterministic

IsUDFDeterministic

------------------

1

(1 row(s) affected)

So we can see UDF is deterministic as well.

NOTE: You need to create UDF with SCHEMA BINDING to make it deterministic. And since we have created our UDF with schema binding, it shows it is deterministic in nature.

Precision requirements: This is solely depends on the data type that are involved in your expression or in UDF for deriving values for your computed column. You can use non-precise value in your computed column like float, real but then you can not create index on such computed column.

Now lets see, whether our computed column are Precise in nature.

select 

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsPrecise')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'dobPlusOneYear is IsPrecise ?',

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsPrecise')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'totalPay is IsPrecise ?'

dobPlusOneYear is IsPrecise ? totalPay is IsPrecise ?

----------------------------- -----------------------

Yes                           Yes

(1 row(s) affected)

So now we are confirmed that both the computed column are Precise as well as Deterministic, so we can create index on computed column, let create the index on it.

create index index_dobPlusOneYear

on dbo.ComputedIndexTesting

(

dobPlusOneYear

)

Command(s) completed successfully.

create index index_totalPay

on dbo.ComputedIndexTesting

(

totalPay

)

Command(s) completed successfully.

Your index is now created on computed column.

Conclusion: For creating index on computed column, two condition should meet first computed column should be:     

• ·         Deterministic
• ·         Precise

Coding: the straightforward among all SDLC  

Post Reference: Vikram Aristocratic Elfin Share