Satheesh Kannan Chandra's Blog

One of my friends wanted to know the way to find statistics in a table. There are many ways to find the statistics in a table; I mostly prefer to use the following query.


select
object_name(id) as [Table / Index],
name [Statistics Name]
from sysindexes
where indexproperty(id,name,N'IsStatistics') = 1

Out of which, the system generated statistics always begins with '_WA_Sys_'. So, we can extract the system generated statistics by using the following query.

select
object_name(id) as [Table / Index],
name [Statistics Name]
from sysindexes
where indexproperty(id,name,N'IsStatistics') = 1
and
name like '_WA_Sys_%'

SQL Server 2005, Visual Studio 2005 and Biztalk Server 2006 Indian Launch programs are starting form 9th Dec 2005.

Get Ready for Bill Gates!

Bill Gates is specially visiting Bangalore on December 9, 2005 to kick off the India Launch of SQL Server 2005, Visual Studio 2005 and BizTalk Server 2006.


If you are a Bangalorean register for the event at http://www.microsoft.com/india/ready2005/blrmain/Register.aspx

I came across a web page related to Bill Gates; my favorite entrepreneur and a great techie. This page is from MTV News about Bill Gates. The most attractive part for me in this page is, Bill Gates view on Bangalored jobs. Here is the excerpt from the page.


Yago: Before we reach that day, certainly I know a lot of people in high school and college are hearing a lot about how India and China will take over a lot of American jobs. What do you say to that generation of young people now that's in college, that's now in high school or approaching high school?

Gates: India and China advancing and getting rich is fantastic news. What that means is that people who have been living in poverty, had ill health and illiteracy, are now getting jobs that allow them to be educated and realize their potential. If we had a choice today where India and China would be as rich as the United States, we should all want that, because not only would it be great for them, but they'd be buying more of our products. ... Their advancing isn't taking away from a finite pool of jobs. What it does is it grows the global economy. It does mean that we have to renew our skills, renew our leadership, and that largely means investing in the education system. So it doesn't have to be a bad thing, it just highlights that we've underinvested in education and in fact other countries do a better job.

At last, I have installed SQL 2005 CTP in my system and trying to get comfortable with Microsoft's most powerful database platform for the enterprise available in the market today.

I am planning to concentrate on What's New in SQL Server 2005. One such whats new feature is SQLCLR. To find more information on this, I have googled into some of my favorite sites. The one of such I found is Kimberly's white paper on SQLCLR. We can view the document in its pre-live version. Lot of information in this page... Hope, I grasp the information in the document in the coming days.

.
A geek discovered that nobody can't create a folder anywhere named as "con" in windows.

This is something pretty cool...and unbelievable... At Microsoft the whole Team, including Bill Gates, couldn't answer why this happened!

Try it out yourself...

This is a forward email I recieved from my friend some days ago. As the word CON is very familiar word to me, I thought of not to overlook the mail. When I read through the mail contents, it is just an anti-Microsoft bigot’s mail.

In those DOS days, the devices are given with constants, like…
CON --> for CONsole output device (that is Video display)
PRN --> for Printer
LPT1, LPT2,… --> for printers (those days Line Printers use these LPT ports)

In the pre-windows age, we use redirectors along with these device constants in our batch scripts. Say for example,

COPY CON filename is to create a new file. What this command doing is, COPYing CONsole contents into a file. All the contents entered after the command will redirected to the file.

Like wise, COPY filename PRN is the command to take a printout of the file.
COPYing the file contents into PRiNter. Similarly, we can use COPY filename LPT1 for printing.

As like in UNIX, we can use these device constants with redirectors and filters.

TYPE filename > CON -->Display the contents of the file into screen.
TYPE filename > PRN --> to take a printout of the file.

Seeing that the device constants are reserved words, we can’t use these words as a file name and they are features of DOS; not a bug.

We often use a dot “.” or a string “(local)” to connect the default instance in the local (stand alone) SQL Server. However this shortcut doesn’t work with cluster virtual (SQL) server. We need to give the actual virtual (SQL)server name. When we use these shortcuts, SQL Server resolved this as a local system. That is, it resolves the shortcut as a host name of the server. In the standalone default SQL instance, the Windows command HOSTNAME and T-SQL command SELECT @@servername gives the same result.

In clustered SQL Server environment, the shortcuts points to the node name which owns the virtual SQL Server not to the virtual instance name. Consequently, the shortcuts do not connect. Here, the Windows command HOSTNAME and T-SQL commands SELECT @@servername doesn’t give the same result. Still, if we want to use the shortcuts then we need to create server alias with shortcut literals as the alias name using “Client Network Utility”.

In DTS, selecting (local) in the server list rather than the name of the SQL server will enable the package to connect to the local server regardless of where the package is executed. If, however, we can specify a particular server name in the package and execute the package on a remote server, the package will attempt to connect to the original server rather than the local server. Understanding the effect of the server name in a package will enable us to ensure the package executes the way it was intended.

Books on Line (BOL) taught us how to compare NULL in T-SQL statements. Here is the excerpt from BOL about NULL comparison.

“To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.”

But, Query Builder tool which comes with SQL Server is not smart enough when it deals with NULL. ;-) The Query Builder does not know to change =NULL to IS NULL when performing NULL comparisons. So, when we build query with query builder, change "=NULL" to "IS NULL" in the query manually.

There is more than one way to achieve a solution. Here is some of the “more than ones” from Production DBA’s point.

To find the SQL Server start date and time

Way1:

SELECT crdate from master.dbo.sysdatabases (NOLOCK) WHERE name = 'tempdb'

Way2:
SELECT login_time FROM master.dbo.sysprocesses (NOLOCK) WHERE spid = 1


To find the file type from sysfiles system table.

Way1:

SELECT filename,
CASE
WHEN (64 & status) = 64 THEN 'Log'
ELSE 'Data'
END AS FileType
FROM sysfiles (NOLOCK)


Way2:

SELECT filename,
CASE
WHEN groupid = 0 THEN 'Log'
ELSE 'Data'
END AS FileType
FROM sysfiles (NOLOCK)


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