SQL Connection Pooling.

First question that arise on ones mind is why do we need connection pooling at first place ? Answer to this would be clear if we look at what happens in a typical server.

To connect to a server, typically there are many time consuming steps.First, a physical channel such as named pipe must be established, initial handshake with server should occur, connection string needs to be parsed, connection must be authenticated and so on.

Think how badly performance would be hit if these steps needs to be done for every query you do with server. In most practical cases, most application would use only one or few different configurations for connections. Or in other words, during the entire life cycle of application, many identical connections will be repeatedly opened and closed. To minimize this cost of performance, ADO.Net came up with a much optimized technique called Connection Pooling.

The Pooler would maintain ownership of the physical connection. It manages by keeping alive a set of active connections for each given connection configurations.

So whenever a user calls open on a connection, pooler first looks in the available connection pool.If its available, it returns it to caller instead of creating a new connection. When application makes a call to the Close methods, pooler returns the connection to the pooled set instead of closing it.

This ensures that the number of times new connections are opened are reduced many a folds. Please note that only connections with same configurations can be pooled. But ADO.Net can keep several pools at same time for each configurations.

One important point to note in here is that SQLConnection object in .Net, by default as the Connection Pooling enabled. To disable it, you need to use "Pooling = false" in the connection string.

Primary Key

I was trying out something for my hobbylist project and i needed to write a query that would find me the primary key of a given table. The query also needs to check whether the primary key is identity.
Following is the solution I finally made out. It uses the System Information Schema views and columnproperty function to get the required results.

select
ColumnProperty ( object_id('dbo.Table1'),
(SELECT Column_Name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cuJOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcOn cu.Constraint_Name = tc.Constraint_Name Where cu.Table_Name = 'Table1'and tc.Constraint_type = 'PRIMARY KEY'),
'IsIdentity') as IfIdentity

Information Schema Views

How do we get meta data about our sql server ? how do we know what all databases exists, and that are the different tables, stored procedures inside each ?

MS Sql Server provides two ways for doing it.
1. Using System Stored Procedures.
2. Information Schema Views

But owing to difficulties of remembering all the internal stored procedures and thier hierarchies, Information Scheme Views are best recommended for the programmers.

Some of the commonly used quieries on same are.

1. To get all Database in your sql server

select *from information_schema.SCHEMATA

2. To get all tables in a DataBase

select *
from information_schema.tables
where table_catalog = 'MyDb'
and Table_type = 'Base Table'

3. To get all coumns in a Table

select *
from information_schema.columns
where table_catalog = 'Consumables'
and Table_Name = 'viewaccessories'

4. To Get all Stored Procedures in a DB

select *
from information_schema.routines
where routine_type='PROCEDURE'
and Specific_Catalog = 'Consumables'

5. To get parameter list for a stored procedure

select *
from information_schema.parameters
where specific_Name = 'sp_Update_Consumable'
and Specific_Catalog = 'Consumables'

Pranil's Solution

In an earlier post, i had said about a Sql Query.I had used 3 temporary table and it was always going to be a bad solution as far as performance is concerned.

Later in the day, my friend Prani Chacko came up with this solution which is way better , both in performance and simplicity.

select
(
convert(varchar,currentdate) + ' - ' +
convert(varchar,
(select top 1 currentdate from temperature C where c.currentdate > A.currentdate ))
) as DateRange ,
hot-(select hot from temperature as B where B.currentdate= (select top 1 currentdate from temperature D where D.currentdate > A.currentdate ) )
as DiffDate
from temperature as A
where A.currentdate < (select max(currentdate) from temperature)
order by A.currentdate asc

Sql Time

It was one of those rare off days in office when the mailboxes and chat windows were taking place of the .Net IE in my desktop, and my TL thought of giving some job rather than idling out. His requirement was simple at first look but took little more time than expected.

There was a SQL table, Temperature, having two fields , Temp And Date.
------------------------
Date Temp
------------------------
10/1/2006 25
10/2/2006 27
10/3/2006 24
10/4/2006 28
10/5/2006 22

His requirement was that he needs a query that would present as output as
---------------------------------------------
Date Temp
---------------------------------------------
10/1/2006 - 10/2/2006 2
10/2/2006 - 10/3/2006 -3
10/3/2006 - 10/4/2006 4
10/4/2006 - 10/5/2006 -6

It did take some time to get working on this and finally made this query using temporary tables.
------------------
drop table #myTempTable1
drop table #myTempTable2
drop table #myTempTable3

SELECT
[date] AS date,
[temp] AS temp
INTO #myTempTable1
FROM Temperature

SELECT [date] AS Date,
[temp] AS Temp
INTO #myTempTable2
FROM Temperature

Create table #myTempTable3
(
[date] varchar(100),
[temp] int
)

declare @count1 int
declare @count2 int

set @count1 = (select count(*) from #myTempTable1)
set @count2 = (select count(*) from #myTempTable1) - 1
delete from #myTempTable2 Where [date] = (select top 1 [date] from #myTempTable2)

while(@count1>=0 or @count2>=0)
begin

insert into #myTempTable3 ( date,temp)
select top 1
(
Convert(varchar, (select top 1 date from #myTempTable1) )
+ ' - ' +
Convert(varchar, (select top 1 date from #myTempTable2))
) as [Date],
(
(select top 1 temp from #myTempTable2)-(select top 1 temp from #myTempTable1)
) as [Temp]
From #myTempTable1 , #myTempTable2

set @count1 = @count1 - 1
set @count2 = @count2 - 1
delete from #myTempTable1 Where [date] = (select top 1 [date] from #myTempTable1)
delete from #myTempTable2 Where [date] = (select top 1 [date] from #myTempTable2)
end

Select * from #myTempTable3