Application Name in a SQL Server connection string

Found an interesting feature - Application Name parameter in a connection string. It comes in very handy when you are using SQL Trace and trying to determine which application is connecting to your database, and Application Name parameter specifies what goes into Application name column in your trace file.

Sample connection string:

Data Source=myServer;Initial Catalog=myDB;User Id=myUsername;Password=myPassword;Application Name=myApp;

Why not ending URL with a slash resulted in 404 File not found error

Came accross an issue I have not encountered before - excluding trailing slash after URL.

The path to the web application I was accessing (sent to me in an e-mail) was http://ServerName/MainDirectory/Subdirectory. When I opened it in IE, I received 404 error - File not found.

The thing is that traditonally, when URL is pointing to a file, it does not include a trailing slash, but URL pointing to a directory does. So whenever the user does not put a trailing slash into the URL, the web server looks for a file with that name as opposed to a directory. leaving off a trailing slash results in a redirect if the file with that name is not found and goes to the folder with that name and looks for a default file in the directory. And that is why it takes slightly longer to load the URL without a trailing slash.

In my case I got an error and was quite puzzled as to why I get an error instead of redirect. I looked in IIS Manager and saw something strange - not only the application directory was configured as an application but all its subfolders. So that was why redirect didn't take place - web server did not see a subfolder as a subfolder but as an application.

I am going to look into it further and try to figure out why did developer configured each subdirectory as an application...

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
PIVOT(
COUNT(OrderID) 
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

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_datawriter	Can 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.

EXISTS vs. IN

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
WHERE EXISTS (SELECT 1 FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) <10 AND CustomerID=c.CustomerID)

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.

Create failed for user 'xx' error

"Create failed for user 'xx'. User or role already exists in the current database" is likely to occur when users were not properly mapped. it is fixed by running sp_change_users_login stored procedure

USE DatabaseName
GO

EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser'
GO

This procedure with action 'Update_One', links the specified user in the current database to an existing SQL Server login. User and login must be specified.

Pivot tables in SQL Server 2008

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
For example, given the data from Orders table - OrderID, CustomerID, OrderDate etc., I can create a pivot table to display number of orders per each year listed in the database:

SELECT * FROM (SELECT OrderID, YEAR(OrderDate) As OrderYear FROM Orders) Data
PIVOT(
COUNT(OrderID) 
FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

will produce something like that:

2008	2009	2010
152	408	270

which would be quite difficult to accomplish without the use of pivot table.

SELECT * FROM (SELECT CustomerID, OrderID, YEAR(OrderDate) As OrderYear FROM Orders) Data
PIVOT(
COUNT(OrderID) 
FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

will produce a list of customers with number of orders for each customer per each year:

CustomerID	2008	2009	20120
ALFKI	0	3	3
ANATR	1	2	1
ANTON	1	5	1
AROUT	2	7	4
BERGS	3	10	5
BLAUS	0	4	3
BLONP	3	7	1
BOLID	1	1	1

The query below is similar to the one just above but it returns Company name instead of Customer ID

SELECT * FROM (
SELECT c.CompanyName, o.OrderID, YEAR(o.OrderDate) As OrderYear FROM Orders as o INNER JOIN Customers as c ON o.CustomerID=c.CustomerID
) Data
PIVOT(
COUNT(OrderID) 
FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

CompanyName	2008	2009	2010
Alfreds Futterkiste	0	3	3
Ana Trujillo Emparedados y helados	1	2	1
Antonio Moreno Taquería	1	5	1
Around the Horn	2	7	4
Berglunds snabbköp	3	10	5
Blauer See Delikatessen	0	4	3
Blondesddsl père et fils	3	7	1
Bólido Comidas preparadas	1	1	1