Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

A script to demo all of the above, as a reminder of Windowing functions...

SELECT
  name
 ,type_desc
 ,COUNT(*) OVER(PARTITION BY NULL) AS CountAllRecords
 ,ROW_NUMBER() OVER (ORDER BY name) AS RowNumberByName
 ,RANK()  OVER (ORDER BY type_desc) AS RankbyType
 ,DENSE_RANK()  OVER (ORDER BY type_desc) AS DenseRankbyType
 ,RANK() OVER (ORDER BY LEFT(Name,1)) AS RankByFirstCharacterofName
 ,DENSE_RANK() OVER (ORDER BY LEFT(Name,1)) AS DenseRankByFirstCharacterofName
 ,ROW_NUMBER() OVER (PARTITION BY LEFT(Name,1) ORDER BY LEFT(Name,1)) AS RowNumberPartitionedbyLeft1
FROM sys.objects
ORDER BY name

SQL 2005 Express Edition - No Sql agent!

I've never had a cause to look at the Express edition of SQL 2005 until today.

My suprise finding (and reason for this post) is that there is no SQL Agent!
Google brings back no end of third party solutions however...

http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

SQL 2005 : SP3

SP3 for SQL 2005 is out.

Beware if you've already applied CU10 / CU11 though, as SP3 is based on CU9!
You'll need to run CU1 for SQL 2005 SP3 therefore!

Hyper-V : SQL Support

SQL Server 2005 is not fully supported on a virtual machine in a Windows Server 2008 Hyper-V environment. Microsoft is considering whether to provide support for SQL Server 2005 on Hyper-V virtual machines in future updates of SQL Server 2005 >

http://support.microsoft.com/kb/956893

( SQL Server 2008 is supported )

Decision time it is then, as currently building Hyper-V images...

SQL 2005 CU9 Released

http://support.microsoft.com/kb/953752/

Management Studio Speedup : Shared Memory

If your client and server are the same server (screams silently), for instance in a local development environment then Shared Memory can help.

As of SQL 2005 it is enabled by default, but incase it isn't or you need to check -

1) Set Shared Memory as Enabled for the sql server -

2) Set Shared Memory as Enabled in the client protocols -

Ref : Default SQL Server Network Configuration

Management Studio Speedup : Disable Error & Usage Reporting

I usually disable this when I perform an installation, but ocassionally I take over a server where it is still active.

Navigate to - Start > All Programs > Micrsoft SQL Server 200x >  SQL Server Error and Usage Reporting

Untick the settings as follows  >

Management Studio Speedup : Remove Splash Screen

Remove that pesky splash screen!

Simple this, change the shortcut that launches Management Studio to include the /NOSPLASH switch as shown below.

OVER Clause in SQL 2005

Converting some reports to SQL2005, I initially forgot the OVER clause can be used aith aggregate functions (had previously only used with RANK).
Saves using multiple joins and GROUP BY record sets >

SELECT   [Name] StateName
  ,[City]
  ,COUNT([Name]) OVER (PARTITION BY [City], [Name]) AS CityTotal
  ,COUNT([Name]) OVER (PARTITION BY [Name]) AS StateTotal
  ,COUNT([Name]) OVER (PARTITION BY [City], [Name]) / CONVERT(FLOAT,COUNT([Name]) OVER (PARTITION BY [Name])) * 100.0 AS PercentOfState
FROM Person.Address a
INNER JOIN Person.StateProvince s
 ON a.StateProvinceID = s.StateProvinceID

SQL 2005 : Using ORDER BY in a view.

The ORDER BY clause is not allowed in a view. It is ignored by the query parser.

To get round this prior to SQL 2005, a TOP 100 PERCENT predicate can be specified in the view definition, e.g. -

CREATE VIEW vw_AlphabeticalEmployees AS 
SELECT TOP 100 PERCENT 
            Contact.LastName, 
            Contact.FirstName, 
            Employee.Title 
    FROM Person.Contact Contact 
            INNER JOIN HumanResources.Employee Employee 
            ON Contact.ContactID = Employee.ContactID 
    ORDER BY LastName, FirstName   

This no longer works in SQL 2005. The view returns data, but the ordering is not applied.

To get round this we can specify TOP 2147483647.
2147483647 is the largest integer that can be passed to the statement and should safely cover most OLTP recordsets!

The SQL 2005 version is therefore -

ALTER VIEW vw_AlphabeticalEmployees AS 
SELECT TOP 2147483647 
            Contact.LastName, 
            Contact.FirstName, 
            Employee.Title 
    FROM Person.Contact Contact 
            INNER JOIN HumanResources.Employee Employee 
            ON Contact.ContactID = Employee.ContactID 
    ORDER BY LastName, FirstName   

20/06/2007 - A colleague has just alerted me to the fact that this feature has now been addressed by a hotfix http://support.microsoft.com/kb/926292

OakLeaf Systems: SQL Server 2005 Ordered View and Inline Function Problems

Management Studio Speedup : Managed Code

SQL Server Management Studio Speedup (Prevent SQL taking ages to open)

