Deal with Indian regional language in SQL Server 2005/2008

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

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

Advertisements

DateTime observation in SQL Server 2008, Rounding or Bug?

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

Convert SQL Server date to GMT + 5.30 in SQL Server 2008/2005

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

Cannot create index on view Msg 1940, Level 16, State 1, Line 1

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

Find table being locked in SQL Server 2008/2005

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

Started contributing in ASP.NET also

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.
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

Became “SAGE” in Experts-Exchange.com

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

I got married on 11th 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 26th March 09 but actively started participating by 1st 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