tag:blogger.com,1999:blog-58492652012-01-28T07:07:42.550+05:30SQL Server Tips n Tricks ...Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.comBlogger4791100tag:blogger.com,1999:blog-5849265.post-22203142014939770502011-11-06T12:06:00.000+05:302011-11-09T07:22:59.442+05:30

SARGable is the short form of  "Search ARGument able". A condition in the SQL Query is said to be SARGable if the database engine can take advantage of an available Indexes and do an INDEX SEEK instead of Table Scan / Index scan to speed up the execution of that query. One of the major mistakes developers do which makes a query non-SARGable is they use functions directly on a column in the

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-37537549101961131322011-10-30T18:40:00.000+05:302011-11-01T08:01:08.677+05:30

I would strongly suggest to do this in the front end application (or) make use of CLR based function to do this job. But for simple well formed html string may be we can make use of the new XML datatype introduced in SQL Server 2005 as shown in the below example. CREATE FUNCTION dbo.Strip_WellFormed_HTML (  @inputString VARCHAR(MAX)  )  RETURNS VARCHAR(MAX)  AS BEGIN --Variable Declaration

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-45374701117177991582011-10-27T08:54:00.002+05:302011-10-27T09:15:58.341+05:30

GO is not a SQL Statement or SQL Command. It is just a Batch Separator used by SQL Client tools like SQL Server Management Studio, SQL CMD etc., Extract from MSDN "GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. SQL Server utilities interpret GO as a signal that they should send the current batch of

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-63706092654222723732011-10-23T19:34:00.000+05:302012-01-08T08:15:22.847+05:30

If you haven't heard about $IDENTITY in SQL Server check out the initial part of the scripts explained below to know what it does. CREATE TABLE IdentityTest (   Sno INT IDENTITY(1,2) PRIMARY KEY, CreatedDate DATETIME ) GO INSERT INTO IdentityTest(CreatedDate)  SELECT Getdate() Go 10 1. How to display the LAST inserted IDENTITY Value into a table in the current scope and session? SELECT

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-56588193176409310532011-10-17T21:16:00.000+05:302011-10-18T21:05:04.990+05:30

Though "NOT EXISTS" and "NOT IN" sounds similar there is quite a lot of difference between them.  To start with check out the blog post by Mladen. In continuation to what Mladen has already written I thought I would show the differences it makes on the Execution Plan and the IO / Time when we use NOT EXISTS or NOT IN in our queries. Let's see few of the differences between them. Case 1: Lets

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-3362615556138127632011-10-13T22:19:00.000+05:302011-10-13T22:49:53.637+05:30

Converting UTC format date to Standard time of CST or EST is straight forward. DECLARE @UTC_Date DATETIME SET @UTC_Date = GETUTCDATE() SELECT @UTC_Date AS [UTC], DATEADD(hh, -6, @UTC_Date) AS [CST - Standard Time], DATEADD(hh, -5, @UTC_Date) AS [EST - Standard Time] But if the given date falls under daylight saving then the above calculation won't work. So how is Daylight saving calculated

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-72234794716749895692011-10-11T08:16:00.000+05:302011-10-11T09:12:26.092+05:30

Yesterday I saw a question in a forum asking for help in writing a script to list out all Triggers within a database. They also wanted to see the source code of each trigger. Method 1: Which works in SQL Server 2000, SQL Server 2005 and SQL Server 2008. But the problem with SYSCOMMENTS is it would truncate any text beyond 4000 characters. As the "Text" column which preserves the source code is

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-24185364680441152222011-10-08T21:19:00.000+05:302011-10-09T11:12:53.376+05:30

The best product I have seen so far for generating random test records within a SQL Server database is Red Gate's Data Generator software. Its feature list is really impressive. Please be advised it is not a free software but it has a 14 days trial version to try it out. If you are looking for a simple script to generate records for a standalone table may be try out the below scripts. To

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-19046069093885260512011-10-07T21:20:00.000+05:302011-10-08T08:02:15.494+05:30

Few years back I wrote a solution for the same problem here. Recently I read an article by Henning on the similar topic which was interesting and informative. After that, I was wondering why not just make use of a NUMBER table and a NOT IN to do this. I am providing the test script as-is and I haven't compared the performance yet between any other solutions out there :) --Lets set up a sample

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-58725633625119048652011-10-06T07:40:00.000+05:302011-10-06T08:35:49.136+05:30

1. For those who are adamant to do it via scripts: :) Few years back I have written a t-sql script to do this. I have disabled the constraints before truncating the data and once it is done have enabled it back. But I guess in the latest versions of SQL Server we might need to drop & recreate the constraints (haven't tried it yet). 2. Generate Scripts: If we need to return back to complete

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-33906909009934717092011-10-03T23:40:00.000+05:302011-10-04T08:05:45.355+05:30

Way back in 2003, I wrote a blog post titled "Is sorting possible in views?". I came to know that by using TOP 100 PERCENT in our SELECT query we can make use of ORDER BY clause within a VIEW. I tested that out in a SQL Server 2000 machine. But till date I have never used that in any of the real project I have worked on :) Recently I was trying out in a SQL Server 2005 machine using

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2Chennai, Tamil Nadu, India13.060422 80.24958312.936679000000002 80.0916545 13.184165 80.4075115tag:blogger.com,1999:blog-5849265.post-84561677489882164862011-10-03T11:59:00.000+05:302011-10-02T12:34:41.077+05:30

