Home Archives for TSQL

Tip for Using SQLServer To Count By Alpha Names in a list

October 4, 2011 By 2 Comments
image.png

  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)   … Continue Reading

Filed Under: SQL Server, Sql Server 2008, TSQL

Writing A Transact SQL (TSQL) Procedure For SQL Server 2008 To Delete Rows From Table Safely

April 29, 2010 By Leave a Comment

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. … Continue Reading

Filed Under: SQL, SQL Server, Sql Server 2008, Transaction, TSQL

Using SqlServer 2008 and TSQL Subtract 1 Hour From All Values In a DateTime Column

April 28, 2010 By Leave a Comment

In this post, we’ll go briefly the process of how you would update all rows in a SQL Server 2008 table such that a particular date column will be moved back 1 hour in time.  This is actually pretty simple, but being that I typically do my work in the ORM layer (that is LINQ2SQL or Entity Framework), I just don’t do much of this.  The process I’m going to follow is first to use Microsoft SQL Server Management Studio to make sure I know what I’m doing with the Sql Server 2008 functions, then add it to an UPDATE statement. … Continue Reading

Filed Under: Sql Server 2008, TSQL

LINQPad Help For Creating Nasty Group by Query in Native SQL

September 28, 2009 By 1 Comment

I often speak very highly of LINQ and also LINQPad.  This morning, I was struggling with some sql that would let me do a count by DateTime while stripping out the time portion.  That is, I just want to know how many entries are in the table for each Date (regardless of what time).  I tried lots of solutions I got from search, and they all gave not correct results, usually involving Casting and other non fun sql programming constructs. … Continue Reading

Filed Under: C#, LINQ, LINQ to SQL, TSQL
Follow

Get every new post delivered to your Inbox

Join other followers: