We have all been in a situation where we had to get the last day of the month with T-SQL. The world would just be so much easier with 30 days in every month, which is not the case. To calculate if the last day is 31, 30, 28 or maybe even 29 is not that easy. There are many solutions to this problem out there, here is the one that i normally use:
Three months back in this blog post - T-SQL Enhancements in SQL Server Denali (2011) – we promised to do a series of blog posts, focusing on some of the new T-SQL enhancements that we all are looking forward to in SQL Server Denali.
This is not a post to tell you all that this series is over – this little blog post is just to list all the topics for you, so that you can see what you missed, and provide you with some links to the older posts in this series.
IIF Function
If you are an old Access or Excel “developer” you know this one. This is not a function I have been missing before I read about it today. If you, like me, is so tired of writing CASE statements with only 2 possible outcoms – you will love this new function.
IIF returns one of two values depending on the Boolean expression. Here is the syntax from BOL
A couple a weeks ago I blogged about a few of the enhancements in the OVER clause, and now I will show you guys a couple of new windowing functions, that goes along with the OVER clause. These new functions are FIRST_VALUE() and LAST_VALUE().
I’ll use the same setup as I did in the OVER clause post:
Recently I ran into a bug related to autogrow of the log file. Because this was the second time I encounted this particular bug, I decided to blog about it.
The symptoms of the problem was the one specific database took a very long time to be brought online after a simple service restart. The database was simply staying in the “in recovery” state for a long time. The reason for this was a very high number of Virtual Log Files in the database. Let me set up a demo for you, so you can see what this is all about:
With SQL Server Denali there has been made some improvements to the OVER clause, which I wasn’t even aware of until today. I was playing around with a few of the new analytical functions, and suddenly struggled to get the results I was expecting. After some investigation, I found the solution in books online.
The basic syntax of the OVER clause from books online:
The ROW or RANGE clause is the new stuff I will be talking about today.
Ever since I was a web developer back in the days, server side paging has been an issues. Back then we returned all the rows from the database and did the paging on the client (webserver). Looking back, that is probably not the best and most efficient way to do it – best case is just to return the x rows needed from the database, this would speed up the application, and remove some preasure from the database.
SQL Server Denali comes with paging support, enhancements has been made to the ORDER BY cluase. The new feature comes with to new keywords OFFSET and FETCH NEXT/FIRST. OFFSET sets the number of rows to be skipped before returning rows and – the value can be a constant value, result of a query or an expression. The FETCH keyword can be used with either NEXT or FIRST – they do exactly the same, it is followed by the number of rows to be retrieved.
Are you tired of doing self joins, just to get the value of a column in the previus/subsequent row, or maybe even worse you are doing multiple self joins. This is where you will start smiling, let’s have a look at an exampel.
I’ll start by creating some demo data:
- CREATE TABLE MonthlyProfit
- (
- yearid int,
- Monthid int,
- Profit bigint
- )
- INSERT INTO MonthlyProfit (yearid, Monthid, Profit)
- VALUES
- (2010,1,1766), (2010,2,100), (2010,3,1500), (2010,4,15000), (2010,5,900), (2010,6,45),
- (2010,7,1766), (2010,8,9100), (2010,9,-100), (2010,10,50), (2010,11,900000), (2010,12,6575)
Continue Reading