There are many ways by which we can SPLIT delimited string in SQL Server. Few years back I have already done couple of posts regarding this. Let me link those here for quick reference: 1. SPLIT function in SQL Server - Method 1 2. SPLIT function in SQL Server - Method 2 In SQL Server 2005 there are interesting XML enhancements done. So for past few years I have been using this XML trick to

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-28721081447722010322011-10-01T19:29:00.000+05:302011-10-01T19:52:40.126+05:30

You have to provide as much information as possible in your question for somebody to provide you a valid answer! If proper information is not provided then even the simplest of question would suddenly start looking very hard for the readers. Assume of a question like this in one of our India based forums. "What is the shortest route to reach Tidel park" So is this an easier question or a

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-22125334217097587132011-10-01T09:49:00.000+05:302011-10-01T19:46:13.243+05:30

Quite a lot of time I have come across people saying "Clustered Index Physically sorts the data inside the table based on the Clustered Index Keys". It's not completely true! The short answer to them is there is something called "Row offset Array" in a PAGE which manages the order of rows in a PAGE. For a long answer with a sample script read on... There can lot of rows inserted one after the

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-43131014072591517812011-09-29T08:21:00.000+05:302011-09-29T08:21:29.787+05:30

1. If you haven't yet read the Ultimate Guide to Datetime Datatypes I would strongly suggest you to do it. 2. Understand that within SQL Server we can store DATETIME values between 01/01/1753 and 12/31/9999 only. If we try to enter a value which isn't this range then it would throw an error. 3. The way you handle dates will depend on the DATEFORMAT set for your login. Internally DATEFORMAT

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-86591228352425861172011-09-28T07:41:00.000+05:302011-09-29T08:22:05.533+05:30

If we want to get the difference between two dates in SQL Server then we need to make use of DATEDIFF function. Showed few variations using DATEDIFF in my forum response here. So thought of logging in my blog as well for future reference :) DECLARE @StartDate DATETIME SET @StartDate = '2011-01-01 10:15:00.000' Declare @EndDate DATETIME SET @EndDate = '2011-01-01 11:35:00.000' --To get only

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-78406652417415971322011-09-26T12:44:00.000+05:302011-09-25T19:06:42.500+05:30

How to script all the SQL Jobs from SQL Server Management studio? Step 1: Go to SQL Server Mgmt Studio. Step 2: Expand SQL Server Agent >> Jobs Step 3: Hit F7 (or) goto menu View >> Summary Step 4: Now ALL jobs would be listed out there. Step 5: Make use of Control key and choose all the JOBs you want to script. (OR) If you want to script everything press Ctrl + A Step 6: Right click >> "

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-66509353701426840462011-09-25T11:30:00.000+05:302011-09-25T11:30:37.896+05:30

How to find 25th percentile or 75th percentile of given set of numbers (records in table) in SQL Server 2005? In this post I will show couple of ways of calculating Percentile. I am not a statistician so i just took the help of YouTube to learn on how Percentile is actually calculated. During that course I found two different formulas for calculating Percentile! So I have provided couple of

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-69819953433158503102011-09-23T08:05:00.000+05:302011-09-23T08:05:38.078+05:30

There was a question yesterday in a forum asking how to find each character count within a word. i.e., We need to count number of occurrences of each character within the given string. For example: If the given string is 'Amala Paul' the result should be like this: Characters, Total Occurance A, 4 I, 2 m, 1 P, 1 u, 1 I gave a CTE Based Solution which would work on SQL Server 2005+ to achieve

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-5496210960688680392011-09-22T09:20:00.000+05:302011-09-22T09:20:25.927+05:30

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug? No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables. For

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-59464442302963534752011-09-21T22:48:00.000+05:302011-09-22T08:50:36.797+05:30

Method 1: If there is no data in the table or column then use this method. CREATE TABLE tblIdentityCheck (    SerialNo INT,    FirstName VARCHAR(10) ) GO ALTER TABLE tblIdentityCheck DROP COLUMN SerialNo GO --By default, IDENTITY will start with 1 and increment by 1 Alter Table tblIdentityCheck Add SerialNo INT IDENTITY GO Method 2: If there is data in the table then create a new table with

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-50878835708325895272011-09-21T09:08:00.000+05:302011-09-21T09:08:05.580+05:30

In continuation to my earlier post about ISNULL and COALESCE I thought would write one of the basic difference between those two. DECLARE @strTest VARCHAR(10) SET @strTest = NULL --it would return only 'Enter some' SELECT ISNULL(@strTest, 'Enter some value') In this case, ISNULL would return only any output which is lesser or equal to 10 characters because the variable used as its first

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-78937394575370168842011-09-20T20:49:00.000+05:302011-09-20T21:05:36.535+05:30

