Monthly Archives: June 2008

  • 2

How Big is google’s database

Visist : (Our Official Website)

Google search crawler uses 850 TB of information, so that’s the amount of raw data from the web.

Google Analytics uses 220 TB stored in two tables: 200 TB for the raw data and 20 TB for the summaries.

Google Earth uses 70.5 TB: 70 TB for the raw imagery and 500 GB for the index data.

The second table “is relatively small (500 GB), but it must serve tens of thousands of queries per second per datacenter with low latency”.

Personalized Search doesn’t need too much data: only 4 TB.
Personalized Search stores each user’s data in Big table.
Each user has a unique user id and is assigned a row named by that user id.
All user actions are stored in a table.

Google Base uses 2 TB and Orkut only 9 TB of data.
If we take into account that all this information is compressed. For example, the crawled data has compression rate of 11%, so 800 TB become 88 TB, Google uses for all the services mentioned before 220 TB.

It’s also interesting to note that the size of the raw imagery from Google Earth is almost equal to the size of the compressed web pages crawled by Google.

Bookmark and Share

  • 0

The real difference between SQL Server and Oracle

Visit : (our Official website)

For years now there’s been a constant war between Microsoft supporters and Oracle supporters. Oracle has these features, SQL Server has these features, etc. But that’s not really where the real importance lies. Sure, functionality is a part of it because your database should be able to do what you need it to do. However, do you want to know what the real difference between the two companies is and why Microsoft has made such a strong impact in the industry?…. Read More….

Bookmark and Share

  • 1

UPDATE Based Off of A Table

visit : (our official website)

 INSERT offers the ability to insert into a table based upon a SELECT statement with the following syntax:



there are time where you may want to do a similar approach with UPDATE.
Here is the syntax to do that very task:

               [TABLEA].[FIELDB] = [TABLEB].[FIELDB] 
               [TABLEA].[FIELDC] = [TABLEB].[FIELDC] 
        FROM [TABLEA]
               JOIN [TABLEB]
        ON [TABLEA].[KEYA] = [TABLEB].[KEYA]


Bookmark and Share

  • 0

Passing parameters to dynamic query

visit (our official Website)


Consider following queries

1. SELECT * FROM @tablename

2. SELECT @colname FROM tbl

3. SELECT * FROM tbl WHERE x IN (@list)

4. SELECT @Cnt=Count(*) FROM tbl WHERE x IN (@list)


For the first query, it will give error ‘Must declare the table variable “@tablename”‘ if @tablename is not Table variable.

For Second query it will display all rows with same value contained in @colname rows in ‘tbl’.

For Third query it will display correct result if @listonly one value(no comma separated values).

Fourth one is same as third query but it stores value in a variable @Cnt. The actual problem will come to know after solving third one.

To get correct result, we need to create Dynamic query for all four queries.


The first three queries can be execute correctly by using Exec().

EXEC() takes one parameter which is an SQL statement to execute.The parameter can be a concatenation of string variables and string literals, but cannot include calls to functions or other operators.


The first three can be written as:


1. Exec (‘SELECT * FROM’ + @tablename)

2. Exec (‘SELECT ‘ + @colname +‘ FROM tbl’)

3. Exec (‘SELECT * FROM tbl WHERE x IN (‘+@list+‘)’)


But Fourth one can not be written simply using Exec(). Because if @Cnt is declared outside the statement being executed in Exec() like


Declare @Cnt int

Exec(‘SELECT @Cnt = Count(*) FROM tbl WHERE x IN (‘+@list+‘)’)

Select @Cnt


it will give error

Must declare the scalar variable “@Cnt”.


Here is need of sp_executesql instead of Exec().

sp_executesql is a system stored procedure that you can use in place of “exec” to execute your dynamic sql. It takes two pre-defined parameters and any number of user-defined parameters.

This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.

The first parameter @sql is mandatory, and contains a batch of one or more SQL statements. The data type of @sql is ntext in SQL 7 and SQL 2000, and nvarchar(MAX) in SQL 2005. A varchar value won’t do.

