The Most Important SQL Startement

People are always searching for the perfect formula or Universal Equation.  This is possible the most important SQL Statement you will ever run.

After completing it your life will be enriched greatly.

Ever wonder what you would do without technology...

JJ Abrams does a decent job showing the results of technology disappearing. Yes its not 100% realistic but if it was NBC would not be able to show it.

However from watching this episode, it gives you an idea of what would happen to that rich guy at Google.

Check out the pilot episode available on NBC.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

You may get the following error message when trying to execute a long query.

Timeout expired. The timeout period elapsed prior to completion

of the operation or the server is not responding.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

You may be tempted to apply a connection string timeout in the config file however someone pointed out that there is a bug and this value is ignored.

The solution is to set the commandtimeout on the context object.

eg. this.context.CommandTimeout = 180;

Check out the article on stack overflow: entity framework timeouts

New Dynamics CRM Blog

For those Guild Members who are looking at Dynamics CRM or are looking at revolutionary and out of the box ways of getting the most out of it, there is a new blog starting up that will be providing a wealth of information on just those topics.

The Dynamics CRM Unleashed Blog is written by someone with hands on experience working with large scale and cloud based CRM.

I know I will be reading it on a regular basis, so I thought I would pass it on to you as well.

If you have any tips, tricks or resources you would like to share with the Guild please email them to Susan Fischer at susan@clinchportal.com or Chris Williams at chrisw_88@hotmail.com

Hashing out hash tables (temp tables)

Temp tables use (or the lack of use) has a great impact on a lot of different aspects of the database.
Temp tables can cause a very negative impact on performance if used incorrectly and even if you use them in an efficient t manner you will still be taking a performance hit.
By using derived tables (Sub queries) you will hit your data in a more efficient way.
That being said sometimes temp tables can be the best solution for your current situation.
Some benefits of tem tables are
• They are sometimes much easier to create then trying to formulate the same result by using a sub query
• Makes production code cleaner; sub queries can get really messy.
• in data analysis tasks or in situations where the code will never hit a production database they are one of our greatest tools

This being said it is imposable to overlook the performance bottle neck that these little guys can inflict on a database. Let’s take a look at what is involved when SQL encounters a tem temp table
1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read activity)
5) DROP TABLE (write activity)
4) Release the locks
This represents a lot of activity under the hood for the database engine and will usually result in poorly performing chunks of code.
This being said I have come across many situations where I have used temp tables in my production code as a last resort. As with everything it depends on the situation.

One example of this is when the situation arises when you need to use a record set generated from one stored procedure in another stored procedure as this is the only way to do this without using a physical table.

A few tips for using temp tables in a efficient manner.
• Only include columns or rows you actually instead on using
• Do not use the select into statement; It places a lock on system objects
• Always delete the temp table yourself... that’s just a waste of resources
• If the temp db is not already on its own disk consider doing this This will help greatly
In closing I would like to say, I personally think that temp tables are a great tool and , if used correctly can be the best situation. As always we must understand the impact of these little guys before we start using them.

Comments, questions
McCannblogs@gmail.com
Or join our LinkedIn group here
http://www.linkedin.com/groups?gid=1856678&trk=hb_side_g

McCann OUT

Failing to commit.... transactions that is: another example of poor understanding of the database

I got called in to a client’s office this morning. They were having an emergency. Their online processing system had come to a complete stop. Customers could not complete their orders. The first thing I did was take looks see at activity monitor. What did I see? About 40 – 50 batches being blocked by a single update statement that was wrapped in a transaction. I have seen more and more of this in the last 2 years. I believe the main problem is most people that do not work with databases as their primary job don’t respect them. What I mean by this is they don’t really understand how many of the features work. Indexing is a great example of this. How many .net developers really understand the concept of indexes? Not enough in my view. Yes while it is true that in most places there is trained Database professionals on staff that will handle all the day to day database tasks. However as a IT guy. I make it my business to know as much about the front end application tools as possible so I can better design the database backend. Those that read my arguments on guids as primary keys found here http://sqlserverexpert.blogspot.com/2009/03/guid-as-primary-key-conclusion.html
Know that one of my reasons was the benefits they had for the overall application design process.
I have many programmer buddies that as soon as there is a performance bottleneck right away it’s the databases fault. And this is true of us as well. How many times all of us have went in to a problem thinking “Those dam programmers buggered it up again”. We need to all work together and some of that responsibility belongs at the company level as well. I happen to work for a great company which believes you should know a little bit about everything so that you understand what your piece has to do, what it has to connect to, what kind of data is it going to receive etc. By doing this My Company is able to build extremely flexible systems for our clients; also we tend to bend the conventional rule book a bit. We do this because it is our belief that the system as a whole is being judged not its individual parts. My guid argument has a lot to do with this fact. Yes having a guid as a primary key has some drawbacks and yes it’s not going to be the most optimized database out there, but the system as a whole will be better because of it. Now this is just one example, all of our systems are different. The point I am trying to make is that we as IT people, not as .net coders’, DBAs, BI developers but all of us have a responsibility to our clients to make the best SYSTEM possible

Thoughts, comments, questions
mccannblogs@gmail.com
McCann Out!