In SQL Server 2008 using MERGE concept we can achieve INSERT and UPDATE in a single statement. Many times we would have faced a scenario like if the key is present in the table then update that record. If the key is not present in the table then insert that as a new record. To do this we normally have to SELECT the record and see whether it EXISTS or not to decide whether to INSERT or UPDATE.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-89884130943509930792011-09-13T20:42:00.000+05:302011-09-13T20:42:54.640+05:30

I was looking up the web all day but couldn't find any C# code for converting Roman letters or numerals to Integer value. The best thing i came across was this code by andyb but the problem was it has been written in PHP which I had no clue :) So I had just taken the logic of AndyB and converted it into C# for anybody who cares for it. Add this Namespace: using System.Collections.Generic;

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-64325816977089162162011-09-13T20:27:00.000+05:302011-09-13T20:27:54.152+05:30

Converting Datatime value in YYYYMMDDHHMMSS format is not that complex as it sounds :) SELECT CONVERT(VARCHAR(10),GETDATE(),112) --Date alone SELECT CONVERT(VARCHAR(10),GETDATE(),108) - Time alone The only problem is in the Time portion we would get ":" separator within HH:MM:SS. So we need to just replace colon with space and concatenate both this result to get an output in the expected

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-79210272492935450912011-09-13T07:39:00.000+05:302011-09-13T07:40:39.810+05:30

I am assuming in interviews this question "How to find the number of times a character or string is repeated within a string" seems to be asked quite often. May be that's why it is one of the popular question in forums :) Normally people respond with a .NET based solution so i thought I would log a SQL Server based solution for this questions. --Variable Declaration DECLARE @strColumn VARCHAR

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-68310580772209674622011-09-12T20:52:00.000+05:302011-09-12T21:00:07.046+05:30

There was a question saying ISNULL won't work for VARCHAR columns. I thought I would provide a very basic sample where ISNULL works with string column as well.--Sample table with a INT and a VARCHAR columnCreate table tbltest( intSno int, strName Varchar(10))GO--Key-in NULL for both columnsInsert into TblTest Values (null, null)GOSample queries:Select isnull(intSno, 0), isnull(strName, 0) from

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-62610253489360567542011-09-12T20:44:00.000+05:302011-09-12T20:49:49.689+05:30

If we want to export the result of an ad-hoc query or Stored procedure from SQL Server Management Studio without writing any scripts then check this out.Option1::One of the easiest option if you are using SQL Server 2005 or above is to make use of the output format specifier in SQL Server Management Studio.Step1: Open SQL Server Management Studio.Step2: Click on New QueryStep3: Goto Menu Query >>

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-71208172745201444062011-09-12T07:55:00.000+05:302011-09-12T08:48:07.157+05:30

I came to know about the site webRupee from Rajesh Kumar Parbat of DNS. The site explains in detail on how to display the Indian Rupee Symbol on a webpage.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-28029668426095617652011-09-11T09:27:00.000+05:302011-09-11T09:34:07.135+05:30

Lets see how to avoid this error:Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permittedWhen would we see this error?When we try to add a column as default value for another column. Create table tblSalesInfo( custId int, SaleDate datetime, IntimateDate datetime default (saledate - 10))GoSaledate is a column

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-53803624081322554272011-09-11T07:17:00.000+05:302011-09-11T07:25:51.179+05:30

Without using Cursors how to call a stored procedure for each record in a table? This was a question asked in a forum yesterday. I come up with an alternate WHILE loop logic which is shown below:--Sample table to hold test input dataCreate table #temp_table(id int)GO--Lets insert some dummy dataInsert into #temp_table values (1)Insert into #temp_table values (2)Insert into #temp_table values (3)

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-28607617371329952032011-09-07T06:56:00.000+05:302011-09-07T07:07:33.934+05:30

Actually have blogged about this few years back here. But recently I ran into an issue with that script. i.e., If there are more than one person getting the same salary then the results weren't coming out properly.Let me make use of the same table structure as shown in that post.-- Table StructureCreate table employee([Name] varchar(20),Sal int)Go-- Populate Sample RecordsInsert into employee

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-82730796538037061432011-09-04T07:32:00.000+05:302011-09-04T07:41:17.193+05:30

Yesterday in a forum I found a question asking for a way to calculate the total of MM:SS in SQL Server. More over that column is being maintained as a "Varchar" type!! Below is the query i suggested as my suggestion. Please note, the below query would work as long as Minutes (MM) is lesser than 60. --Sample table structureCREATE TABLE SumTimeTest( id varchar(4), timeValue varchar(5))GO --Test

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-22635116479198839272011-09-03T06:53:00.000+05:302011-09-03T07:03:20.071+05:30

One of the forum member had posted a question yesterday saying she was trying to use a CASE statement and it was throwing an error as "Error converting data type varchar to float". Let us reproduce the scenario and see the way to fix it. --Sample tableCreate table dbo.ts_vouchers( Lodging_Amount float) --Test recordsInsert into dbo.ts_vouchers values (0)Insert into dbo.ts_vouchers values (10)

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-47435486403245502782011-08-30T20:21:00.000+05:302011-08-30T20:29:39.864+05:30

