TSQL | PeterKellner.netPeterKellner.net

October 4th, 2011Tip for Using SQLServer To Count By Alpha Names in a list

So, this is not a big tip, but worth at least 10 minutes to figure it out on your own. Here is to saving you 10 minutes:

SELECT LEFT (userlastname, 1) as alpha,       count(id)FROM attendeesWHERE id IN (              SELECT attendeesid              FROM AttendeesCodeCampYear              WHERE codecampyearid = 6      )GROUP BY  LEFT (userlastname, 1)OORDER BY LEFT (userlastname, 1)

It’s pretty self explanatory. Our case is we have two tables that we track code camp attendees. One is the master list, and the other is a detail by year. (6 is this year)

(more…)

Sql Server, Sql Server 2008, TSQL 2 Comments »

April 29th, 2010Writing A Transact SQL (TSQL) Procedure For SQL Server 2008 To Delete Rows From Table Safely

In this post, we will show and explain a small TSQL Sql Server 2008 procedure that deletes all rows in a table that are older than some specified date. That is, say the table has 10,000,000 rows in it the accumulated over the past 2 years.

Say you want to delete all but the last 30 days of activity. If you just simply say DELETE FROM table WHERE id>10000, you will cause this to happen in one transaction and likely, you will get an error. That’s the best case. The worst case is your system tries to do this, eventually consumes all the resources in your computer and crashes your server.

(more…)