Case Sensitive Comparison in SQL Server

SQL Server performs the string comparison irrespective of the case. For eg. ”VIRENDRA” and ‘virendra’ are equal when we do comparison in SQL Server.

I found an article which gives complete detail about case sensitive string comparison.

Below are the techniques to do case sensitive comparison

• Converting data to binary type before comparison.
• Using the COLLATE clause to dictate the case sensitiveness of the query.
• Using BINARY_CHECKSUM function.
• Changing the collation of the column permanently, so that all comparisons are case sensitive by default.
• Using computed columns.

Check the original article for more details.

Enjoy…

Fetch random set of rows through SQL Query

If you need to fetch random rows from a SQL server table then RAND() function will do the job for you.

For example:

select rand(), productid, productname from product

The problem with this method is that the Rand() function returns the same value for all rows within a given call, See image below.

You can also supply a seed value to the rand function to generate a unique value.

select top 5 rand(productid) as val ,productid, productname from product order by val

No matter, how many times you run this command, you will always get top 5 rows even though unique random is generated every time.

In such cases how to fetch random rows? SQL Server provides one more mechanism to access random rows. NewID(), which returns a GUID (Globally Unique Identifier).

SELECT TOP <n>
FROM <table>
WHERE <criteria>
ORDER BY NEWID()

select top 5 newid() as val, productid, productname from Product order by val

The key is the use of NEWID() function that returns a GUID. Results will differ, since NewID() manufactures a GUID each time it is called. Therefore, if you run the above command two or three times, you will get different result sets.

Enjoy….

Retrieve Table Name from all the Procedures of any database

In Sql Server 2000 or later versions, 

Suppose you have deleted one of the column of any table and now you have to change all your procedures where this column is used.

1. One way is to open every procedure and look for the table and the column name.

2. Second solution is the

Select * from information_Schema.Routines where Routine_Definition like ‘%table1%’

This query will list out all the procedures in which table name specified in where conditoin is used.

INFORMATION_SCHEMA.ROUTINES view is used to retrieve information about stored procedures. This view contains one row for each stored procedure accessible to the current user in the current database. 

The INFORMATION_SCHEMA.ROUTINES view was introduced in SQL Server 2000. This view is based on the sysobjects, syscomments and other system tables.

Enjoy..

Weird thing with inner queries in SQL SERVER 2005

Few days ago, I found a very strange thing with SQL SERVER 2005 and I strongly feel that THIS IS A BUG in SQL SERVER  which actually can create lots of data discrepancy.

To prove my point I have created a sample SQL Script. Just run the script in SQL SERVER.

Details of the Script

Create new database.

CREATE DATABASE DB_TEST

Use this Database.

USE DB_TEST

Create a table named tblCategory

CREATE TABLE tblCategory
(
      CategoryID INT PRIMARY KEY,
      CategoryNAME VARCHAR(50)
)

 Create another table named tblProduct

CREATE TABLE tblProduct
(
      ProductID INT PRIMARY KEY,
      CategoryID INT FOREIGN KEY REFERENCES tblCategory(CategoryID),
      ISCOMPLETED BIT
)

Insert 5 rows in tblCategory

INSERT INTO tblCategory VALUES (1,‘Category1′)
INSERT INTO tblCategory VALUES (2,‘Category2′)
INSERT INTO tblCategory VALUES (3,‘Category3′)
INSERT INTO tblCategory VALUES (4,‘Category4′)
INSERT INTO tblCategory VALUES (5,‘Category5′) 

Insert 10 rows in tblProduct

INSERT INTO tblProduct VALUES (1,1,0)
INSERT INTO tblProduct VALUES (2,1,0)
INSERT INTO tblProduct VALUES (3,2,0)
INSERT INTO tblProduct VALUES (4,2,0)
INSERT INTO tblProduct VALUES (5,3,0)
INSERT INTO tblProduct VALUES (6,3,0)
INSERT INTO tblProduct VALUES (7,4,0)
INSERT INTO tblProduct VALUES (8,4,0)
INSERT INTO tblProduct VALUES (9,4,0)
INSERT INTO tblProduct VALUES (10,5,0)

Select statement to confirm the data is inserted or not.

SELECT * FROM tblCategory
SELECT * FROM tblProduct 

Here is a select query which is not correct. The query tells select ProductID from tblCategory table where CategoryID = 1. But the problem here is tblCategory  is not having a column named ProductID. So this query throws an error and that’s a correct behavior.

SELECT ProductID FROM tblCategory WHERE CategoryID = 1

Here is the magic. I have used the above select query which is not correct as a inner query with a update statement. What to do you think? What will happen? Update query should throw an error. Logically it should.I was thinking the same But just execute this update query and you will be shocked.

UPDATE tblProduct SET ISCOMPLETED = 1 WHERE ProductID IN (SELECT ProductID FROM tblCategory WHERE CategoryID = 1)