In SQL Server 2005 and above, we can make use of SYS.TRIGGERS to list all the Triggers within a database. The below query would help us list the trigger name and the table name on which this trigger is written. SELECT [name] AS Trigger_Name, OBJECT_NAME(Parent_ID) FROM SYS.TRIGGERSORDER BY Trigger_Name

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-2738358818989897322011-08-28T11:38:00.000+05:302011-08-28T11:49:50.784+05:30

This stored procedure takes two parameters: 1. Column name to be searched within all tables2. string/value which needs to be searched within all tables. CREATE PROCEDURE usp_SearchAllTables_InExistingDB( @colName varchar(100), @colValue varchar(1000))AsBEGIN -- Copyright © 2011 Vadivel Mohanakrishnan. All rights reserved. -- Purpose: To search a particular column of all tables for a given search

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-25614816991787742462011-08-20T07:44:00.000+05:302011-08-20T07:48:25.644+05:30

DECLARE @Dbname VARCHAR(20)DECLARE @Date DATETIMEDECLARE @test VARCHAR(30) SET @Dbname = 'Test'SET @Date = GETDATE()SET @test = 'c:\test' + @Dbname SELECT @test = @test + CONVERT(VARCHAR(10), @Date, 112) + '.bak'BACKUP DATABASE @Dbname TO DISK = @test

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-26090501049694103812011-08-20T06:57:00.000+05:302011-08-20T07:28:41.136+05:30

We can make use of "SQLCMD" to execute scripts from Command prompt. If you are new to it I would suggest to go over my introductory posts on them here. Example 1: Writing a batch file with the TSQL script to backup a DB. Save the below sample script as a batch file (lets say, DBBackup.Bat). echo offcls set /p DBNAME=Enter database name:set BACKUP=c:\%DBNAME%.bakset SQLSERVERNAME=

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-67489297850391517432011-08-19T07:50:00.000+05:302011-08-20T07:52:57.219+05:30

Declare @intSeconds intSet @intSeconds = 99999999 SELECT Total_Duration = CONVERT( VARCHAR, RIGHT('00000' + RTRIM(@intSeconds/3600), 5) + 'hrs ' + RIGHT('00' + RTRIM((@intSeconds % 3600) / 60), 2) + 'mins ' + RIGHT('00' + RTRIM((@intSeconds % 3600) % 60), 2) + 'secs ')

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-75623715789612645952010-03-02T09:00:00.001+05:302010-03-02T09:05:38.318+05:30

Check out this article Zero-day details underscore criticism of Oracle“They (Oracle) are one of the slowest to get things patched. It is astonishing how backwards they are in terms of fixing security issues.” -- David Litchfield, co-founder and principal security consultant, NGSSoftwareThe Apache module, which contains the flaw, allows Web applications to use the Procedural Language/Structured

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-9850755936505329002010-03-02T08:51:00.000+05:302010-03-02T08:57:22.909+05:30

Interesting read ... I urge you guys to spend some quality time going through this."If you look at the number of published vulnerabilities in software and the number of users and compare Windows versus Mac OS you will discover that Mac OS has far more published vulnerabilities per user than Windows does so I think the data pretty much speaks for itself." -- 3ric Johanson, security researcher

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-45283790367932830642010-03-02T08:46:00.000+05:302010-03-02T08:50:45.867+05:30

Chrome is learning from IE !! Google nothing to be ashamed off ... feel free to learn from the leader :)Google yesterday announced it has added several new security features to Chrome, including two that were first popularized by rival Microsoft in Internet Explorer 8 last year.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-88120342305767384122010-03-02T08:40:00.000+05:302010-03-02T08:46:20.657+05:30

Find below some useful articles on Deployability.Microsoft Deployment Toolkit (MDT) 2010Microsoft Assessment and Planning ToolkitChoosing a Deployment StrategyThe Windows Automated Installation Kit (AIK) for Windows 7Microsoft Application Compatibility Toolkit 5.5Understanding Application CompatibilityFive Steps to Windows 7 Application ReadinessDeploying Windows 7 from A to ZWindows 7 Enterprise

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-68400026528317506112010-01-07T22:13:00.000+05:302010-01-07T22:17:10.198+05:30

Windows 7 relatedDownload the new eBook on Deploying Windows 7 - http://go.microsoft.com/?linkid=969126577 Windows 7 Tips -http://technet.microsoft.com/en-in/magazine/2009.10.77windows(en-us).aspxGroovy Security in Windows 7 - http://technet.microsoft.com/en-in/magazine/2009.10.win7security(en-us).aspxInside Windows 7 User Account Control - http://technet.microsoft.com/en-au/magazine/2009.07.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-11306482888300175592010-01-07T22:09:00.000+05:302010-01-07T22:12:25.395+05:30

1. Firefox flaws account for 44% of all browser bugs -http://www.computerworld.com/s/article/9140582/Firefox_flaws_account_for_44_of_all_browser_bugs 2. Snow Leopard's more secure than Leopard, but it's not as secure as Vista or Windows 7 - http://www.computerworld.com/s/article/9137992/Apple_missed_security_boat_with_Snow_Leopard_says_researcher?taxonomyId=17&pageNumber=2 3. Security Essentials

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-49242006737075223022010-01-06T06:54:00.000+05:302010-01-06T07:01:59.618+05:30