Guid as Primary Key: The conclusion

Guid as Primary Key: The conclusion
Ok Guys. I have passed around this topic to some groups in LinkedIn, some msdn sites, some other sources and have gotten a lot of feedback. First of all I want to thank everyone that contributed to this topic. Whether you agree with me or not, by throwing out your ideas and experiences, you have helped make our industry better . Now to the results, about 85% of you disagree with me in some way or another. And by doing so you have made me think about my position a lot more and I have made some changes to my thinking as well. I still like using Guids for my primary keys and will continue to do so in most cases. A lot of you pointed out some very interesting flaws and sanarios where guids should not be used. Some of you even went as far as to say that guids have no place in our database. Let us take a deeper look at everything.. First of all if you have not read the first post, do so now because you wil get an understanding where I was coming from then, and where I am going now.
The first post is here http://sqlserverexpert.blogspot.com/2009/03/primary-keys-1-of-2.html
If you read the first post you know about the criteria I use for selecting primary keys. A lot of you disagreed with rule 4: Whatever method we choose it should be implemented though out the whole database; Your right. After talking to many people I know on this one, I agree. Primary keys should be selected on a table by table basis. So why don’t people like guids for primary keys. I got 4 or 5 basic answers
• Storage
• Performance
• Complicated
• Goes against data modeling or normalization rules
• It is just wrong

Let me address the “It’s just wrong” comments first.
If you guys are not going to put the effort in to proving me wrong by giving me reasons why it is not a good idea, then I can’t really defend myself. I know its ugly to look at a guid, and it has no meaning to the actual record. That being said identity columns are ugly to and they have no meaning to the record either, so that is not really a valid argument

Storage:
Ok, yes it is true a guid is a 16 byte (128 bit) integer and yes it will take up more space than a int or big int column. I will admit to this fault. However how cheap is storage these days? One of my clients has a database going on 3 TB now and we use guids for primary keys almost everywhere and storage has never been an issue. Now yes in some cases it is, and you need to scope that out, if storage is a issue then guids might not be the best choice.

Performance:
I have to say, a lot of people don’t give SQL Server enough credit in this department.
Yes if you use a guid you are going to run into some issues and yes you are going to have to take extra care in planning your clustered indexes and your covering indexes. The performance bottle neck is not as great as you would think. Let’s look at what most people think the major performance issues are. There are 2 things people have pointed out

• Page splits
• Index fragmentation
Ok so yes, if you leave the fill factor at the default value, you will definitely encounter more page splits and yes your indexes will have a tendency to become fragmented very quickly.
However if you manage this correctly and you rebuild your indexes often enough then it is really not a big issue. And now that we have the online option when building indexes it makes this more manageable.
• It’s complicated
This is my favourite one. Can someone tell me how a guid is more complicated then managing identity columns? I mean the whole seeding issue and you end up with rows that go (1,2,3,34) I don’t really think I need to go further do I.
• Goes against data modeling and normalization
I have learned a long time ago that you need to be flexible with the rules of any system, or solution. I am not a big fan of people normalizing their database and following every best practice and such to achieve the perfect database. The fact is in most systems, if you played by the rules you would end up with a very low performing database, it would be correct, it would follow the rules but it would not perform at a high level.
I have given my feedback on all the negative comments about guids as primary keys,
Now let me add some more reasons why I use them.

First my situation in case you don’t know or am wondering. I work for a IT consulting firm in Canada. One of our clients has a database of magazine purchasing data from store to store. Basically walmart sold this one issue of people x amount of times. The system includes many servers running many databases all with different purposes but using the same set of data.
This system is where I first saw guids as primary keys. For this system there is really no other choice which brings me to what I think is the biggest strong point about guids. They are unique. The chances of generating the same guid are almost impossible which makes them perfect when you need to work with more than one database in a system.

The other thing I really like is the fact that you can never really realistically run out of guids. I have heard horror stories about int columns about running out of values.

Now let me bring my ace out of my sleeve, let me let you in on a secret why I think guids are the future. Let’s forget for a second that we are just in the database world. Let us think of our whole system. Most databases have application front ends, so let’s talk about the guys responsible for creating these front ends. The big advantage of guids is that you can generate them on the front end and immediately have control over that instance. If we used identity we would have to create the object, send it to the database and get the identity value back from the database. This is one of the hidden gems when we are talking about guids.

Before we end, let us go though some of the interesting alternatives I got from some of you.
The one that I thought about the most was using timestamp as a primary key.
Timestamps do generate in sequential order and would reduce the amount of page splits and index fragmentation however there is one problem

Unfortunately you really can't use TIMESTAMP for any type of comparison. Or really for much of anything. To quote from SQL Server Books Online:

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

Then we have the good old composite key( more than one column as the primary key)

Talk about performance issues. Try joining 2 tables with a multi column index That is some performance issues.

In closing,
I will in fact change my stance a bit. Before I was saying use guids everywhere. I stand corrected, but use them in most cases, especially if you are planning to scale out the system in the future. The performance cost is not enough to outweigh the obvious advantages. I know some people are still not going to agree with this, and that is fine. Its just one guys opinion

McCann out