Yesterday, I have received one email for help about querying and filtering records contain Telugu Indian regional language. Have a look:

The problem was when person filtering records of telugu language, was not able to get any single records. You have to deal with few things while dealing with regional languages.

1.)    If whole database should be in regional language, set proper collation for that.

2.)    Set proper collation for column

3.)    While querying or inserting data, MUST USE “N” as a prefix as regional data should be in Unicode and “N” prefix is must to differentiate whether data is Unicode.

When I asked that person, what collation are you using for database and column? Below is the answer:

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

Hi Ritish,

                Yes.I have set Indic_General_90_BIN as Proper collation to Telugu Column.

                 But I am not able to get the records.

                 Is there any way to get the records please ? 

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

What is the reason if proper collation is set even though data doesn’t get filtered by following query?

SELECT * FROM TeluguDictionary where (Telugu like '%అక్క%')

Or doesn’t insert proper data with following INSERT statement?

INSERT INTO TeluguDictionary VALUES ('akkadi','అక్కడి')

“N” prefix was missing in both query. If s/he would have used same query with “N” prefix like below, it would have worked.

SELECT * FROM TeluguDictionary where (Telugu like N'%అక్క%')

INSERT INTO TeluguDictionary VALUES ('akkadi',N'అక్కడి')

Reference: Ritesh Shah

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Recently I come across with one very interesting observation. When you assign any date in datetime variable with milliseconds and print it, you will get difference of few milliseconds, sometime one or two or three. I don’t know whether SQL Server is rounding the datetime or it is a bug or SQL Server is not so much accurate up to milliseconds. Have a look at below examples:

declare @datetest datetime
set @datetest = '2009-01-01 23:59:59.998'
select @datetest --answer is 2009-01-01 23:59:59.997
GO

declare @datetest datetime
set @datetest = '2009-01-01 23:59:59.992'
select @datetest --answer is 2009-01-01 23:59:59.993
GO

declare @datetest datetime
set @datetest = '2009-01-01 23:59:59.982'
select @datetest --answer is 2009-01-01 23:59:59.983
GO

 --in this case you can see even day is changed because of one milliseconds :)

declare @datetest datetime
set @datetest = '2009-01-01 23:59:59.999'
select @datetest --answer is 2009-01-02 00:00:00.000
GO

I tried to find exact reason for this but wasn’t able to do so.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Today one of my subordinate asked me one question about how can we convert GETDATE() to Indian time as our server is hosted in USA. Question is very simple and bit difficult for novice that is why I thought to put it on blog so everybody else can get benefit if they doesn’t know.

Well rather than GETDATE(), I would prefer UTC datetime. Just find the different between your time zone and UTC timezone and add that time to UTC time and you will get your preferred time of your zone.

First I run following T-SQL in our development server in India to find exact time difference between UTC time and Indian time zone.

SELECT DATEDIFF(minute, GetUTCdate(), GetDate());

It will give you 330 minutes, add this 330 minutes to UTC time and you will get Indian time. If already know hours different between your timezone and UTC time, you directly convert it to minutes, don’t need to run above T-SQL.

Now Add 330 minutes to your UTC time with below T-SQL and you are done.

SELECT DATEadd(minute, 330,GetUTCdate()) as indianTime, GETDATE() as serverTime;

Quite an easy task!!! Isn’t it???

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

If you are using View, you might have come across error message something like this:

Msg 1940, Level 16, State 1, Line 1

Cannot create index on view 'ViewName'. It does not have a unique clustered index.

I have seen this question many times in different SQL Server forums so finally decided to write something about this error. If you have view and you want to create Index on that view, that is fine but there is one prerequisite, you have to have one Unique Clustered Index in that view then and then you will be able to create NonClustered Index on that view. You may face this error in any SQL Server version after SQL Server 2000.

Let us see it by example.

--create emp table

create table emps

(

      ID int,

      name varchar(50),

      dept varchar(50),

      company varchar(50)

)

GO

 

--create view on that table

create view dbo.Vemps

as

select name,dept,company from dbo.emps

 

--check the view whether it is working

select * from vemps

--create index on view

CREATE nonCLUSTERED INDEX id_View_Vemps

ON dbo.vemps(name,dept)

--as soon as you will try to create above index,

--you will be greeted by following error.

--Msg 1939, Level 16, State 1, Line 1

--Cannot create index on view 'Vemps' because the view is not schema bound.

--reason of above error is, View should be with schemabindings.

--let us drop view and recreate it.

drop view dbo.vemps

go

create view dbo.Vemps

WITH SCHEMABINDING

as

select name,dept,company from dbo.emps

GO

--now let us again create index.

CREATE nonCLUSTERED INDEX id_View_Vemps

ON dbo.vemps(name,dept)

go

--this time you will be greeted with following error.

--Msg 1940, Level 16, State 1, Line 2

--Cannot create index on view 'dbo.vemps'. It does not have a unique clustered index.

--if you create unique clustered index first and then you will be able to create

--nonclustered index on view

CREATE unique CLUSTERED INDEX id_View_Vemps

ON dbo.vemps(name,dept)

GO

--now create nonclustered index

CREATE nonCLUSTERED INDEX id_View_Vemps_non_clust

ON dbo.vemps(company)

--select your view

select * from vemps

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

As a DBA one need to keep watch on database for lock. This is really very interesting subject and often useful in many different troubleshooting situation. There are many types of LOCK in database, I will write article on different types of lock very soon, right now my intention is to show which table is being locked at the moment. Sometime you index the table or change the schema of table, table got completely locked at that time. I have one very small yet useful and handy query which can show you which table is locked right now. I have executed the query on AdventureWorks database under SQL Server 2005 environment but the same query is compatible under SQL Server 2005 and SQL Server 2008 both.

use Adventureworks

go

--to list of tables which are being locked at the moment

select

  object_name(resource_associated_entity_id) as 'TableName' ,*

from

  sys.dm_tran_locks

where resource_type = 'OBJECT'

  and resource_database_id = DB_ID()

GO

--find complete table lock with  'SCH-M' request mode

--generally when you rebuld index, table is being locked

--and unaccesible, it has schema mode change lock ('SCH-M') mode

--so, find out those table which is unavailable

select

  object_name(resource_associated_entity_id) as 'TableName' ,*

from

  sys.dm_tran_locks

where resource_type = 'OBJECT'

  and resource_database_id = DB_ID()

   and request_mode = 'Sch-M'

GO

--one more beautiful query which can show you which command is causing problem.

--if rebuild index causing complete table lock than you will have DBCC in command column

select

  object_name(sl.resource_associated_entity_id) as 'TableName' ,dr.command,sl.*

from

  sys.dm_tran_locks as sl left join sys.dm_exec_requests dr on sl.request_session_id=dr.session_id

where sl.resource_type = 'OBJECT'

  and sl.resource_database_id = DB_ID()

   and sl.request_mode = 'Sch-M'

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

My regular blog reader knows that I used to help community by my blog www.SQLHub.com and by Experts-Exchange.com. Due to high response from the community and few suggestions from my friends I started participating in www.Asp.Net forum also. Actually my account in Asp.net forum is few year old but I have started contributing since last three days only. You know that I am working with Microsoft technology and how is it possible that I don’t contribute anything in Microsoft’s own website? J

Since my prime area of working is SQL Server, I would love to write blog my website and give solution to the people in Experts-Exchange.com; I will share my knowledge of .NET framework and SQL Server both in Asp.Net for a change now onwards.

So from now, I will be contributing to community from three ways.

Blog: www.SQLHub.com

EE Forum: http://www.experts-exchange.com/M_4847866.html (my profile)

Asp.Net forum: http://forums.asp.net/members/Rits4Friends.aspx (my profile)

Happy Helping!!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Saturday 11^th July 2009 was really memorable day for me. I had an achievement in my personal as well as professional life.

I got married on 11^th July 2008 and last Saturday I have completed one year with my beautiful wife who used to encourage me all the time to help community more and more.

One more achievement was that, I became “SAGE” in MS SQL SERVER zone in Experts-Exchange.com. “SAGE” is really one of the prestigious awards and I am proudly say that I have achieved it in just 3 months and 10 days.

I have joined Experts-Exchange.com on 26^th March 09 but actively started participating by 1^st April 2009 with 0 points. Right now I am about to complete 800,000 points in Experts-Exchange.com. Out of which I have more than 500,000+ points in MS SQL SERVER zone because of which I have been awarded with “SAGE” certificate in MS SQL Server zone. Apart from “SAGE” certificates, I have achieved few more certificates in Experts-Exchange.com. You can have a look at my profile in EE at:

http://www.experts-exchange.com/M_4847866.html

In this wonderful moment I would like to thanks to my wife, parents and few of my friends in technical fields (Pinal Dave and Bihag Thaker) who always encourage me to achieve more and more, help community more and more. Apart from all these person, how can I forget my blog reader? You are really wonderful people and always send me suggestions for improvement. BIG THANK TO YOU ALL.

Hope to have co-operation from all of you and blessings as well in future and I promise that I will do my best to help community.

Thank you very much once again.

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

