Vadivel's blog

As you might be knowing the system stored procs would be prefixed with "SP_". If we prefix "sp_" in our user-defined stored procedure it would bring down the performance because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: 1) Master DB, 2) The stored procedure based on the fully qualified name provided, 3) The stored procedure using dbo as the owner, if one is not specified. So, when you have the SP with the prefix "sp_" in the DB other than master, the Master DB is always checked first, and if the user-created SP has the same name as a system stored proc, the user-created stored procedure will never be executed. For example, Let's say that by mistake you have named one of your user defined stored procedure as "sp_help" within the database! Create proc sp_help as Select * from dbo.empdetails Now when you try executing the stored procedure using the below script you would re...

There were few questions regarding "Passing table names as parameters to stored procedures" in Dotnetspider forums. I don't feel this to be a write way of coding. Still many persons are asking similar questions in the forums thought would write a post on "SP_EXECUTESQL()" Vs "EXECUTE()". Sample SP to pass table name as parameter: Create proc SampleSp_UsingDynamicQueries @table sysname As Declare @strQuery nvarchar(4000) Select @strQuery = 'select * from dbo.' + quotename(@table) exec sp_executesql @strQuery -------- (A) --exec (@strQuery) ---------------------- (B) go Test: Execute dbo.samplesp_usingdynamicqueries 'EmpDetails' In the above stored procedure irrespective of whether we use the line which is marked as (A) or (B) it would give us the same result. So what's the difference between them? One basic difference is while using (A) we need to declare the @strQuery as nvarchar or nchar. (i) It would throw the below error if we ...

Let me start by saying, I have never taken a Microsoft exam previously. I was having a "Microsoft Exams 100% Discount Coupon" which was valid till 31-Dec-2006 so thought of utilizing it. I prepared well for 70-431 Microsoft SQL Server 2005 - Implementation and Maintenance paper and today morning at 10.45 AM I took up the online test @ NIIT Adyar. I am glad that I have cleared it with a good score of 982 (out of 1000). Now I am a " Microsoft Certified Technology Specialist " :) There were 52 questions and out of which approximately 15 where simulation questions (that was where I spent most of my time). Though the score might look big, I need to accept that I answered 20 to 30% 0f questions without much of confidence. Hope the choices I made where accidently correct :) I was bit tensed in the morning till the time I answered the first question. Reason being, couple of my friends know that I am going to take up the test today. I was afraid what would they think if I f...

As per RBI regulation I guess all banks should have a "Do Not Distrub Me" or "Do not call me" :) web page inorder to value customers privacy. I get atleast couple of telemarketing calls a day from one bank or other. Its really irritating and i was looking for a way to avoid them totally. Only then i came to know about these "Don't call registeration pages" for various banks. Looooong Live RBI :) :) 1. ICICI Bank - As per the site itseems it would take 15 days for the number to be removed from the telemarketing list! 2. Citibank -- I didn't find the time frame in this site. 3. Deutsche bank -- As per the site itseems it would take 30 days for the number to be removed from the telemarketing list! 4. Standard Chartered Bank -- As per the site itseems it would take 30 days for the number to be removed from the telemarketing list! 5. HDFC Bank -- As per the site itseems it would take 45 days for the number to be removed from the telemarketing list!...

First I suggest you to go through my earlier article on subject "Delete VS Truncate" here . Truncate is also a logged operation, but in a different way. It logs the deallocation of the data pages in which the data exists. Let us create a dummy table for understanding this example: Create Table dbo.TruncateTblDemo (Sno int) Go Insert few records into it: Insert into dbo.TruncateTblDemo values (1) Insert into dbo.TruncateTblDemo values (2) Insert into dbo.TruncateTblDemo values (3) Go You could see that the table has 3 records in it: Select * from dbo.TruncateTblDemo Execute a truncate statement: Truncate Table dbo.TruncateTblDemo After the above statement you can't retrieve the data back because it is an explicit transaction . Unless or until you have set Implicit_Transactions to off . That is, Internally sql would have taken our above truncate statement as follow: Begin Tran Truncate Table dbo.TruncateTblDemo Commit Tran Select * from dbo.truncatetbldemo Getting back data...

Check out this article in computerworld.com . May be after this atleast we need to take our company policies very seriously :) How many of you guys are deleting unwanted mails from your official mailbox as per your company policy? Technorati tags: Microsoft

Read this first: 1. This has no real time usage. So try this out only when you are free :) 2. This script was just done for fun and nothing more. 3. Before executing this script change your result display mode to 'Text' (Ctrl + T) 4. There are easier way of doing the same thing. Just to make it look complex I have done this way :) Code snippet Starts here: Set nocount on Declare @TblLayout table([ID] int Identity, Canvas Char(75)) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Insert into @TblLayout Select Replicate (Convert(Varchar, 0x7E), 75) Inser...

Offlate, many of my friends where talking about this problem. It seems they ask this question frequently in Microsoft Interviews :) I remember asking this question to freshers in 2005! Just thought I would refresh my knowledge also on this :) So here are few samples which I tried for your reference. Method 1: Using intermediate temp variable int intNumOne = 1, intNumTwo = 2; int intTempVariable; //Swapping of numbers starts here intTempVariable = intNumOne; intNumOne = intNumTwo; intNumTwo = intTempVariable; Response.Write("Value of First Variable :: " + intNumOne.ToString() + "<br>"); Response.Write("Value of Second Variable :: " + intNumTwo.ToString() + "<br>"); Method 2: Without using Temp Variable and by using 8th standard Mathematics :) int intNumOne = 11, intNumTwo = 22; //Swapping of numbers starts here intNumOne = intNumOne + intNumTwo; intNumTwo = intNumOne - intNumTwo; intNumOne = intNumOne - intNumTwo; Response.Write(...

In this post let me explain about the three different types of Operations one can do with Partitions. They are: 1. Split Partition 2. Merge Partition 3. Switch Partition (Important of the lot) Before reading further, make sure that you have read my earlier posts. That is, this and this . Split Partition For splitting a partition we need to make use of “Alter Partition Function” syntax. So in our existing “Partition function” lets create a new range with boundary value “Jan 01, 1970”. Alter Partition Function PF_DOB_Range() Split Range ('01-01-1970') Now if we execute this code snippet it would throw an error something like this: Msg 7707, Level 16, State 1, Line 1 The associated partition function 'PF_DOB_Range' generates more partitions than there are file groups mentioned in the scheme 'PS_DOB_1'. So the way to split a partition is: Step 1: Create a new Filegroup (if at all already you don’t have an extra filegroup) Step 2: Make use of that Filegroup while al...

Lets assume that we have table which contains records of our company transaction starting from the date when our company was started 15 years ago! Hope you would understand that the table would have hell a lot of data as it would be holding 15 years of data. But effectively we might be using only last 2 months or 1 year data at the max (very frequently). For each query, it would be processing through this huge data. Bottomline as the table grows larger the performance would go for a toss, also scalabiity and managing data would also be difficult. Hope I have made the point clear. With the help of partitioning a table we can achieve a great level of performance; also managing tables easier. In our case, one of the way to increase the performance would be to “Partition” the data on a yearly basis and stored on a different filegroup (SQL 2005 allows you to partition tables based on specific data usage patterns using defined ranges or lists). For further theoritical knowledge on this subje...

It’s an interesting find. I got few mails asking me suggestions on the way to handle situations where “the application needs to be updated with the latest code base”. I was preparing a blog post something like this: 1. Normally sites are deployed in Web farm scenarios. If your app is also deployed in a web farm then it’s better to bring one server down update the code base there while all the user request would be served by the other servers in the farm. This way the downtime of the application would be almost zero. 2. If at all your application is deployed on a single server then either you need to face the downtime :) or temporarily create another virtual directory with the old code base. This Virtual directory would be functional till the time you update the actual directory with your latest code base. There would be some negligible amount of downtime here. 3. If you can’t create a new virtual directory for some reason! Then create a static page (“SiteDownForMaintanance.htm”) and ba...

PDF (Portable Document Format) reader is a very important software one needs. As now-a-days most of the product user manual, eBooks, visa application forms etc., are in PDF format. Today’s computers almost always come with Adobe PDF reader installed by default. Till few weeks back I was also using it without much satisfaction!! No doubt Adobe PDF reader is a great product but I hate it for the following points: 1. I feel that Adobe PDF reader software is really bulky. 2. Loading time of PDF document is unnecessary in Adobe reader 3. Installation of Adobe PDF reader takes at least >= 5 minutes. For past few weeks I am fiddling with another PDF reader called “ Foxit Reader 2.0 ”. In my experience with this new reader, I haven’t found any of the above mentioned disadvantages which I have with Adobe PDF reader . Let me explain those 4 points in detail now: i) I feel that Adobe PDF reader software is really bulky. First of all downloading Adobe PDF reader isn’t an easy joke :) it takes ...

