Percent rank is defined as the number of values that are the same or less than the current value divided by one less than the number of values. Percent rank is different than PERCENTILE, stay tuned for PERCENTILE_DISC and PERCENTILE_CONT which are different from PERCENT_RANK. For this example I will be using the same revenue table in the sample database …
SQL Server 2012 introduces 8 new analytic functions. This post will cover 2 of them LEAD and LAG, which can be used to reference a value in the row before or the row after the current row. If you have read my OVER clause articles you will recognize the REVENUE table. Just a table listing department revenue year over year …
In a previous article I covered the usage of ROWS PRECEDING and FOLLOWING in the over clause. For this example I am going to use the same database and tables that I created in the previous example to show ROWS UNBOUNDED both PRECEEDING and FOLLOWING. One of the new features available in TSQL in SQL Server 2012 is the ROWS UNBOUNDED …
Transact SQL OVER Clause – ROWS UNBOUNDED PRECEDING or FOLLOWING Read more »
With SQL Server versions 2008R2 and 2012, you can access the registry to get the settings for the current instance of SQL Server. Here is how it works using the dynamic management view (DMV) called dm_server_registry. From there you get get at all sorts of information about the current SQL Server instance. Give dm_server_registry a try.
First for this example, and a few to follow we need to create the database that we are going to play around in. Next a review of the OVER clause as it is support prior to SQL Server 2012. Which produces the following output. Which gives you the average revenue and sum or the revenue for each department. In the …
One of the new Dynamic Management Views introduced in SQL Server 2012 is the dm_server_services which returns info on the services running that are associated with SQL Server. select * from sys.dm_server_services; Give it a try. Looking this over, the first thing that comes to mind is to to use this to be able to detect if the SQL Server Agent Process is …
New DMV in SQL Server 2012 sys.dm_server_services Read more »
There is no more Business Intelligence Development Studio (BIDS) in SQL Server 2012, instead it has been replaced with the SQL Server Data Tools that uses the core of Visual Studio 2010, with the Report Builder and Report Designer built in to the new Visual Studio. The UI has changed colors, and looks a bit sharper, but overall you have …
No more Business Intelligence Development Studio in SQL 2012 Read more »
After several attempts to install SQL Server 2012 on Windows 7, I was getting the following error. “SQL Server Setup has encountered the following error: Value cannot be null. Parameter name: doc.” After 4 retries with different options I finally tried running setup.exe “As Administrator” based on suggestions that I found when searching on the error. Still no luck. Next …
SQL Server 2012 – Setup Problems – Value Cannot Be Null Read more »
I added 2 new reports Top Cache Queries and Top CPU Queries into the Server Health Reports. Here is a screen shot of the latest report, the Top CPU Queries report: Enjoy….
I spent the day today at SQL Saturday in Vancouver BC. I met some fabulous SQL Server professionals, and presented 2 sessions. The first Session was on Unleashing Common Table Expressions in SQL Server. This was the second time that I presented this one, the first was at a SQL Saturday, the first time was SQL Saturday 108 in …