hi all, i am currently developing in a database of people. currently the records throughout the db are keyed as nchar types. the question i have is would my queries perform better (with all the joins) if the key fields were all int fields? thanks
First, yes ints do compare faster than large datatypes, however the difference is normally small, especially when the larger datatype and the dataset in question is not excessively large. In other words, there is a performance benefit to switching to the smallest datatype possible, but it is normally outweighed by the fact you are carrying extra data to permit you to do that. Next, have you considered using indexed views? They can increase the performance of complex joins dramatically as well as hide the underlying complexity of the tables from developers. There is some information on this at [
http://www.sqlservercentral.com/articles/Indexed+Views/63963/] . Finally, this is a matter of some debate, but I think it is hard to normalize too much. It is however possible to do it incorrectly and that will cause problems, but I generally recommend going to 3NF and for certain situations 4NF or 5NF all make perfect sense. :
Unless you're looking at an instance where the address field has been normalized (one table for numbers, another for streets, that sort of thing), it's pretty unlikely that you're looking at an "overly" normalized system. Normalization is a process of defining the necessary information to meet the business requirements. For example, email addresses. It's very easy to say that each person gets one email address, but then, what happens when a person has two. So, you split email off & put it in a seperate table. Then you find that multiple people share an email address. Now things get hard. You can support duplicate values in the email table, or, you need to look at building an interim table to support associating a given email with more than one person and keep the email addresses unique (as they probably should be). We've just gone from one table to three. Now toss in phone numbers and addresses and you're suddenly looking at 10 or more tables to bring back basic information on an individual. But, I wouldn't call that overly normalized. I'd say it's normalized enough to support the data. As to the question on integers being better choices, yes, they are, for the reasons @Timothy said. Queries will perform better on smaller data types. The general rule is move only the data you need to move when you need to move it and store it pretty much the same way. Only make the values big enough to store what's needed. It basically comes down to the more data you can get onto a page, and that's either an index page or a data page, the better your performance will be because SQL Server will need to access fewer pages to return the data needed.
Are your tables Clustered? (i.e. do they have a clustered index?) If no, you could try this. Are you maintaining your indexes regularly? If no, you could try this. Are you shrinking and growing your database more than once a month? If yes, don't.