Stored Procedure naming conventions

I got a question about stored procedure naming conventions today. I spent a bit of time thinking about it and here is what I advised the client:

• The most important thing is to have a standard that is accepted by the whole team and is followed (possibly enforced). The actual standard that is adapted is not very important. You should remember that there are two main goals that the standard (naming convention) is intended to achieve:
  

  • Avoid potentially costly mistakes (for example dropping a table while you think you are dropping a view)
  • Improve the productivity of the developers by making the code much easier to read (understand) and reducing the time it takes to locate an object.
    
• Given those two simple objectives it follows that the name of the object should tell us:
• What type of object it is;
• What is the base object on which this depends (if applicable)
• What does it do (Insert, Delete, Update, Create etc.)
• Other group identifiers if necessary

As far as specifics the only warning I am aware of (this was valid in SQL Server 2000 and I am not sure it applies to 2005) is that you should not prefix the stored procedures with sp_ - there is an article by Brian Moran on SQL Server Magazine (http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html) that explains why.

xSQL Profiler versus SQL Profiler

What do you need xSQL Profiler for when you have SQL Profiler? Well, here are three important things you can do with xSQL Profiler that you can’t with SQL Profiler:

• you can define a trace once and run it on multiple servers simultaneously;
• you can schedule the traces to start and stop automatically;
• trace data from all servers is automatically collected into one central repository

xSQL Profiler supports SQL Server 2000, SQL Server 2005 and SQL Server 2008 all editions including MSDE and SQL Server Express.

Support for Change Tracking and Data Compression

xSQL SDK 3.0 and xSQL Object 3.0 support the new change tracking and data compression features that SQL Server 2008 introduces.

By default change tracking is NOT compared and neither is synchronized however the user can turn that on easily. It is important to note that Change Data Capture, which is different from Change Tracking, is not supported in this version of xSQL Object and xSQL SDK.

xSQL SDK and xSQL Object recognize, compare and synchronize data compression property for the following database objects:

• Tables
• Primary Keys
• Unique Keys
• Indexes

By default data compression is compared and synchronized.

New data types supported by xSQL Software's database comparison tools

The just released xSQL Object v3, xSQL SDK v3 and xSQL Builder v2 support all new data types that Sql Server 2008 introduces as well as the enhancements on the existing data types. xSQL Object allows you to compare and synchronize SQL Server database schemas, xSQL SDK allows you to integrate database schema comparison and synchronization in your application and xSQL Builder allows you to package SQL Server databases in an an easy to deploy executable.

Following is the list of the new and enhanced SQL Server 2008 data types

• date: a date/time data type that stores only the date portion.
• time: a date/time data type that stores only the time portion.
• datetime2: similar to existing datetime data type, but with a larger fractional seconds and date range.
• datetimeoffset: a date/time data type that has time zone awareness.
• geography: a new data type for geodetic spatial data defined latitude and longitude coordinates.
• geometry: a new data type for planar spatial data defined by coordinate on a plane (used mostly for maps).
• hierarchyid: used to store hierarchical relationship.
• FILESTREAM varbinary(max): a variation of varbinary(max) that stores the BLOB data as file in the file system outside the database.

You can download those products from http://www.xsql.com/download/

Support for SQL Server 2008 – new release

We are very excited to announce a major new release of the following tools which now provide full support for SQL Server 2008:

• xSQL Object V3 (free for SQL Server Express)
• xSQL Data Compare V3 (free for SQL Server Express)
• xSQL SDK V3
• xSQL Builder V2
• RSS Reporter V3 (free for one SQL Server instance)
• xSQL Object Search V2 (free tool)
• xSQL Script Executor V2 (free tool)

What's new - a detailed description of changes and enhancements on this new release can be found at: <>. Following are some highlights:

• Support for all new or enhanced data types such as geography, geometry, hierarchyID etc.
  Support for the table level change tracking feature introduced by SQL Server 2008.
• Data compression support for tables, primary key constraints, unique constraints and indexes.
• Support for the enhanced full-text catalogs and indexes.
• Support for Remote Service bindings for Service Broker.
• Improved object dependency handling.

You can download those tools from: http://www.xsql.com/download/

He is just the data guy – what the …. does he know!

This is a true story. I don’t think that this particular opinion about those “data guys” so eloquently articulated by this particular person represents a majority opinion and as such I should probably simply ignore it but it stung pretty badly so I couldn’t put it to rest without expressing my disappointment in writing.

Here is the story. I was consulting for this marketing company who was working on a project for a big customer. An experienced data guy, let’s call him Frank (not the real name), on the customer’s side had provided the data for the project as well as some important pointers on what he thought we should be focusing on. Well during one of the meetings at this marketing company’s site when the discussion was going on a direction that completely ignored Frank’s suggestions I reminded them of what Frank had said. The person in charge, forgetting for a moment that I am a “data guy” by trade as well, said “Frank is just a data guy, what the …. does he know!”. As the last words were coming out of his mouth he realized the mistake but it was too late.

This marketing company has a small IT department which is “coincidentally” made up of “data guys” and I had seen this person tell those data guys how important they were to the company and how much their hard work was appreciated and blah, blah, blah… I was just glad those guys were not in the room at that moment.

As for me personally, I don’t hold any animosity towards this person with a marketing degree who for whatever reason (other than the fact that this guy makes twice as much as those data guys) thinks that he is smarter and better than those “data guys” who have computer and math degrees. I simply doubled my rate for this client and I am sure that in the next assignment (if it ever comes) I will be twice as smart J

There, now I can forget about this!

New version of xSQL Profiler released

We have just released a new version of xSQL Profiler. In addition of performance and other improvements on the core (including bug fixes of course) it includes custom query templates that allow the user to gain invaluable insight from the trace data that the xSQL Profiler collects. It utilizes SQL statement signatures to identify the queries that may be bogging a particular server down because of CPU consumption, I/O traffic etc.

The performance reports included in this release are:

• Top N queries by number of times executed
• Top N queries by CPU time
• Top N queries by duration
• Top N queries by number of Reads
• Top N queries by Read/Writes
• Top N queries by number of Writes

xSQL Profiler supports SQL Server 2008, SQL Server 2005 and SQL Server 2000 all editions. You can even trace SQL Server Express and MSDE instances.

Completely free with no restrictions or limitations for up to two SQL Server instances. Download your copy today from: http://www.xsql.com/download/sql_server_profiler/