A New Internet Library: Add Your Website/Blog or Suggest A Website/Blog to our Free Web Directory http://anil.myfunda.net.

Its very simple, free and SEO Friendly.
Submit Now....

Wednesday, April 23, 2008

Difference between TOP clause in SQL 2000 and SQL 2005

Generally TOP clause is used to perform SELECT on top n results. This feature of TOP is extended in SQL 2005 so that we can also use expression apart from int, bigint and percent to perform query and also extended to be used in UPDATE and DELETE statements.


In SQL 2000


syntax: select Top N [Percent]


EX:

select Top 10 * from TableName

or

select Top 10 Percent * from TableName


n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return


Drawbacks:

We could not parameterize.

It will work only for select statements.

If we want to restrict the number of rows affected by a select at runtime, or restrict the rows affected by an update or delete you had to explicitly describe the rows using a join or where clause, or you could cheat a bit by using ROWCOUNT, like this

set rowcount 10 delete from table where payratefieldname=1 set rowcount 0

It will work but the risk is if for some reason rowcount is not set to 0 then the other statements will also restricted to 10.


All these drawbacks are overcome in SQL 2005 by introducing Expression in syntax.


In SQL 2005

syntax: select Top (Expression) [Percent]


EX:


Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.


Ex:


select Top 10 * from TableName

or

select Top 10 Percent * from TableName

or we can set at runtime as


Declare @ int

SET @topNum = 10

select TOP (@topNum) * from


For select statements you must specify parentheses if you are passing a parameter, otherwise they are optional

Select Top (@topNum) * from TableName Select Top 10 * from TableName

When doing an update or delete, you have to use the parentheses in both cases:

Delete Top (@topNum) from employeesDelete Top (10) from TableName update Top (@topNum) TableName set fieldname = @fieldvalue  update Top (10) from employees set fieldname = @fieldvalue This is nice tricky change introduced in SQL 2005

Detecting a Client's screen resolution and color depth in ASP.NET/Javascript

At times you might want to know how much screen real estate a user has before opening several new browser windows. You also might want to find out if they have a high-color display capable of showing a detailed color image. The following code shows how to learn the user's screen real estate and color depth. The total screen size includes unusable areas like the icon tray on Windows or the menubar on the Mac. The available screen size attributes subtract these system-reserved areas from the total screen size. For multi-screen systems, Internet Explorer will return the size of the monitor it is on, while Netscape returns the size of both monitors combined.

  <HTML> <HEAD> 	<TITLE>Detecting the user's hardware</TITLE> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" ALINK="#6600FF" VLINK="#CC0000"> <SCRIPT LANGUAGE="JavaScript1.2" TYPE="text/javascript"> 	<!-- 	document.writeln("Total Screen size = ", screen.width, " x ", screen.height); 	document.writeln("<P>"); 	document.writeln("Available Screen size = ", screen.availWidth, " x ", screen.availHeight); 	document.writeln("<P>"); 	document.writeln("Color depth = ", screen.colorDepth); 	//--> </SCRIPT> </BODY> </HTML>

Retrieving all Server Environment variable values

How to retrieve all Server Environment variables at one go. Just place
this code in .aspx page run it.

<div>
<table width="70%" align="center">
<tr bgcolor="#FF9900">
<td width="25%" align="center">
ServerVariable:
</td>
<td width="75%">
Value
</td>
</tr>
<% foreach(string Item in Request.ServerVariables){ %>
<tr>
<td width="25%">
<%= Item %>
</td>
<td width="75%">
<%=Request.ServerVariables[Item]%>
</td>
</tr>
<% } %>
</table>
</div>

Complete list of server environment variables

Following is the table containing a list of server environment variables.

Using:

VB: Request.ServerVariables("Variable")

C#: Request.ServerVariables["Variable"]

 

 

Variable

Description

ALL_HTTP

All HTTP headers sent by the client.

ALL_RAW

Retrieves all headers in raw form. The difference between ALL_RAW and ALL_HTTP is that ALL_HTTP places an HTTP_ prefix before the header name and the header name is always capitalized. In ALL_RAW the header name and values appear as they are sent by the client.

APPL_MD_PATH

Retrieves the metabase path for the Application for the ISAPI DLL.

APPL_PHYSICAL_PATH

Retrieves the physical path corresponding to the metabase path. IIS converts the APPL_MD_PATH to the physical (directory) path to return this value.

AUTH_PASSWORD

The value entered in the client's authentication dialog. This variable is available only if Basic authentication is used.

AUTH_TYPE

The authentication method that the server uses to validate users when they attempt to access a protected script.

AUTH_USER

The name of the user as it is derived from the authorization header sent by the client, before the user name is mapped to a Windows account. This variable is no different from REMOTE_USER. If you have an authentication filter installed on your Web server that maps incoming users to accounts, use LOGON_USER to view the mapped user name.

CERT_COOKIE

Unique ID for client certificate, returned as a string. Can be used as a signature for the whole client certificate.

