Category Archives: SQL server

Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”

Posted on 5 December 2011 by Andrew Fraser

Indexed views (the SQL Server equivalent of Oracle’s materialized views) are a good performance fix, especially for reporting and data warehouse systems. However they only work with inner joins. Attempts to create an indexed view with outer joins fail with: … Continue reading →

Posted in Performance tuning, SQL server | Leave a comment

Windows Server 2008 R2 Firewall for SQL Server and FTP

Posted on 23 December 2010 by Andrew Fraser

Windows Server 2008 R2 has a strict firewall by default. It will not let you run FTP (client) from the server and get files from other servers/sites It will also not let you run “SQL Server Management Studio” from your … Continue reading →

Posted in Installs, SQL server, Windows | 2 Comments

Howto remove carriage return line feed from SQL Server for displaying in Excel

Posted on 13 March 2009 by Andrew Fraser

Use this SQL to remove carriage return line feed from SQL server for displaying in excel: select REPLACE(column_name, CHAR(13) + CHAR(10), ‘, ‘) from table_name ; Based on posts by Aaron Bertrand and David Seruyang.

Posted in Character sets, SQL server | Leave a comment

Quick SQL Server Training for Oracle DBAs

Posted on 2 May 2007 by Andrew Fraser

Short SQL Server Training course uploaded in MS-Word format.. Also same in pdf format. It’s a bit dated now – is for SQL Server version 7.0 – but still relevant. I’ll update for version 2005 and convert to html format … Continue reading →

Posted in SQL server | 2 Comments

SQL Server 2005

Posted on 26 January 2007 by Andrew Fraser

SQL Server 2005 doesn’t prompt during install for locations of datafiles and logfiles, meaning they will end up on C:, which you won’t normally want. You can alter the locations during install, but it’s not especially intuitive. Or at least … Continue reading →

Posted in SQL server | 1 Comment