Educate yourself and your organization on how to build more secure applications. The SDL Developer Starter Kit offers content, labs, and training to help you establish a standardized approach to roll out the Microsoft Security Development Lifecycle (SDL) in your organization - http://msdn.microsoft.com/hi-in/security/sdl-starter-kit(en-us).aspx The Trustworthy Computing Security Development

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-39117197967979759862009-11-30T07:27:00.000+05:302009-11-30T09:45:09.779+05:30

TEDxChennai happened in IIT Madras on November 29th 2009. This is the first time I am attending one such event as the cost (Rs. 1028) was affordable for me :)If at all you haven't known, a ticket in TEDIndia was approx Rs. 1 Lakh and 10 thousand. It seems its 1/3rd cheaper than what it used to cost in California (approx 3L phew).Overall it was an mind blowing experience and I loved every moment

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-725404961040142492009-05-15T07:50:00.001+05:302009-05-15T07:50:46.312+05:30

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

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com3tag:blogger.com,1999:blog-5849265.post-68623336963655037072009-05-12T08:44:00.001+05:302009-05-12T08:44:33.605+05:30

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 :)

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-31460323084650885092009-05-12T08:05:00.001+05:302009-05-12T08:05:12.624+05:30

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,

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-68985794214532048742009-05-03T20:57:00.001+05:302009-05-04T05:36:10.411+05:30

digg_url = "http://vadivel.blogspot.com/2009/05/googles-robo.html";digg_title = "Google’s ROBO …";digg_bgcolor = "#EEEECC";digg_skin = "normal";digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;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

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-72511538533943180492009-05-03T19:35:00.001+05:302009-05-03T19:35:43.061+05:30

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

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-35956054278155251572009-05-03T05:18:00.001+05:302009-05-03T06:10:28.414+05:30

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

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-5739940194895589392009-05-03T04:57:00.001+05:302009-05-03T05:07:03.520+05:30

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

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-79508549437527734352009-05-01T08:44:00.000+05:302009-05-04T05:47:04.561+05:30

digg_url = "http://vadivel.blogspot.com/2009/04/query-has-exceeded-maximum-number-of.html";digg_title = "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 ...";digg_bgcolor = "#EEEECC";digg_skin = "normal";digg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined; The

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-30437716761061547962009-05-01T07:43:00.000+05:302009-05-01T07:46:01.888+05:30

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//

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-77777479562530636892009-04-18T10:38:00.001+05:302009-04-18T10:52:48.507+05:30

From Denis blog I came to know recently that we are able to use sp_executesql without declaring the variables! It really sounded strange to me. I thought it to be one another bug :)So thought would dig deep into this and see what best I can conclude.--Dummy table schemaCreate table tblQuestion1(sno int identity,fname varchar(50))Go --Lets add some dummy records into the table Insert into

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-4406948931296148062009-04-11T21:08:00.001+05:302009-04-11T21:11:39.700+05:30

For a longtime I was debating with my father (a communist – CPI) on why can’t there be a provision to vote online? Today I was excited when I saw similar thoughts with Abhishek Kant.  So i decided to record my thoughts/voice as well on this topic. These were the points based on which I was thinking on the lines of ‘Online Voting System’. 1. On the Election day unless we go very early in the

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-17258567864246620292009-04-11T01:59:00.001+05:302009-04-11T02:00:09.625+05:30

From the day I saw Google Finance i was thinking what tool they would be using to display those cool, user friendly charts! After hours of searching on the web I learned that they were using some sort of custom in-house build tool/logic. Fair enough, but that doesn’t solve my problem. I wanted to create some graphs similar to that for few of my projects. On further ‘googling’, just recently I

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-72625734696766782102009-04-10T11:21:00.001+05:302009-04-10T11:24:12.048+05:30

1. Browser – IE 8.0 and/or Google Chrome 2. Anti-Virus – One of the lightweight software is AVG. Check it out @ http://free.grisoft.com/ 3. Offline thesaurus and dictionary - http://wordweb.info/free/ This is a cool app which sits on your task bar. For finding meaning of a word just block the word and press CTRL + Right click on it. 4. Photo Organizer – I love Picasa and am using it for quite

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-39695297986019227422009-04-10T06:13:00.001+05:302009-04-10T06:13:13.701+05:30

This post is just to inform you all that I will start blogging here again very soon. Thanks for all those who were waiting patiently all this while :)

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-92207067920079233942008-09-04T05:46:00.000+05:302008-09-04T09:35:08.298+05:30

Yesterday morning I read about Google Chrome (Beta) and thought of trying it out immediately. I downloaded the installer from here. As usual Google's installer size is pretty less ... its only 475 KB! I guess while installing its actual size would be in few MB's.The installation was pretty fast and no surprises here as that's expected of Google :) Few things which i found interesting in this new

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-8507294979596886562008-08-09T09:16:00.000+05:302008-08-09T09:24:25.306+05:30

Anders Haig talks about 10 ways by which we can save time in building a website (based on his freelancing experience).Points which he has covered are: [he has given a good description of each topic with reference links]1. Dropdown menus2. Image capture3. Batch image resizing4. Web forms5. CSS text boxes6. Vectorizing Images7. Selecting color schemes8. Creating a Patterned Background9. Building a

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-29974027098124156962008-07-31T07:37:00.000+05:302008-07-31T08:32:08.621+05:30

Deefbox is a simple but very important tool from CQsight. This tool would help us (site owners) to understand in real time on how useful our site is to others.In other terms it would help us to understand the search pattern of the users and whether what they came looking for is available in our site or not.Setting up the tool is pretty simple. Just follow up the instructions given here http://

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-45491804979181764082008-07-30T07:22:00.000+05:302008-07-30T08:07:12.167+05:30

By this time you would have heard about 'Cuil' - a search engine developed by ex-top engineers of Google. I tried our few searches in that and as of now I am not pleased with the search results.I came to know from their site that "Cuil is an old Irish word for knowledge"1. Search Result looks like a news site. This is not a complaint :) May be I am used to the search results page template used by

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-2862700384566453112008-05-19T06:59:00.000+05:302008-05-19T07:02:30.599+05:30

For some reason I am not interested in renewing my MVP award this year. So I haven't applied for it yet. That said, hopefully i can be called as a MVP till this month or next month end max :)

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-32495559755573918122007-12-27T23:25:00.000+05:302007-12-27T23:28:23.050+05:30

From classical ASP days if we want to print some string with formatting we used to make use of Response.Write and some function to do the string formatting for us. But in .NET we have Response.Output.Write which is equal to Response.Write + String.Format features.Find below the basic sample explaining this feature! private void Page_Load (object sender, System.EventArgs e){DateTime dtTwoDays =

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-81120614347562922572007-12-24T23:38:00.000+05:302007-12-25T10:12:47.226+05:30

I scored 100% in the 2007 internet quiz @ http://www.justsayhi.com/bb/internetI need to admit I used google for one question as I wasn't clear on the answer. So you can count me as 99% :)What's your score?

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-20402121635281795332007-11-25T22:40:00.000+05:302007-11-25T22:48:00.222+05:30

I see this to be very frequently asked question in dotnetspider.com! Solution to this is to make use of CONVERT function in SQL Server.--Query will fetch the time portion aloneSelect Convert(Varchar, Getdate(), 108) --Query will fetch the date portion alone.Select Convert(Varchar, Getdate(), 101)The last parameter of Convert function is StyleId. Go through books online to know the complete list

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-86792792307003875332007-11-23T22:00:00.000+05:302007-11-23T22:07:30.913+05:30

To my knowledge, there are companies which still works on SQL Server 7.0 and SQL Server 2000. But in the last quarter of 2005, Microsoft released SQL Server 2005. Though I have been using that product personally since that time and officially for more than one year now. I am not too sure whether everybody has migrated to SQL Server 2005!That being a case SQL Server 2008 (code named: Katmai) is

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-38050929228743578102007-11-21T00:00:00.000+05:302007-11-21T22:01:06.016+05:30

Option 1: Right-click on a table and choose 'View Dependencies'.Option 2: For some reason if you want to do it programmatically check out the below code snippetSelectS.[name] as 'Dependent_Tables'Fromsys.objects S inner join sys.sysreferences R on S.object_id = R.rkeyidWhereS.[type] = 'U' ANDR.fkeyid = OBJECT_ID('Person.StateProvince')here, replace Person.StateProvince with your table name.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-5760723931080503172007-11-19T21:58:00.000+05:302007-11-23T22:13:46.979+05:30

For viewing the modified date of Stored Procs and UDF alone:SelectRoutine_name, Routine_Type, Created, Last_alteredFrom Information_schema.routinesWhereRoutine_type in ('PROCEDURE', 'FUNCTION')Order byLast_altered desc, Routine_type, Routine_name For viewing the modified date of Stored Procs, UDF and Views:We can query 'Sys.Objects' table and find out the list of Stored procs, UDFs, Views etc.,

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com3tag:blogger.com,1999:blog-5849265.post-84814913383271886622007-11-18T22:52:00.000+05:302007-11-18T23:07:07.135+05:30

I was discussing with few of my friends on the topic 'Codds rule'. I heard them say that 'SQL Server' and 'Oracle' supports all of the 12 codd's rule but DB2 doesn't support few of them!! I was surprised and then later got confused myself :)Actually to my knowledge there is no RDBMS product (be it, Microsoft SQL Server or Oracle or DB2) which satisfies all of the 12 rules of CODD.Hopefully in few

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-53555074496422590412007-11-18T22:49:00.000+05:302007-11-18T22:51:20.927+05:30

Cursor = Current Set Of Records

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-87863078509281325402007-11-14T22:24:00.000+05:302007-11-14T22:38:33.424+05:30

Using SQLCMD to execute script files easily in different environmentsAssume we have few script files (.sql) which needs to be run in multiple SQL Servers. Hope you would accept that's real pain to connect into different servers from SQL Management Studio and then execute the scripts one after the other.One of the easiest ways of doing it is by making use of the SQLCMD utility of SQL Server 2005.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com3tag:blogger.com,1999:blog-5849265.post-30088717209229966422007-10-28T12:06:00.000+05:302007-10-28T12:09:53.558+05:30

