Friday, May 15, 2009

How to find when SQL Server was last started?

When ever SQL Server is (re)started ‘TempDB’ gets recreated. So the below query should give us the time of when ‘SQL Server’ was last started.

Select create_date from sys.databases where [name] = 'tempdb'

When a SQL Server starts all the tasks which it initiates makes an entry in sysprocesses table with its ‘login time’. That said, all we need to do is find the least login_time which has got recorded in that table!

1. Select min(login_time) from master.dbo.sysprocesses
OR
2. Select login_time from master.dbo.sysprocesses where spid = 1
OR
3. Select login_time from sys.dm_exec_sessions where session_id = 1

Technorati Tags:

Tuesday, May 12, 2009

‘The Downfall’ of AGILE Hitler...

This one is hilarious :) I couldn’t stop laughing after watching this short cooked up movie.

If you are in IT for atleast one year then you would love this as much as I did :)

Arithmetic overflow error converting IDENTITY to data type int.

Today I ran into the below error:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.

Arithmetic overflow occurred.

As the error suggests in the table the max limit of INT (2147483647) has reached.

For the sake of people who haven't seen this error before let me simulate it.

Create table MaxInt
(
  sno int identity,
  Fname varchar(30)
)
Go

---Inserting my first record
Insert into MaxInt values ('Sample')

/* huh i can't insert billions of records like this and show :) Let me Reseed to last few numbers */
DBCC CHECKIDENT ( MaxInt, ReSeed, 2147483646)

--Try inserting a record. It would
Insert into MaxInt values ('Vadivel')

/* Trying to insert another one .... now you would see the error mentioned above as we have reached the max of INT already */
Insert into MaxInt values ('Vadivel')

Select * from MaxInt

Sno

Fname
1 Sample
2147483647 Vadivel

At this instance my first aim was to make the app up and running at the earliest and then think of the rest! :)

So i decided to reseed the identity column to -2147483648 and have the values count upward towards zero.

DBCC CHECKIDENT ( MaxInt, ReSeed, -2147483648)
Go

Insert into MaxInt values ('Vadivel')
Insert into MaxInt values ('Vadivel')

Now we can insert another 2 Billions records into the table.

[Obviously this is just the idea. Before using it please test against your application code and whether it is fine with having negative values in the table etc.,]

That said, Once we reach the Max limit of INT this is not the only solution we have. Other options which we need to consider are:

1. Convert the column from INT to BigInt datatype
2. Other version of the given solution is to start the number from 0 and make it count downwards as –1, –2, –3 etc.,

Related article: http://sql-server-performance.com/Community/forums/p/7430/43219.aspx

Technorati Tags: ,,

Sunday, May 03, 2009

Google’s ROBO …

Only yesterday I came to know that we can add webtoim@gmail.com to our Gtalk contact list and start chatting with that BOT.

I understand that now, instead of Googling and digging Wikipedia for the correct answer, you can take assistance from the WebToIM Bot that is designed to answer such questions.

Few of my observations:

1. Almost for all questions it starts answering as ”hmm…” which looks odd to me.

2. Very often even for basic questions (or) for questions for which I would get the answer by googling is throwing me
“Sorry. I couldn't understand what you are asking. Please rephrase.”

3. I tried the following queries “Whats your age” it said sorry. I rephrased it as “What is your age” it again said sorry.

Finally I asked “How old are you?” and it answered
hmmm...
3 years and 9 months old


4. I got this response quite often as well

hmmm...
I found multiple ways to translate that.


But why can’t it display the different ways it is talking about. I guess this answer doesn’t help anybody!

5. I tried “Who is the author” and got this as the response

norman liebold, Stuart Payne, Stephen King, Steven Pinker, Andy Mabbett, Göran Tunström, Selma Lagerlöf, Johann Wolfgang Goethe, Matt Ruff, J. R. R. Tolkien, Richard P. Crandall, Arthur Hugh Matthews, bee wilson, Bareschius, Longinus and Charles Dickens

6. Next I tried “Sachin Tendulkar” and got this pathetic response!!

hmmm...
Sachin Tendulkar is the (born 1973), the [[India]]n [[cricket]] player

Related URLs:

1. http://www.webtoim.com/
2.
http://www.trueknowledge.com/

Technorati Tags:

Microsoft Surface Computing …

Check out these videos to understand about Microsoft Surface computing. Both the videos are really cool I loved it thoroughly.

 

Blocking unwanted sites using your HOST file…

For past few days my laptop got quite a few malwares / virus. I somehow got it cleaned using few free anti-virus softwares / with the help of various articles in the Internet.

During the course of research!!! I stumbled upon a very interesting article on how to make use of your HOST file to protect your machine. Check out http://www.mvps.org/winhelp2002/hosts.htm

