Mind your Database Ps and Qs via @drsql

I heartfelt plea to all those that create or share code/databases/anything else - documentation, structure and comments are vital if you want anyone else to be able to understand what you have done.

Great blog by Louis Davidson.  I would go a bit further - if you have an ego and want everyone to think the best of you and that what you have done is great/brilliant/amazing then structure it well, leave comments in the code, provide clear documentation and then it will be easy and obvious to see just how clever you really are.

SQLFool makes their scripts open source and has loaded them onto Github

Due to a change in focus she has put all her scripts onto Github and made the open source so others can benefit from all her hard work over the years.  The announcement is here and the scripts are here. An incredibly kind and community spirited act.

WEBINAR: Customer Spotlight: How WellCare Accelerated Big Data Delivery to Improve Analytics - 30 June 2015

In this webinar, speakers from WellCare, Attunity and Pivotal will discuss how WellCare uses Attunity Replicate to offload data quickly and easily from its SQL Server and Oracle systems into Pivotal Greenplum Database to support real-time reporting and analytics.

Register here.

WEBCAST:Free Training on SQL Server Indexes, Clusters, Availability Groups, and Database Mirroring

Want to brush up your skills on SQL Server over the next few months? Brent Ozar Unlimited have got free webcasts coming your way from May to August! Don’t miss out, register today and get these free events on your calendar right away.

Suggest Compression Strategies for Tables and Indexes

Greg Low talks though and generously shares in his blog the script he uses to determine which compression strategy to use on tables.  As part of that the cutoff points are configurable but his default values are as shown:

CCI (Clustered Columnstore Index) will be recommended where the partition is scanned more than 95% of the time, updated less than 10% of the time, seeks and lookups are less than 5% of the time, and where there are at least 800,000 rows. It will also only be recommended if it is supported.
PAGE will be recommended where the partition is scanned more than 75% of the time and updated less than 20% of the time.
ROW will be recommended in all other cases. We believe that ROW should be the default in SQL Server across the board, instead of NONE.

Optimizing SQL Server I/O

In I/O terms SQL Server should be running on a platform that allows it to achieve a single figure latency (ms - millisecond) from the underlying storage.

This blog looks at various considerations and settings to help make IO quicker.

Free eBook - SQL Server Source Control Basics

If you want to implement database source control but aren't sure where to start, this free eBook gives a detailed walkthrough of database source control concepts, with code samples and clear examples.

Download here from +SQLServerCentral

Microsoft SQL Server 2012 Internals: Special Storage

This sample chapter from Microsoft SQL Server 2012 Internals looks at how SQL Server stores data that doesn't use the typical FixedVar record format and data that doesn't fit into the usual 8 KB data page.

Using indexed views on ANY core SQL server edition

In this blog they describe how you can create and use indexed views on express, web, standard, BI and enterprise editions. The difference is that for non-enterprise editions, you have to “force” the optimizer with a hint to use the index whereas enterprise editions will automatically consider the index and use it.

I remember having to use hints on Oracle to make sure it was using the right index in the past and it seems that nothing has really changed.

The need for UPDATE locks in SQL SERVER

+Klaus Aschenbrenner explains why on his website.

I agree with him completely.

Calculating and verifying check digits in T-SQL

In this insightful and detailed blog entry on +Simple-Talk by +Dwain Camps he goes through several examples of how to check the check digits on some popular values that use them and how to calculate them in T-SQL.

SQL Server - finding the last successful CHECKDB and Contained Databases

This blog entry on +SQLServerCentral  explains how.

This blog also on +SQLServerCentral  talks about Contained databases, what they are and the advantages/disadvantages.

Both useful things to know :-)

SQL Server Updateable Column Store Indexes

There are two posts of note for Updateable Column Store Indexes which are available in SQL Server.

This blog entry from the Microsoft MVP Award Program Blog takes you though some of the concepts.

This blog from Rusanu Consulting is a handy Q&A on  them.

Between the two of them I think they give a great grounding in them so you can use them better.

T-SQL- how to find who owns a temp table

In this article on the SQL Performance website Aaron Bertrand explains for several versions of SQL SERVER how to work out ownership of a temp table.

T-SQL - user defined functions - 10 questions you were too shy to ask

In this post on Simple Talk written by Robert Sheldon he goes through some simple solutions to those questions (with code examples) on user defined functions.

I particularly like that a non-deterministic function can be used in a function which I had never realised but makes sense when you think about it.