SQL Server Blog : beginner

How to find all tables with a column of a particular name in SQL Server 2005 and 2008

dacrowlah — Fri, 13 Feb 2009 07:37:00 GMT

This is a simple query for finding all instances of a column with a particular name along with the table that it belongs to. This will not work on SQL Server 2000.

select sys.objects.name as TableName, sys.columns.name as ColumnName
from sys.columns
inner join sys.objects on sys.objects.object_id = sys.columns.object_id
where sys.columns.name = 'countyid'

How to enable CLR Stored procedures on SQL Server 2005 and 2008

dacrowlah — Mon, 27 Oct 2008 01:53:00 GMT

In order to make CLR stored procedures run, you must first enable the CLR in SQL Server, other wise you will just get this error:

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Enabling the CLR is fairly simple, just run this SQL Statement in Query Analyzer, while connected to the database that needs this option enabled:

EXEC sp_configure 'show advanced options' , '1'; 
reconfigure; 

EXEC sp_configure 'clr enabled' , '1' ;
reconfigure; 

EXEC sp_configure 'show advanced options' , '0'; 
reconfigure;

Making the case for stored procedures

dacrowlah — Sun, 26 Oct 2008 07:23:00 GMT

On a few of the mailing lists I am on for developers (primarily ones related to web development), there is a recurring topic that always gets people into rather heated debates: to use stored procedures or prepared statements? Now, either one you choose to use, is largely dependent on two things: preference/comfort level, and the appropriate tool for the appropriate job. This will be my attempt at defining (my opinion of) the right tool for the right job, while allowing for maximum flexibility.

Now, for those who are not aware of the difference, a prepared statement is straight SQL sent to the db for execution, but with explicitly declared parameters so as to prevent SQL injection attacks. The techniques on how to do this vary from language to language, but in ColdFusion it is through the use of the <cfqueryparam> tag, and in .NET, you use the SqlCommand object, type of text, and where you want the parameter values, you place a “?”, then add the parameters in the proper order.

Now, prepared statements are not a bad thing at all; they offer some of the same benefits of stored procedures: explicit parameterization, elevated chances of execution plan reuse, saves overhead on the db side of having to do type checking/conversion, etc. One additional benefit of using prepared statements, it’s a heck of a lot easier to write secure dynamic SQL for complex query building.

However, stored procedures are a far better choice 99% of the time; complex dynamic SQL being the sole exception that I would consent to allowing in any database I was in charge of administering or code being checked in by a developer who worked for me. Stored procedures have several distinct advantages over prepared statements (or inline SQL in general) , and some of them become more apparent only after having built large, complex, enterprise level applications that have multiple developers and support staff working on them.

Guaranteed execution plan reuse
As long as you aren’t doing dynamic SQL within your stored proc and concatenating strings for execution, absolutely no chance of a SQL injection attack happening
Performance increases for saving on permissions checks – when you submit a prepared statement for execution the database server needs to check the permissions chain on every object that that query touches, EVERY time it executes. This is not the same as an execution plan. With a stored procedure you create the procedure and GRANT EXECUTE for that proc to the Id that your web application connects to the database as.
Carefully controlled access to data – sometimes your data has sensitive information (like Social Security Numbers, secret question/answer information, etc) and while you have to both store that information, and maintain a level of security around it so that not everyone in the IT department has access to it, you create a stored proc, and enforce access to the data through stored procedures. This method also gives you the ability to give partial access to data (such as a redacted SSN, instead of the full SSN)
Modularity of code – complex logic for doing multiple operations to data in the database is easily ported from one process to another. As an example, one application I routinely work on has 3 different methods for creating a user (new user auto-registration, administrative interface for creating them by uploading an excel file, and one that creates them via a scheduled batch job from excel files uploaded by clients) and there is some pretty complex logic for finding duplicate users across multiple clients and preventing new users from being created who exist elsewhere. Since this piece of code is in the stored procedure, adding new sources and tools to create users is dead simple… call the stored procedure and that is that. One argument that I heard to try and minimize the impact of this advantage “what if you had a single, well architected application written in a single language and the query was easily available from other sections of code within the application, why is there a modularity advantage for this? Well, if you have a single language in your application, and its easily accessible everywhere else, it does lower the points this one gives you, but that was kind of my point here, it lends flexibility even if you don’t anticipate needing it now.
Minimizing the amount of traffic sent over the wire. Sometimes as developers we think 10Mbps/100Mbps/1000Mbps Ethernet has so much bandwidth that sending this little tiny 20 line query over the wire has no impact and won’t cause any appreciable slowdown. Well, that is true for the most part, but wait till your application starts getting a ton of traffic… the more packets you send over the wire, the higher your collision rate on the network, and you’ll see reduced network efficiency, sad but true; also one of the most often overlooked reasons for using stored procedures.
Wise choices in location of your business logic will greatly reduce your round trips to the database when using stored procedures. If you are performing an operation that can be completed from a solid set of parameters from start to finish and the end result (success/failure) is the only thing you need to know, a stored procedure is what you need. Since this was just used as an example I’ll use it again: say you are creating a user you have first name, last name, email, address, list of positions, departments, and supervisors. If you have ALL of that logic in your middle tier and not in a stored procedure, you will have to keep going back and doing lookups for data, making more round trips to save, add, and then complete. All of these round trips add to network overhead, time spent encoding/decoding data to send across the wire, process... locate, return… lots of data, lots of network chatter. This would all be eliminated with a stored procedure, you would feed one set of data to the proc, and execute it, and everything will be handled server side and the result handed back to you.
Centralization of maintenance, changes, security, and stronger separation of tiers.
Ease in separation in application tiers for troubleshooting. If you write a stored proc, use it in your application, and suddenly start seeing weird results in the application, you can go, execute the proc, and eyeball the results… do they look weird? No? Then the chances are good the problem lies in your application layer, start looking there. Many years of supporting messy applications and code taught me this lesson all too well.

