SQL Server Performance Tuning
How do I find the most frequently occurring value for all data in a table? How do I find that value per group?
As always, there is more than one way to get a correct result. But which way gives you the best performance? Let’s first build a simplified test scenario to illustrate what we’re after. CREATE TABLE #t (ArticleGroup CHAR(2), Price DECIMAL(8,2)) INSERT INTO #t VALUES(‘SP’, 1.2) INSERT INTO #t VALUES(‘SP’, 1.2) INSERT INTO #t VALUES(‘SP’, 2.1) […]
How can I determine the maximum value for each data? And which option provides the best performance?
One of the most common requirements is to generate a list of, say, customers along with their last order date. Or, generally speaking, a list of distinct values in one table along with a corresponding maximum value from another table. To solve such problems you basically have three different alternatives. Let’s have a look at […]
Question Which of the following joins will produce better performance? ANSI JOIN Syntax SELECT fname, lname, departmentFROM names INNER JOIN departments ON names.employeeid = departments.employeeid Former Microsoft JOIN Syntax SELECT fname, lname, departmentFROM names, departmentsWHERE names.employeeid = departments.employeeid AnswerSQL Server supports two variations of performing JOINs: the ANSI JOIN syntax and the former Microsoft JOIN […]
I don’t like to delete records in our production database, even though we will never need them again. Does this affect performance?
Question I don’t like to delete records in our production database, even though we will never need them again. Because of this, I have included an extra column in every table called the “ACTIVE_FLAG” that is set to one character in length that contains either an “a” for active records or a “d” for deleted […]
In my stored procedures I have the option of calling the same data from either a table or a view. For best performance, should I be calling a table or a view?
Anytime you call data from a view instead of a table, there is additional overhead, which can hurt performance. Because of this, optimum performance can be had by calling your data directly from a table, not a view. If you don’t use the stored procedure often, the performance difference will not be noticeable if you […]
Yes, SQL Server’s performance can be affected by using NULLS in your database. There are several reasons for this. First, NULLS that appear in fixed length columns (CHAR) take up the entire size of the column. So if you have a column that is 25 characters wide, and a NULL is stored in it, then […]
Question I understand that I should avoid the use of SQL Server cursors in order to increase an application’s performance. Can you provide some ways that I might be able to avoid using cursors in my applications? AnswerA SQL Server cursor should only be considered in situations where you need to scroll through a set […]
This is virtually no difference in performance between using SET or SELECT to assign values. In most cases you will want to use the ANSI standard, which says you should use SELECT. See the SQL Server Books Online for the exact syntax for both options.
Question I would like some input on the best, most efficient way to store images for use on a website. The two main options that I see are storing images as a BLOB in SQL Server, or storing the URL of the file in SQL Server, and actually storing the image file on a file […]
When creating temp tables within a stored procedure, is it good practice to drop them as soon as they are no longer required?
Maintaining temp tables uses up SQL Server resources, so the sooner you delete them after you are done using them, the better. For most SQL Servers, there is probably not much of a performance difference if you decide to delete them later than sooner, but on a very busy SQL Server that manages lots of […]