In continuation to my previous post on IndiMix I got registered to the webcast couple of days back. I have gone through the agenda of the seminar and was interested only in these two sessions. 1. The Genesis of Creativity and Innovation – The Next Web Now (10:15 AM to 11:15 AM) Speaker: Steve Ballmer , CEO, Microsoft Corporation. 2. Designing for the Next Web Now – Experiences and Expressions that Capture the Imagination (2 PM to 3:15 PM). Speaker: i) Leon Brown , Regional Designer and User Experience Lead, Microsoft Corporation APAC (Singapore) ii) Pandurang Nayak , Developer Evangelist, Microsoft Corporation India iii) Deepak Gulati , Developer Evangelist, Microsoft Corporation India iv) Dax Pandhi , CEO, Nukeation Studios Not that I wasn't interested in the other sessions. Since that I am in vacation and browsing from home, I wanted to keep my broadband usage to the minimum :) Today Morning, when i tried accessing the URL (which they have sent me in the registration confirmatio...

Wow I am really excited to know that i am one among the Top 20 Microsoft India BlogStar winners :) Check out http://www.microsoft.com/india/blogstars/winners.aspx On the flipside, I am bit disappointed that I didn't make it to the Top 5 :( Because the Top 5 guys gets a chance to meet Steve Ballmer in Mumbai tomorrow. But overall this Month seems to be a really good one to me :) Because starting from November 1st, 2006 till date i have got 3 awards/gifts (including this) from various technology related sites. The other two are: 1. I have won the first prize in community-credit website this month. 2. I have won the " Best Member Award " in dotnetspider. Technorati tags: Microsoft , Steve Ballmer

Visual Studio 2005 Tools for the 2007 Microsoft Office System (Visual Studio 2005 Tools for Office Second Edition or VSTO 2005 SE for short) is a free add-on to Visual Studio 2005 that empowers developers to build applications targeting the 2007 Office system. Developers can now harness the benefits of the 2007 Office system platform and create Office-based solutions using the professional development environment of Visual Studio 2005. Go through http://msdn.microsoft.com/office/tool/vsto/2005SE/default.aspx to know about the "Feature List", "Download links", "Demos - Video" and "Artiles" on this product.

The Microsoft SQL Server Health and History Tool (SQLH2) allows you to collect information from instances of SQL Server, store this information, and run reports against the data in order to determine how SQL Server is being used. This version of SQLH2 supports SQL Server 2005 RTM collection.SQLH2 collects four main types of information: 1. Feature Usage – What services/features are installed, running and level of workload on the service. 2. Configuration Settings – Machine, OS and SQL configuration settings, SQL instance and database metadata. 3. Uptime of the SQL Server service 4. Performance Counters (optional) – Used to determine performance trends You can download this tool here Technorati tags: SQL Server 2005

The Database Publishing Wizard enables the deployment of SQL Server 2005 databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a SQL script file which can be used to recreate the database in shared hosting environments where the only connectivity to a server is through a web-based control panel with a scripting window. The Database Publishing Wizard is currently a command line tool, though future releases will add a graphical user interface. 1. Current Release 2. Command Line Interface 3. Supported Objects & Types 4. Known Issues 5. Roadmap for Future Releases 6. FAQ 7. Tutorial: Upload the TimeTracker ASP.NET starter kit to your hosted account Source: codeplex Technorati tags: SQL Server 2005

Below is the extract of the mail which I got from Tony John of dotnetspider.com couple of days back. Vadivel Mohanakrishnan, We are glad to inform you that you are selected as the RapTier Gift winner for the month of October 2006 for your valuable contributions to dotnetspider.com . We are looking forward to see more contributions from you in future. Winners list is here :: http://www.dotnetspider.com/gifts/Winners.aspx Gift which I would be getting is called "RapTier Software" . RapTier is a template-based ASP.NET, C#, VB.NET and SQL code generator that creates robust database driven applications for a wide range of database. Features: ASP.NET, C#, VB.NET and SQL code generation; DataSet and Typed Data Transfer Object support; Stored Procedure and Dynamic SQL support; Existing stored procedure wrapping; Support of many database engines; Template-based design, WebForm UI generation, Database documentation generation To know more about it, check this out http://www.sharppower....