The Basics of Database Normalization - Explained

dacrowlah — Wed, 30 Jul 2008 22:05:00 GMT

Data Normalization, refers to creating a structure to store your data in that results in reduction/elimination of redundant data. In a pure normalized form, a piece of data exists only in one place. Anywhere that the data is displayed is only making a reference to that data and joining tables together to recreate a human readable data structure. You gain several things from having a well normalized database schema, and you lose some things. Things that you gain are forced integrity of your data, everything belongs to something, attribute values are enforced to be in a known set of choices, and things are generally more clean and structured. Things you lose.. simplicity. The process of normalization results in generation of more tables, and so querying against data stored in these becomes more complex as you need to start doing joins and knowing what data will be there, what data might not be there; you can sometimes start getting very odd results if you are not careful.

As with many other subjects, there is a set of vocabulary terms that are used to express complex concepts succintly; data normalization is no different. There are varying degrees of normalization, each one being progressively more strict about what conditions must be met to attain a given level; these are referred to as "Normal Forms".

First Normal Form - typically abbreviated as 1NF, this is the most basic form, stating simply that a table must enforce that each row be unique, and not allow nulls in any column. So, an example to illustrate this lets take a table that most of you will have worked with at one point or another: Addresses. We will define the structure of the table as such:

create table Addresses
(
	AddressId int identity(1,1) not null primary key,
	StreetAddress1 nvarchar(100) not null,
	StreetAddress2 nvarchar(100) not null,
	City nvarchar(100) not null,
	State nvarchar(100) not null,
	Zip nvarchar(100) not null,
	IsMailingAddress bit not null,
	IsBillingAddress bit not null,
	IsPrimaryAddress bit not null	
)
go

This table adheres to the basic definition of 1NF, it has an identity column that has a unique key on it, which by definition, enforces that the row will be unique, even if the data is duplicated in every other column. If we changed the table to allow the IsMailingAddress (or any other column for that matter to contain a null value, then this table would no longer be properly normalized. There is some degree of contention about 1NF though; some people argue that Dr. Edgar F. Codd (the man who worked for IBM and invented the idea of relational data models) stated that null values must be allowed in a relational database. If you go with this concept, you will never be able to achieve a fully normalized database. For the sake of this discussion though, we will avoid a detailed explanation of this so as to keep things in scope.

Second Normal Form - also referred to as 2NF, this states that, first, the table must meet the guidelines of 1NF to be considered 2NF compliant. Further, that all columns in the table must rely on the key to express their uniqueness as a row in the table. Lets think about it like this if you have a table [Employees] with the columns [EmployeeId],[EmployeeName], and [Skill], and that a row in the table relied on a combination of EmployeeId and Skill to express uniqueness as a row, then the table is not meet the guidelines for 2NF. Due to the fact that EmployeeName does not depend on Skill, it only depends on EmployeeId (other employees can have the same skill, but other employees cannot have the same EmployeeId)

Third Normal Form - 3NF is the more commonly used structure when designing a new data model. It states that first a table must meet the requirements of 2NF, and goes one step further to state that all transitive or otherwise extraneous attributes have to be eliminated, and split off into what are commonly called Type tables. Let's look at again at the table above; notice how we have 3 columns that are attributes of an address IsMailingAddress, IsBillingAddress, and IsPrimaryAddress. These 3 columns are transitive properties, and would need to be removed from this table. We would first create a table AddressType, and have the columns AddressTypeId, AddressTypeName, and perhaps an AddressTypeDescription column. Next, we would create a table AddressTypes (note, these naming conventions can be adapted to best suit you, this is simply a convention that I have become accustomed to and ultimately I prefer a verbose name that directly implies what type of data we are working with), this table would have only two columns AddressId, and AddressTypeId. The goal of this is to only explicitly state what types the address IS, not also specify what they are NOT. Since not every address is a billing address, its extraneous data... we just don't care.

In most cases, 3NF is the most you will ever need to know about. There are additional ones though, but their definitions are difficult to explain in concise terms to those new to database design, so I will address those later in detail. Briefly though, they are Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), Fifth Normal Form (5NF), and Sixth Normal Form (6NF).

