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

How to pass table as a parameter to SP?

In earlier versions of SQL SERVER before SQL SERVER 2008, it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Steps to pass table value parameter to stored procedure

1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.

Let’s take an example to see how pass table variable as a parameter to stored procedure

First create a table type and define columns in it

create type myTableType as table

(id int not null primary key,

name varchar(12))

Command(s) completed successfully.

Second, Create a stored procedure which will be taking a parameter of type Table ie myTableType

create procedure myProc

@tableVariable myTableType readonly

as

begin

      select * from @tableVariable

end  

Command(s) completed successfully.

Now lets declare a variable of type Table ie myTableType and insert few records in it and execute our procedure by passing the table varible

declare @tableParameter myTableType

insert into @tableParameter values(1,'Roshna')

insert into @tableParameter values(2,'Sanjana')

      

--lets execute the sp by passing table variable as a parameter

exec myProc @tableParameter 

(1 row(s) affected)

id          name

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

1           Roshna

2           Sanjana

(2 row(s) affected)

With table value parameter we can pass complete datable from front end application to the stored procedure, instead of one by one row. Hopefully this solution in SQL Server 2008 will get rid of all the now unnecessary “hacks” that used XML, comma separated lists.

Code it and unwind self  

Post Reference: Vikram Aristocratic Elfin Share

How to Add a NOT NULL column in an existing table? Is it possible, seems not!!! What’s your reaction? Let’s try out

Let stimulate the whole scenario, by taking a table NotNullColumnDemo with two column firstName and lastName

create table NotNullColumnDemo

( firstName varchar(10),

  lastName varchar(10))

Command(s) completed successfully.

Lets insert two row in the table :

insert into NotNullColumnDemo values ('Mythri','Patel')

insert into NotNullColumnDemo values ('Mansha','Patnaik')

(1 row(s) affected)

(1 row(s) affected)

select * from NotNullColumnDemo

firstName  lastName

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

Mythri     Patel

Mansha     Patnaik

(2 row(s) affected)

Now let’s try to add a column salary with a NOT NULL constraint as what our requirement is

alter table NotNullColumnDemo

add salary numeric(7,0) not null

OUTPUT

Msg 4901, Level 16, State 1, Line 1

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'salary' cannot be added to non-empty table 'NotNullColumnDemo' because it does not satisfy these conditions.

Opps!!! Why it is not allowing me to add a new column with NOT NULL constraint???

Reason: Simply, you can't. Because if you could SQL wouldn't know what to put as value in the already existing records. If you don't have any records in the table it would work by the way.

So What to do now, because Manager needs no justification, he want me to do, cant to done work??

Crook Solution: The best way to do this is create the column with a default and then remove the default constraint. Lets fuel conceptual solution through t-sql

alter table NotNullColumnDemo

add salary numeric(7,0) not null

constraint df_salaray default 49000

Command(s) completed successfully.

select * from NotNullColumnDemo

firstName  lastName   salary

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

Mythri     Patel      49000

Mansha     Patnaik    49000

(2 row(s) affected)

alter table NotNullColumnDemo

drop constraint df_salaray

Command(s) completed successfully.

Now you have a column salary with NOT NULL constraint, you can now update the existing record salary column with the correct salary amount.  

Coding is cool, let’s take pleasure in it                 

Post Reference: Vikram Aristocratic Elfin Share

Inserting data into a table that has just one column which is identity type???

If you have a table with one column, which is promoted with an identity column and you were told to insert rows into the table, how you will be performing the command?

Let’s stimulate the case by taking a table OneColumnTable with only one column id

create table OneColumnTable

(id int identity(1,1))

Now try to insert record in this table using insert statement

insert into OneColumnTable values(1)

//OUTPUT

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'OneColumnTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So it is not allowing to insert record in the table, but then you got a task to insert data into this table, how to do the insertion, lets fire the below query

insert into OneColumnTable default values

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

(1 row(s) affected

Note: If you want to continue inserting identity values, then you can make use of default value clause

And, If you want to insert explicit values then you can write

set identity_insert oneColumnTable on

insert into OneColumnTable(id) values(5);

set identity_insert oneColumnTable on

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

5

(2 row(s) affected)

That’s how you can insert values in identity column. J

Put out of your mind, it’s coding; very straightforward J   

Post Reference: Vikram Aristocratic Elfin Share