Archive

Archive for the ‘SQL Server Questions’ Category

How to retrieve last 12 months records form a table – MSDN TSQL forum

March 24, 2011 Leave a comment

–> Question:

I have a data field in my table that i would to use within my WHERE clause, to get the last 12 months dataset.

So for example, WHERE date between ‘20110323’ and ‘20100323’

Is this possible? I know I have to use something like GETDATE() or something but not quite sure how to incorporate this.
 

–> My Answer:

You can make your WHERE clause like this:

WHERE date_field between getdate() and getdate()-365

-- OR

WHERE date_field between getdate() and dateadd(m,-12,getdate()

 

–> Answer by Uri Dimant:

You can make your WHERE clause like this:

SELECT * 
FROM tbl 
WHERE dt >= DATEADD(month,-12,DATEADD(day,DATEDIFF(day,0,GETDATE()),0)) 
AND dt <=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

 

Ref link.

Categories: SQL Server Questions, SQL Tips Tags:

Is WITH (READPAST) affected by readlocks – MSDN TSQL forum

March 19, 2011 Leave a comment

–> Question:

If a transaction calls a SELECT using the table hint WITH(READPAST)… will it skip rows that are locked by other SELECT statements that are in READ COMMITTED mode and are issuing shared locks?

ie. Does READPAST skip rows that are locked by SELECT statements, not just UPDATE and INSERT statements?
 

–> Answer:

I don’t think READPAST will skip rows locked by SELECT statement, but only in UPDATE & DELETEs.

Check here for an example and demo: Difference between NOLOCK and READPAST table hints

You may also check a similar old post in MSDN forum for more into.
 

Ref link.

Categories: SQL Server Questions Tags: , , ,

Convert unicode Characters to be displayed in ASCII format – MSDN TSQL forum

March 18, 2011 Leave a comment

–> Question:

Is there a function (or any other way) in T SQL, by which I can somehow convert a unicode character (UTF – 8) and then display it in normal ASCII format?

Eg: I want to convert “Ha Nội” to “Hanoi” either programatically or in SQL.
Another Example: Réunion as “Reunion”
 

One other problem is sometimes some characters in unicode come up as “?” when cast using the above method:

Eg: Ḩaḑramawt ?a?ramawt
 

–> Answer:

This is related to database COLLATION settings.

This would not be the best way, but just in case an idea to sail you through….

SELECT 
	'Réunion', 
	CAST('Réunion' AS VARCHAR(100)) COLLATE SQL_Latin1_General_CP1253_CI_AI

… you can also play with COLLATE DATABASE_DEFAULT option.

For more info you can check my blog post on COLLATION, Collation Conflicts and Change a Database Collation.
 

Regarding your other problem, you want to match the foreign language characters to English, right? Which is not possible in most of the cases.

Just like in French, Réunion, contains ‘é’ which is similar to English ‘e’ but with l’accent aigue accent.

But this is not feasible for every character, and other languages too, like Chinese, Hindi. How can you map, and even if you map what symbol will it show? And thus every character cannot be matched against English and you’ll see invalid characters in your result set.
 

Ref link.

Categories: SQL Server Questions Tags: , , ,

difference between COUNT() and COUNT_BIG() – MSDN TSQL forum

September 28, 2010 Leave a comment

 
–> Question:

What is the difference between COUNT() and COUNT_BIG() in SQL Server ?
 

–> Shaahs Answer:

COUNT() and COUNT_BIG() are similar in functionality, but COUNT() always returns INT and COUNT_BIG() returns BIG INT.

MSDN reference
 

–> My Answer:

Also COUNT() can not be used while creating Indexed Views.

And COUNT_BIG() is a mandatory requirement to include in Indexed Views.

Categories: SQL Server Questions Tags: ,

Include other columns while doing aggregates and GROUP BY – MSDN TSQL forum

September 22, 2010 Leave a comment

 
–> Question:

I need maximum mark scored each students and min mark scored each students.

I have done this way…

select ID, max(mark), min(mark) 
from table1 
group by ID

But i need more field as Class…. I don’t want include class field in group by clause
 

–> My Answer:

Try joining the query with the table, like:

select T1.*, T2.ID, T2.max_mark, T2.min_mark
from table1 T1
join (select ID,max(mark) as max_mark,min(mark) as min_mark
	from table1 
	group by ID
) AS T2
on T1.ID=T2.ID

 

–> shaahs Answer:

In this situation you can use the window functions:

select ID, Class, 
	max(mark) over(partition by ID), 
	min(mark) over(Partition by ID) 
from table1

… in this cause you don’t want to include the fields in group by clause.

Categories: SQL Server Questions, SQL Tips Tags: ,