CERT_FLAGS

bit0 is set to 1 if the client certificate is present.

bit1 is set to 1 if the cCertification authority of the client certificate is invalid (it is not in the list of recognized CAs on the server).

CERT_ISSUER

Issuer field of the client certificate (O=MS, OU=IAS, CN=user name, C=USA).

CERT_KEYSIZE

Number of bits in Secure Sockets Layer connection key size. For example, 128.

CERT_SECRETKEYSIZE

Number of bits in server certificate private key. For example, 1024.

CERT_SERIALNUMBER

Serial number field of the client certificate.

CERT_SERVER_ISSUER

Issuer field of the server certificate.

CERT_SERVER_SUBJECT

Subject field of the server certificate.

CERT_SUBJECT

Subject field of the client certificate.

CONTENT_LENGTH

The length of the content as given by the client.

CONTENT_TYPE

The data type of the content. Used with queries that have attached information, such as the HTTP queries GET, POST, and PUT.

GATEWAY_INTERFACE

The revision of the CGI specification used by the server. The format is CGI/revision.

HTTP_<HeaderName>

The value stored in the header HeaderName. Any header other than those listed in this table must be prefixed by HTTP_ in order for the ServerVariables collection to retrieve its value.

Note The server interprets any underscore (_) characters in HeaderName as dashes in the actual header. For example if you specify HTTP_MY_HEADER, the server searches for a header sent as MY-HEADER.

HTTP_ACCEPT

Returns the value of the Accept header.

HTTP_ACCEPT_LANGUAGE

Returns a string describing the language to use for displaying content.

HTTP_COOKIE

Returns the cookie string that was included with the request.

HTTP_HOST

Returns the name of the Web server. This may or may not be the same as SERVER_NAME depending on type of name resolution you are using on your Web server (IP address, host header).

HTTP_REFERER

Returns a string that contains the URL of the page that referred the request to the current page using an HTML <A> tag. Note that the URL is the one that the user typed into the browser address bar, which may not include the name of a default document.

If the page is redirected, HTTP_REFERER is empty.

HTTP_REFERER is not a mandatory member of the HTTP specification.

HTTP_USER_AGENT

Returns a string describing the browser that sent the request.

HTTPS

Returns ON if the request came in through secure channel (SSL) or it returns OFF if the request is for a non-secure channel.

HTTPS_KEYSIZE

Number of bits in Secure Sockets Layer connection key size. For example, 128.

HTTPS_SECRETKEYSIZE

Number of bits in server certificate private key. For example, 1024.

HTTPS_SERVER_ISSUER

Issuer field of the server certificate.

HTTPS_SERVER_SUBJECT

Subject field of the server certificate.

INSTANCE_ID

The ID for the IIS instance in textual format. If the instance ID is 1, it appears as a string. You can use this variable to retrieve the ID of the Web-server instance (in the metabase) to which the request belongs.

INSTANCE_META_PATH

The metabase path for the instance of IIS that responds to the request.

LOCAL_ADDR

Returns the Server Address on which the request came in. This is important on multi-homed computers where there can be multiple IP addresses bound to the computer and you want to find out which address the request used.

LOGON_USER

The Windows account that the user is impersonating while connected to your Web server. Use REMOTE_USER or AUTH_US<CODE>ER to view the raw user name that is contained in the request header. The only time LOGON_USER holds a different value than these other variables is if you have an authentication filter installed.

PATH_INFO

Extra path information as given by the client. You can access scripts by using their virtual path and the PATH_INFO server variable. If this information comes from a URL, it is decoded by the server before it is passed to the CGI script.

PATH_TRANSLATED

A translated version of PATH_INFO that takes the path and performs any necessary virtual-to-physical mapping.

QUERY_STRING

Query information stored in the string following the question mark (?) in the HTTP request.

REMOTE_ADDR

The IP address of the remote host making the request.

REMOTE_HOST

The name of the host making the request. If the server does not have this information, it will set REMOTE_ADDR and leave this empty.

REMOTE_USER

The name of the user as it is derived from the authorization header sent by the client, before the user name is mapped to a Windows account. If you have an authentication filter installed on your Web server that maps incoming users to accounts, use LOGON_USER to view the mapped user name.

REQUEST_METHOD

The method used to make the request. For HTTP, this is GET, HEAD, POST, and so on.

SCRIPT_NAME

A virtual path to the script being executed. This is used for self-referencing URLs.

SERVER_NAME

The server's host name, DNS alias, or IP address as it would appear in self-referencing URLs.

SERVER_PORT

The port number to which the request was sent.

SERVER_PORT_SECURE

A string that contains either 0 or 1. If the request is being handled on the secure port, then this will be 1. Otherwise, it will be 0.

SERVER_PROTOCOL

The name and revision of the request information protocol. The format is protocol/revision.

SERVER_SOFTWARE

The name and version of the server software that answers the request and runs the gateway. The format is name/version.

URL

Gives the base portion of the URL.

 

Dotnet-Interviews