Vadivel's blog

Check out http://www.studygs.net/genius.htm Extract from that site: "Even if you're not a genius, you can use the same strategies as Aristotle and Einstein to harness the power of your creative mind and better manage your future." The following eight strategies encourage you to think productively, rather than reproductively, in order to arrive at solutions to problems. "These strategies are common to the thinking styles of creative geniuses in science, art, and industry throughout history." Let me try and think like a genius going forward :)

Check out this Microsoft research article on choosing whether to store your large objects in your database or filesystem. As expected from the common wisdom, objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, Read on ...

This is one of an excellent article I have read about "Salary Negotiations" . Spare some time and go through this article .... needless to say, it would be worth your time.

Whenever I get into an official call I used to open notepad and start to take notes. After few days if I open that notepad I used to wonder on which particular days call i took this note :) [Yeah sometimes I used to forget to type in the current date and time before starting take notes]. Few weeks back I got to know this interesting tip which I have explained below via my collegue. Step 1: Open notepad Step 2: Type .LOG as the first line of the file and press the carriage return [Enter key] Step 3: Save and close the file. Step 4: Double click on the file and open it ... you could notice that notepad appends the current datetime at the end of the file and places the cursor on the next line. Each and everytime you open the file it automatically appends the current datetime and places the cursor on the next line. Cool isn't it. This way one can make use of notepad as their virtual diary.

Lets assume that you have data like '1.1.11', '1.1.3','4.1.2' etc within a column in a table. If you do Select * from TableName order by FieldName it won't give you the data in the correctly sorted order. Table Structure: Create table tblSortIndexNumbers ( sno int identity, IndexNumber varchar(100) ) Go Insert scripts for generating sample data within our test table: Insert into tblSortIndexNumbers values ('1.1.1') Insert into tblSortIndexNumbers values ('2.1.1') Insert into tblSortIndexNumbers values ('3.1.1') Insert into tblSortIndexNumbers values ('1.1.3') Insert into tblSortIndexNumbers values ('1.1.2') Insert into tblSortIndexNumbers values ('1.2.1') Insert into tblSortIndexNumbers values ('1.1.4') Insert into tblSortIndexNumbers values ('1.2.2') Insert into tblSortIndexNumbers values ('1.3.1') Insert into tblSortIndexNumbers values ('2.2.1') Go Now, try running the below sc...

As everyone of us know, by default if we create a Primary Key (PK) field in a table it would create Clustered Index automatically. I have been frequently asked by some of blog readers on "Is there a way to create PK fields without clustered Index?". Actually there are three methods by which we can achieve this. Let me give you the sample for both the methods below: Method 1: Using this method we can specify it while creating the table schema itself. Create Table tblTest ( Field1 int Identity not null primary key nonclustered, Field2 varchar(30), Field 3 int null ) Go Method 2: Using this method also we could specify it while creating the table schema. It just depends on your preference. Create Table tblTest ( Field1 int Identity not null, Field2 varchar(30), Field 3 int null Constraint pk_parent primary key nonclustered (Field1) ) Go Method 3: If at all you already have a table which have a clustered index on a PK field then you might want to opt for this method. Step 1: Find...

NASDAQ, which became the world’s first electronic stock market in 1971, and remains the largest U.S. electronic stock market, is constantly looking for more-efficient ways to serve its members. As the organization prepared to retire its aging Tandem mainframes, it deployed Microsoft® SQL Server™ 2005 on two 4-node Dell PowerEdge 6850 clusters to support its Market Data Dissemination System (MDDS). Every trade that is processed in the NASDAQ marketplace goes through the MDDS system, with SQL Server 2005 handling some 5,000 transactions per second at market open. SQL Server 2005 simultaneously handles about 100,000 queries a day, using SQL Server 2005 Snapshot Isolation to support real-time queries against the data without slowing the database. NASDAQ is enjoying a lower total cost of ownership compared to the Tandem Enscribe system that the SQL Server 2005 deployment has replaced.

One of the sql newsgroup member asked this question: Guys, I have a table by name "TblRecursive" which has following data ID, Name, ParentID 1, A, 0 2, B, 1 3, C, 2 4, D, 2 5, E, 1 Using the above data I just want to generate a result as below A A\B A\B\C A\B\D A\E Can you help in writing a query for this? My Solution: We can achieve this by calling a "User Defined Function (UDF) recursively". Let me show how to do that with a working example. --Table creation Create table tblEmployeeInfo ( EmpId int primary key, EmpName varchar(30), MgrId int ) --Insert test data into it Insert into tblEmployeeInfo values(1, 'Director', null) Go Insert into tblEmployeeInfo values(2, 'Joint Director', 1) Go Insert into tblEmployeeInfo values(3, 'Secretary', 2) Go Insert into tblEmployeeInfo values(4, 'Joint Secr.,', 3) Go Insert into tblEmployeeInfo values(5, 'Legal Advisor', 1) Go -- User defined function for your requirement Create function ...

