Tag Archives: sql server 2005

DELETE and TRUNCATE – Difference and Comparison in Microsoft SQL Server:

I love to write an article about differences and comparison as it is very useful to make your concept clear and also helpful in interview as many interviewers would like know the differences and comparison from the candidate. It proves candidate’s knowledge and his concept about subject.

I have written many articles for differences and comparisons. I would like to list few of them for reference here before we move to our core subject of the article.

Difference between SQL Server 2000 and 2005
http://ritesh-a-shah.blogspot.com/2009/03/difference-between-microsoft-sql-server.html

Difference between SQL Server 2000 and 2005
http://ritesh-a-shah.blogspot.com/2009/03/difference-between-microsoft-sql-server_16.html

Difference between SET and SELECT statement
http://ritesh-a-shah.blogspot.com/2009/03/set-and-select-in-sql-server-2005.html

Difference between EXEC and sp_ExecuteSQL
http://ritesh-a-shah.blogspot.com/2009/03/spexecutesql-and-execexecute-in-sql.html

Difference between BULK INSERT and BULK COPY (BCP)
http://ritesh-a-shah.blogspot.com/2009/03/bulk-insert-bulk-copy-or-bcp-difference.html

And now I am going to describe difference between DELETE and TRUNCATE

First of all let me start by these command’s type. DELETE is a DML command whereas TRUNCATE is a DDL command.

When you use DELETE command, SQL Server engine first move all data to the Rollback tablespace and then delete from the table. In this case disk space will not be immediately release. In short DELETE is a logged transaction and it used to write everything in log whereas TRUNCATE is completely by pass the log system and remove data right from the table so it is faster than DELETE.

If you wish to DELETE data from table based on condition than I recommend using DELETE as TRUNCATE will empty your table. You won’t get a single row in your table whereas we can use WHERE condition with DELETE so that you can remove only those records which you don’t really need.

Since TRUCATE is by passing the log system, you will not get any trigger in action set on the table whereas you will get delete trigger in action when you use DELETE command.

TRUNCATE command will reset your IDENTITY key whereas DELETE command won’t do it for you. In this manner TRUNCATE = DROP TABLE + CREATE TABLE or TRUNCATE=DELETE TABLE + COMMIT TRAN.

Above differences are specific to Microsoft SQL Server but it more or less works same in ORACLE, PostgreSQL and mySQL.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SELECT INTO TABLE: full copy of one table to another in SQL Server 2005:

I wrote one article few days back about different type of use of INSERT statement. You get can refer that article to get complete idea about how you can use INSERT statement and how you can copy data from one table to another with where condition and how you can use SELECT …… UNION ALL clause with INSERT statement at

http://ritesh-a-shah.blogspot.com/2009/03/dml-insert-with-multiple-ways-in-sql.html

But now I am going to show you one practical example of how you can copy data from one table to another table very efficiently.

Let us create one table for demo and insert some data in it.

–create one table

USE [AdventureWorks]

GO