Bill Gates turns 52 today :) Happy Birthday BillG.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-73007049222061438732007-10-23T17:10:00.000+05:302007-10-23T17:22:29.654+05:30

I removed all of my VZ friends blog from my blogroll and have added onto my bloglines account.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com3tag:blogger.com,1999:blog-5849265.post-41791117624286100392007-10-10T18:39:00.000+05:302007-10-16T22:50:52.504+05:30

Theory::r -- parses additional T-SQL statements and sqlcmd commands from the file specified by into the statement cache.In this article we would see the usage of :r as well as handling spaces in SQLCMD.In SQL Mgmt Studio:Step 1: 01VariableInitialization.sql:setvar filepath "C:\Vadivel\SQL Related\Scripts\sqlcmd":r $(filePath)\02TableCreation.sqlStep 2: 02TableCreation.sqlCreate table tblTest(Sno

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-25583703285116525072007-10-06T22:49:00.000+05:302007-10-06T23:04:35.626+05:30

This example demonstrates the way to create a variable and make use of it for multiple purpose.Actually in this example we would see how to create a variable and append strings into it. Lets create two Database with slight difference in the name. For example, DB1 and DB2. Then create a table in each DB and populate few records into it.---Code snippet which needs to be run in Mgmt Studio starts

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-42168528015246167412007-10-01T21:59:00.000+05:302007-10-10T12:13:30.828+05:30

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a non-clustered Index.Create a table with non-clustered index in it:Create Table tblDemoTable_nonclustered ([Sno] int primary key nonclustered,[Remarks] varchar(5000) not null)GoPump-in some data into the newly created table:Set

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com3tag:blogger.com,1999:blog-5849265.post-9600074053347872002007-10-01T21:41:00.000+05:302007-10-01T22:48:51.777+05:30

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a clustered Index.Create a table with clustered index in it:Create Table tblDemoTable ([Sno] int primary key clustered,[Remarks] char(5000) not null)GoPump-in some data into the newly created table:Set nocount onDeclare @intRecNum

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-70012576037539422742007-09-26T15:34:00.000+05:302007-10-06T23:05:31.668+05:30

To list all available SQLCMD Scripting variables, do the following: Step 1: Go to DOS prompt and open up SQLCMDStep 2: type :listvar which would list all SQLCMD scripting variables.In the screenshot you can see that the SQLCMDEditor and SQLCMDINI variable which we overwrote in the previous posts here and here are displayed.Almost all of the other variables are self-explanatory :)Creating our own

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-11561188967549748772007-09-25T12:31:00.000+05:302007-09-25T12:34:58.227+05:30

There are instances where we might need to run some default scripts on a specified server once SQLCMD gets connected. It can be achieved in just three steps as explained below:Step 1: Create a script file which you wanted to fire when SQLCMD gets connected to your SQL Server.For keeping the example simple, I used the following line and saved it as SqlCmdStartUpScripts.sqlprint 'Welcome today''s

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-72588759120971025082007-09-25T10:55:00.000+05:302007-09-25T11:38:54.520+05:30

From my previous posts one can understand that it is possible to write SQL queries directly in command prompt with the help of SQLCMD utility.Now let's assume we have typed a 'big' query and there is a typo there! Instead of going back and forth to edit it in command prompt won't it be easy if we are able to open the query in an editor and make the corrections there?Yes its possible in SQLCMD.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-59633850565857815222007-09-24T22:28:00.000+05:302007-09-25T10:53:09.602+05:30

If you haven't gone through the first two posts about SQLCMD I would strongly recommend to go over it here and here before proceeding further :)1. Executing a script file from SQLCMD...i. Create a script file by typing in the following line and save it as Message.sqlprint 'Welcome today''s date is: ' + (convert(varchar, getdate()))ii. Now goto command prompt and type: SQLCMD -i Message.sqlhere, -

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-23080833660563046942007-09-24T21:54:00.000+05:302007-09-24T22:52:53.280+05:30

First get connected into the DB Server using SQLCMD using either the windows authentication or SQL authentication as explained in the previous post.Then on the prompt you can type in the TSQL queries directly and press enter. In the next line, say Go and press enter to execute the query and see the result. See below an example to display the current datetime.After displaying the result the cursor

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-32633370014324831772007-09-23T18:04:00.000+05:302007-09-24T22:28:26.132+05:30

“SQLCMD” is a command line tool which was shipped by Microsoft along with SQL Server 2005. Previously SQL Server was having ISQL and OSQL as its command line utility. SQLCMD is replacing both of them (i.e., ISQL is not there in SQL Server 2005 RTM version. OSQL would also be eventually removed!!).When “SQLCMD” is run from the MS-DOS command prompt, it uses the OLE DB provider to execute the given

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-36613259513461414522007-09-23T12:05:00.000+05:302007-09-29T23:11:59.711+05:30

Some pointers:1. NewSequentialID() and NewID() both generates the GUID of datatype of uniqueidentifier.2. NewID() generates the GUID in random order3. NewSequentialID() generates the GUID in sequential order.4. NewSequentialID() can be used ONLY in default clause of a table.5. NewSequentialID() is easily predictable6. So if security is a major concern then go for NewID() instead of

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-79255167187381639982007-07-18T23:51:00.000+05:302007-07-19T00:02:10.788+05:30

There are quite a lot of DMV's which have been introduced along with SQL Server 2005. One such is sys.dm_db_missing_index_details. Using this DMV we can identify what all indexes SQL optimizer is expecing in our tables.Query: select * from sys.dm_db_missing_index_detailsFields to note: equality_columns, inequality_columns, included_columns, statementSo by querying this DMV and having a look at

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com4tag:blogger.com,1999:blog-5849265.post-59625494204167554892007-07-11T14:20:00.000+05:302007-07-11T14:26:29.796+05:30

Want to know what happened same time (approx :)) last year? Check out my wedding anniv post :) Time to take a day off and go around the city.Btw, hopefully from this weekend I would be blogging regularly as I might get back my broadband connection today or tomorrow!

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-55697997092773063662007-07-11T14:14:00.000+05:302007-07-11T14:46:41.153+05:30

I am glad to know that I have been renominated as a MVP in SQL Server category this year (2007 - 2008). Thanks everyone for your support and encouragement!

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-38962506402571565542007-07-11T14:10:00.000+05:302007-07-11T14:47:01.735+05:30

When queries / SP's hits those tables where appropriate indexes are not there then the database engine would automatically create indexes to be used by its execution plan.The hitch is those dynamic indexes would not be reused by the engine the next time when it hits the same table / column. i.e., it would create another dynamic index each time when it hits the same table / column.That said,

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com1tag:blogger.com,1999:blog-5849265.post-42709929393487110052007-05-04T10:01:00.000+05:302007-05-04T10:11:45.026+05:30

If you are connected to a remote desktop (Using Remote Desktop Connection) and for some reason you want to use "Ctrl" + "Alt" + "Del" it would open up the local machines dialog box only.In order to initiate the dialog box of the remote machine to which you are connected to make use of "Ctrl" + "Alt" + "End"Hope this helps!

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com4tag:blogger.com,1999:blog-5849265.post-3892656490647001652007-05-01T10:12:00.000+05:302007-05-01T10:17:52.289+05:30

Prior to SQL Server 2005 if we want to find out the list of recently run queries we need to depend on SQL Profiler.Now in SQL Server 2005 the life has become more easier(!). With the help of of an Dynamic Management Views (DMV) and a table valued function we can list the required result.Points to note:1. sys.dm_exec_query_stats -- This DMV returns aggregate performance statistics for cached query

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com2tag:blogger.com,1999:blog-5849265.post-28696988865738886852007-05-01T09:46:00.000+05:302007-05-01T09:58:35.562+05:30

This is one of the other very common question which I get from many of my blog readers / dotnetspider users.Hi Vadivel,I have created a fresh login in my SQL Server 2005 database. Now I want to grant that newly created login permission to execute any / all stored procedure within that database. Can you tell me how to do this in one shot? As of now, I am writting GRANT statement for all individual

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-14063131518407167452007-05-01T09:40:00.000+05:302007-05-01T09:45:04.851+05:30

In this post I have given one of the way to find out the missing numbers within a table. Please note the you need SQL Server 2005 to execute this example and test it yourself.Sample table creationCreate table tblFindGaps(Sno int not null)GoPopulate dummy records in the table:Insert tblFindGaps values (1)Insert tblFindGaps values (10)Insert tblFindGaps values (3)Insert tblFindGaps values (5)Insert

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-44310903471099106722007-05-01T07:39:00.000+05:302007-05-01T09:19:28.157+05:30

http://blogs.msdn.com/jensenh/archive/2006/08/22/711808.aspx explains the Fitt’s Law and the entire science behind the Office design. I came to know about this from Pandu and its a much read type of article.

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0tag:blogger.com,1999:blog-5849265.post-25752591457710697702007-05-01T06:56:00.000+05:302007-05-01T07:46:00.465+05:30

I am working on SQL Server 2005 for quite sometime now. For past couple of weeks I am facing a strange error often but not always!!Refer the screenshot below to know the actual error:Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945) My system configuration:I am using Windows

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com6tag:blogger.com,1999:blog-5849265.post-15042414089675333462007-04-25T06:02:00.000+05:302007-04-25T06:12:46.048+05:30

Today I just want to write a sample explaining the workaround for 'Divide by zero error encountered.' error in SQL Server.Sample Table Structure for demo purposeCreate Table dbo.TestDivideByZero(WebSite varchar(50),NumOfHits int,Income int)GoInsert dummy recordsInsert into dbo.TestDivideByZero values ('a.com', 100, 20)Insert into dbo.TestDivideByZero values ('b.com', 10, 0)Insert into

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com7tag:blogger.com,1999:blog-5849265.post-52974587050948963662007-04-24T06:29:00.000+05:302007-04-24T06:44:54.979+05:30

Today I was trying to join two table each having approximately 5 Lakhs records in one of my SQL Server 2005 database. I was trying to find the count of some field when this arithmetic overflow error was thrown.Arithmetic overflow error converting expression to data type int.I understood that the calculation has exceeded the maximum INT range. For better understand on this error check out this SQL

Vadivelhttp://www.blogger.com/profile/13387133927257826888noreply@blogger.com0