I wrote a small program for screen scraping any sites using XmlHttp object and VBScript. I know I haven't done any rocket science :) still I thought of sharing the code with you all. XmlHttp -- E x tensible M arkup L anguage H ypertext T ransfer P rotocol An advantage is that - the XmlHttp object queries the server and retrieve the latest information without reloading the page. Source code: < html > < head > < script language ="vbscript"> Dim objXmlHttp Set objXmlHttp = CreateObject("Msxml2.XMLHttp") Function ScreenScrapping() URL == "UR site URL comes here" objXmlHttp.Open "POST", url, False objXmlHttp.onreadystatechange = getref("HandleStateChange") objXmlHttp.Send End Function Function HandleStateChange() If (ObjXmlHttp.readyState = 4) Then msgbox "Screenscrapping completed .." divShowContent.innerHtml = objXmlHttp.responseText End If End Function </ script > < head > < body > ...

Last Updated on October 10, 2007 * Latest articles are added at the end of this post. Articles relating to SQL Server 7.0 / 2000 1. [MSDN] Database documentation 2. Returning comma seperated details from a table ... 3. Quick search within ALL stored procedures ... 4. Find whether a column is identiy or not 5. Encryption in SQL Server 7.0 6. About sp_readerrorlog 7. Useful TSQL code snippets for beginners 8. Copying database diagrams ... 9. Query to display Null values at the bottom ... 10. Alternate rows ... 11. Running number !! 12. Doing case sensitive searches 13. Easiest way to add comments to your SQL 2k code ... 14. Listing records from 10 to 15 (for ex) without using where clause 15. Is sorting possible in Views? 16. Creating thumbnails from binary content 17. Saving an image as binary data into SQL Server 18. Reclaim Unused Table Space 19. Encrypting ALL SP's ... 20. Database Compatibility ... 21. About TimeStamp datatype of SQL Server ... 22. Grouping Store...

I saw an question in one of the SQL newsgroup which I visit frequently (offlate). That person is having a problem with retriving data from a table. Let me explain it in detail. Sample table structure: Create table empTest ( [Id] int identity, Contact varchar(100), Employee_Id int ) Go Let us populate few records into the table: Insert into empTest (Contact, Employee_Id) values ( 'vmvadivel@gmail.com', 101) Insert into empTest (Contact, Employee_Id) values ( '04452014353', 101) Insert into empTest (Contact, Employee_Id) values ( 'vmvadivel@yahoo.com', 102) Insert into empTest (Contact, Employee_Id) values ( '9104452015000', 102) Go And now, as you could see each employee has more than one contact details. So if you query the table as Select * from EmpTest it would list couple of records for each employee. Instead of this won't it be nice if we could generate comma seperated contact details for each employee. i.e., There would be only one record for an...