I am glad that few minutes back i came to know that I have won the 1st prize in Community-credit for the month of October 2006. Below is the extract from the mail which I received from David (Admin of Community-credit site). Hello Vadivel, Congratulations. You won 1st Prize in the October 2006 Contest. As you may know, a lot of people competed, but only a few won. Nicely done! Please also keep in mind that your prize is not just a stupid, geeky gift, but it's also a symbol to represent your contributions to the development community. When folks ask you how you won, you can tell them that it was by helping out other geeks. Be proud and brag. Check out this page to know about the Prize I got :: http://www.community-credit.com/CommunityCreditPrizePage.aspx and choose October 2006 in the dropdown.

For some, working with Databases is fun and for others, it could possibly be a nightmare. Understanding the structure of a Database schema from the available documentation could spell doom for many developers. A picture is worth a thousand words. This golden saying applies aptly to Databases! Yes, often, a pictorial representation of a Database conveys more meaning about the Database Schema than the SQL scripts generated or other documentation that may be available. One of the first thing developers wants to do when starting work on a new project is to understand the Database schema. Sometimes, understanding a complex database schema can be simplified largely by a relationship diagram. The Database diagram tools in the SQL Server enable administrators and developers to create Database diagrams very easily. This article demonstrates an efficient way to move DB diagrams from one Database to another. We are going to use two sample Databases for this purpose. They are named PlayDB and Play...

Check out syncfusion.com It has 300+ ASP.NET related Frequently asked questions. It would be a good read. Technorati tags: ASP.NET

Approximately 20-25 days back I came to know about Community-Credit site and I started participating in it actively. See the below screenshot to know where I stand. FYI, Lorenzo Barbieri is maintaining his lead from day 1. For the past 10+ days I am maintaining the second position and just 4 days left to know who is the winner of this month. Do you think I can make it to the top? Technorati tags: Community-credit

This was the question asked by a member here http://www.dotnetspider.com/qa/ViewQuestion.aspx?QuestionId=59232 Though I have answered there itself, thought I would document it here also for future reference. I) List all Stored procedures in a database: Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%' OR Select * from sys.objects where type='p' and is_ms_shipped=0 and [name] not like 'sp[_]%diagram%' Please note that I have added the 'NOT LIKE' part to get rid of stored procedures created during database installation. II) Delete all [user created] Stored procedures in a database: Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%' This would list down all the user defined Stored procedure as 'Drop Procedure procedureName' in the result pane. Just copy-paste it into the query window ...

1. Partitioning is fully depended on Filegroups. 2. Start table partitioning as early as possible. So that huge data movement can be avoided later. 3. A file would be grouped to one and only Filegroups. Actually Filegroups improves database maintenance, performance and Online restore. 4. For your information, default Filegroup is always “primary filegroup“. If at all you don’t believe me! Create a dummy table something like this, Create Table Test ( sno int ) Go Now in your SQL Mgmt Studio, right click on that table and check the properties yourself. 5. All “System Objects” have to belong to PRIMARY Filegroup. 6. It’s recommended, to have all “User Objects” created in another Filegroup. 7. In SQL Server 2000, any user object can’t belong to more than one Filegroup. But it’s possible now in SQL Server 2005. That is, a table data can be partitioned and stored into multiple Filegroups. 8. If you have two large tables, put them into different Filegroups. Filegroups are recommended being on...

Recently in a discussion forum I came across this question "I have a small Doubt in SQL server. The database name is 'AdventureWorks' and the table name is 'Production.Culture'. What is the query to fetch records from that table." The answer to this is : Select cultureid, [name], modifiedDate from AdventureWorks.Production.Culture There was another member of the forum who was saying that we need to only use the below query: Select * from AdventureWorks. dbo.Production .Culture Please note that he has mentioned two Schema names (dbo and Production) in a single query. This query wouldn't work and I thought I would explain the concept of "Schemas" in SQL Server 2005 to him. In short, a) Schema is similar to Namespaces in .NET b) It helps in logical grouping of tables. c) To view all existing schema in a database, for example within AdventureWorksDB :: 1. Open ur SQL Mgmt studio 2. Go to AdventureWorks DB and expand it 3. Expand the Tables 4. Now jus...

For past 15 days I am actively participating in the discussion forums of dotnetspider. Yesterday there was a question relating to SQL Server which seems to be asked in interviews very often. i.e., How to find out the 2nd highest salary of an employee? Thought I would give out an sample for those who are yet to find an answer for this. For that purpose I have given a table structure with few sample records inserted to it. -- Table Structure Create table employee ( [Name] varchar(20), Sal int ) Go -- Populate Sample Records Insert into employee values ('Vadivel', 80000) Insert into employee values ('Vel', 70000) Insert into employee values ('xxx', 40000) Insert into employee values ('Sneha', 60000) The Solution: Select top 1 [Name], Sal from ( Select Top 2 [Name], Sal from employee order by Sal Desc ) AS A1 Order by Sal Technorati tags: SQL , Databases , SQL Server , SQL Server 2005

Guruji.com - India's local search engine! has been launched in Bangalore on 12th of this month. The site seems to be focused on Indian consumers. May be they wanna become Google of India! I tried out few searches - the results and performance are really impressive. The search strings which i tried are as follows: 1. Pizza Adyar 2. Verizon Guindy Quite a few of my search strings also failed. Its understandable as they are still in Beta! But I feel its definitely a good start. Guys use it and share your experience .... If you like to know more about the Investors, Management Team etc., check out this. Extract from TechTree : Gaurav Mishra, co-founder and coo, guruji.com explains, "90% of Internet search queries are local in nature, and guruji.com will deliver better search results than any other search engine in these instances. For example, if a user types a search "Pizza in Koramangala, Bangalore " or "Chinese restaurant Juhu, Mumbai" the user will be able...

From a fellow MVP ( Harish Ranganathan ) I came to know about this interesting fact. Old News: If you search for "Failure" in Google and click on "I'm feeling lucky" button it would take you to President Bush's homepage :) Latest News: Now, interestingly, if you search for the word "Search" in Google and click on "I'm feeling lucky" button, it goes to Windows Live Search Page. Technorati tags: Google , Microsoft , Windows Live

This post is written having final year students in mind. The first part of this series! is here . So if at all you haven't read it before, I suggest you have a look at it also. Apart from your regular preparation for the final year exams and projects, it’s always better to do the following: 1. Spend time on group discussions 2. Work on Quantitative aptitude and reasoning questions again and again. Some of the famous authors in this topic are listed below for your easy reference. a. George Summers b. Shakuntala Devi c. R.S. Agarwal d. P.S. Agarwal e. Barrons GRE 3. Spend time on websites to know more about Interviews. For example: www.freshersworld.com 4. If you are informed by the company for an interview, collect data regarding the company (visit their website first without fail) 5. Prepare a write-up on your own vernacular. Try to translate it. Practice it in-front of your friends or your lectures or mirror and fine tune it. 6. Prepare a neat CV. Interview Telephonic: 1. Provide...

<html> <head> <title>Check the Size of a File -- Javascript</title> <script language="JavaScript"> function CheckFileSize() { var objSize = new ActiveXObject("Scripting.FileSystemObject"); var strFileName = objSize.getFile(document.frmFileUpload.strFile.value); var SizeOfFile = strFileName.size; alert(SizeOfFile + " bytes"); } </script> </head> <body> <form name="frmFileUpload"> <input type="file" name="strFile"> <input type="button" name="btnSize" value="Show file size" onClick="CheckFileSize();"> </form> </body> </html> The above code snippet will work only if ActiveX is supported @ the client end.

Abstract In this article I have discussed in detail about the easier way of paging records using SQL Server 2005. In the due course I have introduced the nuances of using TOP keyword and the cool enhancements added to it. Along with that I would show you how to use TOP command to do paging in SQL Server 2000, albeit at a performance loss, then show how the same result can be achieved with higher performance thanks to new features in SQL Server 2005. Introduction Databases products always attract me for many reasons. One simple reason is, .NET framework 1.0 was released and then in a short time its “version 1.1” has been released. If at all you are watching the market you would know by this time, few months back .NET Framework 2.0 has also been released. So what’s the point I am trying to make? The application layer or the front end layer changes very frequently and we need to be updating our self on a daily basis. That’s the pace of the technology either keep up or be left out :) On th...

I suggest all SQL developers to have a look at http://SQLKnowledge.com . The site is rich with information on SQL Server and its really informative. I read the history of SQL Server from that site and here is the summary of it: 1. 1993 - SQL Server 4.21 for Windows NT 2. 1995 - SQL Server 6.0, codenamed SQL95 3. 1996 - SQL Server 6.5, codenamed Hydra 4. 1999 - SQL Server 7.0, codenamed Sphinx 5. 1999 - SQL Server 7.0 OLAP, codenamed Plato 6. 2000 - SQL Server 2000 32-bit, codenamed Shiloh 7. 2003 - SQL Server 2000 64-bit, codenamed Liberty 8. 2005 - SQL Server 2005, codenamed Yukon 9. Next release - codenamed Katmai (not confirmed ) Technorati tags: SQL , Databases , SQL Server , SQL Server 2005