Archive for the ‘SQL server 2005’ Category

Understanding sys.objects:

Sys.objects is a system VIEW in SQL Server 2005, for each SQL database there is a separate sys.object view which gets stored within databse itself.

Using Sys.objects returns list of all database objects and its types, type can be either of given below:








V     VIEW

How to DELETE all User Tables , stored procedures , UDF’s and Views using cursor

Use [database name]

declare @q nvarchar(max)

declare @name nvarchar(max);

declare @type nvarchar(max);

declare cur cursor for

select name ,type from sys.objects where type in(‘p’,‘fn’,‘v’,‘u’);

open cur;

fetch next from cur into @name,@type

while @@fetch_status = 0




set @q=N‘drop procedure ‘ + @name;




set @q=N‘drop function ‘ + @name;




set @q=N‘drop view ‘ + @name;




set @q=N‘drop table ‘ + @name;


exec sp_executesql @q;

fetch next from cur into @name,@type


close cur;

deallocate cur;

Read Full Post »

Normalization: is a process of organizing data and minimizing redundancy

De-normalization: is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Stored Procedure: is a named group of T-SQL statements which can be created and stored in Database as an object.

Primary Key: is a unique identifier of a row in a DB table, [it can’t be NULL]

Unique key: forces uniqueness to a respective table column, [it can be NULL]

Foreign Key: a foreign key in 1 table refers to the primary key in other table, Used to force referential integrity.

Inner join: exists in both tables

Left Outer join: all records from left side table + matched rows from right side table (totals number of rows will be same as left table)

Right Outer join: all records from right side table + matched rows from left side table (totals number of rows will be same as right table), it’s a mirror image of left outer join

Full Outer join: all records from left side table + all records from right side table, weather matched or not

Cross join: returns [left table rows * right table rows], a Cartesian product of both tables

Self join: when table joins to itself using diff aliases to avoid confusion

Union: selects only distinct records from both tables

Union all: selects all records from both tables

View: is a subset of a table, can be used to retrieve data, insert or Update data. Can contain multiple select statements inside

Trigger: A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.

Cursor: is a database object used to loop trough records on row by row bases.

Index: pointers to data records, represents structure of how data get stored physically in a table

Clustered index Non clustered index
Reorders physical data stored in table It contains pointers to data rows
A table can have Only 1 clustered index A table can have one OR many non-clustered index
Table is having By default a clustered index
Leaf nodes contains data Leaf nodes contains reference to data

Linked server: is a concept of adding other remote server to a group to query DB’s of both servers together

Collation: set of rules that determines how data stores & compares in database

Collation types: case sensitive, accent sensitive, kana sensitive, width sensitive

Data ware housing:

  1. Record should Never delete from DB
  2. All records must be linked
  3. Once committed records should be read-only
  4. All changes made must be tracked with time

User defined function (UDF): is a bunch of T-SQL statements which accepts 0 or more parameters and returns a scalar data value or table.

DDL: data definition language – e.g. TRUNCATE command is a DDL command

DML: data manipulation language – e.g. INSERT, UPDATE & DELETE are DML commands

Read Full Post »

Most of organizations use proxy server shared internet connection for their internal network. Browsing websites via proxy server requires few settings in browser itself where we set proxy server IP and port to 8080 which is default port, doing so is enough for any 1 to surf internet. But as an SQL server database programmers/developers uses SQL server management studio to connect to remote database from their client computers. Problem arises when a developer tries to connect a remote database server through SQL server management studio but CC Proxy won’t allow connecting to database directly even if developer uses all details correctly.

Why is SQL server management studio not able to connect a remote database?

A very basic thing about proxy server is that all client computers must have to pass all requests to proxy server IP, requests may be from internet browser Or FTP client Or even if its SQL server management studio.

So now if we try to connect a database with original details, proxy server (say proxy server IP is : ) will not allow connecting to database using original Host name.

Because proxy cannot process any request directly, it must come to Proxy server IP first which is (this is a dummy IP, I have used).

How to solve the problem:

Solution is simple; we just have to follow 2 steps as described below. Here we will be configuring CC proxy, SQL server management studio & SQL server configuration manager.

Step 1: Client side settings

Say for example we have following database server details:

Server name : hostname

Login ID : username

Password : password