What is a table scan?

dacrowlah — Mon, 25 Feb 2008 01:18:00 GMT

In the world of RDBMS' you may hear the term "table scan" thrown around, this is a term used to describe an event that occurs when you search for data in a table, and your query either doesn't take advantage of an existing index, or there is no index on the table. Generally, a table scan is not a good thing to have happening on a frequent basis in your database. There are some instances where it's not a bad thing, and I will try now to explain what it is, why it's happening, and when its not that bad of an occurence.

What Happens During a Table Scan

Suppose you aren't much of an organized person, and your cubicle at work isn't really well organized. You've got papers laying all around, some filed, some stacked, some in the filing cabinet, some in folders. Generally speaking, it's hard to find data like that; then suppose your boss asks you for all of your notes related to a specific project that you are working on. Unless you work for a very large company with ample developers and enough resources to have you dedicate yourself to one project at a time, you probably have multiple projects going on, and notes on all of them are mixed around the cubicle.

So how do you find everything? Answer: you look through every stack, folder, drawer, and box you've got; and then hope against hope you have it all. Well, this is essentially a table scan, your cubicle is a table, and you my friend, are the Query Processing engine.

When a query is submitted to SQL Server, it attempts to determine the best way to execute a query, generating what is called a "Query Execution Plan", that describes how it will go about fulfilling the request to find every note related to the project, and return them to the boss. The reason these are typically considered a Very Bad Thing ® is because when SQL Server has to search through all of the data in a table to satisfy a query, it consumes more resources than it really needs to.

How to Avoid Table Scans

If you are an organized person, you keep all of your project notes neatly organized, filed away, and close to each other. SQL Server has its way of doing this as well, it's called an index; an index is a way of keeping track of what data you have,and where it resides in order to enable a fast retrieval of it when it is requested.

Now, building an index in SQL Server is easy, however knowing when and where to build them is a bit more of an art. SQL Server performance tuning is absolutely dependant on indexes, and being knowledgeable enough about how to write queries that take advantage of them is essential. Generally speaking, if a column in a table is referenced frequently in queries, then it should have an index on it, the more read intensive your database is, the more this holds true. It is not usually a good idea to have indexes on every column in a table for multiple reasons, chief among these is space considerations; under the covers, indexes are copies of the data from specific columns in a table extracted and organized for fast searching, so to build an index will take up space, roughly equivalent to the volume of data being indexed.

Another consideration to take in account when building indexes is whether or not your data is added to or updated very frequently; the more often data in a table changes, the greater the impact indexes will have in performance. Every time indexed data changes, SQL Server has to ensure that the index is kept up to date, which adds to the processing overhead which often times is overlooked in database design and performance considerations.

When a Table Scan Isn't Such a Bad Thing ®

As I mentioned previously, sometimes its not so bad to have table scans. If you are retrieving every row from a table, and plan on using it, it doesn't really matter that you are doing a table scan because there is really no other way to do it. Another time that it doesn't matter so much is when there is very little data in a table, like a type table or a small lookup table, because SQL Server can intelligently cache data when it needs to, and when there is very little data it can actually degrade performance some when it does have an index because of the overhead of maintaining the index. Striking a good balance of indexing vs. non-indexing is a skill that will come with time and experience.

It's a good idea to learn how to read an execution plan and know what your server is doing internally when it is running your queries; an upcoming post will cover this.