The second parameter @params is optional, but you will use it 90% of the time. @params declares the parameters that you refer to in @sql. The syntax of @params is exactly the same as for the parameter list of a stored procedure. The parameters can have default values and they can have the OUTPUT marker.Just like @sql, the data type of @params in SQL 7/2000 is ntext and nvarchar(MAX) in SQL 2005.

The rest of the parameters are simply the parameters that you declared in @params, and you pass them as you pass parameters to a stored procedure, either positional or named. To get a value back from your output parameter, you must specify OUTPUT with the parameter, just like when you call a stored procedure.


Let’s write the fourth query using sp_executesql.

Declare @CntRow int

Declare @sql nvarchar(200)

Declare @params nvarchar(200)

set @sql = ‘SELECT @Cnt=Count(*) FROM tbl WHERE x IN (@list)’

set @params = ‘@list varchar(100),@Cnt int output’

exec sp_executesql @sql,@params,‘1,2,3’,@CntRow output

Select @ CntRow

‘1,2,3’ will be passed to @list and result of @Cnt will be set to @CntRow.

Bookmark and Share

  • 0

To get comma separated list of values of single field in SQL Server

Visit: (our official Website)

A solution to get comma separated list of values of single field of a database table


You can use the following code to get the comma separated list of values of a single field of a table:


DECLARE @commaSeparatedVal AS VARCHAR(4000)

SELECT @commaSeparatedVal = ISNULL(@commaSeparatedVal +‘,’,) + CONVERT(VARCHAR,[SKU]) FROM PRODUCT

PRINT @commaSeparatedVal


Bookmark and Share

  • 2


visit: (our official Website)

DIFFERENCE(string1, string2)

Returns the difference between two strings as a value between 0 and 4. This function uses the SOUNDEX algorithm to compare sounds, rather than characters. If the two strings match phonetically very well, the DIFFERENCE() function will return the value 4. If they don’t resemble each other even remotely, it will return the value 0.

The DIFFERENCE function is commonly used to search with names. Instead of a comparison like:

WHERE Authors.AuthorName = sAuthor

where sAuthor is the name supplied by the user, you can use the following comparison:

WHERE DIFFERENCE(Authors.AuthorName, sAuthor) = 4

The latter comparison will match “Mansfield” with “Mansfeeld”, “Petroutsos” with “Petrutsos”, and so on.


Returns the four-character SOUNDEX code of a word. You can use this value to compare the closeness of two words. The SOUNDEX function converts a string to a four-character code: the first character of the code is the first character of the string argument, and the second through fourth characters of the code are numbers that code the consonants in the word (vowels are ignored unless they are the first letter of the string). The strings “Brannon”, “Branon”, and “Bramon” all have a SOUNDEX value of B655, because the sounds of n and m are very close. The word “Bradon”, however, has a SOUNDEX value of B635 (it differs by a consonant in the middle of the word).The word “Branok” has a SOUNDEX value of B652, since it differs in a consonant at the end.


                                 SOUNDEX (Transact-SQL)





Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.


Syntax :


SOUNDEX ( character_expression )


Arguments :


character_expression – Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.


Return Types :



Remarks :

SOUNDEX converts an alphanumeric string to a four-character code to find similar-sounding words or names. The first character of the code is the first character of character_expression and the second through fourth characters of the code are numbers. Vowels in character_expression are ignored unless they are the first letter of the string. String functions can be nested.


Examples :

The following example shows the SOUNDEX function and the related DIFFERENCE function. In the first example, the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for Smith and Smythe returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.


-> Using SOUNDEX

SELECT SOUNDEX (‘Smith’), SOUNDEX (‘Smythe’);


Here is the result set.


No column name     No column name

S530                           S530


(1 row(s) affected)


The DIFFERENCE function compares the difference of the SOUNDEX pattern results. The following example shows two strings that differ only in vowels. The difference returned is 4, the lowest possible difference.