1. Close SSMS.
2. Go into IE, select Tools | Internet Options | Advanced
3. If “Check publisher’s certificate revocation” under the security node is checked, then uncheck it.
4. Close IE, Reopen SSMS.

Why all this bother? What has Internet Explorer got to do with SQL Management Studio?

Basically, Managment Studio contains 'Managed code'. It attempts to contact crl.microsoft.com on startup to check for a certificate for the code when this setting is allowed.
Bear in mind this setting will affect other apps too.

Indexes : Included Columns

CREATE NONCLUSTERED INDEX IndexName 
ON Schema.TableName (ID
,int1
,int2
,text1
,text2
,text3
,text4
,text5)

Warning! The maximum key length is 900 bytes. The index 'IndexName' has maximum length of 938 bytes. For some combination of large values, the insert/update operation will fail.


The way round this in sql 2005+ - INCLUDED COLUMNS

Add the column as an included column >

CREATE NONCLUSTERED INDEX IndexName ON Schema.TableName 
(ID) INCLUDE (int1
,int2
,text1
,text2
,text3
,text4
,text5)

SQL 2005+ - TRY CATCH Error detection

Prior to SQL 2005, errors in TSQL code had to be tested for and captured by @@ERROR.
SQL 2005 implements the TRY CATCH syntax in the same way as javascript, c++ and subsequently the .NET languages.

For example, using AdventureWorks, I purposely attempt to delete a record I shouldnt, and receive an error -

DELETE Person.Address WHERE AddressID = 1

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeAddress_Address_AddressID". The conflict occurred in database "AdventureWorks", table "HumanResources.EmployeeAddress", column 'AddressID'.
The statement has been terminated.

@@ERROR only contains the error in the very next statement after the error, hence to both examine & display the value I have to assign it to a local variable -

TSQL Programming to catch the error -

DECLARE @ErrorResult INTEGER
DELETE Person.Address WHERE AddressID = 1
SET @ErrorResult = @@ERROR
IF @ErrorResult <>0
 BEGIN
  PRINT 'Error ' + CAST(@ErrorResult AS VARCHAR(10)) + ' : Could not Delete record'
 END

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeAddress_Address_AddressID". The conflict occurred in database "AdventureWorks", table "HumanResources.EmployeeAddress", column 'AddressID'.
The statement has been terminated.
Error 547 : Could not Delete record

Note : both the SQL Error and my message are returned.

Using TRY/CATCH however, the error can be caught and the script continues -

BEGIN TRY
   -- Attempt delete of referenced record.
    DELETE Person.Address WHERE AddressID = 1
END TRY
BEGIN CATCH
    -- Do alternate action.
    PRINT 'Could not Delete record'
END CATCH

Much tidier, only my error message is returned -

Error 547 : Could not Delete record

SQL 2005 - Overview

SQL 2005 Management Studio

'Management Studio' is the primary interface for managing SQL 2005 servers and is backwards compatible with older servers.

It acts as the replacement for the main SQL 2000 tools, namely 'Enterprise Manager' , 'Query Analyser' and 'Analysis Manager'.

Management Studio Overview

http://www.yukonxml.com/chapters/apress/ss2005revealed/mgmtstudio/

SSIS - Sql Server Integration Services

In Sql Server 2005, The DTS (Data Transformation Services) functionality has been replaced with SSIS - Sql Server Integration Services.

SSIS is an installable component and provides powerful ETL (extraction, transformation & loading) capability.

SSIS supports automation in the same was that DTS packages could be executed and monitored from within other languages.

To design 'Integration Services' projects you use the Business Intelligence Studio Interface (also used for Analysis Services & Reporting Services components). If you already have Visual Studio 2005 installed, SQL 2005 projects fall within the 'Business Intelligence Projects' project type.

To execute 'Integration Services' projects you use 'SQL Server Management Studio'.

Integration Services Resources

http://www.sqlis.com/

http://www.databasejournal.com/features/mssql/article.php/3503996

Analysis Services 2005

Analysis Services was present on Sql Server 2000.

Like its predecessor, Analysis Services 2005 allows you to create and manage OLAP (Online Analytical Processing) cubes.

Unlike its predecessor, Analysis Services 2005 makes the task a lot easier by combining old Enterprise Manager, Query Analyser and DTS functionality.

'Analysis Services' projects are designed within the Business Intelligence Studio Interface (also used for Integration Services & Reporting Services components). If you already have Visual Studio 2005 installed, SQL 2005 projects fall within the 'Business Intelligence Projects' project type.

Analysis Services Resources

http://www.databasejournal.com/features/mssql/article.php/1429671

SSRS - Sql Server Reporting Services

Originally an add-on to SQL 2000, Sql Server Reporting Services is included in SQL 2005 for free.

It consists of 2 components, 'Report Designer' and 'Report Builder'

Report Designer features a 'drag and drop' interface and will be familiar to developers who have used Crystal Reports or Access.

Report Builder is an adhoc reporting tool which allows users to specify

A unique feature of SSRS (at this time) is that it allows users to subscribe to reports and features the functionality to have these reports emailed out periodically. Licencing for SSRS is free, which should accelerate its take up in the market place.

Reporting Services Resources

http://www.databasejournal.com/features/mssql/article.php/3573361