I have observed quite a few times in different forum, people keep asking question that I have created stored procedure in my database but I can’t find it. Well there are quite a few reason for that, either you have created your stored procedure in different database and you are trying to run in different database or you have not specified schema name for your stored procedure so default schema of your login will be automatically applied to it, while running SP, you don’t specify that schema name and you will face an error like:

“Could not find stored procedure ‘Your SP name’”

Well, to ensure that you stored procedure is created and it is exists in particular database, I used to run following very simple query to confirm.

select ss.name + '.' + sp.name as 'Available Stored Procedure' from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id

Above query will show you all the available stored procedure along with its schema name available in your database.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Well, in few of my past articles, I have saw the method of how to keep watch on your disk drive, how to get information about your data and log file size as those are the very important task of DBA. Today, I am going to show one more important job which DBA simply can’t ignore. DBA needs to keep watch on every data and log file of databases server wide.  If it reaches at certain limits, DBA should be notified by email immediately so that s/he can take any action regarding this. Let me show you one very simple script which can make this task very easy for DBA.

DECLARE @DiskSpace bigint

DECLARE @DataBaseName VARCHAR(50)

DECLARE @Name VARCHAR(50)

DECLARE @DriveLetter CHAR(1)

DECLARE @DiskSize bigint

Declare @fileName varchar(max)

SET   @DiskSpace = 1

DECLARE DriveSpace CURSOR FAST_FORWARD FOR

      select

      databasename=DB_NAME(dbid),

      name, 

      filename,   

      drive=LEFT(filename,1),

      [size]=convert(bigint,[size])*8/1024

      from  sysaltfiles

      order by dbid

open

DriveSpace

fetch

next from DriveSpace into @DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize

WHILE

(@@FETCH_STATUS = 0)

Begin

if

@DiskSize > @DiskSpace

Begin

     

     

      declare @mes varchar(max)

    set @mes='Hi Ritesh, ' + @Name + ' file of ' + @DataBaseName + ' database exceeds 1000 MB limit, current size is ' + cast(@DiskSize as varchar(10)) + ' and file name is ' + @fileName

    --Print @mes

    EXEC

          

          msdb..sp_send_dbmail  @recipients = 'Rits4Friends@gmail.com',

          @subject= 'Drive is about to full',

          @body= @mes

     

End

fetch

next from DriveSpace into @DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize

End

close

DriveSpace

deallocate

DriveSpace

GO

All you need to do is, keep this script in JOBS and run that job once or twice everyday. If you don’t have database email configured than you could check this by remove comment before “Print” statement in script, don’t forget to comment email sending code, if you don’t have email configured in database.

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Well, one of the important tasks for DBA is to keep watch on the free space of hard drive so that no databases face any space related issue. There are so many ways to check it, you can either manually check free space from your explorer, you can use sp_fixedDrives extended stored procedure in SQL Server. What I am going to show you is how you can atomize this process, which keeps an eye on the disk space and can send an email to administrator when it reaches below our warning level.

Let us see one simple stored procedure for this task:

USE

[master]

GO

create PROCEDURE [dbo].[SpDiskSpaceAlert]

AS

IF OBJECT_ID(N'[tempdb]..[#Temptbl]') is not null

      DROP TABLE #Temptbl

CREATE TABLE #Temptbl

(

      Drive CHAR(1) NOT NULL,

      MB INTEGER NOT NULL

)

DECLARE @dsc INT

DECLARE @FreeSpace INT

DECLARE @Drive CHAR(1)

SET @dsc = 20000

INSERT

INTO #Temptbl

EXEC master..xp_fixeddrives

DECLARE DriveSpace CURSOR FAST_FORWARD FOR select Drive, MB from #Temptbl

     

open DriveSpace

      fetch

            next from DriveSpace into @Drive, @FreeSpace

WHILE (@@FETCH_STATUS = 0)

Begin

      if

      @FreeSpace < @dsc

      Begin

            declare @mes varchar(max)

            set @mes='Hi Ritesh

' + @Drive + ': drive reached warning limit, current status is ' + cast(@FreeSpace as varchar) + ' MB only'

            EXEC

                  msdb..xp_send_dbmail @recipients = 'Rits4Friends@gmail.com',

                  @subject= 'Drive is about to full',

                  @body= @mes

      End

     

      fetch next from DriveSpace into @Drive, @FreeSpace

End

close DriveSpace

deallocate DriveSpace

DROP TABLE #Temptbl

GO

--check whether this works or not

exec [Master].[dbo].[SpDiskSpaceAlert]

After creating this stored procedure, you can call it in SQL Server jobs, one or may be twice a day, which sends you an email, if warning level reaches.  Right now I have set it to 20000 MB so if any of the drive in our server reached <20000>

Note: You should have Database mail enable. If you don’t want to send an email, you can either create log or store information in any of the table.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com