SELECT DIFFERENCE(‘Smithers’, ‘Smythers’);


Here is the result set.


No column name



(1 row(s) affected)


In the following example, the strings differ in consonants; therefore, the difference returned is 2, the greater difference.


SELECT DIFFERENCE(‘Anothers’, ‘Brothers’);


Here is the result set.


No column name



(1 row(s) affected)



Bookmark and Share

  • 2

Full Text Search in SQL Server 2005

Visit: (our Official Website)

Definition :

Full text search is the search functionality that enables a fast and easy way of searching text based data in SQL Server.

Why Full Text Search?

Let us assume that we have huge amounts of data in the database and we are having some search functionality involved in User Interface (UI). The UI for the database is a website open to the internet so response time is crucial. Now if the administrator of the website wants to search all the customer names which have a specific pattern, then he or she queries the database system as shown in Listing 1.


Listing 1:

SELECT [NAME] FROM [CUSTOMER] WHERE [NAME] LIKE ‘%PATTERN%’The query in Listing 1 is fine for getting the result. But in real time this may not suffice the actual requirement. Let us see why the query in Listing 1 may not yield us actual results.


1. In case of a large amount of data, the like query works very slowly. It may take minutes to return the result. This is true especially for unstructured text data.


2. If I want to query my data on formatted binary data [format], I cannot use a Like query.


So to summarize, the need to of the hour is for a search functionality that is fast, efficient and can also be run on unstructured text data with the support of search on different data types like formatted binary. Full text search qualifies all these and in fact has many advantages.



Full-Text Indexing Architecture :

You might be a bit surprised to learn that SQL Server doesn’t handle its own full-text indexing tasks. Any version of Windows that SQL Server will run on includes an operating system component named the Microsoft Search Service. This service provides indexing and searching capabilities to a variety of applications, including SQL Server, Exchange, and SharePoint.

SQL Server uses an interface component, the SQL Server Handler, to communicate with the Microsoft Search Service. The Handler extracts data from SQL Server tables that have been enabled for full-text searching and passes it to the search service for indexing. Another component, the full-text OLE DB provider, gets invoked by SQL Server when you actually perform a full-text search. The provider takes the portion of the search that needs to be satisfied by the full-text index and passes it off to the Search Service for evaluation.

You need to be aware of one consequence of this architecture: because the full-text indexes are not in your SQL Server database, they can’t be backed up from within SQL Server. Instead, you need to backup the disk files created by the Search Service. You’ll find these files located under Program FilesMicrosoft SQL ServerMSSQLFTDATA.



Implementing Full Text search :

Let us discuss the steps for implementing full-text search in SQL Server 2005.


-> Adding a full text search catalog to the database – To add a full text search catalog we need to open the database node in Management studio and click on the storage node. We can see the Full text catalog in this node. We have an option for adding the full-text catalog by which we can add a new catalog.


-> Adding the Full text indexing table – We need to specify to the database that we want to implement the full-text search functionality for a table. To do this we can select the respective table and select the right click option of Full-Text Index and Define Full-Text index.


This opens up the Full text indexing wizard.


1. Using the Full text indexing wizard we select a unique index.


2. Once we select a unique index and move to the next step, we select the columns that are eligible for full text queries. This enables the full-text search for the selected columns.


3. We need to select the option of how can we track the changes. If automatically selected then a full population of indexing occurs. To avoid a population at the end of this wizard, select the Do Not Track Changes option, and clear the Start Full Population When Index Is Created checkbox.


4. Once we are done with step 3 we need to map the catalog out of all the catalogs at the database level. Or we could even create a new catalog for the full text search.


5. This is the last step of the wizard. Here we can create a table schedule and catalog schedules and schedule them with time. A table schedule can have a name, time of schedule and occurrences.


Once we create them and click on the finish button the full-text index is created.



Running the Full Text search :

Until now, we have set-up the full text search catalogs and indexes. Let us run the some queries for the full-text search. There are four types of predicates used in SQL Server 2005 for running the Full text search queries. A predicate is an expression that evaluates to TRUE, FALSE or UNKNOWN. A predicate is used in a where condition in T-SQL statement.









Let us see each one of them individually with its usage.



Freetext is the simplest form of predicates in the full text search. It searches for the words that give the similar meaning and not the exact wording of the word. This kind of predicate is used where we go to query a document as a whole for a word in it. The freetext not only returns the exact wording, but also the nearest meanings attached to it. Listing 1 shows the usage of the free text. To understand listing 1, the user should have a basic understanding of T-SQL queries. Let us also see the usage of Freetext with a help of an example. Listing 2 gives an example for the usage of FREETEXT.


Listing 2 :

FREETEXT ( { column_name | (column_list) | * }

, ‘freetext_string’ [ , LANGUAGE language_term ] )


Listing 3 :

SELECT CellularPhone FROM CART WHERE FREETEXT (CellularPhone, ‘1111’)


Listing 4 :





Freetexttable returns a collection of one or more rows as a table. The columns in the rows are selected based on the string given as the predicate. Freetexttable is similar to freetext except the former can return tables and the latter one can return columns. Listing 4 shows the usage of Freetexttable.


Listing 5 :

FREETEXTTABLE (table , { column_name | (column_list) | * }

, ‘freetext_string’

[ ,LANGUAGE language_term ]

[ ,top_n_by_rank ] )



As the name suggests, Contains actually works by verifying if a word or phrase is contained in the columns. It can search for a word, prefix of a word, synonym of a word, word formed from another word. We need to specify the language in case the languages of the words are not of the same language. Listing 5 shows the simple usage of contains column.




Listing 6 :

SELECT [Name], [Address]

FROM Customer


AND CONTAINS([Name], ‘Uday’);



Contains and Containstable are similar to freetext and freetexttable. The former one returns columns and the latter one returns the table.


Conclusion :


Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server 2005 database.


Bookmark and Share

  • 0

MERGE Command in SQL Server

visit: (our official website)

In SQL Server 2008, Microsoft introduces the MERGE functionality through the MERGE command. The MERGE command inserts rows that don’t exist and updates the rows that do exist.

    What MERGE Command works as,

        IF FOUND

           THEN UPDATE



    Until now, this MERGE functionality could only be made available in SQL Server through stored procedures and cursors, etc.

    This article illustrates how to user MERGE command in SQL Server 2008.

    Suppose we have 2 tables with same field structure as ID, ProductName and SKU,

        MERGE INTO ProductTable1 AS Target

        USING  AS ProductTable2 AS Source

                ON Target.ID = Source.ID


                UPDATE SET Target.ProductName = Source.ProductName,

                Target.SKU = Source.SKU


                INSERT (ID,ProductName,SKU) VALUES (Source.ID,Source.ProductName,Source.SKU);


Bookmark and Share

  • 0

CASE (Transact SQL)

Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

· The simple CASE function compares an expression to a set of simple expressions to determine the result.

· The searched CASE function evaluates a set of Boolean expressions to determine the result.


A. Using a SELECT statement with a simple CASE function

Within a SELECT statement, a simple CASE function allows for only an equality check; no other comparisons are made. The following example uses the CASE function to change the display of product line categories to make them more understandable.

USE AdventureWorks;
SELECT   ProductNumber, Category =
CASE ProductLine
WHEN ‘R’ THEN ‘Road’
WHEN ‘M’ THEN ‘Mountain’
WHEN ‘T’ THEN ‘Touring’
WHEN ‘S’ THEN ‘Other sale items’
ELSE ‘Not for sale’
FROM Production.Product
ORDER BY ProductNumber;

B. Using a SELECT statement with a searched CASE function

Within a SELECT statement, the searched CASE function allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.

USE AdventureWorks;
SELECT   ProductNumber, Name, ‘Price Range’ =
WHEN ListPrice =  0 THEN ‘Mfg item – not for resale’
WHEN ListPrice < 50 THEN ‘Under $50’
WHEN ListPrice >= 50 and ListPrice < 250 THEN ‘Under $250’
WHEN ListPrice >= 250 and ListPrice < 1000 THEN ‘Under $1000’
ELSE ‘Over $1000’
FROM Production.Product
ORDER BY ProductNumber ;

Bookmark and Share

  • 0

Paging in SQL Server

Visit: (our official Website)

Web Application Performance (Paging in Sql Server)

As a Web developer, you know by now that using the default paging capabilities of ASP.NET Webcontrols like DataGrid and GridView cause the Performance issue when we have thousands and thousands of records in our database; because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases. But in web application, you always have to count on its Performance.

One alternate approach to this scenario is to serve “On Demand” records. That is; for example; retrieving 1-100 records, then 101-200 records result set based on some event rather than retrieving all the records all of the time. I am talking about “Paging in Sql Server”.


The simplest solution is provided by My-SQL using LIMIT keyword. Check out the below Query:

Select * From Product Limits 15,5

It will retrieve results from 16 to 20 records. In above Query, 15 if the offset from where you want the result set records and 5 is the number of records you want to retrieve.

Sql Server 2005

Unfortunately, SQL Server does not have an equivalent to “Limit” keyword. Its nearest is TOP N, which returns the first N rows. Still there are two alternative than we can use for “Paging”.


Sql 2005 includes the ROW_NUMBER() function, which adds an integer field to each record. In other words, it adds the record’s position within the result set as an additional field so that the first record has a 1, the second a 2, etc.

To ensure the numbering is consistent, however, SQL Server needs to know how to sort the data. Because of this, ROW_NUMBER() must immediately be followed by the OVER() function. OVER() has one required parameter, which is an ORDER BY clause. The basic syntax is:


[Name], [SKU], [ManufacturerPartNumber]

FROM Product

The Product data in result set will be appeared sorted by Name, and it has an extra column indicating each record’s position within the results.

If we want to limit the results displayed to a certain range, we need to nest this SELECT inside another one. To limit our results to records 5 to 10, we can use the following query:



[Name], [SKU], [ManufacturerPartNumber] FROM Product) AS Product1

WHERE RowNum >= 5 AND RowNum <= 10

With Nested Queries

There is one another solution that consists only of 1 sql-statement, and so is efficient and quick, even with large databases. The good news is : it will work ! Always ! it’s looking rather a bit complicated; but its not!

Let us see the Example directly to limit our results to records 6 through 10,

SELECT P.[Name], P.[SKU], P.[ManufacturerPartNumber] FROM (

SELECT TOP 5 [ProductID],[Name] FROM (

SELECT TOP 10 [ProductID],[Name]

FROM Product

ORDER BY Product.[Name] ASC) as Product1

ORDER by Product1.[Name] DESC) as Product2

INNER JOIN Product P ON Product2.ProductID = P.ProductID

ORDER by Product2.[Name] ASC

Where 5 is the number of rows you want to retrieve and 10 is the offset.

The innermost Sql Statement will fetch 10 records (1 to 10) Order by Product Name in Ascending Order.

This means that if you have a table with 10,000 records, and you want to have the last 10 records, the innermost SELECT statement will indeed retrieve 10,000 records. This of course has it’s ramifications on performance, but since it’s only the primary key fields (which are indexed) and optionally some sort fields, the impact will be minimal.

The middle Sql Statement will fetch 5 records (10 to 6) Order by Product Name in Descending Order. And Finally the Outer Sql Stateement is simply select statement that will fetch the result in Ascending Order (6 to 10). In Innermost and Midddle Sql Statement only primary key fields or the fields which are indexed are used.

After looking at this in Query Analyzer, it appears that the extra nesting does not add very much to the load. The SQL statement plays with the sorting orders to limit the records, thus resulting in paged recordsets. This means that there should be at least ONE field to be sorted. If there isn’t any, sort on the primary key field(s) !

Bookmark and Share