“WHERE” Condition with LIKE in ACCESS:

“WHERE” Condition with LIKE in ACCESS:

Generally we used to work with SQL-Server or ORACLE database so we are very well aware with its SQL syntaxes but when sometime we have to deal with ACCESS database, we don’t care to learn it and use our SQL knowledge in ACCESS database. This might lead to logical or syntax error sometime. Once, I have been asked by one of my colleague that simple LIKE is not working in ACCESS database. I had a quick look of it and found one syntax problem in his SQL statement.

Unlike SQL-Server ACCESS database won’t accept”‘%” wildcard. Rather it will accept “*” (asterisk) sign. So if you want to select data from EMPLOYEE table whose first name starts with “R” then you should go for:

SELECT * FROM Employee WHERE FirstName like ‘R*’

Rather than SELECT * FROM Employee WHERE FirstName like ‘R%’

Second SQL statement is wrong in ACCESS database. It’s a logical error.

Reference: Ritesh Shah

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:

This question is one of the mostly asked question in an interview but let me tell you that in depth answer of this question could generate one complete book but let us see the difference here in brief.

Almost features of Microsoft SQL-Server 2000 are available in Microsoft SQL-Server 2005 with some improvement and enhancement along with some new feature. Let us see those in brief.

— GUI improvement (SSMS –SQL Server Management Studio)
— T-SQL enhancement
–CLR integration
–Table Partitioning
–Database Mirroring
–DDL trigger support

Along with “Database Mirroring” as a high availability in Microsoft SQL Server 2005 there are old feature of SQL Server 2000 are also available like replication, clustering and log shipping but it was working for only SQL-Server 2000’s enterprise edition but in Microsoft SQL Server 2005, it is available for standard edition also.

Scalability was limited to 2GB and 4CPU in standard edition of Microsoft SQL Server 2000 but in Microsoft SQL Server 2005, it supports 4 CPU and no RAM limitation in standard edition.

As long as encryption concern there was no built in option available in Microsoft SQL Server 2000, you have to have third party tool and expertise to use that tool while in Microsoft SQL Server 2005 it comes up with inbuilt encryption support.

Apart from that, MS SQL Server 2005 comes up with BI reporting tool as known as “YUKON”.

Reference: Microsoft Site

WHERE and HAVING clause in Microsoft SQL-Server

 WHERE and HAVING clause in Microsoft SQL-Server

People may get confused many time about usage of WHERE and HAVING clause. I am just trying to throw little bit light on WHERE and HAVING clause to make its concept clear. In my many interview session, I asked this simple question to lot of candidate and many of them are aware with “WHERE” condition but not or partial aware with “HAVING” so I felt to write this simple article for them.

Note: This article is moved to following link on 6th NOVE 2012. Please click on following link to read full article with TSQL Script.


Use of RANK() and Partition by clause in SQL-Server 2005

Use of RANK()  and Partition by clause in SQL-Server 2005

We will start by creating one table for demonstration and enter some records in it.

–Create one table



BloggerName VARCHAR(10),

Topic VARCHAR(15),

[Year] INT,

Total INT


 This Article is move to here in “Extreme-Advice.com

Sorry for the inconvenience.


TOP clause takes INT variable – SQL-Server 2005

 TOP clause takes INT variable – SQL-Server 2005

This is TIPS about new feature of SQL-Server 2005. In SQL-Server 2005 you can use variable in TOP clause which was not possible in any of the older version of Microsoft SQL Server.

You can supply any integer value beside TOP clause in Microsoft SQL-Server 2005 and in 2008. BTW, you can now use TOP clause in INSERT, UPDATE and DELETE also.

USE AdventureWorks




SET @ReturnRow=5

SELECT TOP (@ReturnRow) * from Purchasing.Vendor

Reference: Ritesh Shah

Startup Stored Procedure -SQL-Server 2005

 Startup Stored Procedure -SQL-Server 2005

All of you may aware with Stored Procedure but you may not aware with startup stored procedures which suppose to run every time SQL-Server’s services restart.

This task may be useful for some administrative task, i.e.: you may know that TempDB will be recreated from scratch whenever server restarts. You may want some user to grant permission of TempDB every time it is being created.

Before you start generating startup stored procedure, have a look at some basic ground regarding that.

You have to make sure that ‘Scan for Startup Proc’ option is set to 1. You can see that option and make it disable and enable with following queries.


–this will show you all advance option

EXEC sp_configure ‘show advanced option’, ‘1’;



–this will enable startup procedure

EXEC sp_configure ‘scan for startup procs’, ‘1’;


Now, you can create stored procedure and it will be set as startup proc, before you create it, you have to follow some strict guideline.

–Procedure should be reside in MASTER database
–Its owner should be DBO
–Mark your stored procedure as startup stored procedure with sp_procoption
–Only sysAdmin can set sp_procoption
–Your procedure shouldn’t have any input parameter or return any output parameter

Now, we will create one stored procedure and will mark it as startup procedure.

–Creating strored procedure

CREATE PROC spCreateDatabaseAtStartup


      EXEC(‘CREATE database StartupDatabase’)



–Marking SP as startup

exec sp_procoption N‘spCreateDatabaseAtStartup’, ‘startup’, ‘on’

In sp_procoption first parameter is our stored procedure name. Second parameter is “OptionName”  but fortunately or unfortunately there is only one option name “startup” J and third parameter is “OptionValue” that should be “on” or “off”.

Reference: Ritesh Shah

Read XLS or MDB file with OpenDataSource – SQL Server 2005

Read XLS or MDB file with OpenDataSource – SQL Server 2005

Recently, I had a task to transfer data from MDB file to SQL-Server 2005 so I googling little bit and found very good way of doing so. I liked the way and felt it cool so I am sharing with you guys. Before you start this article, Please see my previous article to enable “Ad hoc distributed queries” with sp_configure. Once you enable it. Please create one XLS file, named Emp.XLS, with three columns in “sheet1”.


This article is moved at following location. sorry for the incovenience.


Correlated SubQuery in SQL-Server

Correlated Subquery in MS-SQL Server:


I am writing this article by assuming that you are very well aware with sub-query concept as this is little bit ahead than sub-query. Before we start technically, if you will observe the name “CO-Related subquery”, you will get the concept that this is something highly related to each other and yes, you are absolutely right. Correlated sub-query (also known as repeating subquery) is depend on the main outer query, if you will run it without outer query, you will be greeted with error, while in subquery, if you will run sub-query without outer query, it will give you results set.


MICROSOFT’s definition for Correlated sub-query in its books online:


Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.


Well, enough theory, right????? Now let’s move towards some practical stuff.


We will see this with two table related to cricket match. J


Create one table with cricketer’s personal details with below given query.



CREATE TABLE CricketerDetails



      Name VARCHAR(25) NOT NULL,

      Country VARCHAR(10) NOT NULL



Now, we will insert few records in it.


INSERT INTO CricketerDetails (Name,Country) VALUES(‘Sachin Tendulkar’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Steve Waugh’,‘Australia’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Saurav Ganguly’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Jaisurya’,‘Sri Lanka’)


Once you are done with this, please, create one table for score board as follow.


CREATE TABLE PersonalScore


      MatchName VARCHAR(15) NOT NULL,

      Run INT NOT NULL,

      CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)




Now, it is time to insert few records in score board table.


INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-1998’,190,1)


Now, If you want to see the tournament (match name) with cricketers name, who made run below than average run with correlated subquery.


SELECT C.Name,p.MatchName,p.Run 

FROM PersonalScore p join CricketerDetails  c

ON p.cricketerid=c.id


run<(SELECT AVG(Run) from PersonalScore ps where p.matchname=PS.matchname)


First subquery will calculate the Average of runs and than it will execute every time outer query get new “MatchName”. Every single “MatchName” from outer query will be passed to subquery and it return the results based on the situation.


Reference: Ritesh Shah

Select Duplicate Records in MS SQL-Server

Sometime we require finding duplicate records in our table.  There are several ways to it and I will keep posting separate article for all ways to do the same task. Here is one very short and sweet solution by simple SQL query. Have a look at it.

Well, I am going to create one simple table in AdventureWorks database.

use adventureworks



Create Table SelectDuplicate


ID int identity(1,1) not null,

Fname varchar(10) not null,

Lname varchar(10) not null,

City varchar(10) not null


Now, this is a time to enter some records in just created table above.

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘Ahmedabad’)

INSERT INTO SelectDuplicate VALUES(‘Avi’,‘Sagiv’,‘Edison’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Parsipenny’)

INSERT INTO SelectDuplicate VALUES(‘Ritesh’,‘Shah’,‘WestField’)

INSERT INTO SelectDuplicate VALUES(‘Dharmesh’,‘Kalaria’,‘Ahmedabad’)

So, here is the heart of article below.

SELECT * FROM SelectDuplicate



SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname


As I mentioned in first paragraph of article as well, there are few different ways to do the same thing which will be explained in coming articles.

Of course, you can delete these duplicate records as well, if you wish. All you need to do is change first “SELECT *” to “DELETE” as below given code.

DELETE FROM SelectDuplicate



SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname


Reference: Ritesh Shah