Tech Tips

Free calling in Gmail extended through 2011

noreply@blogger.com (Chauthi Drishti) — Mon, 27 Dec 2010 09:00:00 +0000

When we launched calling in Gmail back in August, we wanted it to be easy and affordable, so we made calls to the U.S. and Canada free for the rest of 2010. In the spirit of holiday giving and to help people keep in touch in the new year, we're extending free calling for all of 2011.

In case you haven't tried it yet, dialing a phone number works just like a regular phone. Look for "Call phone" at the top of your Gmail chat list and dial a number or enter a contact's name.

To learn more, visit gmail.com/call. Calling in Gmail is currently only available to U.S. based Gmail users.

Happy New Year and happy calling!

General SQL Server Performance Tuning Tips

noreply@blogger.com (Chauthi Drishti) — Thu, 18 Nov 2010 19:54:00 +0000

When your transaction log grows large and you want a quick way to shrink it, try this option. Change the database recovery mode of the database you want to shrink from “full” to "simple," then truncate the log file by performing a full backup of the database, then switch back to the “full” recovery mode. By temporally changing from the Full recovery model to the Simple recovery mode, and then back, SQL Server will only keep the "active" portion of the log, which is very small. [7.0, 2000, 2005] Contributed by Tom Kitta. Updated 5-7-2007

*****

If you need to delete all the rows in a table, don't use DELETE to delete them, as the DELETE statement is a logged operation and can take a significant amount of time, especially if the table is large. To perform the same task much faster, use the TRUNCATE TABLE instead, which is not a logged operation. Besides deleting all of the records in a table, this command will also reset the seed of any IDENTITY column back to its original value.

After you have run the TRUNCATE TABLE statement, it is important then to manually update the statistics on this table using UPDATE STATISTICS. This is because running TRUNCATE TABLE will not reset the statistics for the table, which means that as you add data to the table, the statistics for that table will be incorrect for a time period. Of course, if you wait long enough, and if you have Auto Update Statistics turned on for the database, then the statistics will eventually catch up with themselves. But this may not happen quickly, resulting in slowly performing queries because the Query Optimizer is using out-of-date statistics. [6.5, 7.0, 2000, 2005] Updated 5-7-2007

*****

If you use TRUNCATE TABLE instead of DELETE to remove all of the rows of a table, TRUNCATE TABLE will not work when there are Foreign Key references present for that table. A workaround is to DROP the constraints before firing the TRUNCATE. Here's a generic script that will drop all existing Foreign Key constraints on a specific table:

CREATE TABLE dropping_constraints
(
cmd VARCHAR(8000)
)

