MS-SQL Server 2008 Service Pack 1 – CTP

 

 MS-SQL Server 2008 Service Pack 1 – CTP

Microsoft has introduced SP1 for Microsoft SQL Server 2008 CTP version on 23rd FEB 09. You can download it from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=6f26fc45-f0ca-49cf-a6ee-840c7e8bb8af&displaylang=en

Do read instructions and system requirement documentation from setup documentation before applying SP1.

Reference: Microsoft Site

Create your own system stored procedure – SQL-SERVER 2005

 

 Create your own system stored procedure – SQL-SERVER 2005

Sometime, its great help if we can create our own stored procedure like any other system stored procedure. It will help us to access that stored procedure from any of the database within one sql-server instance though it have been created only once.

If you create any stored procedure in MASTER database with “sp_” prefix and mark it as system stored procedure than you can take an advantage of name resolution algorithm of engine. Don’t create any stored procedure with “sp_” prefix in your own database as it incur burden on engine, because when you try to execute stored procedure with “sp_” prefix, engine will first try to search it in MASTER database.

We will create one stored procedure in MASTER database.

CREATE PROC sp_list_of_Employees

AS

SELECT * FROM Employee

RETURN

I am assuming that you are having one table, name “Employee” in “AdventureWorks” database but not in “MASTER” database, though we have created above stored procedure in MASTER database.

Now, try to run above stored procedure.

USE MASTER
GO
exec sp_list_of_Employees

As soon as you will run this, you will be greeted with an error:

Msg 208, Level 16, State 1, Procedure sp_list_of_Employees, Line 4

Invalid object name ‘Employee’.

If you have “Employee” table in “AdventureWorks” database then try to run procedure in AdventureWorks database.

USE AdventureWorks
GO
exec sp_list_of_Employees

Again you will be greeted with the same error:

Msg 208, Level 16, State 1, Procedure sp_list_of_Employees, Line 4

Invalid object name ‘Employee’.

Though you ran the procedure from AdventureWorks database, but it is still looking at “MASTER” database for “Employee” table, because we have not marked it as system routines.

We can mark stored procedure with below given command:

USE MASTER

 EXECUTE sp_ms_marksystemobject ‘sp_list_of_Employees’

Now, try to run SP in AdventureWorks database and you will get results or you can run this stored procedure in any of the database in your SQL instance, which have Employee table.

USE AdventureWorks
GO
exec sp_list_of_Employees

Hope you have enjoyed reading.

Happy SQLing!!!!

 

Reference: Ritesh Shah

 

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

GO

 

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

WHERE ID NOT IN

(

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

WHERE ID NOT IN

(

SELECT MAX(ID) FROM SelectDuplicate

GROUP BY Fname,Lname

)

Reference: Ritesh Shah