Oops, 10 rows affected. Surpised. All the data in ISCOMPLETED field is set to 1 but my inner query (SELECT ProductID FROM tblCategory WHERE CategoryID = 1) is wrong.

This is just a sample query but When I executed such a similiar statement, 3364 rows in my table was updated.

SELECT * FROM tblCategory
SELECT * FROM tblProduct

Initally I thought that this is a bug but actaully it’s not.  The inner query first tries to find the column in the current table(inner query’s table) and if it does not found then it will look for the outer query table. It is the best practice to use the tableName.ColumnName in the inner query. 

UPDATE tblProduct SET ISCOMPLETED = 1 WHERE ProductID IN (SELECT tblCategory.ProductID FROM tblCategory WHERE CategoryID = 1)

Enjoy…..

Waiting for your comments…..

Send mail From SQL server

It is a very common situation when it is required to send mails. There are so many ways to send mail. 

One way is to send mail through Sql server 2005.

There are two methods available
1. DataBase Mail
2. Thorugh OLE

Here is the Stored Procedure to send mail through OLE.

Just copy this prodedure and run it in your database…

–WSA_sp_SendEmail ‘virendra@mailserver.local’,'virendra@mailserver.local’,'Hi’,'Test Mail From CDO’
CREATE PROCEDURE [dbo].[WSA_sp_SendEmail]
@From varchar(100),
@To varchar(1000),
@Subject varchar(250),
@Body varchar(max),
@CC varchar(1000) = null
AS
BEGIN
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
EXEC @hr = sp_OACreate ‘CDO.Message’, @iMsg OUT
Print @From
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“http://schemas.microsoft.com/cdo/configuration/sendusing”).Value’,'2′
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value’,‘10.0.0.100‘
EXEC @hr = sp_OAMethod @iMsg, ‘Configuration.Fields.Update’, null
–Print @To
EXEC @hr = sp_OASetProperty @iMsg, ‘To’, @To
EXEC @hr = sp_OASetProperty @iMsg, ‘From’, @From
if (@Cc <> ”)
EXEC @hr = sp_OASetProperty @iMsg, ‘Cc’, @Cc
EXEC @hr = sp_OASetProperty @iMsg, ‘Subject’, @Subject
EXEC @hr = sp_OASetProperty @iMsg, ‘HtmlBody’, @Body
EXEC @hr = sp_OAMethod @iMsg, ‘Send’, NULL
IF @hr <>0
select @hr
–print @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
Print @hr
IF @hr = 0
BEGIN
SELECT @output = ‘ Source: ‘ + @source
PRINT ‘hi’
PRINT @output
SELECT @output = ‘ Description: ‘ + @description
PRINT @output
END
ELSE
BEGIN
PRINT ‘ sp_OAGetErrorInfo failed.’
RETURN
END
END
EXEC @hr = sp_OADestroy @iMsg
END

*Please change the SMTP server address in the Procedure. Currently it is set to 10.0.0.100, Change this address according to your SMTP server Address.

To send the mail one have to “Enable OLE Automation”.

Go to Sql server source configuration area and enable OLE Automation from the options list.

Sql server Source Configuration area is under Start->Microsoft Sql Server-> Configuaration Tools..

Enjoy….

TOP WITH TIES clause in SELECT queries

The SELECT TOP N query always returns exactly N records, and randomly drops any record that have the same value as the last record in the group.

SELECT TOP 5 price, Booktitle FROM BookTitles ORDER BY price DESC

This query will give 5 records from table BookTitles in descending order by price. Problem over here is Suppose the last book title has a price tag of $19.99. The BookTitles table contains two more books with the same price, but they are ignored by the TOP clause.

To see those recrods add the WITH TIES clause…

SELECT TOP 5 WITH TIES price, Booktitle FROM BookTitles ORDER BY price DESC

**WITH TIES will only work with Order by Clause.

SET NOCOUNT (Transact-SQL)

Whenever we write any procedure and execute it a message appears in message window that shows no of rows affected with the statement written in the procedure and we become very happy to see that our procedure is working. But do you know that this message creates an extra overhead on the network? Yes it does.

By removing this extra overhead from the network, we can actually improve the performance of our database and our application.

How should we do it?
When you create any procedure then first line of your procedure should be

SET NOCOUNT ON;

This one line of code turns off the message that SQL server sends back to front end after every T-SQL statement is executed. This is applied for all SELECT, INSERT, UPDATE and DELETE statements. As when stored procedures are executed there is no need to pass this information back to front end.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.If we still need to get the count of no of rows affected, we can still use @@ROWCOUNT option. Because The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Template used in SQL SERVER 2005

================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: 
– Create date: 
– Description: 
– =============================================
CREATE PROCEDURE 
– Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = ,
<@Param2, sysname, @p2> = 
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;

– Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

================================================

Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Enjoy….

Count number of tables in a SQL Server database

A simple query to find out number of tables in database is

USE YOURDBNAME

SELECT COUNT(*) from information_schema.tables 
WHERE table_type = ‘base table’ 

 

Enjoy…