SQL 2005 Articles

To go through some these articles you may need Microsoft Supplied Sample Databases Pubs and NorthWnd. The SQL script for creating those db are provided here. Download these scripts and run it in your sql server.  Down Load

01) Normalization of Data

   Normalization is a well-known standard that helps efficient storage and retrieval of data from the relational database systems. There are three important normalizations are available. Read More

02) DB table Relations

The relationship between the tables are very important for the modern database systems like Oracle, SQL Server etc., The relationship between is performed by linking the column in one table with the column in other table. When we do so there are four different type of relationship will arise. Read More

03) Creating SQL 2005 Database

 This article shows how do you create a SQL Server 2005 database. Also it explains the how the storage is allocated for the db created and explains shortly the files involved in creating the database. Read More

04) Data types of SQL 2005

 SQL server provides different kind of data types that can be some times may be little confusing to make a correct choice among them. In this article, I will just cover different data types and I will leave the selection of those data types to you when you actually do your database design. Read More

05) Database Constraints

 Constraints are rules that decide what kind of data can enter in the database tables. SQL server has six types of constraints and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below. Read More

06) Database Index

We all know that data entered in the tables persisted in the physical drive in the form of database files. Think about a table say Customer (For any leading bank India) that has around 16 Million records. When we try to retrieve records for two or three customers based on his customer id, all 16 million records or taken and comparison... Read More

07) SQL Select - Part 1

 In this Article we will see how we can query the data from the database using the Select statement in SQL. In this first part, I will show some basics of it. Read More

08) SQL Select - Part 2

 In the previous part we saw the basics of select statements, how do we filter the columns and how do we filter rows. We finished at the usage of the where clause and like operator usage. In this article we will continue exploring the where clause with some other operators. Read More

09) SQL Select - Group by and Order by with top, top ties

In this article we will explore the usage of the Group By and Order By clause. Also we will se how does the TOP option behave with the Order by clause. Read More

10) Table joins in SQL 2005

Joins are useful for bringing data together from different tables based on their database relations. First we will see how the join operates between tables. Then we will explore the Order of Execution when Join and where condition both exists. Finally we will move our exploration to the Importance of the Join order. Read More

11) Inserting data into SQL Tables

We all know that to insert data into SQL server database we use the Insert statement. This article is for inserting data into the database using various techniques available. First we will start with normal insert statements. Then we will go ahead with bulk insertion and temporary table usage. Read More

12) Temp Tables and Global temp tables

In the previous articles we saw some basic and simple techniques on inserting data to table. In this article we will look at some other techniques of inserting data. First we will explore the usage of the Temp tables. Then we will look at the bulk insert using the text files. Read More

13) Transaction Isolation Levels

SQL server 2005 uses two types locks to read and write data in a safer way. The locks are shared and exclusive locks. Understanding these row level locks are useful to perform a safer Transaction the way we wanted. SQL server supports different types transaction Isolation levels and Read More

14) RANKING Functions

Ranking functions are useful to provide ranking on the table columns based on the value stored in it. Say for example the table having students and their total marks in a separate column allows you use the ranking function on... Read More 

15) Understanding SQL Transactions

In SQL Server we call a complete set of action as transaction. The complete set of action together forms a meaningful change to the database. Say for Example, you are transferring money from one bank account to another one. This involves the below..Read More 

16) Sub-Query, Derived Table and CTE

In this article, I will explain some useful select queries that come handy when you want to retrieve data from different tables. There is lot of way you can form select queries that returns same result. For this article, I used... Read More 

17) Creating View and View Restrictions

This article explains how do you create view and then update the underlying data through the view. This article also explain why someone create a view with Check..  Read More 

18) Creating and Using stored procedure

This article explains creating and using stored procedure. First the article shows, creating a basic stored procedure. Then it moves to creating procedure with parameters and return type. This also explains various techniques in executing those procedures with examples.  Read More 

19) Creating a SQL Trigger

This article explains creating and using AFTER Trigger and InStead Of Trigger. It also explains the Virtual Tables, Order of Trigger Execution and dealing with batch delete and insert using cursors.  Read More

20) Creating Scalar-Valued Function

This article explains creating a scalar valued function and different ways of invoking it. The article also explains handling the default parameters.  Read More

21) Creating Table-Valued Function

This article explains creating a table valued function and different ways of invoking it. First the article shows creating a In-Line table valued function then it walks you through creating multi-line table valued function.  Read More

22) Creating SQL-CLR Functions

You can write a function in c-sharp and deploy that in SQL Server. After Deployment, in SQL server developer point of view, it can be used as a normal SQL server function. In this article, you are going to write two functions in C# and going to deploy that in SQL Server.  Read More

22) Creating SQL-Trigger

This article explains creating SQL-CLR Trigger in C-Sharp and deploying that assembly dll in SQL Server. Later, the article walks through firing that trigger through some DML statement in Mgmt.Studio.  Read More

23) Creating and Using Full-Text Queries

The Full Text Search Queries can be enabled in Microsoft SQL 2005 and later versions. Lets us consider an online shopping web site storing products in their database. Let us also assume that the ProdDetails column can store texts of 1000 Words. Now, when user searches for a specific product details.  Read More

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)