Tuesday, July 24, 2012

More on pivot tables - adding row totals to a pivot table

Below is a query that retrieves monthly order count for each customer and displays it in tabular form using pivot table. Second column displayed is the row total for all the months for the current year.

As an example I used Northwind database.

SELECT CompanyName, ISNULL([Jan],0) + ISNULL([Feb],0) + ISNULL([Mar],0) + ISNULL([Apr],0) + ISNULL([May],0) + ISNULL([Jun],0) + ISNULL([Jul],0) + ISNULL([Aug],0) + ISNULL([Sep],0) + ISNULL([Oct],0) + ISNULL([Nov],0) + ISNULL([Dec],0) As [Year Total],[Jan], [Feb], [Mar], [Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec] 
FROM (SELECT c.CompanyName,c.CustomerID,o.OrderID, SUBSTRING(DateName(MONTH,o.OrderDate), 0,4) As OrderMonth FROM Orders as o INNER JOIN Customers as c ON o.CustomerID=c.CustomerID WHERE YEAR(OrderDate)=Year(GETDATE())) Data
FOR OrderMonth IN([Jan], [Feb], [Mar], [Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) As MonthlyOrderQty

The result displayed will be something like this:

Company Name Year Total Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Alfreds Futterkiste 3 1 0 1 1 0 0 0 0 0 0 0 0
Ana Trujillo Emparedados y helados 1 0 0 1 0 0 0 0 0 0 0 0 0
Antonio Moreno Taquería 1 1 0 0 0 0 0 0 0 0 0 0 0
Around the Horn 4 0 1 2 1 0 0 0 0 0 0 0 0
Berglunds snabbköp 5 2 2 1 0 0 0 0 0 0 0 0 0
Blauer See Delikatessen 3 1 0 1 1 0 0 0 0 0 0 0 0
Blondesddsl père et fils 1 1 0 0 0 0 0 0 0 0 0 0 0

Tuesday, July 3, 2012

SQL Server roles

There are two types of roles when it comes to databases - server roles and database roles. Server roles should only be assigned for the advanced users, such as database administrators. Here are some server roles:

Role Description
sysadmin Can perform any task in SQL Server
serveradmin Can set server-wide configuration options, can shut down the server
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and database permissions, read logs, change passwords
processadmin Can manage processes running in SQL Server
dbcreator Can create, alter, and drop databases
diskadmin Can manage disk files
bulkadmin Can execute BULK INSERT statements
public Every SQL Server user account belongs to this server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on an object when you want the object to be available to all users

Database roles are security roles that exist on a database level, as opposed to the server level. There are two kinds: fixed roles and flexible roles. Fixed roles automatically exist in each database. Adding a user to a fixed role will not change user's permissions in any other database. Flexible database-level roles are simply defined as roles that you create in the database.
Here are the fixed database roles:
Role Description
db_owner can make extensive modifications to all aspects of the database. A user who is a db_owner can even drop the database. Here are some actions that can be performed by the user in db_owner role:
  • Creating users
  • Adding them to roles
  • Creating tables/views and stored procedures
  • Adding security settings for tables, views, and stored procedures
db_securityadmin Can modify role permissions and manage permissions with the exception of adding users to db_owner or other fixed database roles. that requires membership is db_owner role
db_accessadmin Users in this role have the ability to change database access. They can grant and revoke access to Windows logins, Windows groups and SQL Server logins. The users that they grant access to will be members of the Public role and will have all the privileges associated with that role
db_backupoperator Can create database backups but do not have permissions to restore the databases they backup. Only users in db_owner database role or in sysadmin server role or dbcreator server role can restored databases
db_ddladmin Can execute DDL commands (CREATE, ALTER, DROP) within the current database. Those users are able to create, or change, new tables, views, indexes and stored procedures. However, they cannot create users or manage security
db_datawriterCan INSERT, UPDATE, and DELETE data from any user created table
db_datareader Can read all data from all the user tables
public Every database user is a member of the Public role. If a user does not have any explicit permissions on a database object, they will inherit the permissions of the Public role. It is important to note that users cannot be removed from the Public role.

Monday, July 2, 2012


I must admit, I am guilty of using IN a lot in the WHERE clause of my queries and I am not proud of it. Replacing IN with EXISTS has significantly improved the performance of my queries and I doubt i will be going back to using IN.

SELECT * FROM Customers
WHERE CustomerID IN(SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) <10  )

SELECT * FROM Customers as c

EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing. The IN statement requires SQL Server to generate a complete result set before making a comparison.