CREATE TABLE [dbo].[Orders](

[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[OrderDate] [datetime] NOT NULL DEFAULT (getdate())

)

ON [PRIMARY]

–insert data in above table.

Insert into Orders

SELECT ‘A1000’,GETDATE() UNION ALL

SELECT ‘A1001’,GETDATE()+1

–create table DummyOrders automatically

–and copy all the data to DummyOrders from Orders table

–based on given condition.

SELECT OrderID,OrderDate

INTO DummyOrders

FROM

Orders

WHERE OrderID=‘A1001’

Above query will not only copy data from one table to another table but it will create new table itself. It will save your lot of time when you need duplicate table.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SET and SELECT in SQL Server 2005 (Difference and Comparison):

As per my observation, many developers are used to with SELECT rather than SET. Is it good practice? Let us see some facts about that.

When question comes to assigning the value to single variable, I prefer SET over SELECT as It is more readable and as per ANSI standard.

As you may know, SET and SELECT both are used to assign the value to variable. But SELECT could used to assign multiple variables at a time and SET can assign value to one variable at a time.

–select command is assigning multiple value

DECLARE @A int

DECLARE @B int

SELECT @A=1,@B=2

PRINT @A

PRINT @B

–set command is assigning only one value per set command

DECLARE @A int

DECLARE @B int

SET @A=1

SET @B=2

PRINT @A

PRINT @B

SET can’t be assigned from SELECT query like we can assign variable in SELECT statement.

–SELECT assigning value to variable @A

USE AdventureWorks

GO

DECLARE @A VARCHAR(25)

SELECT @A=NAME FROM HumanResources.Department

PRINT @A

Note: Don’t assign variable in SELECT when you are not sure whether only one row return or else you will get only last value as @A is variable, not an array

As long as performance concern, I don’t see much difference between these two but yes, you will get only benefit that you could assign more than one variable in SELECT statement.

You can draw your own conclusion based on these facts.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Propercase or TitleCase users define function in SQL Server 2005

We may need to convert some text string to proper case (first letter of each word capital). I have one UDF to share with you guys for proper case. Have a look at it and enjoy the power of T-SQL programming.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/*************************************************************************************************

Examples:

To convert from ‘ritesh a shah’ to ‘Ritesh A Shah’:

SELECT dbo.PROPCASE(‘ritesh a shah’)

*************************************************************************************************/

CREATE FUNCTION [dbo].[PROPCASE](@in varchar(5000))

RETURNS varchar(8000)

AS

BEGIN

IF @in IS NULL

BEGIN

RETURN NULL

END

DECLARE @out varchar(8000)

DECLARE @i int, @len int, @found_at int

DECLARE @LCASE_a int, @LCASE_z int, @Delimiter char(3), @UCASE_A int, @UCASE_Z int

SET @i = 1

SET @len = LEN(@in)

SET @out =

SET @LCASE_a = 97

SET @LCASE_z = 122

SET @Delimiter = ‘ ,-‘

SET @UCASE_A = 65

SET @UCASE_Z = 90

WHILE @i <= @len

BEGIN

WHILE CHARINDEX(SUBSTRING(@in,@i,1), @Delimiter) > 0

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

SET @i = @i + 1

END

IF ASCII(SUBSTRING(@in,@i,1)) BETWEEN @LCASE_a AND @LCASE_z

BEGIN

SET @out = @out + UPPER(SUBSTRING(@in,@i,1))

END

ELSE

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

END

SET @i = @i + 1

WHILE CHARINDEX(SUBSTRING(@in,@i,1), @Delimiter) = 0 AND (@i <= @len)

BEGIN

IF ASCII(SUBSTRING(@in,@i,1)) BETWEEN @UCASE_A AND @UCASE_Z

BEGIN

SET @out = @out + LOWER(SUBSTRING(@in,@i,1))

END

ELSE

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

END

SET @i = @i + 1

END

END

RETURN @out

END

Note: I don’t remember whether I have written this script, modified it or got it somewhere from internet as I am using it since long time.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SPLIT users define function in SQL Server 2005:

We may need to split the string with some separator in SQL Server. To cater this need, I have one function which I am using since very long time and felt to share it with you. Have a look at it and enjoy the power of T-SQL programming.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/*************************************************************************************************

Purpose: To convert a given string to proper case

Tested on: SQL Server 2005

Date Created:March-19-2007

Examples:

To return splitted value

select * from dbo.splitData(‘ritesh, a, shah’,’,’)

it will return three row as three word separated by comma will be splitted

*************************************************************************************************/

CREATE FUNCTION [dbo].[SplitData]

(

@RowData nvarchar(2000),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data nvarchar(100)

)

AS

BEGIN

Declare @Cnt int

Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

Set @Cnt = @Cnt + 1

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

Return

END

Note: I don’t remember whether I have written this script, modified it or got it somewhere from internet as I am using it since long time.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

sp_executeSql and EXEC/EXECUTE in SQL- Server 2005 (Difference and comparison):

Yesterday evening one of my good friend and regular reader of my blog came to my home. He has observed that I have used EXEC somewhere in my articles and sp_executeSQL somewhere in my code so we have discussed pro and cons of both. After finishing discussion he insists me to write an article for the same.

Ok then let us move towards our core topic of the article.

As per my observation, people use EXEC statement more then sp_ExecuteSQL statement. In fact, you will get good performance with sp_ExecuteSQL and cherry on the ice-cream is sp_ExecuteSQL is more secure than EXEC especially when we talk about SQL-Injection attack.

In case of direct use of T-SQL commands, SQL-Server engine will re-use query plan and won’t generate new every time. But when you are using dynamic SP to run T-SQL command, SQL Server engine may not use same query plan and may create new again and again.

Let us think that we have one Employee table in one SP named EmpDetails, if you pass empID, it will return complete detail of that employee. In this case, we may use different empID every time, though query is same just parameter is different, SQL-Server may create new execution plan every time you call your SP. So, now this is the time we have to think about performance. As long as EXEC concern, you can’t pass parameter to it but in sp_ExecuteSQL you can pass parameter so this is also one of the benefit. Let us see the use of sp_ExecuteSQL practically.

–create one table

CREATE TABLE EmpDetails

(

ID INT NOT NULL,

EmpName VARCHAR(15) NOT NULL

)

–inserting the records in it.

INSERT INTO EmpDetails

SELECT 1,‘Ritesh’ UNION ALL

SELECT 2,‘Dharmesh’ UNION ALL

SELECT 3,‘Bihag’ UNION ALL

SELECT 4,‘Rajan’

GO

–CLEARING CACHE

DBCC FREEPOCCACHE

GO

–Example with SP_executeSQL

–note that query variable must be of

–ntext, nchar or nvarchar

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SELECT @StrQuery=‘SELECT * FROM EmpDetails WHERE ID=@eID’

EXEC SP_ExecuteSQL @StrQuery,N‘@eID INT’,1

EXEC SP_ExecuteSQL @StrQuery,N‘@eID INT’,3

–Example with EXEC

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SET @StrQuery=‘SELECT * FROM EmpDetails WHERE ID=2’

EXEC (@StrQuery)

go

DECLARE @EmpID INT

DECLARE @StrQuery NVARCHAR(500)

SET @StrQuery=‘SELECT * FROM EmpDetails WHERE ID=4’

EXEC (@StrQuery)

go

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Synonyms (alias) in SQL Server 2005:

Sometime you might get tired by writing very big table name with its schema name. Sometime people have to use four part table name and it become tedious for developer when he uses it many time in his/her code. Synonyms object in SQL Server 2005 is a solution of that. Synonyms is not only alias but it’s an abstraction layer which will protect you from alteration in your base object. Before we start using Synonyms let me tell you, you can define synonyms on only few object described below.

CLR SP
CLR Function
Table
View
SP
Extended SP

There is also one more limitation, you can’t use Synonyms for other task then described below.

DELETE
INSERT
SELECT
UPDATE
EXECUTE

Now let me show you how you can create Synonyms for already exist object and use it in your code.

I guess you have adventureworks database which ships with SQL Server 2005 and also have table “Purchasing.PurchaseOrderHeader” in AdventureWorks database.

–Creating sysnomym

CREATE SYNONYM PO

FOR Purchasing.PurchaseOrderHeader

–accessing data with synonym

SELECT * FROM PO

Once you finish your work with Synonym and want to DROP it, you can follow below given T-SQL code.

–drop synonym

DROP SYNONYM PO

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Good Article of SQL Injection by Michael Coles

Today, I was searching for SQL Injection and found one detail and very good article written by Mr. Michael Coles, very known name in the world of SQL Server and XML. I felt to share it with my blog readers so I am giving the link of the same.

http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

User Defined Rules (like check constraint) in SQL Server 2005:

You may be aware with CHECK Constraint in SQL Server 2005 which is used to check data integrity for columns. User Defined Rule is the same concept of checking data integrity and consistency. The only difference is you have to use CHECK constraint with every column you want to check for and User Defined Rule, you have to create it once and can use it with any column of any table. One time creation and life time usageJ.

You can create User Defined Rule by following T-SQL command:

CREATE RULE AgeRule AS @Age<=100 AND @Age>0

Now, let us create one table with which we will bind the above rule. If you have any table already created with Age field, you can bind this rule with it.

CREATE TABLE EmpDetail

(

EmpName VARCHAR(15),

Age INT

)

After having the table, we should bind the AgeRule with EmpDetail’s Age column with following T-SQL command.

EXEC sp_BindRule ‘AgeRule’, ‘EmpDetail.Age’

Now, try to insert records which break the rules, it won’t accept those record(s).

Try using following INSERT statement and you will greet with error.

INSERT INTO EmpDetail VALUES(‘Ritesh’,101)

Error:

Msg 513, Level 16, State 0, Line 1

A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database ‘AdventureWorks’, table ‘dbo.EmpDetail’, column ‘Age’.

The statement has been terminated.

If you modify your insert statement likes

INSERT INTO EmpDetail VALUES(‘Ritesh’,29)

You will not face any error rather you will get cool message “(1 row(s) affected)

NOTE: User Defined Rule is for backward compatibility in Microsoft SQL Server 2005 and may not be available in future so beware using it. You can use CHECK constraint for the same purpose.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Naming Convention guideline or rules for database objects in SQL Server 2005:

Naming convention should be used for any database objects in order to maintain consistency and accuracy. Naming convention is not defined by Microsoft; you can define your own naming convention and can follow it throughout your database. Herewith, I am giving naming convention guideline which I am following. There could be a debate in some naming convention rules but as I told you this is what I am following.

Database Naming Convention Guide line:

Use only alphabetic value for database name. Don’t use hyphen or underscore in database name. Don’t use space in database name also or else you have to surround your database name every time with square brackets.

Table Naming Convention Guide line:

Use plural name for table name. Some people would like to debate on this topic for using singular name for table and they might have their own reason as well.

Abbreviation of module should be used as prefix like: purInventoryMaster. This will help us managing tables of modules together.

Each word of table name should be capital to make it more readable like InventoryMaster is more readable than inventorymaster or INVENTORYMASTER.

Space or underscore shouldn’t be used in table name and should starts with alphabetic character.

You should also create and use appropriate Schema with table name and reserve word should not be used.

Column Naming Convention Guide line:

Column name represent entity of your table. Column name should represent the entity rather than table name. It should use first letter of each word capital like CustomerName. Space should be avoided in column name so that you don’t need to bother about square brackets uses.

Reserve word should not be used.

Stored Procedure and Function Naming Convention Guide line:

Always give appropriate name to Stored Procedure of Function show that use can understand its usage by just reading the name, they don’t need to view the code inside. You can use verb, noun etc. to make it clear like UpdateCustomerInfo or maybe you can suffix SP’s input parameter like GetOrderIDbyCustomerName etc.

Don’t use sp_ as a prefix for stored procedure as it has special meaning to it. I wrote one article for that, if you are interested then do visit it at http://ritesh-a-shah.blogspot.com/2009/03/create-your-own-system-stored-procedure.html

Indexes Naming Convention Guide line:

You can have clustered or non-clustered index and maybe unique or non-unique index, generally you used to assign index on one or more columns. I used to given index name like NcSalesSalesID out of which “NC” belongs to “NonClustered”, “Sales” is a table name and “SalesID” is a column name.

Above are the basic guidelines for naming convention. List can grow more but these are the basics I am using.

Rererence: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah