Bytes and Bites of Internet
Database
Isolation levels in SQL Server … part II
Jan 8th
In previous post I explained basics of ISOLATION levels in general. This post is a sort of demo of isolation levels in SQL Server. In SQL Server, Isolation property can be configured using “SET ISOLATION” command. This command can be used to set isolation at any given transaction.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO -- Check user settings DBCC USEROPTIONS GO
For this post, I will be setting up isolation level to Read Uncommitted. As I explained in previous post, this isolation level is of the lowest in group. This will give users highest concurrent access as this isolation level will cause least amount of locks on resources. And users will even see uncommitted transactional data from another transaction.
First step for this test is to create a transaction to update some field in a table, I will be using my TestDB for this demo which can be created using following script,
Isolation levels in SQL Server … part I
Jan 7th
Just like any reliable transaction system, SQL Server supports ACID properties. If you have never heard of ACID properties before than it is actually set of four properties Atomicity, Consistency, Isolation and Durability. These set of properties makes sure that what ever transaction we do in SQL Server (or any DBMS) and database performs consistently every time.
One of the ACID property is Isolation. Which basically makes sure that one transaction is completely independent of another transaction and one transaction can’t access resources being used by another transaction. This basically controls concurrent access to any given resource in database. And it is implemented using locking of database resources.
Issues due to improper isolation level selection,
WordPress and Database Connection Error …
Nov 6th
This week, I changed my WP Blog password and DB Password for that blog as well. And I did it using control panel provided by my hosting service provider. It went all well without any sort of error or so. But just today when I was trying to access my blog for some past reference, with my surprise my blog was displaying “HTTP Error 500” aka Internal Server Error for my IE9 which made no sense to me … but then I fired up my Chrome/Firefox and browse my blog it was stating “Database Connection Error” … Which was kind of self explaining … I had messed up with the database connection setup.
And it is all because I assumed that when I change password for my DB of WP in control panel of Host, it automatically configures WP to make it use the newer settings … but I WAS WRONG … it actually doesn’t … changing password for DB at Host only gives access to WP to use that password to connect DB to use, but how will WP know that it has to use new password instead of new password ?? Because if you have setup the self hosting WP blog then you know that.. we store DB connection strings (passwords,DB name, DB Username, DB Server Name) manually !! So, we hard code the password for database as well. And naturally, the password update thing in host doesn’t update password in my configuration file of WP 
…
Oracle 11g R2 and Windows 7 … dealing with Enterprise Manager Troubles
Oct 3rd
So, this weekend I thought to refresh my experience with Oracle. And in that attempt, I decided to install latest of Oracle DBMS family, Oracle 11g. Now, it’s been a while since I used oracle, it was version 10g. And they have been significantly improved in version 11g, so I wanted to try them. But I think something that have not changed or may be will never change is their web interface for EM (a.k.a. Enterprise Manager). If you have not used Oracle before then for info, EM is basically their central management console for installed Oracle instance.
Now if you are installing Oracle DBMS in a system which as “Static” IP (think of it as an IP that doesn’t change with time), then you have no problem at all. But if you are like me and installing in a machine which has DHCP enabled, and its IP gets changed frequently … or just say it is your laptop or desktop at home network, then you will not be able to access this EM after sometime. The reason is, It is sticked with your IP that it assumes is static !! So, you will get “Page Not Found” message when you will try to access EM, once your IP gets changed. And worse thing is in Oracle 10g, before installation the UI (Universal Installer) checks for pre-requisites and it warns you about having DHCP enabled but in Oracle 11g you will not see anything like that.
So, back to my experience. I installed Oracle 11g R2 as usual, and I fired up the EM. It worked just fine at first, but after sometime when I tried to run it again I was greeted with same old message “Page Not Found”. And I almost smashed my laptop 
(I know, sometimes I over react to things) … then I realized then I forgot the most important step of preparation, Installation of Microsoft Loopback Adapter !!
Installing Microsoft Loopback Adapter
Things look very different in Windows 7, so it was a bit difficult to find “ADD Hardware Wizard” to add an adapter. Then I found that I can run it using “hdwwiz.exe” and after that it is same as we used to with older windows versions. Following are screenshots of whole installation and configuration procedure.
SQL Server Views … to make sure others “view” what you want them to view !!!
Sep 28th
In essence, SQL Server view can be considered as a “Virtual Table”. When we create a view, user “feel” no difference between it and a table. And we can query it exactly same way as we query any other table. View can be really handy when we are using some super complex query to get data, by shortening the query. But keep in mind that when we define a view, actually nothing happens … consider view as a just shortcut to execute some very long query … period.
Creating view by any means doesn’t “improve” the performance of query. It just simplifies the way user can execute a query 
. So if you original query generates super big , complex execution plan then it will do SAME for view.
With that said, following are few use of VIEWs.
First example is demo. of just simple view.
-- Simple View CREATE VIEW dbo.vFoo1 AS SELECT * FROM viewTest GO
But what if underlying table gets changed ?? Well, to prevent any changes to underlying tables used in views we have to create a view with a parameter called SCHEMABINDING. So when someone tries to change underlying table structure (say, adding or removing column) error is generated.
Database Normalization … taming the wild horse
Sep 19th
For any application to use database and hence data effectively it is very important that data is organized properly. The most common problem for database is redundancy in data. If table has highly redundant data, that means for every operation (like insert, update or delete) it takes more effort to find correct record. And it can be even troublesome when we simply search for record and we get redundant results which is just waste of space and time. To overcome this problem of redundancy in data, a specific procedure is developed which is called “Normalization” . As we know, in RDBMSes (like SQL Server, Oracle, MySQL) data is stored based on relation. And this relations are used in process of normalization to reduce redundancy in data and hence improving overall performance of system.
Unfortunately, normalization is the process that can be used to properly arrange data is applied during very initial phase of database designing which is called logical modeling. But, most of the time when you realize that you have screwed up and have not properly optimized database it is already late. The concept of normalization was firstly introduced by E.F. Codd, who is known to us as an inventor of relational database management system. Actually he published a paper containing 12 rules to define what is relational database actually meant. All modern DBMSes follow these rules and if we consider while designing database, it can have significant effect later.
Concepts of normalization can be explained with following example. Consider following table which shows student details including chosen subject and advisor for that subject.
Un-Normalized Data
|
Student# |
Advisor |
Adv.-Room |
Class1 |
Class2 |
Class3 |
|---|---|---|---|---|---|
|
1022 |
Jones |
412 |
101-07 |
143-01 |
159-02 |
|
4123 |
Smith |
216 |
201-01 |
211-02 |
214-01 |
First Normalization (1NF) : It is very basic rule of normalization. It dis-allows multivalued attributes & composite attributes. So, above shown table has to be rearranged as shown below.
Introduction to Indexing …. and SQL Server
Aug 28th
Indexing in simple term can be explained as a pointer to some specific information. It works nearly same as indexes at end of book, just fine the keyword you are looking for at index and get the page number where it is located (when I was studying, mostly sometimes I thought that best way to read a book is to begin from index 
, because it gives you precise location of information).
If you ask any developer or DBA about importance of indexes in database then at first they will look at you like 
or 
… and then probably they will lecture you for next 1 hour about how it is really important to have good indexes (are there bad ones too ? … of course yes) for database. I will talk about bad indexes at end, but first why we really need them ??
Well, basic use of any DBMS is to store data, right ?? And as I said, index is basically pointed to some information or in our case some record. So basically when we execute any query, Query Optimizer of Database Engine first looks for existence of any Index for the column on which we are performing operation (NOTE: before running ANY query, Query Optimizer generates a plan to execute query called EXECUTION PLAN, which is used to analyze/troubleshoot performance of query)!!! And if index exists on column in question then it becomes really easy (generally this is process, but things can get complicated at complex queries) to get results … by easy means execution time of that query reduces significantly. Of course if you have used SQL Server or even any DBMS then you know that there is no general answer for any question and you will hear “it depends” a lot from senior guys 
… which sometimes makes you doubtful about their qualification understanding … but they are correct, I mean even though I am writing it now there are many “it depends” scenario we can talk about but then this post will be more like epic then simple introduction 
… but I will try to mention some points in the end of post.