SQL Window functions were introduced in SQL Server 2005. At the time, only a small set of functionality was available. Window functions fill a need in the aggregation story for SQL Server. Window functions allow the developer to use row level aggregations without the penalty of using cursors to accomplish the same task. Window functions allow you to segment a set of rows and then apply a function to that set of rows. In many cases, you may choose an aggregation function. However, other functions are also available including ranking and analytic functions. In this four-part series, I will start by breaking apart the OVER clause which is the key to understanding window functions in SQL Server. The following posts will expand on each group of window functions which use the OVER clause – ranking, aggregate, and analytic.
This is an archive of the posts published to LessThanDot from 2008 to 2018, over a decade of useful content. While we're no longer adding new content, we still receive a lot of visitors and wanted to make sure the content didn't disappear forever.
The expression language of Tabular Mode SSAS – DAX – provides several great time intelligence functions. One of those it does not provide though a composite function – like DATESMTD or DATESQTD – is one to calculate Week-To-Date summations. To calculate this type of value, a custom expression using the DATESBETWEEN function is needed. In this post, I'll show you what inputs are needed for the DATESBETWEEN function, how to create a custom start date using DAX, and how to implement this solution in your Tabular Model. Note that this solution will also work with PowerPivot for Excel, but the examples show will be using SQL Server Data Tools.
Recently the question was raised, “If you drop a column on a table, does it also drop the statistics and remove the cached plans that relates to the column?” To answer the question on statistics directly, yes. SQL Server will remove any statistics for the column that is dropped on the table. For the plan cache and any plans that relate back to either the statistics or the column, the answer isn’t quite as easy.
Visual studio 2012 has no more Setup project, but you get the choice to download the installshield limited edition instead. So if you go to Setup and deployment you get this but then without the installshield limited edition option above extensibility. So when you choose that and type in a project name and click ok. You get sent to this page in a browser of your choosing. So yeah, it’s free and it’s supposed to have the same functionality as the setup project you used to have and blah blah.
When you install Visual studio 2012 RTM you get, amongst some other stupid surprises, a new version of the Microsoft reportviewer control. Hooray. There are worst things in life, except that all my clients have reportviewer 2010 installed. But that was quickly fixed, just download the new redistributable. Except the lastest you can find out there is the beta version. And how do I know this is the same version I have on my dev machine??
With the CASE statement you can add conditional logic to your T-SQL code. T-SQL 2012 has certain functions that can be seen as CASE shortcuts. With these functions you can quickly use some CASE functionality and as a surplus, because they are functions you can use them everywhere where expressions are allowed. The examples are written against the AdventureWorks2012 database: ISNULL The ISNULL(‘Null_Expression’,‘Replace_Value’) function evaluates the value of the ‘Null_Expression’ and if the result is NULL it returns the ‘Replace_Value’ value. Otherwise the ‘Null_Expression’ value is returned:
So windows 8 went RTM a few weeks ago and I installed it soon after that. I installed it on a normal laptop. And to be honest the normal win8 user experience on a mouse and keyboard laptop is abismal. It’s close to keyboard only. The start screen takes you away from your desktop work area the charms bar opens in a weird place that I can’t customize nor dock. So for me all those features of win8 are not what I need nor want. And since this is my laptop I can and will customize it no matter what other people think or say. And that meant installing start8 from stardock. Start8 is in beta and now on version 0.9 and still free. They just want your emailaddress to send you some newsletters to which you can unsubscribe.
I recently bought a new laptop – a Lenovo X230. It has an Intel Core i5-3360M, 16 GB RAM, and an OCZ Vertex 3 SSD. It’s powerful and wonderful. And fast. Very fast. In the process of installing my programs, I started thinking about all the software I use to make my job easier. There are a lot of great programs available for DBAs and developers. The best ones, of course, are free. Here’s a list of my five favorite free SQL Server tools!
Bad Code is like the birds This morning, I noticed there were hundreds of European Starlings flying over the houses in my neighborhood. At the same time, I was having a conversation with a coworker regarding a code review. If you are not practicing code reviews, I encourage you to put them in place. Code reviews do not mean that a data architect is reviewing .NET code, per se, but should be reviewing any interactions the main application or services coding would be sending to SQL Server (or any other database engine).
Red Gate is one of my favorite application vendors. They make easy-to-use, useful tools for SQL Server and other software. The support and development teams are top-notch. They support a lot of community events, like SQL Saturdays and Tech on Tap. Red Gate also hosts SQL in the City events– a full day of SQL Server sessions, demos from the developers, networking – even snacks! The best part? It’s free! The tour will take them to New York City, Austin, San Francisco, Boston, and Seattle. Best of all, it will be in Chicago on Friday, October 5. If you’re near one of these cities, take advantage of the training being offered and register today!