This Host file for Windows, has entries to block ads, banners, 3rd party Cookies, 3rd party page counters, web bugs, and even most web browser hijackers. This is accomplished by blocking the internet connection to malware sites.

But one thing which I have noticed as of now is my laptop has become slightly slower after replacing my old HOST file with the new file given in that site.

I opened the HOST file and OMG there are hundreds of malware/ad related website listed there. I am not sure how they managed to collect all these address :)

Technorati Tags:

Avoid using functions in WHERE Clause!!

Thumb Rule is as much as possible avoid using FUNCTIONS within WHERE Clause of your SQL Query.

Let me show you some very basic examples to substantiate this theory.

--Table schema
Create table tblDateExample (dtStartDate datetime)
Go

-- Populate dummy records into the table
INSERT tblDateExample VALUES ('2009-04-15 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-14 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-13 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-12 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-11 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-11 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-11 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-11 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-09 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-08 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-07 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')
INSERT tblDateExample VALUES ('2009-04-10 12:00:00.000')

-- Create an Index
Create index IXStartDate on tblDateExample ( dtStartDate )
Go

Using Functions within WHERE Clause

SET SHOWPLAN_TEXT ON
Go

Select dtStartDate from tblDateExample where Convert(Varchar(8), dtStartDate, 112) = '20090411'

SET SHOWPLAN_TEXT OFF
Go

Checkout its execution plan, as expected it doesn’t make use of the Index fully

|—Index Scan (OBJECT:([Testbed].[dbo].[tblDateExample].[IXStartDate]),  WHERE:(CONVERT(varchar(8),[Testbed].[dbo].[tblDateExample].[dtStartDate],112)=[@1]))

Rewrite the same query without a function in the WHERE Clause

SET SHOWPLAN_TEXT ON
Go

Select dtStartDate from tblDateExample where dtStartDate >= '20090411' AND dtStartDate < '20090412'  

SET SHOWPLAN_TEXT OFF
Go

The execution plan shows us that it uses Index Seek.

|—Index Seek (OBJECT:([Testbed].[dbo].[tblDateExample].[IXStartDate]), SEEK:([Testbed].[dbo].[tblDateExample].[dtStartDate] >= CONVERT_IMPLICIT(datetime,[@1],0) AND [Testbed].[dbo].[tblDateExample].[dtStartDate] < CONVERT_IMPLICIT(datetime,[@2],0)) ORDERED FORWARD)

That said, there are instances where we can still make use of Index Seek in spite of using Functions within the WHERE Clause. Check out the below query for an example.

Select dtStartDate from tblDateExample
where
Convert(datetime,dtStartDate,101) > Convert(datetime,'2009-04-11',101) and
Convert(datetime,dtStartDate,101) < Convert(datetime,'2009-04-12',101)

The execution plan for the above query:

|—Index Seek (OBJECT:([Testbed].[dbo].[tblDateExample].[IXStartDate]), SEEK:([Testbed].[dbo].[tblDateExample].[dtStartDate] > CONVERT(datetime,[@1],101) AND [Testbed].[dbo].[tblDateExample].[dtStartDate] < CONVERT(datetime,[@2],101)) ORDERED FORWARD)

On trying to understand the reason for this behavior i noticed there is no real conversion happening in the above query. i.e., Both the Converted type and the original column type are the same :)

Technorati Tags:

Friday, May 01, 2009

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed ...

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

If you happen to get this error message in your SQL Server 2005 then read on:

In "Results to Grid" format one cannot display more than 100 resultset.  So just change the result format to either "Result to Text" or "Result to File" option

Example to reproduce this error:

Step 1: Ctrl + D (setting to Grid format for demo purpose)
Step 2: Try to print the current datetime for 101 times for example

Select GetDate()
Go 101

You would see the error as we are trying to display more than 100 resultset in a "Result to Grid" format.

Just in case you thought 100 is the limitation of GO command in SQL Server try the below script

Create table tblQuestion4
(
Sno int identity,
Fname varchar(20)
)
Go

Insert into tblQuestion4 values ('Test')
Go 101

For easy reference:

1. Ctrl + T :: Result will be displayed in Text mode
2. Ctrl + D :: Result will be displayed in Grid mode
3. Ctrl + Shift + F :: Result will be saved to a File
 
Technorati Tags:

Check out this comment ... really funny

Got this code comment as a email fwd. Its really funny :)


//
// Dear maintainer:
//
// Once you are done trying to 'optimize' this routine,
// and have realized what a terrible mistake that was,
// please increment the following counter as a warning
// to the next guy:
//
// total_hours_wasted_here = 16
//