INSERT INTO dropping_constraints
SELECT
'ALTER TABLE [' +
t2.Table_Name +
'] DROP CONSTRAINT ' +
t1.Constraint_Name
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t1
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2
ON
t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
WHERE t2.TABLE_NAME='your_tablename_goes_here'
DECLARE @stmt VARCHAR(8000)
DECLARE @rowcnt INT
SELECT TOP 1 @stmt=cmd FROM dropping_constraints
SET @rowcnt=@@ROWCOUNT
WHILE @rowcnt<>0
BEGIN
EXEC (@stmt)
SET @stmt = 'DELETE FROM dropping_constraints WHERE cmd ='+ QUOTENAME(@stmt,'''')
EXEC (@stmt)
SELECT TOP 1 @stmt=cmd FROM dropping_constraints
SET @rowcnt=@@ROWCOUNT
END
DROP TABLE dropping_constraints

The above code can also be extended to drop all FK constraints in the current database. To achieve this, just comment the WHERE clause. [7.0, 2000] Updated 5-7-2007

*****

Don't run a screensaver on your production SQL Servers, it can unnecessarily use CPU cycles that should be going to your application. The only exception to this is the "blank screen" screensaver, which is OK to use. [6.5, 7.0, 2000, 2005] Updated 5-7-2007

*****

Don't run SQL Server on the same physical server that you are running Terminal Services, or Citrix software. Both Terminal Services and Citrix are huge resource hogs, and will significantly affect the performance of SQL Server. Running the administrative version of Terminal Services on a SQL Server physical server, on the other hand, is OK, and a good idea from a convenience point of view. As is mentioned in other parts of this website, ideally, SQL Server should run on a dedicated physical server. But if you have to share a SQL Server with another application, make sure it is not Terminal Services or Citrix. [7.0, 2000, 2005] Updated 5-7-2007

*****

Use sp_who or sp_who2 (sp_who2 is not documented in the SQL Server Books Online, but offers more details than sp_who) to provide locking and performance-related information about current connections to SQL Server. Sometimes, when SQL Server is very busy, you can't use Enterprise Manager or Management Studio to view current connection activity via the GUI, but you can always use these two commands from Query Analyzer or Management Studio, even when SQL Server is very busy. [6.5, 7.0, 2000, 2005] Updated 5-7-2007

*****

SQL Server 7.0 and 2000 use its own internal thread scheduler (called the UMS) when running in either native thread mode or in fiber mode. By examining the UMS's Scheduler Queue Length, you can help determine whether or not that the CPU or CPUs on the server are presenting a bottleneck.

This is similar to checking the System Object: Processor Queue Length in Performance Monitor. If you are not familiar with this counter, what this counter tells you is how many threads are waiting to be executed on the server. Generally, if there are more than two threads waiting to be executed on a server, then that server can be assumed to have a CPU bottleneck.

The advantage of using the UMS's Schedule Queue Length over the System Object: Processor Queue Length is that it focuses strictly on SQL Server threads, not all of the threads running on a server.

To view what is going on inside the UMS, you can run the following undocumented command:

DBCC SQLPERF(UMSSTATS)

For every CPU in your server, you will get Scheduler. Each Scheduler will be identified with a number, starting with 0. So if you have four CPUs in your server, there will be four Schedulers listed after running the above command, Schedulers ID 0 through 3.

The "num users" tells you the number of SQL threads there are for a specific scheduler.

The "num runnable," or better known as the "Scheduler Queue Length," is the key indicator to watch. Generally, this number will be 0, which indicates that there are no SQL Server threads waiting to run. If this number is 2 or more, this indicates a possible CPU bottleneck on the server. Keep in mind that the values presented by this command are point data, which means that the values are only accurate for the split second when they were captured, and will be always changing. But if you run this command when the server is very busy, the results should be indicative of what is going on at that time. You may want to run this command multiple time to see what is going on over time.

The "num workers" refers to the actual number of worker threads there are in the thread pool.

The "idle workers" refers to the number of idle worker threads.

The "cntxt switches" refers to the number of context switches between runnable threads.

The "cntxt switches(idle)" refers to the number of context switches to "idle" threads.

Denormalization in SQL Server for Fun and Profit

noreply@blogger.com (Chauthi Drishti) — Thu, 18 Nov 2010 19:52:00 +0000

Almost from birth, database developers are taught that their databases must be normalized. In many shops, failing to fully normalize can result in anything from public ridicule to exile to the company’s Siberian office. Rarely discussed are the significant benefits that can accrue from intentionally denormalizing portions of a database schema. Myths about denormalization abound, such as:

A normalized schema is always more stable and maintainable than a denormalized one.
The only benefit of denormalization is increased performance.
The performance increases from denormalization aren’t worth the drawbacks.

This article will address the first two points (I’ll tackle the final point in the second part of this series). Other than for increased performance, when might you want to intentionally denormalize your structure? A primary reason is to “future-proof” your application from changes in business logic that would force significant schema modifications.

Let’s look at a simple example. You’re designing a database for a pizza store. Each customer’s order contains one or more pizzas, and each order is assigned to a delivery driver. In normal form, your schema looks like:

Table: Orders

Customer

Driver

Amount

Table: OrderItems

Order

Pizza Type

Planning Ahead. Let’s say you’ve heard the owner is considering a new delivery model. To increase customer satisfaction, every pizza will be boxed and sent for delivery the moment it comes out of the oven- even if other pizzas in the order are still baking.

Since you’re a savvy developer, you plan for this and denormalize your data structure. Thoughtoday, the driver column is functionally dependent only on the order itself, you cross your fingers, take a deep breath, and violate Second Normal Form by placing it in the OrderItems table. There—you’ve just future-proofed your application. Orders can now have multiple drivers.

Your denormalization has introduced a small update anomaly (if an order’s driver changes, you have to update multiple rows, rather than just one) but if the probability of the delivery model change is large, this is well worth the cost. This is typical when denormalizing, but usually it’s a small problem, and one that can be handled automatically via triggers, constraints, or other means.. For instance, in this case, you can create (or modify the existing) update SP for Orders to cascade the change into OrderItems. Alternatively, you can create an UPDATE trigger on OrderItems that ensures all rows within one order have the same driver. When the rule changes in the future, just remove the trigger—no need to update your tables or any queries that reference them.

Now let’s consider a slightly more complex (and somewhat more realistic) case. Imagine an application to manage student and teacher assignments for an elementary school. A sample schema might be:

Table: Teachers

Teacher (PK)

Classroom

Table: Students

Student (PK)

Teacher (FK)

Planning Ahead. You happen to know that other elementary schools in the region are assigning secondary teachers to some classrooms. You decide to support this in advance within your schema. How would you do it via denormalization? The ugly “repeating groups” solution of adding a “Teacher2” column is one solution, but not one that should appeal to you. Far better to make the classroom itself the primary key, and move teachers to a child table:

Table: Classrooms

Classroom (PK)

Teacher (FK)

Table: Teachers

Teacher (PK)

Classroom (FK)

Table: Students

Student (PK)

Classroom (FK)

As before, this denormalization creates a problem we need to address. In the future, the school may support multiple teachers in one classroom, but today that’s an error. You solve that by the simple expedient of adding a unique constraint on the classroom FK in the teacher’s table. When the business rule changes in the future, you simply remove the constraint. Voila! A far better solution than having to significantly alter your views, queries, and stored procs to conform to a new schema.

Understanding Common Type System in .NET Framework

noreply@blogger.com (Chauthi Drishti) — Tue, 24 Aug 2010 04:49:00 +0000

As .Net Framework is language independent and support over 20 different programming languages, many programmers will write data types in their own programming language.

For example, an integer variable in C# is written as int, whereas in Visual Basic it is written as integer. Therefore in .Net Framework you have single class called System.Int32 to interpret these variables. Similarly, for the ArrayList data type .Net Framework has a common type called System.Collections.ArrayList. In .Net Framework, System.Object is the common base type from where all the other types are derived.

This system is called Common Type System. The types in .NET Framework are the base on which .NET applications, components, and controls are built. Common Type System in .Net Framework defines how data types are going to be declared and managed in runtime. The Common Type System performs the following functions:

• Automatically adapts itself in a framework that enables integration of multiple languages, type safety, and high performance code execution.
• Provides an object-oriented model.
• Standardizes the conventions that all the languages must follow.
• Invokes security checks.
• Encapsulates data structures.

There are two general types of categories in .Net Framework that Common Type System support. They are value types and reference types. Value types contain data and are user-defined or built-in. they are placed in a stack or in order in a structure. Reference types store a reference of the value’s memory address. They are allocated in a heap structure. You can determine the type of a reference by the values of self-describing types. Reference types can be categorized into self-describing types, pointer types, or interface types.

There are many other types that can be defined under Value types and Reference types. In .Net Framework, the System namespace is the root for all the data types. This namespace consists of classes such as Object, Byte, String, and Int32 that represents base data types. These base data types are used by all applications. During runtime a type name can be classified into two: the assembly name and the type’s name within the assembly. The runtime in .Net Framework uses assemblies to find and load types....

Refernece: http://www.dotnet-guide.com/commontype.html

How to configure dbmail in sql server 2005 and 2008

noreply@blogger.com (Chauthi Drishti) — Fri, 30 Jul 2010 09:34:00 +0000

Step 1) Create Profile and Account:

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the

Configure Database Mail context menu of the Database Mail node in Management Node.

This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail,

we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

How to decompile .net DLL or EXE

noreply@blogger.com (Chauthi Drishti) — Fri, 30 Jul 2010 09:00:00 +0000

DIS# is better decompiler for .net.You can get back your code within few minutes by using dis#.

Before purchase dis# take a trial for this.If you need full version of with lifetime membership dis# then please send me a mail to at mangeshgangwar@gmail.com.

Features of dis#

Download DIS# from Here

Internal editor for quick editing

Type new name and press Enter:

The typical problem with decompilation is the absence of full source information in the executable file. For instance, .NET assembly does not contains names of local variables. Program can automatically assign local names in accordance with their types (what Dis# is really do), but it still too differentiates with the original source.

Dis# makes next logical step in this direction. You can edit the names and keep the changes in a project file. ( see screenshot )
Download DIS# from Here
Dis# project file

Dis# have it's own metadata structure, which expands PE metadata structure with all necessary for decompilation information, such as local variable names. You can save Dis# metadata in the project file (extension .dis) and keep all changes.
Decompilation Speed

Custom metadata provides outstanding decompilation speed, which 25-700 times faster then have other .NET decompilers. Dis# decompiles more then 2000 methods per second.
Multiple Languages decompilation

Support for C#, Visual Basic.NET, Delphi.NET and Chrome.
Well formed code

Dis# generates code, which is look like the human edited. Dis# .net decompiler have many options to adjust code view for your preferences.
Optimization

Dis# optimize code.
.NET 2.0 support

Dis# support .NET 2.0 assembly format, generics etc.
Raw Code

In some cases you have to view raw code (before high level decompilation algorithms processing).
Download DIS# from Here

Windows Communication Foundation

noreply@blogger.com (Chauthi Drishti) — Fri, 30 Jul 2010 08:41:00 +0000

Windows communication Foundation a part of the .NET Framework that provides a unified programming model for building service-oriented applications that communicate across the web and the enterprise.

Read the WCF whitepaper

Visual Studio 2010 and .NET Framework 4 are Here!
Visual Studio 2010 and .NET Framework 4 mark the next generation of developer tools from Microsoft. Check it out!

Watch a short video

Get it

Beginner's Guide

Learn more

WS Interoperability between WCF and Metro
Information for .NET Framework and Java Metro developers on creating standards-based Web Services that enable communication between the two platforms. Demonstrates interoperability across a range of WS-I and other scenarios.

New Web Service Interoperability with WCF page on MSDN
We've just released a new page on MSDN where you can get all kinds of information about WS-* interoperability with WCF - How-To White Papers, case studies, and news about Microsoft’s web service interoperability efforts.

Standards-Based Interoperability between SAP NetWeaver and Microsoft .NET Framework
Information for .NET Framework and SAP NetWeaver developers on creating standards-based Web Services that enable communication between the two platforms. Demonstrates simple ping/echo scenarios, as well as a complete end-to-end ERP Purchase order application.

A Developer's Introduction to Windows Communication Foundation (WCF) .NET 4
An overview of the most important new features and improvements in WCF, with enough technical detail and code to help you as a developer understand how to use them. Now updated for the release of .NET 4 and Visual Studio 2010.

New Social API Web Application Toolkit for .NET Web Developers

noreply@blogger.com (Chauthi Drishti) — Fri, 30 Jul 2010 08:29:00 +0000

Latest in this API

We've just published an update to our Web Application Toolkits! Here is a summary of web application toolkits:

Its totally free for .net developers.It help ASP.NET web developers complete common web development tasks and quickly add new features to your apps. Whether it's Bing Maps integration or adding social capabilities to your site, there's a toolkit for you. For the full list of Web Application Toolkits check out this website.

Summary:-

Added a new Social API Web Application Toolkit
Updated all the WATs to be compatible with Visual Studio 2010

Introducing the Social API Web Application Toolkit

As social networking Web sites are becoming more and more popular, users often want to access simultaneously the different networks they belong to from one only entry point. For example, one user might want to post the same message they are posting on your site also on Facebook, Twitter, MySpace and so on.

Although many of these social networks provide APIs for accessing their information, you might want to integrate your Web application with several social sites at the same time and be able to do this in a consistent manner, without having to go into numerous modifications in your code with each new social network that you want to incorporate.

This Web Application Toolkit provides a generic "Social Networks" API that allows connecting your Web application with different social networks and managing them through one entry point with a consistent set of methods. In the Toolkit you'll find examples of how to use the Social Networks API provided to connect a Web application with Facebook and Twitter, allowing you to manage the data provided by these networks in a generic way.

Please notice that this Toolkit includes examples only for reduced set of operations (mainly posting status updates) within the two social networks named before. Through this documentation you'll find instructions on how to extend this set into more operations and more social networks.

More Details:-

This toolkit comes with Facebook and Twitter Providers that allow you to perform tasks against different Social Network APIs in a common way. For example, Facebook and Twitter do authentication in different ways which is a pain because you have to write different code for each network. The Providers mean that you can call common methods and pass in which social networks you want to perform the action against – behind the scenes the providers call the appropriate methods against Facebook or Twitter to get the job done. The provider model also makes it easy to extend the API to other Social Networks in the future – we've provided detailed instructions on how to do this in the documentation that comes with the toolkit download – it's in the "next steps" section.

public ActionResult NetworkLogin(string providerName)
{
var social = new SocialProxy();
return this.Redirect(social.GetProvider(providerName).LoginUrl);
}

The code above shows how you use the SocialProxy class, included in the toolkit to get the login URL for the given social network. In this example we then redirect the user to that URL instead of an MVC view in our application.

The Social Networks API checks if the user is already authenticated in the application and if not it authenticates him by using the FormsAuthentication.SetAuthCookie method. The API maintains a user repository with the account information for each user's social networks.

public bool Login(string providerName, HttpContextBase httpContext)
{
var provider = this.GetProvider(providerName);
var identity = provider.GetLoginIdentity(httpContext);
if (identity == null)
{
return false;
}
if (!httpContext.User.Identity.IsAuthenticated)
{
var userId = this.usersRepository.FindIdentity(identity) ?? this.usersRepository.CreateUser(identity);
FormsAuthentication.SetAuthCookie(userId, false);
}
else
{
var userId = this.usersRepository.FindIdentity(identity);
if (userId != httpContext.User.Identity.Name)
{
this.usersRepository.AssociateIdentity(httpContext.User.Identity.Name, identity);
}
}
return true;
}

Notice that new users do not need to create a separate account when registering on the Web application. The API stores the user's Facebook and Twitter account information, and creates a unique identifier on the user repository for that user to keep them associated.

The API stores the user internal identifier together with the login information for each of its associated social networks, by using the UsersRepository.AssociateIdentity method.

How to decompile any .net DLL

noreply@blogger.com (Chauthi Drishti) — Fri, 09 Jul 2010 08:10:00 +0000

How to decompile any .net DLL

Part -1

Part -2

Part -3

Part -4

Find nth Wor from a table in SQL Server or any row from table

noreply@blogger.com (Chauthi Drishti) — Wed, 07 Jul 2010 06:29:00 +0000

/***************************

FIND NTH ROW FROM A TABLE IN SQL SERVER OR ANY ROW FROM TABLE

BY: MANGESH SINGH

**********************************/

DECLARE @N INT

DECLARE @COUNT INT

SET @N=1

SELECT @COUNT=COUNT(*) FROM TBLAGENTEMPLOYEE

SELECT * FROM(SELECT ROW_NUMBER() OVER (ORDER BY AGENTEMPLOYEEID DESC) AS EID,* FROM TBLAGENTEMPLOYEE) AS AGENTEMPLOYEE

WHERE EID=@COUNT-@N

/*** WHERE NTH=@N */

Cursors in SQL Server 2005

noreply@blogger.com (Chauthi Drishti) — Sat, 19 Jun 2010 13:35:00 +0000

Cursors

In this article, I want to tell you how to create and use server side cursors and how you can optimize a cursor performance.

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

The server side cursors were first added in SQL Server 6.0 release and now supported in all editions of SQL Server 7.0 and SQL Server 2000.

Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources.

Declaring a Cursor

Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.

SQL-92 Syntax

This is SQL-92 Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR select_statement

[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

where

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

INSENSITIVE - specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor.

SCROLL - specifies that cursor can fetch data in all directions, not only sequentially until the end of the result set. If this argument is not specified, FETCH NEXT is the only fetch option supported.

select_statement - the standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.

READ ONLY - specifies that cursor cannot be updated.

UPDATE [OF column_name [,...n]] - specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Cursor Options Compatibility

	INSENSITIVE	SCROLL	READ ONLY	UPDATE
INSENSITIVE		Yes	Yes	No
SCROLL	Yes		Yes	Yes
READ ONLY	Yes	Yes		No
UPDATE	No	Yes	No

Transact-SQL Extended Syntax

This is Transact-SQL Extended Syntax:

DECLARE cursor_name CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR select_statement

[FOR UPDATE [OF column_name [,...n]]]

where

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

LOCAL - specifies that cursor can be available only in the batch, stored procedure, or trigger in which the cursor was created. The LOCAL cursor will be implicitly deallocated when the batch, stored procedure, or trigger terminates.

GLOBAL - specifies that cursor is global to the connection. The GLOBAL cursor will be implicitly deallocated at disconnect.

FORWARD_ONLY - specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported.

STATIC - specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor.

KEYSET - specifies that cursor uses the set of keys that uniquely identify the cursor's rows (keyset), so that the membership and order of rows in the cursor are fixed when the cursor is opened. SQL Server uses a table in tempdb to store keyset. The KEYSET cursor allows updates nonkey values from being made through this cursor, but inserts made by other users are not visible. Updates nonkey values made by other users are visible as the owner scrolls around the cursor, but updates key values made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2.

DYNAMIC - specifies that cursor reflects all data changes made to the base tables as you scroll around the cursor. FETCH ABSOLUTE option is not supported with DYNAMIC cursor.

FAST_FORWARD - specifies that cursor will be FORWARD_ONLY and READ_ONLY cursor. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server.

READ ONLY - specifies that cursor cannot be updated.

SCROLL_LOCKS - specifies that cursor will lock the rows as they are read into the cursor to ensure that positioned updates or deletes made through the cursor will be succeed.

OPTIMISTIC - specifies that cursor does not lock rows as they are read into the cursor. So, the positioned updates or deletes made through the cursor will not succeed if the row has been updated outside the cursor since this row was read into the cursor.

TYPE_WARNING - specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.

select_statement - the standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.

UPDATE [OF column_name [,...n]] - specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Cursor Options Compatibility

(L)

(G)

(FO)

(S)

(K)

(D)

(FF)

(RO)

(SL)

(O)

(TW)

(U)

LOCAL (L)

Yes

GLOBAL (G)

Yes

FORWARD_ONLY (FO)

Yes

STATIC (S)

Yes

KEYSET (K)

Yes

DYNAMIC (D)

Yes

FAST_FORWARD (FF)

Yes

READ_ONLY (RO)

Yes

SCROLL_LOCKS (SL)

Yes

OPTIMISTIC (O)

Yes

TYPE_WARNING (TW)

Yes

UPDATE (U)

Yes

Opening a Cursor

Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax:

OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}

where

GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be opened; otherwise, the global cursor will be opened.

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - the name of a cursor variable that references a cursor.

After a cursor is opening, you can determine the number of rows that were found by the cursor. To get this number, you can use @@CURSOR_ROWS scalar function.

Fetching a Cursor

Once a cursor has been opened, you can fetch from it row by row and make multiple operations on the currently active row in the cursor. To fetch from a cursor, you can use the following syntax:

FETCH

        [    [    NEXT | PRIOR | FIRST | LAST

                | ABSOLUTE {n | @nvar}

                | RELATIVE {n | @nvar}

            FROM

{ { [GLOBAL] cursor_name } | @cursor_variable_name}

[INTO @variable_name[,...n] ]

where

NEXT - the default cursor fetch option. FETCH NEXT returns the next row after the current row.

PRIOR - returns the prior row before the current row.

FIRST - returns the first row in the cursor.

LAST - returns the last row in the cursor.

ABSOLUTE {n | @nvar} - returns the nth row in the cursor. If a positive number was specified, the rows are counted from the top of the data set; if 0 was specified, no rows are returned; if a negative number was specified, the number of rows will be counted from the bottom of the data set.

RELATIVE {n | @nvar} - returns the nth row in the cursor relative to the current row. If a positive number was specified, returns the nth row beyond the current row; if a negative number was specified, returns the nth row prior the current row; if 0 was specified, returns the current row.

GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be fetched; otherwise, the global cursor will be fetched.

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - the name of a cursor variable that references a cursor.

INTO @variable_name[,...n] - allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.

Closing a Cursor

When you have finished working with a cursor, you can close it to release any resources and locks that SQL Server may have used while the cursor was open.
To close a cursor, you can use the following syntax:

CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }

where

GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be closed; otherwise, the global cursor will be closed.

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - the name of a cursor variable that references a cursor.

Note. If you have closed a cursor, but have not deallocated it, you can open it again when needed.

Deallocating a Cursor

When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor.
To deallocate a cursor, you can use the following syntax:

DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}

where

GLOBAL - if this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be deallocated; otherwise, the global cursor will be deallocated.

cursor_name - the name of the server side cursor, must contain from 1 to 128 characters.

cursor_variable_name - the name of a cursor variable that references a cursor.

Note. Deallocating a cursor completely removes all cursor references. So, after a cursor is deallocated, it no longer can be opened.

    +91-9457874019
    +91-522-4102340
    mangeshgangwar@gmail.com
      www.onlineindiamusic.info

Contact Me

Joins in SQL Server 2005

noreply@blogger.com (Chauthi Drishti) — Sat, 19 Jun 2010 13:34:00 +0000

Inner Join:

Inner Join is a default type join of SQL Server. It uses logical operators such as =, <, > to match the records in two tables. Inner Join includes equi join and natural joins.

Outer Join:

Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause.

Types:

o Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple.

o Right Outer Join is exactly the reverse method of Left Outer Join. It returns all the rows from right table and returns null values for the rows having no match in the left joined table.

o Full Outer Join: Full outer join returns all the rows from both left and right joined tables. If there is any match missing from the left table then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table.

Cross Join:

Cross join works as a Cartesian product of rows for both left and right table. It combined each row of left table with all the rows of right table.

    +91-9457874019
    +91-522-4102340
    mangeshgangwar@gmail.com
      www.onlineindiamusic.info

Contact Me

Concept of OOPS

noreply@blogger.com (Chauthi Drishti) — Sat, 19 Jun 2010 13:32:00 +0000

Concept of OOPS

Encapsulation
Abstraction
Inheritance
Polymorphism

Encapsulation:

The wrapping of data and function together in a single unit is called encapsulation. It tell how to bind data.this is useful to hide the data which is the new feature added to oops for providing security. Encapsulation is used for data hiding.

Abstraction:

An abstract class is a parent class that allows inheritance but can never be instantiated. Abstract classes contain one or more abstract methods that do not have implementation. Abstract classes allow specialization of inherited classes. Abstract classes and Interfaces both are used either for design reasons or Security reasons.

Inheritance :

Inheritance is the process of creating new classes, called derived classes, from existing classes.The derived class inherits all the capabilities of the base class, but can add enhancements and refinements of its own.

Polymorphism:

Polymorphism is the ability of an object (or<br>reference) to assume (be replaced by) or become many different forms of object.<br><br>Example: function overloading, function overriding, virtual functions. Another example can be a plus ?+? sign, used for adding two integers or for using it to concatenate two strings.

    +91-9457874019
    +91-522-4102340
    mangeshgangwar@gmail.com
      www.onlineindiamusic.info

Contact Me

Visual Studio 2010 Released

noreply@blogger.com (Chauthi Drishti) — Sat, 19 Jun 2010 13:30:00 +0000

Download Visual Studio 2010

First, if you want it, go download Visual Studio 2010 now. If you're an MSDN Subscriber or WebSiteSpark/BizSpark member, you can download the final release now. If not, you can download a free trial or one of the free Express editions.

Microsoft Visual Studio 2010 Professional
- Web Install
- ISO (DVD-9)
Microsoft Visual Studio 2010 Ultimate
- Web Install
- ISO (DVD-9)
Microsoft Visual Studio Team Foundation Server
- ISO (DVD-9)

I'm running the free Visual Web Developer 2010 Express on my netbook. You can install ASP.NET 4, ASP.NET MVC 2, and Visual Web Developer 2010 Express really quickly with the Web Platform Installer.

There's an excellent page on MSDN that's cherry-picked and categorized the best VS2010 content, but I've included my own list below.

What's new in Visual Studio 2010 and .NET 4?

Buttloads. Here's the things I'm digging most.

What's new in Visual Studio 2010 - The IDE and Editor has really shaped up nicely. I've got it installed side-by-side with by existing VS2008 with no problems. There's hundreds of new things that I can't fit here, although some favorite IDE features of mine are:
- Multimonitor support - You can drag documents or toolboxes out of the IDE and onto other monitors.
- Zoom - You can "ctrl+scroll" (press the Ctrl key while scrolling your mouse wheel) to zoom in editors or diagrams. The editor has been totally rewritten using WPF.
- Navigate To - Hit "ctrl+comma" to navigate around your files, code, variables or methods much faster than Ctrl-F.
- Code-First Intellisense - You can hit Ctrl+Alt+Spacebar to tell toggle intellisense between regular Intellisense and a more TDD-friendly style that lets you create new classes and methods without getting hassled by the editor.
Multi-Targeting Support - You can use VS2010 to create (target) .NET 2.0, 3.0, 3.5 or 4 applications. That means you can work on existing applications and get all the new IDE features while also working on new .NET 4 apps, all with the same IDE.
ASP.NET 4 and ASP.NET MVC 2 are included in the box. WebForms now lets you create clean markup (no more tables) that's semantically correct and CSS-friendly, even for "legacy" controls and clientids that you control. ViewState is way smaller and can be turned on and off with greater granularity. Chart controls are included as well. On the MVC 2 side, we've got Areas, Strongly-typed helpers, Templated Helpers, field validation in models, and more. Both MVC and WebForms get all the core ASP.NET 4 improvements like a smaller web.config, the new <%: %> encoding syntax, extensible output caching, preloading of web apps, session state compression and routing for SEO-friendly URLs.
Windows Communication Foundation (WCF) gets easier with a better configuration experience for services (bothSOAP and REST) as well as new functionality around routing and discovery.
Windows Workflow (WF) gets a massive speed increase, the flowchart services, and it's way easier to make custom services than before. It's all in System.Activities.
Entity Framework 4 and WCF Data Services 4 (OData) - Lots of improvements in the Data space, particularly in the Entity Framework 4. There's POCO support for Self-Tracking Entities, a DDL Generation Provider for creating databases based on a model, as well as lots of improvements to the designer. Don't forget WCF Data Services, I blogged about how easy it is to implement OData recently when I made an API for StackOverflow.
What's new in C# 4 - C# gets a lot of new features in version 4 (or Visual C# 2010 if you like) like Dynamic Support (that's the DLR, built right in), Type Equivalence, and Covariance and Contravariance which makes generics much more flexible.
What's new in Visual Basic 2010 - Jonathan Aneja discusses the coevolution strategy, and new features like Implicit Line Continuation, Statement Lambdas, Auto-Implemented Properties, Collection Initializers and how VB uses the DLR (Dynamic Language Runtime).
New C++ and MFC Features - What? C++? Oh, yes. Pete Brown recently dipped his feet back into C++ with VS2010 and was shocked to find how easy it was to make a Ribbon Application with MFC. In this article, Samit Kumar talks about some of the new C++0x (that's see plus plus oh ex) core language features as well as major improvements in the standard library. VS 2010 enables lambda expressions, the auto keyword, rvalue references, static_assert, nullptr and decltype. It's not your father's C++. Well, maybe it is, if your dad is a ninja.
What's new in WPF - Pete Brown has a fantastic and deep blog post on all the new WPF4 features. There's speed updates, a better designer, Windows 7 and touch support, text is crystal clear (not blurry!), a new datagrid, pixel shaders, the Visual State Manager, WPF Tracing support in VS2010 and more.
MEF, the Managed Extensibility Framework comes with .NET 4. You use MEF to create extensible, compose-able applications. You don't need to download anything, it's integral to the framework and it works anywhere you like, including WinForms, WPF, ASP.NET or Silverlight.
F# is in the box - F# ships with Visual Studio 2010. There's a good Introduction to Functional Programming for .NET Developers you should check out to see how you can use F# and how it will augment and complement your current language of choice.
Concurrency, Threading and Parallelism - Check out the Parallel Computing Dev Center and how PLinq, the Task Parallel Library and the Coordination Data Structures work. Understand how to apply parallel patterns with .NET 4 with this awesome whitepaper. There's also major improvements in the Profiling Tools, including a Concurrency Visualizer for seeing how Multithreaded apps really behave. View their threads and how those threads migrate across cores.
Team Foundation Server - Previously, installing TFS was, ahem, challenging. Today, I've seen people install TFS in 6 minutes with VS2010. Some say they can do it in 3. The point is, it's easy to install now along with lots more new features.

Also, there's a FREE e-Book called "Moving to Visual Studio 2010" that you might want to check out. It's an excerpt of a larger book that'll be coming from MSPress later this summer. It takes a interesting approach as it has three parts, moving from VS2003, moving from VS2005, and moving from VS2008. It's clever, actually. You start in the book on the version that you're currently on. If you're not familiar with versions like VS2008, you start at the beginning. Otherwise, you jump ahead. When you're done, you're ready to move to VS2010.

MSDN and Visual Studio 2010

When a new product launches, MSDN launches with updates and new features of its own. Here's a few things the folks at MSDN have been doing to support the launch.

Better MSDN Search - Most people likely use a search engine to search MSDN, but if you do search from within MSDN, there are a number of new improvements. You can refine by source, saying only search blogs, or only search the library. There's also an OpenSearch provider so you can search the MSDN Library directly from within Windows itself.

MSDN Search also includes Metadata from the results to help you find right thing. For example, if a search turns up a CodePlex project, I can see type-specific details within search results:
MSDN Subscriber Downloads Improvements - There's been lots of UX improvements including as-you-type filtering as well as filtering by platform (x64, etc) and language. I will very likely not need to download Quechua Windows, so now I don't need to see it.
MSDN Library in Lightweight and ScriptFree - You can choose between three flavors of MSDN Library, Classic (the one with the treeview on the side), Lightweight (what I use) or ScriptFree. ScriptFree is great for mobile devices, and it's lightning fast anywhere. Lightweight is the new default and I like it because it features community annotations made to the library prominently on the left side as well as a tabbed interface for code sample languages. I blogged a preview of this work last year and included some charts and graphs showing the improvements in speed worldwide.

How to setup vpn server in windows xp

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 12:38:00 +0000

WindowsXP VPN Server
The following page details the steps necessary to create a WindowsXP VPN Server
1.	Go to Start / Settings / Network Connections
2.	Start the New Connection Wizard

3.	*Click on the Next* button**
4.	Select Set up advanced connection

5.	*Click on the Next* button.**
6.	Click on Accept incoming connections

7.	*Click on the Next* button**
8.	*At the LPT1 page, skip it and just click on the Next* button.**

9.	Click on Allow virtual private connection

10.	*Click on the Next* button**
11.	Add user accounts that you want to be able to connect to your WindowsXP computer.

12.	*Click on the Next* button.**
13.	Highlight Internet Protocol (TCP/IP) and click on Properties

14.	Determine how you want the remote computers to get their IP address

19.	The above example will assign IP addresses to each client. Make sure the IP scheme is the same as on your server.
20.	If the VPN server is behind a router, Port Mapping will need to be done on the router. Standard port usage is 1723 for PPTP. You might also need to configure your router for PPTP Passthrough. Port usage for IPSec is 500, 50-51. These ports will have to be forwarded to the VPN server's IP

Using AT commands to Send and Receive SMS

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 12:33:00 +0000

Using AT commands to Send and Receive SMS

This AT command tutorial is written to support our Teltonika T-ModemUSB, a USB2.0 GSM modem based on the Nokia 12i GSM module - fast EDGE technology is supported. Some of the most popular applications are SMS based telemetry, security and news broadcasting.

Steps using AT commands to send and receive SMS using a GSM modem from a computer

1. Setting up GSM modem
2. Using the HyperTerminal
3. Initial setup AT commands
4. Sending SMS using using AT commands
5. Receiving SMS using using AT commands
6. Using a computer program to send and receive SMS

After succesfully sending and receiving SMS using AT commands via the HyperTerminal, developers can 'port' the ASCII instructions over to their programming environment, eg. Visual Basic, C/C++ or Java and also programmically parse ASCII messages from modem.

1. Setting up your GSM modem

Most GSM modems comes with a simple manual and necessary drivers. To setup your T-ModemUSB, download the USB GSM Modem Quick Start ( Windows ) guide (460kB PDF). You would be able to send SMS from the Windows application and also setup GPRS connectivity. The GSM modem will map itself as a COM serial port on your computer.

Windows based control panel to setup GSM modem, GPRS and send SMS

2. Using the HyperTerminal

Hint :: By developing your AT commands using HyperTerminal, it will be easier for you to develop your actual program codes in VB, C, Java or other platforms.

Go to START\Programs\Accessories\Communications\HyperTerminal (Win 2000) to create a new connection, eg. "My USB GSM Modem". Suggested settings ::

- COM Port :: As indicated in the T-Modem Control Tool
- Bits per second :: 230400 ( or slower )
- Data Bits : 8
- Parity : None
- Stop Bits : 1
- Flow Control : Hardware

You are now ready to start working with AT commands. Type in "AT" and you should get a "OK", else you have not setup your HyperTerminal correctly. Check your port settings and also make sure your GSM modem is properly connected and the drivers installed.

3. Initial setup AT commands

We are ready now to start working with AT commands to setup and check the status of the GSM modem.

AT	Returns a "OK" to confirm that modem is working
AT+CPIN="xxxx"	To enter the PIN for your SIM ( if enabled )
AT+CREG?	A "0,1" reply confirms your modem is connected to GSM network
AT+CSQ	Indicates the signal strength, 31.99 is maximum.

4. Sending SMS using AT commands

We suggest try sending a few SMS using the Control Tool above to make sure your GSM modem can send SMS before proceeding. Let's look at the AT commands involved ..

AT+CMGF=1	To format SMS as a TEXT message
AT+CSCA="+xxxxx"	Set your SMS center's number. Check with your provider.

To send a SMS, the AT command to use is AT+CMGS ..

AT+CMGS="+yyyyy" <Enter>
> Your SMS text message here <Ctrl-Z>

The "+yyyyy" is your receipent's mobile number. Next, we will look at receiving SMS via AT commands.

5. Receiving SMS using AT commands

The GSM modem can be configured to response in different ways when it receives a SMS.

a) Immediate - when a SMS is received, the SMS's details are immediately sent to the host computer (DTE) via the +CMT command

AT+CMGF=1	To format SMS as a TEXT message
AT+CNMI=1,2,0,0,0	Set how the modem will response when a SMS is received

When a new SMS is received by the GSM modem, the DTE will receive the following ..

+CMT : "+61xxxxxxxx" , , "04/08/30,23:20:00+40"
This the text SMS message sent to the modem

Your computer (DTE) will have to continuously monitor the COM serial port, read and parse the message.

b) Notification - when a SMS is recieved, the host computer ( DTE ) will be notified of the new message. The computer will then have to read the message from the indicated memory location and clear the memory location.

AT+CMGF=1	To format SMS as a TEXT message
AT+CNMI=1,1,0,0,0	Set how the modem will response when a SMS is received

When a new SMS is received by the GSM modem, the DTE will receive the following ..

+CMTI: "SM",3	Notification sent to the computer. Location 3 in SIM memory
AT+CMGR=3 <Enter>	AT command to send read the received SMS from modem

The modem will then send to the computer details of the received SMS from the specified memory location ( eg. 3 ) ..

+CMGR: "REC READ","+61xxxxxx",,"04/08/28,22:26:29+40"
This is the new SMS received by the GSM modem

After reading and parsing the new SMS message, the computer (DTE) should send a AT command to clear the memory location in the GSM modem ..

AT+CMGD=3 <Enter> To clear the SMS receive memory location in the GSM modem

If the computer tries to read a empty/cleared memory location, a +CMS ERROR : 321 will be sent to the computer.

6. Using a computer program to send and receive SMS

Once we are able to work the modem using AT commands, we can use high-level programming ( eg. VB, C, Java ) to send the AT ASCII commands to and read messages from the COM serial port that the GSM modem is attached to.

Developer Tip : To speed up development of your solution, download trial versions of SerialTools for C# and VB.NET or SerialTools ActiveX for Windows - with sample serial port ( modem ) reading and writing samples.

Simple ASP.NET 2.0 Tips and Tricks

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 12:18:00 +0000

Simple ASP.NET 2.0 Tips and Tricks

ASP.NET 2.0 is an awesome framework for developing Web applications. If you've worked with it for awhile then that's no secret. It offers some great new features that you can implement with a minimal amount of code. I wanted to start a list of some of the most simple (yet cool) things you could do with it that required little or no C#/VB.NET code. If you have other suggestions add a comment and I'll update the list if the suggestion is a simple task that can be applied easily.

1. Maintain the position of the scrollbar on postbacks: In ASP.NET 1.1 it was a pain to maintain the position of the scrollbar when doing a postback operation. This was especially true when you had a grid on the page and went to edit a specific row. Instead of staying on the desired row, the page would reload and you'd be placed back at the top and have to scroll down. In ASP.NET 2.0 you can simply add the MaintainScrollPostionOnPostBack attribute to the Page directive:

<%@ Page Language="C#" MaintainScrollPositionOnPostback="true" AutoEventWireup="true" CodeFile="..." Inherits="..." %>

2. Set the default focus to a control when the page loads: This is another extremely simple thing that can be done without resorting to writing JavaScript. If you only have a single textbox (or two) on a page why should the user have to click in the textbox to start typing? Shouldn't the cursor already be blinking in the textbox so they can type away? Using the DefaultFocus property of the HtmlForm control you can easily do this.

3. Set the default button that is triggered when the user hits the enter key: This was a major pain point in ASP.NET 1.1 and required some JavaScript to be written to ensure that when the user hit the enter key that the appropriate button on the form triggered a "click" event on the server-side. Fortunately, you can now use the HtmlForm control's DefaultButton property to set which button should be clicked when the user hits enter. This property is also available on the Panel control in cases where different buttons should be triggered as a user moves into different Panels on a page.

4. Locate nested controls easily: Finding controls within a Page's control hierarchy can be painful but if you know how the controls are nested you can use the lesser known "$" shortcut to find controls without having to write recursive code. If you're looking for a great way to recursively find a control (in cases where you don't know the exact control nesting) check out my good buddy Michael Palermo's blog entry. The following example shows how to use the DefaultFocus property to set the focus on a textbox that is nested inside of a FormView control. Notice that the "$" is used to delimit the nesting:

This little trick can also be used on the server-side when calling FindControl(). I blogged about this awhile back if you'd like more details. Here's an example:

TextBox tb = this.FindControl("form1$formVw$txtName") as TextBox;
if (tb != null)
{
//Access TextBox control
}

5. Strongly-typed access to cross-page postback controls: This one is a little more involved than the others, but quite useful. ASP.NET 2.0 introduced the concept of cross-page postbacks where one page could postback information to a page other than itself. This is done by setting the PostBackUrl property of a button to the name of the page that the button should postback data to. Normally, the posted data can be accessed by doing something like PreviousPage.FindControl("ControlID"). However, this requires a cast if you need to access properties of the target control in the previous page (which you normally need to do). If you add a public property into the code-behind page that initiates the postback operation, you can access the property in a strongly-typed manner by adding the PreviousPageType directive into the target page of the postback. That may sound a little confusing if you haven't done it so let me explain a little more.

If you have a page called Default.aspx that exposes a public property that returns a Textbox that is defined in the page, the page that data is posted to (lets call it SearchResults.aspx) can access that property in a strongly-typed manner (no FindControl() call is necessary) by adding the PreviousPageType directive into the top of the page:

<%@ PreviousPageType VirtualPath="Default.aspx" %>

By adding this directive, the code in SearchResults.aspx can access the TextBox defined in Default.aspx in a strongly-typed manner. The following example assumes the property defined in Default.aspx is named SearchTextBox.

TextBox tb = PreviousPage.SearchTextBox;

This code obviously only works if the previous page is Default.aspx. PreviousPageType also has a TypeName property as well where you could define a base type that one or more pages derive from to make this technique work with multiple pages. You can learn more about PreviousPageType here.

6. Strongly-typed access to Master Pages controls: The PreviousPageType directive isn't the only one that provides strongly-typed access to controls. If you have public properties defined in a Master Page that you'd like to access in a strongly-typed manner you can add the MasterType directive into a page as shown next (note that the MasterType directive also allows a TypeName to be defined as with the PreviousPageType directive):

<%@ MasterType VirtualPath="MasterPage.master" %>

You can then access properties in the target master page from a content page by writing code like the following:

this.Master.HeaderText = "Label updated using MasterType directive with VirtualPath attribute.";

You can find several other tips and tricks related to working with master pages including sharing master pages across IIS virtual directories at a previous blog post I wrote.

7. Validation groups: You may have a page that has multiple controls and multiple buttons. When one of the buttons is clicked you want specific validator controls to be evaluated rather than all of the validators defined on the page. With ASP.NET 1.1 there wasn't a great way to handle this without resorting to some hack code. ASP.NET 2.0 adds a ValidationGroup property to all validator controls and buttons (Button, LinkButton, etc.) that easily solves the problem. If you have a TextBox at the top of a page that has a RequiredFieldValidator next to it and a Button control, you can fire that one validator when the button is clicked by setting the ValidationGroup property on the button and on the RequiredFieldValidator to the same value. Any other validators not in the defined ValidationGroup will be ignored when the button is clicked. Here's an example:

<form id="form1" runat="server">

    Search Text: <asp:TextBox ID="txtSearch" runat="server" />

    <asp:RequiredFieldValidator ID="valSearch" runat="Server"
      ControlToValidate="txtSearch" ValidationGroup="SearchGroup" />

    <asp:Button ID="btnSearch" runat="server" Text="Search"
      ValidationGroup="SearchGroup" />
    ....
    Other controls with validators and buttons defined here
</form>

8. Finding control/variable names while typing code: This tip is a bit more related to Visual Studio than to ASP.NET directly, but it's definitely helpful for those of you who remember the first few characters of control variable name (or any variable for that matter) but can't remember the complete name. It also gives me the chance to mention two great downloads from Microsoft. First the tip though. After typing the first few characters of a control/variable name, hit CTRL + SPACEBAR and Visual will bring up a short list of matching items. Definitely a lot easier than searching for the control/variable definition. Thanks to Darryl for the tip. For those who are interested, Microsoft made all of the VS.NET keyboard shortcuts available in a nice downloadable and printable guide. Get the C# version here and the Visual Basic version here.

That's all for now. There are a lot of other things that could be mentioned and I'll try to keep this post updated. Have a great (simple) ASP.NET 2.0 tip or trick? Post the details in the comments and I'll add it if the content is appropriate for the list. Make sure to list your name so I can give proper credit.

For those who are interested, you can also view videos I've put together that show how to accomplish different tasks from working with AJAX, to ASP.NET to Web Services and WCF at the following URL:

http://weblogs.asp.net/dwahlin/archive/tags/Video/default.aspx

SQL Server interview auestions and answers

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 12:14:00 +0000

1. What is normalization? Explain different levels of normalization?

Check out the article Q100139 from Microsoft knowledge base and of
course, there's much more information available in the net. It'll be a
good idea to get a hold of any RDBMS fundamentals text book,
especially the one by C. J. Date. Most of the times, it will be okay
if you can explain till third normal form.

2. What is denormalization and when would you go for it?

As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.

3. How do you implement one-to-one, one-to-many and many-to-many
relationships while designing tables?

One-to-One relationship can be implemented as a single table and
rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into
two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with
the keys from both the tables forming the composite primary key of the
junction table.

It will be a good idea to read up a database designing fundamentals
text book.

4. What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which
they are defined. But by default primary key creates a clustered index
on the column, where are unique creates a nonclustered index by
default. Another major difference is that, primary key doesn't allow
NULLs, but unique key allows one NULL only.

5. What are user defined datatypes and when you should go for them?

User defined datatypes let you extend the base SQL Server datatypes by
providing a descriptive name, and format to the database. Take for
example, in your database, there is a column called Flight_Num which
appears in many tables. In all these tables it should be varchar(8).
In this case you could create a user defined datatype called
Flight_num_type of varchar(8) and use it across all your tables.

See sp_addtype, sp_droptype in books online.

6. What is bit datatype and what's the information that can be stored
inside a bit column?

Bit datatype is used to store boolean information like 1 or 0 (true or
false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0
and there was no support for NULL. But from SQL Server 7.0 onwards,
bit datatype can represent a third state, which is NULL.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table. If the
table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called
composite key.

7. What are defaults? Is there a column to which a default can't be bound?

A default is a value that will be used by a column, if no value is
supplied to that column while inserting data. IDENTITY columns and
timestamp columns can't have defaults bound to them. See CREATE
DEFUALT in books online.

8. What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must
be performed or none. ACID stands for Atomicity, Consistency,
Isolation, Durability. These are the properties of a transaction. For
more information and explanation of these properties, see SQL Server
books online or any RDBMS fundamentals text book.

9. Explain different isolation levels

An isolation level determines the degree of isolation of data between
concurrent transactions. The default SQL Server isolation level is
Read Committed. Here are the other isolation levels (in the ascending
order of isolation): Read Uncommitted, Read Committed, Repeatable
Read, Serializable. See SQL Server books online for an explanation of
the isolation levels. Be sure to read about SET TRANSACTION ISOLATION
LEVEL, which lets you customize the isolation level at the connection
level.

CREATE INDEX myIndex ON myTable(myColumn)

10. What type of Index will get created after executing the above statement?

Non-clustered index. Important thing to note: By default a clustered
index gets created on the primary key, unless specified otherwise.

11. What's the maximum size of a row?

8060 bytes. Don't be surprised with questions like 'what is the
maximum number of columns per table'. Check out SQL Server books
online for the page titled: "Maximum Capacity Specifications".

12. Explain Active/Active and Active/Passive cluster configurations

Hopefully you have experience setting up cluster servers. But if you
don't, at least be familiar with the way clustering works and the two
clusterning configurations Active/Active and Active/Passive. SQL
Server books online has enough information on this topic and there is
a good white paper available on Microsoft site.

13. Explain the architecture of SQL Server

This is a very important question and you better be able to answer it
if consider yourself a DBA. SQL Server books online is the best place
to read about SQL Server architecture. Read up the chapter dedicated
to SQL Server Architecture.

14. What is lock escalation?

Lock escalation is the process of converting a lot of low level locks
(like row locks, page locks) into higher level locks (like table
locks). Every lock is a memory structure too many locks would mean,
more memory being occupied by locks. To prevent this from happening,
SQL Server escalates the many fine-grain locks to fewer coarse-grain
locks. Lock escalation threshold was definable in SQL Server 6.5, but
from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

15. What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets
logged in the transaction log, which makes it slow. TRUNCATE TABLE
also deletes all the rows in a table, but it won't log the deletion of
each row, instead it logs the deallocation of the data pages of the
table, which makes it faster. Of course, TRUNCATE TABLE can be rolled
back.

16. Explain the storage models of OLAP

Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more
infomation.

17. What are the new features introduced in SQL Server 2000 (or the latest
release of SQL Server at the time of your interview)? What changed
between the previous version of SQL Server and the current version?

This question is generally asked to see how current is your knowledge.
Generally there is a section in the beginning of the books online
titled "What's New", which has all such information. Of course,
reading just that is not enough, you should have tried those things to
better answer the questions. Also check out the section titled
"Backward Compatibility" in books online which talks about the changes
that have taken place in the new version.

18. What are constraints? Explain different types of constraints.

Constraints enable the RDBMS enforce the integrity of the database
automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

For an explanation of these constraints see books online for the pages
titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"

19. Whar is an index? What are the types of indexes? How many clustered
indexes can be created on a table? I create a separate index on each
column of a table. what are the advantages and disadvantages of this
approach?

Indexes in SQL Server are similar to the indexes in books. They help
SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes.
When you craete a clustered index on a table, all the rows in the
table are stored in the order of the clustered index key. So, there
can be only one clustered index per table. Non-clustered indexes have
their own storage separate from the table data storage. Non-clustered
indexes are stored as B-tree structures (so do clustered indexes),
with the leaf level nodes having the index key and it's row locater.
The row located could be the RID or the Clustered index key, depending
up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the
query performance, as the query optimizer can choose from all the
existing indexes to come up with an efficient execution plan. At the
same t ime, data modification operations (such as INSERT, UPDATE,
DELETE) will become slow, as every time data changes in the table, all
the indexes need to be updated. Another disadvantage is that, indexes
need disk space, the more indexes you have, more disk space is used.

20. What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide
fault tolerance to database servers. There are six RAID levels 0
through 5 offering different levels of performance, fault tolerance.
MSDN has some information about RAID levels and for detailed
information, check out the RAID advisory board's homepage

21. What are the steps you will take to improve performance of a poor
performing query?

This is a very open ended question and there could be a lot of reasons
behind the poor performance of a query. But some general issues that
you could talk about would be: No indexes, table scans, missing or out
of date statistics, blocking, excess recompilations of stored
procedures, procedures and triggers without SET NOCOUNT ON, poorly
written query with unnecessarily complicated joins, too much
normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance
problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET
STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance
monitor, Graphical execution plan in Query Analyzer.

Download the white paper on performance tuning SQL Server from
Microsoft web site. Don't forget to check out sql-server-performance.com

22. What are the steps you will take, if you are tasked with securing an
SQL Server?

Again this is another open ended question. Here are some things you
could talk about: Preferring NT authentication, using server, databse
and application roles to control access to the data, securing the
physical database files using NTFS permissions, using an unguessable
SA password, restricting physical access to the SQL Server, renaming
the Administrator account on the SQL Server computer, disabling the
Guest account, enabling auditing, using multiprotocol encryption,
setting up SSL, setting up firewalls, isolating SQL Server from the
web server etc.

Read the white paper on SQL Server security from Microsoft website.
Also check out My SQL Server security best practices

23. What is a deadlock and what is a live lock? How will you go about
resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one
piece of data, attempt to acquire a lock on the other's piece. Each
process would wait indefinitely for the other to release the lock,
unless one of the user processes is terminated. SQL Server detects
deadlocks and terminates one user's process.

A livelock is one, where a request for an exclusive lock is
repeatedly denied because a series of overlapping shared locks keeps
interfering. SQL Server detects the situation after four denials and
refuses further shared locks. A livelock also occurs when read
transactions monopolize a table or page, forcing a write transaction
to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL
Server books online. Also check out the article Q169960 from Microsoft
knowledge base.

24. What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock
and a second connection requires a conflicting lock type. This forces
the second connection to wait, blocked on the first.

Read up the following topics in SQL Server books online: Understanding
and avoiding blocking, Coding efficient transactions.

25. Explain CREATE DATABASE syntax

Many of us are used to craeting databases from the Enterprise Manager
or by just issuing the command: CREATE DATABAE MyDB. But what if you
have to create a database with two filegroups, one on drive C and the
other on drive D with log on drive E with an initial size of 600 MB
and with a growth factor of 15%? That's why being a DBA you should be
familiar with the CREATE DATABASE syntax. Check out SQL Server books
online for more information.

26. How to restart SQL Server in single user mode? How to start SQL Server
in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE.
This EXE has some very important parameters with which a DBA should be
familiar with. -m is used for starting SQL Server in single user mode
and -f is used to start the SQL Server in minimal confuguration mode.
Check out SQL Server books online for more parameters and their
explanations.

As a part of your job, what are the DBCC commands that you commonly
use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC,
DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there
are a whole load of DBCC commands which are very useful for DBAs.
Check out SQL Server books online for more information.

27. What are statistics, under what circumstances they go out of date, how
do you update them?

Statistics determine the selectivity of the indexes. If an indexed
column has unique values then the selectivity of that index is more,
as opposed to an index with non-unique values. Query optimizer uses
these indexes in determining whether to choose an index or not while
executing a query.

Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added,
changed, or removed (that is, if the distribution of key values has
changed), or the table has been truncated using the TRUNCATE TABLE
statement and then repopulated
3) Database is upgraded from a previous version

Look up SQL Server books online for the following commands: UPDATE
STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP
STATISTICS, sp_autostats, sp_createstats, sp_updatestats

28. What are the different ways of moving data/databases between servers
and databases in SQL Server?

There are lots of options available, you have to choose your option
depending upon your requirements. Some of the options you have are:
BACKUP/RESTORE, dettaching and attaching databases, replication, DTS,
BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT
scripts to generate data.

29. Explian different types of BACKUPs avaialabe in SQL Server? Given a
particular scenario, how would you go about choosing a backup plan?

Types of backups you can create in SQL Sever 7.0+ are Full database
backup, differential database backup, transaction log backup,
filegroup backup. Check out the BACKUP and RESTORE commands in SQL
Server books online. Be prepared to write the commands in your
interview. Books online also has information on detailed
backup/restore architecture and when one should go for a particular
kind of backup.

30. What is database replicaion? What are the different types of
replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on
the same or different servers. SQL Server supports the following types
of replication scenarios:

    * Snapshot replication
    * Transactional replication (with immediate updating subscribers,
with queued updating subscribers)
    * Merge replication

See SQL Server books online for indepth coverage on replication. Be
prepared to explain how different replication agents function, what
are the main system tables used in replication etc.

31. How to determine the service pack currently installed on SQL Server?

The global variable @@Version stores the build number of the
sqlservr.exe, which is used to determine the service pack installed.
To know more about this process visit SQL Server service packs and
versions.

32. What are cursors? Explain different types of cursors. What are the
disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See
books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor,
it results in a network roundtrip, where as a normal SELECT query
makes only one rowundtrip, however large the resultset is. Cursors are
also costly because they require more resources and temporary storage
(results in more IO operations). Furthere, there are restrictions on
the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of
cursors. Here is an example:

33. If you have to give a flat hike to your employees using the following
criteria:

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each
employee's salary and update his salary according to the above
formula. But the same can be achieved by multiple update statements or
can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

Another situation in which developers tend to use cursors: You need to
call a stored procedure when a column in a particular row meets
certain condition. You don't have to use cursors for this. This can be
achieved using WHILE loop, as long as there is a unique key to
identify each row. For examples of using WHILE loop for row by row
processing, check out the 'My code library' section of my site or
search for WHILE.

34. Write down the general syntax for a SELECT statements covering all the
options.

Here's the basic syntax: (Also checkout SELECT in books online for
advanced syntax).

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]

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

For more information see pages from books online titled: "Join
Fundamentals" and "Using Joins".

36. Can you have a nested transaction?

Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and
@@TRANCOUNT

37. What is an extended stored procedure? Can you instantiate a COM object
by using T-SQL?

An extended stored procedure is a function within a DLL (written in a
programming language like C, C++ using Open Data Services (ODS) API)
that can be called from T-SQL, just the way we call normal stored
procedures using the EXEC statement. See books online to learn how to
create extended stored procedures and how to add them to SQL Server.

Yes, you can instantiate a COM (written in languages like VB, VC++)
object from T-SQL by using sp_OACreate stored procedure. Also see
books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty,
sp_OADestroy. For an example of creating a COM object in VB and
calling it from T-SQL, see 'My code library' section of this site.

38. What is the system function to get the current user's user id?

USER_ID(). Also check out other system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

39. What are triggers? How many triggers you can have on a table? How to
invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed
automatically when an INSERT, UPDATE or DELETE operation takes place
on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for
INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0
onwards, this restriction is gone, and you could create multiple
triggers per each action. But in 7.0 there's no way to control the
order in which the triggers fire. In SQL Server 2000 you could specify
which trigger fires first or fires last using sp_settriggerorder

Triggers can't be invoked on demand. They get triggered only when an
associated action (INSERT, UPDATE, DELETE) happens on the table on
which they are defined.

Triggers are generally used to implement business rules, auditing.
Triggers can also be used to extend the referential integrity checks,
but wherever possible, use constraints for this purpose, instead of
triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification
operation happens. So in a way, they are called post triggers. But in
SQL Server 2000 you could create pre triggers also. Search SQL Server
2000 books online for INSTEAD OF triggers.

Also check out books online for 'inserted table', 'deleted table' and
COLUMNS_UPDATED()

There is a trigger defined for INSERT operations on a table, in an
OLTP system. The trigger is written to instantiate a COM object and
pass the newly insterted rows to it for some custom processing. What
do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time consuming process and since you
are doing it from within a trigger, it slows down the data insertion
process. Same is the case with sending emails from triggers. This
scenario can be better implemented by logging all the necessary data
into a separate table, and have a job which periodically checks this
table and does the needful.

What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of
the same table will be joined in the query. Here is an example:
Employees table which contains rows for normal employees as well as
managers. So, to find out the managers of all the employees, you need
a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid

Here's an advanced query using a LEFT OUTER JOIN that even returns the
employees without managers (super bosses)

SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid

how to decompile c# dll and exe..net decompiler

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 08:51:00 +0000

Decompiler features

Support generics introduced by .NET Framework 2.0,
recognize all .NET language constructs, such as attributes, properties, events, fields, methods and nested types,
automitically recognize different compilers, and generate corresponding target languages, such as C#, managed C++, Visual Basic.NET,and thus salamander is C# decompiler, VB.NET Decompiler, VC++.NET Decompiler and J# Decompiler
support unsafe codes and pointer arithmetics, support fixed statement,
generate Visual Studio .NET project file for easy recompilation,
support .h and .cpp file generation for MC++. Advanced dependency analysis enables the correct .h files to be included in a .cpp file,
comments are generated along with the source codes when API documentation is available,
recognize all CIL instructions, handles address type instructions correctly,
robust and thoroughly tested against as many .NET assemblies as we can find, more than 10,000 classes have been successfully decompiled,

Download dot net decompiler from here

Inplace Editor

Double click or press Enter:

Type new name and press Enter:

The typical problem with decompilation is the absence of full source information in the executable file. For instance, .NET assembly does not contains names of local variables. Program can automatically assign local names in accordance with their types (what Dis# is really do), but it still too differentiates with the original source.

Dis# makes next logical step in this direction. You can edit the names and keep the changes in a project file. ( see screenshot )
Dis# project file

Dis# have it's own metadata structure, which expands PE metadata structure with all necessary for decompilation information, such as local variable names. You can save Dis# metadata in the project file (extension .dis) and keep all changes.
Decompilation Speed

Custom metadata provides outstanding decompilation speed, which 25-700 times faster then have other .NET decompilers. Dis# decompiles more then 2000 methods per second.
Multiple Languages decompilation

Support for C#, Visual Basic.NET, Delphi.NET and Chrome.
Well formed code

Dis# generates code, which is look like the human edited. Dis# .net decompiler have many options to adjust code view for your preferences.
Optimization

Dis# optimize code.
.NET 2.0 support

Dis# support .NET 2.0 assembly format, generics etc.
Raw Code

In some cases you have to view raw code (before high level decompilation algorithms processing).

Download dot net decompiler from here

           Mangesh Singh
    +91-9457874019
    +91-522-4102340
    mangeshgangwar@gmail.com
      www.onlineindiamusic.info

Contact Me

Windows XP Genuine Patch,Windows XP Genuine Ceack,How to activate Windows XP Genuine

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 08:24:00 +0000

This is the newest Windows Genuine Advantage from Microsoft

''LegitCheckControl.dll Notification v1.5.708.0'' NEW

Microsoft has come out with a new ''Windows Genuine Advantage Notification v1.5.708.0 released 22 September 2006.

Also included WGA nag screen removal file.

(WindowsXP KB905474-1.5.554.0.)

Windows Genuine Advantage Validation v1.5.554.0 new added on 2 october 2006.

and some instructions for this latest Windows Genuine Advantage Notification.

INSTALL NOTES :

Before applying this close all applications & do the below step

1. click ''LegitCheckControl .DLL Validation v1.5.708.0'' button & follow install notes...

100% working on Windows Update

and no nag windows :)

Restart your computer & Open ''Genuine validation status Tool'' again and you will see the ''Genuine validation status'' will be changed & your

Windows XP to GENUINE....

Now Download and Install All MicroSoft ( IE7 , WMP11 , Windows Defender etc... ) without any Problem as a Genuine XP

User. !!

Download Windows XP Genuine utility from here

TRY ANOTHER METHOD :

INSTRUCTIONS

STEP 1: Open up ''Genuine validation status Tool'' and check the ''Genuine validation status'', if it says Invalid

Product Key then close this program and go to step 2

STEP 2 :

1. Open ''Windows XP CD Key and Product ID Changer''

2. Click on the ''verify'' tab and you'll see popup window there verify that your windows xp is Activated or not ?!

if not Activated close that window click ''Reactivate'' in ''Windows XP CD Key and Product ID Changer'' thre another window

will popup there choose second option ''activate by phone'' then click next then choose ''change product key button'' then

click next

then ''Enter a valid key .

V2C47-MK7JD-3R89F-D2KXW-VPK3J

then press ''Update'' & close window & Restart your computer

Again open ''Windows XP CD Key and Product ID Changer'' & press ''verify'' button to double check & your Windows XP is

activated...

3. Open ''Genuine validation status Tool'' again and you will see the ''Genuine validation status'' will be changed & your

Windows XP to GENUINE....

Download Windows XP Genuine utility from here

ANOTHER WAY TO MAKE GENUINE :

1. Load up Microsoft Genuine Advantage Diagnostic Tools and check the "Genuine validation status", if it says Invalid Product Key then close this program and go to step 2

2. Load Port_RockXP_V4, when loaded click ''Agree''

3. Click on the Windows Key tab and then Click ''Scan''

4. Once the ''scan'' is complete, select ''Microsoft Windows XP'' in the software box. Then Under ''Change you key'' enter :

V2C47-MK7JD-3R89F-D2KXW-VPK3J

And then click on ''Modify''

5. RockXP will then re-scan your computer and your product key will change. Close RockXP.

6. Load up Microsoft Genuine Advantage Diagnostic Tools again and you will see the ''Genuine validation status'' will have changed.

OR ONE MORE METHOD :

Also you may use ''WindowsXP KB905474-x86-1.5.540.0.exe'' XP Validator & WGA nag screen removal exe file. ( WindowsXP KB905474-x86-1.5.540.0.exe)t.

just run it ... restart your computer

& see for yourself that your startup nag screen has gone

& your Windows XP is activated

& can update ''All windows update'' see for yourself to

beleive.....

Now Download and Install All MicroSoft ( IE7 , WMP11 , Windows Defender etc... ) without any Problem as a Genuine XP

User. !!

enjoy !

Download Windows XP Genuine utility from here

An Introduction to Clustered and Non-Clustered Index Data Structures

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 07:28:00 +0000

An Introduction to Clustered and Non-Clustered Index Data Structures

Part I: Non-Clustered Index

Creating a Table

To better explain SQL Server non-clustered indexes; let's start by creating a new table and populating it with some sample data using the following scripts. I assume you have a database you can use for this. If not, you will want to create one for these examples.

Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)

When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a "heap". We can confirm the fact that this new table doesn't have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. "Indid" refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not have an index, and is stored by SQL Server as a heap.

Now let's add a few records in this table using this script:

Insert Into DummyTable1 Values (4, Replicate ('d',2000))
GO

Insert Into DummyTable1 Values (6, Replicate ('f',2000))
GO

Insert Into DummyTable1 Values (1, Replicate ('a',2000))
GO

Insert Into DummyTable1 Values (3, Replicate ('c',2000))
GO

Now, let's view the contests of the table by executing the following command in Query Analyzer for our new table.

Select EmpID From DummyTable1
GO

Empid

As you would expect, the data we inserted earlier has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.

Now, let's execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.

dbcc ind(dbid, tabid, -1) – This is an undocumented command.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')

DBCC ind(@DBID, @TableID, -1)
GO

This script will display many columns, but we are only interested in three of them, as shown below.

PagePID	IndexID	PageType
26408	0	10
26255	0	1
26409	0	1

Here's what the information displayed means:

PagePID is the physical page numbers used to store the table. In this case, three pages are currently used to store the data.

IndexID is the type of index,

Where:

0 – Datapage

1 – Clustered Index

2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index),

PageType tells you what kind of data is stored in each database,

Where:

10 – IAM (Index Allocation MAP)

1 – Datapage

2 – Index page

Now, let us execute DBCC PAGE command. This is an undocumented command.

DBCC page(dbid, fileno, pageno, option)

Where:

dbid = database id.

Fileno = fileno of the page. Usually it will be 1, unless we use more than one file for a database.

Pageno = we can take the output of the dbcc ind page no.

Option = it can be 0, 1, 2, 3. I use 3 to get a display of the data. You can try yourself for the other options.

Run this script to execute the command:

DBCC TRACEON (3604)
GO

DBCC page(@DBID, 1, 26408, 3)
GO

The output will be page allocation details.

DBCC TRACEON (3604)
GO

dbcc page(@DBID, 1, 26255, 3)
GO

The data will be displayed in the order it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 both display the data page.

I have displayed the data page information for page 26255 only. This is how MS SQL stores the contents in data pages as such column name with its respective value.

Record Type = PRIMARY_RECORD

EmpId = 4

EmpName = ddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

Record Type = PRIMARY_RECORD

EmpId = 6

EmpName = ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

Record Type = PRIMARY_RECORD

EmpId = 1

EmpName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

This displays the exact data storage in SQL, without any index on table. Now, let's go and create a unique non-clustered index on the EmpID column.

Creating a Non-Clustered Index

Now, we will create a unique non-clustered index on the empid column to see how it affects the data, and how the data is stored in SQL Server.

CREATE UNIQUE NONCLUSTERED INDEX DummyTable1_empid
ON DummyTable1 (empid)
GO

Now, execute the DBCC ind (dbid, tabid, -1)

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID	IndexID	PageType
26408	0	10
26255	0	1
26409	0	1
26411	2	10
26410	2	2

Now, we see two more rows than before, which now contains index page details. Page 26408 displays the page allocation details, and pages 26255 and 26409 display the data page details, as before.

In regard to the new pages, page 26411 displays the page allocation details of an index page and page 26410 displays the index page details.

MS SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.

Let us see what would be the output for page 26411, that is page type = 10

IAM: Single Page Allocations @0x308A608E

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

Slot 0 = (1:26410)

Let us view page 26410 to see the index page details.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26410, 3)
GO

SQL populates the index column data in order. The last column (?) is pointed to the row locator.

Here are the results, using two different methods:

Method I

FileID	PageID	EMPID	?
1	26410	1	0x8F66000001000200
1	26410	3	0x2967000001000000
1	26410	4	0x8F66000001000000
1	26410	6	0x8F66000001000100

The row location display in one of two ways:

If the table does not have a clustered index, the row locator will be combination of fileno, pageno and the no of rows in a page.
If the table does have clustered index, the row location will be clustered index key value.

Non-clustered indexes are particularly handy when we want to return a single row from a table.

For example, to search for employee ID (empid = 3) in a table that has a non-clustered index on the empid column, SQL Server looks through the index to find an entry that lists the exact page and row in the table where the matching empid can be found, and then goes directly to that page and row. This greatly speeds up accessing the record in question.

Select EmpID, EmpName From DummyTable1 WHERE EMPID = 3

Now, let's insert some more rows in our table and view the data page storage of our non-clustered index.

Insert Into DummyTable1 Values (10, Replicate ('j',2000))
GO

Insert Into DummyTable1 Values (2, Replicate ('b',2000))
GO

Insert Into DummyTable1 Values (5, Replicate ('e',2000))
GO

Insert Into DummyTable1 Values (8, Replicate ('h',2000))
GO

Insert Into DummyTable1 Values (9, Replicate ('i',2000))
GO

Insert Into DummyTable1 Values (7, Replicate ('g',2000))
GO

Now, let's view the data in our table.

Execute:

Select EmpID From DummyTable1

Here are the results:

EmpID
4
6
1
3
10
2
5
8
9
7

As you may notice above, the data is still in the order we entered it, and not in any particular order. This is because adding the non-clustered index didn't change how the data was stored and ordered on the data pages.

Now, let's view the results of the DBCC IND command. In order to find out what happened when the new data was added to the table.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID	IndexID	PageType
26408	0	10
26255	0	1
26409	0	1
26412	0	1
26413	0	1
26411	2	10
26410	2	2

Let us execute the page 26410 again and get the index page details.

DBCC TRACEON (3604)
GO

dbcc page(10, 1, 26410, 3)
GO

SQL Server populates the index column data in order. The last column (?) is pointed to the row locator.

Here are the results:

Method I

FileID	PageID	EMPID	?
1	26410	1	0x8F66000001000200
1	26410	2	0x2C67000001000000
1	26410	3	0x2967000001000000
1	26410	4	0x8F66000001000000
1	26410	5	0x2C67000001000100
1	26410	6	0x8F66000001000100
1	26410	7	0x2D67000001000000
1	26410	8	0x2C67000001000200
1	26410	9	0x2967000001000200
1	26410	10	0x2967000001000100

As I explained earlier, there are two types of row locations. We have seen Method I. Now, let's try Method II with the help of a clustered and non-clustered index in a table. DummyTable1 already has a non-clustered index. Let's now add a new column to the DummyTabl1 table and add a clustered index on that column.

Alter Table DummyTable1 Add EmpIndex Int IDENTITY(1,1)
GO

This will link the clustered index key value, instead of the row locator, and be will the combination of fileno, pageno and no of rows in a page.

This adds the Empindex column to DummyTable1. I have used an identity column so that we will not have null values on that column.

You can execute the DBCC ind and DBCC page to check if there any change after the new column is added to the table. If you don't want to check this yourself, I can tell you that adding the new column did not affect the total number of pages currently allocated to the table by SQL Server.

Now, let's add a unique clustered index on the empindex column and then view the differences in page 26410.

First, we execute the DBCC ind command. This displays a new set of pages for dummytable1.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID	IndexID	PageType
26415	1	10
26414	0	1
26416	1	2
26417	0	1
26418	0	1
26420	2	10
26419	2	2

Pages 26415 and 26420 have page allocation details. Pages 26414, 26417 and 26418 have data page details.

Now, let's view pages 26416 and 26419 and see the output.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26416, 3)
GO

Here are the results:

FileID	PageID	ChildPageID	EMPID
1	26416	26414	0
1	26416	26417	5
1	26416	26418	9

This displays the output of the clustered index page, which has got a link to data page (ChildPageID). EMPID is an index column that contains the starting row of the page.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26419, 3)
GO

Here are the results:

Method II

FileID	PageID	EMPID	EMPIndex
1	26419	1	1
1	26419	2	2
1	26419	3	3
1	26419	4	4
1	26419	5	5
1	26419	6	6
1	26419	7	7
1	26419	8	8
1	26419	9	9
1	26419	10	10

It is interesting to see the differences now. There is a difference between Method I and Method II. Method II is now linked to a clustered index key.

The main difference between Method I and Method II is the link to a row in a data page.

Part II: Clustered Index

Creating a Table

To better explain how SQL Server creates clustered indexes; let's start by creating a new table and populating it with some sample data using the following scripts. You can use the same sample database as before.

Create Table DummyTable2

(
EmpId Int,
EmpName Varchar(8000)
)

As in the previous example, when you first create a new table, there is no index created by default, and a heap is created. As before, we can confirm the fact that this new table doesn't have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. "Indid" refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not have an index, and is stored by SQL Server as a heap.

Now let's add a few records in this table using this script:

Insert Into DummyTable2 Values (4, Replicate ('d',2000))
GO

Insert Into DummyTable2 Values (6, Replicate ('f',2000))
GO

Insert Into DummyTable2 Values (1, Replicate ('a',2000))
GO

Insert Into DummyTable2 Values (3, Replicate ('c',2000))
GO

Now, let's view the contents of the table by executing the following command in Query Analyzer for our new table.

Select EmpID From DummyTable2
GO

Empid

As you would expect, the data we inserted has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.

Now, let's execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.

DBCC ind(dbid, tabid, -1) – It is an undocumented command.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable2')
DBCC ind(@DBID, @TableID, -1)
GO

This script will display many columns, but we are only interested in three of them, as shown below.

Here are the results:

PagePID	IndexID	PageType
26408	0	10
26255	0	1
26409	0	1

Here's what the information displayed means:

PagePID is the physical page numbers used to store the table. In this case, three pages are currently used to store the data.

IndexID is the type of index,

Where:

0 – Datapage

1 – Clustered Index

2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index)

PageType tells you what kind of data is stored in each database

Where:

10 – IAM (Index Allocation MAP)

1 – Datapage

2 – Index page

Now, let us execute DBCC PAGE command.

DBCC page(dbid, fileno, pageno, option)

Where:

dbid = database id.

Fileno = fileno of the page. Usually it will be 1, unless we use more than one file for a database.

Pageno = we can take the output of the dbcc ind page no.

Option = it can be 0, 1, 2, 3. I use 3 to get a display of the data. You can try yourself for the other options.

Run this script to execute the command:

DBCC TRACEON (3604)
GO

DBCC page(@DBID, 1, 26408, 3)
GO

The output will be page allocation details.

DBCC TRACEON (3604)
GO

DBCC page(@DBID, 1, 26255, 3)
GO

The output will display the data however it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 will display the data page.

I have displayed the data page information for page 26255 only. This is how MS-SQL stores the contents in data pages as such column name with its respective value.

Record Type = PRIMARY_RECORD

EmpId = 4

EmpName = dddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

Record Type = PRIMARY_RECORD

EmpId = 6

EmpName = ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

Record Type = PRIMARY_RECORD

EmpId = 1

EmpName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

This displays the exact data storage in SQL without any index on table. Now, let's go and create a Unique Clustered Index on EmpID column.

Create a Clustered Index

Now, let us create a unique clustered index on empid column to see how it affects the data that is stored in SQL Server.

CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndex
ON DummyTable2 (EmpID)
GO

Execute:

Select EmpID From DummyTable2

Here are the results:

Empid

Now, execute the DBCC ind (dbid, tabid, -1)

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id('DummyTable2')

DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID	IndexID	PageType
26411	1	10
26410	0	1
26412	1	2

MS SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.

Now, let us view the page 26410 and 26412 and see the page details.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26412, 3)
GO

Here are the results:

FileID	PageID	ChildPageID	EMPID
1	26412	26410	0

The output display many columns, but we are only interested in four of them as shown above.

This will display the output of the index page, which has got link to data page (ChildPageID). EMPID is an index column will contain the starting row of the page.

Now, let us view the page 26410 and see the page details.

DBCC TRACEON (3604)
GO

DBCC page (10, 1, 26410, 3)
GO

Here are the results:

Record Type = PRIMARY_RECORD

EmpId = 1

EmpName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Record Type = PRIMARY_RECORD

EmpId = 2

EmpName = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

Record Type = PRIMARY_RECORD

EmpId = 3

EmpName = cccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

Though I have added disorder records, SQL has displayed the data page in sequence because we have got a clustered index on empid. This is absolutely great! Adding a clustered index to the table has physically reordered the data pages, putting them in physical order based on the indexed column.

Now, let's insert some more rows in our table and view the data and index page storage of our clustered index.

Insert Into DummyTable2 Values (10, Replicate ('j',2000))
GO

Insert Into DummyTable2 Values (2, Replicate ('b',2000))
GO

Insert Into DummyTable2 Values (5, Replicate ('e',2000))
GO

Insert Into DummyTable2 Values (8, Replicate ('h',2000))
GO

Insert Into DummyTable2 Values (9, Replicate ('i',2000))
GO

Insert Into DummyTable2 Values (7, Replicate ('g',2000))
GO

Now, execute the DBCC ind (dbid, tabid, -1)

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id('DummyTable2')

DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID	IndexID	PageType
26411	1	10
26410	0	1
26412	1	2
26255	0	1
26408	0	1
26409	0	1

Now, we see few more rows than before. Page 26411 displays the page allocation details, and pages 26408, 26409, 26410 and 26255 display the data page details, as before.

In regard to the new pages, page 26411 displays the page allocation details of an index page and 26412 displays the index page details.

MS-SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.

Let us see what would be the output for page 26411, that is page type = 10.

DBCC TRACEON (3604)
GO

dbcc page(10, 1, 26411, 3)
GO

Here are the results:

IAM: Single Page Allocations @0x30A5C08E

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

Slot 0 = (1:26410)

Slot 1 = (1:26412)

Slot 2 = (1:26255)

Slot 3 = (1:26408)

Slot 4 = (1:26409)

Let us view page 26412 to see the index page details.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26412, 3)
GO

Here are the results:

FileID	PageID	ChildPageID	EMPID
1	26412	26410	0
1	26412	26408	4
1	26412	26255	6
1	26412	26409	9

This helps us to get an idea to decide the need of clustered index. It is really useful to have a clustered index when retrieve many rows of data, ranges of data, and when BETWEEN is used in the WHERE clause. Because, the leaf level of the clustered index is the data. It should be used to save many I/Os. So, it is better to use clustered indexes to solve queries asking for ranges of data, not one row.

For example, to search for an employee ID (empid between 3 and 9) in a table that has a clustered index on the empid column.

Select EmpID, EmpName From DummyTable1 WHEREEMPID Between 3 And 9

How to remoove references in whole database if primary key column name and foreign column name are same

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 07:27:00 +0000

/**********************************************************************

Procedure Name: RemooveAllReference

Created By: Mangesh Singh

Date: 24 April 2009

Purpose: To Remoove All Constraints / Foreign Keys In Database

**********************************************************************/

ALTER Procedure RemooveAllReference

DECLARE @ERR INT

Begin Tran Reference

Create Table #temp (ID INT IDENTITY(1,1),TableName varchar(100),IDD INT)

Insert INTO #temp(TableName,IDD)

Select Name,ID From Sys.SysObjects Where ID IN( select Distinct Parent_Obj from sys.sysobjects where xtype='F' )

Declare @Count INT

Declare @A INT

Declare @TableName varchar(100)

Set @A=1

Select @Count=Count(*) From #temp

While(@A<=@Count)

Begin

Select @TableName=TableName From #temp Where ID=@A

Create Table #Temp1(ID INT IDENTITY(1,1),ColumnName Varchar(100))

Insert Into #Temp1(ColumnName)

select Name from Sys.SysObjects Where XType='F' AND Parent_Obj=(Select IDD From #temp Where ID=@A)

Declare @CNT INT

Declare @AA INT

Set @AA=1

Select @CNT=Count(*) From #Temp1

While(@AA<=@CNT)

Begin

Declare @CLNAME Varchar(200)

Declare @SQL Varchar(1000)

Select @CLNAME=ColumnName From #Temp1 Where ID=@AA

Set @SQL='ALTER TABLE dbo.'+@TableName+'

DROP CONSTRAINT '+@CLNAME+''

Exec(@SQL)

Set @Err=@@ERROR

if (@ERR <> 0)

Begin

Raiserror('Insert Statement Fail',16,1)

RollBack Transaction Reference

End

Set @AA=@AA+1

End

Set @A=@A+1

Drop Table #temp1

End

Drop Table #temp

Commit Tran

How to apply foreign key in whole databse if primary key column name and foreign column name are same

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 07:26:00 +0000

/**********************************************************************

Procedure Name: [Add_Reference]

Created By: Mangesh Singh

Date: 24 April 2009

Purpose: To Add Relationship In Database

**********************************************************************/

ALTER Procedure [dbo].[Add_Reference]

DECLARE @ERR INT

Begin Tran Reference

Create Table #temp

(ID INT IDENTITY,TableName varchar(100),IDD INT)

Insert Into #temp(TableName,IDD)

Select Name,ID From sys.sysobjects Where xtype='U'

Declare @Count INT

declare @A INT

declare @PrimaryColumn varchar(100)

declare @primarytable varchar(100)

declare @ForegnKeyTable varchar(100)

declare @Sql varchar(max)

Set @A=1

Select @Count=Count(*) From #temp

While(@A<=@Count)

Begin

Declare @IsPrimary Bit

Declare @FK Varchar(100)

Declare @PK Varchar(100)

Declare @PKTABLE Varchar(100)

Declare @FKTABLE Varchar(100)

Select @PKTABLE=TableName From #temp Where ID=@A

Select @PK=Name From Sys.SysColumns Where ID IN(Select ID From Sys.SysObjects Where Name=(Select TableName From #temp

Where ID=@A)) and ColStat=1

Create Table #fk(ID INT IDENTITY(1,1),TblName Varchar(Max))

Insert Into #fk(TblName)

Select name From Sys.SysObjects Where ID IN(Select ID From Sys.SysColumns Where Name=@PK) AND XType='U'

Declare @CountTemp INT

Declare @AA INT

Set @AA=1

Select @CountTemp=Count(*) From #fk

While(@AA<=@CountTemp)

Begin

Select @FKTABLE=TblName From #fk Where ID=@AA

IF(@FKTABLE<>@PKTABLE)

Begin

Set @Sql='ALTER TABLE '+@FKTABLE+' ADD FOREIGN KEY ('+@PK+') REFERENCES '+@PKTABLE+'('+@PK+');'

Exec(@Sql)

Set @Err=@@ERROR

if (@ERR <> 0)

Begin

Raiserror('Insert Statement Fail',16,1)

RollBack Transaction Reference

End

Set @AA=@AA+1

End

Drop table #fk

Set @A=@A+1

End

Drop table #temp

Commit Tran

how to generate Serial numbers in select query in sql

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 07:25:00 +0000

SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1) As SrNo, ColumnName1, ColumnName2 FROM TableName

Fig- (1) Query to display Serial Number Column using SQL Server

Detailded example

Abstract: Sometimes you just need to extract every other row from a result set. Other times you need a row number so as to provide paging in a web application.

Here is an example of how to extract every other row from a result set in both SQL Server 2000 and SQL Server 2005

Body:

Possible shortcut: If the underlying result set has an integer that is more or less evenly divided between odd and even then this example with the Categories table in the Northwind database will work.

SELECT CategoryID, CategoryName

FROM dbo.Categories

WHERE CategoryID % 2 = 1

By using the % (modulo) operator we get rows with the categoryid is odd. This does not perfectly give you everyother row since we could have a missing value (the row could have been deleted). Even worse, your additional search criteria could select data that is not evenly distributed between even and odd ids.

This will work every time:

DECLARE @tCat TABLE (TID int identity(1,1), CategoryID int, CategoryName varchar(100))

INSERT @tCat (CategoryID, CategoryName )

SELECT CategoryID, CategoryName

FROM dbo.Categories

SELECT CategoryID, CategoryName

FROM @tCat

WHERE TID % 2 = 1

In SQL 2005 you could use the Row_Number() function.

SELECT CategoryID, CategoryName

FROM (SELECT Row_Number() OVER(ORDER BY CategoryName DESC) as RowNum, CategoryID, CategoryName

FROM dbo.Categories

) Cat

WHERE RowNum % 2 = 1

To Count Records In Tables in sql server 2005,2008

noreply@blogger.com (Chauthi Drishti) — Fri, 11 Jun 2010 07:19:00 +0000

---To Count Records In Tables

Create PROCEDURE [dbo].[listTableRowCounts]

BEGIN

SET NOCOUNT ON

DECLARE @SQL VARCHAR(255)

SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'

EXEC(@SQL)

CREATE TABLE #foo

(

tablename VARCHAR(255),

rc INT

)

INSERT #foo

EXEC sp_msForEachTable

'SELECT PARSENAME(''?'', 1),

COUNT(*) FROM ?'

SELECT tablename, rc

FROM #foo

where rc > 0

Order By tablename

DROP TABLE #foo

END

Tech Tips

Free calling in Gmail extended through 2011

General SQL Server Performance Tuning Tips

Denormalization in SQL Server for Fun and Profit

Understanding Common Type System in .NET Framework

How to configure dbmail in sql server 2005 and 2008

How to decompile .net DLL or EXE

Windows Communication Foundation

New Social API Web Application Toolkit for .NET Web Developers

Latest in this API

Introducing the Social API Web Application Toolkit

How to decompile any .net DLL

Find nth Wor from a table in SQL Server or any row from table

Cursors in SQL Server 2005

Cursors

Declaring a Cursor

SQL-92 Syntax

Cursor Options Compatibility

Transact-SQL Extended Syntax

Cursor Options Compatibility

Opening a Cursor

Fetching a Cursor

Closing a Cursor

Deallocating a Cursor

Joins in SQL Server 2005

Concept of OOPS

Visual Studio 2010 Released

Download Visual Studio 2010

What's new in Visual Studio 2010 and .NET 4?

MSDN and Visual Studio 2010

How to setup vpn server in windows xp

WindowsXP VPN Server

Using AT commands to Send and Receive SMS

Simple ASP.NET 2.0 Tips and Tricks

SQL Server interview auestions and answers

how to decompile c# dll and exe..net decompiler

Windows XP Genuine Patch,Windows XP Genuine Ceack,How to activate Windows XP Genuine

An Introduction to Clustered and Non-Clustered Index Data Structures

An Introduction to Clustered and Non-Clustered Index Data Structures

How to remoove references in whole database if primary key column name and foreign column name are same

How to apply foreign key in whole databse if primary key column name and foreign column name are same

how to generate Serial numbers in select query in sql

To Count Records In Tables in sql server 2005,2008