Prefix 'N' for Unicode values in SQL Server

Chances are that you have a nvarchar, nchar or ntext field in SQL Server which expects a Unicode value. However, when you INSERT/UPDATE the field, you literally end up having ????????????? in the field. So what went wrong.

It is important to understand that any n... field expects a Unicode value (for more information on Unicode, visit http://unicode.org/). This is done by prefixing the value with 'N' where N stands for National  Language Charter Set. The prefix dictates that the value is a Unicode value. SQL Server provides built in support of Unicode value. If we do not prefix the value with an 'N', the data is interpreted as ASCII value by SQL Server. The following example demonstrates this concept:

>

UPDATE

   Employee

SET

   ArabicName = N '...value...'

WHERE

   ID = ...id...

SQL Server Orphaned Users

Recently I had to deal with what we call SQL Server 'Orphaned Users'. In this post I will talk about what they are and how to fix them. In any database environment, we need to create a Database User with specific privileges (please see my other article on creating a limited SQL Server User). However when we restore the database on another machine, we end up having 'Orphaned Users'.

An orphaned user is the one without matching SID. As you are aware, every object in SQL Server including Users have an SID which uniquely identifies it. This SID is basically a GUID stored in the Master Database. When we restore a database on another machine, the restored User has the SID from the previous location. This doesn't exist on the new machine. Even if we create a Login and associate it with the User, both SIDs are different. This results in Orphaned Users without matching IDs. To fix the above issue, we use the sp_change_users_login stored procedure. This SP has different parameters for different purposes.

To report any orphaned user, the following synxtax is used:

EXEC sp_change_users_login 'REPORT'

Similarly, if the target machine already has a Login with the same name (but different SID), the following will associate the respective User and Login:

EXEC sp_change_users_login 'Auto_Fix', 'Employee'

The above will associate the 'Employee' User with a Login of the same name. Similarly, if we first want to create the Login and then associate it with the respective User, the following synxtax will be used:

EXEC sp_change_users_login 'Auto_Fix', 'Employee', null, 'pwd'

The above will first create a User 'Employee' with Password 'Pwd' and then associate it with the respective User. We can also use the Update_One parameter which will associate a User to an existing Login.

I hope you have learned from this blog. Stay tunned for more...

Visual Studio Solution Files

When a website is created using Visual Studio, it creates a solution (.sln) file and a hidden solution user options (.suo) file. These files are created under My Documents\Visual Studio[version] folder. The solution file, an XML file, consists of the following information:

• List of projects existing under the solution
• List of project dependencies
• Source control information, including Microsoft VSTF Server
• A list of associated add-ins
• Default language used by the solution

The solution user options file, a binary file, contains user project settings for the IDE and are specific to each developer. This file consists of the following information:

• The task list
• Debugger break points and watch window settings
• Visual Studio window locations

Since the solution files are Visual Studio specific and not needed for website deployment, they are not located under the website folder (although they may exist under the same folder). Also, since a solution file consists of a list of projects and websites, it is a good practice to keep them separate. In addition, different developers can have different solution file (pointing to the same list of projects) configured to their preference.

Using visual studio, we can either create a website or a web application. The former does not have an associated project file while the later does. The project file in web applications is helpful for developers who use visual studio for building applications.

Determine the SQL Server Service Pack Installed

It can be difficult to find which Service Pack for SQL Server is installed on a machine. Neither is this information stated under SQL Server nor under Windows > Add Remove Programs. To get this information, use the following query:

SELECT SERVERPROPERTY ('productlevel')

You will get the following result (depending on the SP installed):

SP[1,2,3...]

I hope this helps. Stay tunned for more...

ASP.NET application not browsing on localhost

Recently I came across a problem where we hosted a new ASP.NET website on a freshly installed IIS (Windows 7). We were able to browse the application through the IP address. But when we tried to browse the application using the hostname or localhost, it didn't work (we reached the homepage but afterwards, all functions resulted in an asp.net error). We had the following scenario:

1. http://hostmachine/somesite (No)
2. http://localhost/somesite (No)
3. http://1.1.1.1/somesite (Yes)

After doing lots of googling (I mean binging :-), it turned out to be a potential bug where the DNS resolution didn't work for the loopback address. Microsoft claims that 127.0.0.1 is automatically resolved. But this is not the case and needs a fix. To fix this issue, go to C:\Windows\System32\drivers\etc and open the file hosts. Uncomment the following line:


# 127.0.0.1 localhost (remove #)


Now you can browse your application using localhost or hostname without any issue. I hope you find this article useful. Stay tuned for more.

SQL Server Indexing

In this blog, I will talk about SQL Server indexing. More to follow shortly...

Nested Master Pages

In this blog, we talk about nested master pages. Master Pages are a great way of providing a consistent look and feel throughout a asp.net website. More to follow shortly....

Information System

In this blog entry, I will talk about what are Information Systems? what are the different types of Information Systems and what benefits do they provide? More to follow shortly. Stay tuned for more...

SQL Server EXCEPT Clause

A few days back I had performance issues with a query in SQL Server 2005. The query was a nested query similar to the following (details omitted for brevity):

SELECT
   EmployeeID, DateAttended
FROM
   CourseDetails
WHERE
   EmployeeID NOT IN
   (
      SELECT 
         EmployeeID 
      FROM
         CourseDetails
      WHERE
         isActive = 0
   )

The above query would return all data rows where [Courses] are 'Active'. Due to the slow query, the web application was timing out. After doing some research, I finally came across the wonder EXCEPT clause in SQL Server 2005.

The EXCEPT clause returns all rows from the first query which are not present in the second query. It's more like a LEFT JOIN where rows from the left tables are returned. It is important to note that both the first and second query must have same data columns with similar data types.

Using the EXCEPT clause, we can now rewrite the above query as following:

SELECT
   EmployeeID, DateAttended
FROM
   CourseDetails
EXCEPT
   SELECT 
      EmployeeID 
   FROM
      CourseDetails
   WHERE
      isActive = 0

To my joy, the performance of the query was lightening fast. It really worked like a charm. So my advice would be to use the EXCEPT clause wherever possible and avoid nested queries.

I hope you found this blog use. Stay tuned for more.

Using Surrogate Key in Databases

I prefer using surrogate key (a non business key used as an identifier e.g. Instead of using a StudentID as an identifier, we may use a separate RowID column to identify each student record) whenever I can. Different people have different opinions about using surrogate keys in databases. Some will always use a surrogate key while others won’t. It all comes down to the personal preference of a database developer.

However, based on my experience working with surrogate keys, I am off the opinion that surrogate keys are both good and bad. Don’t get me wrong when I call them bad. They don’t do any harm rather add to the working load on behalf of the developer. Following are some of the pros and cons of using a surrogate key:


PROS

1. Primary keys (or natural keys) are hard to change. In programming, there are times when we may need to change the primary key e.g. we can change a Student-ID from type char (6) to char (8) to accommodate more students. If Student-ID is defined as a primary key, it must have links in several tables. This means, the change must be reflected in all the associated tables (a nightmare for a developer). However, if a separate surrogate key is defined, Student-ID type can be changed without affecting multiple tables.

2. A surrogate key can assist developers in programming e.g. suppose a table has a surrogate key defined while the unique key is defined by the combination of three columns. If the data in this table is displayed in a gridview in asp.net, the developer can easily write code to Update and Delete records based on the primary key. In this case, he doesn’t have to worry about all three keys which define the unique key.

3. There is no locking contention since a surrogate key is generated by the database and cached making them highly scalable.


CONS

1. Data integrity becomes the responsibility of the developer. Whenever a record is inserted or updated, the developer must check if a similar natural key already exists to avoid duplication. If the natural key was defined as the primary key, the database would take care of data integrity.

2. Excessive joins are needed since joins are dependent on keys with business value and not a surrogate key e.g. to get details of a student from different tables, a developer would prefer to use his Registration-ID and not a surrogate key.

3. An separate index must be defined on the natural key.

4. From a programming point of view, surrogate keys cannot be used as a search key.

I hope you find this blog as useful. Stay tuned for more...