This article would explain in detail the methods involved in searching strings within ALL stored procedures. I am sure there might have been situation where you want to find out a stored procedure where you remember writing some complex logic. Won't it be nice if we can find out that stored procedure where we have already written that important piece of code .. so that we can reuse? If your answer is "yes" read on. Points to note before executing this SP: 1. I have written 2 methods for this purpose. If we want this SP to be in the MASTER database then set @method =1. If not set it to 2 2. If @method is set to 2 then it is advisable to change the SP name. As you know only SP's which exist in MASTER database needs to be prefixed with "SP_" (for performance reason). The Stored Procedure: Create Procedure sp_searchForStoredProc ( @searchString varchar(100) ) As /************************************************** Stored Procedure: sp_searchForStoredProc CreatiOn...

In one of the newgroup somebody was asking the way to find whether a column is identity or not. I thought I would write my answer there as an article for the benefit of those who have the same doubt. I know of two ways of finding whether a given column is an identity column or not. Let me try and explain it ... Sample Table structure: Create a sample table with an identity column in it. Create table [order_details] ( OrderId int identity, OrderName varchar(10), UnitPrice int ) Method 1: [Easiest way] Select ColumnProperty(Object_id('order_details'), 'OrderId', 'IsIdentity') Method 2: For some reasons if you don't want the above method!! then try this one Declare @colName varchar(100) Declare @RetColName varchar(100) Set @colName = 'OrderId' -- Specify the column name for which you want to check --Status column = 128 means its an identity column Select @RetColName=[name] from syscolumns where (status & 128) = 128 and id=(select id from sysobjects...

Often people ask me "I have a table and there are few views based on that table. When I make a structural change to my table it invalidates all those views. So we are left out with no other option than to drop those views and recreate it. But is there any alternate way for this?". For all those people who have this doubt in mind .. read on. i) Create this sample table for demo purpose Create table tstTestingUpdateView ( Sno int identity, [Name] varchar(10), Mail varchar(50) ) ii)Insert some dummy records Insert into tstTestingUpdateView Values('Vadivel','smart3a@yahoo.com') iii) Create a view based on that table Create view tstView1 As Select * from tstTestingUpdateView iv) Execute the newly created view and have a look at the output Select * from tstView1 v) Now add a new column to the table Alter table tstTestingUpdateView add ContactNumber Varchar(20) --Now if you execute the view it won't list the newly added column in it Select * from tstView1 vi) So...

Try double clicking any word within my blogspace. For example, if you double click on a word "response" then you would get an definition of that word in detail. Try it out for yourself and do let me know whether this would be helpful for you guys.

Estonia has sucessfully conducted an national election with online voting option. A tiny Baltic nation last week became what appears to be the first country to open its local elections to Internet voting on a nationwide level--although only about 1 percent of the votes were cast online. Check out the full article here Estonia pulls off nationwide Net voting Needless to say, internet voting would save lot of time and energy for almost everybody. But I seriously donno whether in near future it would be possible in India! I personally feel that we need to improve a lot in the following fields "Security", "Infrastructure" and "Computer awareness". I don't think this would be possible here in India or Tamil nadu for atleast next 10 years.

After a long time I visited CNUG (Chennai .NET User Group) . When I was going through the questions I saw a question posted by "Nitin" asking Is it possible to encrypt data within data server (SQL Server 7.0) URL of that post can be seen here >> http://groups.msn.com/ChennaiNetUserGroup/general.msnw?action=get_message&mview=0&ID_Message=9327&all_topics=0 My response to that post: There are two undocumented functions in SQL Server (since SQL Server 6.5). They are: 1. Pwdencrypt and 2. Pwdcompare. Pwdencrypt -- It uses one way encryption. That is, it takes a string and returns a encrypted version of that string. Pls note that in one way encryption you can't get back the actual string (i.e., you can decrypt the encrypted data). Pwdcompare -- It compares an unencrypted string to its encrypted representation to see whether they match. Since it is undocumented functions there is a possibility that MS can remove or change it at anytime without prior notice. So...

In this article we would look into the easiest way of importing an image as BLOB content into a SQL table. 1. Openrowset has new bulk features introduced in SQL Server 2005. 2. Openrowset supports bulk operations through a built-in bulk provider that allows data from a file to be read and returned as a rowset. 3. Using the BULK rowset provider you can load a file into a table's column using regular DML. 4. Unlike SQL Server 2000, instead of being limited to Text, NText and Image datatypes for large objects, in SQL Server 2005 we can also use Varchar(max), nvarchar(max) and Varbinary(max) datatypes. The new MAX option allows you to manipulate large objects the same way you manipulate regular datatypes 5. With OPENROWSET you'll be able to return a rowset from a file as a single varbinary(max), varchar(max) or nvarchar(max) data type value. We'll use "SINGLE_BLOB", "SINGLE_CLOB" or "SINGLE_NCLOB" to diffentiate what kind of single-row, ...

I came to know about "Notorious B.G on MTV" from my favourite bloggers blogspace >> http://scobleizer.wordpress.com/2005/10/29/why-do-i-work-at-microsoft/ I went through the complete transcript @ http://www.mtv.com/thinkmtv/features/education/gates_forum/ and the below QA is what I liked the most. 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 on...

In this article we would look into basics of paging records in SQL Server 2005. I have provided couple of methods with correspondng code snippets for our better understanding. Code snippet for the sample table: Create Table tstSQLPaging ( Sno int, FirstName varchar(50), LastName varchar(50), EmailId varchar(100), Salary int ) Go Enter sample data into that table: Insert into tstSQLPaging values (1, 'Vadivel','M','vmvadivel@yahoo.com',10000) Insert into tstSQLPaging values (2, 'Sailakshmi','L','abc@yahoo.com',9000) Insert into tstSQLPaging values (3, 'Raj','A','aRaj@yahoo.com',11000) Insert into tstSQLPaging values (4, 'Dhina','B','bDhina@yahoo.com',25000) Insert into tstSQLPaging values (5, 'Siddharth','s','itissiddhu@yahoo.com',6000) Insert into tstSQLPaging values (6, 'Vicky','L','vicky@yahoo.com',19000) Insert into tstSQLPaging values (7, ...