Archive for the ‘SQL Server 2008’ Category
How to Calculate Running Totals, Subtotals and Grand Total Without a Cursor
Posted by: admin in MS SQL Server, SQL Express Edition, SQL Server 2008 on August 13th, 2011
If you have ever had the need to show detailed data of individual transactions and also keep a running total, subtotals, and grand total columns at the same time, but were not exactly sure how to tackle the problem then this article might help. In this article I will show you a few different techniques for calculating and summing information on multiple rows without using a cursor. The techniques I will show you will just use a basic SELECT statement. Of course, the calculations of the running total, subtotals and grand total will be done using other SQL clauses and functions like SUM and CASE.
Sample Data Used by Examples
Prior to showing you my examples, I will first need to establish a set of test data, which all my examples will use. My test data will consist of an “Orders” table with the following format:
create table Orders (OrderID int identity, OrderAmt Decimal(8,2), OrderDate SmallDatetime)
I’ve populated this test Orders table with the following set of records:
OrderID OrderAmt OrderDate ----------- ---------- -------------------- 1 10.50 2003-10-11 08:00:00 2 11.50 2003-10-11 10:00:00 3 1.25 2003-10-11 12:00:00 4 100.57 2003-10-12 09:00:00 5 19.99 2003-10-12 11:00:00 6 47.14 2003-10-13 10:00:00 7 10.08 2003-10-13 12:00:00 8 7.50 2003-10-13 19:00:00 9 9.50 2003-10-13 21:00:00
All my examples will be using this table to produce the running totals, sub totals, and grand total reports shown below. Basically this table contains a number of different orders that where created over time. Each order has an ID (OrderID) which uniquely identifies each record, an order amount (OrderAmt) that holds a decimal amount for the order, and a timestamp (OrderDate) that identifies when the order was placed.
Running Total On Each Record
This first example will display a simple method of calculating the running total of the OrderAmt. The calculated running total will be displayed along with each record in the Orders table. The “Running Total” column will be created with a simple SELECT statement and a correlated sub query. The correlated sub query is the part of the statement that does the heavy lifting to produce the running total.
select OrderId, OrderDate, O.OrderAmt
,(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID)
'Running Total'
from Orders O
When I run this query against my Orders table I get the following results:
OrderId OrderDate OrderAmt Running Total ----------- -------------------- ---------- ------------- 1 2003-10-11 08:00:00 10.50 10.50 2 2003-10-11 10:00:00 11.50 22.00 3 2003-10-11 12:00:00 1.25 23.25 4 2003-10-12 09:00:00 100.57 123.82 5 2003-10-12 11:00:00 19.99 143.81 6 2003-10-13 10:00:00 47.14 190.95 7 2003-10-13 12:00:00 10.08 201.03 8 2003-10-13 19:00:00 7.50 208.53 9 2003-10-13 21:00:00 9.50 218.03
As you can see, there is a “Running Total” column that displays the running total along with the other column information associated with each Orders table record. This running total column is calculated, by summing up the OrderAmt for all Orders where the OrderID is less than or equal to the OrderID of the current ID being displayed.
Running Total for Each OrderDate
This example is similar to the one above, but I will calculate a running total for each record, but only if the OrderDate for the records are on the same date. Once the OrderDate is for a different day, then a new running total will be started and accumulated for the new day. Here is the code to accomplish this:
select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt,
(select sum(OrderAmt) from Orders
where OrderID <= O.OrderID and
convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
'Running Total'
from Orders O
order by OrderID
Here are the results returned from the query using my sample Orders Table:
OrderId Order Date OrderAmt Running Total ----------- ---------- ---------- --------------- 1 10/11/2003 10.50 10.50 2 10/11/2003 11.50 22.00 3 10/11/2003 1.25 23.25 4 10/12/2003 100.57 100.57 5 10/12/2003 19.99 120.56 6 10/13/2003 47.14 47.14 7 10/13/2003 10.08 57.22 8 10/13/2003 7.50 64.72 9 10/13/2003 9.50 74.22
Note that the “Running Total” starts out with a value of 10.50, and then becomes 22.00, and finally becomes 23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But when OrderID 4 is displayed the running total is reset, and the running total starts over again. This is because OrderID 4 has a different date for its OrderDate, then OrderID 1, 2, and 3. Calculating this running total for each unique date is once again accomplished by using a correlated sub query, although an extra WHERE condition is required, which identified that the OrderDate’s on different records need to be the same day. This WHERE condition is accomplished by using the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.
Running Totals With Subtotals and Grand totals
In this example, I will calculate a single sub totals for all Orders that were created on the same day and a Grand Total for all Orders. This will be done using a CASE clause in the SELECT statement. Here is my example.
select O.OrderID,convert(char(10),O.OrderDate,101) 'Order Date',O.OrderAmt,
case when OrderID = (select top 1 OrderId from Orders
where convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101)
order by OrderID desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders
where OrderID <= O.OrderID
and convert(char(10),OrderDate,101)
= convert(char(10),O.OrderDate,101))
else ' ' end as 'Sub Total',
case when OrderID = (select top 1 OrderId from Orders
order by OrderDate desc)
then (select cast(sum(OrderAmt) as char(10))
from Orders)
else ' ' end as 'Grand Total'
from Orders O
order by OrderID
Output from the SELECT statement looks like this:
OrderID Order Date OrderAmt Sub Total Grand Total ----------- ---------- ---------- ---------- ----------- 1 10/11/2003 10.50 2 10/11/2003 11.50 3 10/11/2003 1.25 23.25 4 10/12/2003 100.57 5 10/12/2003 19.99 120.56 6 10/13/2003 47.14 7 10/13/2003 10.08 8 10/13/2003 7.50 9 10/13/2003 9.50 74.22 218.03
In this example the first CASE statement controls the printing of the “Sub Total’ column. As you can see, the sub total is printed only on the last order of the day, which is determined by using a correlated sub query. The second CASE statement prints the “Grand Total”, which is only printed along with the very last order. Each of these CASE statements uses the TOP clause to determine which OrderID is the correct order for which to print out the “Grand Total”.
Conclusion
Hopefully these examples will help you understand different methods that can be used to calculate running totals, sub totals, and a grand total. As you can see you don’t need to use a cursor to calculate these different totals. With the creative use of correlated sub queries and other SELECT clauses like CASE you can easily create all these different totals. Next time you need to calculate totals consider using one of these non-cursor based solutions.
Posted by: admin in Database, MS SQL Server, SQL Express Edition, SQL Server 2008 on August 12th, 2011
Top 10 SQL Server Counters for Monitoring SQL Server Performance
Do you have a list of SQL Server Counters you review when monitoring your SQL Server environment? Counters allow you a method to measure current performance, as well as performance over time. Identifying the metrics you like to use to measure SQL Server performance and collecting them over time gives you a quick and easy way to identify SQL Server problems, as well as graph your performance trend over time.
Below is my top 10 list of SQL Server counters in no particular order. For each counter I have described what it is, and in some cases I have described the ideal value of these counters. This list should give you a starting point for developing the metrics you want to use to measure database performance in your SQL Server environment.
1. SQLServer: Buffer Manager: Buffer cache hit ratio
The buffer cache hit ratio counter represents how often SQL Server is able to find data pages in its buffer cache when a query needs a data page. The higher this number the better, because it means SQL Server was able to get data for queries out of memory instead of reading from disk. You want this number to be as close to 100 as possible. Having this counter at 100 means that 100% of the time SQL Server has found the needed data pages in memory. A low buffer cache hit ratio could indicate a memory problem.
2. SQLServer: Buffer Manager: Page life expectancy
The page life expectancy counter measures how long pages stay in the buffer cache in seconds. The longer a page stays in memory, the more likely SQL Server will not need to read from disk to resolve a query. You should watch this counter over time to determine a baseline for what is normal in your database environment. Some say anything below 300 (or 5 minutes) means you might need additional memory.
3. SQLServer: SQL Statistics: Batch Requests/Sec
Batch Requests/Sec measures the number of batches SQL Server is receiving per second. This counter is a good indicator of how much activity is being processed by your SQL Server box. The higher the number, the more queries are being executed on your box. Like many counters, there is no single number that can be used universally to indicate your machine is too busy. Today’s machines are getting more and more powerful all the time and therefore can process more batch requests per second. You should review this counter over time to determine a baseline number for your environment.
4. SQLServer: SQL Statistics: SQL Compilations/Sec
The SQL Compilations/Sec measure the number of times SQL Server compiles an execution plan per second. Compiling an execution plan is a resource-intensive operation. Compilations/Sec should be compared with the number of Batch Requests/Sec to get an indication of whether or not complications might be hurting your performance. To do that, divide the number of batch requests by the number of compiles per second to give you a ratio of the number of batches executed per compile. Ideally you want to have one compile per every 10 batch requests.
5. SQLServer: SQL Statistics: SQL Re-Compilations/Sec
When the execution plan is invalidated due to some significant event, SQL Server will re-compile it. The Re-compilations/Sec counter measures the number of time a re-compile event was triggered per second. Re-compiles, like compiles, are expensive operations so you want to minimize the number of re-compiles. Ideally you want to keep this counter less than 10% of the number of Compilations/Sec.
6. SQLServer: General Statistics: User Connections
The user connections counter identifies the number of different users that are connected to SQL Server at the time the sample was taken. You need to watch this counter over time to understand your baseline user connection numbers. Once you have some idea of your high and low water marks during normal usage of your system, you can then look for times when this counter exceeds the high and low marks. If the value of this counter goes down and the load on the system is the same, then you might have a bottleneck that is not allowing your server to handle the normal load. Keep in mind though that this counter value might go down just because less people are using your SQL Server instance.
7. SQLServer: Locks: Lock Waits / Sec: _Total
In order for SQL Server to manage concurrent users on the system, SQL Server needs to lock resources from time to time. The lock waits per second counter tracks the number of times per second that SQL Server is not able to retain a lock right away for a resource. Ideally you don’t want any request to wait for a lock. Therefore you want to keep this counter at zero, or close to zero at all times.
8. SQLServer: Access Methods: Page Splits / Sec
This counter measures the number of times SQL Server had to split a page when updating or inserting data per second. Page splits are expensive, and cause your table to perform more poorly due to fragmentation. Therefore, the fewer page splits you have the better your system will perform. Ideally this counter should be less than 20% of the batch requests per second.
9. SQLServer: General Statistic: Processes Block
The processes blocked counter identifies the number of blocked processes. When one process is blocking another process, the blocked process cannot move forward with its execution plan until the resource that is causing it to wait is freed up. Ideally you don’t want to see any blocked processes. When processes are being blocked you should investigate.
10. SQLServer: Buffer Manager: Checkpoint Pages / Sec
SQL Tuning and SQL Optimization Tips
Posted by: admin in Database, MS SQL Server, SQL Express Edition, SQL Server 2008 on August 11th, 2011
SQL Tuning/SQL Optimization Techniques:
1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than ‘*’.
For Example: Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;
2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as
SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;
Instead of:
SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';
3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';
Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';
4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as
Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)
Instead of:
Select * from product p
where product_id IN
(select product_id from order_items
5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of:
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;
6) Try to use UNION ALL in place of UNION.
For Example: Write the query as
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;
Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;
7) Be careful while using conditions in WHERE clause.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);
Write the query as
SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of:
SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)
Write the query as
SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';
Instead of:
SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';
Use non-column expression on one side of the query because it will be processed earlier.
Write the query as
SELECT id, name, salary
FROM employee
WHERE salary < 25000;
Instead of:
SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;
Write the query as
SELECT id, first_name, age
FROM student_details
WHERE age > 10;
Instead of:
SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;
8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as
SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';
Instead of:
SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';
9) To store large binary objects, first place them in the file system and add the file path in the database.
10) To write queries which provide efficient performance follow the general SQL standard rules.
a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb
How To: Find / Check if the index is exists
Posted by: admin in Database, MS SQL Server, SQL Express Edition, SQL Server 2008 on May 11th, 2011
IF EXISTS(select * from sys.indexes where object_id = OBJECT_ID(‘TableName’) AND name=’IndexName’)
print ‘Exists’
or
exec sp_helpindex ‘TableName’
Could not allocate space for object ‘dbo.SORT temporary run storage
Posted by: admin in MS SQL Server, SQL Server 2008 on April 26th, 2011
This error happens when you do not have enough disk space.
Sql Server Basic
Posted by: admin in Database, MS Access, MS SQL Server, SQL Express Edition, SQL Server 2008, Sql Server Notes on February 23rd, 2011
Concurrent Connections – 32,767
Storage – 524, 272 TB per Database
# of Database – 32, 767
# of of Tables per Database – 2,147,483,647
# of Columns per Table – 1024
How to assign Default value of the Field in the table
Posted by: admin in MS SQL Server, SQL Server 2008 on June 16th, 2010
Use the ALTER TABLE command to assign the default value of the field.
Example:
ALTER TABLE [dbo].[tblHyperLink] ADD CONSTRAINT [DF__tblHyperL__LastU__021E29CA] DEFAULT (getdate()) FOR [LastUpdate]
GO
How to check if a varchar (string) column can be converted to numeric
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on May 19th, 2010
Use the ISNUMERIC function of the sql server to this job.
To check all records of that particular column if it contains a string or can be converted to numeric use the script below.
select
case when sum(
case when COLUMN_NAME is null then 1
when ltrim(rtrim(COLUMN_NAME)) = ” then 1
else isnumeric(COLUMN_NAME)
end) = count(*) then 1 else 0 end x from TABLENAME
You can find more info here
How to email result query from SQL Server
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on May 7th, 2010
To send the result via email in sql server use the sp_send_dbmail procedures but before you execute that sp you need to enable the Database Mail using the Configuration Wizard or use the sp_configure
Example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘MyProfilename’,
@recipients = ‘myemail@gmail.com’,
@query =’SELECT * FROM tblname,
@body = ‘Description.’,
@subject = ‘Subject’ ;
How to check the SQL Server Version
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on May 5th, 2010
You can check the sql server version by using the SERVERPROPERTY function.
Exampe:
SELECT
SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,
SERVERPROPERTY(‘Edition’) AS Edition,
SERVERPROPERTY(‘EngineEdition’) AS EngineEdition;
GO