Port : 1433 [ SQL server uses 1433 port by default, in case if its different then we have to do settings in CC proxy accordingly ]

Traditionally we use above given details directly to connect, if we connect from SSMS (SQL server management studio), it will take user to the screen as shown blow.

Now in above screen if we use Server name:
hostname then proxy will not process the request and SSMS will fail to connect to database server.

So rather than providing Server name:
hostname we have to set CC Proxy’s server IP which is But only this much will not work, because we also need to request CC proxy to a specific port which is not used anywhere else in proxy server. So we will use any port, say we use PORT: 1444 instead 1433 which is default port in SQL server.

So now in above screen we have to set [Server name:, 1444] by keeping userID password as it is.

Ok so we are now done with client side settings. But this much will also not work as we have to do few port map settings in CC proxy on server as well.

Step 2: Proxy server side settings

Setting up above step 1 setting in client machine will now request proxy server with the port 1444. So we have to define a PORT MAP setting in CC proxy for port: 1444.

Below are the steps to follow for port map settings:

  1. Open CC proxy 6.0
  2. Go to OPTIONS
  3. Click on port map, it will take you to the screen as shown below

In above screen set the following:

Dest Host : hostname

Port type : TCP

Local Port : 1444

Dest Port : 1433

Save all above details , and we are done.

After this step all client machines must have to connect to database server using Proxy server IP only with a specific port ( in our case its 1444, however we can use any port but it should not be used anywhere else in proxy server).

Read Full Post »

/*SQL server configuration settings */
exec sp_configure
sp_configure ‘show advanced options’, 1
exec sp_configure ‘Ad Hoc Distributed Queries’, 1
/*Excel import into sql table  using distributed query*/
select * into XLimport from
‘Excel 8.0;Database=C:\Documents and Settings\Administrator\Desktop\filename.xls’
select * from XLimport

Read Full Post »


APSX page :
<form id=”form1″ runat=”server”>
<div style=”padding: 200px;”>
<asp:Image ID=”imagebox” runat=”server” ImageUrl=”~/sql-image-store/image-handler.ashx” />



Handler code: image-handler.ashx

<%@ WebHandler Language=”C#” Class=”image_handler” %>

using System;

using System.Web;

using System.IO;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Configuration;

public class image_handler : IHttpHandler


public void ProcessRequest(HttpContext context)


string xStrCon = System.Configuration.ConfigurationManager.ConnectionStrings[“conStrImage”].ConnectionString;

SqlConnection xCon = new SqlConnection(xStrCon);


SqlCommand xCom = new SqlCommand(“select * from images where”, xCon);

SqlDataAdapter xAda = new SqlDataAdapter(xCom);

DataSet ds = new DataSet();


// Image processing starts

byte[] bytImage = ReadFile(context.Server.MapPath(“banner.JPG”));

xCom.CommandText = “insert into images values(2,’kensington’,@imagedata)”;

xCom.Parameters.Add(new SqlParameter(“@imagedata”, (object)bytImage));


//Display image from SQL server

byte[] imageData = (byte[])ds.Tables[0].Rows[0][2];


context.Response.ContentType = “image/JPEG”;

context.Response.OutputStream.Write(imageData, 0, imageData.Length);


byte[] ReadFile(string sPath)


//Initialize byte array with a null value initially.

byte[] data = null;

//Use FileInfo object to get file size.

FileInfo fInfo = new FileInfo(sPath);

long numBytes = fInfo.Length;

//Open FileStream to read file

FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

//Use BinaryReader to read file stream into byte array.

BinaryReader br = new BinaryReader(fStream);

//When you use BinaryReader, you need to supply number of bytes to read from file.

//In this case we want to read entire file. So supplying total number of bytes.

data = br.ReadBytes((int)numBytes);

return data;


public bool IsReusable




return false;




Read Full Post »

cte (empID,manID,depth,hierarchy) as


select e.employeeID,e.managerID ,1 , cast(e.employeeID as nvarchar(max))as hierarchy

from humanresources.Employee as e

where managerID is null

union all

select emp.employeeID,emp.managerID ,depth+1  , hierarchy + ‘/’ +cast(emp.employeeID as nvarchar(max)) as hierarchy

from humanresources.Employee emp

inner join cte

on emp.managerID=cte.empID


select * from cte order by depth

Read Full Post »

%d bloggers like this: