Temporary Tables in SQL Server 2005

About Temporary Tables in SQL Server 2005

1. Temporary Tables are useful in a way when we want to create a table for temporary processing.
2. We can create temporary table in a same way as normal table except temporary table has a pound sign or hash sign (#) preceding its name.
3. Temporary tables are created in a tempdb
4. Temporary is tables are available only in a batch or procedure that creates it. Once the execution of stored procedure or batch is over they are deleted.
5. If you create a temporary table in a Query analyzer, it will be available until the end of the session.
6. You can also drop temporary table within the batch.
7. Temporary tables are accessible only those connections which creates the Temporary table. In this way the scope for temporary table is very limited.
8. Temporary tables are created in tempdb with a unique name. The unique name is created on assigned table name + connection identifier. Assigned table name is a name given to temporary table while creating it plus connection identifier.
9. The length of the Temporary table name is 116 characters + last 12 characters makes the name unique.

Let’s look at a simple example.

–Temporary Table Example

/*

Please execute as a batch. Since this will be executed in a SQL Query Browser (Analyzer) temporary table will be available till the session exists. So you might get error if you create table with the same name again in the same session.Because the table already exists.

you can also drop temporary table..Follow the below example

*/

–Create Temporary Table
CREATE TABLE #Temp1 (Empno int PRIMARY KEY)

–Inserting row into a temporary table
INSERT INTO #Temp1 VALUES (1001)

–Select rows from Temporary Table
SELECT * FROM #Temp1

–Insert rows from other Table
INSERT INTO #Temp1 SELECT EMPNO from Test

–Select rows from Temporary Table
SELECT * FROM #Temp1

–Using Temporary Table in a stored procedure

CREATE PROCEDURE TempTableTest (@NewEmpNo int)
AS
SET NOCOUNT ON

BEGIN
–Create new Temporary Table
CREATE TABLE #Temp2 (Empno int PRIMARY KEY)

–Insert new Record
INSERT INTO #Temp2 VALUES (@NewEmpNo)

–Display new Inserted Record
SELECT * FROM #Temp2
END

EXEC TempTableTest 2

Result

Year 2038 problem

The year 2038 problem (also known as "Unix Millennium bug", "Y2K38," "Y2K+38," or "Y2.038K" by analogy to the Y2K problem) may cause some computer software to fail before or in the year 2038. The problem affects Unix-like dating systems, which represent system time as the number of seconds (ignoring leap seconds) since 00:00:00 January 1, 1970.[1] This representation also affects software written for most other operating systems because of the broad deployment of C. On most 32-bit systems, the time_t data type used to store this second count is a signed 32-bit integer. The latest time that can be represented in this format, following the POSIX standard, is 03:14:07 UTC on Tuesday, January 19, 2038. Times beyond this moment will "wrap around" and be represented internally as a negative number, and cause programs to fail, since they will see these times as being not in 2038, but rather in 1901. Erroneous calculations and decisions may therefore result.

Known problems

In May 2006, reports surfaced of an early Y2038 problem in the AOLserver software. The software would specify that a database request should "never" time out by specifying a timeout date one billion seconds in the future. One billion seconds (just over 31 years 251 days and 12 hours) after 21:27:28 on 12 May 2006 is beyond the 2038 cutoff date, so after this date, the timeout calculation overflowed and calculated a timeout date that was actually in the past, causing the software to crash.

Example

$ date
Su 6. Jul 00:32:27 CEST 2008
$ openssl req -x509 -in server.csr -key server.key -out server.crt -days 10789 && openssl x509 -in server.crt -text | grep After
           Not After : Jan 18 22:32:32 2038 GMT
$ openssl req -x509 -in server.csr -key server.key -out server.crt -days 10790 && openssl x509 -in server.crt -text | grep After
           Not After : Dec 14 16:04:21 1901 GMT   (32-Bit System)
$ openssl req -x509 -in server.csr -key server.key -out server.crt -days 2918831 && openssl x509 -in server.crt -text | grep After
           Not After : Dec 31 22:41:18 9999 GMT   (64-Bit System)

Solutions

There is no easy fix for this problem for existing CPU/OS/File System combinations. Changing the definition of time_t to use a 64-bit type would break binary compatibility for software, data storage, and generally anything dealing with the binary representation of time. Changing time_t to an unsigned 32-bit integer, effectively allowing timestamps to be accurate until the year 2106, would affect many programs that deal with time differences, and thus also break binary compatibility in many cases.

Most operating systems for 64-bit architectures already use 64-bit integers in their time_t. The move to these architectures is already under way and many expect it to be complete before 2038. Using a (signed) 64-bit value introduces a new wraparound date in about 290 billion years, on Sunday, December 4, 292,277,026,596. This problem, however, is not widely regarded as a pressing issue. As of 2007, however, hundreds of millions of 32-bit systems are deployed, many in embedded systems, and it is far from certain they will all be replaced by 2038. Further, long before that date programs which project any kind of pattern into the future will begin to run into problems. E.g.; by 2028, projecting a trend ten years will encounter the "bug".

Despite the modern 18- to 24-month generational update in computer systems technology, embedded computers may operate unchanged for the life of the system they control. The use of 32-bit time_t has also been encoded into some file formats, which means it can live on for a long time beyond the life of the machines involved.

A variety of alternative proposals have been made, some of which are in use, including storing either milliseconds or microseconds since an epoch (typically either January 1, 1970 or January 1, 2000) in a signed-64 bit integer, providing a minimum of 300,000 years range.[4][5] Other proposals for new time representations provide different precisions, ranges, and sizes (almost always wider than 32 bits), as well as solving other related problems, such as the handling of leap seconds.

protect your stored procedure code

When deploying applications to a client's server(s) or to a shared SQL Server, there is often a concern that other people might peek at your business logic. Since often the code in a stored procedure can be proprietary, it is understandable that we might want to protect our T-SQL work. There is a trivial way to do this in SQL Server, instead of:

CREATE PROCEDURE dbo.foo AS BEGIN SELECT 'foo' END

You can use the WITH ENCRYPTION option:

CREATE PROCEDURE dbo.foo WITH ENCRYPTION AS BEGIN SELECT 'foo' END

Now, before you do this, make sure you keep the logic of the stored procedure in a safe place, since you won't have easy access to the procedure's code once you've saved it.

Now you will notice that when you try to open the procedure in Enterprise Manager's GUI, you will receive the following error:

Microsoft SQL-DMO Error 20585: [SQL-DMO] /****** Encrypted object is not transferable, and script can not be generated. ******/

And when you try to use sp_helptext to review the code:

EXEC sp_helptext 'foo'

You will get the following error:

The object comments have been encrypted.

Unfortunately, there are at least two ways to defeat this mechanism. One is to run SQL Profiler while executing the stored procedure; this often can reveal the text of the procedure itself, depending on what the stored procedure does (e.g. if it has GO batches, dynamic SQL etc). If they miss the initial install, the user can delete the stored procedures or drop the database, start a Profiler trace, and ask you to re-create them (in which case they will capture the CREATE PROCEDURE statements). You can prevent Profiler from revealing the text to snoopers by embedding sp_password in the code, as a comment:

CREATE PROCEDURE dbo.foo WITH ENCRYPTION AS BEGIN SELECT 'foo' -- comment: sp_password END

Another way thatusers might be able to get at your encrypted code is to use readily available code that allows you to break SQL Server's relatively trivial encryption algorithm. You can find this code online if you know what you are looking for...

How do I debug my SQL statements?

Many people who get errors in SQL statements post their ASP code and the error, and say "what's wrong with this SQL statement?" It's hard for anyone to tell when you've got a SQL statement that hasn't been built yet, for example:

<% sql = "SELECT my_column FROM my_table WHERE my_id = " & my_var set rs = conn.execute(sql) %>

If all the information we have is the above, you may as well have called your mechanic, told him you have a problem with your car, and then gave him your license plate number. We have no idea if you have a column named my_column, have a table named my_table, have permissions to that table, what datatype my_id is, and what value (if any) is present in my_var.

To debug this properly, when you get an error, you should add the following two lines:

<% sql = "SELECT my_column FROM my_table WHERE my_id = " & my_var response.write sql response.end set rs = conn.execute(sql) %>

Now, you can copy the SQL statement from the browser and post it directly in Access or Query Analyzer, and you might get a little bit more infor about why it's not working. Sometimes it will be obvious even before that point, e.g. my_var is empty or the wrong data type; you should put quotes around a string, enclose # around a date, or remove the quotes around a numeric; or you are using a reserved word (like DATE) for a column name (see Article #2080).

If you still can't solve the issue, we can't even come close to helping you unless you include the SQL statement above, the actual error message, and following as many of the recommendations in Article #5006 as possible--most importantly the structure of the table, including data types. Following these steps, you are likely to solve your problem much quicker than people who post their ASP code and just ask us to fix it. The latter will almost always result in a "we need more info" type of reply.

How do I calculate the median in a table?

MEAN is easy (using the aggregate function AVG). MEDIAN, on the other hand, can be a little more difficult to calculate. (MEDIAN is one of the aggregate functions typically handled by statistics packages or by OLAP / Analysis Services.)

---

SQL Server

Say we have the following table:

CREATE TABLE blat ( splunge INT NOT NULL ) GO SET NOCOUNT ON INSERT blat VALUES(1) INSERT blat VALUES(2) INSERT blat VALUES(3) INSERT blat VALUES(5) INSERT blat VALUES(7) INSERT blat VALUES(8) INSERT blat VALUES(8) INSERT blat VALUES(9)

If we had an odd number of rows, we could calculate the median by simply finding the row where the number of values greater than that splunge value is equal to the number of values less than that splunge value:

SELECT splunge FROM blat b WHERE ( SELECT count(splunge) FROM blat WHERE splunge < b.splunge ) = ( SELECT count(splunge) FROM blat WHERE splunge > b.splunge )

Or this way, by just taking the greatest value from the top 50% of the table, ordered by splunge:

SELECT TOP 1 splunge FROM ( SELECT TOP 50 PERCENT splunge FROM blat ORDER BY splunge ) sub ORDER BY splunge DESC

With an even number of rows, however, the first query returns no results, and the second query might not return the desired result (in the case above, it will return the 4th of 8 rows, where splunge = 5). We need to do a little more work to calculate the *true* median, including implicitly converting to decimal, and taking the average of two nested subqueries and a union:

SELECT AVG(splunge) FROM ( SELECT splunge FROM ( SELECT TOP 1 splunge = splunge * 1.0 FROM ( SELECT TOP 50 PERCENT splunge FROM blat ORDER BY splunge ) sub_a ORDER BY 1 DESC ) sub_1 UNION ALL SELECT splunge FROM ( SELECT TOP 1 splunge = splunge * 1.0 FROM ( SELECT TOP 50 PERCENT splunge FROM blat ORDER BY splunge DESC ) sub_b ORDER BY 1 ) sub_2 ) median

No, it's not pretty, but it gets the job done. There are similar ways to approach this problem that require fewer subqueries, but add the requirement of dynamic SQL.

For added fun, you could determine beforehand which method you need, so—in the case where there are an odd number of rows—the overall work required by the procedure will likely be more efficient.

IF (SELECT COUNT(*) % 2 FROM blat) = 1 -- use easy query for odd # of rows ELSE -- use more complex query for even # of rows

Keep in mind that I had a CONSTRAINT here that prevented splunge from containing a NULL (this could break some of the logic above). If the column you are using can have NULL values, you'll want to add AND splunge IS NOT NULL to all WHERE clauses.

---

Microsoft Access

In Access, we can run the same single subquery example as above, and the same CREATE TABLE statement.

SELECT TOP 1 splunge FROM ( SELECT TOP 50 PERCENT splunge FROM blat ORDER BY splunge ) ORDER BY splunge DESC

In the case of an even number of rows, we might want to round to the 5th instead of 4th value, we can just swap it around:

SELECT TOP 1 splunge FROM ( SELECT TOP 50 PERCENT splunge FROM blat ORDER BY splunge DESC ) ORDER BY splunge

However, we can also calculate the mathematical median in Access, which is basically the average of the two median values. Instead of nesting a bunch of sub-queries and a union, let's create a peripheral table to store the results temporarily.

CREATE TABLE blat(id INT)

INSERT INTO blat(id)
SELECT TOP 1 id FROM
(
SELECT TOP 50 PERCENT id
FROM table1 ORDER BY id
)
ORDER BY id DESC

INSERT INTO blat(id)
SELECT TOP 1 id FROM
(
SELECT TOP 50 PERCENT id
FROM table1 ORDER BY id DESC
)
ORDER BY id

SELECT AVG(id) FROM blat

DROP TABLE blat

AJAX Script For Beginners

In traditional JavaScript coding, if you want to get any information from a database or a file on the server, or send user information to a server, you will have to make an HTML form and GET or POST data to the server. The user will have to click the "Submit" button to send/get the information, wait for the server to respond, then a new page will load with the results.

Because the server returns a new page each time the user submits input, traditional web applications can run slowly and tend to be less user-friendly.

With AJAX, your JavaScript communicates directly with the server, through the JavaScript XMLHttpRequest object

With an HTTP request, a web page can make a request to, and get a response from a web server - without reloading the page. The user will stay on the same page, and he or she will not notice that scripts request pages, or send data to a server in the background.

Here are the possible values for the readyState property:

State	Description
0	The request is not initialized
1	The request has been set up
2	The request has been sent
3	The request is in process
4	The request is complete

// JavaScript Document
var xmlHttp

function showProject(str)
{

xmlHttp=GetXmlHttpObject()
if (xmlHttp==null)
{
alert ("Browser does not support HTTP Request")
return
}
var url="getProject.php"
url=url+"?q="+str
url=url+"&sid="+Math.random()
xmlHttp.onreadystatechange=stateChanged
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}

function stateChanged()
{
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
{
document.getElementById("txtProjectName").innerHTML=xmlHttp.responseText
}
}

function GetXmlHttpObject()
{
var xmlHttp=null;
try
{
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
//Internet Explorer
try
{
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e)
{
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
}
return xmlHttp;
}

xmlHttp.onreadystatechange=function() { if(xmlHttp.readyState==4) { // Get the data from the server's response } }

The responseText Property

The data sent back from the server can be retrieved with the responseText property.

In our code

xmlHttp.onreadystatechange=function()
{
if(xmlHttp.readyState==4)
{
document.myForm.time.value=xmlHttp.responseText;
}
}

Web Page Hit Counter in PHP

This is the Hit counter to count the hit when the page session is registered

Insert a simple piece of code on your web page you will be able to analyse and monitor all the visitors to your website in real-time!

?php
if (!session_is_registered("counted")){
mysql_query("UPDATE tblcounter SET count=(count + 1) WHERE count_id=1");
session_register("counted");
}

$result=mysql_query("select count from tblcounter where count_id=1");
$row_count=mysql_fetch_row($result);
echo($row_count[0]);


?>