Complexity. How it is calculated.

What are the magic pink numbers you can see in Code Outline window?

The answer is very simple - these are Code Complexity numbers. Each number represents complexity of outlined node, something like Cost in query execution plan. The same numbers you can see after Complexity check - but for whole procedure, not for each statement.

So how Complexity is calculated?

Varchar "miracle" and best practice

I've just spent fifteen minutes helping colleague to find "miracle in t-sql code".
- There should be rows, definitely! I can run simple query and voila! 4 thousands of rows selected! So why there are no rows when I'm doing a simple left join with small filter expression?
There is no miracle of course.

ExpressProfiler: The New Kid on the Block

I'm pleased to inform you that yet another of my pet-projects, ExpressProfiler, joined the rapidly growing family of Ecosystem-based tools for SQL Server.
You may ask me, "why should I use ExpressProfiler if there is standard profiler supplied with SQL Server?"

There are a number of reasons:

First - not every edition of SQL Server has a profiler. The Express edition didn’t benefit from a profiler until 2012SP1.
Of course you can buy the inexpensive Developer Edition, but what will you do on environments where only SQLExpress is installed? Will you install The Developer Edition? Of course not. The solution is to download ExpressProfiler, which doesn't need to be installed. Simply copy a single executable and run it! (it also comes with an optional installer.)
Okay. You may say, "I already have the Standard (or even Enterprise) edition of SQL Server. Why do I need ExpresProfiler?"
1. It is small and simple.
2. It is always ready for action (especially when launched from SSMS, which supplies it with your server connection settings)
3. It is fast. I'm not joking. When you are working in a remote session with servers in your datacenter, ExpressProfiler is much faster than the standard profiler due to its far simpler UI.

Psst! Wanna some API?

If you have already visited SQL Code Guard's website you might notice a reference to an "API for custom tool development". Pretty cool sounding, isn’t it? SQL Code Guard can not only find issues as a standalone tool, but also exposes its capabilities to be used in your own solutions!

A few words about simplicity: Using Red-Gate Ecosystem framework

In my first blog post I briefly covered when and why I created SQL Code Guard (SCG).

To be honest, the first version of SCG was a standalone application, but supporting a GUI took too much time so I decided to convert it to an SSMS plugin – in this case I was able to use SSMS’s GUI, its editors, syntax highlighting and so on.

After  some considerable research I discovered that the knowledge on how to write an SSMS addin is a much hidden treasure.

When issue is not exactly "issue"

In last release of SQL Code Guard (2.4.5222) I've added few issues which can cause confusion.

Eg - ST012 "Consider using temporary table instead of table variable"

You can ask me - "What is wrong with table variable? Everyone suggests to use it! Even Microsoft!"

New release 2.4.5222

Today we released new version of SQL Code Guard - 2.4.5222

There are many fixes and improvements, some new rules were added.

Full list of changes you can view at http://sqlcodeguard.com/index-database-changelog.html

But there is a few more important things.

First, we introduced new type of issues - "Script issues". This category will contain issues that can be vital during build, deploy and continuous integration process. Used with msbuild it can be very useful in modern development processes.

Second, we introduced "exclusions". What that means? Sometime you want to relax checking some rules, eg to not check nullability of columns for tables which name fits specific pattern. So now you can add exception for some issues.

Third - we added support for TFS Checkin Policy so you can establish new level of code quality control for t-sql scripts.

And last but not least - SQL Code Guard team now offers support and custom development. You can read more at http://sqlcodeguard.com/index-database-custom.html