When you co-relate Disk Drives with Girls - Funny Joke

I got the below joke from my friend. Its very funny about co-relating Disk Drives with Girls.

HARD-DISK woman: She remembers everything, FOREVER.

RAM woman: She forgets about you, the moment you turn her off.

INTERNET woman: Difficult to access.

SERVER woman: Always busy when you need her.

CD-ROM woman: She is always faster and faster.

EMAIL woman: Every ten things she says, eight are nonsense.

VIRUS woman: Also called “wife”; when you are not expecting her, she comes, installs herself and uses all your resources. If you try to uninstall her you will lose something, if you don’t you will lose everything!!

njoy..

Case Sensitive Search on a Case Insensitive SQL Server

In this post i would like to show you, how to implement case sensitive search on
a case insensitive SQL Server column.


First let me create a temp table:

--------------------------------
create table #EMP
(ID int, Name VARCHAR(30));



Insert into #EMP
Select 1, 'vijaya kadiyala' UNION ALL
Select 2, 'Vijaya Kadiyala' UNION ALL
Select 3, 'VIJAYA KADIYALA';


Now run the below query:


SELECT * FROM #EMP WHERE Name = 'VIJAYA KADIYALA'


This query retrieves all the records as shown in below figure:

The string literal what i passed to the query is in Upper case and i am expecting only the row which satisfies this condition. But by default SQL Server Collation attribute is Case insensitive. So SQL Server ignores the case of the string literal.

You can find the collation information by invoking the following procedure.

SELECT DATABASEPROPERTYEX('AdventureWorks2008', 'Collation')

The default collation property value is SQL_Latin1_General_CP1_CI_AS. The text which is Highlighted in green color indicates Case Insensitive.

Now to convert the query into case sensitive query, just add collation property as shown below.

SELECT * FROM #EMP WHERE Name = 'VIJAYA KADIYALA' COLLATE Latin1_General_CS_AS

Now the query is retruning only one row.

Get Database Traffic Report in SQL Server

My Database team is working on, to find the database traffic on the server. The database is heavily used by the Online appllication and it is 24/7. We would like to see when the database is heavily loaded and see if there are any processes blocking each other which results in slower performance. One option which came to my mind is setting up a server side trace and capture rpc complete and sql batch complete events along with other events. So we decided to run the trace for 1 week during the first week of the month and 1 week during middle of the month and last week of the month because we are very sure that this is the time where the system is heavily loaded.

During this preriod of time i happen to read one article from technet. With the help of this article you can find the follwwing information:

• Determine who's connected to your SQL Server
• Dealing with blocked processes
• Performance killers
• Clues to SQL Server performance problems
  
  Get A Traffic Report: Analyze Your Database Usage With System Tables

Why Upgrade to SQL Server 2008?

With the release of the most recent version of SQL Server, Microsoft has delivered a Trusted, Productive and Intelligent Enterprise Data Platform. There are enhancements and new capabilities engineered into SQL Server 2008 to enable your applications to run better and reduce the amount of time you need to spend managing them. If you have been running existing applications on SQL Server 2000 or 2005, you will find a set of exciting new capabilities to improve your applications and reduce support needs within a familiar management interface. Many of these new features can provide immediate benefits without the need to make application changes.

Key reasons to upgrade to SQL Server 2008

Experience the benefits right away - Significant enhancements ranging from Data and Backup compression to query performance and enhanced database mirroring are available without the need to modify your existing applications.

Enhanced security and auditing – Get data encryption and database auditing capabilities within existing applications.

Improved system management capabilities - Features like policy based server management and new tools such as Performance Data Collection help you effectively manage the growth of your data.

Performance Enhancements – There have been many performance enhancements made throughout the technology stack, including enhancements within Analysis Services, Reporting Services and Integration Services. For example Unisys and Microsoft set a new ETL performance record by loading one terabyte of data in less than 30 minutes using SQL Server 2008 Integration Services. Read about additional performance records.

Predictable System Response – New Features such as query governor and data compression along with general scalability enhancements provide scalable solutions that are more reliable for very large enterprise systems.

Developer productivity - Tools like Entity Framework and LINQ, and new date/time, FILESTREAM and spatial datatypes provide powerful and easy to use application development enhancements.

Enhanced Business Intelligence capabilities – additional reporting capabilities integrated with Microsoft Office applications and a new report designer application allows the creation of enterprise reports without the need for Developer Studio.

Application Compatibility & ease of Migration - There are upgrade tools available from Microsoft to help manage your upgrade from prior versions. Compatibility has been maintained with the majority of functionality which should enable most applications to upgrade seamlessly. Learn more about all the system changes from the prior version, and get started with infrastructure assessment and planning for upgrade to SQL Server 2008.

Mainstream support – With the latest version of SQL Server you can benefit from a long term and current support path. As of April 2008 SQL Server 2000 has moved off Mainstream support to an extended support path.

Consistent pricing and support – Microsoft continues its pricing policies of SQL Server 2005 with some additional improvements. In addition, by participating in Microsoft’s Software Assurance program you are eligible for product upgrades, support and other benefits.

Referece : http://www.microsoft.com/sqlserver/2008/en/us/why-upgrade.aspx

Print from the DataGrid view

I see one question in almost every technical forum. which is how to print the grid view data.

The DataGridView control in .NET is an amazing data representation control, and contains many advanced features that we could benefit from.

Please check out the below link which will give you very detailed information:
http://www.codeproject.com/KB/printing/datagridviewprinter.aspx

Convert PDF to Image Using Ghostscript API

In this post i would like to reference one link to generate image from PDF using external APIs.

You will need at least GhostScript 8.64 (other versions have a problem with Vista). After you have installed the program, just copy the gs32dll.dll from the installation directory (the bin subdirectory) to the directory where you have the EXE of my program.

Check out the below link for complete information:
http://www.codeproject.com/KB/cs/GhostScriptUseWithCSharp.aspx

Convert Rows to Columns with aggregate functions

In todays post i would like to show you a simple way to convert rows into columns with aggregate functions. You can also use PIVOT feature to do the same.

create table #Marks
(ID int, Sub int, Value Int)

Insert into #Marks
Select 1, 1, 10 Union all
Select 1, 2, 20 Union all
Select 1, 3, 25 Union all
Select 1, 4, 50 Union all
Select 2, 1, 15 Union all
Select 2, 2, 25 Union all
Select 2, 3, 30 Union all
Select 2, 4, 49

Select * from #Marks;

SELECT Id,
MAX(CASE WHEN Sub = 1 THEN Value ELSE '' END) AS Subject1,
MAX(CASE WHEN Sub = 2 THEN Value ELSE '' END) AS Subject2,
MAX(CASE WHEN Sub = 3 THEN Value ELSE '' END) AS Subject3,
MAX(CASE WHEN Sub = 4 THEN Value ELSE '' END) AS Subject4
FROM #Marks
GROUP BY Id

Thats it :)

Referece: www.DotNetVJ.com

Qwitter and Twitter based on Newton's Third Law

Now a days Twitter is very famous. Every one says Just follow me on Twitter....


As per Newton's Third Law

For every action, there is an equal and opposite reaction.

When some one is following you on twitter you will get a notification. When some one is leaving you can even get the notification using Qwitter.

Qwitter e-mails you when someone stops following you on Twitter.

http://useqwitter.com/


isn't this kool??

Select the nth best amount with simple query

In this post i would like to show you a simple code, which is used to find the nth best amount with out using any RANK or MAX funcions.

Code is simple and straight forward. You just have to pass the value to the variable @N. If you want to find the TOP amount or highest price of any given product the you need to pass value 1 to the variable @N.

This code works on AdventureWorks2008 database.

DECLARE @N smallint
SET @N = 1
Select distinct PP_o.StandardCost
from Production.Product PP_o where (@N-1) = (select count(distinct(PP_i.StandardCost))
from Production.Product PP_i
WHERE PP_o.StandardCost < PP_i.StandardCost)

Reference: www.DotNetVJ.com

Custom Pagging in SQL Server using ROW_NUMBER Function

This is simple post to implement the pagination in SQL Server using Analytical functions.

-- Variable Declaration
DECLARE @PageLimit smallint, @CurrentPageNumber smallint
SET @PageLimit = 10 -- Setting Page Limit to 10
SET @CurrentPageNumber = 10 -- Current Page Number
DECLARE @StartRow INT
DECLARE @EndRow INT
-- Calculating the starting row
SET @StartRow = ((@CurrentPageNumber-1) * @PageLimit)
-- Calculation the Ending row
SET @EndRow = @StartRow + @PageLimit
-- Query to get the actual records based on Order by Clase
SELECT * FROM
(
SELECT ProductID,
Name,
ProductNumber,
ROW_NUMBER()OVER(ORDER BY Name) AS RowNumber
FROM Production.Product
) v
WHERE v.RowNumber > @StartRow AND v.RowNumber <= @EndRow

SQL Server Vs Oracle

My friend Pinal Dave (http://blog.sqlauthority.com/) posted one very good article on "Differences in Vulnerability between Oracle and SQL Server". This is a kinid of topic which will never end.

Its a very good topic and each one one us has different opinion and also every one should take this in +ve mode.
I will not say which one is better here. Coz each of these are best on their own.
Based on my consulting exprience in US and UK, Database servers are decided based on the following factors:

1) The application which is used to access the tables. If the front-end application is in .net or any of the microsoft products then default database server is SQL Server. If the front-end application is in Java then default database server is oracle.

2) selecting database server is entirely based on the big picture of enterprise architecture. if the firm is using SQL Server right from the begining then they preffer to go with SQL Server than changing the whole architecture.

I know these are not the only factors but these are the two answers i get normally when i review the architecture.

On the other-hand, what i have seen now a days, people are looking more towards vertica or Sybase IQ or Netezza. which are mainly for datawarehouse applications. So i think in the next 2 to 3 years there would be a huge changes to various database architectures.