Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

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: ,

Defensive Database Programming with SQL Server… a must have (e)book

September 21, 2010 Leave a comment

Book By Alex Kuznetsov… and a must read for all T-SQL techies.

The goal of Defensive Programming is to produce resilient code that responds gracefully to the unexpected.
To the SQL Server programmer, this means T-SQL code that behaves consistently and predictably in cases of unexpected usage, doesn’t break under concurrent loads, and survives predictable changes to database schemas and settings.
Inside this book, you will find dozens of practical, defensive programming techniques that will improve the quality of your T-SQL code and increase its resilience and robustness.

Download the e-book from here: http://www.red-gate.com/specials/Ebooks/Defensive_Database_Programming.pdf
Related article: http://www.simple-talk.com/sql/t-sql-programming/book-review-defensive-database-programming-with-sql-server/

Categories: DB Concepts Tags: ,

Stopping a DELETE statement in middle of its execution – MSDN TSQL forum

September 17, 2010 Leave a comment

 
–> Question:

I accidentally executed the DELETE statement for a table.

However, I stopped it before it completed.

What effect will this have on the table in Database, is my data safe?
 

–> My Answer:

In SQL Server by default a single SQL statement is bounded within a Transaction.

So, if you cancelled the DELETE statement execution in between it would have cancelled the overall deletion from the table. So either all of your rows are safe or all gone. SQL Server ensures that a transaction should be completed fully or not at all, one of the ACID properties.

Categories: DB Concepts, SQL Server Questions Tags: ,

Combine multiple ROWS to CSV String… and vice-versa

September 16, 2010 16 comments

There are times when you want to combine values from multiple rows of a single column into a CSV (Comma Separates Value) String.

Like combining multiple orders of each customer into one single row, or multiple Cities against each State as shown below.

It was a bit difficult to do in SQL Server 2000 and requires to create a customized function or a script.

But with SQL Server’s 2005 version, the new “PATH” mode with “FOR XML” provides us a way to get the desired output. Its a bit tricky but will do the needful, that’s why I’ve also added this post for my reference as well.

CREATE TABLE #tempCityState (State VARCHAR(5), City VARCHAR(50))

INSERT INTO #tempCityState
SELECT 'CO', 'Denver' UNION
SELECT 'CO', 'Teluride' UNION
SELECT 'CO', 'Vail' UNION
SELECT 'CO', 'Aspen' UNION
SELECT 'CA', 'Los Angeles' UNION
SELECT 'CA', 'Hanford' UNION
SELECT 'CA', 'Fremont' UNION
SELECT 'WA', 'Seattle' UNION
SELECT 'WA', 'Redmond' UNION
SELECT 'WA', 'Bellvue'

SELECT * FROM #tempCityState

State	City
CA	Hanford
CA	Fremont
CA	Los Angeles
CO	Denver
CO	Aspen
CO	Vail
CO	Teluride
WA	Seattle
WA	Redmond
WA	Bellvue

 

Using the “FOR XML PATH” syntax:

SELECT DISTINCT State, (SELECT SUBSTRING((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),2,200000)) AS Cities
INTO #tempCityStateCSV
FROM #tempCityState t

-- OR --

SELECT DISTINCT State, (SELECT STUFF((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),1,1,'')) AS Cities
FROM #tempCityState t

SELECT * FROM #tempCityStateCSV

State	Cities
CA	Hanford,Fremont,Los Angeles
CO	Denver,Aspen,Vail,Teluride
WA	Seattle,Redmond,Bellvue

 

Now, converting it back to multiple rows:

SELECT A.[State], Split.a.value('.', 'VARCHAR(100)') AS City
FROM (SELECT [State], CAST ('<M>' + REPLACE([Cities], ',', '</M><M>') + '</M>' AS XML) AS String
	FROM  #tempCityStateCSV) AS A
CROSS APPLY String.nodes ('/M') AS Split(a)
ORDER BY 1,2

State	City
CA	Fremont
CA	Hanford
CA	Los Angeles
CO	Aspen
CO	Denver
CO	Teluride
CO	Vail
WA	Bellvue
WA	Redmond
WA	Seattle
Categories: SQL Tips Tags: , , ,