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..

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

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

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

List all the Foreign Keys in a SQL Server

The below script will give you list of Foreign Keys in a database and columns which are part of the Foreign key definition.

If you don't know how to add a Foreign key on a table then you need to check out the "Adding Foreign Key on a Table" first.

SELECT

TC.CONSTRAINT_NAME,
TC.TABLE_NAME ,
KCU.COLUMN_NAME,
ORDINAL_POSITION AS COLUMN_POSITION

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
WHERE 1=1
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

There could be several ways to get the same kind of information but from my point of view this is most simple way to do it.

Please let me know if you have a query which is much simple than this.....

List all the Primary Keys in SQL Server Database

The below script will give you list of Primary Keys in a database and columns which are part of the primary key definition.

If you don't know how to add a primary key on a table then you need to check out the "Adding Primary Key on a Table" first.


SELECT

TC.CONSTRAINT_NAME,
TC.TABLE_NAME ,
KCU.COLUMN_NAME,
ORDINAL_POSITION AS COLUMN_POSITION

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

WHERE 1=1
AND TC.TABLE_NAME = 'DEPT'
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

There could be several ways to get the same kind of information but from my point of view this is most simple way to do it.

If you do not want to limit your result set to a table then remove the TABLE_NAME condition.

Please let me know if you have a query which is much simple than this.....

Cursors in SQL Server

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row.

cursors are over thirty times slower than normal SQL queries.

Reference : http://www.sqlteam.com/article/cursors-an-overview

SQL Server 2008

Microsoft SQL Server 2008 is at the heart of a comprehensive data platform that enables you to access and manipulate business-critical data from a variety of diverse devices, platforms, and data services across the enterprise.

Top New Features

Map data structures to business entities by using the new ADO.NET Entity Framework

Use consistent syntax to query diverse data through .NET Language Integrated Query (LINQ) extensions to Microsoft Visual C# and Microsoft Visual Basic.NET

Create occasionally connected solutions using SQL Server 2008 Compact Edition and Microsoft Synchronization Services

Consolidate data storage through the SQL Server 2008 support for relational, XML, Filestream, and geo-location-based data

Get your hands on
http://www.microsoft.com/sqlserver/2008/en/us/app-dev.aspx

What is a join and explain different types of joins

Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.
OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

SQL Query Basics

i have read one good blog which explains basics of query process. Here is the order in which query operators are evaluated. There are 11 levels.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH{CUBE | ROLLUP}
7. HAVING
8. SELECT
9. DISTINCT
10. HAVING
11. TOP

First the query processor reads all the rows from the FROM the left table and apply the ON condition with the right table provided in JOIN.

If there are more JOINs the same is done for all the JOINs.

Then the WHERE clause is applied to filter rows.

Then GROUP BY is done

There WITH clause get evaluated followed by HAVING.

Then the columns are selected. (This is the reason why you cannot use a column alias in WHERE or GROUP BY)

Then DISTINCT clause applied

Then ORDER BY is processed…(This is the reason why you CAN use a column alias in the ORDER BY clause)

Then TOP clause get evaluated.

Reference:
https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-IN&EventID=1032345689&CountryCode=IN

ON UPDATE CASCADE

ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.

ON DELETE CASCADE

ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.

differnce between @@sqlstatus and @@fetch_status

The @@sqlstatus global variable holds status information (warning exceptions) resulting from the execution of a fetch statement. Its value reflects the last cursor fetched.

The @@fetch_status global variable provides information about whether fetch is executed successfully in a scrollable cursor.

Delete two tables data same at a time

How to delete the data from two tables at the same time if they have common column?

There is NO direct method to do this. If the tables are having Parent-Child relationship then you need to delete the Child table first and delete the parent table.

If not you can define Trigger on Parent table and inside the trigger body write your code to delete the data from child table.

Or You can use CASCADE DELETE option at the KEY level to do this.

SELECT Statement Execution in SQL Server

SQL Server uses the following steps to process a single SELECT statement:

1. The parser breaks SELECT statement into logical units, such as keywords, expressions, operators, and identifiers.
2. A query tree is built.
3. The query optimizer then determines the best steps that can return fastest result while consuming the fewest resources.
4. The relational engine begins executing the execution plan, requesting the storage engine to pass up data.
5. The relational engine processes the data returned from the storage engine into the result set format and returns the result set to the client.

Profiling in SQL Server

SQL Profiler captures SQL Server events from a server. The events are saved in a trace file that can be used to analyze and diagnose problem.

The different purposes of using SQL Profiler are:
It is used to find the cause of the problem by stepping through problem queries.
It is very useful to analyze the cause of slow running queries.
It can be used to tune workload of the SQL server.
It also stores security-related actions that can be reviewed by a security administrator.
SQL Profiler also supports auditing the actions performed on instances of SQL Server.

English Query in SQL Server

SQL Server English Query helps to build applications that can accept query in questions form (in English). The application passes the questions string to the English query engine. English Query then returns to the application a SQL statement or a Multi-Dimensional Expression (MDX) query that will return the answer to the user’s question.

Create CLR Stored Procedure

With the latest version of SQL Server 2005, we have the the ability to write CLR from any .Net language.

Lets dig into more details:
I have started leaning C#, so let me show you how to write this code in C#. Please bare with me on the logic :)

Step:
1. Start Visual Studio 2005
2. Add new file
3. Select Class with C#
4. Mark the name as DemoProcCls.cs and write the below lines of code.

using System.Data.SqlServer;
using System.Data.SqlTypes;
public class DemoProcCls
{
public static void DemoProc()
{
SqlPipe sqlPipe = SqlContext.GetPipe();
sqlPipe.Send("Hello World");
}
}

i) Compile the code into assembly
You need to find out the SQL Server path of "sqlaccess.dll".

csc /target:library D:\DemoProcCls.cs /r:"sqlaccess.dll"

ii) Load the above created assembly in SQL with the use of CREATE PROCEDURE
create procedure SampleDemoProc
as external name DemoProcCls.DemoProcCls.DemoProc

and execute the stored procedure as

exec SampleDemoProc

Table Size in SQL Server

SELECT
CASE WHEN (GROUPING(sob.name)=1) THEN 'Total'
ELSE ISNULL(sob.name, 'unknown') END AS 'Table',
SUM(sys.length) AS Byte_Length
FROM sysobjects sob, syscolumns sys
WHERE sob.xtype='u'
AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE